날짜/시간 더미 데이터 생성
이번 포스팅에서는 postgresql에서 generate_series 함수를 활용하여 날짜 및 시간 더미 데이터를 생성하는 방법을 알아봅니다.
generate_series
generate_series (start,stop[,step])
- 일련의 숫자를 생성하는 함수. 시작과 끝, 증가 단계를 설정할 수 있음
- start : 시작 값입니다.
- stop : 시리즈의 끝값.
- step : 각 연속 숫자 사이의 증분 값. 선택 사항이며, 기본값은 1
예시)
SELECT generate_series(1, 10) as cnt
월 단위 데이터 더미 생성 (year-month)
- 2024년 1월부터 12월까지의 더미 데이터 생성
- "generate_series(0,11)"을 활용하여 0부터 11까지 sequence를 생성
- "date '2024-01-01' + (n || ' month')::interval" : 2020년 1월 1일 부터 각각 sequence를 더하여 각 월의 첫 날 생성
- "date_trunc('month', ....)" : 각 날짜 timestamp을 해당 월의 첫번째 날로 변환
- "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월 15일부터 10일전까지의 더미 데이터 생성
- 즉 2024년 01월 05일 부터 2024년 1월 15일까지의 더미 데이터 생성
- "generate_series(0,11)"을 활용하여 0부터 11까지 sequence를 생성
- "date '2024-01-15' - interval '1 day' * s.a" : 각 sequence값에 대해 2024-01-15부터 각각의 sequence를 뺌
- PostgreSQL에서 interval을 활용한 날짜 계산 방법 : https://ysyblog.tistory.com/136
- "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월 29일의 0시부터 23시까지의 더미 데이터 생성
- "generate_series(timestamp '2024-01-29 00:00:00', timestamp '2024-01-29 23:00:00', interval '1 hour')" : 1시간 간격으로 timestamp sequence 생성
- "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/
728x90
반응형
'SQL & DB > PostgreSQL' 카테고리의 다른 글
[SQL] 부정 논리 연산자(not in 등) 과 Null의 관계 (2) | 2024.07.08 |
---|---|
[PostgreSQL] Json(Jsonb) 컬럼 Query 방법 (->, ->>과 json_array_elements) (1) | 2024.07.07 |
[PostgreSQL] PostgreSQL의 특징과 장단점 (1) | 2023.12.08 |
[PostgreSQL/MYSQL] 날짜/시간 계산(INTERVAL타입, age함수) (DATEDIFF, TIMESTAMPDIFF) (0) | 2023.11.24 |
[PostgreSQL] CSV File를 Table에 삽입(Import)하기. (2) | 2023.03.05 |