求连续三天有销售记录的店铺
表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;