时间戳

首页 > 解决方案 > 使用 DISTINCT 记录从 DATETIME 计算和创建持续时间

sql - 使用 DISTINCT 记录从 DATETIME 计算和创建持续时间

问题描述

我有一个非常大的时间数据集，显示了 2 年内整个美国的停电情况。我想使用来自 4 列的不同记录来计算中断的持续时间。CustomersOut 的“0”值被视为重置。

例子：

Utility1, State1, County1, City1, 1500, 0, 2017-12-28 12:25:00
Utility1, State1, County1, City1, 1500, 50, 2018-01-01 03:25:00
Utility1, State1, County1, City1, 1500, 65, 2018-01-01 03:36:00
Utility1, State1, County1, City1, 1500, 40, 2018-01-01 03:55:00
Utility1, State1, County1, City1, 1500, 0, 2018-01-01 04:34:00

这意味着在 City1 中，有 50 名顾客外出 11 分钟、65 名顾客外出 19 分钟、40 名顾客外出 39 分钟，总共有 55.75 个顾客外出时间。

期望的结果：

Utility1, State1, County1, City1, 1500, 0, 2017-12-28 12:25:00, NULL
Utility1, State1, County1, City1, 1500, 50, 2018-01-01 03:25:00, 00:11.00
Utility1, State1, County1, City1, 1500, 65, 2018-01-01 03:36:00, 00:19.00
Utility1, State1, County1, City1, 1500, 40, 2018-01-01 03:55:00, 00:39.00
Utility1, State1, County1, City1, 1500, 0, 2018-01-01 04:34:00, NULL

表格图像

标签： sqlsql-servertsqldategroup-by

解决方案

您可以从计算当前记录与下一条记录之间差异的子查询开始。然后，外部查询过滤与断电和聚合对应的记录：

select 
     UtilityName, 
     StateName, 
     CountyName, 
     CityName,
     sum(CustomersOut) CustomersOut,
     sum(MinutesOut) MinutesOut,
     sum(CustomersOut * MinutesOut) / 60.0 CustomersHoursOut
from (
    select 
        UtilityName, 
        StateName, 
        CountyName,
        CityName,
        CustomersOut,
        datediff(
            minute,
            RecordedDateTime,
            lead(RecordedDateTime) over(
                partition by UtilityName, StateName, CountyName, CityName 
                order by RecordedDateTime
            )
        ) MinutesOut
    from mytable 
) t
where CustomersOut > 0
group by
     UtilityName, 
     StateName, 
     CountyName, 
     CityName

DB Fiddle 上的演示：

实用程序名称 | 州名 | 县名 | 城市名 | 客户输出 | 分钟输出 | 顾客营业时间
:------------ | :-------- | :--------- | :------- | ------------: | ---------: | :-----------------
实用程序1 | 状态1 | 县1 | 城市1 | 155 | 69 | 55.750000

编辑

您想要的信息可以从内部查询中生成：

select 
    t.*,
    case when CustomersOut > 0
    then datediff(
        minute,
        RecordedDateTime,
        lead(RecordedDateTime) over(
            partition by UtilityName, StateName, CountyName, CityName 
            order by RecordedDateTime
        )
    ) 
    end MinutesOut
from mytable t

演示：

实用程序名称 | 州名 | 县名 | 城市名 | 客户跟踪 | 客户输出 | 记录日期时间 | 分钟结束
:------------ | :-------- | :--------- | :------- | ---------------: | ------------: | :----------------- | ---------:
实用程序1 | 状态1 | 县1 | 城市1 | 1500 | 0 | 2017-12-28 12:25:00 |       无效的
实用程序1 | 状态1 | 县1 | 城市1 | 1500 | 50 | 2018-01-01 03:25:00 | 11
实用程序1 | 状态1 | 县1 | 城市1 | 1500 | 65 | 2018-01-01 03:36:00 | 19
实用程序1 | 状态1 | 县1 | 城市1 | 1500 | 40 | 2018-01-01 03:55:00 | 39
实用程序1 | 状态1 | 县1 | 城市1 | 1500 | 0 | 2018-01-01 04:34:00 |       无效的

推荐阅读