移動累計を求める相関サブクエリ
http://d.hatena.ne.jp/psappho/20111107/1320687388
この日の日記に書いていた移動累計を求めるSQLの意味がやっとわかりました。
達人に学ぶ SQL徹底指南書 (CodeZine BOOKS)
- 作者: ミック
- 出版社/メーカー: 翔泳社
- 発売日: 2008/02/07
- メディア: 単行本(ソフトカバー)
- 購入: 54人 クリック: 1,004回
- この商品を含むブログ (78件) を見る
SELECT prc_date, A1.prc_amt, (SELECT SUM(prc_amt) FROM accounts A2 WHERE A1.prc_date >= A2.prc_date AND (SELECT COUNT(*) FROM accounts A3 WHERE A3.prc_date BETWEEN A2.prc_date AND A1.prc_date ) <= 3 HAVING COUNT(*) = 3 ) AS mvg_sum FROM accounts A1 ORDER BY prc_date ;
この、赤字にした部分を"<= 3"から"= 3"に変えるとどうしてNULLばかり返ってきてしまう(SUMするレコードがヒットしない)のか解らなかったのですが、以下のSQLを書いて実行したら理解できました。
ちなみに、accountsテーブルに入っているデータは以下です。
mysql> select * from accounts; +------------+---------+ | prc_date | prc_amt | +------------+---------+ | 2011-10-26 | 12000 | | 2011-10-28 | 2500 | | 2011-10-31 | -15000 | | 2011-11-03 | 34000 | | 2011-11-04 | -5000 | | 2011-11-06 | 7200 | | 2011-11-11 | 11000 | +------------+---------+ 7 rows in set (0.00 sec)
[僕が書いた検証用SQL 青字部分は必要な分だけに結果を絞る為に追加]
SELECT
A1.prc_date as date1,
A2.prc_date as date2,
A2.prc_amt as amt,
(
SELECT COUNT(*) FROM accounts A3
WHERE A3.prc_date BETWEEN A2.prc_date AND A1.prc_date
) X
FROM
accounts A1,
accounts A2
WHERE
A1.prc_date >= A2.prc_date
AND A1.prc_date <= "2011-11-03"
ORDER BY
A1.prc_date,
A2.prc_date
;
結果はこうなります。
+------------+------------+--------+------+ | date1 | date2 | amt | X | +------------+------------+--------+------+ | 2011-10-26 | 2011-10-26 | 12000 | 1 | | 2011-10-28 | 2011-10-26 | 12000 | 2 | | 2011-10-28 | 2011-10-28 | 2500 | 1 | | 2011-10-31 | 2011-10-26 | 12000 | 3 | | 2011-10-31 | 2011-10-28 | 2500 | 2 | | 2011-10-31 | 2011-10-31 | -15000 | 1 | | 2011-11-03 | 2011-10-26 | 12000 | 4 | | 2011-11-03 | 2011-10-28 | 2500 | 3 | | 2011-11-03 | 2011-10-31 | -15000 | 2 | | 2011-11-03 | 2011-11-03 | 34000 | 1 | +------------+------------+--------+------+ 10 rows in set (0.00 sec)
それぞれの行に対してCOUNTを算出してXとして返しています。
このSQLの処理順序は
- A1, A2をJOINする。 -- これをPとする。
- A3の各行をPの行と照合して条件に一致する(A1の日付とA2の日付の間にある)COUNTを取る。
- 結果を返す。
こうなるみたいです。
pythonっぽく書くと
P = Table.join(A1, A2) for x in P: for y in A3: filtr = lambda p: y.prc_date >= p.date1\ and y.prc_date <= p.date2 x.count = sum((0 if filtr(z) else 1 for z in P))
こんな感じかな。
つまり、相関サブクエリを使ったら、サブクエリで指定した方の集合(A3)の全部の行とメインクエリの集合(P)を照合していくことになります。
そして、この照合作業がメインクエリの行数ぶん実行されることになります。
さくっと書いた簡潔なSELECT文だけど、裏ではものすごい仕事が行われているんですね!