Haskell製ORMの使い方を学ぶチュートリアル第四回目の今回はデータの集計について学んでいきましょう。

• 初回から読みたい方はHaskellでDBを操作しよう　Beamチュートリアル　Part 1
• 前回のチュートリアルはHaskellでDBを操作しよう　Beamチュートリアル　Part 3
• このチュートリアルのソースコードはGithub

集計関数

SQLで使用可能なSUM, COUNT, MAX, MIN, AVGなどの関数はBeamでも使用可能です。以下の表に汎用集計関数を示します。

Beamの集計関数	対応するSQL関数
count_	COUNT(x)
countAll_	COUNT(*)に対応
avg_	AVG
sum_	SUM
min_	MIN
max_	MAX
every_	EVERY(x)
any_	ANY(x)
some_	SOME(x)

尚、本稿では詳しく触れませんが、上記に加えてQuantified aggregate functions(定量化集計関数)があります。これは汎用集計関数に数量詞を指定することが可能で汎用集計の各関数にOverがつきます。例えばcount_に対応する関数はcountOver_になります。

設定できる数量詞に関しては以下があります。

数量詞	説明
distinctInGroup_	AGG(DISTINCT ..)を生成する
allInGroup_	AGG(ALL ..)に相当するが、明示的にALLは生成しない
allInGroupExplicitly_	allInGroupと異なり明示的にALLを生成します。

定量子化集計関数の詳細はHackageをご覧ください。

COUNTを使う

まず簡単はデータのカラム数をカウントするサンプルを見てみましょう。集計を使うときにはaggregate_関数を使います。posts_tagsテーブルのレコード数を数えてみるコードを見てみましょう。

sample21 :: Connection -> IO ()
sample21 c = do
  li <- runBeamPostgresDebug putStrLn c $ runSelectReturningList $ select $ do
    aggregate_ (\pt -> (as_ @Int32 $ count_ $ posttagId pt)) $ all_ (posts_tags blogDatabase)
  print li

生成されるSQL

SELECT COUNT("t0"."id") AS "res0" FROM "posts_tags" AS "t0"

今回はall_関数で全てのレコードを取得しましたが、filter_関数を用いて条件付けをしてもOKです。aggregate_関数で返す値をCOUNTのみとしましたが、他の値と返すこともできます。次のGROUP BYの例で見ていきましょう。

GROUP BY句による集計

GROUP BYを使う場合を以下のようなサンプルで見てみましょう。GROUP BYで重複データを集約して数を数えてみます。aggregate_関数内でgroup_関数を使うことで実現できます。

以下の例はposts_tagsテーブルのレコードをpost__idでグループ化してレコード数をグループごとに集計しpost__idとカウント数を取得する作業をしています。

sample22 :: Connection -> IO ()
sample22 c = do
  li <- runBeamPostgresDebug putStrLn c $ runSelectReturningList $ select $ do
    aggregate_ (\pt -> (group_ $ posttagPost pt, as_ @Int32 $ countAll_)) $ all_ (posts_tags blogDatabase)
  print li

生成されるSQL

SELECT "t0"."post__id" AS "res0", COUNT(*) AS "res1" FROM "posts_tags" AS "t0" GROUP BY "t0"."post__id"

今回はpost__idの数を数えるのではなくcountAll_関数を使ってレコード数を数えました。countAll_を使うとCOUNT(*)の結果を得ることができます。

複数のテーブルの集計

結合された複数のテーブルについて集計する場合も基本的には単一テーブルと同じです。ただaggregate_関数の第一引数に入れる関数の第一引数（誤記ではありません）の型が変わる点に注意して下さい。これはテーブルを結合した際のfilter_関数と同じ使い方です。

ここでは多対多のサンプルで紹介したケースでpost__slugカラムごとに集計して数を数えるクエリを書いてみましょう。

sample23 :: Connection -> IO ()
sample23 c = do
  li <- runBeamPostgresDebug putStrLn c $ runSelectReturningList $ select $ do
    aggregate_ (\(p, t) -> (group_ $ postSlug p, as_ @Int32 $ countAll_)) $ do
      manyToMany_ (posts_tags blogDatabase)
                  posttagPost
                  posttagTag
                  (all_ (posts blogDatabase))
                  (all_ (tags blogDatabase))
  print li

生成されるSQL

SELECT "t0"."slug" AS "res0", COUNT(*) AS "res1"
FROM "posts" AS "t0" CROSS JOIN "tags" AS "t1" INNER JOIN "posts_tags" AS "t2" ON (("t2"."post__id") = ("t0"."id")) AND (("t2"."tag__id") = ("t1"."id"))
GROUP BY "t0"."slug"

HAVING

SQLではGROUP BY句を使った場合にはWHERE句ではなくHAVING句で抽出を行います。Beamでは特にHAVING用の書き方は用意されていません。filter_やguard_関数を使ってデータ抽出を行うとBeamが自動的にWHERE句かHAVING句かを判断してSQLを生成します。以下似サンプルコードを示します。

ここではポストテーブルとカテゴリーテーブルを結合してカテゴリーごとに集約し、その数を数え、カテゴリーSlugが”we”と前方一致するかつカウント数が2となるデータを抽出しています。

sample24 :: Connection -> IO ()
sample24 c = do
  li <- runBeamPostgresDebug putStrLn c $ runSelectReturningList $ select $ do
    filter_ (\(ca, cnt) -> categorySlug ca `like_` "we%" &&. cnt ==. 2) $ do
      aggregate_ (\(_, ca) -> (group_ ca, as_ @Int32 $ countAll_)) $ do
        po <- all_ (posts blogDatabase)
        ca <- related_ (categories blogDatabase) (postCategory po)
        pure (po, ca)

  print li

生成されるSQL

SELECT "t1"."id" AS "res0", "t1"."name" AS "res1", "t1"."slug" AS "res2", "t1"."created_at" AS "res3", "t1"."updated_at" AS "res4", COUNT(*) AS "res5" FROM "posts" AS "t0" INNER JOIN "categories" AS "t1" ON ("t0"."category__id") = ("t1"."id") GROUP BY "t1"."id", "t1"."name", "t1"."slug", "t1"."created_at", "t1"."updated_at" HAVING (("t1"."slug") LIKE ('we%')) AND ((COUNT(*)) = (2))

まとめ

ここまでのチュートリアルでクエリの結合と集計を行うかなり複雑なクエリを書けるようになったと思います。次からはデータの挿入、更新、削除について学んでいきましょう。