pyspark 计算每天的业务小时数- sql

w41d8nur  于 2023-11-16  发布在  Spark
关注(0)|答案(1)|浏览(124)

早上好，
我有一个表格，每个项目都有一个“初始交付时间”和一个“最终交付时间”。我需要计算每次交付需要多少工作时间，然后**汇总（按最终交付时间）**以查看我的货件每天交付所需的平均工作时间。
对于我们的业务，我们认为“周一至周五的营业时间”为上午9点至下午6点（18：00）。
货件的初始时间可能安排在营业时间以外的任何时间。在这种情况下，我们必须仅在输入营业时间时开始盘点。例如：
如果货件的初始交付时间设定为周四下午7点，而最终交付时间为周五上午10点，则我们应仅考虑1个小时（从周五上午9点到周五上午10点，知道周四下午7点到周五上午9点是工作时间以外）。
尝试执行下面的查询-但它不遵守我们所有的业务规则（尚未聚合）
不幸的是，在办公室里，我们不能在SQL中使用“变量”（我们使用一种叫做Metadata的东西--这使得它更难
有人愿意帮我吗？

SELECT 
    SUM(
        TIMESTAMPDIFF(
            HOUR, 
            GREATEST(data_inicial, TIMESTAMP(DATE(data_inicial), '09:00:00')), 
            LEAST(data_final, TIMESTAMP(DATE(data_final), '18:00:00'))
        )
    ) AS hours
FROM table
WHERE WEEKDAY(data_inicial) BETWEEN 0 AND 4
  AND HOUR(data_inicial) < 18
  AND WEEKDAY(data_final) BETWEEN 0 AND 4
  AND HOUR(data_final) >= 9
UNION
SELECT 
    COUNT(*) * 9 AS hours
FROM table
WHERE WEEKDAY(data_inicial) BETWEEN 0 AND 4
  AND HOUR(data_inicial) >= 9
UNION
SELECT 
    COUNT(*) * 9 AS horas_uteis
FROM table
WHERE WEEKDAY(data_final) BETWEEN 0 AND 4
  AND HOUR(data_final) < 18;

字符串

pyspark

1条答案

按热度按时间
qjp7pelc

qjp7pelc1#

对于这个任务，我认为最好使用日历表。例如，这个日历表临时创建为CTE。
您可以使用带有索引的永久表，例如on（date，startH，endH，workDay），该表易于维护，工作日历有助于设置任何一天的属性，如节假日等。
在示例中，由最终交货制成的骨料date
参见示例：

with recursive WorkCalendar as( -- work calendar, sufficient for test data
  select 1 n
    ,date('2023-10-23') dt
    ,case when weekday(date('2023-10-23')) between 0 and 4 then 1 else 0 end workDay
    ,case when weekday(date('2023-10-23')) between 0 and 4 then 9 else 0 end startH
    ,case when weekday(date('2023-10-23')) between 0 and 4 then 18 else 0 end endH
    ,weekday(date('2023-10-23')) wd
    ,week(date('2023-10-23')) w
    ,dayname(date('2023-10-23')) dn
union all
  select  n+1
    ,date_add(dt, interval 1 day)  
    ,case when weekday(date_add(dt, interval 1 day)) between 0 and 4 then 1 else 0 end workDay
    ,case when weekday(date_add(dt, interval 1 day)) between 0 and 4 then 9 else 0 end startH
    ,case when weekday(date_add(dt, interval 1 day)) between 0 and 4 then 18 else 0 end endH
    ,weekday(date_add(dt, interval 1 day)) wd
    ,week(date_add(dt, interval 1 day)) w
    ,dayname(date_add(dt, interval 1 day)) dn
  from WorkCalendar where date_add(dt, interval 1 day)<date('2023-11-23')
)
,ShipmentDt as( -- subquery for to shorten formulas
select * 
  ,hour(data_inicial) h1 ,cast(data_inicial as date) dt1
  ,hour(data_final) h2 ,cast(data_final as date) dt2
from Shipment
)
,ShipmentHours as(
select id,data_inicial,data_final,c.dt,c.workDay,c.startH,c.endH 
     -- hours in data_inicial
      case when dt2>dt1 then  
        endH-case when h1<startH then startH when h1>endH then endH else h1  end
      else -- intraday
        case when h2<startH then startH when h2>endH then endH else h2  end
       -case when h1<startH then startH when h1>endH then endH else h1  end
      end 
    else 0
    end hours_first
    -- hours in data_final
   ,case when dt2>dt1 and c.dt=dt2 then
        case when h2<startH then 0 when h2>endH then (endH-startH) else h2-startH  end
    else 0
    end hours_last
    -- hours in data between (not inculded) data_inicial and data_final
   ,case when c.dt>dt1 and c.dt<dt2 then
        endH-startH
    else 0
    end hours_inter
from ShipmentDt sd
left join WorkCalendar c on c.dt>=sd.dt1 and c.dt<=sd.dt2
)
,ShipmentWorkHours as( -- total work hours for shipment
select id,min(data_inicial) data_inicial,min(data_final)data_final
  ,sum((hours_first+hours_last+hours_inter)*workDay) as totH
from ShipmentHours
group by id
)
,AvgDelivery as( -- average work hours, delivered in date - target task
select cast(data_final as date) data_final, avg(totH) avgH,count(*) cnt
from ShipmentWorkHours
group by cast(data_final as date)
)
,ActiveShipments as( -- additional - count of active shipments by date
select dt,count(*) cnt
from ShipmentHours
group by dt
)
,ActiveDeliveryByDate as( -- additional - active shipments list for date
select dt,id,data_inicial,data_final
from ShipmentHours
where dt=cast('2023-10-25' as date)
)
 select * from AvgDelivery
