date_trunc와 generate_series

기본 패턴

SELECT
  date_trunc('day', created_at) AS day,
  COUNT(*) AS count
FROM orders
GROUP BY 1
ORDER BY 1;

설명

• date_trunc는 timestamp를 일, 주, 월 같은 일정한 단위로 잘라 같은 버킷에 묶게 해 줍니다. 시간대별·일별·월별 리포트에서 거의 기본처럼 쓰입니다.
• generate_series는 연속된 숫자나 날짜 구간을 가상 테이블처럼 만들어 줍니다. 그래서 "기록이 없는 날짜도 0으로 채운 보고서" 같은 요구를 처리할 때 특히 강합니다.
• 이 둘을 함께 쓰면 단순 집계를 넘어 시계열 리포트 구조를 꽤 정교하게 만들 수 있습니다. 예를 들어 최근 30일 날짜 목록을 먼저 만들고, 실제 주문 집계를 left join해 빈 날짜를 채울 수 있습니다.
• 중요한 것은 리포트에서 "없는 날짜"도 데이터라는 감각입니다. 실제 행이 없는 날을 그냥 빼면 그래프와 정산표가 왜곡될 수 있습니다.
• PostgreSQL은 이런 시계열 보조 작업을 애플리케이션 코드 바깥, SQL 안에서 상당 부분 처리할 수 있습니다. 그래서 데이터 팀과 백엔드가 같은 쿼리 언어로 대화하기 쉬워집니다.

빠른 정리

주의할 점