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

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

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

　以下に示すのは、日本時間を基準として年月日と時刻を与え、月初であるstart_monthから月末のend_monthまで、1ヶ月ずつ追加していきながら、「月ごとにラベルを生成していく」month_seriesである。

　多くの場合は、テーブル内の時刻データを取得すれば完結することが多いが、例えば、「月ごとにAへ所属していたユーザーを調べ、結果をつなげる」というケースである場合は、入会月や退会月という時刻のデータを単純に出力することが不適切になるので、こういった方法が役に立つ。

-- 日本時間基準で月初を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	ユーザー名

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

-- クラブ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	ささささささ

(下に続いていく......感じになっている)

簡単な感想

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