创表造数据:
create table table1(name string, date string);
insert into table1 values
('张三','2021-01-01'),
('张三','2021-01-02'),
('张三','2021-01-03'),
('张三','2021-01-02'),
('李四','2021-01-01'),
('李四','2021-01-02'),
('王五','2021-01-03'),
('王五','2021-01-02'),
('王五','2021-01-02');
select * from game;
求连续三天登录人员:
with t1 as (
select distinct name,`date` from table1 --1、先对用户和日期进行联合去重
),t2 as (
select *,
row_number() over (partition by name order by date)rn --2、对同一人的不同日期进行排列序号
from t1
),t3 as (
select *,
date_sub(date,rn) date2 --3、用日期减去序号,得到一个临时日期
from t2
) select name, count(date2) cnt
from t3 group by name,date2
having count(date2) >=3 --4、以用户和临时日期作为聚合维度,筛选出临时日期个数 >= n 的行
;