[Postgresql] 일련의 연속적인 날짜 구하기

generate_series

SELECT * FROM generate_series('2022-12-01'::date, '2022-12-31'::date, '1 day'::interval) AS "day"

generate_series 는 연속적인 값들을 나타낼 때 사용하는 함수다.

generate_series의 arguments는 start, stop, step으로 시작 값, 끝 값, 그리고 간격이다.

 

SELECT * FROM generate_series(1, 10); -- 1,2,3,4,5,6,7,8,9,10 --
SELECT * FROM generate_series(1, 10, 2); -- 1,3,5,7,9 --

여기서 시작 값과 끝 값을 넣어주고 간격을 입력하면 그 사이의 값들을 rows로 표현해준다.

하지만 text의 경우는 일련의 형식으로 나타낼 수 없기 때문에 날짜의 경우 타입 캐스팅이 필수적이다.

또한 타입을 변환할 경우에는, 시간 값에서도 간격 기준을 필요로 하기 때문에, interval 타입의 값을 주어야 한다.

물론, 앞의 두 타입이 date나 timestamp라면 뒤의 타입은 interval인 게 당연하기에 생략 가능하다.

 

SELECT * FROM generate_series('2022-12-01'::date, '2022-12-31'::date, '1 day'::interval) AS "day"

이 SQL의 의미는 고로, 2022년 12월 1일부터 31일까지를 하루 간격으로 표현하라는 것이 된다.

간격을 조정하기 위해 아래처럼도 가능하다.

 

SELECT * FROM generate_series('2022-12-01'::date, '2022-12-31'::date, '1 hour') AS "day"

 

• second
• minute
• hour
• month
• 그 외 interval로 표현 가능한 모든 값들

 

이전 날짜 구하기

SELECT '2022-12-31'::DATE + '1 day'::INTERVAL -- 하루 후 --
SELECT '2022-12-31'::DATE + '-1 day'::INTERVAL -- 하루 전 --

하루 전과 하루 후는 위와 같이, 특정 날짜에 덧셈식을 이용해서 구해줄 수 있다.

여기서는 ::interval 이라는 타입 캐스팅이 필수적으로 들어가야 한다.

그렇지 않으면 문자열을 더하는 공식이 되기 때문에 Postgresql에서는 에러가 나온다.

이제 위에서 구한 쿼리들을 활용하면, 아래처럼 더 복잡한 시간 간격들을 구해볼 수 있다.

 

복잡한 시간 간격 구하기

매 하루마다, 그 하루와 그 하루로부터 한 달전의 날짜로 구성된 rows를 얻고자 한다면?

SELECT
        DATE_TRUNC('day',
            "date"."day") + '-30 days' AS "startDate",
        "date"."day" AS "endDate"
    FROM (
        SELECT
            *
        FROM
            generate_series('2022-12-01'::date,
                '2022-12-31'::date,
                '1 day') AS "day") AS "date"

 

 

from에 대한 서브 쿼리에 아까 쿼리를 넣어 그걸로부터 다시 startDate를 구하게끔 쿼리를 짰다.

이제 이 날짜를 cte ( common table exression ) 으로 삼아 데이터를 더 유용하게 가공할 수 있게 되었다.

 

WITH "days" AS (
    SELECT
        DATE_TRUNC('day',
            "date"."day") + '-30 days' AS "startDate",
        "date"."day" AS "endDate"
    FROM (
        SELECT
            *
        FROM
            generate_series('2022-12-01'::date,
                '2022-12-31'::date,
                '1 day'::interval) AS "day") AS "date"
)
SELECT
    days.*,
    (
        SELECT
            COUNT(*)
        FROM
            user AS u
        WHERE
            u. "createAt" >= "days"."startDate"
            AND u. "createAt" < "endDate")
    FROM
        "days"

이 쿼리는 각 날짜로부터 한 달 뒤까지 계산했을 때, 그 간격마다 유저의 가입자를 구하는 쿼리가 된다.

일부러 날짜끼리 겹치게 하여, 점점 증가하는 경향성을 가지고 있는지 확인해볼 수 있다.

아래는 위 쿼리를 실행했을 때 출력될 결과물의 예시다.

 

• 2022-11-01 00:00:00+09 2022-12-01 00:00:00+09 100
• 2022-11-02 00:00:00+09 2022-12-02 00:00:00+09 120
• 2022-11-03 00:00:00+09 2022-12-03 00:00:00+09 130
• 2022-11-04 00:00:00+09 2022-12-04 00:00:00+09 140
• 2022-11-05 00:00:00+09 2022-12-05 00:00:00+09 150