質問編集履歴
6
細かい修正
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -1,5 +1,6 @@
|
|
|
1
1
|
sqlで特定の変化を検出する方法を知りたいです。
|
|
2
2
|
|
|
3
|
+
(質問がわかりづらいと指摘されたため、下部に追記しました。)
|
|
3
4
|
プログラム初心者(teratail利用はこれが最初)で今はmysqlでデータを扱います。
|
|
4
5
|
|
|
5
6
|
やりたいことは、以下のようなdbについて考えます。
|
5
添付データのミス
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -145,33 +145,38 @@
|
|
|
145
145
|
|
|
146
146
|
実行結果は以下のようになる。
|
|
147
147
|
|
|
148
|
-
2 | a | 2017-03-03 21:46:57 | live
|
|
149
|
-
5 | a | 2017-03-03 21:47:03 | live
|
|
150
|
-
|
|
148
|
+
rows|name|time|state|beforestate|afterstate
|
|
151
|
-
22 | a | 2017-03-03 21:48:19 | dead
|
|
152
|
-
23 | a | 2017-03-03 21:48:21 | dead
|
|
153
|
-
25 | a | 2017-03-03 21:48:25 | dead
|
|
154
|
-
36 | a | 2017-03-03 21:48:48 | live
|
|
155
|
-
39 | a | 2017-03-03 21:48:54 | live
|
|
156
|
-
43 | a | 2017-03-03 21:49:02 | live
|
|
157
|
-
46 | a | 2017-03-03 21:49:08 | live
|
|
158
|
-
57 | a | 2017-03-03 21:49:31 | dead
|
|
159
|
-
58 | a | 2017-03-03 21:49:33 | dead
|
|
160
|
-
63 | a | 2017-03-03 21:49:43 | live
|
|
161
|
-
3 | b | 2017-03-03 21:46:59 | live
|
|
162
|
-
10 | b | 2017-03-03 21:47:14 | live
|
|
163
|
-
11 | b | 2017-03-03 21:47:16 | live
|
|
164
|
-
13 | b | 2017-03-03 21:47:20 | live
|
|
165
|
-
15 | b | 2017-03-03 21:47:24 | dead
|
|
166
|
-
20 | b | 2017-03-03 21:47:34 | dead
|
|
167
|
-
31 | b | 2017-03-03 21:48:37 | live
|
|
168
|
-
32 | b | 2017-03-03 21:48:39 | live
|
|
169
|
-
35 | b | 2017-03-03 21:48:46 | live
|
|
170
|
-
37 | b | 2017-03-03 21:48:50 | live
|
|
171
|
-
38 | b | 2017-03-03 21:48:52 | live
|
|
172
|
-
40 | b | 2017-03-03 21:48:56 | live
|
|
173
149
|
|
|
150
|
+
2 | a | 2017-03-03 21:46:57 | live | | dead
|
|
151
|
+
5 | a | 2017-03-03 21:47:03 | live | | dead
|
|
152
|
+
16 | a | 2017-03-03 21:47:26 | dead | live | live
|
|
153
|
+
22 | a | 2017-03-03 21:48:19 | dead | live | live
|
|
154
|
+
23 | a | 2017-03-03 21:48:21 | dead | live | live
|
|
155
|
+
25 | a | 2017-03-03 21:48:25 | dead | live | live
|
|
156
|
+
36 | a | 2017-03-03 21:48:48 | live | dead | dead
|
|
157
|
+
39 | a | 2017-03-03 21:48:54 | live | dead | dead
|
|
158
|
+
43 | a | 2017-03-03 21:49:02 | live | dead | dead
|
|
159
|
+
46 | a | 2017-03-03 21:49:08 | live | dead | dead
|
|
160
|
+
57 | a | 2017-03-03 21:49:31 | dead | live | live
|
|
161
|
+
58 | a | 2017-03-03 21:49:33 | dead | live | live
|
|
162
|
+
63 | a | 2017-03-03 21:49:43 | live | dead |
|
|
163
|
+
3 | b | 2017-03-03 21:46:59 | live | | dead
|
|
164
|
+
10 | b | 2017-03-03 21:47:14 | live | | dead
|
|
165
|
+
11 | b | 2017-03-03 21:47:16 | live | | dead
|
|
166
|
+
13 | b | 2017-03-03 21:47:20 | live | | dead
|
|
167
|
+
15 | b | 2017-03-03 21:47:24 | dead | live | live
|
|
168
|
+
20 | b | 2017-03-03 21:47:34 | dead | live | live
|
|
169
|
+
31 | b | 2017-03-03 21:48:37 | live | dead | dead
|
|
170
|
+
32 | b | 2017-03-03 21:48:39 | live | dead | dead
|
|
171
|
+
35 | b | 2017-03-03 21:48:46 | live | dead | dead
|
|
172
|
+
37 | b | 2017-03-03 21:48:50 | live | dead | dead
|
|
173
|
+
38 | b | 2017-03-03 21:48:52 | live | dead | dead
|
|
174
|
+
40 | b | 2017-03-03 21:48:56 | live | dead | dead
|
|
175
|
+
|
|
176
|
+
※空白部分はnull
|
|
177
|
+
|
|
174
178
|
これは条件指定に、行番号の大小に加え、注目するカラムが変化している、つまり、等しくない「<>」を加えたものである。これをみると、行番号を不等号にしただけなので、明らかにstateが変化しない不要な区間を含んでいる。
|
|
179
|
+
(それとは別に、before,afterの値がおかしいのも気になるが、それは表示する対象の問題なので、とりあえず遷移箇所のみを検出したい)
|
|
175
180
|
|
|
176
181
|
しかし、name別に状態遷移を検出したいが、行番号は飛び飛びであるため、行番号±1にstateに対する変化という条件を課す事ができない。
|
|
177
182
|
|
4
細かい修正
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -44,9 +44,13 @@
|
|
|
44
44
|
|
|
45
45
|
追記2)
|
|
46
46
|
|
|
47
|
-
すみません。自分でもわかりづら
|
|
47
|
+
すみません。自分でもわかりづらいと思い、さらに修正している途中でした。
|
|
48
|
-
さきほど、生成したtableを例にとります。
|
|
48
|
+
さきほど、生成したtableを例にとります。
|
|
49
|
+
わかりやすくするために、長くしています。
|
|
50
|
+
以下のテーブルを考える。カラムは
|
|
49
51
|
|
|
52
|
+
rows|name|time|state
|
|
53
|
+
|
|
50
54
|
1 | c | 2017-03-03 21:46:55 | live
|
|
51
55
|
2 | a | 2017-03-03 21:46:57 | live
|
|
52
56
|
3 | b | 2017-03-03 21:46:59 | live
|
|
@@ -139,11 +143,36 @@
|
|
|
139
143
|
from states_test1 as a
|
|
140
144
|
order by name,rows
|
|
141
145
|
|
|
142
|
-
実行結果は以下のようになる。
|
|
146
|
+
実行結果は以下のようになる。
|
|
143
147
|
|
|
144
|
-
これは条件指定に、行番号の大小に加え、注目するカラムが変化している、つまり、等しくない「<>」を加えたものである。これをみると、
|
|
145
|
-
|
|
148
|
+
2 | a | 2017-03-03 21:46:57 | live
|
|
149
|
+
5 | a | 2017-03-03 21:47:03 | live
|
|
150
|
+
16 | a | 2017-03-03 21:47:26 | dead
|
|
151
|
+
22 | a | 2017-03-03 21:48:19 | dead
|
|
152
|
+
23 | a | 2017-03-03 21:48:21 | dead
|
|
153
|
+
25 | a | 2017-03-03 21:48:25 | dead
|
|
154
|
+
36 | a | 2017-03-03 21:48:48 | live
|
|
155
|
+
39 | a | 2017-03-03 21:48:54 | live
|
|
156
|
+
43 | a | 2017-03-03 21:49:02 | live
|
|
157
|
+
46 | a | 2017-03-03 21:49:08 | live
|
|
158
|
+
57 | a | 2017-03-03 21:49:31 | dead
|
|
159
|
+
58 | a | 2017-03-03 21:49:33 | dead
|
|
160
|
+
63 | a | 2017-03-03 21:49:43 | live
|
|
161
|
+
3 | b | 2017-03-03 21:46:59 | live
|
|
162
|
+
10 | b | 2017-03-03 21:47:14 | live
|
|
163
|
+
11 | b | 2017-03-03 21:47:16 | live
|
|
164
|
+
13 | b | 2017-03-03 21:47:20 | live
|
|
165
|
+
15 | b | 2017-03-03 21:47:24 | dead
|
|
166
|
+
20 | b | 2017-03-03 21:47:34 | dead
|
|
167
|
+
31 | b | 2017-03-03 21:48:37 | live
|
|
168
|
+
32 | b | 2017-03-03 21:48:39 | live
|
|
169
|
+
35 | b | 2017-03-03 21:48:46 | live
|
|
170
|
+
37 | b | 2017-03-03 21:48:50 | live
|
|
171
|
+
38 | b | 2017-03-03 21:48:52 | live
|
|
172
|
+
40 | b | 2017-03-03 21:48:56 | live
|
|
146
173
|
|
|
174
|
+
これは条件指定に、行番号の大小に加え、注目するカラムが変化している、つまり、等しくない「<>」を加えたものである。これをみると、行番号を不等号にしただけなので、明らかにstateが変化しない不要な区間を含んでいる。
|
|
175
|
+
|
|
147
176
|
しかし、name別に状態遷移を検出したいが、行番号は飛び飛びであるため、行番号±1にstateに対する変化という条件を課す事ができない。
|
|
148
177
|
|
|
149
178
|
このように、行番号による印が使えないときに、状態遷移を検出するにはどうすればよいでしょうか？
|
3
説明不足を指摘された\(自分でも修正中だった\)
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -40,4 +40,110 @@
|
|
|
40
40
|
追記)
|
|
41
41
|
|
|
42
42
|
最もやりやすいのは、とりあえず、where name = a で特定nameのテーブルの問題にしてから、行番号または時系列的に隣接するものに対し、さらにユーザー変数によるautoincrementをつけて、隣接する行同士の特定カラムの変化を書く事かなと。
|
|
43
|
-
でも、ユーザー変数@aaでインクリメントしようにもスコープ範囲の問題でうまくいかないし、すべてのnameに対してやる方法が思いつかないです。
|
|
43
|
+
でも、ユーザー変数@aaでインクリメントしようにもスコープ範囲の問題でうまくいかないし、すべてのnameに対してやる方法が思いつかないです。
|
|
44
|
+
|
|
45
|
+
追記2)
|
|
46
|
+
|
|
47
|
+
すみません。自分でもわかりづらくさらに修正している途中でした。
|
|
48
|
+
さきほど、生成したtableを例にとります。以下のテーブル、
|
|
49
|
+
|
|
50
|
+
1 | c | 2017-03-03 21:46:55 | live
|
|
51
|
+
2 | a | 2017-03-03 21:46:57 | live
|
|
52
|
+
3 | b | 2017-03-03 21:46:59 | live
|
|
53
|
+
4 | c | 2017-03-03 21:47:01 | live
|
|
54
|
+
5 | a | 2017-03-03 21:47:03 | live
|
|
55
|
+
6 | d | 2017-03-03 21:47:06 | live
|
|
56
|
+
7 | c | 2017-03-03 21:47:08 | live
|
|
57
|
+
8 | d | 2017-03-03 21:47:10 | live
|
|
58
|
+
9 | d | 2017-03-03 21:47:12 | live
|
|
59
|
+
10 | b | 2017-03-03 21:47:14 | live
|
|
60
|
+
11 | b | 2017-03-03 21:47:16 | live
|
|
61
|
+
12 | c | 2017-03-03 21:47:18 | live
|
|
62
|
+
13 | b | 2017-03-03 21:47:20 | live
|
|
63
|
+
14 | d | 2017-03-03 21:47:22 | live
|
|
64
|
+
15 | b | 2017-03-03 21:47:24 | dead
|
|
65
|
+
16 | a | 2017-03-03 21:47:26 | dead
|
|
66
|
+
17 | c | 2017-03-03 21:47:28 | dead
|
|
67
|
+
18 | d | 2017-03-03 21:47:30 | dead
|
|
68
|
+
19 | d | 2017-03-03 21:47:32 | dead
|
|
69
|
+
20 | b | 2017-03-03 21:47:34 | dead
|
|
70
|
+
21 | c | 2017-03-03 21:48:17 | dead
|
|
71
|
+
22 | a | 2017-03-03 21:48:19 | dead
|
|
72
|
+
23 | a | 2017-03-03 21:48:21 | dead
|
|
73
|
+
24 | c | 2017-03-03 21:48:23 | dead
|
|
74
|
+
25 | a | 2017-03-03 21:48:25 | dead
|
|
75
|
+
26 | d | 2017-03-03 21:48:27 | dead
|
|
76
|
+
27 | c | 2017-03-03 21:48:29 | dead
|
|
77
|
+
28 | d | 2017-03-03 21:48:31 | dead
|
|
78
|
+
29 | d | 2017-03-03 21:48:33 | dead
|
|
79
|
+
30 | d | 2017-03-03 21:48:35 | dead
|
|
80
|
+
31 | b | 2017-03-03 21:48:37 | live
|
|
81
|
+
32 | b | 2017-03-03 21:48:39 | live
|
|
82
|
+
33 | c | 2017-03-03 21:48:41 | live
|
|
83
|
+
34 | d | 2017-03-03 21:48:43 | live
|
|
84
|
+
35 | b | 2017-03-03 21:48:46 | live
|
|
85
|
+
36 | a | 2017-03-03 21:48:48 | live
|
|
86
|
+
37 | b | 2017-03-03 21:48:50 | live
|
|
87
|
+
38 | b | 2017-03-03 21:48:52 | live
|
|
88
|
+
39 | a | 2017-03-03 21:48:54 | live
|
|
89
|
+
40 | b | 2017-03-03 21:48:56 | live
|
|
90
|
+
41 | b | 2017-03-03 21:48:58 | live
|
|
91
|
+
42 | c | 2017-03-03 21:49:00 | live
|
|
92
|
+
43 | a | 2017-03-03 21:49:02 | live
|
|
93
|
+
44 | c | 2017-03-03 21:49:04 | live
|
|
94
|
+
45 | b | 2017-03-03 21:49:06 | live
|
|
95
|
+
46 | a | 2017-03-03 21:49:08 | live
|
|
96
|
+
47 | d | 2017-03-03 21:49:10 | dead
|
|
97
|
+
48 | c | 2017-03-03 21:49:12 | dead
|
|
98
|
+
49 | b | 2017-03-03 21:49:14 | dead
|
|
99
|
+
50 | b | 2017-03-03 21:49:16 | dead
|
|
100
|
+
51 | b | 2017-03-03 21:49:19 | dead
|
|
101
|
+
52 | d | 2017-03-03 21:49:21 | dead
|
|
102
|
+
53 | d | 2017-03-03 21:49:23 | dead
|
|
103
|
+
54 | d | 2017-03-03 21:49:25 | dead
|
|
104
|
+
55 | c | 2017-03-03 21:49:27 | dead
|
|
105
|
+
56 | b | 2017-03-03 21:49:29 | dead
|
|
106
|
+
57 | a | 2017-03-03 21:49:31 | dead
|
|
107
|
+
58 | a | 2017-03-03 21:49:33 | dead
|
|
108
|
+
59 | d | 2017-03-03 21:49:35 | dead
|
|
109
|
+
60 | c | 2017-03-03 21:49:37 | dead
|
|
110
|
+
61 | c | 2017-03-03 21:49:39 | live
|
|
111
|
+
62 | c | 2017-03-03 21:49:41 | live
|
|
112
|
+
63 | a | 2017-03-03 21:49:43 | live
|
|
113
|
+
64 | d | 2017-03-03 21:49:46 | live
|
|
114
|
+
65 | c | 2017-03-03 21:49:48 | live
|
|
115
|
+
66 | c | 2017-03-03 21:49:50 | live
|
|
116
|
+
67 | c | 2017-03-03 21:49:52 | live
|
|
117
|
+
68 | d | 2017-03-03 21:49:54 | live
|
|
118
|
+
69 | c | 2017-03-03 21:49:56 | live
|
|
119
|
+
70 | d | 2017-03-03 21:49:58 | live
|
|
120
|
+
|
|
121
|
+
以上のような、テーブルで各nameについてstateの変化箇所を検索したいです。
|
|
122
|
+
例えば、このリンクのselectに入れる部分に直接条件が入る方法
|
|
123
|
+
https://codezine.jp/article/detail/3105
|
|
124
|
+
を利用する。oracleの分析関数なるものをmysql用に変えたものであり、
|
|
125
|
+
その中の「limit句を使うsql」のソースを改変して以下を実行。
|
|
126
|
+
(ちなみに、リンク先のselectになぜ二つもas bがでてるか不明。重複するのでcにした。)
|
|
127
|
+
|
|
128
|
+
select `rows`,`name`,`time`,`state`
|
|
129
|
+
,(select b.state
|
|
130
|
+
from states_test1 as b
|
|
131
|
+
where b.name=a.name
|
|
132
|
+
and b.rows < a.rows
|
|
133
|
+
order by b.rows desc Limit 1) as `beforestate`
|
|
134
|
+
,(select c.state
|
|
135
|
+
from states_test1 as c
|
|
136
|
+
where c.name=a.name
|
|
137
|
+
and c.rows > a.rows
|
|
138
|
+
order by c.rows Limit 1) as `afterstate`
|
|
139
|
+
from states_test1 as a
|
|
140
|
+
order by name,rows
|
|
141
|
+
|
|
142
|
+
実行結果は以下のようになる。(長すぎ。)
|
|
143
|
+
|
|
144
|
+
これは条件指定に、行番号の大小に加え、注目するカラムが変化している、つまり、等しくない「<>」を加えたものである。これをみると、
|
|
145
|
+
明らかにstateが変化しない区間を含んでいる。
|
|
146
|
+
|
|
147
|
+
しかし、name別に状態遷移を検出したいが、行番号は飛び飛びであるため、行番号±1にstateに対する変化という条件を課す事ができない。
|
|
148
|
+
|
|
149
|
+
このように、行番号による印が使えないときに、状態遷移を検出するにはどうすればよいでしょうか？
|
2
説明不足を改善
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -23,20 +23,21 @@
|
|
|
23
23
|
|
|
24
24
|
条件)
|
|
25
25
|
|
|
26
|
-
行番号は一般的には時系列に従う。但し、たまに古いデータを挿入する可能性あ
|
|
26
|
+
行番号は一般的には時系列に従う。但し、たまにtimeの古いデータを後から挿入する可能性があるため、後述する行番号の比較を隣接するtimeに直した方がよいかもしれない。
|
|
27
27
|
|
|
28
|
-
このように、あるnameのstateを定期的に取得し続けた時にstateに当
|
|
28
|
+
このように、あるnameのstateを定期的に取得し続けた時にstateに相当するカラム(実際は何でもよい)が指定した方向に変化(live -> dead or dead -> live )または任意の変化を起こしたときに、その遷移が発生している終始のレコード全てを検索するクエリを発行したいです。
|
|
29
29
|
|
|
30
30
|
|
|
31
31
|
ただ、acとなってる行番号ですが、同一nameのレコードは必ずしも隣接しているとは限らない。つまり、
|
|
32
|
-
where name = aとして、特定nameに
|
|
32
|
+
where name = aとして、特定nameのみのテーブルにしてから遷移を検出しようとすると、
|
|
33
|
-
行番号が時系列に従い一応増えているが、一般的にとびとびなので、隣接する行番号のstateが違う(where a.row = b.row + 1みたいな)という条件が使えない。
|
|
34
33
|
|
|
34
|
+
行番号が時系列に従い一応増えているという条件はあるが、隣接するレコードのnameは一定とは言えず、特定nameに対する行番号は一般的に飛び飛びになってしまうので、隣接する行番号のstateが違う(where a.row = b.row + 1みたいな)条件が使えない。
|
|
35
|
+
|
|
35
36
|
そして、これをすべてのnameに対して検出を行うのでサブクエリが必要？
|
|
36
37
|
とりあえず、とびとびのレコードで検索した方法は目的を達成しないものでした。
|
|
37
38
|
説明が下手なので、わからなければ質問をお願いします。
|
|
38
39
|
|
|
39
40
|
追記)
|
|
40
41
|
|
|
41
|
-
最もやりやすいのは、とりあえず、where name = a で特定nameのテーブルの問題にしてから、行番号または時系列的に隣接するものに対し、さらにautoincrementをつけて、隣接する行同士の特定カラムの変化を書く事かなと。
|
|
42
|
+
最もやりやすいのは、とりあえず、where name = a で特定nameのテーブルの問題にしてから、行番号または時系列的に隣接するものに対し、さらにユーザー変数によるautoincrementをつけて、隣接する行同士の特定カラムの変化を書く事かなと。
|
|
42
|
-
でも、ユーザー変数@aaでインクリメントしようにもスコープ範囲の問題でうまくいかないし、すべてのnameに対してやる方法が思いつかない。
|
|
43
|
+
でも、ユーザー変数@aaでインクリメントしようにもスコープ範囲の問題でうまくいかないし、すべてのnameに対してやる方法が思いつかないです。
|
1
追記・修正
title
CHANGED
|
@@ -1,1 +1,1 @@
|
|
|
1
|
-
mysqlで特定の遷移を
|
|
1
|
+
mysqlでレコード中の特定の遷移を検出・検索する方法を知りたい。
|
body
CHANGED
|
@@ -17,15 +17,15 @@
|
|
|
17
17
|
・
|
|
18
18
|
・
|
|
19
19
|
・
|
|
20
|
-
|b| 2016/
|
|
20
|
+
|b| 2016/02/17| live |
|
|
21
|
+
|b| 2016/4/4| dead |
|
|
21
|
-
|b| 2016/
|
|
22
|
+
|b| 2016/5/01| dead|
|
|
22
|
-
|b| 2016/01/01| dead|
|
|
23
23
|
|
|
24
24
|
条件)
|
|
25
25
|
|
|
26
26
|
行番号は一般的には時系列に従う。但し、たまに古いデータを挿入する可能性あり。(よって、後述する行番号の比較を隣接するtimeに直した方がよいかも)
|
|
27
27
|
|
|
28
|
-
このように、あるnameのstateを定期的に取得し続けた時にstateに当たるカラム(実際は何でもよい)が指定した方向に変化(live
|
|
28
|
+
このように、あるnameのstateを定期的に取得し続けた時にstateに当たるカラム(実際は何でもよい)が指定した方向に変化(live -> dead or dead -> live )または任意の変化を起こしたときに、その遷移が発生している終始のレコード全てを検索するクエリを発行したいです。
|
|
29
29
|
|
|
30
30
|
|
|
31
31
|
ただ、acとなってる行番号ですが、同一nameのレコードは必ずしも隣接しているとは限らない。つまり、
|
|
@@ -34,4 +34,9 @@
|
|
|
34
34
|
|
|
35
35
|
そして、これをすべてのnameに対して検出を行うのでサブクエリが必要？
|
|
36
36
|
とりあえず、とびとびのレコードで検索した方法は目的を達成しないものでした。
|
|
37
|
-
説明が下手なので、わからなければ質問をお願いします。
|
|
37
|
+
説明が下手なので、わからなければ質問をお願いします。
|
|
38
|
+
|
|
39
|
+
追記)
|
|
40
|
+
|
|
41
|
+
最もやりやすいのは、とりあえず、where name = a で特定nameのテーブルの問題にしてから、行番号または時系列的に隣接するものに対し、さらにautoincrementをつけて、隣接する行同士の特定カラムの変化を書く事かなと。
|
|
42
|
+
でも、ユーザー変数@aaでインクリメントしようにもスコープ範囲の問題でうまくいかないし、すべてのnameに対してやる方法が思いつかない。
|