ここはとある
ここには
それがSQL
そう、
ロバート
SQLを
学 ぶ上 で最 も高 いハードルとなるのが、順序 と手続 きではなく、集合 と論理 の観点 から考 えることだ。
―J.
（Morgan Kaufmann、
ループによる表現
（PM3:00
ワイリー、
いーえ、
ああっ！
ワイリー、
カルテ：
その
- より
古 い年 のデータが存在 しない場合 ：NULL 直近 の年 のデータより売 り上 げが伸 びた場合 ：+直近 の年 のデータより売 り上 げが減 った場合 ：-直近 の年 のデータより売 り上 げと同 じ場合 ：=
SQLでループを置 き換 えるには
リスト1
CREATE OR REPLACE PROCEDURE PROC_INSERT_VAR
IS
/* ①カーソル宣言 */
CURSOR c_sales IS
SELECT company, year, sale
FROM Sales
ORDER BY company, year;
/* レコードタイプ宣言 */
rec_sales c_sales%ROWTYPE;
/* カウンタ */
i_pre_sale INTEGER := 0;
c_company CHAR(1) := '*';
c_var CHAR(1) := '*';
BEGIN
OPEN c_sales;
LOOP
/* レコードをフェッチして変数 に代入 */
fetch c_sales into rec_sales;
/* レコードがなくなったらループ終了 */
exit when c_sales%notfound;
IF (c_company = rec_sales.company) THEN
/* 直前 のレコードが同 じ会社 のレコードの場合 */
/* 直前 のレコードと売 り上 げを比較 （②③）*/
IF (i_pre_sale < rec_sales.sale) THEN
c_var := '+';
ELSIF (i_pre_sale > rec_sales.sale) THEN
c_var := '-';
ELSE
c_var := '=';
END IF;
ELSE
c_var := NULL;
END IF;
/* ④登録 先 テーブルにデータを登録 */
INSERT INTO Sales2 (company, year, sale, var) VALUES (rec_sales.company, rec_sales.year, rec_sales.sale, c_var);
c_company := rec_sales.company;
i_pre_sale := rec_sales.sale;
END LOOP;
CLOSE c_sales;
commit;
END; ※ OracleのPL/
- ① Salesテーブルから
全 件 レコードを取得 する。このとき「 企業 ,年 」の 昇順 にソートしておく - ② 1レコードずつループを
行 い、同 じ会社 のレコードであるか比較 する - ③
同 じ会社 のレコードであれば、その 売 り上 げと直近 の売 り上 げを比較 し、比較 の結果 に応 じて「 変化 」列 の値 を設定 する - ④ Sales2テーブルへINSERTする
- ⑤ ②～④の
処理 をレコードがなくなるまで続 ける
その
えっと……ウィンドウ
そうね。ループの
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オプションを
ですがこれは
ウィンドウ
相関 サブクエリでループを置 き換 える
うーん、
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.の
このように、
相関 サブクエリの正体 は入 れ子 集合
へえ～、
そうね。そういう
この
奇妙 なサンドイッチ
------------------------------------------------------------------------------------------- | 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)
あれ？ ウィンドウ
それはウィンドウ
そっか。ウィンドウ
いや、
ループからの脱出
うまいことを
“
関係 操作 では、関係 全体 をまとめて操作 の対象 とする。目的 は繰返 し（ループ） 。いやしくもをなくすことである 末端 利用 者 の生産 性 を考 えようというのであれば、この 条件 を欠 くことはできないし、応用 プログラマの生産 性 向上 に有益 であることも明 らかである。”
（
―E.
『ACMチューリング
Codd
アメリカにそんな
その
あれ、
お
ぎくっ！
と、
……では
相関 サブクエリによる更新
うーんと、
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 ;
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．ミック
『SQL ゼロからはじめるデータベース 操作 』（ 翔 泳 社 、2010 年 ） - 「
第 8章 SQLで高度 な処理 を行 なう」でウィンドウ 関数 の考 え方 について、詳 しく解説 しています。「ROWS BETWEEN」 オプションの 使 い方 についても触 れています。 - 2．ミック WEB+DBVol.
55 連載 「SQLアタマアカデミー」 最終 回 「OLAP 関数 で強力 な統計 処理 を実現 ！」 - ウィンドウ
関数 の基礎 的 な使 い方 についてはこの回 を参照 してください。なお「OLAP 関数 」とは、 ウィンドウ 関数 の（やや 古 い）別名 です。gihyo.jp でも公開 しております。 - 3．ミック
「 相関 サブクエリで行 と行 を比較 する」 相関 サブクエリによる行間 比較 の解説 はこちらをどうぞ。