Redshift : [window function] SUM() OVER BETWEEN ~ AND ~ : 여러 row 값을 참조하여 특정 조건에 따라 합계 구하기

 

 

SUM() OVER BETWEEN ~ AND ~

Syntax

SUM() OVER(PARTITION BY column_name ORDER BY column_name ASC/DESC ROWS/RANGE BEWEEEN option_1 AND option_2)

위 Syntax는 특정 조건에 따라 SUM 함수를 실행할 수 있도록 OVER() 조건을 추가한 Syntax입니다.
이렇게 OVER() 절을 사용할 수 있는 함수를 Windowing Clause라고 합니다.
SQL Reference에선 이러한 Windowing Clause를 사용할 수 있는 함수는 *표시가 붙어있습니다.

위에서 명시된 option_1과 option_2는 아래 이미지처럼 여러 가지 종류가 있습니다.

좀 복잡하죠.
몇가지만 살펴보겠습니다.

1. ROW, RANGE
ROW는 다음에 나올 BETWEEN ~ AND ~에서 현재 row 기준 앞뒤로 몇 개의 row를 참조하여 SUM 함수등의 함수를 적용할지를 나타냅니다.
RANGE는 다음에 나올 BETWEEN ~ AND ~에서 현재 row의 값 기준으로 얼마만큼의 값 범위인 값들을 참조하여 SUM 함수 등의 함수를 적용할지를 나타냅니다.

2. UNBOUNDED PRECEDING
ROW 옵션을 명시했을 때 BETWEEN ~ AND ~ 절에 UNBOUNDED PRECEDING을 명시하면 현재 row 기준으로 현재 row보다 더 위쪽에 있는 모든 row를 참조한다는 의미입니다.

3. UNBOUNDED FOLLOWING
ROW 옵션을 명시했을 때 BETWEEN ~ AND ~ 절에 UNBOUNDED FOLLOWING을 명시하면 현재 row 기준으로 현재 row보다 더 아래쪽에 있는 모든 row를 참조한다는 의미입니다.

4. CURRENT ROW
ROW 옵션을 명시했을 때 BETWEEN a AND b 절의 a 부분에 CURRENT ROW을 명시하면 현재 row를 시작점으로 간주하라는 뜻입니다.
ROW 옵션을 명시했을 때 BETWEEN a AND b 절의 b부분에 CURRENT ROW을 명시하면 현재 row를 끝점으로 간주하라는 뜻입니다.

5. value_expr PRECEDING
ROW 옵션을 명시했을 때 BETWEEN ~ AND ~ 절에 value_expr PRECEDING을 명시하면 현재 row 기준으로 현재 row보다 value_expr만큼 위쪽에 있는 row 까지를 참조한다는 의미입니다.

6. value_expr FOLLOWING
ROW 옵션을 명시했을 때 BETWEEN ~ AND ~ 절에 value_expr FOLLOWING을 명시하면 현재 row 기준으로 현재 row보다 value_expr만큼 아래쪽에 있는 row 까지를 참조한다는 의미입니다.

table name : products

productno	category_no	price
1250	10	10000
3028	10	8000
2075	10	7000
5217	10	12000
4203	20	11000
3356	20	5500
2103	20	3030
4301	20	6040
8043	30	1010
3356	30	5500
9034	30	9040
1234	30	6500

 

SELECT  product_no, category_no, price
        SUM() OVER(PARTITION BY category_no ORDER BY product_no ROWS 1 PRECEDING) AS sum_result
FROM products

 

product_no	category_no	price	sum_result
1250	10	10000	10000
2075	10	7000	17000
3028	10	8000	15000
5217	10	12000	20000
2103	20	3030	3030
3356	20	5500	8530
4203	20	11000	16500
4301	20	6040	17040
1234	30	6500	6500
3356	30	5500	12000
8043	30	1010	6520
9034	30	9040	10050

 

sum_result 컬럼을 보면 현재 row와 이전 row의 price가 더해져서 나타내지는 것을 볼 수 있습니다.
또한 PARTITION BY에 category_no가 명시되어있으므로 동일한 category_no를 가진 row끼리 SUM() OVER() 함수가 적용됩니다.

 

 

