在sql或powerbi中有没有一种方法可以定义作业，计算出执行作业所需的时间，以及作业之间的时间？

cgfeq70w 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(336)

在下表中，lodnum下有作业活动和唯一标识符。我在想办法找出一份工作之间的时间差。托盘移动等作业有两个条目。如第1行和第2行所示，这是托盘a的托盘移动。想象第一个入口是捡起托盘，第二个入口是放下托盘。第3行和第4行是将托盘移动到其他位置的单独作业。第7行和第9行也是不同的作业，有时会跳过几行。我想计算出每个用户执行每个任务所需的时间。以及用户每次作业之间的间隔时间。
如何在sql或powerbi中实现这一点？

+---------+-----------------+--------+------+-------+-----+---------+--------+-------+
| Row Num |    activity     | usr_id | year | month | day |  time   | lodnum | wh_id |
+---------+-----------------+--------+------+-------+-----+---------+--------+-------+
|       1 | Pallet Move     | User1  | 2020 |     8 |   5 | 8:24:23 | A      | WH1   |
|       2 | Pallet Move     | User1  | 2020 |     8 |   5 | 8:24:30 | A      | WH1   |
|       3 | Pallet Move     | User2  | 2020 |     8 |   5 | 8:25:10 | A      | WH1   |
|       4 | Pallet Move     | User2  | 2020 |     8 |   5 | 8:25:14 | A      | WH1   |
|       5 | Pallet Move     | User1  | 2020 |     8 |   5 | 8:25:27 | B      | WH1   |
|       6 | Pallet Move     | User1  | 2020 |     8 |   5 | 8:25:30 | B      | WH1   |
|       7 | Inbound Putaway | User3  | 2020 |     8 |   6 | 4:19:58 | C      | WH2   |
|       8 | Inbound Putaway | User3  | 2020 |     8 |   6 | 4:19:59 | D      | WH2   |
|       9 | Inbound Putaway | User3  | 2020 |     8 |   6 | 4:22:05 | C      | WH2   |
|      10 | Inbound Putaway | User3  | 2020 |     8 |   6 | 4:22:10 | D      | WH2   |
|      11 | Trailer Load    | User1  | 2020 |     8 |   6 | 6:22:17 | E      | WH3   |
|      12 | Trailer Load    | User1  | 2020 |     8 |   6 | 6:22:23 | F      | WH3   |
|      13 | Trailer Load    | User1  | 2020 |     8 |   6 | 6:22:26 | E      | WH3   |
|      14 | Trailer Load    | User1  | 2020 |     8 |   6 | 6:22:30 | F      | WH3   |
+---------+-----------------+--------+------+-------+-----+---------+--------+-------+

sql powerbi

来源：https://stackoverflow.com/questions/63285067/is-there-a-way-in-sql-or-power-bi-where-i-can-define-jobs-and-figure-out-the-tim

1条答案

按热度按时间

nhn9ugyo1#

一些假设：
工作总是在同一天开始和结束？对计算很重要。
活动/usr\u id/year/month/lodnum/wh\u id的唯一组合定义了“一个”作业
请让我们知道这是否正确？
可能的解决方案：
首先，我构建了一个cte（公共表表达式），它添加了两列：
[行]时间列定义作业顺序，基于唯一列的组合。结果1和2：开始作业和结束作业。
[键]基于唯一列的组合创建键，用于以后的联接。
其次，我连接两个版本的cte：一个是在[row]=1（start job）上的过滤器，它连接了第二个在[key]和[row]=2上的过滤器。然后以秒为单位计算差值。

WITH CTE AS (
SELECT
       [activity]
      ,[usr_id]
      ,[year]
      ,[month]
      ,[day]
      ,[time]
      ,[lodnum]
      ,[wh_id]
      ,ROW_NUMBER() OVER (PARTITION BY activity, usr_id, year, month, day, lodnum, wh_id ORDER BY [time]) as [Row]
      ,RANK() OVER (ORDER BY activity, usr_id, year, month, day, lodnum, wh_id) as [Key]
  FROM [dbo].[JobActivities] )

  SELECT
     StartTime.usr_id, 
     StartTime.activity, 
     StartTime.day,
     StartTime.time as [Start Time],
     EndTime.time as [End Time],
     DATEDIFF(SECOND, StartTime.time, EndTime.time) as [Difference (s)]
  FROM
  CTE StartTime
  LEFT OUTER JOIN CTE EndTime ON StartTime.[Key] = EndTime.[Key] AND EndTime.Row = 2
  WHERE StartTime.Row = 1
  ORDER BY StartTime.day, StartTime.time

解决方案2-使用lead（无连接，性能更佳）：

WITH CTE AS (
SELECT [Row Num]
      ,[activity]
      ,[usr_id]
      ,[year]
      ,[month]
      ,[day]
      ,[time] [Start Time]
      ,[lodnum]
      ,[wh_id]
      ,ROW_NUMBER() OVER (PARTITION BY activity, usr_id, year, month, day, lodnum, wh_id ORDER BY [time]) as [Row]
      ,LEAD([time]) OVER (PARTITION BY activity, usr_id, year, month, day, lodnum, wh_id ORDER BY [time]) AS [End Time]
  FROM [dbo].[JobActivities] )

  SELECT
     usr_id, 
     activity, 
     day,
     [Start Time],
     [End Time],
     DATEDIFF(SECOND, [Start Time], [End Time]) as [Difference (s)]
  FROM
  CTE
  WHERE Row = 1
 ORDER BY day, [Start Time]

赞(0）回复(0）举报 2021-07-24

我来回答

在sql或powerbi中有没有一种方法可以定义作业，计算出执行作业所需的时间，以及作业之间的时间？

1条答案

相关问题

热门标签

最新问答