sqlで特定の変化を検出する方法を知りたいです。
(質問がわかりづらいと指摘されたため、下部に追記しました。)
プログラム初心者(teratail利用はこれが最初)で今はmysqlでデータを扱います。
やりたいことは、以下のようなdbについて考えます。
(autoincrementの行番号はありますが略。)
| name | time | state |
|a| 2016/01/01| live |
・
・
・
|a| 2016/03/17| live |
|a| 2016/0/01| dead |
|a| 2016/01/01| dead|
|b| 2016/01/01| live |
・
・
・
|b| 2016/02/17| live |
|b| 2016/4/4| dead |
|b| 2016/5/01| dead|
条件)
行番号は一般的には時系列に従う。但し、たまにtimeの古いデータを後から挿入する可能性があるため、後述する行番号の比較を隣接するtimeに直した方がよいかもしれない。
このように、あるnameのstateを定期的に取得し続けた時にstateに相当するカラム(実際は何でもよい)が指定した方向に変化(live -> dead or dead -> live )または任意の変化を起こしたときに、その遷移が発生している終始のレコード全てを検索するクエリを発行したいです。
ただ、acとなってる行番号ですが、同一nameのレコードは必ずしも隣接しているとは限らない。つまり、
where name = aとして、特定nameのみのテーブルにしてから遷移を検出しようとすると、
行番号が時系列に従い一応増えているという条件はあるが、隣接するレコードのnameは一定とは言えず、特定nameに対する行番号は一般的に飛び飛びになってしまうので、隣接する行番号のstateが違う(where a.row = b.row + 1みたいな)条件が使えない。
そして、これをすべてのnameに対して検出を行うのでサブクエリが必要?
とりあえず、とびとびのレコードで検索した方法は目的を達成しないものでした。
説明が下手なので、わからなければ質問をお願いします。
追記)
最もやりやすいのは、とりあえず、where name = a で特定nameのテーブルの問題にしてから、行番号または時系列的に隣接するものに対し、さらにユーザー変数によるautoincrementをつけて、隣接する行同士の特定カラムの変化を書く事かなと。
でも、ユーザー変数@aaでインクリメントしようにもスコープ範囲の問題でうまくいかないし、すべてのnameに対してやる方法が思いつかないです。
追記2)
すみません。自分でもわかりづらいと思い、さらに修正している途中でした。
さきほど、生成したtableを例にとります。
わかりやすくするために、長くしています。
以下のテーブルを考える。カラムは
rows|name|time|state
1 | c | 2017-03-03 21:46:55 | live
2 | a | 2017-03-03 21:46:57 | live
3 | b | 2017-03-03 21:46:59 | live
4 | c | 2017-03-03 21:47:01 | live
5 | a | 2017-03-03 21:47:03 | live
6 | d | 2017-03-03 21:47:06 | live
7 | c | 2017-03-03 21:47:08 | live
8 | d | 2017-03-03 21:47:10 | live
9 | d | 2017-03-03 21:47:12 | live
10 | b | 2017-03-03 21:47:14 | live
11 | b | 2017-03-03 21:47:16 | live
12 | c | 2017-03-03 21:47:18 | live
13 | b | 2017-03-03 21:47:20 | live
14 | d | 2017-03-03 21:47:22 | live
15 | b | 2017-03-03 21:47:24 | dead
16 | a | 2017-03-03 21:47:26 | dead
17 | c | 2017-03-03 21:47:28 | dead
18 | d | 2017-03-03 21:47:30 | dead
19 | d | 2017-03-03 21:47:32 | dead
20 | b | 2017-03-03 21:47:34 | dead
21 | c | 2017-03-03 21:48:17 | dead
22 | a | 2017-03-03 21:48:19 | dead
23 | a | 2017-03-03 21:48:21 | dead
24 | c | 2017-03-03 21:48:23 | dead
25 | a | 2017-03-03 21:48:25 | dead
26 | d | 2017-03-03 21:48:27 | dead
27 | c | 2017-03-03 21:48:29 | dead
28 | d | 2017-03-03 21:48:31 | dead
29 | d | 2017-03-03 21:48:33 | dead
30 | d | 2017-03-03 21:48:35 | dead
31 | b | 2017-03-03 21:48:37 | live
32 | b | 2017-03-03 21:48:39 | live
33 | c | 2017-03-03 21:48:41 | live
34 | d | 2017-03-03 21:48:43 | live
35 | b | 2017-03-03 21:48:46 | live
36 | a | 2017-03-03 21:48:48 | live
37 | b | 2017-03-03 21:48:50 | live
38 | b | 2017-03-03 21:48:52 | live
39 | a | 2017-03-03 21:48:54 | live
40 | b | 2017-03-03 21:48:56 | live
41 | b | 2017-03-03 21:48:58 | live
42 | c | 2017-03-03 21:49:00 | live
43 | a | 2017-03-03 21:49:02 | live
44 | c | 2017-03-03 21:49:04 | live
45 | b | 2017-03-03 21:49:06 | live
46 | a | 2017-03-03 21:49:08 | live
47 | d | 2017-03-03 21:49:10 | dead
48 | c | 2017-03-03 21:49:12 | dead
49 | b | 2017-03-03 21:49:14 | dead
50 | b | 2017-03-03 21:49:16 | dead
51 | b | 2017-03-03 21:49:19 | dead
52 | d | 2017-03-03 21:49:21 | dead
53 | d | 2017-03-03 21:49:23 | dead
54 | d | 2017-03-03 21:49:25 | dead
55 | c | 2017-03-03 21:49:27 | dead
56 | b | 2017-03-03 21:49:29 | dead
57 | a | 2017-03-03 21:49:31 | dead
58 | a | 2017-03-03 21:49:33 | dead
59 | d | 2017-03-03 21:49:35 | dead
60 | c | 2017-03-03 21:49:37 | dead
61 | c | 2017-03-03 21:49:39 | live
62 | c | 2017-03-03 21:49:41 | live
63 | a | 2017-03-03 21:49:43 | live
64 | d | 2017-03-03 21:49:46 | live
65 | c | 2017-03-03 21:49:48 | live
66 | c | 2017-03-03 21:49:50 | live
67 | c | 2017-03-03 21:49:52 | live
68 | d | 2017-03-03 21:49:54 | live
69 | c | 2017-03-03 21:49:56 | live
70 | d | 2017-03-03 21:49:58 | live
以上のような、テーブルで各nameについてstateの変化箇所を検索したいです。
例えば、このリンクのselectに入れる部分に直接条件が入る方法
https://codezine.jp/article/detail/3105
を利用する。oracleの分析関数なるものをmysql用に変えたものであり、
その中の「limit句を使うsql」のソースを改変して以下を実行。
(ちなみに、リンク先のselectになぜ二つもas bがでてるか不明。重複するのでcにした。)
select rows
,name
,time
,state
,(select b.state
from states_test1 as b
where b.name=a.name
and b.rows < a.rows
order by b.rows desc Limit 1) as beforestate
,(select c.state
from states_test1 as c
where c.name=a.name
and c.rows > a.rows
order by c.rows Limit 1) as afterstate
from states_test1 as a
order by name,rows
実行結果は以下のようになる。
rows|name|time|state|beforestate|afterstate
2 | a | 2017-03-03 21:46:57 | live | | dead
5 | a | 2017-03-03 21:47:03 | live | | dead
16 | a | 2017-03-03 21:47:26 | dead | live | live
22 | a | 2017-03-03 21:48:19 | dead | live | live
23 | a | 2017-03-03 21:48:21 | dead | live | live
25 | a | 2017-03-03 21:48:25 | dead | live | live
36 | a | 2017-03-03 21:48:48 | live | dead | dead
39 | a | 2017-03-03 21:48:54 | live | dead | dead
43 | a | 2017-03-03 21:49:02 | live | dead | dead
46 | a | 2017-03-03 21:49:08 | live | dead | dead
57 | a | 2017-03-03 21:49:31 | dead | live | live
58 | a | 2017-03-03 21:49:33 | dead | live | live
63 | a | 2017-03-03 21:49:43 | live | dead |
3 | b | 2017-03-03 21:46:59 | live | | dead
10 | b | 2017-03-03 21:47:14 | live | | dead
11 | b | 2017-03-03 21:47:16 | live | | dead
13 | b | 2017-03-03 21:47:20 | live | | dead
15 | b | 2017-03-03 21:47:24 | dead | live | live
20 | b | 2017-03-03 21:47:34 | dead | live | live
31 | b | 2017-03-03 21:48:37 | live | dead | dead
32 | b | 2017-03-03 21:48:39 | live | dead | dead
35 | b | 2017-03-03 21:48:46 | live | dead | dead
37 | b | 2017-03-03 21:48:50 | live | dead | dead
38 | b | 2017-03-03 21:48:52 | live | dead | dead
40 | b | 2017-03-03 21:48:56 | live | dead | dead
※空白部分はnull
これは条件指定に、行番号の大小に加え、注目するカラムが変化している、つまり、等しくない「<>」を加えたものである。これをみると、行番号を不等号にしただけなので、明らかにstateが変化しない不要な区間を含んでいる。
(それとは別に、before,afterの値がおかしいのも気になるが、それは表示する対象の問題なので、とりあえず遷移箇所のみを検出したい)
しかし、name別に状態遷移を検出したいが、行番号は飛び飛びであるため、行番号±1にstateに対する変化という条件を課す事ができない。
このように、行番号による印が使えないときに、状態遷移を検出するにはどうすればよいでしょうか?