ここはとある街の総合病院。
ここには通常の診療科のほかに、
何軒もの病院をたらいまわしにされた、
それがSQL緊急救命室、
そう、
ロバート
救命室部長。腕の立つエンジニアだが、
SQLを学ぶ上で最も高いハードルとなるのが、
順序と手続きではなく、 集合と論理の観点から考えることだ。
―J.
（Morgan Kaufmann、
ループによる表現
（PM3:00 手術室。先ほどかつぎ込まれたばかりの患者を前にして、
ワイリー、
いーえ、
若僧が、
ああっ！
ワイリー、
カルテ：図1のような2つのテーブルがあるとする。Salesテーブルは企業ごとの会計年ごとの売り上げを記録している。ただし年は連続しているとは限らない。このデータから、
その結果を、
- より古い年のデータが存在しない場合：NULL
- 直近の年のデータより売り上げが伸びた場合：+
- 直近の年のデータより売り上げが減った場合：-
- 直近の年のデータより売り上げと同じ場合：=
登録後のSales2テーブルは図2のようになる。
SQLでループを置き換えるには
患者のコードはリスト1ですね。
リスト1 患者のコード ※ OracleのPL/
CREATE OR REPLACE PROCEDURE PROC_
問題のパターンとしては、
素朴な疑問なんですけど、
確かに、
その呪いから脱するにはどうすればいいんですか？
えっと……ウィンドウ関数
そうね。ループの部分を丸ごとそれで置き換えることが可能よ。あとは、
INSERT INTO Sales2
SELECT company,
year,
sale,
CASE WHEN MAX(company) ―①直前のレコードが同じ会社のレコードの場合
OVER (ORDER BY company, year
ROWS BETWEEN 1 PRECEDING ―②
AND 1 PRECEDING) = company
THEN CASE SIGN(sale - MAX(sale) ―③直前のレコードと売り上げを比較
OVER (ORDER BY company, year
ROWS BETWEEN 1 PRECEDING ―④
AND 1 PRECEDING) )
WHEN 0 THEN '='
WHEN 1 THEN '+'
WHEN -1 THEN '-'
ELSE NULL END
ELSE NULL END AS var
FROM Sales;ウィンドウ関数でループを置き換える
リスト2の2つのCASE式が、
この解で重要な技術は、ROWS BETWEENオプションを使っていることですROWS BETWEEN 1 PRECEDING AND 1 PRECEDINGは、
つまり、
リスト3、
SELECT company,
year,
sale,
MAX(company)
OVER (ORDER BY company, year
ROWS BETWEEN 1 PRECEDING
AND 1 PRECEDING) AS pre_company,
MAX(sale)
OVER (ORDER BY company, year
ROWS BETWEEN 1 PRECEDING
AND 1 PRECEDING) AS pre_sale
FROM Sales;company year sale pre_company pre_sale
------- ---- ----- ----------- --------
A 2002 50
A 2003 52 A 50
A 2004 55 A 52
A 2007 55 A 55
B 2001 27 A 55
B 2005 28 B 27
B 2006 28 B 28
B 2009 30 B 28
C 2001 40 B 30
C 2005 39 C 40
C 2006 38 C 39
C 2010 35 C 38
もし、ROWS BETWEEN 2 PRECEDING AND 2 PRECEDINGと、
またこの解では、
ROWS BETWEENオプションを利用できる環境
ただし、ROWS BETWEENオプションを使うことができません。
ですがこれは標準SQLの機能なので、
ウィンドウ関数が使えない場合のために、
相関サブクエリでループを置き換える
うーん、
自慢気に言うな。では、
INSERT INTO Sales2
SELECT company,
year,
sale,
CASE SIGN(sale - (SELECT sale ― 直近の年の売上げを選択
FROM Sales S2
WHERE S1.company = S2.company
AND S2.year =
(SELECT MAX(year) ― 直近の年を選択
FROM Sales S3
WHERE S1.company = S3.company
AND S1.year > S3.year )))
WHEN 0 THEN '='
WHEN 1 THEN '+'
WHEN -1 THEN '-'
ELSE NULL END AS var
FROM Sales S1;うむ、S1.という条件によって
相関サブクエリの中で非等値結合を使うことによってカレントレコードを起点とした集合を作るのは、
今、MAX(sale)を選択しています。これがすなわちS1.という不等式です。カレントレコードはS1.のほうですから、
S1.と、S1.の条件に合致するレコード集合の対応をマッピングすると図5のようになります
このように、
相関サブクエリの正体は入れ子集合
へえ～、
そうね。そういう見方をすれば、
この包含関係を図示すると、
奇妙なサンドイッチ
非等値結合は、
------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 12 | 108 | 2 (0)| 00:00:01 | | 1 | WINDOW BUFFER | | 12 | 108 | 2 (0)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID| SALES | 12 | 108 | 2 (0)| 00:00:01 | | 3 | I NDEX FULL SCAN | SYS_C004248 | 12 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 348 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 29 | | |
| 2 | TABLE ACCESS BY INDEX ROWID | SALES | 1 | 29 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | SYS_C004280 | 1 | | 1 (0)| 00:00:01 |
| 4 | SORT AGGREGATE | | 1 | 16 | | |
| 5 | FIRST ROW | | 1 | 16 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN (MIN/MAX)| SYS_C004280 | 1 | 16 | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | SALES | 12 | 348 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("S2"."COMPANY"=:B1 AND "S2"."YEAR"= (SELECT "YEAR" FROM "SALES"
"S3" WHERE "S3"."YEAR"<:B2 AND "S3"."COMPANY"=:B3))
6 - access("S3"."COMPANY"=:B1 AND "S3"."YEAR"<:B2)
あれ？ ウィンドウ関数を使ったほうのSQL、
それはウィンドウ関数のソートキーがどちらも
そっか。ウィンドウ関数は内部的にはソート処理をしていたんだった。うーん、
変？
いや、
ループからの脱出
うまいことを言うようになったな。お前の指摘はSQLの微妙な立場をうまく言い当てている。もともと、
“関係操作では、
関係全体をまとめて操作の対象とする。目的は繰返し （ループ） 。いやしくも末端利用者の生産性を考えようというのであれば、をなくすことである この条件を欠くことはできないし、 応用プログラマの生産性向上に有益であることも明らかである。”
（強調は筆者）
―E.
『ACMチューリング賞講演集』
Codd博士、
アメリカにそんな政党あった？ Coddには、
その意味で、
先生はフレームワーク否定派ですか？
別に否定も肯定もせんよ。一長一短さ。フレームワークにも処理の隠蔽による開発効率改善や共通化による保守性向上など、
あれ、
お前、
ぎくっ！
と、
……では図10のようなテーブルSales3を想定しよう。今度はvar列を同じテーブル内の列として持っており、
相関サブクエリによる更新
うーんと、
当たり前よ。これじゃSalesテーブルの1行にSales3テーブルから選択した全行を対応させようとしているのだから。ちゃんと一対一に条件を絞りなさい。
UPDATE Sales3
SET var =(SELECT CASE SIGN(sale - (SELECT MAX(sale) ― 直前のレコードと売り上げを比較
FROM Sales3 S2
WHERE S1.company = S2.company
AND S1.year > S2.year ))
WHEN 0 THEN '='
WHEN 1 THEN '+'
WHEN -1 THEN '-'
ELSE NULL END AS var
FROM Sales3 S1);そっか。SET句で相関サブクエリを使えば……できた！
UPDATE Sales3
SET var =(SELECT CASE SIGN(sale - (SELECT MAX(sale) ― 直前のレコードと売り上げを比較
FROM Sales3 S2
WHERE S1.company = S2.company
AND S2.year =
(SELECT MAX(year) ― 直近の年を選択
FROM Sales3 S3
WHERE S1.company = S3.company
AND S1.year > S3.year )))
WHEN 0 THEN '='
WHEN 1 THEN '+'
WHEN -1 THEN '-'
ELSE NULL END AS var
FROM Sales3 S1
WHERE Sales3.company = S1.company┐
AND Sales3.year = S1.year); ┘―更新先のレコードと更新元のレコードを一対一に対応させる正解ね。
ウィンドウ関数による更新
じゃあ、
こんなんでどうでしょう
UPDATE Sales3
SET var = CASE WHEN MAX(company) ― 直前のレコードが同じ会社のレコードの場合
OVER (ORDER BY company, year
ROWS BETWEEN 1 PRECEDING
AND 1 PRECEDING) = company
THEN CASE SIGN(sale - MAX(sale) ― 直前のレコードと売り上げを比較
OVER (ORDER BY company, year
ROWS BETWEEN 1 PRECEDING
AND 1 PRECEDING) )
WHEN 0 THEN '='
WHEN 1 THEN '+'
WHEN -1 THEN '-'
ELSE NULL END
ELSE NULL END ;基本的にはこれでOKよ。留保つきなのは、
UPDATE Sales3
SET var = (SELECT var ┐
FROM (SELECT company,｜―サブクエリを間にかませる
year, ┘
CASE WHEN MAX(company) ― 直前のレコードが同じ会社のレコードの場合
OVER (ORDER BY company, year
ROWS BETWEEN 1 PRECEDING
AND 1 PRECEDING) = company
THEN CASE SIGN(sale - MAX(sale) ― 直前のレコードと売り上げを比較
OVER (ORDER BY company, year
ROWS BETWEEN 1 PRECEDING
AND 1 PRECEDING) )
WHEN 0 THEN '='
WHEN 1 THEN '+'
WHEN -1 THEN '-'
ELSE NULL END
ELSE NULL END AS var
FROM Sales3) TMP
WHERE Sales3.company = TMP.company
AND Sales3.year = TMP.year);明らかに外側のサブクエリは冗長ですね。
ま、
もし、
しかし、
手続き型言語的な書き方～（ループ）を積極的に使う場合
まず、
一方、
つまり、
また、
SQLのみで記述する場合
一方、
SQLで記述することのメリットはパフォーマンスにあります。フレームワークを使う場合よりもレイヤが1つ少ない分、
デメリットは、
また、
トレードオフを考える
このように、
ただ、
【参考資料】
- 1．ミック
『SQL ゼロからはじめるデータベース操作』 （翔泳社、 2010年） - 「第8章 SQLで高度な処理を行なう」
でウィンドウ関数の考え方について、 詳しく解説しています。 「ROWS BETWEEN」 オプションの使い方についても触れています。 - 2．ミック WEB+DBVol.
55 連載 「SQLアタマアカデミー」 最終回 「OLAP関数で強力な統計処理を実現！」 - ウィンドウ関数の基礎的な使い方についてはこの回を参照してください。なお
「OLAP関数」 とは、 ウィンドウ関数の （やや古い） 別名です。gihyo. jp でも公開しております。 - 3．ミック
「相関サブクエリで行と行を比較する」 - 相関サブクエリによる行間比較の解説はこちらをどうぞ。