如何在SQL Server中以5分钟间隔对datetime进行分区？

debugcn 发表于 Dev

考勤系统

如何以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

瓦希德·法拉赫曼迪安（Vahid Farahmandian）

您可以尝试以下方法：

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

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。