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

次に、集計用のキー列を実テーブルに持つことの是非について考えてみます。集計用のキーとは、その名のとおり、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文をビューとして保存しておくのもよいでしょう。そうすれば、いつでも手軽に集計結果を引き出せます。ただし今度は、そのたびにテーブル検索が実行されるため、時間がかかることは忘れないでください。