时间戳

首页 > 解决方案 > 每次卡车在 SQL 中没有速度时获取总时间？

sql - 每次卡车在 SQL 中没有速度时获取总时间？

问题描述

我在 SQL Server 2014 中有下表：

Vehicle_Id |  Speed  |     Event    |     Datetime
-----------+---------+--------------+----------------------
1          |    0    |  Door-Open   |   2019-05-04 15:00:00
1          |    0    |  Door-Closed |   2019-05-04 15:15:00
1          |    50   |  Driving     |   2019-05-04 15:35:00
1          |    0    |  Parked      |   2019-05-04 15:50:00
1          |    0    |  Door-Open   |   2019-05-04 15:51:00
1          |    0    |  Door-Closed |   2019-05-04 15:52:00
1          |    50   |  Driving     |   2019-05-04 15:57:00

我需要识别datetime卡车已经行驶了speed = 0一个多小时的区域。因此，每次以速度 0 出现一行时，它应该创建一个唯一的block_id，直到出现一行speed。所以总时间应该是卡车第一次速度为 0 直到它找到下一行speed > 0。

预期输出：

Vehicle_Id |  Speed  |     Event    |     Datetime           |     Block   | Total_State_Time_Block(Minutes)
-----------+---------+--------------+------------------------+-------------+---------------------------------
1          |    0    |  Door-Open   |   2019-05-04 15:00:00  |       1     |      35 Minutes
1          |    0    |  Door-Closed |   2019-05-04 15:15:00  |       1     |      35 Minutes
1          |    50   |  Driving     |   2019-05-04 15:35:00  |       2     |      15 Minutes
1          |    0    |  Parked      |   2019-05-04 15:50:00  |       3     |      7 Minutes
1          |    0    |  Door-Open   |   2019-05-04 15:51:00  |       3     |      7 Minutes
1          |    0    |  Door-Closed |   2019-05-04 15:52:00  |       3     |      7 Minutes
1          |    50   |  Driving     |   2019-05-04 15:57:00  |       4     |        ...

因此，按照 的顺序datetime，我们的想法是创建相邻行的组，speed = 0以便我可以识别卡车超过一个小时没有移动的时间。

我尝试了窗口函数来按车辆和日期获得结果。但我无法实现这最后一步。

标签： sqlsql-server

解决方案

你可以试试lag()

select
  vehicle_id,
    speed,
    event,
    datetime,
    sum(case when speed = rnk then 0 else 1 end) over (order by datetime) as block
from
(
    select
      *,
      lag(speed) over (order by datetime) as rnk
  from myTable
) val

输出：

| vehicle_id | speed | event       | datetime                 | block |
| ---------- | ----- | ----------- | ------------------------ | ----- |
| 1          | 0     | Door-Open   | 2019-05-04 15:00:00      | 1     |
| 1          | 0     | Door-Closed | 2019-05-04 15:15:00      | 1     |
| 1          | 50    | Driving     | 2019-05-04 15:35:00      | 2     |
| 1          | 0     | Parked      | 2019-05-04 15:50:00      | 3     |
| 1          | 0     | Door-Open   | 2019-05-04 15:51:00      | 3     |
| 1          | 0     | Door-Closed | 2019-05-04 15:52:00      | 3     |
| 1          | 50    | Driving     | 2019-05-04 15:57:00      | 4     |

推荐阅读