teratail
回答率
85.48%
質問するログイン新規登録

質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう！

新規登録して質問してみよう
ただいま回答率
85.48%
トップMySQLに関する質問
MySQL

MySQL（マイエスキューエル）は、TCX DataKonsultAB社などが開発するRDBMS（リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

Q&A

3回答

2123閲覧

mysqlでレコード中の特定の遷移を検出・検索する方法を知りたい。

compu
compu

総合スコア8

MySQL

MySQL（マイエスキューエル）は、TCX DataKonsultAB社などが開発するRDBMS（リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

0グッド

0クリップ

投稿2017/03/03 11:10

編集2017/03/03 13:36

0

0

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に対する変化という条件を課す事ができない。

このように、行番号による印が使えないときに、状態遷移を検出するにはどうすればよいでしょうか？

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

yambejp
yambejp

2017/03/03 12:18

質問内容がわかりません。例示の日付データがガタガタなので揃えてください。日にち単位で管理しているように見えますがdatetimeでやらないのでしょうか？
guest

回答3件

0

sql
1SELECT
2  t1.name,
3  t1.after_rows,
4  t1.after_time,
5  t1.after_state,
6  t1.before_rows,
7  t1.before_time,
8  t1.before_state
9FROM (
10  SELECT
11    after_test.name,
12    after_test.rows after_rows,
13    after_test.time after_time,
14    after_test.state after_state,
15    before_test.rows before_rows,
16    before_test.time before_time,
17    before_test.state before_state,
18    TIMEDIFF(after_test.time, before_test.time) AS diff
19  FROM test AS after_test
20  INNER JOIN test AS before_test
21    ON after_test.name = before_test.name AND after_test.time > before_test.time
22) AS t1
23LEFT OUTER JOIN (
24  SELECT 
25    after_test.name,
26    after_test.rows after_rows,
27    after_test.time after_time,
28    after_test.state after_state,
29    before_test.rows before_rows,
30    before_test.time before_time,
31    before_test.state before_state,
32    TIMEDIFF(after_test.time, before_test.time) AS diff
33  FROM test AS after_test
34  INNER JOIN test AS before_test
35    ON after_test.name = before_test.name AND after_test.time > before_test.time
36) AS t2
37ON t1.after_rows = t2.after_rows AND t1.diff > t2.diff
38WHERE t2.diff IS NULL
39  AND t1.after_state <> t1.before_state;

実行結果

