SQLで期間指定してい日付一覧（リスト）を取得する方法（カレンダー取得）【MySQL】

DB関連・MySQL

こんにちは　ひこにき です！

SQLで指定した期間の日付一覧（カレンダー）を取得する方法についてご紹介いたします

BIツールで実績レポートをSQLで作成する際に、実績があるデータしか抽出（表示）されずに日付が歯抜け状態のレポートになったりしてませんか？

今回ご紹介する方法によって 指定した期間の日付一覧が取得できるようになりますので 実績取得するSQLと 外部結合（LEFT JOIN） することによって連続した日付一覧のレポートを作成することができます！

Point

WITH句（CTE）を利用したSQLになりますが MySQL 8.0 からのサポートとなりますので注意してください

SQLで開始日、終了日の期間を指定して日付の一覧を取得するSQL

WITH句 共通テーブル式（CTE）を利用した取得方法

【SQL】日付一覧取得するSQL
-- 対象となる日付を設定
SET @start_date = '2023-01-27';
SET @end_date = '2023-02-01';

-- ↑設定した変数でSQL実行（期間の日付を出力）
WITH RECURSIVE date_list AS 
(
  SELECT    -- 開始日出力
      1 as n, @start_date as target_date
  UNION ALL
  SELECT    -- 日付を1日ずつ増加して出力
      n + 1 ,DATE_ADD(@start_date, interval n day) 
  FROM date_list
  WHERE n <= DATEDIFF(@end_date,@start_date)    -- 開始終了の差分日付件数まで実行
)
SELECT target_date FROM date_list;

指定する開始日付（@start_date）と終了日付（@end_date）を変数としておりますが、BIツールのパラメータに書き換えたり、直接SQLに値を記述しても動きますのでご確認ください

【実行結果】指定した日付一覧の結果
mysql> SET @start_date = '2023-01-27';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @end_date = '2023-02-01';
Query OK, 0 rows affected (0.00 sec)

mysql> WITH RECURSIVE date_list AS
    -> (
    ->   SELECT    -- 開始日出力
    ->       1 as n, @start_date as target_date
    ->   UNION ALL
    ->   SELECT    -- 日付を1日ずつ増加して出力
    ->       n + 1 ,DATE_ADD(@start_date, interval n day)
    ->   FROM date_list
    ->   WHERE n <= DATEDIFF(@end_date,@start_date)    -- 開始終了の差分日付件数まで実行
    -> )
    -> SELECT target_date FROM date_list;
+-------------+
| target_date |
+-------------+
| 2023-01-27  |
| 2023-01-28  |
| 2023-01-29  |
| 2023-01-30  |
| 2023-01-31  |
| 2023-02-01  |
+-------------+
6 rows in set (0.00 sec)

上記の例では開始日と終了日を指定して日付一覧を取得しましたが、年月指定 や LAST_DAY関数を利用することで簡単にカレンダーを取得することもできます。

年月指定でカレンダーの日付を取得するSQL

【SQL】カレンダー取得するSQL
-- 対象となる年月の初日(1日)を設定
SET @target_YM = '2023-01-01';

-- カレンダー取得SQLの実行
WITH RECURSIVE calendar AS 
(
  SELECT    -- 開始日出力
      1 as n, @target_YM as date
  UNION ALL
  SELECT    -- 日付を1日ずつ増加して出力
      n + 1 ,DATE_ADD(@target_YM, interval n day) 
  FROM calendar
  WHERE n <= DATEDIFF(LAST_DAY(@target_YM),@target_YM)  
)
SELECT date,
  CASE dayofweek(date)  -- 曜日出力
   WHEN 1 THEN '日'
   WHEN 2 THEN '月'
   WHEN 3 THEN '火'
   WHEN 4 THEN '水'
   WHEN 5 THEN '木'
   WHEN 6 THEN '金'
   WHEN 7 THEN '土'
  END youbi
FROM calendar;
【実行結果】指定した年月のカレンダー（日付・曜日）の取得結果
mysql> SET @target_YM = '2023-01-01';
Query OK, 0 rows affected (0.00 sec)

mysql> WITH RECURSIVE calendar AS
    -> (
    ->   SELECT    -- 開始日出力
    ->       1 as n, @target_YM as date
    ->   UNION ALL
    ->   SELECT    -- 日付を1日ずつ増加して出力
    ->       n + 1 ,DATE_ADD(@target_YM, interval n day)
    ->   FROM calendar
    ->   WHERE n <= DATEDIFF(LAST_DAY(@target_YM),@target_YM)
    -> )
    -> SELECT date,
    ->   CASE dayofweek(date)  -- 曜日出力
    ->    WHEN 1 THEN '日'
    ->    WHEN 2 THEN '月'
    ->    WHEN 3 THEN '火'
    ->    WHEN 4 THEN '水'
    ->    WHEN 5 THEN '木'
    ->    WHEN 6 THEN '金'
    ->    WHEN 7 THEN '土'
    ->   END youbi
    -> FROM calendar;

+------------+-------+
| date       | youbi |
+------------+-------+
| 2023-01-01 | 日    |
| 2023-01-02 | 月    |
| 2023-01-03 | 火    |
| 2023-01-04 | 水    |
| 2023-01-05 | 木    |
| 2023-01-06 | 金    |
| 2023-01-07 | 土    |
| 2023-01-08 | 日    |
| 2023-01-09 | 月    |
| 2023-01-10 | 火    |
| 2023-01-11 | 水    |
| 2023-01-12 | 木    |
| 2023-01-13 | 金    |
| 2023-01-14 | 土    |
| 2023-01-15 | 日    |
| 2023-01-16 | 月    |
| 2023-01-17 | 火    |
| 2023-01-18 | 水    |
| 2023-01-19 | 木    |
| 2023-01-20 | 金    |
| 2023-01-21 | 土    |
| 2023-01-22 | 日    |
| 2023-01-23 | 月    |
| 2023-01-24 | 火    |
| 2023-01-25 | 水    |
| 2023-01-26 | 木    |
| 2023-01-27 | 金    |
| 2023-01-28 | 土    |
| 2023-01-29 | 日    |
| 2023-01-30 | 月    |
| 2023-01-31 | 火    |
+------------+-------+
31 rows in set (0.00 sec)

今回はSQLでカレンダーを取得する方法を紹介しました。このSQLを覚えておくと日付を起点に複雑なレポート集計をSQLで実装することもできるため覚えておくと役に立つときがくると思います

コメント

タイトルとURLをコピーしました