[SQL] window 함수 frame 절 예제

0. 목표

 - window 함수 frame 절을 예제를 통해 학습

1. 실습

 1) 테이블 생성

CREATE TABLE sql_test.frame_test
(
	id char,
    ordered int,
    price int
);

 2) 데이터 삽입

INSERT INTO sql_test.frame_test(id, ordered, price) VALUES('a', 1, 100);
INSERT INTO sql_test.frame_test(id, ordered, price) VALUES('a', 2, 200);
INSERT INTO sql_test.frame_test(id, ordered, price) VALUES('a', 3, 300);
INSERT INTO sql_test.frame_test(id, ordered, price) VALUES('a', 4, 400);
INSERT INTO sql_test.frame_test(id, ordered, price) VALUES('a', 5, 500);
INSERT INTO sql_test.frame_test(id, ordered, price) VALUES('a', 6, 600);
INSERT INTO sql_test.frame_test(id, ordered, price) VALUES('b', 1, 100);
INSERT INTO sql_test.frame_test(id, ordered, price) VALUES('b', 2, 200);
INSERT INTO sql_test.frame_test(id, ordered, price) VALUES('b', 3, 300);
INSERT INTO sql_test.frame_test(id, ordered, price) VALUES('b', 4, 400);

 3) 예제를 통한 학습

  (1) R1 : 처음부터 해당 로우까지 합계

  (2) R2 : 처음부터 해당 로우까지 합계

  (3) R3 : 앞의 1 로우까지만의 합계, 처음은 앞이 없으니 100만... 그 다음은 100 + 200, 그 다음은 200 + 300

  (4) R4 : 앞의 2 로우까지 합계, 처음은 100, 그 다음은 100 + 200, 그 다음은 100 + 200 + 300, 다음은 200 + 300 + 400

  (5) R5 : 앞의 3 로우까지 합계, 100, 100 + 200, 100 + 200 + 300, 100 + 200 + 300 + 400, ...

  (6) R6 : 앞의 1 로우에서 뒤의 1 로우까지의 합계, 100 + 200, 100 + 200 + 300, 200 + 300 + 400 ...

  (7) R7 : 앞의 1 로우에서 뒤의 2 로우까지의 합계, 100 + 200 + 300, 100 + 200 + 300 + 400 ...

  (8) R8 : 앞의 2 로우에서 뒤의 2 로우까지의 합계, 100 + 200 + 300, 100 + 200 + 300 + 400, 100 + 200 + 300 + 400 + 500 ...  

  (9) R9 : 현재 로우에서 뒤의 2 로우까지의 합계, 100 + 200 + 300, 200 + 300 + 400 ...

  (10) R10 : 현재 로우에서 뒤의 2 로우까지의 합계, 100 + 200 + 300, 200 + 300 + 400 ...

SELECT
	id,
    ordered,
    price,
    SUM(price) OVER(PARTITION BY id ORDER BY ordered ASC ROWS UNBOUNDED PRECEDING) AS R1,
    SUM(price) OVER(PARTITION BY id ORDER BY ordered ASC) AS R2,
    SUM(price) OVER(PARTITION BY id ORDER BY ordered ASC ROWS 1 PRECEDING) AS R3,
    SUM(price) OVER(PARTITION BY id ORDER BY ordered ASC ROWS 2 PRECEDING) AS R4,
    SUM(price) OVER(PARTITION BY id ORDER BY ordered ASC ROWS 3 PRECEDING) AS R5,
    SUM(price) OVER(PARTITION BY id ORDER BY ordered ASC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS R6,
    SUM(price) OVER(PARTITION BY id ORDER BY ordered ASC ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) AS R7,
    SUM(price) OVER(PARTITION BY id ORDER BY ordered ASC ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING) AS R8,
    SUM(price) OVER(PARTITION BY id ORDER BY ordered ASC ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS R9,
    SUM(price) OVER(PARTITION BY id ORDER BY ordered ASC ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS R9,
    SUM(price) OVER(PARTITION BY id ORDER BY ordered ASC RANGE BETWEEN CURRENT ROW AND 2 FOLLOWING) AS R10
FROM sql_test.frame_test;

 4) 다양한 윈도우 함수 사용

  (1) R1 : 첫 로우부터 현재 로우 까지 중 첫 번째 값

  (2) R2 : 첫 로우부터 현재 로우 까지의 합계

  (3) R3 : 첫 로우부터 현재 로우 까지 중 2 번째 값

SELECT
	id,
	FIRST_VALUE(price) OVER(PARTITION BY id ORDER BY ordered ASC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS R1,
    SUM(price) OVER(PARTITION BY id ORDER BY ordered ASC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS R2,
    NTH_VALUE(price, 2) OVER(PARTITION BY id ORDER BY ordered ASC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS R3
FROM sql_test.frame_test;

 5) 4) 를 간단하게 표현

  - OVER 절을 w 로 묶어서 뒤로 보내버림

SELECT
	id,
	FIRST_VALUE(price) OVER w AS R1,
    SUM(price) OVER w AS R2,
    NTH_VALUE(price, 2) OVER w AS R3
FROM sql_test.frame_test
WINDOW w AS (PARTITION BY id ORDER BY ordered ASC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);

2. 참고

 - WINDOW 함수 Frame절 (MySQL 8.0.11)