sql - 按时间间隔(例如天)和总持续时间对时间序列进行分组

Question

我有一个包含时间序列的表格，其中包含以下信息。每条记录代表“改变模式”的事件。

 Timestamp        | Mode 
------------------+------
 2018-01-01 12:00 |  1   
 2018-01-01 18:00 |  2   
 2018-01-02 01:00 |  1   
 2018-01-02 02:00 |  2   
 2018-01-04 04:00 |  1   

通过使用 LEAD 函数，我可以创建具有以下结果的查询。现在每条记录都包含信息，“模式处于事件状态”的时间和时长。

请检查第 2 条和第 4 条记录。它们“属于”多天。

 StartDT          | EndDT            | Mode | Duration
------------------+------------------+------+----------
 2018-01-01 12:00 | 2018-01-01 18:00 |  1   |   6:00
 2018-01-01 18:00 | 2018-01-02 01:00 |  2   |   7:00
 2018-01-02 01:00 | 2018-01-02 02:00 |  1   |   1:00
 2018-01-02 02:00 | 2018-01-04 04:00 |  2   |  50:00
 2018-01-04 04:00 | (NULL)           |  1   | (NULL)

现在我想要一个按日期和模式 对数据进行分组并汇总持续时间的查询。

需要这个结果表:

 Date       | Mode | Total
------------+------+-------
 2018-01-01 |  1   |  6:00
 2018-01-01 |  2   |  6:00
 2018-01-02 |  1   |  1:00
 2018-01-02 |  2   | 23:00
 2018-01-03 |  2   | 24:00
 2018-01-04 |  2   | 04:00

我不知道如何处理“属于”多天的记录。有什么想法吗？

Answer 1

create table ChangeMode ( ModeStart datetime2(7), Mode int )

insert into ChangeMode ( ModeStart, Mode ) values
( '2018-11-15T21:00:00.0000000', 1 ),
( '2018-11-16T17:18:19.1231234', 2 ),
( '2018-11-16T18:00:00.5555555', 1 ),
( '2018-11-16T18:00:01.1234567', 2 ),
( '2018-11-16T19:02:22.8888888', 1 ),
( '2018-11-16T20:00:00.9876543', 2 ),
( '2018-11-17T09:00:00.0000000', 1 ),
( '2018-11-17T23:23:23.0230450', 2 ),
( '2018-11-19T17:00:00.0172839', 1 ),
( '2018-11-20T03:07:00.7033077', 2 )

;
with 
-- Determine the earliest and latest dates.
-- Cast to date to remove the time portion.
-- Cast results back to datetime because we're going to add hours later.
MinMaxDates 
as 
(select cast(min(cast(ModeStart as date))as datetime) as MinDate, 
        cast(max(cast(ModeStart as date))as datetime) as MaxDate from ChangeMode),

-- How many days have passed during that period
Dur
as
(select datediff(day,MinDate,MaxDate) as Duration from MinMaxDates),

-- Create a list of numbers.
-- These will be added to MinDate to get a list of dates.
NumList
as
( select 0 as Num
    union all
    select Num+1 from NumList,Dur where Num<Duration ),

-- Create a list of dates by adding those numbers to MinDate
DayList 
as
( select dateadd(day,Num,MinDate)as ModeDate from NumList, MinMaxDates  ),

-- Create a list of day periods
PeriodList
as
( select ModeDate as StartTime,
            dateadd(day,1,ModeDate) as EndTime
            from DayList                        ),

-- Use LEAD to get periods for each record
-- Final record would return NULL for ModeEnd
-- We replace that with end of last day
ModePeriodList
as
( select ModeStart, 
            coalesce( lead(ModeStart)over(order by ModeStart),
                    dateadd(day,1,MaxDate) ) as ModeEnd, 
            Mode from ChangeMode, MinMaxDates               ),

ModeDayList
as
( select * from ModePeriodList, PeriodList 
where ModeStart<=EndTime and ModeEnd>=StartTime
),

-- Keep the later   of the mode start time, and the day start time
-- Keep the earlier of the mode   end time, and the day   end time
ModeDayPeriod
as
( select case when ModeStart>=StartTime then ModeStart  else StartTime end as StartTime,
            case when ModeEnd<=EndTime  then ModeEnd else EndTime   end as EndTime,
            Mode from ModeDayList ),

SumDurations
as
( select cast(StartTime as date) as ModeDate, 
        Mode, 
        DateDiff_Big(nanosecond,StartTime,EndTime)
        /3600000000000 
            as DurationHours from ModeDayPeriod   )                        

-- List the results in order
-- Use MaxRecursion option in case there are more than 100 days 
select ModeDate as [Date], Mode, sum(DurationHours) as [Total Duration Hours]
     from SumDurations 
group by ModeDate, Mode
order by ModeDate, Mode
option (maxrecursion 0)

结果是:

Date       Mode        Total Duration Hours
---------- ----------- ---------------------------------------
2018-11-15 1           3.00000000000000
2018-11-16 1           18.26605271947221
2018-11-16 2           5.73394728052777
2018-11-17 1           14.38972862361111
2018-11-17 2           9.61027137638888
2018-11-18 2           24.00000000000000
2018-11-19 1           6.99999519891666
2018-11-19 2           17.00000480108333
2018-11-20 1           3.11686202991666
2018-11-20 2           20.88313797008333