是否有一种方法可以递归地查询具有日期范围的日期列?

Question

我得到了如下的行，其中包含日期列和用户名

visit_date | user_name
2021-01-01, a
2021-01-02, a
2021-01-03, a
2021-01-04, a
2021-01-04, a
2021-01-01, b

和我想要这样的查询结果从日期和日期与特定时间段。

visit_date_from | visit_date_to | user_name | count
2021-01-01, 2021-01-01, a, 1
2021-01-01, 2021-01-02, a, 2
2021-01-02, 2021-01-02, a, 1
2021-01-01, 2021-01-03, a, 3
2021-01-02, 2021-01-03, a, 2
2021-01-03, 2021-01-03, a, 1
2021-01-01, 2021-01-04, a, 5
2021-01-02, 2021-01-04, a, 4
2021-01-03, 2021-01-04, a, 3
2021-01-04, 2021-01-04, a, 2
2021-01-01, 2021-01-05, a, 4
.
.
.
.

有这样的查询方法吗?

Answer 1

递归查询的基本用法

create table t0812 as 
select date_format('2021-01-01', '%Y-%m-%d') dat, 'a' nam union all
select date_format('2021-01-02', '%Y-%m-%d') dat, 'a' nam union all
select date_format('2021-01-03', '%Y-%m-%d') dat, 'a' nam union all
select date_format('2021-01-04', '%Y-%m-%d') dat, 'a' nam union all
select date_format('2021-01-01', '%Y-%m-%d') dat, 'b' nam
;
with recursive rec(dat, to_dat, nam) as (
select dat, dat, nam from t0812 t0 
union all
select t1.dat,t2.dat, t1.nam 
from rec t1, 
t0812 t2 
where t1.nam = t2.nam 
and date_add(t1.to_dat, interval 1 day) = t2.dat
)
select * from rec t1
order by t1.nam, t1.to_dat, t1.dat
;