데이터베이스 질문과 답변
by 홍이 [SQL Query] SQL Query SQL 쿼리 MySQL [2018.09.28 16:45:21]
안녕하세요. sql로 count를 하려고 하는데 어떻게 해야 할지 생각이 나지 않아 질문 드릴려고 합니다.
code 컬럼에 AA1 ~ AA7까지 "," 쉼표 구분자로 한개의 값만 있을 수 있고 여러개의 값이 있을수 있는 컬럼이 있습니다.
AA1
AA3
AA5
의 각각 갯수를 COUNT하는 쿼리를 짜고 싶습니다.
각각 그냥 COUNT를 하는것이면 구분자로 발라내어 COUNT하면 될것 같은데
AA1,AA3,AA5가 같이 있을 때 이것의 COUNT를 AA1 1개, AA3 1개, AA5 1개 가 아닌
AA1만 1개로 COUNT를 해야 해서 고민 해봐도 도저히 답이 보이지 않아 질문을 드리게 되었습니다.
아침 저녁으로 쌀쌀한 날씨에 감기 조심하세요
감사합니다.
SELECT CODE, COUNT(*) CNT
FROM (
SELECT CASE WHEN INSTR(CODE,'AA1')>0 THEN 'AA1'
WHEN INSTR(CODE,'AA2')>0 THEN 'AA2'
WHEN INSTR(CODE,'AA3')>0 THEN 'AA3'
ELSE 'AA'
END AS CODE
FROM 테이블)
WHERE CODE > 'AA'
GROUP BY CODE
;
질문 내용이 정확히 이해가 안가지만
유추해 볼땐 AA1, AA3, AA5가 같이 있어도 하나로만 카운트 하고 싶다는 것으로 이해 했습니다.
그래서 인라인뷰로 AA1부터 AA3, AA5가 있는지 INSTR함수로 확인 후 먼저 나오는 것 하나만 취하도록 한후
AA1, AA3, AA5가 없는 것은 빼고 COUNT했습니다.
적당히 이용해서 만들어 보세요. ^^
AA1,AA3,AA5 이 필드값일때 가장 앞에 값인 AA1 만 카운트해서 AA1 1 이렇게 되는건가요?
만약 가장 앞에 값만 카운트가 필용하다고 하면 문자열을 잘라 그룹핑하여 카운트 하면 될것 같은네요
WITH data_t AS
(
SELECT 1 no, 'AA1' code, '2018-09-28' wdate
UNION ALL SELECT 2, 'AA1,AA2' , '2018-09-28'
UNION ALL SELECT 3, 'AA2,AA3' , '2018-09-28'
UNION ALL SELECT 4, 'AA3,AA4' , '2018-09-28'
UNION ALL SELECT 5, 'AA1' , '2018-09-28'
UNION ALL SELECT 6, 'AA2,AA4' , '2018-09-28'
UNION ALL SELECT 7, 'AA1,AA6' , '2018-09-28'
UNION ALL SELECT 8, 'AA3,AA5' , '2018-09-28'
UNION ALL SELECT 9, 'AA2,AA3,AA4', '2018-09-28'
UNION ALL SELECT 10, 'AA1,AA3,AA5', '2018-09-28'
UNION ALL SELECT 11, 'AA3,AA5' , '2018-09-28'
UNION ALL SELECT 12, 'AA1,AA5' , '2018-09-28'
UNION ALL SELECT 13, 'AA3,AA7' , '2018-09-28'
UNION ALL SELECT 14, 'AA1,AA5' , '2018-09-28'
)
, code_t AS
(
SELECT 'AA1' code
UNION ALL SELECT 'AA2'
UNION ALL SELECT 'AA3'
UNION ALL SELECT 'AA4'
UNION ALL SELECT 'AA5'
UNION ALL SELECT 'AA6'
UNION ALL SELECT 'AA7'
)
SELECT c.code
, COUNT(*) cnt
FROM data_t d
INNER JOIN code_t c
ON INSTR(d.code, c.code) > 0
GROUP BY c.code
;
아! 질문을 다시 읽어 보니 이게 아닌 듯...
SELECT SUBSTR(code, 1, 3) code
, COUNT(*) cnt
FROM data_t
GROUP BY SUBSTR(code, 1, 3)
;
DB는 postgreSQL에서 실행했습니다.
AA1,AA3,AA5 가 같이있는경우에만 AA1을 카운트하고 그외에는 각각 (AA1,AA3,AA5)카운트하는걸로 이해해서 작성했습니다.
WITH DATA_T(NO,CODE,WDATE) AS
(
SELECT 1, 'AA1' , '2018-09-28' UNION ALL
SELECT 2, 'AA1,AA2' , '2018-09-28' UNION ALL
SELECT 3, 'AA2,AA3' , '2018-09-28' UNION ALL
SELECT 4, 'AA3,AA4' , '2018-09-28' UNION ALL
SELECT 5, 'AA1' , '2018-09-28' UNION ALL
SELECT 6, 'AA2,AA4' , '2018-09-28' UNION ALL
SELECT 7, 'AA1,AA6' , '2018-09-28' UNION ALL
SELECT 8, 'AA3,AA5' , '2018-09-28' UNION ALL
SELECT 9, 'AA2,AA3,AA4', '2018-09-28' UNION ALL
SELECT 10, 'AA1,AA3,AA5', '2018-09-28' UNION ALL
SELECT 11, 'AA3,AA5' , '2018-09-28' UNION ALL
SELECT 12, 'AA1,AA5' , '2018-09-28' UNION ALL
SELECT 13, 'AA3,AA7' , '2018-09-28' UNION ALL
SELECT 14, 'AA1,AA5' , '2018-09-28'
)
, CODE_T (CODE)AS
(
SELECT 'AA1' UNION ALL
SELECT 'AA3' UNION ALL
SELECT 'AA5'
)
,CODE_ARRAY_T(NO,CODE,WDATE) AS
(
SELECT A.NO
,UNNEST(STRING_TO_ARRAY( A.CODE ,',') ) AS CODE
,A.WDATE
FROM DATA_T A
GROUP BY A.NO,A.WDATE,A.CODE
ORDER BY A.NO,A.WDATE,A.CODE
)
,CODE_CNT_T(NO,CNT) AS
(
SELECT NO,COUNT(*) CNT
FROM CODE_ARRAY_T A
WHERE CODE IN (SELECT CODE FROM CODE_T)
GROUP BY NO
ORDER BY NO
)
SELECT CODE
,COUNT(*)-MAX(CNT) AS CNT
FROM (
SELECT A.NO
,CASE WHEN CNT=3 THEN REPLACE(A.CODE,A.CODE,'AA1') ELSE A.CODE END AS CODE
,CASE WHEN CNT=3 THEN 2 ELSE 0 END AS CNT
FROM CODE_ARRAY_T A
LEFT OUTER JOIN CODE_CNT_T B
ON A.NO = B.NO
WHERE 1=1
AND A.CODE IN (SELECT CODE FROM CODE_T)
GROUP BY A.NO,A.CODE,B.CNT
ORDER BY A.NO,A.CODE,B.CNT
)AA
GROUP BY CODE
ORDER BY CODE
;
--결과
CODE | CNT
AA1 7
AA3 6
AA5 4
) 버튼을 클릭하여 작성 하시면 됩니다.