2.使用row_number() over() 排序函数计算每个id的排名,SQL如下:
select *,row_number() over(partition by id order by date ) 'rank' from orde where orders is not NULL;
查看数据表:
3.将date日期字段减去rank排名字段,SQL如下:
select *,DATE_SUB(a.date,interval a.rank day) 'date_sub' from( select *,row_number() over(partition by id order by date ) 'rank' from orde where orders is not NULL ) a;
查看数据:
4.根据id和date分组并计算分组后的数量(count)、计算最早登录和最晚登录的时间,SQL如下:
select b.id,min(date) 'start_time',max(date) 'end_time',count(*) 'date_count' from( select *,DATE_SUB(a.date,interval a.rank day) 'date_sub' from( select *,row_number() over(partition by id order by date ) 'rank' from orde where orders is not NULL ) a ) b group by b.date_sub,id having count(*) >= 3 ;
查看数据:
参考资料:
SQL查询至少连续七天下单的用户
更多SQL内容来自木庄网络博客