Mysql:查找重复事件的开始

Question

我的数据库有很多志愿 worker 的时间戳条目:我们称它们为“worker1”和“worker2”。我需要找到worker1何时开始工作以及之后worker2何时开始工作。 (它们一次交替工作)同一工作人员可以有多个连续的时间戳。

(1, 1351508000, 'worker1'), 
(2, 1351508020, 'worker1'),  
(3, 1351508060, 'worker2'), 
(4, 1351508070, 'worker1'),  
(5, 1351508089, 'worker1'), 
(6, 1351508200, 'worker1'),  
(7, 1351508300, 'worker1'), 
(8, 1351508370, 'worker2'),   
(9, 1351508410, 'worker2'), 
(10,1351508450, 'worker2'),   
(11,1351508530, 'worker1'), 
(12,1351508700, 'worker2'),   
(13,1351508780, 'worker2') 

所以结果应该是:

1351508000, 'worker1'
1351508060, 'worker2'
1351508070, 'worker1'
1351508370, 'worker2'
1351508530, 'worker1'
1351508700, 'worker2'

感谢您帮助我们!

http://sqlfiddle.com/#!9/483706/2

Answer 1

问题的核心可以这样解决......

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id SERIAL PRIMARY KEY
,timestamp int NOT NULL
,worker char(38) NOT NULL
);

INSERT INTO my_table VALUES
(1,  1351508000,     'worker1'), 
(2,   1351508020,     'worker1'),  
(3,   1351508060,     'worker2'), 
(4,   1351508070,     'worker1'),  
(5, 1351508089,     'worker1'), 
(6,   1351508200,     'worker1'),  
(7,   1351508300,     'worker1'), 
(8,   1351508370,     'worker2'),   
(9,   1351508410,     'worker2'), 
(10,  1351508450,     'worker2'),   
(11,  1351508530,     'worker1'), 
(12,  1351508540,     'worker2'),   
(13,  1351508580,     'worker2'), 
(14,  1351508600,     'worker2'),  
(15,  1351509290,     'worker2'), 
(16,  1351509380,     'worker1'),  
(17,  1351509390,     'worker1'),
(18,  1351509460,     'worker2'),  
(19,  1351509480,     'worker2'),  
(20,  1351509710,     'worker1'), 
(21,  1361510000,     'worker1'), 
(22,  1361510020,     'worker1'),   
(23,  1361510060,     'worker1'), 
(24,  1361510070,     'worker1') ;

SELECT x.*
     , CASE WHEN @prev=worker THEN @i:=@i ELSE @i:=@i+1 END i
     , @prev:=worker
  FROM my_table x
     , (SELECT @prev:=null,@i:=0) vars
 ORDER 
    BY timestamp;
+----+------------+---------+------+---------------+
| id | timestamp  | worker  | i    | @prev:=worker |
+----+------------+---------+------+---------------+
|  1 | 1351508000 | worker1 |    1 | worker1       |
|  2 | 1351508020 | worker1 |    1 | worker1       |
|  3 | 1351508060 | worker2 |    2 | worker2       |
|  4 | 1351508070 | worker1 |    3 | worker1       |
|  5 | 1351508089 | worker1 |    3 | worker1       |
|  6 | 1351508200 | worker1 |    3 | worker1       |
|  7 | 1351508300 | worker1 |    3 | worker1       |
|  8 | 1351508370 | worker2 |    4 | worker2       |
|  9 | 1351508410 | worker2 |    4 | worker2       |
| 10 | 1351508450 | worker2 |    4 | worker2       |
| 11 | 1351508530 | worker1 |    5 | worker1       |
| 12 | 1351508540 | worker2 |    6 | worker2       |
| 13 | 1351508580 | worker2 |    6 | worker2       |
| 14 | 1351508600 | worker2 |    6 | worker2       |
| 15 | 1351509290 | worker2 |    6 | worker2       |
| 16 | 1351509380 | worker1 |    7 | worker1       |
| 17 | 1351509390 | worker1 |    7 | worker1       |
| 18 | 1351509460 | worker2 |    8 | worker2       |
| 19 | 1351509480 | worker2 |    8 | worker2       |
| 20 | 1351509710 | worker1 |    9 | worker1       |
| 21 | 1361510000 | worker1 |    9 | worker1       |
| 22 | 1361510020 | worker1 |    9 | worker1       |
| 23 | 1361510060 | worker1 |    9 | worker1       |
| 24 | 1361510070 | worker1 |    9 | worker1       |
+----+------------+---------+------+---------------+

从这里，您可以将上面的内容用作 GROUPWISE MAX(或 MIN)标准模式中的子查询，如下所示...

SELECT a.*
  FROM my_table a
  JOIN 
     ( SELECT MIN(id) id 
         FROM
            ( SELECT x.*
                   , CASE WHEN @prev=worker THEN @i:=@i ELSE @i:=@i+1 END i
                   , @prev:=worker
                FROM my_table x
                   , (SELECT @prev:=null,@i:=0) vars
               ORDER 
                  BY timestamp
            ) n
        GROUP 
           BY i
     ) b
    ON b.id = a.id;

+----+------------+---------+
| id | timestamp  | worker  |
+----+------------+---------+
|  1 | 1351508000 | worker1 |
|  3 | 1351508060 | worker2 |
|  4 | 1351508070 | worker1 |
|  8 | 1351508370 | worker2 |
| 11 | 1351508530 | worker1 |
| 12 | 1351508540 | worker2 |
| 16 | 1351509380 | worker1 |
| 18 | 1351509460 | worker2 |
| 20 | 1351509710 | worker1 |
+----+------------+---------+