1. Union활용
각기 다른 테이블 두개
20250701~...과 userid
실적 숫자 cnt1, cnt2가 있는 테이블일 때
두 테이블의 년,월 별 합계를 그룹화하여 출력하려고 했을 때
개념 각 테이블을 그룹화 하여, 조인한다
SELECT
NVL(a.userid, b.userid) AS userid,
NVL(a.yyyymm, b.yyyymm) AS yyyymm,
NVL(a.sum_cnt1, 0) AS test1_sum_cnt1,
NVL(a.sum_cnt2, 0) AS test1_sum_cnt2,
NVL(b.sum_cnt1, 0) AS test2_sum_cnt1,
NVL(b.sum_cnt2, 0) AS test2_sum_cnt2
FROM (
SELECT
userid,
SUBSTR(yyyymmdd, 1, 6) AS yyyymm,
SUM(cnt1) AS sum_cnt1,
SUM(cnt2) AS sum_cnt2
FROM test1
WHERE yyyymmdd BETWEEN '20240701' AND '20240731'
GROUP BY userid, SUBSTR(yyyymmdd, 1, 6)
) a
FULL OUTER JOIN (
SELECT
userid,
SUBSTR(yyyymmdd, 1, 6) AS yyyymm,
SUM(cnt1) AS sum_cnt1,
SUM(cnt2) AS sum_cnt2
FROM test2
WHERE yyyymmdd BETWEEN '20240701' AND '20240731'
GROUP BY userid, SUBSTR(yyyymmdd, 1, 6)
) b
ON a.userid = b.userid
AND a.yyyymm = b.yyyymm
ORDER BY userid, yyyymm;
괄호에 현혹되지 않고 간단히 생각한다(중요)
select * from a join b a.userid = b.userid