-- select * from ActiveShipments
-- select * from ActiveDeliveryByDate
-- select * from ShipmentWorkHours

字符串
使用示例数据查询结果
| 最终数据|平均值H| CNT|
| --|--|--|
| 2023年10月23日|9万| 3 |
| 2023年10月26日星期五|14.8千| 5 |
| 2023年10月27日|一万七千元| 1 |
| 2023年10月24日|八点六六六七| 6 |
| 2023年10月28日|三万六千元| 1 |
| 2023年10月29日|三万七千元| 1 |
| 2023年10月30日|四万六千元| 1 |
| 2023年10月31日|五万五千元| 1 |
| 2023年11月5日|八万二| 1 |
| 2023年11月6日|九万元| 1 |
有效发运示例
| DT| CNT|
| --|--|
| 2023年10月23日| 15 |
| 2023年10月26日星期五| 12 |
| 2023年10月25日| 11 |
| 2023年10月27日| 7 |
| 2023年10月24日| 12 |
| 2023年10月28日| 6 |
| 2023年10月29日| 5 |
| 2023年10月30日| 4 |
| 2023年10月31日| 3 |
| 2023年11月5日| 2 |
| 2023年11月4日| 2 |
| 2023年11月3日| 2 |
| 2023年11月2日| 2 |
| 2023年11月1日| 2 |
| 2023年11月6日| 1 |
“按日期有效发运”示例
| DT| ID|数据初始化|最终数据|
| --|--|--|--|
| 2023年10月25日| 14 |2023年10月25日11时00分00秒|2023年10月26日13时00分00秒|
| 2023年10月25日| 15 |2023年10月25日11时00分00秒|2023年10月26日18时00分00秒|
| 2023年10月25日| 16 |2023年10月25日11时00分00秒|2023年10月26日20：00：00|
| 2023年10月25日| 17 |2023年10月25日11时00分00秒|2023年10月26日16时00分00秒|
| 2023年10月25日| 18 |2023年10月25日09时00分00秒|2023年10月26日17时00分00秒|
| 2023年10月25日| 33 |2023年10月23日19时00分00秒|2023年10月28日19时00分00秒|
| 2023年10月25日| 34 |2023年10月23日17时00分00秒|2023年10月29日19时00分00秒|
| 2023年10月25日| 35 |2023年10月23日17时00分00秒|2023年10月30日19时00分00秒|
| 2023年10月25日| 36 |2023年10月23日17时00分00秒|2023年10月31日19时00分00秒|
| 2023年10月25日| 37 |2023年10月23日17时00分00秒|2023年11月5日19时00分00秒|
| 2023年10月25日| 38 |2023年10月23日17时00分00秒|2023年11月6日17时00分00秒|
工作日历示例
| n| DT|工作日|启动H|末端H| WD| W| DN|
| --|--|--|--|--|--|--|--|
| 1 |2023年10月23日| 1 | 9 | 18 | 0 | 43 |周一|
| 2 |2023年10月24日| 1 | 9 | 18 | 1 | 43 |周二|
| 3 |2023年10月25日| 1 | 9 | 18 | 2 | 43 |周三|
| 4 |2023年10月26日星期五| 1 | 9 | 18 | 3 | 43 |周四|
| 5 |2023年10月27日| 1 | 9 | 18 | 4 | 43 |周五|
| 6 |2023年10月28日| 0 | 0 | 0 | 5 | 43 |周六|
| 7 |2023年10月29日| 0 | 0 | 0 | 6 | 44 |周日|
| 8 |2023年10月30日| 1 | 9 | 18 | 0 | 44 |周一|
在演示中，我将使用示例数据

Create table Shipment (id int,data_inicial datetime,data_final datetime);
insert into Shipment values
 (11,'2023-10-23 09:00:00','2023-10-23 17:00:00')
,(12,'2023-10-23 08:00:00','2023-10-23 12:00:00')
,(13,'2023-10-23 08:00:00','2023-10-23 19:00:00')
,(14,'2023-10-25 11:00:00','2023-10-26 13:00:00')
,(15,'2023-10-25 11:00:00','2023-10-26 18:00:00')
,(16,'2023-10-25 11:00:00','2023-10-26 20:00:00')
,(17,'2023-10-25 11:00:00','2023-10-26 16:00:00')
,(18,'2023-10-25 09:00:00','2023-10-26 17:00:00')
,(19,'2023-10-26 09:00:00','2023-10-27 17:00:00')
,(21,'2023-10-23 08:00:00','2023-10-24 17:00:00')
,(22,'2023-10-23 17:00:00','2023-10-24 17:00:00')
,(23,'2023-10-23 17:00:00','2023-10-24 19:00:00')
,(24,'2023-10-23 19:00:00','2023-10-24 09:00:00')
,(31,'2023-10-23 19:00:00','2023-10-24 17:00:00')
,(32,'2023-10-23 19:00:00','2023-10-24 17:00:00')
,(33,'2023-10-23 19:00:00','2023-10-28 19:00:00')
,(34,'2023-10-23 17:00:00','2023-10-29 19:00:00')
,(35,'2023-10-23 17:00:00','2023-10-30 19:00:00')
,(36,'2023-10-23 17:00:00','2023-10-31 19:00:00')
,(37,'2023-10-23 17:00:00','2023-11-05 19:00:00')
,(38,'2023-10-23 17:00:00','2023-11-06 17:00:00')
;

型
Demo here

赞(0）分享  回复(0）举报  2023-11-16
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com