[PostgreSQL] 일/주/월/분기별 집계 쿼리를 짜는 방법(+ 없는 일자 채우기)

"주문(orders)" 이라는 명칭의 테이블이 있고 소비자들의 주문량을 일/주/월/분기별로 알아보는 통계 쿼리를 짠다면 아마 아래와 같은 형태로 짜게 될 겁니다.

참고: 쿼리에서 사용되는 order_date(주문일자) 컬럼의 타입은 date 입니다.

일별

select date_trunc('day', order_date)::date as day
	-- , 원하는 집계함수를 작성하세요.
from orders a 
group by date_trunc('day', order_date)::date 

주별

참고: PostgreSQL 에서는 기본적으로 주(week)의 시작을 월요일로 합니다.

1. 월요일부터 시작

select date_trunc('week', order_date)::date as week_from_monday
	--, 원하는 집계함수를 작성하세요.
from orders a 
group by date_trunc('week', order_date)::date 

2. 일요일부터 시작

select date_trunc('week', order_date)::date - 1 as week_from_sunday
	--, 원하는 집계함수를 작성하세요.
from orders a 
group by date_trunc('week', order_date)::date - 1

월별

select date_trunc('month', order_date)::date as month
	-- , 원하는 집계함수를 작성하세요.
from orders a 
group by date_trunc('month', order_date)::date 

분기별

select date_trunc('quarter', order_date)::date as quarter
	--, 원하는 집계함수를 작성하세요.
from orders a 
group by date_trunc('quarter', order_date)::date 

보충: 빠진 일자 채워주기

그런데 만약에 중간중간 데이터가 없는 일자들이 있는 경우에는 일별 통계 에서
해당 날짜가 아예 표시되지 않는 문제가 있습니다.

select date_trunc('day', order_date)::date as day
     , sum(amount) as sum_amount
from nw.orders a
         join nw.order_items b on a.order_id = b.order_id
group by date_trunc('day', order_date)::date
order by 1

• 중간중간에 빠지는 일자가 보이시죠?

비록 데이터가 없는 일자여도 표시가 되게 하고 싶다면 어떻게 할까요?

방법은 generate_series 를 통해서 연속되는 일자를 갖는 dataset 을 생성하고,
앞서 구한 일별 통계 결과물과 함께 left join 하면 됩니다!

with date_series(serial_day) as (
    select generate_series( 
    	'1996-07-01'::date,
    	'1998-12-31'::date, 
    	'1 day'::interval)::date
)
select a.serial_day, sum_amount
from date_series a
left join (
    select date_trunc('day', order_date)::date as day
         , sum(amount) as sum_amount
    from orders
    group by date_trunc('day', order_date)::date
) b on a.serial_day = b.day

• 없는 날짜에 대해서는 null 이 들어가는 걸 확인할 수 있습니다.