ホーム ＞ リレーショナル・データベースの世界 ＞
「SQL緊急救命室」サポートページ
このサイトは、『Web+DB Press』Vol.62 より連載されている「SQL緊急救命室」のサポートサイトです。主に演習問題の解答を掲載しています。
疑問、間違いの指摘なども随時受け付けております。メール、ゲストブック、ブログのいずれからでもどうぞ。
2011年 4月（Vol.62）：第1回「サブクエリ・パラノイア」
・初出は『WEB+DB PRESS Vol.62』
。演習問題の解答を以下に示します。
/* ヘレンの解 */
SELECT cust_id,
SUM(CASE WHEN min_seq = 1 THEN price ELSE 0 END)
- SUM(CASE WHEN max_seq = 1 THEN price ELSE 0 END) AS diff
FROM (SELECT cust_id, price,
ROW_NUMBER() OVER (PARTITION BY cust_id
ORDER BY seq) AS min_seq,
ROW_NUMBER() OVER (PARTITION BY cust_id
ORDER BY seq DESC) AS max_seq
FROM Receipts ) WORK
WHERE WORK.min_seq = 1
OR WORK.max_seq = 1
GROUP BY cust_id;
本文図 13 の状態のテーブルからスタートしましょう。二つの ROW_NUMBER 関数によって min_seq および max_seq という二つの列が追加されています。min_seq が 1 のレコードは、同一の顧客 ID において枝番が最小であることを意味します。max_seq が 1 のレコードは、同一の顧客 ID において枝番が最大であることを意味します。
Receipts
cust_id
（顧客ID） | seq
（枝番） | price
（金額） | min_seq
（昇順連番） | max_seq
（降順連番） |
|---|
|
| A | 1 | 500 | 1 | 3 |
| A | 2 | 1000 | 2 | 2 |
| A | 3 | 700 | 3 | 1 |
| B | 5 | 100 | 1 | 5 |
| B | 6 | 5000 | 2 | 4 |
| B | 7 | 300 | 3 | 3 |
| B | 9 | 200 | 4 | 2 |
| B | 12 | 1000 | 5 | 1 |
| C | 10 | 600 | 1 | 4 |
| C | 20 | 100 | 2 | 3 |
| C | 45 | 200 | 3 | 2 |
| C | 70 | 50 | 4 | 1 |
| D | 3 | 2000 | 1 | 1 |
ここから、ヘレンの解では、WHERE句の「WORK.min_seq = 1 OR WORK.max_seq = 1」という条件によって、枝番が最小または最大のレコードだけに限定しています。これによって、対象のレコード集合は次のような状態になります。
Receipts
cust_id
（顧客ID） | seq
（枝番） | price
（金額） | min_seq
（昇順連番） | max_seq
（降順連番） |
|---|
|
| A | 1 | 500 | 1 | 3 |
| A | 3 | 700 | 3 | 1 |
| B | 5 | 100 | 1 | 5 |
| B | 12 | 1000 | 5 | 1 |
| C | 10 | 600 | 1 | 4 |
| C | 70 | 50 | 4 | 1 |
| D | 3 | 2000 | 1 | 1 |
これで、顧客ID ごとに見ると、レコード数は1または2になります（顧客Dのみ1）。後は、min_seq = 1 のレコードの金額から max_seq = 1 のレコードの金額を引けばいいのですが、SQL では通常、レコードをまたいだ計算は出来ません。しかし、集約関数の中で CASE 式を使うことで、「条件付き集計」が可能になります。
例えば、「SUM(CASE WHEN min_seq = 1 THEN price ELSE 0 END)」という式は、min_seq = 1 という条件に合致するレコードの金額（price）のみを集計の対象としているのです。同様に、「SUM(CASE WHEN max_seq = 1 THEN price ELSE 0 END)」は、max_seq = 1 という条件に合致するレコードだけを集計の対象にしています。このように、集約関数の中で CASE 式を使うと、いわばレコードを「間引き」した形での集計が可能になるわけです。
ところで、このように聞くと「だったら、WHERE句の WORK.min_seq = 1 OR WORK.max_seq = 1 という条件は不要なんじゃないの？ どうせ条件に合致しないレコードは CASE式で間引くんだから」と思う人もいるかもしれません。これは正しい答えを出すことだけを考えるならその通りです。ただし、集約関数はレコードをソートするため、比較的コストの高い操作です。だから、事前にソート対象のレコード数を減らしておいた方が、パフォーマンスが良くなるのです。そのために本文中のコードでは事前にWHERE句でレコードの絞込みを行うようにしているのです。
作成者：ミック
作成日：2011/04/24
最終更新日：2011/04/24
この作品は、クリエイティブ・コモンズ・ライセンスの下でライセンスされています。
