集計 — GROUP BYが実際にやっていること

一言でいうと

GROUP BYは行をグループに畳み、畳んだ後にはグループを代表する値だけが残ります。そのため、SELECTリストに置けるのは、グループのキー、キーに従属する値、集計関数だけです。集計はエラーなしに間違える演算なので、結果を信じる前に、畳む前と後の合計が合っているかを確認します。

なぜ必要なのか

月次レポートのチャネル別売上が、財務チームの数字と3%違うという連絡が来ました。クエリはエラーなしに動き、数字ももっともらしく見えます。調べてみると、原因は3つでした。返品伝票がマイナスの数量で混ざっていました。月を切るときにサーバーのタイムゾーン(UTC)を使ったため、毎月1日の午前0時から9時までの韓国の注文が前の月に回っていました。そして、注文表を注文明細表と結合した後に注文金額を足したため、商品が複数ある注文の金額が何度も足されていました。

「チャネル別売上」や「ステータス別の注文数」のような質問は、個々の行ではなく要約を求めています。集計は複数の行を1つに畳む演算で、その畳む基準がGROUP BYです。畳んだ後は元の行が見えなくなるので、上の事例のように、誤って畳まれた結果は、見た目では区別できません。そのため、動作原理とあわせて、確認する方法も一緒に身につける必要があります。

どう動くのか

処理の順序を覚えておくと、混乱する部分の大半が解けます。

FROM -> WHERE -> GROUP BY -> aggregate -> HAVING -> SELECT -> ORDER BY -> LIMIT

WHEREは畳む前、HAVINGは畳んだ後にかかります。そのため、WHERE count(*) > 5は書けません。その時点では、まだ数える対象がないからです。行単位の条件は、WHEREに置くほうがよいです。畳む行自体が減って、仕事が少なくなります。
PostgreSQLでは、SELECTで付けたエイリアスを、ORDER BYとGROUP BYでは使えますが、WHEREとHAVINGでは使えません。そこでは、式を書き直す必要があります。
SELECTリストのカラムは、GROUP BYにあるか、集計関数の中にある必要があります。例外が1つあります。ある表の主キーでグループ化したなら、その表の他のカラムはキーに関数従属していて、グループごとに値が1つしかないので、PostgreSQLは、そのカラムをGROUP BYに書かなくても受け付けてくれます。

集計関数はNULLを無視します。avg(price)は、NULLの行を分母からも除きます。count(*)は行をすべて数えますが、count(price)はpriceがNULLでない行だけを数えます。そして、行が1つもないと、countを除く集計関数は、0ではなくNULLを返します。sumも同じです。0が必要なら、coalesce(sum(x), 0)のように明示します。

FILTER句は、同じグループで条件ごとに別の集計をするときに使います。条件が真の行だけが、その集計関数に入ります。

SELECT channel,
       count(*) FILTER (WHERE status = 'paid')      AS paid_count,
       count(*) FILTER (WHERE status = 'cancelled') AS cancelled_count
FROM orders
GROUP BY channel;

同じことをWHEREで行うには、クエリを2回動かして結合する必要があり、片方にないチャネルが結果から抜ける問題まで、別に処理しなければなりません。

時間単位の集計では、タイムゾーンを必ず固定します。timestamptzカラムにdate_trunc('month', ordered_at)を使うと、セッションのTimeZone設定を基準に切ります。同じクエリが、接続したツールによって違う答えを出す理由です。ordered_at AT TIME ZONE 'Asia/Seoul'は、その時刻がソウルで何時だったかを、タイムゾーンなしのタイムスタンプとして返すので、それを切れば、どこで実行しても同じ答えが出ます。PostgreSQLは、3番目の引数でタイムゾーンを受け取るdate_truncも提供しています。

現場で何が問題になるのか

結合が行を膨らませます。注文1件に商品が3つあれば、注文と注文明細を結合した結果には、その注文が3行で出ます。ここで注文金額を足すと、3回足されます。症状は、売上が平均商品数の分だけ膨らんでいることです。注文単位の金額は、注文表で先に集計するか、結合の前に、必要な単位に畳みます。

LEFT JOINが静かにINNER JOINになります。注文のない顧客まで分母に入れようと、顧客表を基準にLEFT JOINしたのに、WHEREに注文表のカラムの条件(o.status = 'paid')をかけると、注文のない顧客の行は、その条件でNULLになって除外されます。条件は、ON句に移す必要があります。同じクエリでcount(*)を使うと、注文のない顧客も1と数えるので、注文数を数えるときはcount(o.id)を使います。

マイナスと欠損を知らずに足します。返品伝票がマイナスの数量で入っていると、sum(quantity)が販売数量を静かに減らし、上位商品のランキングが入れ替わります。集計の前に、符号とNULLをまず見ます。

月の境界が9時間ずれます。韓国時間はUTCより9時間進んでいます。UTCで月を切ると、毎月1日の午前9時より前の韓国の注文が、前の月の売上として計上されます。月別の合計をすべて足すと全体と同じになるので、合計の検証では表に出ず、月ごとに少しだけ違います。

平均の平均を出します。店舗別の平均客単価をもう一度平均すると、注文が10件の店舗と1万件の店舗が、同じ重みを持ちます。全体の平均が必要なら、合計を合計で割ります。

整数の除算と丸めの型。PostgreSQLでは、整数同士を割ると、小数点以下が切り捨てられます。100 * a / bが0や99で出たら、これを疑い、100.0 * a / bのように、片方をnumericにします。また、小数点の桁数を受け取るround(x, 2)は、numericに対して定義されているので、double precisionの値に使うと、関数がないというエラーが出ます。round(x::numeric, 2)に変えます。

どう確認するのか

集計結果を出す前に、4つを突き合わせます。

-- 1. fan-out: rows vs distinct keys after the join
SELECT count(*), count(DISTINCT o.id)
FROM orders o JOIN order_items i ON i.order_id = o.id;

-- 2. sign and nulls before summing
SELECT min(quantity), max(quantity), count(*) - count(quantity) AS null_qty
FROM order_items;

-- 3. which time zone this session uses
SHOW TimeZone;

-- 4. the grouped totals must add up to the ungrouped total
SELECT (SELECT sum(total_amount) FROM orders) AS all_rows,
       (SELECT sum(revenue) FROM (SELECT channel, sum(total_amount) AS revenue
                                  FROM orders GROUP BY channel) g) AS by_group;

最初のクエリで2つの数字が違えば、結合が行を膨らませています。2つ目のクエリの最小値がマイナスであるか、NULLの個数が0でなければ、その行をどう扱うかを先に決めます。4つ目のクエリの2つの値が違えば、グループに畳む過程で、行が抜けたか重なったのです。そして、平均や1人当たりの値を出すときは、分母に何を入れたのかを、結果と一緒に書きます。注文のない顧客を入れるか除くかによって、数字が大きく変わり、どちらも正しい答えでありえるからです。

次の理論で見ること

すぐ後の読み物で、行を畳まずに、順位と累積を計算するウィンドウ関数を学びます。その後のラボで、ステータス別の件数、チャネル別売上、HAVINGとFILTER、ソウル基準の月別集計、マイナスの数量を除いた上位商品、注文のない顧客まで分母に入れた等級別の1人当たり売上を順に作りながら、この節の落とし穴を1つずつ自分で踏んでみます。