SELECT  product_no, category_no, price,
        SUM() OVER(ORDER BY category_no, product_no ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS sum_1,
        SUM() OVER(ORDER BY category_no, product_no ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sum_2,
        SUM() OVER(ORDER BY category_no, product_no ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS sum_3,
        SUM() OVER(ORDER BY category_no, product_no ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS sum_4,
        SUM() OVER(ORDER BY category_no, product_no ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS sum_5
FROM products

 

product_no	category_no	price	sum_1	snm_2	sum_3	sum_4	sum_5
1250	10	10000	84620	10000	84620	10000	17000
2075	10	7000	84620	17000	74620	17000	15000
3028	10	8000	84620	25000	67620	15000	20000
5217	10	12000	84620	37000	59620	20000	15030
2103	20	3030	84620	40030	47620	15030	8530
3356	20	5500	84620	45530	44590	8530	16500
4203	20	11000	84620	56530	39090	16500	17040
4301	20	6040	84620	62570	28090	17040	12540
1234	30	6500	84620	69070	22050	12540	12000
3356	30	5500	84620	74570	15550	12000	6510
8043	30	1010	84620	75580	10050	6510	10050
9034	30	9040	84620	84620	9040	10050	9040

 

sum_1 ~ sum_5까지의 컬럼을 보며 현재 행 기준으로 얼마만큼의 행이 더해졌는지를 잘 살펴보세요.

table name : sales

sale_month	price
201901	10000
201902	8000
201903	7000
201904	12000
201905	11000
201906	5500
201907	3030
201908	6040
201909	1010
201910	5500
201911	9040
201912	6500

 

SELECT  sale_month, price,
        SUM() OVER(ORDER BY TO_DATE(sale_month, 'yyyymm') RANGE BETWEEN INTERVAL '3' MONTH PRECEDING AND INTERVAL '1' MONTH PRECEDING) AS sum_1,
        SUM() OVER(ORDER BY TO_DATE(sale_month, 'yyyymm') RANGE BETWEEN INTERVAL '1' MONTH FOLLOWING AND INTERVAL '3' MONTH FOLLOWING) AS sum_2
FROM sales

 

sale_month	price	sum_1	sum_2
201901	10000		53000
201902	8000	10000	70000
201903	7000	18000	82000
201904	12000	25000	85500
201905	11000	27000	78030
201906	5500	30000	62600
201907	3030	28500	44180
201908	6040	19530	37200
201909	1010	14570	38180
201910	5500	10080	28100
201911	9040	12550	15550
201912	6500	15550

 

1. OVER(ORDER BY TO_DATE(sale_month, 'yyyymm')

RANGE BETWEEN INTERVAL'3' MONTH PRECEDING AND INTERVAL '1' MONTH PRECEDING

위 내용은 sale_month 컬럼 기준으로 오름차순을 하여 sale_month 컬럼의 현재 row에 있는 데이터 기준 3달 이전부터 1달이전까지의 값에 SUM() 함수를 적용시킨다는 뜻입니다.

예를들어 sale_month = 201902면 201811 ~ 201901까지의 데이터를 더한다는 것입니다.

하지만 위 테이블에서 201811, 201812에 대한 데이터가 없기 때문에 201901에 해당하는 price만 더하게 됩니다.

 

2. OVER(ORDER BY TO_DATE(sale_month, 'yyyymm')

RANGE BETWEEN INTERVAL '1' MONTH FOLLOWING AND INTERVAL '3' MONTH FOLLOWING

위 내용은 sale_month 컬럼 기준으로 오름차순을 하여 sale_month 컬럼의 현재 row에 있는 데이터 기준 1달 이후부터 3달 이후까지의 값에 SUM() 함수를 적용시킨다는 뜻입니다.

예를들어 sale_month = 201902면 201903 ~ 2019005까지의 데이터를 더한다는 것입니다.

위 테이블에선 201903, 201904, 201905에 해당하는 price를 더하게 됩니다.

 

 

 

예시에선 계속 sum을 사용했지만 avg 등 다양한 함수에 window function을 적용할 수 있습니다.