[Sqlite] 날짜 차원(Date Dimension) 테이블 만들기

시계열 분석을 할 때 주의해야 할 것은 중간에 측 데이터가 생기는 상황에서의 대응입니다.

 

이를 처리하기 위해서 날짜 차원 테이블을 이용하는데 날짜 차원 테이블은 특정 구간 내 모든 날짜별로 값이 저장된 행이 있는 정적 테이블을 의미합니다. 대부분의 데이터베이스에서 기본적으로 내장되어 있는 테이블이지만 sqlite를 이용할 경우 날짜 차원 테이블을 이용하기 위해서는 직접 만들어주는 수 밖에 없습니다.

 

다음의 쿼리문을 실행시키면 date_dim이라는 이름의 날짜 차원 테이블을 생성할 수 있습니다. 

 

1. 날짜 차원 테이블 생성 쿼리

-- Create a table to permanently store the output
CREATE TABLE date_dim AS
-- Initiate the recursive loop
WITH RECURSIVE
-- Define a CTE to hold the recursive output
rDateDimensionMinute (CalendarDateInterval)
AS
    (
        -- The anchor of the recursion is the start date of the date dimension
        SELECT datetime('2015-01-01 00:00:00')
        UNION ALL
        -- The recursive query increments the time interval by the desired amount
        -- This can be any time increment (monthly, daily, hours, minutes)
        SELECT datetime(CalendarDateInterval, '+24 hour') FROM rDateDimensionMinute
        -- Set the number of recursions
        -- Functionally, this is the number of periods in the date dimension
        LIMIT 100000
    )
-- Output the result set to the permanent table
SELECT CalendarDateInterval
FROM rDateDimensionMinute;

위 코드를 실행시키면 date_dim이라는 이름의 새로운 테이블이 저장된 것을 확인하실 수 있고 이를 이용해서 시계열 분석을 할 수 있게 됩니다. 

 

 

Reference

https://medium.com/@jeffclark_61103/creating-a-date-dimension-in-sqlite-aa6f52450971