对SQL Server中每段的客户事务进行排名

Question

我在下面有一个表里面有客户的交易细节。

CustomerID1/29/2022

使用lag()检查TransDate在2天内的变化，并组在一起(作为一个段)。然后使用row_number()生成所需的序列

with 
cte as
(
select *,
g = case when datediff(day,
lag(t.TransDate) over (order by t.TransDate),
t.TransDate
) <= 2
then 0
else 1
end
from   tbl t
),
cte2 as
(
select *, grp = sum(g) over (order by TransDate)
from cte
)
select *, row_number() over (partition by grp order by TransDate)
from   cte2 

,db&lt的在小提琴演示