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

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

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

  1. SELECT 
  2.     SUM(
  3.         TIMESTAMPDIFF(
  4.             HOUR, 
  5.             GREATEST(data_inicial, TIMESTAMP(DATE(data_inicial), '09:00:00')), 
  6.             LEAST(data_final, TIMESTAMP(DATE(data_final), '18:00:00'))
  7.         )
  8.     ) AS hours
  9. FROM table
  10. WHERE WEEKDAY(data_inicial) BETWEEN 0 AND 4
  11.   AND HOUR(data_inicial) < 18
  12.   AND WEEKDAY(data_final) BETWEEN 0 AND 4
  13.   AND HOUR(data_final) >= 9
  14. UNION
  15. SELECT 
  16.     COUNT(*) * 9 AS hours
  17. FROM table
  18. WHERE WEEKDAY(data_inicial) BETWEEN 0 AND 4
  19.   AND HOUR(data_inicial) >= 9
  20. UNION
  21. SELECT 
  22.     COUNT(*) * 9 AS horas_uteis
  23. FROM table
  24. WHERE WEEKDAY(data_final) BETWEEN 0 AND 4
  25.   AND HOUR(data_final) < 18;

字符串

pyspark

1条答案

按热度按时间
qjp7pelc

qjp7pelc1#

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

  1. with recursive WorkCalendar as( -- work calendar, sufficient for test data
  2.   select 1 n
  3.     ,date('2023-10-23') dt
  4.     ,case when weekday(date('2023-10-23')) between 0 and 4 then 1 else 0 end workDay
  5.     ,case when weekday(date('2023-10-23')) between 0 and 4 then 9 else 0 end startH
  6.     ,case when weekday(date('2023-10-23')) between 0 and 4 then 18 else 0 end endH
  7.     ,weekday(date('2023-10-23')) wd
  8.     ,week(date('2023-10-23')) w
  9.     ,dayname(date('2023-10-23')) dn
  10. union all
  11.   select  n+1
  12.     ,date_add(dt, interval 1 day)  
  13.     ,case when weekday(date_add(dt, interval 1 day)) between 0 and 4 then 1 else 0 end workDay
  14.     ,case when weekday(date_add(dt, interval 1 day)) between 0 and 4 then 9 else 0 end startH
  15.     ,case when weekday(date_add(dt, interval 1 day)) between 0 and 4 then 18 else 0 end endH
  16.     ,weekday(date_add(dt, interval 1 day)) wd
  17.     ,week(date_add(dt, interval 1 day)) w
  18.     ,dayname(date_add(dt, interval 1 day)) dn
  19.   from WorkCalendar where date_add(dt, interval 1 day)<date('2023-11-23')
  20. )
  21. ,ShipmentDt as( -- subquery for to shorten formulas
  22. select * 
  23.   ,hour(data_inicial) h1 ,cast(data_inicial as date) dt1
  24.   ,hour(data_final) h2 ,cast(data_final as date) dt2
  25. from Shipment
  26. )
  27. ,ShipmentHours as(
  28. select id,data_inicial,data_final,c.dt,c.workDay,c.startH,c.endH 
  29.      -- hours in data_inicial
  30.       case when dt2>dt1 then  
  31.         endH-case when h1<startH then startH when h1>endH then endH else h1  end
  32.       else -- intraday
  33.         case when h2<startH then startH when h2>endH then endH else h2  end
  34.        -case when h1<startH then startH when h1>endH then endH else h1  end
  35.       end 
  36.     else 0
  37.     end hours_first
  38.     -- hours in data_final
  39.    ,case when dt2>dt1 and c.dt=dt2 then
  40.         case when h2<startH then 0 when h2>endH then (endH-startH) else h2-startH  end
  41.     else 0
  42.     end hours_last
  43.     -- hours in data between (not inculded) data_inicial and data_final
  44.    ,case when c.dt>dt1 and c.dt<dt2 then
  45.         endH-startH
  46.     else 0
  47.     end hours_inter
  48. from ShipmentDt sd
  49. left join WorkCalendar c on c.dt>=sd.dt1 and c.dt<=sd.dt2
  50. )
  51. ,ShipmentWorkHours as( -- total work hours for shipment
  52. select id,min(data_inicial) data_inicial,min(data_final)data_final
  53.   ,sum((hours_first+hours_last+hours_inter)*workDay) as totH
  54. from ShipmentHours
  55. group by id
  56. )
  57. ,AvgDelivery as( -- average work hours, delivered in date - target task
  58. select cast(data_final as date) data_final, avg(totH) avgH,count(*) cnt
  59. from ShipmentWorkHours
  60. group by cast(data_final as date)
  61. )
  62. ,ActiveShipments as( -- additional - count of active shipments by date
  63. select dt,count(*) cnt
  64. from ShipmentHours
  65. group by dt
  66. )
  67. ,ActiveDeliveryByDate as( -- additional - active shipments list for date
  68. select dt,id,data_inicial,data_final
  69. from ShipmentHours
  70. where dt=cast('2023-10-25' as date)
  71. )
  72.  select * from AvgDelivery
  73. -- select * from ActiveShipments
  74. -- select * from ActiveDeliveryByDate
  75. -- 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 |周一|
在演示中，我将使用示例数据

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

型
Demo here

展开查看全部
赞(0）分享  回复(0）举报  2023-11-16
我来回答

相关问题

    查看更多

    热门标签

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

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com