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

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

ただいまの
回答率

90.35%

  • MySQL

    6137questions

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

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

受付中

回答 3

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 553

compu

score 2

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

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

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

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

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

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • yambejp

    2017/03/03 21:18

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

    キャンセル

回答 3

+4

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

  • 元データ
create table tbl(id int primary key,name varchar(20),dt datetime,status varchar(10),index(name,dt));

insert into tbl 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');

 修正

前後がいる?

  • viewを用意します
create 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ごとに前後いずれかに遷移があったレコードを抽出
select v2.id,v2.rank,v2.name,v2.dt
,v1.status as beforestatus
,v2.status
,v3.status as afterstatus
from vw as v1
inner join vw  as v2 on v1.name=v2.name and  v1.rank+1=v2.rank
inner join vw  as v3 on v2.name=v3.name and  v2.rank+1=v3.rank
where not(v1.status=v2.status) or not(v2.status=v3.status)
order by v2.name,v2.id

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/03/03 23:16 編集

    回答ありがとうございます。
    作成されたテーブルはそのような感じです(indexについては未習のため不明)。

    ただ、rank関数まで勉強していないです。(window関数やってない)、
    今window関数までやるべきとわかったので出直します。
    そして、提示されたソースの意味と、実行結果が理解できたらまたコメントいたします。

    キャンセル

  • 2017/03/03 23:25

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

    キャンセル

  • 2017/03/03 23:35

    修正された方法で目的が達成できました。
    きちんと検出できました
    ありがとうございます。

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

    キャンセル

+4

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;


実行結果

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

-4

作成してみました。

SELECT t11.rows, t11.name, t11.time, t11.state FROM
(SELECT @i:=@i+1 AS no, t10.*
 FROM (SELECT @i:=0) AS seqno
 , (SELECT * FROM states_test1 ORDER BY name,time) as t10) as t11
LEFT JOIN
(SELECT @j:=@j+1 AS no, t20.*
FROM (SELECT @j:=0) AS seqno
, (SELECT * FROM states_test1 ORDER BY name,time) as t20) as t21
ON t11.no = t21.no+1
LEFT JOIN
(SELECT @k:=@k+1 AS no, t30.*
FROM (SELECT @k:=0) AS seqno
, (SELECT * FROM states_test1 ORDER BY name,time) as t30) as t31
ON t11.no = t31.no-1
WHERE t11.name != t21.name OR t11.state != t21.state
   OR t11.name != t31.name OR t11.state != t31.state
   OR t21.name is null OR t31.name is null
ORDER BY t11.name, t11.rows;

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

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

  • ただいまの回答率 90.35%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

同じタグがついた質問を見る

  • MySQL

    6137questions

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