对符合条件的日期范围内的行进行计数

Question

我不确定这个问题能否在SQL server中解决，但我还是决定问你。

让我们创建一个表并添加一些随机变量：

CREATE TABLE test_c (
timestamp datetime,
product varchar(max),
client_id varchar(max),
seconds int,
)
--Insert values
INSERT INTO test_c VALUES
('2022-08-05 08:17:55', 'B', '5', 12)
, ('2022-08-05 07:14:59', 'A', '11', 1)
,   ('2022-08-05 07:16:44', 'A', '11', 1)
,   ('2022-08-05 07:17:50', 'A', '11', 10)
,   ('2022-08-05 07:22:50', 'A', '11', 1)
,   ('2022-08-05 07:31:59', 'A', '11', 1)
,   ('2022-08-05 08:16:44', 'B', '5', 1)
,   ('2022-08-05 08:17:50', 'B', '5', 7)
,   ('2022-08-05 08:22:50', 'B', '5', 3)
,   ('2022-08-05 08:31:59', 'B', '5', 1)
,   ('2022-08-05 08:44:59', 'B', '5', 1)
,   ('2022-08-05 08:45:59', 'B', '5', 1)
,   ('2022-08-05 08:46:59', 'C', '3', 3)
,   ('2022-08-05 08:16:44', 'C', '3', 3)
,   ('2022-08-05 08:17:50', 'C', '3', 3)
,   ('2022-08-05 08:22:50', 'C', '3', 3)
,   ('2022-08-05 08:31:59', 'C', '3', 3)
,   ('2022-08-05 08:44:59', 'C', '3', 3)
,   ('2022-08-05 08:45:59', 'C', '3', 3)
,   ('2022-08-05 08:46:59', 'C', '3', 3)

现在，让我们在表中添加两列：

• "before_5"-给定时间戳前五分钟
• "5〃-给定时间戳的五分钟

SELECT *
, DATEADD(minute, -5, timestamp) AS 'before_5'
, DATEADD(minute, 5, timestamp) AS 'after_5'
FROM test_c

现在我们的表格如下所示：

客户端id前5>后52022-08-08 07:09:592022:08-05 07:09:59

	时间戳	产品	秒
2022-08-05 07:14:590.00	A	11	1
2022-08-05 07:14:59 00	A	11	1

一个简单的关联子查询应该会给出您期望的结果。

select [Timestamp], Product, Client_Id, Seconds, 
case when seconds < 8 then 0 else (
select Count(*) from t t2 
where t2.product = t.product 
and t.timestamp >= DateAdd(minute, -5, t2.timestamp) 
and t.timestamp < DateAdd(minute, 5, t2.timestamp)
) end Num_Of_Products
from t
order by [Timestamp];

目前尚不清楚您是否希望将结果筛选为仅包含seconds >=8的行，但如果是这种情况，则查询只需要where条件并删除case表达式：

select [Timestamp], Product, Client_Id, Seconds, (
select Count(*) from t t2 
where t2.product = t.product 
and t.timestamp >= DateAdd(minute, -5, t2.timestamp) 
and t.timestamp < DateAdd(minute, 5, t2.timestamp)
) Num_Of_Products
from t
where t.seconds >= 8
order by [Timestamp];

请注意，datetime几年前就被datetime2否决了，如果您的产品名称不是>长度8kb。

请参见DB<gt；Fiddle

1. 使用公共表表达式(CTE(、表变量或临时表来构建seconds大于或等于8的表数据。

2. test_c表与CTE表交叉应用。GROUP BY、timestamp、product_id和client_id列。对num_of_products执行COUNT。

WITH CTE AS
(
SELECT [timestamp]
, [product]
, [client_id]
, [seconds]
, DATEADD(minute, -5, timestamp) AS 'before_5'
, DATEADD(minute, 5, timestamp) AS 'after_5'
FROM test_c
WHERE seconds >= 8
) 
SELECT b.[timestamp]
, a.[product]
, a.[client_id]
, MAX(b.[seconds]) AS [seconds]
, COUNT(1) AS [num_of_products]
FROM test_c a
CROSS APPLY (
SELECT *
FROM CTE b
WHERE a.[product] = b.[product]
and a.[client_id] = b.[client_id]
and a.[timestamp] >= [before_5] and a.[timestamp] <= [after_5]
) b
GROUP BY b.[timestamp], a.[product], a.[client_id]

样品DB Fiddle