Microsoft SQL SERVER:根据第一个不同的行按30分钟对行进行分组(行可以有不同的日期和时间)

Question

为了示例起见，我有一个带有不同列的事务表，我只保留相关列。

我的表：交易

id  TransactionTime             idClient 
---------------------------------------------
1   2019-05-29 09:37:30.000     1531201523215
1   2019-05-29 09:37:30.000     1531201523215
2   2019-05-29 10:20:06.000     1531201523215
3   2019-05-29 10:45:19.000     1531201523215
4   2019-05-29 10:50:19.000     1531201523215
5   2019-05-29 10:51:43.000     1531201523215
6   2019-05-29 10:57:36.000     1531201523215
6   2019-05-29 10:57:36.000     1531201523215
7   2019-05-30 10:07:14.000     1531201523215
8   2019-05-30 10:37:12.000     1531201523215
9   2019-05-30 10:38:11.000     1531201523215
10  2019-05-30 10:46:17.000     1531201523215

我想要实现的是在30分钟内将行分组，所需的输出将是

id  TransactionTime             idClient            Count
----------------------------------------------------------
1   2019-05-29 09:37:30.000     1531201523215       2  
2   2019-05-29 10:20:06.000     1531201523215       2
4   2019-05-29 10:50:19.000     1531201523215       4
7   2019-05-30 10:07:14.000     1531201523215       2      
9   2019-05-30 10:38:11.000     1531201523215       2

我尝试使用：

SELECT ROW_NUMBER() OVER (
PARTITION BY tl.idClient
,(dateadd(minute, (datediff(second, 0, tl.TransactionTime) / 60 / 30) * 30, 0)) ORDER BY tl.idClient
,tl.TransactionTime
) AS filter_row

但我无法实现我想要的。。。已经被困4天了。。。请帮忙。

Answer 1

您需要一个递归查询。这个想法是从每个id_client的第一个记录开始(根据transaction_time(，然后通过升序transaction_time遍历组。

with 
tab as (
select t.*, row_number() over(partition by id_client order by transaction_time) rn
from mytable t
),
cte as (
select id, id_client, rn, transaction_time from tab where rn = 1
union all
select 
t.id,
t.id_client,
t.rn,
case when t.transaction_time > dateadd(minute, 30, c.transaction_time)
then t.transaction_time
else c.transaction_time
end
from cte c
inner join tab t on t.id_client = c.id_client and t.rn = c.rn + 1
)
select min(id) id, transaction_time, id_client, count(*) cnt
from cte 
group by id_client, transaction_time
order by id_client, transaction_time

DB Fiddle上的演示：

id|transaction_time|id_client|cnt-：|：--------------------------------------------|：------------------------------------------------|：1 | 2019-05-29 09:37:30.00 | 1531201523215 | 22 | 2019-05-29 10:20:06.000 | 1531201523215 | 24 | 2019-05-29 10:50:19.000 | 1531201523215 | 47 | 2019-05-30 10:07:14.000 | 1531201523215 | 29 | 2019-05-30 10:38:11.000 | 1531201523215 | 2