在SQL Server中基于10秒时间间隔对记录进行分组 [英] Group By records based on 10 second time interval in SQL server

问题描述

要求是根据10秒的时间间隔对表的记录进行分组.给定表

Requirement is to Group record of table based on 10 second time interval. Given table

 Id      DateTime            Rank
 1     2011-09-27 18:36:15      1
 2     2011-09-27 18:36:15      1
 3     2011-09-27 18:36:19      1
 4     2011-09-27 18:36:23      1
 5     2011-09-27 18:36:26      1
 6     2011-09-27 18:36:30      1
 7     2011-09-27 18:36:32      1
 8     2011-09-27 18:36:14      2
 9     2011-09-27 18:36:16      2
 10    2011-09-27 18:36:35      2

组应该是这样

 Id      DateTime            Rank    GroupRank
 1     2011-09-27 18:36:15      1         1
 2     2011-09-27 18:36:15      1         1
 3     2011-09-27 18:36:19      1         1
 4     2011-09-27 18:36:23      1         1
 5     2011-09-27 18:36:26      1         2
 6     2011-09-27 18:36:30      1         2
 7     2011-09-27 18:36:32      1         2
 8     2011-09-27 18:36:14      2         3
 9     2011-09-27 18:36:16      2         3
 10    2011-09-27 18:36:35      2         4

对于排名1的最短时间为18:36:15，并且基于18:36:15到18:36:24之间的所有记录都应该在一个组中，依此类推.

For Rank 1 Minimum time is 18:36:15 and based on that all records between 18:36:15 to 18:36:24 should be in a group and so on.

我要在同一张表中使用GroupRank.因此，它将带有density_Rank()Over子句.谁能帮我用SQL编写查询.

I want GroupRank in the same table. so it would be something with dense_Rank() Over clause. Can anyone help me to write the query in SQL.

推荐答案

您需要分两步进行操作，第一步是通过获取与最短时间的差值(秒)将每条记录分成10组.对于每个等级，将其除以10，然后将其四舍五入为最接近的整数.

You need to do this in two steps, the first is to separate each record into its 10 second groups, by getting the number of seconds difference from the minimum time for each rank, dividing it by 10, then rounding it down to the nearest integer.

SELECT  *,
        SecondGroup = FLOOR(DATEDIFF(SECOND, 
                                    MIN([DateTime]) OVER(PARTITION BY [Rank]), 
                                    [DateTime]) / 10.0)
FROM    #T;

哪个给:

Id  DateTime                    Rank    SecondGroup
---------------------------------------------------
1   2011-09-27 18:36:15.000     1       0
2   2011-09-27 18:36:15.000     1       0
3   2011-09-27 18:36:19.000     1       0
4   2011-09-27 18:36:23.000     1       0
5   2011-09-27 18:36:26.000     1       1
6   2011-09-27 18:36:30.000     1       1
7   2011-09-27 18:36:32.000     1       1
8   2011-09-27 18:36:14.000     2       0
9   2011-09-27 18:36:16.000     2       0
10  2011-09-27 18:36:35.000     2       2

然后您可以按Rank和SecondGroup进行DENSE_RANK排序:

Then you can do your DENSE_RANK ordering by Rank and SecondGroup:

SELECT  Id, [DateTime], [Rank],
        GroupRank = DENSE_RANK() OVER(ORDER BY [Rank], SecondGroup)
FROM    (   SELECT  *,
                    SecondGroup = FLOOR(DATEDIFF(SECOND, 
                                                MIN([DateTime]) OVER(PARTITION BY [Rank]), 
                                                [DateTime]) / 10.0)
            FROM    #T
        ) AS t;

哪个会提供您想要的输出.

Which gives your desired output.

样品数据

CREATE TABLE #T (Id INT, [DateTime] DATETIME, [Rank] INT);

INSERT #T (Id, [DateTime], [Rank])
VALUES
    (1, '2011-09-27 18:36:15', 1),
    (2, '2011-09-27 18:36:15', 1),
    (3, '2011-09-27 18:36:19', 1),
    (4, '2011-09-27 18:36:23', 1),
    (5, '2011-09-27 18:36:26', 1),
    (6, '2011-09-27 18:36:30', 1),
    (7, '2011-09-27 18:36:32', 1),
    (8, '2011-09-27 18:36:14', 2),
    (9, '2011-09-27 18:36:16', 2),
    (10, '2011-09-27 18:36:35', 2);

这篇关于在SQL Server中基于10秒时间间隔对记录进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！