반응형
- GPT의 도움을 받았습니다
- 카테고리는 code1 ~ code5 까지 있습니다.
- code1 필드값은 'cate' 로 통일합니다
- code2에 들어갈 데이터를 생성합니다 ( https://knsesang2000.tistory.com/592 )
- code3에 들어갈 데이터를 생성합니다 ( https://knsesang2000.tistory.com/593 )
- code4에 들어갈 데이터를 생성합니다
● 테이블 구조 ( POSTGRESQL )
codeseq - INTEGER
code1 - VARCHAR(50)
code2 - VARCHAR(50)
code3 - VARCHAR(50)
code4 - VARCHAR(50)
code5 - VARCHAR(50)
codename - VARCHAR(50)
regdate - TIMESTAMP WITHOUT TIME ZONE
enableyn - VARCHAR(1)
voteyn - VARCHAR(1)
comment - VARCHAR(100)
● 결과값 먼저 확인하기
WITH colors AS (
-- 색상을 랜덤하게 섞어서 배열로 만듭니다.
SELECT array_agg(color ORDER BY random()) AS color_list
FROM unnest(ARRAY['빨강', '주황', '노랑', '초록', '파랑', '남색', '보라']) AS color
),
existing_categories AS (
-- 기존 카테고리를 선택합니다.
SELECT code2, code3, codename, comment
FROM t_code
WHERE code1 = 'cate' AND code3 IS NOT NULL AND code4 IS NULL
),
random_color_counts AS (
-- 각 카테고리에 대해 3-6개의 랜덤한 색상 개수를 생성합니다.
SELECT
code2,
code3,
codename,
comment,
3 + floor(random() * 4)::int AS color_count
FROM existing_categories
),
numbered_colors AS (
-- 색상을 랜덤하게 선택하고 번호를 매깁니다.
SELECT
rc.code2,
rc.code3,
rc.codename,
rc.comment,
c.color_list[s.a] AS color,
TO_CHAR(ROW_NUMBER() OVER (PARTITION BY rc.code3 ORDER BY s.a), 'FM000') AS color_number
FROM
random_color_counts rc
CROSS JOIN colors c
CROSS JOIN LATERAL (
SELECT a
FROM generate_series(1, 7) a
WHERE a <= rc.color_count
ORDER BY random()
) s
)
SELECT
'cate' AS code1,
nc.code2,
nc.code3,
nc.code3 || ';' || nc.color_number AS code4,
NULL AS code5,
nc.color AS codename,
now()::timestamp AS regdate,
'Y' AS enableyn,
'N' AS voteyn,
nc.comment || ' > ' || nc.color AS comment
FROM
numbered_colors nc
ORDER BY nc.code2, nc.code3, RANDOM();
● DB에 생성
WITH colors AS (
-- 색상을 랜덤하게 섞어서 배열로 만듭니다.
SELECT array_agg(color ORDER BY random()) AS color_list
FROM unnest(ARRAY['빨강', '주황', '노랑', '초록', '파랑', '남색', '보라']) AS color
),
existing_categories AS (
-- 기존 카테고리를 선택합니다.
SELECT code2, code3, codename, comment
FROM t_code
WHERE code1 = 'cate' AND code3 IS NOT NULL AND code4 IS NULL
),
random_color_counts AS (
-- 각 카테고리에 대해 3-6개의 랜덤한 색상 개수를 생성합니다.
SELECT
code2,
code3,
codename,
comment,
3 + floor(random() * 4)::int AS color_count
FROM existing_categories
),
numbered_colors AS (
-- 색상을 랜덤하게 선택하고 번호를 매깁니다.
SELECT
rc.code2,
rc.code3,
rc.codename,
rc.comment,
c.color_list[s.a] AS color,
TO_CHAR(ROW_NUMBER() OVER (PARTITION BY rc.code3 ORDER BY s.a), 'FM000') AS color_number
FROM
random_color_counts rc
CROSS JOIN colors c
CROSS JOIN LATERAL (
SELECT a
FROM generate_series(1, 7) a
WHERE a <= rc.color_count
ORDER BY random()
) s
)
INSERT INTO t_code (code1, code2, code3, code4, code5, codename, regdate, enableyn, voteyn, comment)
SELECT
'cate' AS code1,
nc.code2,
nc.code3,
nc.code3 || ';' || nc.color_number AS code4,
NULL AS code5,
nc.color AS codename,
now()::timestamp AS regdate,
'Y' AS enableyn,
'N' AS voteyn,
nc.comment || ' > ' || nc.color AS comment
FROM
numbered_colors nc
ORDER BY nc.code2, nc.code3, RANDOM();
● 하위 카테고리 갯수 불러오기
CREATE OR REPLACE FUNCTION get_subcategory_count(
p_code_1 VARCHAR(5),
p_code_2 VARCHAR(5) DEFAULT NULL,
p_code_3 VARCHAR(5) DEFAULT NULL,
p_code_4 VARCHAR(5) DEFAULT NULL
)
RETURNS INTEGER AS $$
DECLARE
v_count INTEGER;
v_level INTEGER;
BEGIN
-- 입력된 파라미터에 따라 레벨 결정
IF p_code_4 IS NOT NULL AND p_code_4 != '' THEN
v_level := 4;
ELSIF p_code_3 IS NOT NULL AND p_code_3 != '' THEN
v_level := 3;
ELSIF p_code_2 IS NOT NULL AND p_code_2 != '' THEN
v_level := 2;
ELSE
v_level := 1;
END IF;
-- 레벨에 따라 적절한 쿼리를 실행
IF v_level = 1 THEN
-- code1이 주어졌을 때 code2의 개수를 계산
SELECT COUNT(DISTINCT code2)
INTO v_count
FROM t_code
WHERE code1 = p_code_1 AND code2 IS NOT NULL AND code2 != '';
ELSIF v_level = 2 THEN
-- code2가 주어졌을 때 code3의 개수를 계산
SELECT COUNT(DISTINCT code3)
INTO v_count
FROM t_code
WHERE code1 = p_code_1 AND code2 = p_code_2 AND code3 IS NOT NULL AND code3 != '';
ELSIF v_level = 3 THEN
-- code3이 주어졌을 때 code4의 개수를 계산
SELECT COUNT(DISTINCT code4)
INTO v_count
FROM t_code
WHERE code1 = p_code_1 AND code2 = p_code_2 AND code3 = p_code_3 AND code4 IS NOT NULL AND code4 != '';
ELSIF v_level = 4 THEN
-- code4가 주어졌을 때 code5의 개수를 계산
SELECT COUNT(DISTINCT code5)
INTO v_count
FROM t_code
WHERE code1 = p_code_1 AND code2 = p_code_2 AND code3 = p_code_3 AND code4 = p_code_4 AND code5 IS NOT NULL AND code5 != '';
END IF;
RETURN v_count;
END;
$$
LANGUAGE plpgsql;
● 검색결과 쿼리
-- 쿼리
select *, get_subcategory_count('cate',code2 , code3 , '') as count from t_code where code1='cate' and code2 is not null and code3 is not null and code4 is not null and code5 is null order by code1,code2,code3,code4;
반응형