MYSQL系列(4) 窗口函数解决连续几天登录的问题

求连续三天有销售记录的店铺

表t_jd 字段shopid, string, sale

第一步：先给员工打编号

select 
		shopid, dt, sale,
		row_number() over(partition by shopid order by dt) as rn
from 
		t_jd;

第二步：根据编号，生成连续的日期

select 
		shopid, dt, sale, rn,
		date_sub(to_date(dt), rn)
from
		(
        select 
				shopid, dt, sale,
				row_number() over(partition by shopid order by dt) as rn
		from 
				t_jd
        ) t

第三步，分组求count

select 
		shopid, count(1) as cnt
from
		select 
		shopid, dt, sale, rn,
		date_sub(to_date(dt), rn)
from
		(
        select 
				shopid, dt, sale,
				row_number() over(partition by shopid order by dt) as rn
		from 
				t_jd
        ) t
        ) t1

第四步，筛选出连续天数大于等于3的

select 
		shopid
from
		(
        select 
		shopid, count(1) as cnt
		from
		select 
		shopid, dt, sale, rn,
		date_sub(to_date(dt), rn)
		from
		(
        select 
				shopid, dt, sale,
				row_number() over(partition by shopid order by dt) as rn
		from 
				t_jd
        ) t
        ) t1)t2
where t2.cnt>=3;