SQLアタマ養成講座
第2回 SQL流条件分岐(2) 列の交換
列の交換:順列と組み合わせ
2問目は,
ある店舗で,
表2 Perm2テーブル
cust_ (顧客ID) | item_ (商品1) | item_ (商品2) |
---|---|---|
001 | 時計 | 浄水器 |
002 | 携帯電話 | 携帯電話 |
003 | 浄水器 | 時計 |
004 | 携帯電話 | 携帯電話 |
005 | インク | メガネ |
さて,
そこで,
表3 商品の並び順を無視した組み合わせ
item_ (商品1) | item_ (商品2) |
---|---|
時計 | 浄水器 |
携帯電話 | 携帯電話 |
インク | メガネ |
言い方を変えると,
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;
図3 リスト3の実行結果
c1 c2 --------- --------- 時計 浄水器 携帯電話 携帯電話 時計 浄水器 携帯電話 携帯電話 インク メガネ
item_
リスト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;
図4 リスト4の実行結果
c1 c2 --------- --------- インク メガネ 携帯電話 携帯電話 時計 浄水器
この方法は,
ではこの問題を一般化します。商品列を3列に増やした場合
表4 Perm3テーブル
cust_ (顧客ID) | item_ (商品1) | item_ (商品2) | item_ (商品3) |
---|---|---|---|
001 | 時計 | 浄水器 | ティッシュ |
002 | ティッシュ | 浄水器 | 時計 |
003 | カレンダー | ノート | 時計 |
004 | カレンダー | ノート | インク |
005 | 文庫本 | ゲームソフト | メガネ |
006 | 文庫本 | メガネ | ゲームソフト |
やりたいことは同じなので,
表5 求めたい結果
item_ (商品1) | item_ (商品2) | item_ (商品3) |
---|---|---|
インク | カレンダー | ノート |
カレンダー | ノート | 時計 |
ゲームソフト | メガネ | 文庫本 |
ティッシュ | 時計 | 浄水器 |
この場合も,
こういう一般化したケースをなんとかしたいという相談が持ち込まれてきたとしたら,
リスト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;
図5 リスト5の実行結果
cust_id item -------- ----------- 001 浄水器 001 時計 001 ティッシュ 002 浄水器 002 ティッシュ 002 時計 003 ノート 003 カレンダー 003 時計 004 ノート 004 カレンダー 004 インク 005 ゲームソフト 005 文庫本 005 メガネ 006 文庫本 006 ゲームソフト 006 メガネ
いったんこの形式に直してしまえば,
リスト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;
図6 リスト6の実行結果
c1 c2 c3 ------------- ---------- ------- インク カレンダー ノート カレンダー ノート 時計 ゲームソフト メガネ 文庫本 ティッシュ 時計 浄水器
やはり不等号を使って商品ごとに順序づけを行う,
このクエリの意味は,
- ① まず1人の顧客について,
3つの商品の中から最小値を選択する (MIN(CI1. item)) - ② 次に,
その最小値を除いた集合から最小値を選択する (MIN(CI2. item)) - ③ 最後に,
その値も除いた集合から最小値 (最後なので1つしか残っていないが) を選択する (MIN(CI3. item))
これなら,