[PostgreSQL] 시계열 기반으로 데이터 집계하기

1. 날짜별 매출과 평균 구매액 집계

 

select dt, count(dt) as purchase_count, sum(purchase_amount) as total_amount, 
avg(purchase_amount) as avg_amount
from purchase_log
group by dt
order by dt;

 

2. 날짜별 매출과 7일 이동평균 집계

 

select dt, sum(purchase_amount) as total_amount, 
	avg(sum(purchase_amount)) over(order by dt rows between 6 preceding and current row) as seven_day_avg_amount,
	case 
	when 7 = count(*) over (order by dt rows between 6 preceding and current row)
	then avg(sum(purchase_amount)) over (order by dt rows between 6 preceding and current row)
	end as seven_day_avg_amount_strict
from purchase_log
group by dt;

 

• 최근 최대 7일 동안의 평균, 최근 7일 동안의 평균을 확실히 계산하는 2가지 방식이 있음
• 2번은 데이터가 없으면, 해당 일자를 제외하고 계산

 

3. 날짜별 매출과 당월 누계 매출을 집계

 

select dt, substring(dt,1,7) as year_month, sum(purchase_amount) as total_amount, 
sum(sum(purchase_amount)) over (partition by substring(dt,1,7) order by dt)
from purchase_log
group by dt;

 

• sum을 2번쓰는 이유 : 첫 번째 sum은 각 날짜에 대한 구매 금액의 합계를 구하고, 두 번째 sum은 합계를 년-월로 누적해서 합계를 구한다.
• window function을 사용하면 개별 행마다 value가 생성되기 때문에, group by를 위해 한번 더 묶어주는 것
• partition by로 연-월로 분할

 

4. 날짜별 매출을 임시 테이블로 제작

 

with daily_purchase as(
select dt, substring(dt,1,4) as year, substring(dt,6,2) as month, 
	substring(dt,9,2) as day, count(order_id)
from purchase_log
group by dt
)
select * 
from daily_purchase
order by dt;

 

5. daily_purchase 테이블에 당월 누계 매출을 집계

 

with daily_purchase as(
select dt, substring(dt,1,4) as year, substring(dt,6,2) as month, substring(dt,9,2) as day, 
	count(order_id), sum(purchase_amount) as purchase_amount
	from purchase_log
	group by dt
)

select dt, concat(concat(year,'-'), month) as year_month, purchase_amount,
	sum(purchase_amount) over(partition by year,month order by dt 
    rows between unbounded preceding and current row) as agg_amount
from daily_purchase
order by dt;

 

6. 월별 매출과 작대비를 계산

 

with daily_purchase as(
select dt, substring(dt,1,4) as year, substring(dt,6,2) as month, substring(dt,9,2) as day, 
	count(order_id), sum(purchase_amount) as purchase_amount
	from purchase_log
	group by dt
) select month, 
	sum(case year when '2014' then purchase_amount end) as amount_2014,
	sum(case year when '2015' then purchase_amount end) as amount_2015,
	round(100.0 * sum(case year when '2015' then purchase_amount end) / 
    sum(case year when '2014' then purchase_amount end),2) as rate
from daily_purchase
group by month
order by month;

 

• agg(case column(조건) when 조건 then column(집계) end) : 집계함수와 함께, 조건에 해당하는 column을 계산
• 작대비 : 작년 매출과 비교한 비율(작년 대비 비율)

 

7. Z차트 작성

 

• z차트 : 월차매출, 매출누계, 이동년계 3개의 지표로, 계절 변동의 영향을 배제하고 트렌드를 분석하는 방법
• 월차매출 : 매출 합계를 월별로 집계
• 매출누계 : 해당 월의 매출에 이전월까지의 매출 누계를 합한 값
• 이동년계 : 해당 월의 매출에 과거 11개월의 매출을 합한 값

 

with daily_purchase as(
select dt, substring(dt,1,4) as year, substring(dt,6,2) as month, substring(dt,9,2) as day, 
	count(order_id), sum(purchase_amount) as purchase_amount
	from purchase_log
	group by dt
), monthly_amount as(
select year, month, sum(purchase_amount) as amount
	from daily_purchase
	group by year, month
), calc_index as(
select year, month, amount, 
	sum(case when year = '2015' then amount end)
	over(order by year, month rows unbounded preceding) as agg_amount,
	sum(amount) over(order by year, month rows between 11 preceding and current row) as year_avg_amount
	from monthly_amount
	order by year, month
)

select concat(year,'-',month) as year_month, amount, agg_amount, year_avg_amount
from calc_index
where year = '2015'
order by year_month;

 

• 조건 누적합 : sum() 함수안에 case when으로 조건을 명시하고, then 뒤에 합칠 column을 제시
• 이동합(평균) : 집계함수()와 윈도우 함수를 조합, 집계함수 뒤에 over(order by column rows between 기간 preceding and current row)

 

8. 매출 관련 지표 집계

 

with daily_purchase as(
select dt, substring(dt,1,4) as year, substring(dt,6,2) as month, substring(dt,9,2) as day, 
	count(order_id) as orders, sum(purchase_amount) as amount
	from purchase_log
	group by dt
),
monthly_purchase as(
	select year, month, sum(orders) as orders, avg(amount) as avg_amount, sum(amount) as monthly
	from daily_purchase
	group by year, month
)

select concat(year,'-',month) as year_month, orders, avg_amount, monthly,
	sum(monthly) over(partition by year order by year,month rows between unbounded preceding and current row) as agg_amount,
	lag(monthly,12) over(order by year, month) as last_year,
	100.0 * monthly / lag(monthly,12) over(order by year, month) as rate
from monthly_purchase;

 

• 판매월, 판매횟수, 평균 구매액, 매출액, 누계 매출액, 작년 매출액, 작년비
• lag() 함수를 쓰기 위해서는 over 함수가 필요
• CTE에서 group by와 집계 함수를 활용하면, 쉽게 쿼리를 완성할 수 있음