소스: sql-src/work_04_period_report/02_yoy_target.sql, 03_dialects.sql.
월별로 집계하면 2024년은 3월이 빠진 11개 행이고 2025년은 6개 행입니다(소스 02_yoy_target.sql 의 1번). 이 빈 달이 아래 결과의 원인입니다. 두 방식을 같은 월별 표(m)에서 돌립니다. 먼저 LAG(amt, 12) 입니다.
WITH m AS (
SELECT
EXTRACT(YEAR FROM ord_date) AS yr
, EXTRACT(MONTH FROM ord_date) AS mo
, SUM(amt) AS amt
FROM orders
GROUP BY EXTRACT(YEAR FROM ord_date), EXTRACT(MONTH FROM ord_date)
)
SELECT
yr
, mo
, amt
, prev_lag
FROM (
SELECT
yr
, mo
, amt
, LAG(amt, 12) OVER (ORDER BY yr, mo) AS prev_lag
FROM m
) t
WHERE yr = 2025
ORDER BY mo;YR | MO | AMT | PREV_LAG
-----+----+------+---------
2025 | 1 | 1050 | NULL
2025 | 2 | 900 | 800
2025 | 3 | 1150 | 750
2025 | 4 | 950 | 850
2025 | 5 | 1200 | 900
2025 | 6 | 1800 | 1500
(6행)값이 모두 틀렸습니다. 2025-01 의 전년 동월은 800(2024-01)인데 NULL 이고, 2025-02 는 750(2024-02)이어야 하는데 800 입니다. 3월 이후도 전부 한 달 앞의 값입니다. 3월 행이 없어 12행 전이 11개월 전을 가리키기 때문입니다.
이번에는 연-1 자기 조인입니다.
WITH m AS (
SELECT
EXTRACT(YEAR FROM ord_date) AS yr
, EXTRACT(MONTH FROM ord_date) AS mo
, SUM(amt) AS amt
FROM orders
GROUP BY EXTRACT(YEAR FROM ord_date), EXTRACT(MONTH FROM ord_date)
)
SELECT
c.yr
, c.mo
, c.amt
, p.amt AS prev_amt
FROM m c
LEFT JOIN m p ON p.yr = c.yr - 1
AND p.mo = c.mo
WHERE c.yr = 2025
ORDER BY c.mo;YR | MO | AMT | PREV_AMT
-----+----+------+---------
2025 | 1 | 1050 | 800
2025 | 2 | 900 | 750
2025 | 3 | 1150 | NULL
2025 | 4 | 950 | 850
2025 | 5 | 1200 | 900
2025 | 6 | 1800 | 1500
(6행)전 행이 맞고, 전년에 주문이 없던 3월은 NULL 입니다. 조인이 짝을 찾는 기준이 "행 위치" 가 아니라 "연도와 월 값" 이기 때문입니다.
주의
LAG(x, 12)는 12개월 전이 아니라 12행 전입니다. 빈 달이 하나라도 있으면 값이 조용히 밀리므로 연-1 조인을 기본으로 씁니다.
조인 방식에 증감액과 증감률을 붙입니다. NULLIF 로 분모 0 을 막고 * 100.0 으로 소수를 살립니다.
WITH m AS (
SELECT
EXTRACT(YEAR FROM ord_date) AS yr
, EXTRACT(MONTH FROM ord_date) AS mo
, SUM(amt) AS amt
FROM orders
GROUP BY EXTRACT(YEAR FROM ord_date), EXTRACT(MONTH FROM ord_date)
)
SELECT
c.mo
, c.amt
, p.amt AS prev_amt
, c.amt - p.amt AS diff
, ROUND((c.amt - p.amt) * 100.0 / NULLIF(p.amt, 0), 1) AS yoy_pct
FROM m c
LEFT JOIN m p ON p.yr = c.yr - 1
AND p.mo = c.mo
WHERE c.yr = 2025
ORDER BY c.mo;MO | AMT | PREV_AMT | DIFF | YOY_PCT
---+------+----------+------+--------
1 | 1050 | 800 | 250 | 31.3
2 | 900 | 750 | 150 | 20.0
3 | 1150 | NULL | NULL | NULL
4 | 950 | 850 | 100 | 11.8
5 | 1200 | 900 | 300 | 33.3
6 | 1800 | 1500 | 300 | 20.0
(6행)3월은 전년 자료가 없어 증감률도 NULL 입니다. 보고서에는 0 이 아니라 "-" 나 "해당 없음" 으로 표시합니다. 0% 로 보이면 "변동 없음" 과 혼동됩니다.
LAG 를 쓰려면 중급 10 에서 본 달력 조인으로 빈 달을 0 으로 채운 뒤 돌립니다(소스 02 의 6번). 월 첫날 18개를 재귀 CTE 로 만들어 LEFT JOIN 하면 3월도 행이 생겨 12행 전이 12개월 전이 되고, 결과는 조인 방식과 같은 값입니다.
다만 2025-03 의 전년 값이 NULL 이 아니라 0 이 되므로 증감률 분모에 NULLIF(prev, 0) 가 꼭 필요합니다. H2 에서는 CTE 뒤에 컬럼 목록 m(ms, amt) 를 적어야 실행되고, 재귀 CTE 표기는 MySQL 8.0·H2 는 WITH RECURSIVE, MSSQL·Oracle 은 RECURSIVE 없이 씁니다.
월 실적에 목표 표 sales_target 을 조인하고, 연도별로 누적 실적과 누적 목표를 구합니다.
WITH m AS (
SELECT
TO_CHAR(ord_date, 'YYYY-MM') AS ym
, EXTRACT(YEAR FROM ord_date) AS yr
, SUM(amt) AS amt
FROM orders
GROUP BY TO_CHAR(ord_date, 'YYYY-MM'), EXTRACT(YEAR FROM ord_date)
),
r AS (
SELECT
m.ym
, m.amt
, t.target_amt
, SUM(m.amt) OVER (PARTITION BY m.yr ORDER BY m.ym
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_amt
, SUM(t.target_amt) OVER (PARTITION BY m.yr ORDER BY m.ym
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_target
FROM m
JOIN sales_target t ON t.ym = m.ym
)
SELECT
ym
, amt
, target_amt
, ROUND(amt * 100.0 / target_amt, 1) AS rate_pct
, cum_amt
, cum_target
, ROUND(cum_amt * 100.0 / cum_target, 1) AS cum_rate_pct
FROM r
ORDER BY ym;YM | AMT | TARGET_AMT | RATE_PCT | CUM_AMT | CUM_TARGET | CUM_RATE_PCT
--------+------+------------+----------+---------+------------+-------------
2025-01 | 1050 | 1000 | 105.0 | 1050 | 1000 | 105.0
2025-02 | 900 | 1000 | 90.0 | 1950 | 2000 | 97.5
2025-03 | 1150 | 1200 | 95.8 | 3100 | 3200 | 96.9
2025-04 | 950 | 1000 | 95.0 | 4050 | 4200 | 96.4
2025-05 | 1200 | 1200 | 100.0 | 5250 | 5400 | 97.2
2025-06 | 1800 | 1800 | 100.0 | 7050 | 7200 | 97.9
(6행)1월만 목표를 넘었고 2월부터 미달이지만 누적 달성률은 96~98% 에서 안정적입니다. 윈도 함수는 집계 결과 위에서 돌아야 하므로 월 집계 CTE(m)를 만든 뒤 조인하고 누적을 계산합니다. 프레임을 ROWS 로 명시해 동점 정렬 값에도 행 단위로 누적합니다. 목표가 없는 달이 있으면 JOIN 이 그 달을 버리므로 LEFT JOIN 으로 바꾸고 달성률의 분모는 NULLIF 로 감쌉니다.
소스 03_dialects.sql 은 5개 주문(2024-12-29 ~ 2025-01-20)에 SET MODE 별 월 버킷을 돌립니다. Oracle 모드의 TO_CHAR 로 월과 ISO 연도·주를 만들어 묶은 결과입니다.
-- Oracle · Tibero
SELECT
TO_CHAR(ord_date, 'YYYY-MM') AS ym
, TO_CHAR(ord_date, 'IYYY') AS iso_yr
, TO_CHAR(ord_date, 'IW') AS iso_wk
, SUM(amt) AS amt
FROM orders
GROUP BY TO_CHAR(ord_date, 'YYYY-MM'), TO_CHAR(ord_date, 'IYYY'), TO_CHAR(ord_date, 'IW')
ORDER BY ym, iso_wk;YM | ISO_YR | ISO_WK | AMT
--------+--------+--------+----
2024-12 | 2025 | 01 | 550
2024-12 | 2024 | 52 | 700
2025-01 | 2025 | 01 | 450
2025-01 | 2025 | 04 | 600
(4행)MySQL·MSSQL 모드에서는 YEAR(d)·MONTH(d) 만 H2 에서 실행되고 결과는 2024-12 가 1250, 2025-01 이 1050 으로 같습니다. 나머지 함수는 H2 미지원이라 문법 검토만 합니다.
-- MySQL (H2 미지원, 문법 검토만)
SELECT DATE_FORMAT(ord_date, '%Y-%m') AS ym, WEEK(ord_date, 3) AS iso_wk, SUM(amt) AS amt
FROM orders
GROUP BY DATE_FORMAT(ord_date, '%Y-%m'), WEEK(ord_date, 3);
-- MSSQL (H2 미지원, 문법 검토만)
SELECT CONVERT(CHAR(7), ord_date, 120) AS ym, DATEPART(ISO_WEEK, ord_date) AS iso_wk, SUM(amt) AS amt
FROM orders
GROUP BY CONVERT(CHAR(7), ord_date, 120), DATEPART(ISO_WEEK, ord_date);Oracle 의 TRUNC(d, 'MM')(월 첫날)과 TRUNC(d, 'IW')(주 시작 월요일)도 H2 에서는 재현되지 않아 문법 검토만 합니다. 이 세 방언의 월 첫날은 실무에서 날짜 타입 버킷이 필요할 때 문자 대신 씁니다.