sql计算连续小时数_大数据知识库

关闭。这个问题需要细节或清晰。它目前不接受答案。
**想改进这个问题吗？**通过编辑这个帖子来添加细节并澄清问题。

10个月前关门了。
改进这个问题
需要计算连续小时数。
这是数据

╔════╦══════════╦════════════╦═══════════╦═══════╗
║ 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                ║
╚══════════╩════════════╩═════════╩═════════╩═══════╩═══════════════════╝

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

这是一种缺口和孤岛问题。因为你处理的是分钟，所以对我来说，积累分钟比积累小时更有意义。你可以除以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;

这是一把小提琴。

sql计算连续小时数

1条答案

相关问题

热门标签

最新问答