集計用のキー列をテーブルに持つべきか
次に、
たとえば、
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
テーブルに存在する列では集計のキーになりませんから、
そこで、
列を追加したら、
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;
なお、
-- 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;
極めて明快な方法ですから、
難点①:集計キーのグルーピングに頻繁に変更が生じる場合
上の例で言えば、
難点②:テーブルのサイズが増える
これは特に、
もしこうした欠点が問題になる場合は、
それには、
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_