时间戳

首页 > 解决方案 > 如何计算列值变化的时间差？

sql-server - 如何计算列值变化的时间差？

问题描述

我正在尝试编写一个查询来获取关于 SQL Server 2012 中列中值更改的累积时间差。

我正在尝试收集一些关于任务等待另一个用户的时间的分析，在这个任务中有 2 个参与者角色 = 0 是实施者，角色 = 1 是任务的审阅者。在任务的持续时间内，实施者和审查者可以多次对任务执行活动。目的是获取审核者和实施者等待的总时间。要重新创建数据快照，请参见下面的示例

CREATE TABLE ActivityTable
([Id] [int] IDENTITY(1,1) NOT NULL,
[RoleId] [int] NULL,
[ActivityDate] [DATETIME] NULL)

INSERT INTO [ActivityTable] VALUES (1, '2018-10-19 13:00:19.840')
INSERT INTO [ActivityTable] VALUES (1, '2018-10-19 13:00:18.073')
INSERT INTO [ActivityTable] VALUES (1, '2018-10-19 12:59:48.417')
INSERT INTO [ActivityTable] VALUES (0, '2018-10-15 13:48:00.557')
INSERT INTO [ActivityTable] VALUES (0, '2018-10-15 12:56:25.567')
INSERT INTO [ActivityTable] VALUES (0, '2018-10-15 12:56:09.967')
INSERT INTO [ActivityTable] VALUES (0, '2018-10-15 12:55:26.500')
INSERT INTO [ActivityTable] VALUES (0, '2018-10-15 12:53:17.997')
INSERT INTO [ActivityTable] VALUES (1, '2018-10-15 12:36:17.967')
INSERT INTO [ActivityTable] VALUES (1, '2018-10-15 12:35:38.497')
INSERT INTO [ActivityTable] VALUES (1, '2018-10-15 12:33:05.860')
INSERT INTO [ActivityTable] VALUES (1, '2018-10-15 12:32:07.793')
INSERT INTO [ActivityTable] VALUES (1, '2018-10-15 12:32:00.010')
INSERT INTO [ActivityTable] VALUES (0, '2018-10-15 12:18:18.417')
INSERT INTO [ActivityTable] VALUES (0, '2018-10-15 12:17:16.370')
INSERT INTO [ActivityTable] VALUES (0, '2018-10-15 12:11:48.590')
INSERT INTO [ActivityTable] VALUES (0, '2018-10-15 11:58:38.557')
INSERT INTO [ActivityTable] VALUES (0, '2018-10-15 11:56:23.820')`

所以每次转换的总时间看起来像

RoleInfo  Start Time               End Time                 Duration Minutes
0         2018-10-15 11:56:23.820  2018-10-15 12:32:00.010  37
1         2018-10-15 12:32:00.010  2018-10-15 12:53:17.997  22
0         2018-10-15 12:53:17.997  2018-10-19 12:59:48.417  5767
1         2018-10-19 12:59:48.417  2018-10-19 13:00:19.840  1

预期的最终结果是待处理时间的聚合

RoleInfo  Duration in Minutes
0         5804
1         23

标签： sql-server

解决方案

请参阅查询中的注释。

单独执行每个内部查询以查看结果以便更好地理解

select  RoleId, Duration = sum(datediff(minute, StartTime, EndTime))
from
(
    -- perform a group by RoleId + grp
    -- min() and max() on ActivityDate will gives you based on RoleId
    -- however you wanted the StartTime of next RoleId. For this LEAD() OVER() is used
    select  RoleId, 
            StartTime = min(ActivityDate), 
            EndTime   = coalesce(lead(min(ActivityDate)) over (order by min(ActivityDate)), 
                                 max(ActivityDate))
    from
    (
        -- identify the group. each group is continuous same RoleId value
        select  *, grp = Id - dense_rank() over (partition by RoleId 
                                                     order by ActivityDate desc)
        from    ActivityTable
    ) a
    group by RoleId, grp
) b
group by RoleId

顺便说一句，我认为您发布的预期结果是错误的

/* RESULT
    RoleId  Duration
    0       5802
    1       22
*/

推荐阅读