MSSQL 選擇日期範圍內的實體

我的任務是找出使用者每分鐘執行操作的頻率。我找到了按日期分組的解決方案：

CREATE TABLE UserAction(Id int, Date datetime, Action nvarchar(200))
Go
INSERT INTO UserAction VALUES
   (111, '2000-01-01 15:30:10', 'Action Made'),
   (555, '2000-01-01 15:30:10', 'Action Made'), 
   (111, '2000-01-01 15:30:20', 'Action Made'),
   (111, '2000-01-01 15:31:05', 'Action Made'),
   (111, '2000-01-01 15:31:10', 'Action Made'),
   (555, '2000-01-01 15:31:10', 'Action Made'),
   (111, '2000-01-01 15:32:05', 'Action Made'),
   (555, '2000-01-01 15:32:05', 'Action Made'),
   (555, '2000-01-03 15:35:10', 'Action Made'),
   (555, '2000-01-03 15:35:20', 'Action Made'),
   (111, '2000-01-03 15:36:05', 'Action Made'),
   (555, '2000-01-03 15:36:05', 'Action Made')
GO

-- query 
SELECT 
   Id,
   MIN(Date) as [From],
   MAX(Date) as [To],
   COUNT(*) as [Count]
FROM 
   UserAction
GROUP BY 
   ID,
   DATEADD(MINUTE, 1 + (DATEDIFF(MINUTE, 0, Date) / 1) * 1, 0)
ORDER BY
   Id

-- should be
SELECT 
   * 
FROM 
   (VALUES
       (111, '2000-01-01 15:30:10.000', '2000-01-01 15:31:05.000', 3),
       (111, '2000-01-01 15:31:10.000', '2000-01-01 15:32:05.000', 2),
       (111, '2000-01-03 15:36:05.000', '2000-01-03 15:36:05.000', 1),
       (111, '2000-01-01 15:30:10.000', '2000-01-01 15:30:10.000', 1),
       (111, '2000-01-01 15:31:10.000', '2000-01-01 15:32:05.000', 2),
       (111, '2000-01-03 15:35:10.000', '2000-01-03 15:36:05.000', 3)
   )
as _shouldBe(Id, [From], [To], [Count])

但我無法在一分鐘（60 秒）內找出如何按 datediff 分組。現在的結果：

Id          From                    To                      Count
----------- ----------------------- ----------------------- -----------
111         2000-01-01 15:30:10.000 2000-01-01 15:30:20.000 2
111         2000-01-01 15:31:05.000 2000-01-01 15:31:10.000 2
111         2000-01-01 15:32:05.000 2000-01-01 15:32:05.000 1
111         2000-01-03 15:36:05.000 2000-01-03 15:36:05.000 1
555         2000-01-01 15:30:10.000 2000-01-01 15:30:10.000 1
555         2000-01-01 15:31:10.000 2000-01-01 15:31:10.000 1
555         2000-01-01 15:32:05.000 2000-01-01 15:32:05.000 1
555         2000-01-03 15:35:10.000 2000-01-03 15:35:20.000 2
555         2000-01-03 15:36:05.000 2000-01-03 15:36:05.000 1

應該如何：

Id          From                    To                      Count
----------- ----------------------- ----------------------- -----------
111         2000-01-01 15:30:10.000 2000-01-01 15:31:05.000 3
111         2000-01-01 15:31:10.000 2000-01-01 15:32:05.000 2
111         2000-01-03 15:36:05.000 2000-01-03 15:36:05.000 1
111         2000-01-01 15:30:10.000 2000-01-01 15:30:10.000 1
111         2000-01-01 15:31:10.000 2000-01-01 15:32:05.000 2
111         2000-01-03 15:35:10.000 2000-01-03 15:36:05.000 3

如果您能幫助我在這裡找到最佳解決方案，我將不勝感激。

您應該從每個 ID 的最小日期開始按除以 60 的結果進行分組：

declare @UserAction table (Id int, Date datetime, Action nvarchar(200))

INSERT INTO @UserAction VALUES
   (111, '2000-01-01 15:30:10', 'Action Made'),
   (555, '2000-01-01 15:30:10', 'Action Made'), 
   (111, '2000-01-01 15:30:20', 'Action Made'),
   (111, '2000-01-01 15:31:05', 'Action Made'),
   (111, '2000-01-01 15:31:10', 'Action Made'),
   (555, '2000-01-01 15:31:10', 'Action Made'),
   (111, '2000-01-01 15:32:05', 'Action Made'),
   (555, '2000-01-01 15:32:05', 'Action Made'),
   (555, '2000-01-03 15:35:10', 'Action Made'),
   (555, '2000-01-03 15:35:20', 'Action Made'),
   (111, '2000-01-03 15:36:05', 'Action Made'),
   (555, '2000-01-03 15:36:05', 'Action Made');

with cte AS
(
select *, 
      min(Date) over(partition by id) as min_dt
from @UserAction 
)

,cte_gr AS
(
select *,
      datediff(ss, min_dt, Date) / 60 as gr
from cte
)

select id, 
      min(date) as [From],
      max(date) as [To],
      count(*) as cnt
from cte_gr
group by id, gr;

引用自：https://dba.stackexchange.com/questions/195428