sql - 计算每分钟插入的记录数

Question

我有一个日志记录表。在该表中，我记录了 SSIS 正在执行的 http 请求，以便从 Web API 下载数据。某些 web api 限制了您在每个时间范围内可以发送的请求数量

现在我需要做的是检查 SSIS 是否每 url 每 Y 秒(阈值)触发超过 X 个 http 请求。

结果应该是这样的(number_of_request 不是基于实际数据，rest 是)。

阈值 = 60 秒

[min_start],[max_start](该阈值内的第一个和最后一个启动时间

url 但没有参数

(我需要 min 和 max 以便我可以找到其中的请求
阈值)

[treshold], [min_start],[max_start],[url], [number_of_requests]
[60seconds1], [06:00:31],[06:00:47], [MyUrl.com], [25]
[60seconds2], [06:00:51],[06:01:22], [MyUrl.com], [62]
[60seconds3], [06:03:49],[06:05:38], [MyUrl.com], [1]   <-- This is interesting, query last longer than treshold. How to cope with that?

在下表中，您将找到发送的(虚构的)请求及其各自的开始时间。
我想我必须对排名做些什么，但如何做？

CREATE TABLE [test].[logging](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [taskname] [nvarchar](256) NULL,
    [start] [datetime2](7) NULL,
    [url] [nvarchar](100) NULL
) 
GO
SET IDENTITY_INSERT [test].[logging] ON 
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (1, N'tf_finmut', CAST(N'2020-04-10T06:00:31.0000000' AS DateTime2), N'https://MyUrl.com/find&id=8')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (2, N'tf_finmut', CAST(N'2020-04-10T06:00:36.0000000' AS DateTime2), N'https://MyUrl.com/find&id=10')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (3, N'tf_finmut', CAST(N'2020-04-10T06:00:42.0000000' AS DateTime2), N'https://MyUrl.com/find&id=12')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (4, N'tf_finmut', CAST(N'2020-04-10T06:00:47.0000000' AS DateTime2), N'https://MyUrl.com/find&id=14')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (5, N'tf_finmut', CAST(N'2020-04-10T06:00:51.0000000' AS DateTime2), N'https://MyUrl.com/find&id=16')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (6, N'tf_finmut', CAST(N'2020-04-10T06:00:56.0000000' AS DateTime2), N'https://MyUrl.com/find&id=18')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (7, N'tf_finmut', CAST(N'2020-04-10T06:01:01.0000000' AS DateTime2), N'https://MyUrl.com/find&id=20')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (8, N'tf_finmut', CAST(N'2020-04-10T06:01:07.0000000' AS DateTime2), N'https://MyUrl.com/find&id=22')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (9, N'tf_finmut', CAST(N'2020-04-10T06:01:12.0000000' AS DateTime2), N'https://MyUrl.com/find&id=24')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (10, N'tf_finmut', CAST(N'2020-04-10T06:01:17.0000000' AS DateTime2), N'https://MyUrl.com/find&id=26')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (11, N'tf_finmut', CAST(N'2020-04-10T06:01:22.0000000' AS DateTime2), N'https://MyUrl.com/find&id=28')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (12, N'tf_finmut', CAST(N'2020-04-10T06:03:49.0000000' AS DateTime2), N'https://MyUrl.com/find&id=30')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (13, N'tf_finmut', CAST(N'2020-04-10T06:05:38.0000000' AS DateTime2), N'https://MyUrl.com/find&id=32')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (14, N'tf_finmut', CAST(N'2020-04-10T06:07:15.0000000' AS DateTime2), N'https://MyUrl.com/find&id=34')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (15, N'tf_finmut', CAST(N'2020-04-10T06:08:57.0000000' AS DateTime2), N'https://MyUrl.com/find&id=36')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (16, N'tf_finmut', CAST(N'2020-04-10T06:09:06.0000000' AS DateTime2), N'https://MyUrl.com/find&id=39')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (17, N'tf_finmut', CAST(N'2020-04-10T06:09:11.0000000' AS DateTime2), N'https://MyUrl.com/find&id=41')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (18, N'tf_finmut', CAST(N'2020-04-10T06:09:16.0000000' AS DateTime2), N'https://MyUrl.com/find&id=43')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (19, N'tf_finmut', CAST(N'2020-04-10T06:09:22.0000000' AS DateTime2), N'https://MyUrl.com/find&id=45')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (20, N'tf_finmut', CAST(N'2020-04-10T06:09:29.0000000' AS DateTime2), N'https://MyUrl.com/find&id=47')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (21, N'tf_finmut', CAST(N'2020-04-10T06:09:34.0000000' AS DateTime2), N'https://MyUrl.com/find&id=49')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (22, N'tf_finmut', CAST(N'2020-04-10T06:09:40.0000000' AS DateTime2), N'https://MyUrl.com/find&id=51')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (23, N'tf_finmut', CAST(N'2020-04-10T06:09:45.0000000' AS DateTime2), N'https://MyUrl.com/find&id=53')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (24, N'tf_finmut', CAST(N'2020-04-10T06:09:50.0000000' AS DateTime2), N'https://MyUrl.com/find&id=55')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (25, N'tf_finmut', CAST(N'2020-04-10T06:10:01.0000000' AS DateTime2), N'https://MyUrl.com/find&id=57')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (26, N'tf_finmut', CAST(N'2020-04-10T06:10:07.0000000' AS DateTime2), N'https://MyUrl.com/find&id=59')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (27, N'tf_finmut', CAST(N'2020-04-10T06:12:47.0000000' AS DateTime2), N'https://MyUrl.com/find&id=61')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (28, N'tf_finmut', CAST(N'2020-04-10T06:14:32.0000000' AS DateTime2), N'https://MyUrl.com/find&id=63')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (29, N'tf_finmut', CAST(N'2020-04-10T06:16:16.0000000' AS DateTime2), N'https://MyUrl.com/find&id=65')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (30, N'tf_finmut', CAST(N'2020-04-10T06:17:59.0000000' AS DateTime2), N'https://MyUrl.com/find&id=67')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (31, N'tf_finmut', CAST(N'2020-04-10T06:19:07.0000000' AS DateTime2), N'https://MyUrl.com/find&id=70')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (32, N'tf_finmut', CAST(N'2020-04-10T06:19:47.0000000' AS DateTime2), N'https://MyUrl.com/find&id=72')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (33, N'tf_finmut', CAST(N'2020-04-10T06:19:52.0000000' AS DateTime2), N'https://MyUrl.com/find&id=74')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (34, N'tf_finmut', CAST(N'2020-04-10T06:19:58.0000000' AS DateTime2), N'https://MyUrl.com/find&id=76')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (35, N'tf_finmut', CAST(N'2020-04-10T06:20:03.0000000' AS DateTime2), N'https://MyUrl.com/find&id=78')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (36, N'tf_finmut', CAST(N'2020-04-10T06:20:09.0000000' AS DateTime2), N'https://MyUrl.com/find&id=80')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (37, N'tf_finmut', CAST(N'2020-04-10T06:20:14.0000000' AS DateTime2), N'https://MyUrl.com/find&id=82')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (38, N'tf_finmut', CAST(N'2020-04-10T06:20:19.0000000' AS DateTime2), N'https://MyUrl.com/find&id=84')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (39, N'tf_finmut', CAST(N'2020-04-10T06:20:24.0000000' AS DateTime2), N'https://MyUrl.com/find&id=86')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (40, N'tf_finmut', CAST(N'2020-04-10T06:20:38.0000000' AS DateTime2), N'https://MyUrl.com/find&id=88')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (41, N'tf_finmut', CAST(N'2020-04-10T06:20:44.0000000' AS DateTime2), N'https://MyUrl.com/find&id=90')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (42, N'tf_finmut', CAST(N'2020-04-10T06:23:02.0000000' AS DateTime2), N'https://MyUrl.com/find&id=92')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (43, N'tf_finmut', CAST(N'2020-04-10T06:24:31.0000000' AS DateTime2), N'https://MyUrl.com/find&id=94')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (44, N'tf_finmut', CAST(N'2020-04-10T06:25:56.0000000' AS DateTime2), N'https://MyUrl.com/find&id=96')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (45, N'tf_finmut', CAST(N'2020-04-10T06:27:20.0000000' AS DateTime2), N'https://MyUrl.com/find&id=98')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (46, N'tf_finmut', CAST(N'2020-04-10T06:27:30.0000000' AS DateTime2), N'https://MyUrl.com/find&id=101')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (47, N'tf_finmut', CAST(N'2020-04-10T06:27:36.0000000' AS DateTime2), N'https://MyUrl.com/find&id=103')
GO
INSERT [test].[logging] ([id], [taskname], [start], [url]) VALUES (48, N'tf_finmut', CAST(N'2020-04-10T06:27:42.0000000' AS DateTime2), N'https://MyUrl.com/find&id=105')
GO
SET IDENTITY_INSERT [test].[logging] OFF
GO

Answer 1

这是一个间隙和孤岛问题，可能最好通过 lag() 来解决以及定义岛屿的累积总和:

select trunc_url, min(start), max(start), count(*)
from (select l.*,
             sum(case when prev_start >= dateadd(second, -60, start)
                      then 0 else 1
                 end) over (partition by trunc_url order by start) as grp
      from (select l.*, v.trunc_url,
                   lag(start) over (partition by trunc_url order by start) as prev_start
            from logging l cross apply
                 (values (left(url, len(url) - charindex('/', reverse(url))))) v(trunc_url)
           ) l
     ) l
group by trunc_url, grp
order by trunc_url, min(start)

Here是一个db<> fiddle 。

这些结果与您想要的结果不符，但根据问题，它们对我来说是有意义的。