SQL 计算连续小时数

Question

需要计算连续小时数。

这是数据

╔════╦══════════╦════════════╦═══════════╦═══════╗
║ ID ║ ClientID ║ Date       ║ From(Min) ║ To    ║  
╠════╬══════════╬════════════╬═══════════╬═══════╣
║ 101║ 2563     ║ 2020-06-19 ║ 360       ║ 1080  ║  
║ 102║ 2563     ║ 2020-06-19 ║ 1080      ║ 1140  ║ 
║ 103║ 2563     ║ 2020-06-19 ║ 1140      ║ 1200  ║  
║ 104║ 2561     ║ 2020-06-19 ║ 360       ║ 1080  ║  
║ 105║ 2563     ║ 2020-06-19 ║ 1200      ║ 1440  ║  
║ 106║ 2563     ║ 2020-06-20 ║ 0         ║ 60    ║  
║ 107║ 2561     ║ 2020-05-19 ║ 1080      ║ 1140  ║ 
║ 107║ 2563     ║ 2020-05-20 ║ 1080      ║ 1140  ║ 
╚════╩══════════╩════════════╩═══════════╩═══════╝

这客户端对允许的连续小时数有限制。

这是我正在寻找的结果

╔══════════╦════════════╦═════════╦═════════╦═══════╦═══════════════════╗
║ ClientID ║ Date       ║ From    ║ To      ║ Hours ║ Consecutive Hours ║
╠══════════╬════════════╬═════════╬═════════╬═══════╣═══════════════════╣
║ 2563     ║ 2020-06-19 ║ 6:00am  ║ 6:00pm  ║ 12    ║ 12                ║
║ 2563     ║ 2020-06-19 ║ 6:00pm  ║ 7:00pm  ║ 1     ║ 13                ║
║ 2563     ║ 2020-06-19 ║ 7:00pm  ║ 8:00pm  ║ 1     ║ 14                ║
║ 2563     ║ 2020-06-19 ║ 8:00pm  ║ 12:00am ║ 4     ║ 18                ║
║ 2563     ║ 2020-06-20 ║ 12:00am ║ 1:00am  ║ 1     ║ 19                ║
║ 2563     ║ 2020-06-20 ║ 6:00pm  ║ 7:00pm  ║ 1     ║ 1                 ║
║ 2561     ║ 2020-06-19 ║ 6:00am  ║ 6:00pm  ║ 12    ║ 12                ║
║ 2561     ║ 2020-06-19 ║ 7:00pm  ║ 8:00pm  ║ 1     ║ 13                ║
╚══════════╩════════════╩═════════╩═════════╩═══════╩═══════════════════╝

或用于计算客户端是否超过允许的连续小时数的公式。

Answer 1

这是一种间隙和孤岛问题。 因为你正在处理几分钟，所以对我来说，积累几分钟而不是几个小时更有意义。 您可以除以 60 得到小时数：

select t.*,
sum(tom - fromm) over (partition by clientid, date, grp order by fromm) as consecutive_minutes
from (select t.*,
sum(case when prev_tom = fromm then 0 else 1 end) over (partition by clientid, date order by fromm) as grp
from (select t.*,
dateadd(minute, fromm, date) as fromdt,
dateadd(minute, tom, date) as todt,
lag(tom) over (partition by clientid, date order by fromm) as prev_tom
from t
) t
) t
order by clientid, date, fromm;

编辑：

处理跨天的小时数实际上只是调整上述查询：

select t.*,
sum(tom - fromm) over (partition by clientid, grp order by date, fromm) as consecutive_minutes
from (select t.*,
sum(case when prev_todt = fromdt then 0 else 1 end) over (partition by clientid order by date, fromm) as grp
from (select t.*,
dateadd(minute, fromm, date) as fromdt,
dateadd(minute, tom, date) as todt,
lag(dateadd(minute, tom, date)) over (partition by clientid order by date, fromm) as prev_todt
from t
) t
) t
order by clientid, date, fromm;

这是一个数据库<>小提琴。