时间戳

首页 > 解决方案 > 时间跨度计算

sql - 时间跨度计算

问题描述

我有一张这样的桌子：

#Row  ID  Status1 Status2 TimeStatusChange
------------------------------------------
  1   24    0       0      2020-09-02 09:18:02.233
  2   48    0       0      2020-09-02 09:18:58.540 
  3   24    1       0      2020-09-02 09:19:47.233     
  4   24    0       0      2020-09-02 09:19:47.587     
  5   48    0       1      2020-09-02 09:22:53.923      
  6   36    1       0      2020-09-02 09:24:14.343     
  7   48    0       0      2020-09-02 09:24:49.670     
  8   24    1       0      2020-09-02 09:38:37.820

并且想知道，如何计算按 ID 分组的所有状态（1 或 2）从 0 到 1（或 1 到 0）变化的时间跨度总和。

在这个 ID 24 的示例中，Status1 从 0 到 1，这将是 #Row 3 和 #row 1 的 TimeStatusChange 的差异 + #Row 8 和 #row 4 的 TimeStatusChange 的差异，大约 21 分钟。

完美的输出如下所示：

 ID  Change            TimeSpanInMinutes
----------------------------------------
 24  Status1_from_0_1    20
 36  .....

尽管我对 PL/SQL 有一定的经验，但我还是一无所获。

标签： sqldatetimesql-server-2014

解决方案

样本数据

我添加了几行以获得更多结果数据，并验证给定 ID 具有相同状态的连续行的场景。

declare @data table
(
    ID int, 
    Status1 int,
    Stamp datetime
)

insert into @data (ID, Status1, Stamp) values
(48, 1, '2020-09-02 09:00:00.000'), --added row
(24, 0, '2020-09-02 09:18:02.233'),
(48, 0, '2020-09-02 09:18:58.540'),
(24, 1, '2020-09-02 09:19:47.233'),
(24, 0, '2020-09-02 09:19:47.587'),
(48, 0, '2020-09-02 09:22:53.923'),
(36, 1, '2020-09-02 09:24:14.343'),
(48, 0, '2020-09-02 09:24:49.670'),
(24, 1, '2020-09-02 09:38:37.820'),
(48, 1, '2020-09-02 10:00:00.000'); --added row

解决方案

在lag()函数的帮助下，使用公用表表达式 (CTE, cte_data) 来获取相同 ID 的先前记录（无论其状态值如何）。在 CTE 外部的子句中删除与前一行具有相同值的后续行。where

with cte_data as
(
    select  d.ID,
            d.Status1,
            d.Stamp,
            lag(d.Status1) over(partition by d.ID order by d.Stamp) as Status1Prev,
            lag(d.Stamp) over(partition by d.ID order by d.Stamp) as StampPrev
    from @data d
)
select  d.ID,
        d.Status1Prev as Status1From,
        d.Status1 as Status1To,
        sum(datediff(MI, d.StampPrev, d.Stamp)) as StampDiffSumM, --minutes
        convert(time(3), dateadd(MS, sum(datediff(MS, d.StampPrev, d.Stamp)), '1900-01-01 00:00:00.000')) as StampDiffSumF --formatted
from cte_data d
where d.Status1 <> d.Status1Prev
  and d.Status1Prev is not null
group by d.ID, d.Status1Prev, d.Status1
order by d.ID;

结果

ID          Status1From Status1To   StampDiffSumM StampDiffSumF
----------- ----------- ----------- ------------- ----------------
24          0           1           20            00:20:35.233
24          1           0           0             00:00:00.353
48          0           1           36            00:35:10.330
48          1           0           18            00:18:58.540

推荐阅读