时间戳

首页 > 解决方案 > SQL：计算两个时间间隔之间的工作时间

sql - SQL：计算两个时间间隔之间的工作时间

问题描述

我正在尝试计算每个送货员的每月工作时间。这是一家快递公司的数据，每个快递员开始轮班和结束轮班的时间都不一样。

这是表格外观的示例：

类型_ID Delivery_Guy_ID 创建日期
5000 210 2020-11-16 16:34:43
7000 210 2020-11-16 16:35:24
3000 210 2020-11-16 16:35:46
3000 210 2020-11-16 16:37:41
4000 210 2020-11-16 16:39:41
3000 210 2020-11-16 16:42:53
4000 210 2020-11-16 16:47:53
3000 210 2020-11-16 16:48:16
4000 210 2020-11-16 16:50:16
3000 210 2020-11-16 16:53:01
2000 210 2020-11-16 18:53:07

类型 ID = 2000 指的是“Shift Ended”，类型 ID = 7000 指的是“Shift Started”。送货员可能会在一天中多次开始轮班和结束轮班。

这是我尝试过的查询：

WITH working_hours_cte AS 
( 
       SELECT * 
       FROM   ( 
                       SELECT   id AS id 
                                , 
                                type_id AS type_id 
                                , 
                                delivery_guy_id AS delivery_guy_id
                                , 
                                Timezone('Africa/Cairo', date_created) as date_created
                       FROM     delivery_guys_event
                       ORDER BY delivery_guy_id,
                                date(date_created), 
                                date_created ) AS t 
       WHERE  t.date_created >= date('11-01-2020') 
       AND    t.date_created <= date('11-30-2020') ), get_shift_start_time_cte AS 
( 
                SELECT DISTINCT 
                ON( 
                                                t.delivery_guy_id) t.* , 
                                t.date_created::timestamptz AS shift_start_time 
                FROM            working_hours_cte  AS t 
                WHERE           t.type_id = 7000 
                GROUP BY        1, 
                                2, 
                                3, 
                                4 
                                
                ORDER BY        t.delivery_guy_id, 
                                date(date_created), 
                                t.date_created), get_shift_end_time_cte AS 
( 
                SELECT DISTINCT 
                ON( 
                                                t.delivery_guy_id) t.* , 
                                t.date_created::timestamptz AS shift_end_time 
                FROM            working_hours_cte  AS t 
                WHERE           t.type_id = 2000 
                GROUP BY        1, 
                                2, 
                                3, 
                                4
                ORDER BY        t.delivery_guy_id, 
                                date(date_created), 
                                t.date_created), get_working_hours_cte1 AS 
( 
          SELECT    base.* , 
                    (date_part('day', ap.shift_end_time::timestamptz - aa.shift_start_time::timestamptz) * 24 + date_part('hour', ap.shift_end_time::timestamptz - aa.shift_start_time::timestamptz)) * 60 + date_part('minute', ap.shift_end_time::timestamptz - aa.shift_start_time::timestamptz) AS working_hours
                     
          FROM      working_hours_cte AS base 
          LEFT JOIN get_shift_start_time_cte   AS aa 
          ON        base.delivery_guy_id = aa.delivery_guy_id
          LEFT JOIN get_shift_end_time_cte AS ap 
          ON        base.delivery_guy_id = ap.delivery_guy_id) 
SELECT    u.NAME                                          AS delivery_guy_name
          , 
          round(sum(timings.working_hours::numeric),0)/60 AS working_hours 
FROM      get_working_hours_cte1                          AS timings 
LEFT JOIN delivery_guys_contacts                          AS u  
ON        timings.delivery_guy_id = u.id 
WHERE     timings.date_created >= date('11/01/2020') 
AND       timings.date_created <= date('11/30/2020') 
GROUP BY  delivery_guy_name 
ORDER BY  delivery_guy_name ASC

期望的输出：

Delivery_guy_name 工作时间
约翰 200
迈克尔 150
乔 230

我也尝试自行加入，但似乎没有人给出正确的小时数。有人可以告诉我问题出在哪里吗？

标签： sqlpostgresqldatetimegaps-and-islandsdate-arithmetic

解决方案

将当前行与下一行之间的时间差相加如何，除非当前行是班次结束？这可以通过窗口函数轻松完成：

select delivery_guy_id, sum(diff) as total_diff
from (
    select delivery_guy_id, 
        lead(date_created) over(partition by delivery_guy_id order by date_created) 
            - date_created as diff
    from mytable
) t
wxhere type_id <> 2000
group by delivery_guy_id

如果在船舶的开始/结束之外没有记录，这将起作用 - 也就是说，班次结束后总是有班次开始。

推荐阅读