时间戳

首页 > 解决方案 > 计算一个值大于 x 的时间（以分钟为单位）

sql - 计算一个值大于 x 的时间（以分钟为单位）

问题描述

我希望能够计算温度列超过某个温度的总时间（以分钟为单位）。例如，我想知道温度已经超过 16 的时间（以分钟计）。

如果读数 at 12:28was16和读数 at 12:30is 17，我们说 from 12:28to12:30的值是 was 17。

此外，如果第一次或唯一一次读数高于 x (17)，这将是两分钟，因为当设备启动时，在获取第一次读数之前需要 x 分钟（在本例中为 2 分钟）。


  SerialNumber, CombinDateTime, Temperature
  1000649496, 2018-12-05 10:56:52,    16.6
  1000649496, 2018-12-05 10:58:52,    17.3
  1000649496, 2018-12-05 11:00:52,    16.8
  1000649496, 2018-12-05 11:02:52,    16.6
  1000649496, 2018-12-05 11:04:52,    16.4
  1000649496, 2018-12-05 11:06:52,    16.3
  1000649496, 2018-12-05 11:08:52,    16.3
  1000649496, 2018-12-05 11:10:52,    16.2
  1000649496, 2018-12-05 11:12:52,    16.2
  1000649496, 2018-12-05 11:14:52,    16.2
  1000649496, 2018-12-05 11:16:52,    16.2
  1000649496, 2018-12-05 11:18:52,    16.2
  1000649496, 2018-12-05 11:20:52,    16.1
  1000649496, 2018-12-05 11:22:52,    16.1
  1000649496, 2018-12-05 11:24:52,    16.1
  1000649496, 2018-12-05 11:26:52,    16
  1000649496, 2018-12-05 11:28:52,    16
  1000649496, 2018-12-05 11:30:52,    16
  1000649496, 2018-12-05 11:32:52,    16
  1000649496, 2018-12-05 11:34:52,    16.1
  1000649496, 2018-12-05 11:36:52,    16.1
  1000649496, 2018-12-05 11:38:52,    16.1
  1000649496, 2018-12-05 11:40:52,    16.1
  1000649496, 2018-12-05 11:42:52,    16.1
  1000649496, 2018-12-05 11:44:52,    16.1
  1000649496, 2018-12-05 11:46:52,    16.1
  1000649496, 2018-12-05 11:48:52,    16
  1000649496, 2018-12-05 11:50:52,    16
  1000649496, 2018-12-05 11:52:52,    16
  1000649496, 2018-12-05 11:54:52,    16
  1000649496, 2018-12-05 11:56:52,    16
  1000649496, 2018-12-05 11:58:52,    16
  1000649496, 2018-12-05 12:00:52,    16.1
  1000649496, 2018-12-05 12:02:52,    16.1
  1000649496, 2018-12-05 12:04:52,    16.1
  1000649496, 2018-12-05 12:06:52,    16.1
  1000649496, 2018-12-05 12:08:52,    16
  1000649496, 2018-12-05 12:10:52,    16
  1000649496, 2018-12-05 12:12:52,    16
  1000649496, 2018-12-05 12:14:52,    16
  1000649496, 2018-12-05 12:16:52,    16
  1000649496, 2018-12-05 12:18:52,    16
  1000649496, 2018-12-05 12:20:52,    16
  1000649496, 2018-12-05 12:22:52,    16
  1000649496, 2018-12-05 12:24:52,    16
  1000649496, 2018-12-05 12:26:52,    16
  1000649496, 2018-12-05 12:28:52,    16
  1000649496, 2018-12-05 12:30:52,    16
  1000649496, 2018-12-08 08:08:52,    15.1
  1000649496, 2018-12-05 12:32:52,    16
  1000649496, 2018-12-05 12:34:52,    16
  1000649496, 2018-12-05 12:36:52,    16
  1000649496, 2018-12-05 12:38:52,    16

到目前为止，我的查询非常基本：

    SELECT SerialNumber, CombineDateTime, Temperature 
    FROM RawData
    WHERE Temperature > 16

我想到的主要是我选择数据集并order by date遍历每一行，直到找到一个结束的值16。然后我获取日期，然后在记录中移动，直到找到一个值<= 16，然后获取该日期和时间datediff()以及minutes.

我知道您不应该遍历SQL记录，所以我正在考虑使用 a CTE，但我不太确定如何执行此操作。

例如，我的预期结果是：

    SerialNumber, MinutesOver 
    1000649496, 1186

TIA

标签： sqlsql-serverdatetimesql-server-2017gaps-and-islands

解决方案

这看起来像是一个间隙和孤岛问题（需要将连续 > 16 个温度和 <= 16 个温度组合在一起），一种解决方案如下：

DECLARE @threshold DECIMAL(18, 2) = 16;
WITH cte1 AS (
    SELECT *, CASE 
           -- first row itself is greater than threshold
           WHEN Temperature  >  @threshold  AND  LAG(Temperature)  OVER (PARTITION BY SerialNumber ORDER BY CombinDateTime) IS NULL      THEN 1
           -- next row is greater than threshold
           WHEN Temperature <=  @threshold  AND LEAD(Temperature)  OVER (PARTITION BY SerialNumber ORDER BY CombinDateTime) > @threshold THEN 1
           -- prev row is greater than threshold
           WHEN Temperature <=  @threshold  AND  LAG(Temperature)  OVER (PARTITION BY SerialNumber ORDER BY CombinDateTime) > @threshold THEN 1
    END AS chg
    FROM @t
), cte2 AS (
    SELECT *, SUM(chg) OVER (PARTITION BY SerialNumber ORDER BY CombinDateTime) AS grp
    FROM cte1
)
SELECT SerialNumber
     , MIN(CombinDateTime) AS StartDateTime
     , MAX(CombinDateTime) AS EndDateTime
     , DATEDIFF(SECOND, MIN(CombinDateTime), MAX(CombinDateTime)) / 60.0 AS Total
FROM cte2
GROUP BY SerialNumber, grp
HAVING MAX(Temperature) > @threshold

结果：

SerialNumber  StartDateTime        EndDateTime          Total
1000649496    2018-12-05 10:56:52  2018-12-05 11:24:52  28.000000
1000649496    2018-12-05 11:32:52  2018-12-05 11:46:52  14.000000
1000649496    2018-12-05 11:58:52  2018-12-05 12:06:52  8.000000

推荐阅读