【初心者の備忘録】WITH RECURSIVE句を使って月ごとにラベル付けしてみよう

　SQLによるクエリをいくつか作成してきて、WITH RECURSIVE句を使うレアなパターンがあった。今回は、コードと一緒にケースを説明する。

 ## WITH RECURSIVEによる、月初ラベル作成

 　以下に示すのは、**日本時間**を基準として年月日と時刻を与え、月初である`start_month`から月末の`end_month`まで、1ヶ月ずつ追加していきながら、「月ごとにラベルを生成していく」`month_series`である。
  
　多くの場合は、テーブル内の時刻データを取得すれば完結することが多いが、例えば、「月ごとにAへ所属していたユーザーを調べ、結果をつなげる」というケースである場合は、入会月や退会月という時刻のデータを単純に出力することが不適切になるので、こういった方法が役に立つ。

```sql
-- 日本時間基準で月初をunixtimeとして与える
SET
    @start_month = UNIX_TIMESTAMP('2023-09-01 00:00:00') - 32400;

-- 日本時間基準で月末をunixtimeとして与える
SET
    @end_month = UNIX_TIMESTAMP('2024-08-31 23:59:59') - 32400;

WITH RECURSIVE month_series AS (
    -- 初期値を設定 (UNIX時間をDATE型に変換)
    SELECT
        DATE_FORMAT(
            CONVERT_TZ(
                FROM_UNIXTIME(@start_month),
                '+00:00',
                '+09:00' -- UTCから日本時間に変換
            ),
            '%Y-%m-01 00:00:00' -- 月初にフォーマット
        ) AS current_start
    UNION
    -- 次の月をDATE型で加算 (1ヶ月足す)
    SELECT
        DATE_ADD(current_start, INTERVAL 1 MONTH)
    FROM
        month_series
    WHERE
        UNIX_TIMESTAMP(DATE_ADD(current_start, INTERVAL 1 MONTH)) - 32400 <= @end_month
)
SELECT
......(中略)
```

## これを用いた呼び出し方の例
　例えば、以下のように月初のラベルを活用するものがある。(日本時間基準の)指定期間で月ごとにクラブAへ所属していたユーザーの**ID**と**名前**を出力するというケースである。また、用いているテーブルは簡単にいうと以下のようなものである。(ユーザーのデータがある`user`と、クラブのメンバーのデータがある`club_member`とする)

 | テーブル`user`のカラム | 説明欄 |
 | -- | -- |
 | id | ユーザーID |
 | name | ユーザー名 |
<br>

 | テーブル`club_member`のカラム | 説明欄 |
 | -- | -- |
 | club_id | クラブのID |
 | user_id | ユーザーID(user.idと同値) |
 | start | クラブのメンバーの入会月(unixtime) |
 | expire | クラブのメンバーの退会月(unixtime) |
 <br>

 ```sql
 -- クラブAのID
 SET
    @club_id = 9999;

-- 日本時間基準で月初をunixtimeとして与える
SET
    @start_month = UNIX_TIMESTAMP('2023-09-01 00:00:00') - 32400;

-- 日本時間基準で月末をunixtimeとして与える
SET
    @end_month = UNIX_TIMESTAMP('2024-08-31 23:59:59') - 32400;

WITH RECURSIVE month_series AS (
    -- 初期値を設定 (UNIX時間をDATE型に変換)
    SELECT
        DATE_FORMAT(
            CONVERT_TZ(
                FROM_UNIXTIME(@start_month),
                '+00:00',
                '+09:00' -- UTCから日本時間に変換
            ),
            '%Y-%m-01 00:00:00' -- 月初にフォーマット
        ) AS current_start
    UNION
    -- 次の月をDATE型で加算 (1ヶ月足す)
    SELECT
        DATE_ADD(current_start, INTERVAL 1 MONTH)
    FROM
        month_series
    WHERE
        UNIX_TIMESTAMP(DATE_ADD(current_start, INTERVAL 1 MONTH)) - 32400 <= @end_month
)
SELECT
    DATE_FORMAT(current_start, '%Y-%m') AS `調査月`,
    club_member.user_id AS `ユーザーID`,
    u.name AS `ユーザー名`
FROM
    month_series AS ms
    JOIN club_member ON club_member.club_id = @club_id
    AND club_member.start <= UNIX_TIMESTAMP(
        DATE_SUB(
            DATE_ADD(ms.current_start, INTERVAL 1 MONTH),
            INTERVAL 1 SECOND
        )
    ) - 32400
    AND club_member.expire >= UNIX_TIMESTAMP(ms.current_start) - 32400
    LEFT JOIN user AS u ON u.id = club_member.user_id
ORDER BY
    ms.current_start;
 ```

 　これを用いることで、あの**UNION ALL**を用いずに、1年分の月ごとのデータを繋げることができるのである。(素晴らしい！)

　ちなみに、以下のような形式で出力される。(あくまで一例)

 | 調査月 | ユーザーID | ユーザー名 |
 | -- | -- | -- |
 | 2023-09 | 1111111 | ああああああ |
 | 2023-10 | 1111111 | ああああああ |
 | 2023-10 | 1112112 | かかかかかか |
 | 2023-11 | 1111111 | ああああああ |
 | 2023-11 | 1112112 | かかかかかか |
 | 2023-11 | 1333333 | ささささささ |
 <br>
 (下に続いていく......感じになっている)

## 簡単な感想
　ラベル付けを日付による再帰で行うと便利ではあるが、これは再帰的な方法を用いて結果を出力しているので、(クラブの行が重複して)行の数が多くなりがちである。したがって、長すぎる期間を指定しすぎないように注意することが必要だ。