日ごとの更新件数をEctoで集計したいとき

データベースへは時刻（日時）で記録しておき、利用するときに日付単位で集計したいときがあります。

例えば更新の頻度を日付単位で集計したいばあい、次のような SQL になると思います。

select
  date(updated_at) as updated_date,
  count(1)
from
  items
group by
  updated_date
order by
  updated_date

これを Ecto で実現したい、というのが今回のテーマです。

ちなみに補足をしておくと。 Ecto はデータベース操作のための Elixir のライブラリです。ですので今回も Elixir の話題です。念のため。

前提

ここで Repo はリポジトリモジュール、Item は items テーブルのスキーマモジュール、Ecto.Query モジュールは import 済みとします。

hexdocs.pm

日付のみを取得する

まず、データベース上で日付のみを取得したいので、時刻から日付を取得するデータベースの関数 date を利用することが前提になります。

データベースの関数を利用するには、記述した SQL 片を直接データベースに適用する Ecto.Query.API.fragment/1 を利用します。

hexdocs.pm

from(
  i in Item,
  select: fragment("date(?) as updated_date", i.updated_at)
)
|> Repo.all()

生成される SQL は次のようになります（読みやすいように整形しています）。

SELECT
  date(i0."updated_at") as updated_date
 FROM
   "items" AS i0

頻度を数える

頻度を得るためには group by を利用しますが、group_by: "updated_date" や group_by: i.updated_date とは記述できません。 updated_date は SQL に「直書き」された名前なので、参照するばあいにも fragment を使う必要があります。

from(
  i in Item,
  group_by: fragment("updated_date"),
  select: %{
    updated_date: fragment("date(?) as updated_date", i.updated_at),
    count: count(1)
  }
)
|> Repo.all()

生成される SQL を確認すると、最初に書いた SQL と同じ構造になっていることがわかります。これでまずは目的を達成することができました。

SELECT
  date(i0."updated_at") as updated_date,
  count(1)
FROM
  "items" AS i0
GROUP BY
  updated_date

…できましたが。クエリとして融通が効きませんし、SQL 片を直接組み立てているので危うさがあります。

サブクエリを使う

もっとよい方法がないかと Elixir Forum を探した結果、サブクエリ Ecto.Query.subquery/2を使う方法に行き当たりました。

elixirforum.com

hexdocs.pm

まず、日付だけを抽出するクエリを組み立てます。このとき select のパラメータはマップ形式にしてキーで値を引けるようにしておきます。

dates =
  from(
    i in Item,
    select: %{updated_date: fragment("date(?)", i.updated_at)}
  )

日付のクエリを subquery を使って頻度を取得するクエリに埋め込みます。日付の値をキーで取得できるようにしておいたので、新しいクエリの中でもそのキーを利用することができます。

from(
  d in subquery(dates),
  group_by: d.updated_date,
  select: %{
    updated_date: d.updated_date,
    count: count(1)
  }
)
|> Repo.all()

生成される SQL も、当然ですが、サブクエリで構築されています。

SELECT
  s0."updated_date",
  count(1)
FROM
  (
    SELECT
      date(si0."updated_at") AS "updated_date"
     FROM
      "items" AS si0
  ) AS s0
GROUP BY
  s0."updated_date"