lxy2 回复 用户jGnXs7014103(提问者)
with a as(
select \'a1\' as 账号,\'A\'AS 号码,\'2022-07-01\' as 时间
UNION ALL
select \'a2\' as 账号,\'A\'AS 号码,\'2022-07-02\' as 时间
UNION ALL
select \'a3\' as 账号,\'A\'AS 号码,\'2022-07-09\' as 时间
UNION ALL
select \'a4\' as 账号,\'A\'AS 号码,\'2022-07-17\' as 时间
UNION ALL
select \'a5\' as 账号,\'A\'AS 号码,\'2022-07-20\' as 时间
UNION ALL
select \'a1\' as 账号,\'b\'AS 号码,\'2022-07-01\' as 时间
UNION ALL
select \'a1\' as 账号,\'b\'AS 号码,\'2022-07-05\' as 时间
),
b as (
select *,ROW_NUMBER()over(partition by 号码 order by 时间 desc )序号 from a
),
c as(
select *,
datediff(D,lead(时间,1)over(partition by 号码 order by 时间 desc ),时间) as 时间差,
lead(时间,1)over(partition by 号码 order by 时间 desc ) as 新时间,
case when datediff(D,lead(时间,1)over(partition by 号码 order by 时间 desc ),时间)>=0 and
datediff(D,lead(时间,1)over(partition by 号码 order by 时间 desc ),时间)<=5 then 1
else 0 end as 计数
from b
)
select sum(计数) as 总数量,
账号 from c
group by 账号--看看这个是不是你要的呢
1min目标场景问卷
立即参与
我使用lag（）over（）复制一列排序向下偏移一格做对比，开窗后再group by统计，原先列排序会恢复，统计出来的数量不对。有没有什么其他方法可以做计算，求大神！
