GROUP BY と集約
GROUP BY は完全なグループキーの組で入力を分け、各群の集約を維持して、一群を一結果行へ縮める。キー列を増やすと既存群を細分できる。各元行は一群に属し、各群は一行を返す。この二規則が数値と結果の粒度を決める。
COUNT(*) は全行、COUNT(column)、SUM(column)、AVG(column) はその列の NULL を除く。NULL が入ると、行数と非 NULL 値数が分かれる。
GROUP BY の仕組み
地域ごとの注文と金額を求める例を使う。
SELECT
region,
COUNT(*) AS orders,
COUNT(amount) AS recorded_amounts,
SUM(amount) AS revenue,
AVG(amount) AS average_amount
FROM sales
GROUP BY region;
現在のキーは (region)。region、channel にすれば二要素の組となり、North / online と North / store は別群になる。
COUNT(*) は全行、COUNT(amount) は非 NULL 金額だけを数える。SUM(amount) は非 NULL を足し、一つもなければ NULL。AVG(amount) の分母は COUNT(amount) であり、COUNT(*) ではない。四集約は各群の一行に四値を作り、四行を作るわけではない。
GROUP BY のステップ
既定の七行を次の順で読み、region で分ける。
| order | region | channel | amount |
|---|---|---|---|
| o1 | North | online | 120 |
| o2 | South | store | 90 |
| o3 | North | store | 80 |
| o4 | West | online | 40 |
| o5 | South | online | 60 |
| o6 | North | online | NULL |
| o7 | West | store | 110 |
- o1 で North を作り、COUNT(*)=1、COUNT(amount)=1、SUM=120。
- o2 で South を作り、両カウント 1、SUM=90。
- o3 は o1 と隣り合わなくても North なので、両カウント 2、SUM=200。
- o4 で West を作り、両カウント 1、SUM=40。
- o5 で South を更新し、両カウント 2、SUM=150。
- o6 は金額 NULL。North の COUNT(*) は 2 から 3、COUNT(amount) は 2、SUM は 200 のまま。
- o7 で West を更新し、両カウント 2、SUM=150。
North の平均は 200 / 2 = 100 で、3 では割らない。South と West は 150 / 2 = 75。各群が一行を返す。
| region | COUNT(*) | COUNT(amount) | SUM(amount) | AVG(amount) |
|---|---|---|---|---|
| North | 3 | 2 | 200 | 100 |
| South | 2 | 2 | 150 | 75 |
| West | 2 | 2 | 150 | 75 |
これは説明順で、SQL の順序保証には ORDER BY が必要だ。左に元表、中央に現在行の所属、右に結果を示す。最初の三行で仕組みを示してから反復を速め、NULL の更新は両カウントを区別できる速度に戻す。
粒度が結果を変える
| 集約方法 | キー | 群数 |
|---|---|---|
| GROUP BY なしの集約 | 全体で一群 | 1 |
| GROUP BY region | (region) | 3 |
| GROUP BY region, channel | (region, channel) | 6 |
channel を加えても region がキーに残るので、地域内を分けるだけで異なる地域を合流できない。所属は次のように変わる。
| 元の地域群 | 完全な新キー | 行 |
|---|---|---|
| North：o1、o3、o6 | (North, online) | o1、o6 |
| North：o1、o3、o6 | (North, store) | o3 |
| South：o2、o5 | (South, store) | o2 |
| South：o2、o5 | (South, online) | o5 |
| West：o4、o7 | (West, online) | o4 |
| West：o4、o7 | (West, store) | o7 |
North の三行一群は二群になるが、合わせると o1、o3、o6 のままで欠落も重複もない。他の地域も同じで 3→6 を説明し、計数欄は全体を 1→3→6 と示す。
地域粒度では一行が一地域、地域とチャネルなら一組を表す。地域の合計を複数チャネル行へ結合して再加算すると重複するため、下流の結合と再集約の粒度を決める必要がある。
GROUP BY のない集約は全入力を一つの全体群とし、キー列なしの一行を返す。元行をそのまま返す意味ではない。
SQL は論理結果を定め、物理方法は定めない。ハッシュ、ソート、索引、整列済み入力のストリーム、並行部分集約などが同じ群を作れる。利用前に、一行の意味、完全キー、NULL と分母、下流結合の粒度を確認する。
境界ケース
- 二行の金額が全て NULL なら COUNT(*)=2、COUNT(amount)=0、SUM=NULL、AVG=NULL。
- NULL キーは同じ群に入る。複合キーでは組全体で判定する。等値結合の
NULL = NULLが TRUE でないこととは別の規則だ。 - 空入力の GROUP BY region は群がなく、ゼロ行。
- 空入力でも GROUP BY なしの全体集約は一行を返す。両カウントは 0、SUM と AVG は NULL。
- 全キーが一意なら各群一行なので、結果行数は減らない。
よくある間違い
- SUM(amount)/COUNT(*) を AVG の代わりにする。NULL があれば分母が違う。
- GROUP BY region と共に channel をそのまま選ぶ。地域に複数チャネルがあれば一値に決まらず、集約かキー追加が必要だ。標準の一部の関数従属性や方言差もあるため、依存を明示する。
- 隣接で所属を決める。同キーは離れていても同群だ。
- NULL 行ごとに別群を作る。完全なグループキーが同じなら一緒にする。
- ORDER BY なしで、一回安定して見えた順序に依存する。
- 描画の桶からハッシュ集約の物理計画だと断定する。桶は論理所属を示すだけだ。
モデルの範囲
論理グループ、NULL、粒度、件数を扱う。照合規則、精度と桁あふれ、DISTINCT・順序付き集合の集約、HAVING、GROUPING SETS、関数従属性、最適化器、索引、並行部分集約、可視性、方言差は省く。実際のクエリは対象 DB の SQL と計画規則で確認する必要がある。