Блог

Я пытаюсь рассчитать ежемесячное рабочее время каждого курьера. Это данные компании-курьера, и у каждого курьера разное время, когда он начинает свою смену и заканчивает ее.

Это пример того, как выглядит таблица:

Идентификатор типа = 2000 означает «Окончание смены», а идентификатор типа = 7000 означает «Начало смены». Курьер может начинать свою смену и заканчивать ее несколько раз в течение дня.

Это запрос, который я пробовал:

 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 
 

Желаемый результат:

Я также пытался самостоятельно присоединиться, но, похоже, ни один из них не дает правильного количества часов. Может кто-нибудь, пожалуйста, скажите мне, в чем проблема?

Как насчет суммирования разницы во времени между текущей строкой и следующей строкой, за исключением случаев, когда текущая строка является концом смены? Это можно легко сделать с помощью оконных функций:

 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
 

Это будет работать, если нет записей за пределами начала / конца отправки, то есть за окончанием смены всегда следует начало смены.