[PostgreSQL] 팬 차트로 매출 증가율 확인(FIRST VALUE 함수)

팬차트란?

팬차트 : 어떤 기준 시점을 100%로 두고, 이후의 숫자 변동을 확인할 수 있게 하는 그래프이다.

계절 변동이 적은 평균적인 달을 기준으로 하는 것이 좋다. 기준 달에 따라 결과가 달라지므로 근거를 잘 생각하고 작성한다.

 

팬차트 작성을 위해 필요한 데이터를 가공해보자.

 

 

데이터가 다음과 같이 정리되어야 팬차트를 만들 수 있다.

원본 데이터

결과 데이터

---

 

1. 먼저 날짜에서 연/월/일을 추출하고 일자별 매출을 합산한다.
2. 일자별 매출을 토대로, 연/월을 기준으로 카테고리별 매출을 다시 합산한다.
3. 윈도우 함수로, FIRST VALUE를 사용하여 기준 연/월, 카테고리별 매출 값을 구한다.
4. 기준점 대비 비율을 구하기 위해 100 * 연/월 매출 / 기준점 매출을 계산한다.
5. 마지막으로, 연/월과 카테고리로 order by 한다.

WITH datily_category_amount AS (
	SELECT dt
		, category
		, substring(dt, 1, 4) AS year
		, substring(dt, 6, 2) AS month
		, substring(dt, 9, 2) AS date
		, sum(price) AS amount
	FROM purchase_detail_log 
	GROUP BY dt, category
),
monthly_category_amount AS (
	SELECT concat(YEAR, '-', month) AS year_month
		, category
		, sum(amount) AS amount
	FROM datily_category_amount
	GROUP BY YEAR, MONTH, category
)
SELECT year_month
	, category
	, amount
	, first_value(amount) OVER (PARTITION BY category ORDER BY year_month, category ROWS UNBOUNDED PRECEDING) AS base_amount
	, 100.0 * amount / first_value(amount) OVER (PARTITION BY category ORDER BY year_month, category ROWS UNBOUNDED PRECEDING) AS rate
FROM monthly_category_amount
ORDER BY 1, 2
;

 

결과 일부