集計用のキー列をテーブルに持つべきか

次に，集計用のキー列を実テーブルに持つことの是非について考えてみます。集計用のキーとは，その名のとおり，GROUP BY句のキーになる列です。このキー列が最初から入力データに存在していれば，テーブルにも自然に保持することになるので，特に問題はありません。考えなければならないのは，最初はテーブルに存在しないキー列の扱い方です。

たとえば，図8のような商品を管理するテーブルを考えます。

このテーブルをもとにして，各商品を「A：台所用品」「B：食品」「C：オフィス用品」の3グループに分類して，グループごとの平均価格を示すレポートを算出するには，どうすればよいでしょう。分類は具体的に次のようにします。

• Aグループ：洗剤，しゃもじ，コップ，箸
• Bグループ：パン，クッキー，ビール
• Cグループ：ボールペン，はさみ

すると求めたい結果は次のようになります。

結果

item_grp           avg_price
--------           ---------
A：台所用品           405
B：食品              343
C：オフィス用品         225

テーブルに存在する列では集計のキーになりませんから，キーを新たに作る必要があることは明らかです。

そこで，一番単純に考えるなら図9のように集計キーを列として追加する方法が考えられます。

列を追加したら，この新しい列に値を入れるには，リスト2のようなUPDATE文ひとつでできます（注4）。

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;

なお，この際，UPDATE文をリスト3のように3つに分けるのは，一般的にSQLの発行回数が多く，時間もかかって不経済です（この場合は，item_noの主キーのインデックスが使えるので，断言はできませんが）。

-- 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');

この集計キーの列を作れたら，あとはこれをGROUPBY句に指定すれば，目的の平均値を得ることが可能になります。

SELECT item_grp,
       AVG(price) AS avg_price
  FROM Items
 GROUP BY item_grp;

極めて明快な方法ですから，この方法を使っている方も多いと思います。私も，基本的にこの方法に大きな問題はないと考えています。今回紹介する中では，最も“白”に近いと思います。それでも，難点として次の2つを挙げることができます。

難点①：集計キーのグルーピングに頻繁に変更が生じる場合

上の例で言えば，商品の分類の基準が頻繁に変わるようなケースを考えてもらえばイメージが湧くでしょう。いま，商品の用途ごとに3種類のグループ分けを行いましたが，たとえばこれを，「500円以上」「200円以上500円未満」「200円未満」のように，値段を基準に分類した場合も調べてみたい，という要望がクライアントから寄せられるかもしれません。そういう場合，固定的な列に集計キーを持っていると，もう一度UPDATE文で更新するか，新しい集計キーの列を追加する必要が生じます。

難点②：テーブルのサイズが増える

これは特に，集計キーの列を複数追加した場合に顕著な問題として現れます。当然のことながら，集計キーなしの最もシンプルなテーブルに比べれば，1列追加するごとに，最低でも，行数 × 1列分のサイズだけの追加領域を必要とします。したがって，行数が多いテーブルほど，列を追加したときに領域を多く消費することになります。また，列を追加し，更新するSQLの手間も勘定に入れなければなりません。

もしこうした欠点が問題になる場合は，集計キー列なしのシンプルなテーブルだけを使う方法もあります。それには，SELECT文の中で一時的に集計キーを算出すればよいのです。いわばそのクエリでのみ有効な「使い捨てキー」を作ってやるのです。

それには，リスト4のように，SELECT句とGROUPBY句の中に，さきほどUPDATE文で使ったCASE式を埋め込めばOKです。

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_grp列の内容をそのまま展開してGROUP BY句に「代入」しているわけです注5。この方法ならば，テーブルのディスク消費量を気にすることなく，気軽に集計キーをいくらでも組替えることが可能です。お望みなら，よく使う集計キーごとに，このSELECT文をビューとして保存しておくのもよいでしょう。そうすれば，いつでも手軽に集計結果を引き出せます。ただし今度は，そのたびにテーブル検索が実行されるため，時間がかかることは忘れないでください。

注4）

このCASE式の使い方については、「SQLアタマ養成講座」で詳しく解説したので、ロジックの詳細はそちらを参照ください。