[PostgreSQL] generate_series을 활용한 날짜/시간 더미 데이터 생성 (월단위, 일단위, 시간단위)

날짜/시간 더미 데이터 생성

이번 포스팅에서는 postgresql에서 generate_series 함수를 활용하여 날짜 및 시간 더미 데이터를 생성하는 방법을 알아봅니다.

generate_series

generate_series (start,stop[,step])

• 일련의 숫자를 생성하는 함수. 시작과 끝, 증가 단계를 설정할 수 있음
• start : 시작 값입니다.
• stop : 시리즈의 끝값.
• step : 각 연속 숫자 사이의 증분 값. 선택 사항이며, 기본값은 1

예시)

SELECT generate_series(1, 10) as cnt

1부터 10까지 출력된다.

 

월 단위 데이터 더미 생성 (year-month)

• 2024년 1월부터 12월까지의 더미 데이터 생성

1. "generate_series(0,11)"을 활용하여 0부터 11까지 sequence를 생성
2. "date '2024-01-01' + (n || ' month')::interval" : 2020년 1월 1일 부터 각각 sequence를 더하여 각 월의 첫 날 생성
3. "date_trunc('month', ....)" : 각 날짜 timestamp을 해당 월의 첫번째 날로 변환
4. "TO_CHAR(...., ' YYYY_MM ')" : 결과를 연월(YYYY_MM)  형식으로 변환

SELECT 
    TO_CHAR(date_trunc('month', (date '2024-01-01' + (n || ' month')::interval)), 'YYYY_MM') AS yyyy_mm
FROM 
    generate_series(0, 11) AS n;

2024년 1월부터 12월까지 생성

 

일 단위 더미 데이터 생성

• 2024년 1월 15일부터 10일전까지의 더미 데이터 생성
  • 즉 2024년 01월 05일 부터 2024년 1월 15일까지의 더미 데이터 생성

1. "generate_series(0,11)"을 활용하여 0부터 11까지 sequence를 생성
2. "date '2024-01-15' - interval '1 day' * s.a" : 각 sequence값에 대해 2024-01-15부터 각각의 sequence를 뺌
   • PostgreSQL에서 interval을 활용한 날짜 계산 방법 :  https://ysyblog.tistory.com/136
3. "TO_CHAR(...., ' YYYYMMDD ')" : 결과를 연월일(YYYYMMDD)  형식으로 변환

SELECT 
    TO_CHAR(date '2024-01-15' - interval '1 day' * s.a, 'YYYYMMDD') AS date
FROM 
    generate_series(0, 10) AS s(a)
ORDER BY 
    date;

2024년 1월 15일의 10일전부터 일별로 데이터가 생성되었음

 

시간단위 더미데이터 생성

• 2024년 1월 29일의 0시부터 23시까지의 더미 데이터 생성

1. "generate_series(timestamp '2024-01-29 00:00:00', timestamp '2024-01-29 23:00:00', interval '1 hour')" : 1시간 간격으로 timestamp sequence 생성
2. "TO_CHAR(...., ' YYYY-MM-DD HH24 ')" : 결과를 연월일시간(YYYY-MM-DD HH24)  형식으로 변환

SELECT 
    TO_CHAR(generate_series(timestamp '2024-01-29 00:00:00', timestamp '2024-01-29 23:00:00', interval '1 hour'), 'YYYY-MM-DD HH24') AS datetime
ORDER BY 
    datetime;

 

날짜/시간 더미 데이터가 필요한 경우

• 날짜/시간 더미 데이터를 만드는 이유는 다양하겠지만, 보통 실무를 하며 필요한 경우는 주로 없는 날짜/시간을 출력하기 위함
  • 예를들어 1/1 ~ 1/10 일별 유저수를 집계하는 로직을 만들고 싶는데, 데이터가 없는 날짜가 있을 수 있는 경우
  • 데이터 없는 날짜에 "유저가 없다"는 의미로 0을 채워 데이터를 세팅하고자 함.
  • 이때 필요한 것이 날짜/시간 더미 데이터
  • 날짜/시간 더미 데이터를 만든다음, raw데이터에서 집계한 데이터를 Left join 후 null 값을 0으로 채워 넣으면 된다.

 

참고자료

https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-generate_series/