출처: https://sas-study.tistory.com/380
generate_series 함수
- 9.1 버전 등장
먼저 위의 결과를 똑같이 내는 쿼리문을 짜보겠습니다.
select generate_series(1, 10) as num
-- 결과 -------
1
2
3
4
5
6
7
8
9
10
단지 generate_series 함수의 인자에 1과 10을 넘겼을 뿐입니다. 이는 자연적으로 1씩 증가합니다.
2씩 증가
select generate_series(1, 10, 2) as num
-- 결과 (1부터 10까지 2씩 증가)
------- 1 3 5 7 9
1씩 감소
select generate_series(10, 1, -1) as num
-- 결과 (10부터 1까지 1씩 감소)
------- 10 9 8 7 6 5 4 3 2 1
날짜 시간관련 데이터 생성
1년씩 증가
select generate_series('2010-01-01'::date, '2020-12-31'::date, '1 years') years;
-- 결과 ---------------------------
2010-01-01 00:00:00.000000
2011-01-01 00:00:00.000000
2012-01-01 00:00:00.000000
2013-01-01 00:00:00.000000
2014-01-01 00:00:00.000000
2015-01-01 00:00:00.000000
2016-01-01 00:00:00.000000
2017-01-01 00:00:00.000000
2018-01-01 00:00:00.000000
2019-01-01 00:00:00.000000
2020-01-01 00:00:00.000000
1달씩 증가
select generate_series('2020-01-01'::date, '2020-12-31'::date, '1 months') as months;
-- 결과 -------------------------
2020-01-01 00:00:00.000000
2020-02-01 00:00:00.000000
2020-03-01 00:00:00.000000
2020-04-01 00:00:00.000000
2020-05-01 00:00:00.000000
2020-06-01 00:00:00.000000
2020-07-01 00:00:00.000000
2020-08-01 00:00:00.000000
2020-09-01 00:00:00.000000
2020-10-01 00:00:00.000000
2020-11-01 00:00:00.000000
2020-12-01 00:00:00.000000
1일씩 증가
select generate_series('2020-01-01'::date, '2020-12-31'::date, '1 days') days;
-- 결과 ---------------------------
2020-01-01 00:00:00.000000
2020-01-02 00:00:00.000000
2020-01-03 00:00:00.000000
2020-01-04 00:00:00.000000
2020-01-05 00:00:00.000000
2020-01-06 00:00:00.000000
2020-01-07 00:00:00.000000
2020-01-08 00:00:00.000000
2020-01-09 00:00:00.000000
2020-01-10 00:00:00.000000
2020-01-11 00:00:00.000000 ...
2020-12-31 00:00:00.000000
1시간씩 증가
select generate_series('2020-01-01'::date, '2020-01-02'::date, '1 hours') hours;
-- 결과 ---------------------------
2020-01-01 00:00:00.000000
2020-01-01 01:00:00.000000
2020-01-01 02:00:00.000000
2020-01-01 03:00:00.000000
2020-01-01 04:00:00.000000
2020-01-01 05:00:00.000000
2020-01-01 06:00:00.000000
2020-01-01 07:00:00.000000
2020-01-01 08:00:00.000000
2020-01-01 09:00:00.000000
2020-01-01 10:00:00.000000 ...
2020-01-02 00:00:00.000000
1분씩 증가
select generate_series('2020-01-01'::date, '2020-01-02'::date, '1 minutes') minutes;
-- 결과 ------------------------
2020-01-01 00:00:00.000000
2020-01-01 00:01:00.000000
2020-01-01 00:02:00.000000
2020-01-01 00:03:00.000000
2020-01-01 00:04:00.000000
2020-01-01 00:05:00.000000
2020-01-01 00:06:00.000000
2020-01-01 00:07:00.000000
2020-01-01 00:08:00.000000 ...
2020-01-02 00:00:00.000000
