SQLアタマアカデミー

第3回 テーブル設計のグレーゾーン~毒と薬は紙一重 (3)集計用のキー列をテーブルに持つべきか

この記事を読むのに必要な時間:およそ 2 分

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

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

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

図8 集計用のキーがないテーブル

item_no
(製品番号)
item_name
(製品名)
price
(値段)
001洗剤400
002パン200
003ボールペン100
004しゃもじ300
005クッキー550
006ビール280
007はさみ350
008コップ600
009320

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

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

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

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

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

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

図9 集計用のキーを追加したテーブル

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

リスト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;

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

リスト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');

この集計キーの列を作れたら,あとはこれを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です。

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

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

著者プロフィール

ミック

SI企業に勤務するDBエンジニア。主にデータウェアハウス業務に従事している。自身のサイト「リレーショナル・データベースの世界」でデータベースとSQLについての技術情報を公開している。『Web+DB Press』で「SQLアタマアカデミー」を連載中。

著書:『達人に学ぶ SQL徹底指南書』(翔泳社、2008)訳書:J.セルコ『SQLパズル 第2版』(翔泳社、2007)

SQLアタマアカデミー:サポートページ

コメント

コメントの記入