GreenPlum 递归查询

应用场景

drop table if exists tmp;
create table tmp as 
select '张三' name,to_date('20220401','yyyymmdd') updatedate
union all
select '张三' name,to_date('20220405','yyyymmdd') updatedate
union all
select '李四' name,to_date('20220228','yyyymmdd') updatedate
union all
select '李四' name,to_date('20220226','yyyymmdd') updatedate
union all
select '李四' name,to_date('20220303','yyyymmdd') updatedate
distributed randomly;

• 原表
  

• 实现目标查询结果：相同名字补全最小日期到最大日期之间的日期
  

递归查询

with recursive t(name,n) as (
	select name,min(updatedate) from tmp 
	group by name
	union all 
	select t.name,n+1 from t , (
		select name
			,max(updatedate) updatedate_max
		from tmp 
		group by name
	) b 
	where t.name = b.name
		and n<updatedate_max
)
select name,n from t;