-- 월별 누적 합계 쿼리
WITH MON_T AS ( -- 기준 월
SELECT 1 AS MON FROM DUAL UNION ALL
SELECT 2 AS MON FROM DUAL UNION ALL
SELECT 3 AS MON FROM DUAL UNION ALL
SELECT 4 AS MON FROM DUAL UNION ALL
SELECT 5 AS MON FROM DUAL UNION ALL
SELECT 6 AS MON FROM DUAL UNION ALL
SELECT 7 AS MON FROM DUAL UNION ALL
SELECT 8 AS MON FROM DUAL UNION ALL
SELECT 9 AS MON FROM DUAL UNION ALL
SELECT 10 AS MON FROM DUAL UNION ALL
SELECT 11 AS MON FROM DUAL UNION ALL
SELECT 12 AS MON FROM DUAL
),
DATA_T AS ( -- 데이터 테이블
SELECT 1 AS MON, 11 AS D_CNT FROM DUAL UNION ALL
SELECT 2 AS MON, 12 AS D_CNT FROM DUAL UNION ALL
SELECT 3 AS MON, 13 AS D_CNT FROM DUAL UNION ALL
SELECT 4 AS MON, 14 AS D_CNT FROM DUAL UNION ALL
SELECT 5 AS MON, 15 AS D_CNT FROM DUAL UNION ALL
SELECT 6 AS MON, 16 AS D_CNT FROM DUAL UNION ALL
SELECT 7 AS MON, 17 AS D_CNT FROM DUAL UNION ALL
SELECT 8 AS MON, 18 AS D_CNT FROM DUAL UNION ALL
SELECT 9 AS MON, 19 AS D_CNT FROM DUAL UNION ALL
SELECT 10 AS MON, 20 AS D_CNT FROM DUAL UNION ALL
SELECT 11 AS MON, 21 AS D_CNT FROM DUAL UNION ALL
SELECT 12 AS MON, 22 AS D_CNT FROM DUAL
)
SELECT
M.MON
, D.MON AS MON2
, D.D_CNT
FROM MON_T M
, DATA_T D
WHERE D.MON BETWEEN 1 AND M.MON
ORDER BY M.MON, D.MON
-- WITH 를 이용한 월별 데이터(누적x) 집계 쿼리
-- 기준월(1월 ~ 12월)
WITH MON_T AS (
SELECT 1 AS MON FROM DUAL UNION ALL
SELECT 2 AS MON FROM DUAL UNION ALL
SELECT 3 AS MON FROM DUAL UNION ALL
SELECT 4 AS MON FROM DUAL UNION ALL
SELECT 5 AS MON FROM DUAL UNION ALL
SELECT 6 AS MON FROM DUAL UNION ALL
SELECT 7 AS MON FROM DUAL UNION ALL
SELECT 8 AS MON FROM DUAL UNION ALL
SELECT 9 AS MON FROM DUAL UNION ALL
SELECT 10 AS MON FROM DUAL UNION ALL
SELECT 11 AS MON FROM DUAL UNION ALL
SELECT 12 AS MON FROM DUAL
),
DATA_SAMPLE1 AS (
SELECT
TO_CHAR(TO_DATE(날짜컬럼), 'MM') AS MON,
COL_A,
COL_B,
-----------------------
--- 기존 select 쿼리
-----------------------
FROM
-- 날짜별 조건 추가
AND 날짜컬럼 BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD') AND TO_DATE('2023-12-31', 'YYYY-MM-DD') -- /* 1월 ~ 12월 조건 */
GROUP BY TO_CHAR(TO_DATE(날짜컬럼), 'MM'), COL_A, COL_B -- 날짜값 형반환 주의
ORDER BY MON-- /* 1월 ~ 12월 정렬 */
)
SELECT
MT.MON || '월' AS MON /* 기준월 */
, D1.COL_A
, D2.COL_A
..
FROM MON_T MT -- 기준월을 기준으로
LEFT OUTER JOIN DATA_SAMPLE1 D1 ON D1.MON = MT.MON -- left join으로 데이터가 존재하는 row만 보이도록 처리
... 기타 join 조건 추가 ...
WHERE 1=1
-- 날짜 조건 param 값으로 처리
AND MT.MON BETWEEN TO_NUMBER(SUBSTR(#{s_bill_st_dt},5,2)) AND TO_NUMBER(SUBSTR(#{s_bill_end_dt},5,2))
GROUP BY MT.MON -- 월별
, D1.COL_A -- select 절에 가져오는 컬럼은 group by 에 모두 기입되어야함.
, D2.COL_A
...
ORDER BY MT.MON -- 월별(날짜순서)
