Zenn
Open2

WITH句、「集計」と「詳細」。

SQL
naon708naon708

「集計」と「詳細表示」を同時にやりたい時の定番パターンが「WITH句で集計 → JOIN」

-- 集計
with hoge_stats as (
  select
    h.id as hoge_id,
    count(f.id) as fuga_count
  from hoges h
  inner join fugas f on h.id = f.hoge_id
  where h.id in (1111, 1112)
  group by h.id
)

-- 詳細表示
select
  h.id,
  h.name,
  h.updated_at,
  hs.fuga_count
from hoges h
inner join fugas h.id = f.hoge_id
inner join hoge_stats hs on h.id = hs.hoge_id -- withで作成した集計用テーブルと結合
where h.id in (1111, 1112)
order by
  hs.fuga_count DESC,
  h.name,
  h.updated_at DESC
naon708naon708

集計したうえで、詳細も表示したい場合に with を検討。

  • hogeごとのfuga数をカウントしたい（GROUP BY h.id）
  • でも個別のhogeの詳細も表示したい（hoge.id, hoge.name, hoge.updated_at）
  • この2つは同時にできない…
    • 「あ、これ2段階に分けるしかないじゃん」
STEP1: hogeごとのfuga数を先に計算する（WITH句）
STEP2: 元データにSTEP1の結果をくっつける（JOIN）

まとめ

  • 「hogeごとの集計」と「個別hogeの詳細表示」を両立させるため
  • 普通のGROUP BYだと、どっちか一方しかできない
  • WITH句で「集計結果」を別テーブルとして作っておいて、後でJOINする