相関サブクエリとは、SQLの実行結果の行と行を比較することです。
ある行とある行の時間差を求めることで何秒間隔でデータが登録されているかがわかったり、
ある行とある行の売上差を求めることで、売上の上がり下がりをだしたりできます。
相関サブクエリを知らなければ、
SELECTしたデータをもとに、
プログラムを書く必要がありますが、
データベースだけでもある程度のことはできてしまうのです！
ポイントは２点あります。
（ポイント１）必要なデータだけをソートして抜き出し、行番号を振る
このときに、ある行と次の行が比較対象となるようにソートしましょう。
（ポイント２）自己結合をして比較する
以上の２点です。
必要なデータだけをソートして抜き出し、行番号を振
必要なデータを抜き出すのは、whereで条件をつけます。
ソースするのはもちろんorder byです。
結果に行番号をつけることがポイントです。
Oracleではrownumものがありますが、MySQLではありません。
結果に行番号をつけるには(select @i:=0) とユーザー変数を利用します。
自己結合をして比較する
自己結合の詳細はここでは省略します。
自己結合した結果の行番号を１つずつずらして比較していくことがポイントです。
実際にやってみる
以下のようなテーブルから、
2013年06月14日分のデータがどのぐらいの間隔で登録されたかを確認します。
(サンプルがいまいちなのはわかっています・・・ :x001: )
mysql> select * from test; +----+--------+---------------------+ | id | name | insert_time | +----+--------+---------------------+ | 1 | ice | 2013-06-13 09:10:11 | | 2 | cake | 2013-06-13 11:10:20 | | 3 | apple | 2013-06-13 13:11:05 | | 4 | juce | 2013-06-14 10:09:00 | | 5 | pizza | 2013-06-14 16:11:44 | | 6 | orange | 2013-06-14 20:15:55 | +----+--------+---------------------+ 6 rows in set (0.00 sec)
ここから2013年06月14日分を抜き出して、その結果に行番号をつけると以下のようになります。
t1_rownum というのが、SELECTの結果に対してついた行番号です。
idとは違うということに注意してください。
SELECT
t1.rownum as t1_rownum
,t1.id
,t1.insert_time
FROM
(
select
@i:=@i+1 as rownum,
t.*
from
(select @i:=0)tmp
,test t
where
t.insert_time >= '2013-06-14'
order by
insert_time)t1;
+-----------+----+---------------------+
| t1_rownum | id | insert_time |
+-----------+----+---------------------+
| 1 | 4 | 2013-06-14 10:09:00 |
| 2 | 5 | 2013-06-14 16:11:44 |
| 3 | 6 | 2013-06-14 20:15:55 |
+-----------+----+---------------------+
3 rows in set (0.00 sec)
自己結合して比較すると以下のようになります。
SELECT
timediff(t2.insert_time,t1.insert_time)
FROM
(select @i:=@i+1 as rownum,t.* from (select @i:=0)tmp ,test t where t.insert_time >= '2013-06-14' order by insert_time)t1,
(select @j:=@j+1 as rownum,t.* from (select @j:=0)tmp ,test t where t.insert_time >= '2013-06-14' order by insert_time)t2
where
t1.rownum = t2.rownum -1;
+-----------------------------------------+
| timediff(t2.insert_time,t1.insert_time) |
+-----------------------------------------+
| 06:02:44 |
| 04:04:11 |
+-----------------------------------------+
2 rows in set (0.00 sec)
データが少なすぎて分かりにくいかもしれませんが、
2013年06月14日分のデータ登録された間隔を表示しています。
最後のポイントとして、
自己結合する２つのユーザー変数は、別名の変数を使う必要があるということです。
この場合はiとjを使用しています。
同じiだと２つのテーブルを通した連番になってしまいます。
参考になったでしょうか？ :x002:
