SQLアタマアカデミー
第3回 テーブル設計のグレーゾーン~毒と薬は紙一重 (3)集計用のキー列をテーブルに持つべきか
集計用のキー列をテーブルに持つべきか
次に,
たとえば,
図8 集計用のキーがないテーブル
item_ | item_ | price |
---|---|---|
001 | 洗剤 | 400 |
002 | パン | 200 |
003 | ボールペン | 100 |
004 | しゃもじ | 300 |
005 | クッキー | 550 |
006 | ビール | 280 |
007 | はさみ | 350 |
008 | コップ | 600 |
009 | 箸 | 320 |
このテーブルをもとにして,
- Aグループ:洗剤,
しゃもじ, コップ, 箸 - Bグループ:パン,
クッキー, ビール - Cグループ:ボールペン,
はさみ
すると求めたい結果は次のようになります。
- 結果
item_
grp avg_ price -------- --------- A:台所用品 405 B:食品 343 C:オフィス用品 225
テーブルに存在する列では集計のキーになりませんから,
そこで,
図9 集計用のキーを追加したテーブル
列を追加したら,
リスト2 集計キーの更新を行うクエリ
UPDATE Items
SET item_grp = CASE WHEN item_no IN ('001', '004', '008', '009') THEN 'A'
WHEN item_no IN ('002', '005', '006') THEN 'B'
WHEN item_no IN ('003', '007') THEN 'C'
ELSE NULL END;
なお,
リスト3 ABC別々に更新を行う
-- Aグループの更新
UPDATE Items
SET item_grp = 'A'
WHERE item_no IN ('001', '004', '008', '009');
-- Bグループの更新
UPDATE Items
SET item_grp = 'B'
WHERE item_no IN ('002', '005', '006');
-- Cグループの更新
UPDATE Items
SET item_grp = 'C'
WHERE item_no IN ('003', '007');
この集計キーの列を作れたら,
SELECT item_grp,
AVG(price) AS avg_price
FROM Items
GROUP BY item_grp;
極めて明快な方法ですから,
難点①:集計キーのグルーピングに頻繁に変更が生じる場合
上の例で言えば,
難点②:テーブルのサイズが増える
これは特に,
もしこうした欠点が問題になる場合は,
それには,
リスト4 使い捨てキーの例
SELECT CASE WHEN item_no IN ('001', '004', '008', '009') THEN 'A'
WHEN item_no IN ('002', '005', '006') THEN 'B'
WHEN item_no IN ('003', '007') THEN 'C'
ELSE NULL END AS item_grp,
AVG(price) AS avg_price
FROM Items
GROUP BY CASE WHEN item_no IN ('001', '004', '008', '009') THEN 'A'
WHEN item_no IN ('002', '005', '006') THEN 'B'
WHEN item_no IN ('003', '007') THEN 'C'
ELSE NULL END;
item_
- 注4)
- このCASE式の使い方については、
「SQLアタマ養成講座」 で詳しく解説したので、ロジックの詳細はそちらを参照ください。
バックナンバー
SQLアタマアカデミー
- 最終回 OLAP関数で強力な統計処理を実現!―手続き型から理解するSQL (5)集合指向と手続き型
- 最終回 OLAP関数で強力な統計処理を実現!―手続き型から理解するSQL (4)OLAP関数と集約関数を組み合わせる
- 最終回 OLAP関数で強力な統計処理を実現!―手続き型から理解するSQL (3)OLAP専用関数
- 最終回 OLAP関数で強力な統計処理を実現!―手続き型から理解するSQL (2)OLAP関数の基本構文
- 最終回 OLAP関数で強力な統計処理を実現!―手続き型から理解するSQL (1)OLAP関数とは何か
- 第10回 結合大全 (5)非等値結合
- 第10回 結合大全 (4)自己結合
- 第10回 結合大全 (3)外部結合
- 第10回 結合大全 (2)内部結合
- 第10回 結合大全 (1)クロス結合