将 2 行合并为 1 行

Question

我想将每 2 行合并为一行表：clock_activitiesid      employee_id   activity     created_at1       1             start_break  1:002       1             end_break    1:103       1             start_break  2:004       1             end_break    2:105       1             start_break  2:306       1             end_break    2:45        7       1             start_break  3:108       1             end_break    3:20我正在寻找的是这样的：start    end       total1:00     1:10      00:102:00     2:10      00:102:30     2:45      00:153:10     3:20      00:10谁能指出我正确的方向？我在 Stackoverflow 上找不到任何具有这些相同要求的内容。

Answer 1

使用自连接：

select c1.employee_id, 

  c1.created_at `start`, min(c2.created_at) `end`,

  time_format(timediff(

    min(c2.created_at),

    c1.created_at

  ), "%H:%i") total

from clock_activities c1 inner join clock_activities c2

on c1.employee_id = c2.employee_id

and c1.activity = 'start_break' and c2.activity = 'end_break'

and c1.created_at < c2.created_at

group by c1.employee_id, c1.created_at

请参阅演示。

结果：

| employee_id | start | end  | total |

| ----------- | ----- | ---- | ----- |

| 1           | 1:00  | 1:10 | 00:10 |

| 1           | 2:00  | 2:10 | 00:10 |

| 1           | 2:30  | 2:45 | 00:15 |

| 1           | 3:10  | 3:20 | 00:10 |

Answer 2

我会用定义记录组的窗口总和来解决这个问题：每次'start_break'看到 a 时，都会启动一个新组。然后您可以聚合：

select 

    employee_id,

    min(case when id = 'start_break' then created_at end) start_break,

    max(case when id = 'end_break' then created_at end) end_break,

    timestampdiff(

        minute, 

        max(case when id = 'end_break' then created_at end),

        min(case when id = 'start_break' then created_at end)

    ) total_minutes

from (

    select t.*, sum(activity = 'start_break') over(partition by employee_id order by id) grp

    from mytable t

)

group by employee_id, grp

Answer 3

    Select id,  min(activity='start_break')

      Over (partition by id<=id%2), 

     Min(activity='end_break')Over 

     (partition by id<=id%2),

     Min(Case when 

       activity='end_break' then 

      Date end) - Min(Case when 

       activity='start_break' then 

      Date end)Over (partition by 

       id<=id%2)

     From table

Answer 4

对于每个开始，您都可以使用窗口函数获得下一个结束：

select employee_id, time, created_at as start_time, end_time,

       timestamp_diff(second, start_time, end_time)

from (select t.*,

             min(case when activity = 'end_break' then created_at end) over (partition by employee_id order by created_at desc) as end_time

      from t

     ) t

where activity = 'start_break';