条件:求在连续时间下 每个用户连胜(success=1)或连败(success=2)的次数
SQL语句
select a.uid,a.success,count(uid) as times from
(select record_time,uid,success,
row_number() over (partition by uid,success order by record_time) as row1,
row_number() over (partition by uid order by record_time) as row2
FROM test ORDER BY record_time) a
group by a.uid,a.row2-a.row1,a.success
having count(1)>1
a 里面获取的内容如下:
需要的最终结果如下: