데이터베이스 질문과 답변
현재 아래와 같은 테이블을 사용하고 있습니다(필요한 부분만 표현했습니다).
TABLE A
| SA_ID | SB_ID | Q15 |
| 1 | 1 | 80 |
| 2 | 2 | 60 |
| 3 | 2 | 70 |
| 4 | 3 | 80 |
| 5 | 3 | 60 |
TABLE B
| SB_ID | G_ID | YEAR | PERIOD |
| 1 | 1 | 2022 | SECOND |
| 2 | 1 | 2022 | THIRD |
| 3 | 1 | 2021 | FIRST |
위와 같은 테이블을 사용중이고, B.G_ID를 검색조건으로 선택하여 데이터를 표출하도록 개발중입니다.
다만 저 위에서, B.PERIOD가 코드값으로 관리되고 있습니다.
해당 코드는 아래의 테이블 형태로 표출되고 있습니다.
| CODE | DATA |
| FIRST | 1차 |
| SECOND | 2차 |
| THIRD | 3차 |
위와 같은 형태입니다.
최종적으로 B.G_ID와 B.YEAR를 선택하여 데이터를 표출하는데 아래와 같은 형태로 표출되는게 최종목표입니다.
조건 : B.G_ID = 1 AND YEAR IN (2020, 2021, 2022)
| YEAR | PERIOD | AVG_Q15(평균값) |
| 2021 | FIRST | 70 |
| 2021 | SECOND | 0 |
| 2021 | THIRD | 0 |
| 2022 | FIRST | 0 |
| 2022 | SECOND | 80 |
| 2022 | THIRD | 65 |
즉, 테이블 B에는 2022년도의 PERIOD는 SECOND, THIRD만 존재하고 2021년도의 PERIOD는 FIRST만 존재하고, 2020년도의 PERIOD는 한건도 없기 때문에
표출형태는 코드로 관리중인 코드테이블의 CODE와 매칭되어 B테이블에 없는 데이터도 같이 표출되었으면 하는게 최종목표입니다.
지금 없는데이터를 빼고 표출하는 것은 성공했는데, 없는 데이터를 0으로 표출하는 ROW를 만드는것을 전혀 못하겠습니다..
어떻게 해야할지 방법을 제시해주시면 감사드리겠습니다.
WITH table_a AS
(
SELECT 1 sa_id, 1 sb_id, 80 q15
UNION ALL SELECT 2, 2, 60
UNION ALL SELECT 3, 2, 70
UNION ALL SELECT 4, 3, 80
UNION ALL SELECT 5, 3, 60
)
, table_b AS
(
SELECT 1 sb_id, 1 g_id, 2022 year, 'SECOND' period
UNION ALL SELECT 2, 1, 2022, 'THIRD'
UNION ALL SELECT 3, 1, 2021, 'FIRST'
)
, table_c AS
(
SELECT 'FIRST' code, '1차' data
UNION ALL SELECT 'SECOND', '2차'
UNION ALL SELECT 'THIRD' , '3차'
)
SELECT b.year
, c.code
, c.data
, IFNULL(a.q15, 0) q15
FROM (SELECT DISTINCT year
FROM table_b
WHERE g_id = 1
AND year IN (2020, 2021, 2022)
) b
CROSS JOIN table_c c
LEFT OUTER JOIN
(SELECT b.year
, b.period
, ROUND(AVG(a.q15), 1) q15
FROM table_b b
INNER JOIN table_a a
WHERE b.sb_id = a.sb_id
AND b.g_id = 1
AND b.year IN (2020, 2021, 2022)
GROUP BY b.year, b.period
) a
ON b.year = a.year
AND c.code = a.period
ORDER BY b.year, c.code
;
와 정말 감사합니다.
깔끔하게 잘 돌아가네요...
저번에도 CROSS JOIN에 대해서 나왔었는데 이번에도 써야 하는군요.
조인에 대해서 더 공부해야겠습니다.
깔끔하게 해결해주셔서 감사합니다.
WITH TABLE_A AS (
SELECT '1' AS SA_ID, '1' AS SB_ID, 80 AS Q15 UNION ALL
SELECT '2' AS SA_ID, '2' AS SB_ID, 60 AS Q15 UNION ALL
SELECT '3' AS SA_ID, '2' AS SB_ID, 70 AS Q15 UNION ALL
SELECT '4' AS SA_ID, '3' AS SB_ID, 80 AS Q15 UNION ALL
SELECT '5' AS SA_ID, '3' AS SB_ID, 60 AS Q15
)
, TABLE_B AS (
SELECT '1' AS SB_ID, '1' AS G_ID, '2022' AS YEAR, 'SECOND' AS PERIOD UNION ALL
SELECT '2' AS SB_ID, '1' AS G_ID, '2022' AS YEAR, 'THIRD' AS PERIOD UNION ALL
SELECT '3' AS SB_ID, '1' AS G_ID, '2021' AS YEAR, 'FIRST' AS PERIOD
)
, TABLE_CODE AS (
SELECT 'FIRST' AS CODE, '1차' AS DATA UNION ALL
SELECT 'SECOND' AS CODE, '2차' AS DATA UNION ALL
SELECT 'THIRD' AS CODE, '3차' AS DATA
)
SELECT T11.YEAR, T11.PERIOD, IFNULL(T12.AVG_DATA,0) AS AVG_DATA
FROM (
SELECT T1.YEAR, T2.CODE AS PERIOD, T2.DATA
FROM (SELECT DISTINCT YEAR FROM TABLE_B ) T1
CROSS JOIN TABLE_CODE T2
WHERE T1.YEAR IN ('2020','2021','2022') -- 조건 입력부
) T11
LEFT OUTER JOIN (
SELECT T2.YEAR, T2.PERIOD, AVG(T1.Q15) AS AVG_DATA
FROM TABLE_A T1
INNER JOIN TABLE_B T2
ON T1.SB_ID = T2.SB_ID
GROUP BY T2.YEAR, T2.PERIOD
) T12
ON T11.YEAR = T12.YEAR
AND T11.PERIOD = T12.PERIOD
ORDER BY T11.YEAR, T11.PERIOD
저도 쿼리 작성 해보았습니다.
잘 된 예가 있지만... ㅎㅎ
정말 감사합니다.
쿼리문이 위에랑 다른거 같은데도 결과가 같게 나오네요 신기..
핵심이 크로스조인과 아우터조인인거같습니다.
덕분에 좀더 이해하기 쉬웠습니다. 정말 감사합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.