集計でサマリを作る

目標

GROUP BY、HAVING、FILTER、date_truncを使って、元の行を意味のある要約に畳めるようになります。

なぜ重要なのか

集計は、SQLで最もよく使われ、最も静かに間違える領域です。エラーが出ずに、数字だけがおかしくなるからです。

特に注意すべきことが3つあります。1つ目は、集計関数がNULLを無視するので、平均の分母が予想と違う場合があることです。2つ目は、このデータには返品伝票がマイナスの数量で混ざっていて、そのまま合計すると販売数量が減ることです。3つ目は、タイムゾーンを含むカラムを月単位で切るとき、基準のタイムゾーンを明示しないと、サーバー設定によって答えが変わることです。

処理の順序を覚えておけば、大半の混乱が整理されます。FROM、WHERE、GROUP BY、集計、HAVING、SELECT、ORDER BYの順です。WHEREは畳む前で、HAVINGは畳んだ後だという事実1つで、「なぜWHEREにcountを使えないのか」が説明できます。

ステップ

ステータス別の注文件数を含むビューv_status_countを作ります。カラムはstatus、order_countです。
statusがpaid、shipped、deliveredの注文だけを対象に、チャネル別の売上合計を含むビューv_channel_revenueを作ります。カラムはchannel、revenueです。
注文が5件以上の顧客だけを含むビューv_big_customersを作ります。カラムはcustomer_id、order_countです。
チャネルごとに、決済完了の件数とキャンセルの件数を一度に含むビューv_status_splitを作ります。カラムはchannel、paid_count、cancelled_countで、すべてのチャネルが残る必要があります。
完了ステータスの注文の月別売上を含むビューv_monthly_revenueを作ります。カラムはmonth(date型)、revenueで、月を切るときはordered_at AT TIME ZONE 'Asia/Seoul'を基準にします。
カテゴリ別の平均価格を、小数点なしに丸めて含むビューv_category_avgを作ります。カラムはcategory、avg_priceです。
販売数量の上位10個の商品を含むビューv_top_productsを作ります。カラムはproduct_id、name、sold_qtyで、数量が正の行だけを合計し、並べ替えは数量の降順、同じならproduct_idの昇順です。
等級別の顧客数、売上、1人当たり売上を含むビューv_tier_arpuを作ります。カラムはtier、customer_count、revenue、arpuで、売上は完了ステータスの注文だけを数え、注文がない顧客も分母に含めます。arpuは小数第2位で丸めます。

参考

処理順序: FROM → WHERE → GROUP BY → 集計 → HAVING → SELECT → ORDER BY
count(*) FILTER (WHERE 조건)の構文を、ステップ4で使います(プレースホルダーは条件です)。
よくある間違い1: ステップ7でマイナスの数量をそのまま足すと、順位が変わります。
よくある間違い2: ステップ8で、結合のために顧客が複数回数えられないように、重複を除く必要があります。

ステータス別の注文件数

注文のステータス別の件数を含むビューv_status_countを作ります。カラムはstatus、order_countです。

何を基準に畳むかを決めて、行を数える集計関数を使います。

チャネル別の売上合計

statusがpaid、shipped、deliveredの注文だけを対象に、チャネル別の売上合計を含むビューv_channel_revenueを作ります。カラムはchannel、revenueです。

キャンセルと返金、保留中の注文は、売上から除く必要があります。畳む前に絞り込む句がどこか、考えてみてください。

注文の多い顧客だけを残す

注文が5件以上の顧客だけを含むビューv_big_customersを作ります。カラムはcustomer_id、order_countです。

集計結果に対する条件は、WHEREではかけられません。畳んだ後に絞り込む句が別にあります。

1回のスキャンで2つを数える

チャネルごとに、決済完了の件数とキャンセルの件数を一度に含むビューv_status_splitを作ります。カラムはchannel、paid_count、cancelled_countで、すべてのチャネルが残る必要があります。

集計関数の後ろに条件を付けられる句があります。チャネルはすべて残る必要があります。

月別の売上集計

完了ステータスの注文の月別売上を含むビューv_monthly_revenueを作ります。カラムはmonth(date型)、revenueで、月を切るときはordered_at AT TIME ZONE 'Asia/Seoul'を基準にします。

タイムゾーンを含むカラムを切るときは、基準のタイムゾーンを明示すると、どこで実行しても同じ答えが出ます。

カテゴリ別の平均価格

カテゴリ別の平均価格を、小数点なしに丸めて含むビューv_category_avgを作ります。カラムはcategory、avg_priceです。

小数点なしに丸める関数を使います。桁数を指定しなければ、整数に丸められます。

販売数量の上位10個の商品

販売数量の上位10個の商品を含むビューv_top_productsを作ります。カラムはproduct_id、name、sold_qtyで、数量が正の行だけを合計し、並べ替えは数量の降順、同じならproduct_idの昇順です。

このデータでは、返品がマイナスの数量で入っています。そのまま足すと、順位が入れ替わります。

等級別の1人当たり売上

等級別の顧客数、売上、1人当たり売上を含むビューv_tier_arpuを作ります。カラムはtier、customer_count、revenue、arpuで、売上は完了ステータスの注文だけを数え、注文がない顧客も分母に含めます。arpuは小数第2位で丸めます。

注文がない顧客も、分母に入る必要があります。結合のために、顧客が複数回数えられないようにしてください。