sql-server - 如何仅对差异为零的记录求和

Question

我在 sql server 查询中遇到问题，这里是

Table 1
Id  |User   |Start  |End
====+=======+=======+======
1   | 5     | 1:00  | 2:00
2   | 5     | 2:00  | 3:00
3   | 5     | 3:00  | 4:00
4   | 5     | 7:20  | 8:35
5   | 7     | 3:00  | 4:30

Result 
ID  |User   |Start  |End    |TotalAll   |Running
====+=======+=======+=======+===========+========
1   |5      |1:00   | 2:00  | 3.0       | 1.0
2   |5      |2:00   | 3:00  | 3.0       | 2.0
3   |5      |3:00   | 4:00  | 3.0       | 3.0
4   |5      |7:20   | 8:35  | 1.25      | 1.25
5   |7      |3:00   | 4:30  | 1.5       | 1.5

由于会话 1 & 2 & 3 相互连接，没有任何中断，而且它们也来自同一个用户，我想要 2 个额外的列，1，连接会话的总小时数为 3.0 小时，2 ，运行总和，第一个记录为 1 小时，第二个记录为 2 小时，第三个记录为 3 小时。

标签： sql-server

Answer 1

主要问题是确定哪些是"connected" Id. 为此，递归 ctercte查询用于识别基于Start和End时间

至于TotalAlland Running，这只是sum()与窗口函数一起使用

; with 
cte as
(
    select  *, 
            rn  = row_number() over (partition by [User] order by [Start])
    from    your_table t
),
rcte as
(
    select  *, grp = 1
    from    cte 
    where   rn  = 1

    union all

    select  c.*, 
            grp = case when r.[End] = c.[Start] then r.grp else r.grp + 1 end
    from    rcte r
            inner join cte c    on  r.[User]    = c.[User]
                               and  r.rn        = c.rn - 1
)
select  r.[Id], r.[User], r.[Start], r.[End],
        TotalAll = sum(datediff(minute, [Start], [End])) 
                   over (partition by [User], [grp]) / 60.0,
        Running  = sum(datediff(minute, [Start], [End])) 
                   over (partition by [User], [grp] 
                             order by Id) / 60.0
from    rcte r
order by [Id]