mysql> CREATE TABLE test (
    ->   rows INT PRIMARY KEY AUTO_INCREMENT,
    ->   name VARCHAR(1),
    ->   time DATETIME,
    ->   state VARCHAR(4)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO test VALUES
    -> (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');
Query OK, 70 rows affected (0.00 sec)
Records: 70  Duplicates: 0  Warnings: 0

mysql> SELECT
    ->   t1.name,
    ->   t1.after_rows,
    ->   t1.after_time,
    ->   t1.after_state,
    ->   t1.before_rows,
    ->   t1.before_time,
    ->   t1.before_state
    -> FROM (
    ->   SELECT
    ->     after_test.name,
    ->     after_test.rows after_rows,
    ->     after_test.time after_time,
    ->     after_test.state after_state,
    ->     before_test.rows before_rows,
    ->     before_test.time before_time,
    ->     before_test.state before_state,
    ->     TIMEDIFF(after_test.time, before_test.time) AS diff
    ->   FROM test AS after_test
    ->   INNER JOIN test AS before_test
    ->     ON after_test.name = before_test.name AND after_test.time > before_test.time
    -> ) AS t1
    -> LEFT OUTER JOIN (
    ->   SELECT 
    ->     after_test.name,
    ->     after_test.rows after_rows,
    ->     after_test.time after_time,
    ->     after_test.state after_state,
    ->     before_test.rows before_rows,
    ->     before_test.time before_time,
    ->     before_test.state before_state,
    ->     TIMEDIFF(after_test.time, before_test.time) AS diff
    ->   FROM test AS after_test
    ->   INNER JOIN test AS before_test
    ->     ON after_test.name = before_test.name AND after_test.time > before_test.time
    -> ) AS t2
    -> ON t1.after_rows = t2.after_rows AND t1.diff > t2.diff
    -> WHERE t2.diff IS NULL
    ->   AND t1.after_state <> t1.before_state;
+------+------------+---------------------+-------------+-------------+---------------------+--------------+
| name | after_rows | after_time          | after_state | before_rows | before_time         | before_state |
+------+------------+---------------------+-------------+-------------+---------------------+--------------+
| a    |         16 | 2017-03-03 21:47:26 | dead        |           5 | 2017-03-03 21:47:03 | live         |
| c    |         17 | 2017-03-03 21:47:28 | dead        |          12 | 2017-03-03 21:47:18 | live         |
| b    |         15 | 2017-03-03 21:47:24 | dead        |          13 | 2017-03-03 21:47:20 | live         |
| d    |         18 | 2017-03-03 21:47:30 | dead        |          14 | 2017-03-03 21:47:22 | live         |
| b    |         31 | 2017-03-03 21:48:37 | live        |          20 | 2017-03-03 21:47:34 | dead         |
| a    |         36 | 2017-03-03 21:48:48 | live        |          25 | 2017-03-03 21:48:25 | dead         |
| c    |         33 | 2017-03-03 21:48:41 | live        |          27 | 2017-03-03 21:48:29 | dead         |
| d    |         34 | 2017-03-03 21:48:43 | live        |          30 | 2017-03-03 21:48:35 | dead         |
| d    |         47 | 2017-03-03 21:49:10 | dead        |          34 | 2017-03-03 21:48:43 | live         |
| c    |         48 | 2017-03-03 21:49:12 | dead        |          44 | 2017-03-03 21:49:04 | live         |
| b    |         49 | 2017-03-03 21:49:14 | dead        |          45 | 2017-03-03 21:49:06 | live         |
| a    |         57 | 2017-03-03 21:49:31 | dead        |          46 | 2017-03-03 21:49:08 | live         |
| a    |         63 | 2017-03-03 21:49:43 | live        |          58 | 2017-03-03 21:49:33 | dead         |
| d    |         64 | 2017-03-03 21:49:46 | live        |          59 | 2017-03-03 21:49:35 | dead         |
| c    |         61 | 2017-03-03 21:49:39 | live        |          60 | 2017-03-03 21:49:37 | dead         |
+------+------------+---------------------+-------------+-------------+---------------------+--------------+
15 rows in set (0.02 sec)

http://sqlfiddle.com/#!9/419fbc3/1

投稿2017/03/05 10:56

KiyoshiMotoki
KiyoshiMotoki

総合スコア4791

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

0

曲解していたらスミマセン。以下のような感じでしょうか？

  • 元データ
SQL
1create table tbl(id int primary key,name varchar(20),dt datetime,status varchar(10),index(name,dt));
2
3insert into tbl values
4(1,'c','2017-03-03 21:46:55','live'),
5(2,'a','2017-03-03 21:46:57','live'),
6(3,'b','2017-03-03 21:46:59','live'),
7(4,'c','2017-03-03 21:47:01','live'),
8(5,'a','2017-03-03 21:47:03','live'),
9(6,'d','2017-03-03 21:47:06','live'),
10(7,'c','2017-03-03 21:47:08','live'),
11(8,'d','2017-03-03 21:47:10','live'),
12(9,'d','2017-03-03 21:47:12','live'),
13(10,'b','2017-03-03 21:47:14','live'),
14(11,'b','2017-03-03 21:47:16','live'),
15(12,'c','2017-03-03 21:47:18','live'),
16(13,'b','2017-03-03 21:47:20','live'),
17(14,'d','2017-03-03 21:47:22','live'),
18(15,'b','2017-03-03 21:47:24','dead'),
19(16,'a','2017-03-03 21:47:26','dead'),
20(17,'c','2017-03-03 21:47:28','dead'),
21(18,'d','2017-03-03 21:47:30','dead'),
22(19,'d','2017-03-03 21:47:32','dead'),
23(20,'b','2017-03-03 21:47:34','dead'),
24(21,'c','2017-03-03 21:48:17','dead'),
25(22,'a','2017-03-03 21:48:19','dead'),
26(23,'a','2017-03-03 21:48:21','dead'),
27(24,'c','2017-03-03 21:48:23','dead'),
28(25,'a','2017-03-03 21:48:25','dead'),
29(26,'d','2017-03-03 21:48:27','dead'),
30(27,'c','2017-03-03 21:48:29','dead'),
31(28,'d','2017-03-03 21:48:31','dead'),
32(29,'d','2017-03-03 21:48:33','dead'),
33(30,'d','2017-03-03 21:48:35','dead'),
34(31,'b','2017-03-03 21:48:37','live'),
35(32,'b','2017-03-03 21:48:39','live'),
36(33,'c','2017-03-03 21:48:41','live'),
37(34,'d','2017-03-03 21:48:43','live'),
38(35,'b','2017-03-03 21:48:46','live'),
39(36,'a','2017-03-03 21:48:48','live'),
40(37,'b','2017-03-03 21:48:50','live'),
41(38,'b','2017-03-03 21:48:52','live'),
42(39,'a','2017-03-03 21:48:54','live'),
43(40,'b','2017-03-03 21:48:56','live'),
44(41,'b','2017-03-03 21:48:58','live'),
45(42,'c','2017-03-03 21:49:00','live'),
46(43,'a','2017-03-03 21:49:02','live'),
47(44,'c','2017-03-03 21:49:04','live'),
48(45,'b','2017-03-03 21:49:06','live'),
49(46,'a','2017-03-03 21:49:08','live'),
50(47,'d','2017-03-03 21:49:10','dead'),
51(48,'c','2017-03-03 21:49:12','dead'),
52(49,'b','2017-03-03 21:49:14','dead'),
53(50,'b','2017-03-03 21:49:16','dead'),
54(51,'b','2017-03-03 21:49:19','dead'),
55(52,'d','2017-03-03 21:49:21','dead'),
56(53,'d','2017-03-03 21:49:23','dead'),
57(54,'d','2017-03-03 21:49:25','dead'),
58(55,'c','2017-03-03 21:49:27','dead'),
59(56,'b','2017-03-03 21:49:29','dead'),
60(57,'a','2017-03-03 21:49:31','dead'),
61(58,'a','2017-03-03 21:49:33','dead'),
62(59,'d','2017-03-03 21:49:35','dead'),
63(60,'c','2017-03-03 21:49:37','dead'),
64(61,'c','2017-03-03 21:49:39','live'),
65(62,'c','2017-03-03 21:49:41','live'),
66(63,'a','2017-03-03 21:49:43','live'),
67(64,'d','2017-03-03 21:49:46','live'),
68(65,'c','2017-03-03 21:49:48','live'),
69(66,'c','2017-03-03 21:49:50','live'),
70(67,'c','2017-03-03 21:49:52','live'),
71(68,'d','2017-03-03 21:49:54','live'),
72(69,'c','2017-03-03 21:49:56','live'),
73(70,'d','2017-03-03 21:49:58','live');

修正

前後がいる？

  • viewを用意します
SQL
1create view vw as select id,name,dt,status,(select count(*)+1 from tbl as t2 where t2.name=t1.name and t2.dt<t1.dt) as rank from tbl as t1;
  • 遷移抽出

nameごとに前後いずれかに遷移があったレコードを抽出

SQL
1select v2.id,v2.rank,v2.name,v2.dt
2,v1.status as beforestatus
3,v2.status
4,v3.status as afterstatus
5from vw as v1
6inner join vw  as v2 on v1.name=v2.name and  v1.rank+1=v2.rank
7inner join vw  as v3 on v2.name=v3.name and  v2.rank+1=v3.rank
8where not(v1.status=v2.status) or not(v2.status=v3.status)
9order by v2.name,v2.id

投稿2017/03/03 13:50

編集2017/03/03 14:17
yambejp
yambejp

総合スコア114767

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

compu
compu

2017/03/03 14:18 編集

回答ありがとうございます。 作成されたテーブルはそのような感じです(indexについては未習のため不明)。 ただ、rank関数まで勉強していないです。(window関数やってない)、 今window関数までやるべきとわかったので出直します。 そして、提示されたソースの意味と、実行結果が理解できたらまたコメントいたします。
yambejp
yambejp

2017/03/03 14:25

データの抽出根拠がよくわからないのでとりあえず前後のデータをチェックするようにしてあります
compu
compu

2017/03/03 14:35

修正された方法で目的が達成できました。 きちんと検出できました ありがとうございます。 特定の遷移を検出する方法は特定の遷移がある全てのデータに適用でき、汎用性が高いと思いますが、 知識が足りない部分がありますので、理解するまで再び質問するかもしれないです。 ご了承下さい。
guest

0

作成してみました。

sql
1SELECT t11.rows, t11.name, t11.time, t11.state FROM
2(SELECT @i:=@i+1 AS no, t10.*
3 FROM (SELECT @i:=0) AS seqno
4 , (SELECT * FROM states_test1 ORDER BY name,time) as t10) as t11
5LEFT JOIN
6(SELECT @j:=@j+1 AS no, t20.*
7FROM (SELECT @j:=0) AS seqno
8, (SELECT * FROM states_test1 ORDER BY name,time) as t20) as t21
9ON t11.no = t21.no+1
10LEFT JOIN
11(SELECT @k:=@k+1 AS no, t30.*
12FROM (SELECT @k:=0) AS seqno
13, (SELECT * FROM states_test1 ORDER BY name,time) as t30) as t31
14ON t11.no = t31.no-1
15WHERE t11.name != t21.name OR t11.state != t21.state
16   OR t11.name != t31.name OR t11.state != t31.state
17   OR t21.name is null OR t31.name is null
18ORDER BY t11.name, t11.rows;

投稿2017/03/04 03:40

A.Ichi
A.Ichi

総合スコア4070

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

質問の解決につながる回答をしましょう。 サンプルコードなど、より具体的な説明があると質問者の理解の助けになります。 また、読む側のことを考えた、分かりやすい文章を心がけましょう。

まだベストアンサーが選ばれていません

会員登録して回答してみよう

アカウントをお持ちの方は

15分調べてもわからないことは
teratailで質問しよう！

ただいまの回答率
85.48%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問

トップMySQLに関する質問

mysqlでレコード中の特定の遷移を検出・検索する方法を知りたい。