代码之家 › 专栏 › 技术社区 › Bob Horn

按日期范围分组行

sql-server-2014 date sql-server sql

Bob Horn · 技术社区 · 6 年前

我想知道对于一个用户来说,在一定的秒数(比如10秒)内存在多少行。因此,根据这些数据:

UserId  CreatedDate
4.........2017-01-11 01:40:19:077
4.........2017-01-11 01:40:19:437
4.........2017-01-11 01:40:20:077
4.........2017-01-11 01:50:19:077
4.........2017-01-11 02:40:19:077
4.........2017-01-11 02:40:19:437
4.........2017-01-11 02:40:20:077
4.........2017-01-11 02:40:20:437
4.........2017-01-11 02:40:21:077
4.........2017-01-11 02:40:22:077
4.........2017-01-11 02:40:23:077
4.........2017-01-11 03:15:19:077
4.........2017-01-11 03:40:19:077
4.........2017-01-11 04:40:19:077

前三行将被分组在一起,第5-11行将被分组在一起(因为它们之间的间隔都在10秒内)。

我试过这样的方法,但那只会给我两个一组的行。我想知道10秒范围内的所有行。

;WITH CTE AS
(
    SELECT UserId
        ,CreatedDate
        ,ISNULL(LAG(CreatedDate) OVER (Partition BY UserId ORDER BY CreatedDate), '1/1/2000') AS PriorCreatedDate
    FROM Foo
)
SELECT *
FROM CTE
WHERE DATEDIFF(SECOND,PriorCreatedDate,CreatedDate) <= 1
ORDER BY UserId, CreatedDate

这可能吗?

1 回复 | 直到 6 年前

John Cappelletti 6 年前

也许是这样的。。。

Declare @YourTable Table ([UserId] int,[CreatedDate] datetime)
Insert Into @YourTable Values 
 (4,'2017-01-11 01:40:19:077')
,(4,'2017-01-11 01:40:19:437')
,(4,'2017-01-11 01:40:20:077')
,(4,'2017-01-11 01:50:19:077')
,(4,'2017-01-11 02:40:19:077')
,(4,'2017-01-11 02:40:19:437')
,(4,'2017-01-11 02:40:20:077')
,(4,'2017-01-11 02:40:20:437')
,(4,'2017-01-11 02:40:21:077')
,(4,'2017-01-11 02:40:22:077')
,(4,'2017-01-11 02:40:23:077')
,(4,'2017-01-11 03:15:19:077')
,(4,'2017-01-11 03:40:19:077')
,(4,'2017-01-11 04:40:19:077')

;with cte as (
    Select *
          ,Flg = case when datediff(SECOND,lag(CreatedDate,1,CreatedDate) over (Partition By UserID Order by CreatedDate),CreatedDate ) >=10 then 1 else 0 end
     From @YourTable
) 
Select UserID
      ,DateR1 = min(CreatedDate)
      ,DateR2 = max(CreatedDate)
      ,RecCnt = sum(1)
 From (Select *,Grp=sum(Flg) over (partition by UserID order by CreatedDate) From  cte ) A
 Group by UserID,Grp

退换商品

UserID  DateR1                      DateR2                      RecCnt
4       2017-01-11 01:40:19.077     2017-01-11 01:40:20.077     3
4       2017-01-11 01:50:19.077     2017-01-11 01:50:19.077     1
4       2017-01-11 02:40:19.077     2017-01-11 02:40:23.077     7
4       2017-01-11 03:15:19.077     2017-01-11 03:15:19.077     1
4       2017-01-11 03:40:19.077     2017-01-11 03:40:19.077     1
4       2017-01-11 04:40:19.077     2017-01-11 04:40:19.077     1

如果你要执行

;with cte as (
    Select *
          ,Flg = case when datediff(SECOND,lag(CreatedDate,1,CreatedDate) over (Partition By UserID Order by CreatedDate),CreatedDate ) >=10 then 1 else 0 end
     From @YourTable
) 
Select *
      ,Grp=sum(Flg) over (partition by UserID order by CreatedDate) 
From  cte

结果是:

注意Flg和Grp列。Grp列本质上是Flg列的运行总计。