列の交換：順列と組み合わせ

2問目は、「⁠列の切り替え」問題の発展版を練習してみましょう。

ある店舗で、売れた商品の明細を顧客ごとに記録する表2のようなテーブルがあるとします。列持ち形式のため、定義されている列数以上の商品が売れたときは記録できないという欠点を抱える設計ですが、最初はその点には目をつぶります（あとでこの欠点に対する解決策も示します⁠）⁠。

さて、このテーブルを使って、一緒に売れた商品のペアを単位としていろいろな分析を行うことを考えます。その際、このテーブルのままだと不都合なことが起きます。顧客IDが001と003の行を見ればわかるように、この2人は、商品の組み合わせとしては {時計、浄水器} という同じペアを買っています。しかしおそらく、テーブルに記録されるときは買った順番で記録したためでしょう、テーブル上では順序を入れ替えた、異なるペアとして存在しています。これでは、単純にitem_1、item_2を「SELECT DISTINCT」で選択したり、GROUP BY句で集約したとしても、正しい商品のペアが求められません。

そこで、表2のようなテーブルから、表3のように商品の並び順を無視した組み合わせを求めましょう。

言い方を変えると、順序を意識した「順序集合」を、順序を無視した「非順序集合」へ変換する、ということです。あるいは、学校で習った馴染み深い表現を使うなら、順列（Permutation）を組み合わせ（Combination）へ変換するのです。

CASE式を使えばこんなことも朝飯前です。リスト3のクエリを見てください（図3は実行結果⁠）⁠。

SELECT 
       CASE WHEN item_1 < item_2 THEN item_1
            ELSE item_2 END AS c1,
       CASE WHEN item_1 < item_2 THEN item_2
            ELSE item_1 END AS c2
  FROM Perm2;

   c1        c2
--------- ---------
時計       浄水器
携帯電話   携帯電話
時計       浄水器
携帯電話   携帯電話
インク     メガネ

item_1とitem_2は文字列ですので、不等号で比較可能な順序を持っています。ということは、最初の列「c1」に小さいほう、2番目の列「c2」に大きいほうを配置してやれば、同じ要素を持っていて順序だけ異なる順序集合同士も、同じ並び順に配置し直すことができるわけです。あとは、重複行を排除してできあがり（リスト4、図4⁠）⁠。

SELECT DISTINCT 
       CASE WHEN item_1 < item_2 THEN item_1
            ELSE item_2 END AS c1,
       CASE WHEN item_1 < item_2 THEN item_2
            ELSE item_1 END AS c2
  FROM Perm2;

   c1        c2
--------- ---------
インク     メガネ
携帯電話   携帯電話
時計       浄水器

この方法は、比較したい列が文字型でも数値型でも日付型でも、とにかく順序づけられるデータ型ならば適用できる汎用性の高い方法です。

ではこの問題を一般化します。商品列を3列に増やした場合（表4）はどうなるでしょう。

やりたいことは同じなので、求めたい結果は表5のようなものになります。

この場合も、原理的には2列の場合と同じように、不等号の比較条件をCASE式で記述することができるのですが、条件がかなり複雑になりますし、4列、5列…と増えていった場合にはさらに厳しくなります。ちょっと読むに堪えないクエリになるでしょう。

こういう一般化したケースをなんとかしたいという相談が持ち込まれてきたとしたら、私ならリスト5、図5のように列持ちの形式を行持ちの形式に直す方法を奨めます。

CREATE VIEW CustItems (cust_id, item) AS
SELECT cust_id, item_1
  FROM Perm3
UNION ALL
SELECT cust_id, item_2
  FROM Perm3
UNION ALL
SELECT cust_id, item_3
  FROM Perm3;

cust_id  item
-------- -----------
001      浄水器
001      時計
001      ティッシュ
002      浄水器
002      ティッシュ
002      時計
003      ノート
003      カレンダー
003      時計
004      ノート
004      カレンダー
004      インク
005      ゲームソフト
005      文庫本
005      メガネ
006      文庫本
006      ゲームソフト
006      メガネ

いったんこの形式に直してしまえば、あとはリスト6のクエリで組み合わせへ変換できます（実行結果は図6⁠）⁠。

SELECT DISTINCT MIN(CI1.item) AS c1, 
       MIN (CI2.item) AS c2,
       MIN (CI3.item) AS c3
  FROM CustItems CI1
           INNER JOIN CustItems CI2
                   ON CI1.cust_id = CI2.cust_id
                  AND CI1.item < CI2.item
                 INNER JOIN CustItems CI3
                         ON CI2.cust_id = CI3.cust_id
                        AND CI2.item < CI3.item
 GROUP BY CI1.cust_id;

     c1           c2       c3
------------- ---------- -------
インク        カレンダー ノート
カレンダー    ノート     時計
ゲームソフト  メガネ     文庫本
ティッシュ    時計       浄水器

やはり不等号を使って商品ごとに順序づけを行う、という点はCASE式のときと同じですが、MIN関数を併用することで比較条件を非常に簡潔なものに抑えることができるのです。

このクエリの意味は、次のようなものです。

1. まず1人の顧客について、3つの商品の中から最小値を選択する（MIN(CI1.item)）
2. 次に、その最小値を除いた集合から最小値を選択する（MIN(CI2.item)）
3. 最後に、その値も除いた集合から最小値（最後なので1つしか残っていないが）を選択する（MIN(CI3.item)）

これなら、商品数が何列に増えても対応できます（ただし、この方法では、同じ商品の組み合わせは選択できなくなります。これに対応する方法は、みなさんも考えてみてください⁠）⁠。このことからテーブル設計においては、例外的なケースを除いて、なるべく列持ちよりは行持ちの形式を採用したほうが拡張的な要件にも耐える、という教訓が得られます。安易に配列やフラットファイルをそのままテーブルの「列」に写し取る無芸な設計をしてしまうと、あとあとまで大きな禍根を抱え込むことになるので、注意が必要です。