将 2 行合并为 1 行

Question

我想将每 2 行合并为一行

表：clock_activities

id      employee_id   activity     created_at

1       1             start_break  1:00

2       1             end_break    1:10

3       1             start_break  2:00

4       1             end_break    2:10

5       1             start_break  2:30

6       1             end_break    2:45        

7       1             start_break  3:10

8       1             end_break    3:20

我正在寻找的是这样的：

start    end       total

1:00     1:10      00:10

2:00     2:10      00:10

2:30     2:45      00:15

3:10     3:20      00:10

谁能指出我正确的方向？我在 Stackoverflow 上找不到任何具有这些相同要求的内容。

Answer 1

使用自连接：select c1.employee_id,&nbsp;&nbsp; c1.created_at `start`, min(c2.created_at) `end`,&nbsp; time_format(timediff(&nbsp; &nbsp; min(c2.created_at),&nbsp; &nbsp; c1.created_at&nbsp; ), "%H:%i") totalfrom clock_activities c1 inner join clock_activities c2on c1.employee_id = c2.employee_idand c1.activity = 'start_break' and c2.activity = 'end_break'and c1.created_at < c2.created_atgroup by c1.employee_id, c1.created_at请参阅演示。结果：| employee_id | start | end&nbsp; | total || ----------- | ----- | ---- | ----- || 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| 1:00&nbsp; | 1:10 | 00:10 || 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| 2:00&nbsp; | 2:10 | 00:10 || 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| 2:30&nbsp; | 2:45 | 00:15 || 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| 3:10&nbsp; | 3:20 | 00:10 |

Answer 2

我会用定义记录组的窗口总和来解决这个问题：每次'start_break'看到 a 时，都会启动一个新组。然后您可以聚合：select&nbsp;&nbsp; &nbsp; employee_id,&nbsp; &nbsp; min(case when id = 'start_break' then created_at end) start_break,&nbsp; &nbsp; max(case when id = 'end_break' then created_at end) end_break,&nbsp; &nbsp; timestampdiff(&nbsp; &nbsp; &nbsp; &nbsp; minute,&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; max(case when id = 'end_break' then created_at end),&nbsp; &nbsp; &nbsp; &nbsp; min(case when id = 'start_break' then created_at end)&nbsp; &nbsp; ) total_minutesfrom (&nbsp; &nbsp; select t.*, sum(activity = 'start_break') over(partition by employee_id order by id) grp&nbsp; &nbsp; from mytable t)group by employee_id, grp

Answer 3

&nbsp; &nbsp; Select id,&nbsp; min(activity='start_break')&nbsp; &nbsp; &nbsp; Over (partition by id<=id%2),&nbsp;&nbsp; &nbsp; &nbsp;Min(activity='end_break')Over&nbsp;&nbsp; &nbsp; &nbsp;(partition by id<=id%2),&nbsp; &nbsp; &nbsp;Min(Case when&nbsp;&nbsp; &nbsp; &nbsp; &nbsp;activity='end_break' then&nbsp;&nbsp; &nbsp; &nbsp; Date end) - Min(Case when&nbsp;&nbsp; &nbsp; &nbsp; &nbsp;activity='start_break' then&nbsp;&nbsp; &nbsp; &nbsp; Date end)Over (partition by&nbsp;&nbsp; &nbsp; &nbsp; &nbsp;id<=id%2)&nbsp; &nbsp; &nbsp;From table

Answer 4

对于每个开始，您都可以使用窗口函数获得下一个结束：select employee_id, time, created_at as start_time, end_time,&nbsp; &nbsp; &nbsp; &nbsp;timestamp_diff(second, start_time, end_time)from (select t.*,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;min(case when activity = 'end_break' then created_at end) over (partition by employee_id order by created_at desc) as end_time&nbsp; &nbsp; &nbsp; from t&nbsp; &nbsp; &nbsp;) twhere activity = 'start_break';