質問編集履歴
6
細かい修正
test
CHANGED
File without changes
|
test
CHANGED
@@ -2,6 +2,8 @@
|
|
2
2
|
|
3
3
|
|
4
4
|
|
5
|
+
(質問がわかりづらいと指摘されたため、下部に追記しました。)
|
6
|
+
|
5
7
|
プログラム初心者(teratail利用はこれが最初)で今はmysqlでデータを扱います。
|
6
8
|
|
7
9
|
|
5
添付データのミス
test
CHANGED
File without changes
|
test
CHANGED
@@ -292,60 +292,70 @@
|
|
292
292
|
|
293
293
|
|
294
294
|
|
295
|
-
|
296
|
-
|
297
|
-
|
298
|
-
|
299
|
-
|
300
|
-
|
301
|
-
|
302
|
-
|
303
|
-
|
304
|
-
|
305
|
-
2
|
306
|
-
|
307
|
-
3
|
308
|
-
|
309
|
-
|
310
|
-
|
311
|
-
|
312
|
-
|
313
|
-
|
314
|
-
|
315
|
-
|
316
|
-
|
317
|
-
|
318
|
-
|
319
|
-
|
320
|
-
|
321
|
-
|
322
|
-
|
323
|
-
|
324
|
-
|
325
|
-
|
326
|
-
|
327
|
-
1
|
328
|
-
|
329
|
-
1
|
330
|
-
|
331
|
-
|
332
|
-
|
333
|
-
|
334
|
-
|
335
|
-
|
336
|
-
|
337
|
-
3
|
338
|
-
|
339
|
-
3
|
340
|
-
|
341
|
-
3
|
342
|
-
|
343
|
-
|
295
|
+
rows|name|time|state|beforestate|afterstate
|
296
|
+
|
297
|
+
|
298
|
+
|
299
|
+
2 | a | 2017-03-03 21:46:57 | live | | dead
|
300
|
+
|
301
|
+
5 | a | 2017-03-03 21:47:03 | live | | dead
|
302
|
+
|
303
|
+
16 | a | 2017-03-03 21:47:26 | dead | live | live
|
304
|
+
|
305
|
+
22 | a | 2017-03-03 21:48:19 | dead | live | live
|
306
|
+
|
307
|
+
23 | a | 2017-03-03 21:48:21 | dead | live | live
|
308
|
+
|
309
|
+
25 | a | 2017-03-03 21:48:25 | dead | live | live
|
310
|
+
|
311
|
+
36 | a | 2017-03-03 21:48:48 | live | dead | dead
|
312
|
+
|
313
|
+
39 | a | 2017-03-03 21:48:54 | live | dead | dead
|
314
|
+
|
315
|
+
43 | a | 2017-03-03 21:49:02 | live | dead | dead
|
316
|
+
|
317
|
+
46 | a | 2017-03-03 21:49:08 | live | dead | dead
|
318
|
+
|
319
|
+
57 | a | 2017-03-03 21:49:31 | dead | live | live
|
320
|
+
|
321
|
+
58 | a | 2017-03-03 21:49:33 | dead | live | live
|
322
|
+
|
323
|
+
63 | a | 2017-03-03 21:49:43 | live | dead |
|
324
|
+
|
325
|
+
3 | b | 2017-03-03 21:46:59 | live | | dead
|
326
|
+
|
327
|
+
10 | b | 2017-03-03 21:47:14 | live | | dead
|
328
|
+
|
329
|
+
11 | b | 2017-03-03 21:47:16 | live | | dead
|
330
|
+
|
331
|
+
13 | b | 2017-03-03 21:47:20 | live | | dead
|
332
|
+
|
333
|
+
15 | b | 2017-03-03 21:47:24 | dead | live | live
|
334
|
+
|
335
|
+
20 | b | 2017-03-03 21:47:34 | dead | live | live
|
336
|
+
|
337
|
+
31 | b | 2017-03-03 21:48:37 | live | dead | dead
|
338
|
+
|
339
|
+
32 | b | 2017-03-03 21:48:39 | live | dead | dead
|
340
|
+
|
341
|
+
35 | b | 2017-03-03 21:48:46 | live | dead | dead
|
342
|
+
|
343
|
+
37 | b | 2017-03-03 21:48:50 | live | dead | dead
|
344
|
+
|
345
|
+
38 | b | 2017-03-03 21:48:52 | live | dead | dead
|
346
|
+
|
347
|
+
40 | b | 2017-03-03 21:48:56 | live | dead | dead
|
348
|
+
|
349
|
+
|
350
|
+
|
351
|
+
※空白部分はnull
|
344
352
|
|
345
353
|
|
346
354
|
|
347
355
|
これは条件指定に、行番号の大小に加え、注目するカラムが変化している、つまり、等しくない「<>」を加えたものである。これをみると、行番号を不等号にしただけなので、明らかにstateが変化しない不要な区間を含んでいる。
|
348
356
|
|
357
|
+
(それとは別に、before,afterの値がおかしいのも気になるが、それは表示する対象の問題なので、とりあえず遷移箇所のみを検出したい)
|
358
|
+
|
349
359
|
|
350
360
|
|
351
361
|
しかし、name別に状態遷移を検出したいが、行番号は飛び飛びであるため、行番号±1にstateに対する変化という条件を課す事ができない。
|
4
細かい修正
test
CHANGED
File without changes
|
test
CHANGED
@@ -90,9 +90,17 @@
|
|
90
90
|
|
91
91
|
|
92
92
|
|
93
|
-
すみません。自分でもわかりづら
|
93
|
+
すみません。自分でもわかりづらいと思い、さらに修正している途中でした。
|
94
|
-
|
94
|
+
|
95
|
-
さきほど、生成したtableを例にとります。
|
95
|
+
さきほど、生成したtableを例にとります。
|
96
|
+
|
97
|
+
わかりやすくするために、長くしています。
|
98
|
+
|
99
|
+
以下のテーブルを考える。カラムは
|
100
|
+
|
101
|
+
|
102
|
+
|
103
|
+
rows|name|time|state
|
96
104
|
|
97
105
|
|
98
106
|
|
@@ -280,13 +288,63 @@
|
|
280
288
|
|
281
289
|
|
282
290
|
|
283
|
-
実行結果は以下のようになる。
|
291
|
+
実行結果は以下のようになる。
|
292
|
+
|
293
|
+
|
294
|
+
|
284
|
-
|
295
|
+
2 | a | 2017-03-03 21:46:57 | live
|
296
|
+
|
285
|
-
|
297
|
+
5 | a | 2017-03-03 21:47:03 | live
|
298
|
+
|
286
|
-
|
299
|
+
16 | a | 2017-03-03 21:47:26 | dead
|
300
|
+
|
301
|
+
22 | a | 2017-03-03 21:48:19 | dead
|
302
|
+
|
303
|
+
23 | a | 2017-03-03 21:48:21 | dead
|
304
|
+
|
305
|
+
25 | a | 2017-03-03 21:48:25 | dead
|
306
|
+
|
307
|
+
36 | a | 2017-03-03 21:48:48 | live
|
308
|
+
|
309
|
+
39 | a | 2017-03-03 21:48:54 | live
|
310
|
+
|
311
|
+
43 | a | 2017-03-03 21:49:02 | live
|
312
|
+
|
313
|
+
46 | a | 2017-03-03 21:49:08 | live
|
314
|
+
|
315
|
+
57 | a | 2017-03-03 21:49:31 | dead
|
316
|
+
|
317
|
+
58 | a | 2017-03-03 21:49:33 | dead
|
318
|
+
|
319
|
+
63 | a | 2017-03-03 21:49:43 | live
|
320
|
+
|
321
|
+
3 | b | 2017-03-03 21:46:59 | live
|
322
|
+
|
323
|
+
10 | b | 2017-03-03 21:47:14 | live
|
324
|
+
|
325
|
+
11 | b | 2017-03-03 21:47:16 | live
|
326
|
+
|
327
|
+
13 | b | 2017-03-03 21:47:20 | live
|
328
|
+
|
329
|
+
15 | b | 2017-03-03 21:47:24 | dead
|
330
|
+
|
331
|
+
20 | b | 2017-03-03 21:47:34 | dead
|
332
|
+
|
333
|
+
31 | b | 2017-03-03 21:48:37 | live
|
334
|
+
|
335
|
+
32 | b | 2017-03-03 21:48:39 | live
|
336
|
+
|
337
|
+
35 | b | 2017-03-03 21:48:46 | live
|
338
|
+
|
339
|
+
37 | b | 2017-03-03 21:48:50 | live
|
340
|
+
|
341
|
+
38 | b | 2017-03-03 21:48:52 | live
|
342
|
+
|
343
|
+
40 | b | 2017-03-03 21:48:56 | live
|
344
|
+
|
345
|
+
|
346
|
+
|
287
|
-
これは条件指定に、行番号の大小に加え、注目するカラムが変化している、つまり、等しくない「<>」を加えたものである。これをみると、
|
347
|
+
これは条件指定に、行番号の大小に加え、注目するカラムが変化している、つまり、等しくない「<>」を加えたものである。これをみると、行番号を不等号にしただけなので、明らかにstateが変化しない不要な区間を含んでいる。
|
288
|
-
|
289
|
-
明らかにstateが変化しない区間を含んでいる。
|
290
348
|
|
291
349
|
|
292
350
|
|
3
説明不足を指摘された\(自分でも修正中だった\)
test
CHANGED
File without changes
|
test
CHANGED
@@ -83,3 +83,215 @@
|
|
83
83
|
最もやりやすいのは、とりあえず、where name = a で特定nameのテーブルの問題にしてから、行番号または時系列的に隣接するものに対し、さらにユーザー変数によるautoincrementをつけて、隣接する行同士の特定カラムの変化を書く事かなと。
|
84
84
|
|
85
85
|
でも、ユーザー変数@aaでインクリメントしようにもスコープ範囲の問題でうまくいかないし、すべてのnameに対してやる方法が思いつかないです。
|
86
|
+
|
87
|
+
|
88
|
+
|
89
|
+
追記2)
|
90
|
+
|
91
|
+
|
92
|
+
|
93
|
+
すみません。自分でもわかりづらくさらに修正している途中でした。
|
94
|
+
|
95
|
+
さきほど、生成したtableを例にとります。以下のテーブル、
|
96
|
+
|
97
|
+
|
98
|
+
|
99
|
+
1 | c | 2017-03-03 21:46:55 | live
|
100
|
+
|
101
|
+
2 | a | 2017-03-03 21:46:57 | live
|
102
|
+
|
103
|
+
3 | b | 2017-03-03 21:46:59 | live
|
104
|
+
|
105
|
+
4 | c | 2017-03-03 21:47:01 | live
|
106
|
+
|
107
|
+
5 | a | 2017-03-03 21:47:03 | live
|
108
|
+
|
109
|
+
6 | d | 2017-03-03 21:47:06 | live
|
110
|
+
|
111
|
+
7 | c | 2017-03-03 21:47:08 | live
|
112
|
+
|
113
|
+
8 | d | 2017-03-03 21:47:10 | live
|
114
|
+
|
115
|
+
9 | d | 2017-03-03 21:47:12 | live
|
116
|
+
|
117
|
+
10 | b | 2017-03-03 21:47:14 | live
|
118
|
+
|
119
|
+
11 | b | 2017-03-03 21:47:16 | live
|
120
|
+
|
121
|
+
12 | c | 2017-03-03 21:47:18 | live
|
122
|
+
|
123
|
+
13 | b | 2017-03-03 21:47:20 | live
|
124
|
+
|
125
|
+
14 | d | 2017-03-03 21:47:22 | live
|
126
|
+
|
127
|
+
15 | b | 2017-03-03 21:47:24 | dead
|
128
|
+
|
129
|
+
16 | a | 2017-03-03 21:47:26 | dead
|
130
|
+
|
131
|
+
17 | c | 2017-03-03 21:47:28 | dead
|
132
|
+
|
133
|
+
18 | d | 2017-03-03 21:47:30 | dead
|
134
|
+
|
135
|
+
19 | d | 2017-03-03 21:47:32 | dead
|
136
|
+
|
137
|
+
20 | b | 2017-03-03 21:47:34 | dead
|
138
|
+
|
139
|
+
21 | c | 2017-03-03 21:48:17 | dead
|
140
|
+
|
141
|
+
22 | a | 2017-03-03 21:48:19 | dead
|
142
|
+
|
143
|
+
23 | a | 2017-03-03 21:48:21 | dead
|
144
|
+
|
145
|
+
24 | c | 2017-03-03 21:48:23 | dead
|
146
|
+
|
147
|
+
25 | a | 2017-03-03 21:48:25 | dead
|
148
|
+
|
149
|
+
26 | d | 2017-03-03 21:48:27 | dead
|
150
|
+
|
151
|
+
27 | c | 2017-03-03 21:48:29 | dead
|
152
|
+
|
153
|
+
28 | d | 2017-03-03 21:48:31 | dead
|
154
|
+
|
155
|
+
29 | d | 2017-03-03 21:48:33 | dead
|
156
|
+
|
157
|
+
30 | d | 2017-03-03 21:48:35 | dead
|
158
|
+
|
159
|
+
31 | b | 2017-03-03 21:48:37 | live
|
160
|
+
|
161
|
+
32 | b | 2017-03-03 21:48:39 | live
|
162
|
+
|
163
|
+
33 | c | 2017-03-03 21:48:41 | live
|
164
|
+
|
165
|
+
34 | d | 2017-03-03 21:48:43 | live
|
166
|
+
|
167
|
+
35 | b | 2017-03-03 21:48:46 | live
|
168
|
+
|
169
|
+
36 | a | 2017-03-03 21:48:48 | live
|
170
|
+
|
171
|
+
37 | b | 2017-03-03 21:48:50 | live
|
172
|
+
|
173
|
+
38 | b | 2017-03-03 21:48:52 | live
|
174
|
+
|
175
|
+
39 | a | 2017-03-03 21:48:54 | live
|
176
|
+
|
177
|
+
40 | b | 2017-03-03 21:48:56 | live
|
178
|
+
|
179
|
+
41 | b | 2017-03-03 21:48:58 | live
|
180
|
+
|
181
|
+
42 | c | 2017-03-03 21:49:00 | live
|
182
|
+
|
183
|
+
43 | a | 2017-03-03 21:49:02 | live
|
184
|
+
|
185
|
+
44 | c | 2017-03-03 21:49:04 | live
|
186
|
+
|
187
|
+
45 | b | 2017-03-03 21:49:06 | live
|
188
|
+
|
189
|
+
46 | a | 2017-03-03 21:49:08 | live
|
190
|
+
|
191
|
+
47 | d | 2017-03-03 21:49:10 | dead
|
192
|
+
|
193
|
+
48 | c | 2017-03-03 21:49:12 | dead
|
194
|
+
|
195
|
+
49 | b | 2017-03-03 21:49:14 | dead
|
196
|
+
|
197
|
+
50 | b | 2017-03-03 21:49:16 | dead
|
198
|
+
|
199
|
+
51 | b | 2017-03-03 21:49:19 | dead
|
200
|
+
|
201
|
+
52 | d | 2017-03-03 21:49:21 | dead
|
202
|
+
|
203
|
+
53 | d | 2017-03-03 21:49:23 | dead
|
204
|
+
|
205
|
+
54 | d | 2017-03-03 21:49:25 | dead
|
206
|
+
|
207
|
+
55 | c | 2017-03-03 21:49:27 | dead
|
208
|
+
|
209
|
+
56 | b | 2017-03-03 21:49:29 | dead
|
210
|
+
|
211
|
+
57 | a | 2017-03-03 21:49:31 | dead
|
212
|
+
|
213
|
+
58 | a | 2017-03-03 21:49:33 | dead
|
214
|
+
|
215
|
+
59 | d | 2017-03-03 21:49:35 | dead
|
216
|
+
|
217
|
+
60 | c | 2017-03-03 21:49:37 | dead
|
218
|
+
|
219
|
+
61 | c | 2017-03-03 21:49:39 | live
|
220
|
+
|
221
|
+
62 | c | 2017-03-03 21:49:41 | live
|
222
|
+
|
223
|
+
63 | a | 2017-03-03 21:49:43 | live
|
224
|
+
|
225
|
+
64 | d | 2017-03-03 21:49:46 | live
|
226
|
+
|
227
|
+
65 | c | 2017-03-03 21:49:48 | live
|
228
|
+
|
229
|
+
66 | c | 2017-03-03 21:49:50 | live
|
230
|
+
|
231
|
+
67 | c | 2017-03-03 21:49:52 | live
|
232
|
+
|
233
|
+
68 | d | 2017-03-03 21:49:54 | live
|
234
|
+
|
235
|
+
69 | c | 2017-03-03 21:49:56 | live
|
236
|
+
|
237
|
+
70 | d | 2017-03-03 21:49:58 | live
|
238
|
+
|
239
|
+
|
240
|
+
|
241
|
+
以上のような、テーブルで各nameについてstateの変化箇所を検索したいです。
|
242
|
+
|
243
|
+
例えば、このリンクのselectに入れる部分に直接条件が入る方法
|
244
|
+
|
245
|
+
https://codezine.jp/article/detail/3105
|
246
|
+
|
247
|
+
を利用する。oracleの分析関数なるものをmysql用に変えたものであり、
|
248
|
+
|
249
|
+
その中の「limit句を使うsql」のソースを改変して以下を実行。
|
250
|
+
|
251
|
+
(ちなみに、リンク先のselectになぜ二つもas bがでてるか不明。重複するのでcにした。)
|
252
|
+
|
253
|
+
|
254
|
+
|
255
|
+
select `rows`,`name`,`time`,`state`
|
256
|
+
|
257
|
+
,(select b.state
|
258
|
+
|
259
|
+
from states_test1 as b
|
260
|
+
|
261
|
+
where b.name=a.name
|
262
|
+
|
263
|
+
and b.rows < a.rows
|
264
|
+
|
265
|
+
order by b.rows desc Limit 1) as `beforestate`
|
266
|
+
|
267
|
+
,(select c.state
|
268
|
+
|
269
|
+
from states_test1 as c
|
270
|
+
|
271
|
+
where c.name=a.name
|
272
|
+
|
273
|
+
and c.rows > a.rows
|
274
|
+
|
275
|
+
order by c.rows Limit 1) as `afterstate`
|
276
|
+
|
277
|
+
from states_test1 as a
|
278
|
+
|
279
|
+
order by name,rows
|
280
|
+
|
281
|
+
|
282
|
+
|
283
|
+
実行結果は以下のようになる。(長すぎ。)
|
284
|
+
|
285
|
+
|
286
|
+
|
287
|
+
これは条件指定に、行番号の大小に加え、注目するカラムが変化している、つまり、等しくない「<>」を加えたものである。これをみると、
|
288
|
+
|
289
|
+
明らかにstateが変化しない区間を含んでいる。
|
290
|
+
|
291
|
+
|
292
|
+
|
293
|
+
しかし、name別に状態遷移を検出したいが、行番号は飛び飛びであるため、行番号±1にstateに対する変化という条件を課す事ができない。
|
294
|
+
|
295
|
+
|
296
|
+
|
297
|
+
このように、行番号による印が使えないときに、状態遷移を検出するにはどうすればよいでしょうか?
|
2
説明不足を改善
test
CHANGED
File without changes
|
test
CHANGED
@@ -48,11 +48,11 @@
|
|
48
48
|
|
49
49
|
|
50
50
|
|
51
|
-
行番号は一般的には時系列に従う。但し、たまに古いデータを挿入する可能性あ
|
51
|
+
行番号は一般的には時系列に従う。但し、たまにtimeの古いデータを後から挿入する可能性があるため、後述する行番号の比較を隣接するtimeに直した方がよいかもしれない。
|
52
52
|
|
53
53
|
|
54
54
|
|
55
|
-
このように、あるnameのstateを定期的に取得し続けた時にstateに当
|
55
|
+
このように、あるnameのstateを定期的に取得し続けた時にstateに相当するカラム(実際は何でもよい)が指定した方向に変化(live -> dead or dead -> live )または任意の変化を起こしたときに、その遷移が発生している終始のレコード全てを検索するクエリを発行したいです。
|
56
56
|
|
57
57
|
|
58
58
|
|
@@ -60,9 +60,11 @@
|
|
60
60
|
|
61
61
|
ただ、acとなってる行番号ですが、同一nameのレコードは必ずしも隣接しているとは限らない。つまり、
|
62
62
|
|
63
|
-
where name = aとして、特定nameに
|
63
|
+
where name = aとして、特定nameのみのテーブルにしてから遷移を検出しようとすると、
|
64
64
|
|
65
|
+
|
66
|
+
|
65
|
-
行番号が時系列に従い一応増えているが、一般的に
|
67
|
+
行番号が時系列に従い一応増えているという条件はあるが、隣接するレコードのnameは一定とは言えず、特定nameに対する行番号は一般的に飛び飛びになってしまうので、隣接する行番号のstateが違う(where a.row = b.row + 1みたいな)条件が使えない。
|
66
68
|
|
67
69
|
|
68
70
|
|
@@ -78,6 +80,6 @@
|
|
78
80
|
|
79
81
|
|
80
82
|
|
81
|
-
最もやりやすいのは、とりあえず、where name = a で特定nameのテーブルの問題にしてから、行番号または時系列的に隣接するものに対し、さらにautoincrementをつけて、隣接する行同士の特定カラムの変化を書く事かなと。
|
83
|
+
最もやりやすいのは、とりあえず、where name = a で特定nameのテーブルの問題にしてから、行番号または時系列的に隣接するものに対し、さらにユーザー変数によるautoincrementをつけて、隣接する行同士の特定カラムの変化を書く事かなと。
|
82
84
|
|
83
|
-
でも、ユーザー変数@aaでインクリメントしようにもスコープ範囲の問題でうまくいかないし、すべてのnameに対してやる方法が思いつかない。
|
85
|
+
でも、ユーザー変数@aaでインクリメントしようにもスコープ範囲の問題でうまくいかないし、すべてのnameに対してやる方法が思いつかないです。
|
1
追記・修正
test
CHANGED
@@ -1 +1 @@
|
|
1
|
-
mysqlで特定の遷移を
|
1
|
+
mysqlでレコード中の特定の遷移を検出・検索する方法を知りたい。
|
test
CHANGED
@@ -36,11 +36,11 @@
|
|
36
36
|
|
37
37
|
・
|
38
38
|
|
39
|
-
|b| 2016/0
|
39
|
+
|b| 2016/02/17| live |
|
40
40
|
|
41
|
-
|b| 2016/
|
41
|
+
|b| 2016/4/4| dead |
|
42
42
|
|
43
|
-
|b| 2016/
|
43
|
+
|b| 2016/5/01| dead|
|
44
44
|
|
45
45
|
|
46
46
|
|
@@ -52,7 +52,7 @@
|
|
52
52
|
|
53
53
|
|
54
54
|
|
55
|
-
このように、あるnameのstateを定期的に取得し続けた時にstateに当たるカラム(実際は何でもよい)が指定した方向に変化(live
|
55
|
+
このように、あるnameのstateを定期的に取得し続けた時にstateに当たるカラム(実際は何でもよい)が指定した方向に変化(live -> dead or dead -> live )または任意の変化を起こしたときに、その遷移が発生している終始のレコード全てを検索するクエリを発行したいです。
|
56
56
|
|
57
57
|
|
58
58
|
|
@@ -71,3 +71,13 @@
|
|
71
71
|
とりあえず、とびとびのレコードで検索した方法は目的を達成しないものでした。
|
72
72
|
|
73
73
|
説明が下手なので、わからなければ質問をお願いします。
|
74
|
+
|
75
|
+
|
76
|
+
|
77
|
+
追記)
|
78
|
+
|
79
|
+
|
80
|
+
|
81
|
+
最もやりやすいのは、とりあえず、where name = a で特定nameのテーブルの問題にしてから、行番号または時系列的に隣接するものに対し、さらにautoincrementをつけて、隣接する行同士の特定カラムの変化を書く事かなと。
|
82
|
+
|
83
|
+
でも、ユーザー変数@aaでインクリメントしようにもスコープ範囲の問題でうまくいかないし、すべてのnameに対してやる方法が思いつかない。
|