[sql] date_trunc

postgres에는 date_trunc라는 함수가 있는데, 이 함수를 이용하면 년/월/주/일 별 구매건수와 매출액을 구하는데 용이하다.

 

nw.orders 라는 주문정보 테이블에 'order_date'라는 주문일자 칼럼이 있다고 해보자. 

select date_trunc('day', order_date)
from nw.orders;

 

이때 위 쿼리처럼 date_trunc('day', order_date) 함수를 써주면, order_date의 시분초 단위는 절삭하여 일(day) 단위의 timestamp를 출력해준다.

 

date_trunc 함수를 사용하여 일단위로 절삭했으므로 사실 시분초 단위는 필요없다. 그래서 출력된 timestamp 형식의 데이터를 아래처럼 date 형식으로 변환해주면 좀더 깔끔하게 데이터를 볼 수 있다.

select date_trunc('day', order_date)::date
from nw.orders;

 

위와 같은 형태를 이용하면 아래처럼 일별 상품 주문건수와 매출액을 구할수 있다.

select date_trunc('day', order_date)::date as day,
	count(distinct O.order_id) as ord_cnt,
	sum(I.amount) as sum_amount
from nw.orders O
	left join nw.order_items I using(order_id)
group by date_trunc('day', order_date)::date
order by date_trunc('day', order_date)::date desc
;

 

추가적으로 date_trunc 함수는 'day' 뿐만아니라 'year', 'month', 'week' 따위의 단위를 사용할수 있다. 해당인자를 각각 기입하게되면 주문일자를 각 단위로 절삭하여 표현한다.

 

예를들어 date_trunc('month', order_date)::date 라고 작성한다면, 23년 12월 1일부터 12월 31일의 데이터는 일단위를 절삭하여 모두 2023-12-01로 표현된다. date_trunc('year', order_date)::date 라고 쿼리를 작성하면, 년도 하위단위는 모두 절삭하여 23년 1월1일부터 12월 31일의 일자는 모두 2023-01-01로 표현된다.

 

따라서, 위 함수를 적절히 이용하면 일별, 월별, 년도별 구매건수와 매출액을 쉽게 구할수 있다.

 

▼ 년도별 매출구하는 쿼리와 그 결과

select date_trunc('year', order_date)::date as day,
	count(distinct O.order_id) as ord_cnt,
	sum(I.amount) as sum_amount
from nw.orders O
	left join nw.order_items I using(order_id)
group by date_trunc('year', order_date)::date
order by date_trunc('year', order_date)::date
;