GENERATE_SERIES()

generate_series() 시리즈 생성 함수

통계쿼리를 살펴보는 중 찾아보았다.

oracle에서 CONNECT BY LEVEL 비슷한 기능을 한다.

아래 링크 참조 - 

https://www.postgresql.org/docs/9.5/static/functions-srf.html

예제)

-- 시간별
SELECT
    TO_CHAR(START_DATE, 'YYYY-MM-DD HH24:00') AS START_DATE,
    TO_CHAR(START_DATE +'1 hours', 'YYYY-MM-DD HH24:00') AS END_DATE
FROM generate_series(TO_CHAR(CURRENT_TIMESTAMP + '-1 days','YYYY-MM-DD HH24:00')::timestamp, 
                    CURRENT_TIMESTAMP, '1 HOUR') START_DATE
 
-- 일간
SELECT
    TO_CHAR(START_DATE, 'YYYY-MM-DD') AS START_DATE,
    TO_CHAR(START_DATE +'1 days', 'YYYY-MM-DD') AS END_DATE
FROM generate_series(TO_CHAR(CURRENT_TIMESTAMP + '-30 days','YYYY-MM-DD')::timestamp, 
                    CURRENT_TIMESTAMP, '1 days') START_DATE
 
-- 주간
SELECT
    TO_CHAR(START_DATE, 'YYYY-MM-DD') AS START_DATE,
    TO_CHAR(START_DATE +'7 DAY', 'YYYY-MM-DD') AS END_DATE
FROM generate_series(TO_CHAR(CURRENT_TIMESTAMP + '-30 days','YYYY-MM-DD')::timestamp, 
                    CURRENT_TIMESTAMP, '7 DAY') START_DATE
 
-- 월간
SELECT
    TO_CHAR(START_DATE, 'YYYY-MM') AS START_DATE,
    TO_CHAR(START_DATE::timestamp +'1 MONTH', 'YYYY-MM') AS END_DATE
FROM generate_series(TO_CHAR(CURRENT_TIMESTAMP + '- 1 YEARS','YYYY-MM-DD')::timestamp, 
                    CURRENT_TIMESTAMP, '1 MONTH') START_DATE