按近时间对数据行进行分组 [英] Group data rows by near time

问题描述

这是我面临的问题: 我有一个包含行的大表，我想将它们按接近的时间进行分组，更具体地说是时差小于2分钟，示例如下 使用以下输入数据:

Here is the problem I am facing: I got a large table containing rows, I want to group them by near time, more specifically the time difference less than 2 minutes, example as following With following input data:

• A 16:01:01
• B 16:01:20
• C 16:14:02
• D 16:15:01
• E 16:20:02

预期结果是

16:01:01 2
16:14:02 2
16:20:02 1

推荐答案

SELECT CONVERT(VARCHAR(8),
               DATEADD(minute, (DATEDIFF(n, 0, time) / 2) * 2, 0),
               108),
       COUNT(*)
FROM times
GROUP BY DATEDIFF(n, 0, time) / 2

说明: CONVERT以hh:mm:ss格式(= 108)显示DateTime. DATEDIFF转换为分钟，然后除以2，四舍五入为整数，因此每2分钟的GROUP解析为相同的整数. DATEADD用于将此分钟数转换回DateTime，乘以2即可返回正确的(四舍五入)时间.

Explanation: CONVERT displays a DateTime in hh:mm:ss format (= 108). DATEDIFF converts to minutes and then divides by two, rounding to an integer so each GROUP of 2 minutes resolves to the same integer. DATEADD is used to convert this number of minutes back to a DateTime, having multiplied by 2 to get back to the correct (rounded) time.

请参见 SQL小提琴演示

这篇关于按近时间对数据行进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！