시간 윈도우 롤링 - 누적값 계산

누적값 계산

• 누적값을 계산하는 쿼리이다.
• 연도별로 누적 매출 합계를 구한다.

-- JOIN 이용
SELECT
	A.sales_month,
	A.sales,
	SUM(B.sales) AS sales_ytd
FROM retail_sales AS A JOIN retail_sales AS B
	ON DATE_PART('year', A.sales_month) = DATE_PART('year', B.sales_month)
		AND B.sales_month <= A.sales_month
		AND B.kind_of_business = 'Women''s clothing stores'
WHERE A.kind_of_business = 'Women''s clothing stores'
GROUP BY A.sales_month, A.sales

• 위 쿼리보다 더 간단하게 같은 결과를 도출해낼 수 있다.

SELECT
	sales_month,
	sales,
	SUM(sales) OVER(PARTITION BY DATE_PART('year', sales_month) ORDER BY sales_month) AS sales_ytd
FROM retail_sales
WHERE kind_of_business = 'Women''s clothing stores'