我想將每 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 上找不到任何具有這些相同要求的內容。
4 回答
炎炎設計
TA貢獻1808條經驗 獲得超4個贊
使用自連接：
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 |
白衣染霜花
TA貢獻1796條經驗 獲得超10個贊
我會用定義記錄組的窗口總和來解決這個問題：每次'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
慕無忌1623718
TA貢獻1744條經驗 獲得超4個贊
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
楊魅力
TA貢獻1811條經驗 獲得超6個贊
對于每個開始，您都可以使用窗口函數獲得下一個結束：
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';
- 4 回答
- 0 關注
- 143 瀏覽
添加回答
舉報
0/150
提交
取消