列の交換:順列と組み合わせ
2問目は、「列の切り替え」問題の発展版を練習してみましょう。
ある店舗で、売れた商品の明細を顧客ごとに記録する表2のようなテーブルがあるとします。列持ち形式のため、定義されている列数以上の商品が売れたときは記録できないという欠点を抱える設計ですが、最初はその点には目をつぶります(あとでこの欠点に対する解決策も示します)。
表2 Perm2テーブル
cust_id (顧客ID) | item_1 (商品1) | item_2 (商品2) |
001 | 時計 | 浄水器 |
002 | 携帯電話 | 携帯電話 |
003 | 浄水器 | 時計 |
004 | 携帯電話 | 携帯電話 |
005 | インク | メガネ |
さて、このテーブルを使って、一緒に売れた商品のペアを単位としていろいろな分析を行うことを考えます。その際、このテーブルのままだと不都合なことが起きます。顧客IDが001と003の行を見ればわかるように、この2人は、商品の組み合わせとしては {時計、浄水器} という同じペアを買っています。しかしおそらく、テーブルに記録されるときは買った順番で記録したためでしょう、テーブル上では順序を入れ替えた、異なるペアとして存在しています。これでは、単純にitem_1、item_2を「SELECT DISTINCT」で選択したり、GROUP BY句で集約したとしても、正しい商品のペアが求められません。
そこで、表2のようなテーブルから、表3のように商品の並び順を無視した組み合わせを求めましょう。
表3 商品の並び順を無視した組み合わせ
item_1 (商品1) | item_2 (商品2) |
時計 | 浄水器 |
携帯電話 | 携帯電話 |
インク | メガネ |
言い方を変えると、順序を意識した「順序集合」を、順序を無視した「非順序集合」へ変換する、ということです。あるいは、学校で習った馴染み深い表現を使うなら、順列(Permutation)を組み合わせ(Combination)へ変換するのです。
CASE式を使えばこんなことも朝飯前です。リスト3のクエリを見てください(図3は実行結果)。
item_1とitem_2は文字列ですので、不等号で比較可能な順序を持っています。ということは、最初の列「c1」に小さいほう、2番目の列「c2」に大きいほうを配置してやれば、同じ要素を持っていて順序だけ異なる順序集合同士も、同じ並び順に配置し直すことができるわけです。あとは、重複行を排除してできあがり(リスト4、図4)。
この方法は、比較したい列が文字型でも数値型でも日付型でも、とにかく順序づけられるデータ型ならば適用できる汎用性の高い方法です。
ではこの問題を一般化します。商品列を3列に増やした場合(表4)はどうなるでしょう。
表4 Perm3テーブル
cust_id (顧客ID) | item_1 (商品1) | item_2 (商品2) | item_3 (商品3) |
001 | 時計 | 浄水器 | ティッシュ |
002 | ティッシュ | 浄水器 | 時計 |
003 | カレンダー | ノート | 時計 |
004 | カレンダー | ノート | インク |
005 | 文庫本 | ゲームソフト | メガネ |
006 | 文庫本 | メガネ | ゲームソフト |
やりたいことは同じなので、求めたい結果は表5のようなものになります。
表5 求めたい結果
item_1 (商品1) | item_2 (商品2) | item_3 (商品3) |
インク | カレンダー | ノート |
カレンダー | ノート | 時計 |
ゲームソフト | メガネ | 文庫本 |
ティッシュ | 時計 | 浄水器 |
この場合も、原理的には2列の場合と同じように、不等号の比較条件をCASE式で記述することができるのですが、条件がかなり複雑になりますし、4列、5列…と増えていった場合にはさらに厳しくなります。ちょっと読むに堪えないクエリになるでしょう。
こういう一般化したケースをなんとかしたいという相談が持ち込まれてきたとしたら、私ならリスト5、図5のように列持ちの形式を行持ちの形式に直す方法を奨めます。
いったんこの形式に直してしまえば、あとはリスト6のクエリで組み合わせへ変換できます(実行結果は図6)。
やはり不等号を使って商品ごとに順序づけを行う、という点はCASE式のときと同じですが、MIN関数を併用することで比較条件を非常に簡潔なものに抑えることができるのです。
このクエリの意味は、次のようなものです。
- まず1人の顧客について、3つの商品の中から最小値を選択する(MIN(CI1.item))
- 次に、その最小値を除いた集合から最小値を選択する(MIN(CI2.item))
- 最後に、その値も除いた集合から最小値(最後なので1つしか残っていないが)を選択する(MIN(CI3.item))
これなら、商品数が何列に増えても対応できます(ただし、この方法では、同じ商品の組み合わせは選択できなくなります。これに対応する方法は、みなさんも考えてみてください)。このことからテーブル設計においては、例外的なケースを除いて、なるべく列持ちよりは行持ちの形式を採用したほうが拡張的な要件にも耐える、という教訓が得られます。安易に配列やフラットファイルをそのままテーブルの「列」に写し取る無芸な設計をしてしまうと、あとあとまで大きな禍根を抱え込むことになるので、注意が必要です。