SELECT文だけでカレンダーデータを期間ごとCSVとして取得する

プログラムを作成せずSELECT文のクエリ1つで、行方向(縦)に並んでいるカレンダーデータを期間ごとに分類して列方向(横)にしてCSVで取得する方法を記載します。

列に期間ごとの日付列を持つカレンダーがほしい

EXCELなどに張り付ける日付データは行方向に並べる場合はさして問題ないのですが、長期間の日付を年ごとや月ごとに分類して列方向に並べる場合は結構大変です。
通常のデータベースでは列をSELECT文実行時に増やすのは非効率的なものです。
ですが……

WITH句での再帰処理とグループ化を駆使することでSELECT文1つで期間ごとに行に分けて日付をCSVとして取得することが可能です!

では、実際にやってみましょう。

再帰可能なWITH句を利用したSELECT文で行に並んだデータを列に集約する

WITH句では定義したものを自分自身が呼び出せる「再帰」処理が可能ということは「SELECT文だけでカレンダーテーブルを作成する方法」のページで記載した通りです。

どうやって日付をCSV化するかというと、以下のように自分自身を呼び出す時に以下のようにCSVを集約するカラムを作ってしまえばいいわけです。
つまり、

  1. CSV用カラムに初期値として期間の最初の日付を設定する
  2. CSV用カラムに翌日の日付とカンマを結合して自分自身を呼び出す
  3. 取得側SELECT文のWHERE句で期間の終了日を設定する
というような感じでSELECT文を組み立てればよいのです。

そのクエリが以下の通りのSELECT文です。
※下の例では2014年から2020年までの月ごとの日付CSVを取得するSELECT文です。

-- カレンダービュー定義CREATE文
DROP VIEW IF EXISTS calendar;
CREATE VIEW calendar 
( 
  calendar_date
) AS 
WITH RECURSIVE calendar(calendar_date) AS (
    SELECT 
        DATE('2010-01-01') AS calendar_date
    UNION ALL
    SELECT
        DATE(calendar_date, '+1 day') AS calendar_date
    FROM calendar
    WHERE
        calendar_date < DATE('now', '+20 years')
)
SELECT calendar_date FROM calendar;

-- 年月ごとに日付をCSVとするSELECT文
WITH RECURSIVE calendar_to_csv
(
    year
    , year_month
    , calendar_date
    , calendar_date_csv
) AS (
    -- 上のSELECT文を初期値SELECTとします。
    SELECT
        STRFTIME('%Y', MIN(calendar_date)) AS year
        , STRFTIME('%Y-%m', MIN(calendar_date)) AS year_month
        , MIN(calendar_date)
        , MIN(calendar_date) AS  calendar_date_csv
    FROM calendar
    GROUP BY
         STRFTIME('%Y', calendar_date)
        , STRFTIME('%Y-%m', calendar_date)
    UNION ALL
    -- 下のSELECT文を再帰SELECTとします。
    SELECT
        STRFTIME('%Y',      t.calendar_date, '+1 day') AS year
        , STRFTIME('%Y-%m', t.calendar_date, '+1 day') AS year_month
        , DATE(t.calendar_date, '+1 day') AS calendar_date
        , v.calendar_date_csv || ',' || DATE(t.calendar_date, '+1 day') AS calendar_date_csv
    FROM calendar_to_csv    AS v
        INNER JOIN
        calendar            AS t
        ON
            v.year          = STRFTIME('%Y',    t.calendar_date, '+1 day')
        AND v.year_month    = STRFTIME('%Y-%m', t.calendar_date, '+1 day')
        AND v.calendar_date = t.calendar_date
)
SELECT 
    year_month
    , MAX(calendar_date_csv)
FROM calendar_to_csv
WHERE
    year BETWEEN '2014' AND '2020'
GROUP BY 
    year_month;

            

このカレンダーCSV取得SELECT文の課題

ただ、上のSELECT文にはまだ問題が残っています。
年ごとの日付CSVを取得したい場合は初期値SELECT文のGROUP BY句と再帰SELECT文の結合条件を変更しないと対応不可能です。
これに関しては今後の課題としておきます。すみません。

以下に年ごとに日付CSVを取得するSELECT文を記載しておきます。

-- 年ごとに日付をCSVとするSELECT文
WITH RECURSIVE calendar_to_csv
(
    year
    , year_month
    , calendar_date
    , calendar_date_csv
) AS (
    -- 上のSELECT文を初期値SELECTとします。
    SELECT
        STRFTIME('%Y', MIN(calendar_date)) AS year
        , STRFTIME('%Y-%m', MIN(calendar_date)) AS year_month
        , MIN(calendar_date)
        , MIN(calendar_date) AS  calendar_date_csv
    FROM calendar
    GROUP BY
        STRFTIME('%Y', calendar_date)
        -- 下の年月グループ化を削除
        -- , STRFTIME('%Y-%m', calendar_date)
    UNION ALL
    -- 下のSELECT文を再帰SELECTとします。
    SELECT
        STRFTIME('%Y',      t.calendar_date, '+1 day') AS year
        , STRFTIME('%Y-%m', t.calendar_date, '+1 day') AS year_month
        , DATE(t.calendar_date, '+1 day') AS calendar_date
        , v.calendar_date_csv || ',' || DATE(t.calendar_date, '+1 day') AS calendar_date_csv
    FROM calendar_to_csv    AS v
        INNER JOIN
        calendar            AS t
        ON
            v.year          = STRFTIME('%Y',    t.calendar_date, '+1 day')
        -- 下の年月結合条件を削除
        -- AND v.year_month    = STRFTIME('%Y-%m', t.calendar_date, '+1 day')
        AND v.calendar_date = t.calendar_date
)
SELECT 
    year
    , MAX(calendar_date_csv)
FROM calendar_to_csv
WHERE
    year BETWEEN '2014' AND '2020'
GROUP BY 
    year;