More than 1 year has passed since last update.

【MySQL】歯抜けになっている日付を埋めて集計したい

Last updated at 2022-06-23Posted at 2022-06-23

SQLで集計を行う際、以下のように取得元テーブルの
レコードの日付が連続しておらず、歯抜けになっていることがあります。

集計したいテーブル

SQL

SELECT * FROM rental;

出力

+-----------+---------------------+
| rental_id | rental_date         |
+-----------+---------------------+
|         1 | 2005-05-01 22:53:30 |
|         2 | 2005-05-02 22:54:33 |
|         3 | 2005-05-04 23:03:39 |
|         4 | 2005-05-07 23:04:41 |
|         5 | 2005-05-15 23:05:21 |
|         6 | 2005-05-17 23:08:07 |
|         7 | 2005-05-18 23:11:53 |
|         8 | 2005-05-20 23:31:46 |
|         9 | 2005-05-21 00:00:40 |
|        10 | 2005-05-21 00:02:21 |
|        11 | 2005-05-21 00:09:02 |
|        12 | 2005-05-22 00:19:27 |
|        13 | 2005-05-24 00:22:55 |
|        14 | 2005-05-24 00:31:15 |
|        15 | 2005-05-25 00:39:22 |
|        16 | 2005-05-25 00:43:11 |
|        17 | 2005-05-25 01:06:36 |
|        18 | 2005-05-25 01:10:47 |
|        19 | 2005-05-25 01:17:24 |
|        20 | 2005-05-25 01:48:41 |
|        21 | 2005-05-26 01:59:46 |
|        22 | 2005-05-27 02:19:23 |
|        23 | 2005-05-27 02:40:21 |
|        24 | 2005-05-28 02:53:02 |
|        25 | 2005-05-30 03:21:20 |
+-----------+---------------------+

　
上記のようなテーブルから日ごとのレンタル数を集計する場合、
単純にCOUNTで集計するとレンタルが行われなかった日はレコードが
存在しないため、以下のように日付が歯抜けになってしまいます。

日付が抜けた集計表

SQL

SELECT
     DATE(rental_date) as date,
     COUNT(*) as rent_count
FROM
     rental
GROUP BY
     DATE(rental_date)
;

出力

+------------+------------+
| date       | rent_count |
+------------+------------+
| 2005-05-01 |          1 |
| 2005-05-02 |          1 |
| 2005-05-04 |          1 |
| 2005-05-07 |          1 |
| 2005-05-15 |          1 |
| 2005-05-17 |          1 |
| 2005-05-18 |          1 |
| 2005-05-20 |          1 |
| 2005-05-21 |          3 |
| 2005-05-22 |          1 |
| 2005-05-24 |          2 |
| 2005-05-25 |          6 |
| 2005-05-26 |          1 |
| 2005-05-27 |          2 |
| 2005-05-28 |          1 |
| 2005-05-30 |          1 |
+------------+------------+

時分秒は不要なので切っています。

　
集計表は以下のように期間内の日付が全て表示され、集計対象（今回はレンタル数）が
存在しない場合には0となっているのが一般的かと思います。

+------------+------------+
| date       | rent_count |
+------------+------------+
| 2005-05-01 |          1 |
| 2005-05-02 |          1 |
| 2005-05-03 |          0 |
| 2005-05-04 |          1 |
| …          |          … |
+------------+------------+

MySQLには共通テーブル式という機能があるため、そちらを使用することで
集計期間内の日付を埋めて集計表を作成してみようと思います。

MySQL日本語ドキュメント
https://dev.mysql.com/doc/refman/8.0/ja/with.html
※MySQL8.0以降で使用可能。それ以前のバージョンではsyntax errorになります。

実際に書いたSQL

5月中の日ごとのレンタル数を取得しています。

SQL

WITH RECURSIVE date_list (date) AS
(
    SELECT '2005-05-01'
  UNION ALL
    SELECT DATE(date + INTERVAL 1 DAY)
    FROM date_list
    WHERE date < '2005-05-31'
)
SELECT
    date,
    IFNULL(rent.rent_count, 0) as rent_count
FROM date_list
LEFT JOIN
(
    SELECT
        DATE(rental_date) as rent_date,
        count(*) as rent_count
    FROM
        rental
    WHERE
        rental_date BETWEEN '2005-05-01' AND '2005-05-31'
    GROUP BY 
        rent_date
) as rent
ON  date = rent.rent_date
;

出力

+------------+------------+
| date       | rent_count |
+------------+------------+
| 2005-05-01 |          1 |
| 2005-05-02 |          1 |
| 2005-05-03 |          0 |
| 2005-05-04 |          1 |
| 2005-05-05 |          0 |
| 2005-05-06 |          0 |
| 2005-05-07 |          1 |
| 2005-05-08 |          0 |
| 2005-05-09 |          0 |
| 2005-05-10 |          0 |
| 2005-05-11 |          0 |
| 2005-05-12 |          0 |
| 2005-05-13 |          0 |
| 2005-05-14 |          0 |
| 2005-05-15 |          1 |
| 2005-05-16 |          0 |
| 2005-05-17 |          1 |
| 2005-05-18 |          1 |
| 2005-05-19 |          0 |
| 2005-05-20 |          1 |
| 2005-05-21 |          3 |
| 2005-05-22 |          1 |
| 2005-05-23 |          0 |
| 2005-05-24 |          2 |
| 2005-05-25 |          6 |
| 2005-05-26 |          1 |
| 2005-05-27 |          2 |
| 2005-05-28 |          1 |
| 2005-05-29 |          0 |
| 2005-05-30 |          1 |
| 2005-05-31 |          0 |
+------------+------------+

無事、日付を埋めて集計できました。

【参考】SQLの内容

WITH RECURSIVE date_list (date) AS
(
    SELECT '2005-05-01'
  UNION ALL
    SELECT DATE(date + INTERVAL 1 DAY)
    FROM date_list
    WHERE date < '2005-05-31'
)

WITH句では5月中の日付一覧を生成しています。
今回使用しているのは再帰的共通テーブル式（WITH RECURSIVE）と呼ばれるものです。

再帰的共通テーブル式には非再帰部分（UNION ALLの上のSELECT）と
再帰部分（UNION ALLの下のSELECT）があり、上記SQLでは以下の処理を行っています。

【非再帰部分】
・最初の行（2005-05-01）を生成

【再帰部分】
・2行目以降の行を生成
・FROM句でdate_list自身を再帰的に参照することで繰り返し行の生成を行う

再帰部分の date は '2005-05-01' , '2005-05-02' , '2005-05-03' ... '2005-05-30'の
ように与えられ、date が '2005-05-31' になった時点でWHERE条件を
満たさなくなるため再帰処理を終了します。SELECTしているのは
date + 1日であるため、再帰部分では '2005-05-02' ～ '2005-05-31' が生成されます。

SELECT
    date,
    IFNULL(rent.rent_count, 0) as rent_count
FROM date_list
LEFT JOIN
(
    SELECT
        DATE(rental_date) as rent_date,
        count(*) as rent_count
    FROM
        rental
    WHERE
        rental_date BETWEEN '2005-05-01' AND '2005-05-31'
    GROUP BY 
        rent_date
) as rent
ON  date = rent.rent_date
;

WITH句以降の部分では先ほど記載した日付が歯抜けになっている集計結果を
取得し、date_listを主テーブルとして外部結合することで日付の抜けを補完しています。

外部結合の場合には紐づく結合テーブルのレコードがなかった場合には
値がNULLになるため、IFNULLで0を設定しています。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up