[sql] recursive CTE2

https://indistract.tistory.com/70

[sql] recursive CTE

 

위 포스팅에서는 recursive cte를 이용해서 시간 테이블을 만드는 방법을 알아봤었다. 이번에는 recursive cte를 이용해 일자 테이블을 만드는 방법을 알아본다. 테이블을 만드는 원리는 위 포스팅과 동일하며, 아래와 같은 테이블을 만드는 것이 목적이다 ("2023-03-31" 일자까지 출력).

 

포스팅 내용을 복기해보면, 아래와 같이 재귀 CTE를 사용했었다.

 

① with recuresive로 재귀 cte를 사용함 명시
② 첫 select 절에 초기값 기입
③ union all 아래 select 절에 반복처리 설정 
④ union all 아래 where절에 정지조건 표기

 

일자 테이블을 만들기위해 위 순서로 진행하면 아래와 같은 쿼리를 작성할 수 있다. "2024-03-01"을 초기값으로 지정해줬고, 1일씩 더해서 반복적으로 union하도록 했다. 그리고 curr_date 변수가 3월의 마지막 일자에 다다르면 쿼리는 중단된다. mysql에는 last_day()라는 함수가있어, 일자의 마지막 일을 쉽게 구할수 있다.

WITH RECURSIVE
temp_00 AS (
    SELECT DATE('2024-03-01') AS curr_date
    UNION ALL
    SELECT curr_date + INTERVAL 1 DAY
    FROM temp_00
    WHERE curr_date < LAST_DAY(DATE('2024-03-01'))
)

SELECT * FROM temp_00;

 

recursive cte는 쉽게 다가오는 개념은 아니다. mysql에서는 이런 일자테이블을 생성하려면 recursive cte를 사용해야하지만, postgres에서는 generate_series라는 함수가 있어, recursive cte를 사용하지 않아도 아래와같이 편리하게 일자 테이블을 생성할 수 있다. 이렇게 데이터 베이스마다 편리함을 주는 부분이 다름을 깨닫고 간다. 

with 
temp_00 as (
select generate_series('2023-03-01'::date , '2023-03-31'::date, '1 day'::interval)::date as current_date
)
select *
from temp_00
;