PostgreSQL | 날짜별 기간별 group by 원하는 값 카운트

 

 

1. 원하는 날짜별 혹은 기간별로 자르기

 

Truncate의 축약 표현인 trunc가 들어간 함수이다.

date_trunc 원래 단어 뜻 그대로 날짜를 잘라낸다, 절사한다. 직관적이다.

 

date_trunc(절사 단위, 절사 대상)

 

 

• 절사 대상 위치에 날짜 혹은 시간 값이 표현되는 데이터베이스 필드명을 입력한다.
  (type timestamp, timestamp with time zone 또는 interval의 값)
• 해당 날짜를 어느 정도의 정밀도로 쪼갤 것인지 단위를 지정한다.
  (반환 값도 마찬가지로 type timestamp, time zone 또는 interval)

date_trunc('year', timestamp) // 연도별
date_trunc('month', timestamp) // 월별
date_trunc('week', timestamp) // 주별
date_trunc('day', timestamp) // 날짜별

 

• date_trunc 함수에서 지원하는 절사 단위 전체는 아래와 같다.

microseconds

milliseconds

second

minute

hour

day

week

month

quarter

year

decade

century

millennium

 

 

2. 쪼갠 단위 기준으로 select, group by

쪼갠 단위 기준으로 값을 묶어줘야 하니 group by 절도 함께 명시해야 한다.

추가 조건이 없는 가장 심플한 구문 형태는 아래와 같다.

select
  date_trunc('year', timestamp) as yearly,
  카운트 할 값이 포함된 필드
from
  데이터베이스 테이블
group by
  date_trunc('year', timestamp) as yearly

 

 

e.g. 월별로 가입자수를 조회한다면?

상황

가입한 유저의 일반적인 정보를 담고 있는 테이블 users_info
생성된 유저 값 고유 필드 id
유저가 가입한 날짜 값 필드 date_joined

[질문]
2023년 한 해 동안 월별 가입자수는? 

 

 

쿼리문

select
	DATE_TRUNC('month', u.date_joined at time zone 'kst') as monthly,
	count(id)
from
	users_info as u
where
	u.date_joined at time zone 'kst' >= '2023-01-01 00:00:00' 
	and u.date_joined at time zone 'kst' <= '2023-12-31 23:59:59'
group by
	DATE_TRUNC('month', u.date_joined at time zone 'kst')

 

조회 결과

montly	count
"2023-01-01 00:00:00"	283
"2023-02-01 00:00:00"	251
"2023-03-01 00:00:00"	335
"2023-04-01 00:00:00"	135
"2023-05-01 00:00:00"	345
"2023-06-01 00:00:00"	274
"2023-07-01 00:00:00"	267
"2023-08-01 00:00:00"	236
"2023-09-01 00:00:00"	462
"2023-10-01 00:00:00"	434
"2023-11-01 00:00:00"	665
"2023-12-01 00:00:00"	986

 

 

---

참고 공식문서:

 

https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC

9.9. Date/Time Functions and Operators