时间戳

首页 > 解决方案 > 如果连续记录具有基于用户输入的相同数据，则计算时间总和

sql - 如果连续记录具有基于用户输入的相同数据，则计算时间总和

问题描述

我有每分钟的数据，如下所示

数据样本

在此基于 perameter x（用户输入）值，如果 x = 2，则我必须返回 totalmoves 0 且连续超过 2 分钟的记录，如果 x 值为 3，则我必须返回 totalmoves 0 且连续记录超过3 像这样我想要所有连续记录和总时间（以分钟为单位）（连续记录的总和）

如果 x = 3 则预期结果如下

TotalMoves   Id                               TrackedDateTime           TotalTimeInMin TimeGaps
0   03579A90-9098-4042-BF6B-A8507EF77BAC    2020-07-24 00:04:04.063     9                 4
0   A1BD60A9-9418-4068-A20C-3C83971A7CCB    2020-07-24 00:05:04.063     9                 4
0   1552E876-4DBB-418A-B4C4-BDF51E5CEB25    2020-07-24 00:06:04.063     9                 4
0   EE04BE2F-F8AF-4A5D-93E1-215798F63EC0    2020-07-24 00:07:04.063     9                 4
0   EE04BE2F-F8AF-4A5D-93E1-215798F63EC1    2020-07-24 00:20:04.063     9                 5
0   EE04BE2F-F8AF-4A5D-93E1-215798F63EC2    2020-07-24 00:21:04.063     9                 5
0   EE04BE2F-F8AF-4A5D-93E1-215798F63EC3    2020-07-24 00:22:04.063     9                 5   
0   EE04BE2F-F8AF-4A5D-93E1-215798F63EC4    2020-07-24 00:23:04.063     9                 5
0   EE04BE2F-F8AF-4A5D-93E1-215798F63EC5    2020-07-24 00:24:04.063     9                 5

标签： sqlsql-server

解决方案

with numbered as (
    select *, row_number() over (order by TrackedDateTime) as rn 
    from T
), grouped as (
    select *, row_number() over (order by TrackedDateTime) - rn as grp
    from numbered where TotalMoves = 0
), counted as (
    select *, count(*) over (partition by grp) as cnt
    from grouped
)
select TotalMoves, Id, TrackedDateTime,
    count(*) over () as TotalTimeInMin, cnt as TimeGaps 
from counted where cnt >= 3
order by TrackedDateTime;

https://rextester.com/XVFFC25349

推荐阅读