猫型iPS細胞研究所

Windows、Linux、iOS、DB、Network

DataBase MySQL

MySQLで相関サブクエリを実行する

投稿日:2013年7月10日 更新日:

相関サブクエリとは、SQLの実行結果の行と行を比較することです。
ある行とある行の時間差を求めることで何秒間隔でデータが登録されているかがわかったり、
ある行とある行の売上差を求めることで、売上の上がり下がりをだしたりできます。

相関サブクエリを知らなければ、
SELECTしたデータをもとに、
プログラムを書く必要がありますが、
データベースだけでもある程度のことはできてしまうのです!

ポイントは2点あります。

(ポイント1)必要なデータだけをソートして抜き出し、行番号を振る
このときに、ある行と次の行が比較対象となるようにソートしましょう。

(ポイント2)自己結合をして比較する
以上の2点です。

必要なデータだけをソートして抜き出し、行番号を振

必要なデータを抜き出すのは、whereで条件をつけます。
ソースするのはもちろんorder byです。
結果に行番号をつけることがポイントです。
Oracleではrownumものがありますが、MySQLではありません。
結果に行番号をつけるには(select @i:=0) とユーザー変数を利用します。

自己結合をして比較する

自己結合の詳細はここでは省略します。
自己結合した結果の行番号を1つずつずらして比較していくことがポイントです。

実際にやってみる

以下のようなテーブルから、
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日分のデータ登録された間隔を表示しています。

最後のポイントとして、
自己結合する2つのユーザー変数は、別名の変数を使う必要があるということです。
この場合はiとjを使用しています。
同じiだと2つのテーブルを通した連番になってしまいます。

参考になったでしょうか? :x002:

Gooleアドセンス用336

Gooleアドセンス用336

-DataBase, MySQL

執筆者:

関連記事

mysql

MySQLの文字コードを変更する

日本語を扱うにはutf8にするべきでしょう。 アプリケーション側で接続する際に文字コードを指定することもできます。 string constr = “userid=root;password=pass …

mysql

MySQLの時間のフォーマット

MySQLのdatetime型はそのままSELECTすると、 2013-05-15 13:45:59 のように表示されてしまいます。 yyyy-mm-ddではなく、yyyy/mm/dd がいい場合もあ …

sqlserver

SQLSERVER saでログインできるようにする

私はsaも設定しておくべきだと思います。 そこでsaログインするための確認ポイントです。 INDEX1 ログインを有効にする2 サーバー認証の設定3 名前付きパイプ ログインを有効にする Manage …

mysql

MySQLのmysqldumpのエクスポートとインポート

ダンプはバックアップやテストに大活躍してくれます。 しかし、データベース単位でエクスポートしても、インポートには随分時間がかかったりします。 その辺は別の機会にしますが、今回は基本コマンドのまとめです …

mysql

MySQLのChar型は”(ダブルクォーテーション)ではなく'(シングルクォーテーション)でSELECT

タイトルの通り MySQLのChar型は”(ダブルクォーテーション)ではなく'(シングルクォーテーション)で囲ってSELECT しなければなりません。 正確には、そうしなければパフォーマン …