소스: sql-src/adv_02_window_agg/01_running_total.sql, 02_frame.sql. 01 은 직원(emp) 6명과 주문(orders) 8건, 02 는 같은 날짜 주문이 2건 있는 주문 6건을 씁니다.
SELECT
id
, ord_date
, amt
, SUM(amt) OVER (ORDER BY ord_date) AS running_total
FROM orders
ORDER BY ord_date;ID | ORD_DATE | AMT | RUNNING_TOTAL
---+------------+-----+--------------
1 | 2024-01-05 | 300 | 300
2 | 2024-01-08 | 150 | 450
3 | 2024-01-15 | 500 | 950
4 | 2024-01-20 | 250 | 1200
5 | 2024-02-05 | 100 | 1300
6 | 2024-02-10 | 400 | 1700
7 | 2024-02-18 | 200 | 1900
8 | 2024-03-01 | 350 | 2250
(8행)행은 그대로 8개이고, RUNNING_TOTAL 이 첫 행부터 현재 행까지의 합입니다. 이 데이터는 날짜가 모두 달라서 기본 프레임(RANGE)이어도 행 단위 누적과 같습니다. 같은 날짜가 있을 때의 함정은 4절 변형 1 에서 봅니다.
SELECT
emp_id
, ord_date
, amt
, SUM(amt) OVER (PARTITION BY emp_id ORDER BY ord_date) AS emp_running
FROM orders
ORDER BY emp_id, ord_date;EMP_ID | ORD_DATE | AMT | EMP_RUNNING
-------+------------+-----+------------
2 | 2024-01-05 | 300 | 300
2 | 2024-01-15 | 500 | 800
2 | 2024-02-05 | 100 | 900
2 | 2024-03-01 | 350 | 1250
3 | 2024-01-08 | 150 | 150
3 | 2024-02-10 | 400 | 550
5 | 2024-01-20 | 250 | 250
5 | 2024-02-18 | 200 | 450
(8행)PARTITION BY emp_id 가 직원마다 누적을 새로 시작하게 합니다. 직원 3 의 첫 행은 150 으로 다시 시작하고, 직원 5 도 250 부터 시작합니다.
SELECT
id
, amt
, SUM(amt) OVER () AS total_amt
, ROUND(amt * 100.0 / SUM(amt) OVER (), 1) AS pct
FROM orders
ORDER BY id;ID | AMT | TOTAL_AMT | PCT
---+-----+-----------+-----
1 | 300 | 2250 | 13.3
2 | 150 | 2250 | 6.7
3 | 500 | 2250 | 22.2
4 | 250 | 2250 | 11.1
5 | 100 | 2250 | 4.4
6 | 400 | 2250 | 17.8
7 | 200 | 2250 | 8.9
8 | 350 | 2250 | 15.6
(8행)SUM(amt) OVER () 는 모든 행에 전체 합계 2250 을 붙입니다. 서브쿼리 (SELECT SUM(amt) FROM orders) 와 같은 값이지만, 같은 테이블을 한 번만 읽는다는 점이 다릅니다.
주의
amt가 INT 이므로amt * 100 / SUM(amt) OVER ()처럼 정수끼리 나누면 MSSQL 에서 소수점이 잘립니다.100.0을 곱해 소수로 만드는 습관이 필요합니다. H2 는 모든 모드에서 정수 나눗셈을 하므로, 이 차이는 H2 로 재현되지 않습니다.
SELECT
name
, dept
, sal
, AVG(sal) OVER (PARTITION BY dept) AS dept_avg
, sal - AVG(sal) OVER (PARTITION BY dept) AS diff
FROM emp
ORDER BY dept, sal DESC;NAME | DEPT | SAL | DEPT_AVG | DIFF
-------+------+------+----------+-------
이부장 | 개발 | 800 | 700.0 | 100.0
박과장 | 개발 | 700 | 700.0 | 0.0
최대리 | 개발 | 600 | 700.0 | -100.0
김대표 | 경영 | 1000 | 1000.0 | 0.0
정사원 | 영업 | 500 | 490.0 | 10.0
강사원 | 영업 | 480 | 490.0 | -10.0
(6행)OVER (PARTITION BY dept) 에 ORDER BY 가 없어서 부서 전체가 프레임입니다. 그래서 개발팀 세 명 모두 평균 700 이 붙고, 각자의 급여에서 평균을 뺀 값이 DIFF 입니다.
SELECT
id
, ord_date
, amt
, AVG(amt) OVER (ORDER BY ord_date, id
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg3
FROM orders
ORDER BY ord_date, id;ID | ORD_DATE | AMT | MOVING_AVG3
---+------------+-----+------------
1 | 2024-01-05 | 100 | 100.0
2 | 2024-01-06 | 200 | 150.0
3 | 2024-01-07 | 300 | 200.0
4 | 2024-01-07 | 400 | 300.0
5 | 2024-01-08 | 500 | 400.0
6 | 2024-01-09 | 600 | 500.0
(6행)2 PRECEDING AND CURRENT ROW 는 현재 행과 앞의 2행, 최대 3행이 프레임입니다. 처음 두 행은 앞 행이 모자라 1행, 2행으로 평균을 냅니다(100, 150).
정확히 3건 평균만 원하면 바깥에서 ROW_NUMBER() 로 앞의 두 행을 걸러냅니다.