如何在SQL Server中以5分钟的间隔对日期时间进行分区

Question

如何以5分钟为间隔对日期时间进行分组。

这是我的桌子

id  Belongs_to            dt
----------------------------------------
102 2020-08-07  2020-08-07 09:48:01.000
102 2020-08-07  2020-08-07 09:48:03.000
102 2020-08-07  2020-08-07 16:24:01.000
102 2020-08-07  2020-08-07 17:22:03.000
102 2020-08-07  2020-08-07 17:23:01.000
102 2020-08-07  2020-08-07 17:23:01.000
102 2020-08-07  2020-08-07 21:28:03.000
102 2020-08-07  2020-08-07 21:28:04.000

我尝试了下面的查询来分组

select 
t.emp_Reader_id, Belongs_to, 
format(dt, 'yyyy-MM-dd HH:mm') as dt,
row_number() over (partition by t.emp_reader_id, Belongs_to order by FORMAT(dt, 'yyyy-MM-dd HH:mm')) as seqnum
from
trnevents t
where 
T.emp_reader_id = 102
group by 
t.emp_reader_id, format(dt, 'yyyy-MM-dd HH:mm'), Belongs_to

现在时间秒被忽略了，如果在同一时间，我也想按5分钟的间隔分组

预期输出：

id  Belongs_to           dt        seqnum
102 2020-08-07  2020-08-07 09:48    1
102 2020-08-07  2020-08-07 16:24    2
102 2020-08-07  2020-08-07 17:22    3
102 2020-08-07  2020-08-07 21:28    4

Answer 1

你可以试试这个：

SELECT *,
ROW_NUMBER() OVER (ORDER BY dt) AS seqno
FROM
(
SELECT DISTINCT
K.emp_Reader_id,
K.Belongs_to,
K.newDT AS dt
FROM
(
SELECT emp_Reader_id, Belongs_to,
CASE
WHEN DATEDIFF(MINUTE, K.checker, dt) > 5 THEN
dt
ELSE
K.checker
END AS newDT
FROM
(
SELECT *,
FIRST_VALUE(dt) OVER (PARTITION BY CAST(dt AS DATE), DATEPART(HOUR, dt)
ORDER BY dt
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS checker
FROM #r
) AS K
) AS K
) AS K;

阅读有关FIRST_VALUE 的更多信息

Answer 2

像这样的东西怎么样

with groupCTE As (
select T.emp_Reader_id, 
T.belongs_to, 
T.dt, 
rowN = ROW_NUMBER() OVER(PARTITION BY dateadd(minute, datediff(minute,0,T.dt) / 5 * 5, 0) ORDER BY T.dt)
from
trnevents T
where 
T.emp_reader_id = 102
)
select emp_Reader_id as id, 
Belongs_to, 
dt, 
ROW_NUMBER() OVER(ORDER BY dt) as seqnum
from groupCTE   
where rowN = 1
order by dt

Answer 3

其他解决方案：

with DataWithUniQID as (
select f0.emp_Reader_id, f0.Belongs_to, f0.dt, format(f0.dt, 'yyyy-MM-dd HH:mm') as fmtdt,
row_number() over (partition by f0.emp_reader_id order by f0.dt) as ID
from trnevents f0
)
select f1.*,
row_number() over (partition by f1.emp_reader_id order by f1.ID) as seqnum 
from DataWithUniQID f1
outer apply
(
select top 1 f1.id, f2.dt from DataWithUniQID f2
where f1.emp_Reader_id=f2.emp_Reader_id and f2.ID<f1.ID
order by f2.ID desc
) f3
where f3.ID is null or DATEDIFF(MINUTE, f1.dt, f3.dt) >= 5