【MySQL】日付のログデータから日別の件数に集計・カウントするクエリの例（with recursive句）

MySQLで顧客IDと各イベントの日付（購入日、クーポン取得日、クーポン利用日 etc）が格納されているデータを日付ごとの各イベントの発生件数に集計しなおすクエリの例をご紹介します。

日付のログデータを日付ごとの件数に集計するというのは、以下画像の左側のデータを右側のデータのように変形するイメージです。

MySQLではwith recursive句を使って、日付の配列を生成することができますので、生成した日付配列と仮想テーブルをcross joinし、日付ごとのイベント発生件数をカウントしていきます。

以下のように「顧客ID」「面談日」「契約日」が格納されているダミーデータを使って、日付ごとに面談数・契約数がカウントされたデータに集計していきます。

日付データを日別の件数で集計するクエリ
1. with recursive句で日付の配列を生成
2. 仮想テーブルをcross joinし、count + case whenで日付ごとにカウント
まとめ

日付データを日別の件数で集計するクエリ

実際のクエリ例は以下のとおりです。

with recursive generate_d_array (date) as (
  select '2023-01-01'
  union all
  select date + interval 1 day
  from generate_d_array
  where date < '2023-12-31'
), event as (
  select
    id
    ,interview_date
    ,contract_date
  from
    table
)

select
  d.date
  ,count(case when d.date = e.interview_date then e.id end) as interview_cnt
  ,count(case when d.date = e.contract_date then e.id end) as contract_cnt
from
  generate_d_array as d
  cross join event as e
group by
  1
order by
  1

きます。

with recursive句で日付の配列を生成

まず、with recursive句で日付の配列を生成します。

with recursive句は、with句で実行した自己結果を参照し、その結果を元にしてループ処理をすることができます。

1から100までの連番を生成したり、1月1日～12月31日までの連続する日付を生成する、といったループ処理が必要なケースで重宝します。

※with recursive句は「再帰的問い合わせ」を指すSQL標準化委員会で選ばれている用語のようですが、再帰（recursion）というよりも、反復（iteration）がフィットする表現になります。

with recursive 仮想テーブル名 (カラム名) as (
select 開始レコード
union all
select カラム名 + ループ処理
from 仮想テーブル名
where < 終了レコード
)

のように記述します。

※whereで終了レコードを指定しないとループが永遠と続くクエリになり、エラーとなってしまいます。

with recursive generate_d_array (date) as (
  select '2023-01-01'
  union all
  select date + interval 1 day
  from generate_d_array
  where date < '2023-12-31'
), event as (
  select
    id
    ,interview_date
    ,contract_date
  from
    table
)

1日単位の日付の配列を生成する場合は、上記のように「カラム名 + interval 1 day」で可能です。

同様に1月単位であれば、「カラム名 + interval 1 month」となります。

単純な連番は、最初のselectで「1」などの開始番号を指定し、次のselectで「カラム名 + 1」で生成できます。

仮想テーブルをcross joinし、count + case whenで日付ごとにカウント

with recursive句で生成した日付の配列と「顧客ID」「面談日」「契約日」をselectした仮想テーブル同士をcross joinします。

総当たりで結合されるので、count(case when 配列の日付 = イベントの日付 then 顧客ID end) と記述します。

この条件処理を行うことで、イベント（「面談日」「契約日」）の日付が配列の日付と合致する場合に、顧客IDを返してカウントすることができます。

select
  d.date
  ,count(case when d.date = e.interview_date then e.id end) as interview_cnt
  ,count(case when d.date = e.contract_date then e.id end) as contract_cnt
from
  generate_d_array as d
  cross join event as e
group by
  1
order by
  1

実際にクエリを実行すると、「面談日」「契約日」を日付ごとに集計できます。