PostgreSQL中的递归+lag-实现Pandas中的ffill操作

递归查询语法结构

WITH RECURSIVE recursive_cte AS (
  -- Anchor member
  SELECT 
    id,
    parent_id,
    name
  FROM your_table
  WHERE parent_id IS NULL
  UNION ALL
  -- Recursive member
  SELECT 
    t.id,
    t.parent_id,
    t.name
  FROM your_table t
  JOIN recursive_cte r ON t.parent_id = r.id
)
SELECT * FROM recursive_cte;

在递归查询中，递归操作会一直执行直到满足终止条件。在递归CTE中，终止条件通常由递归查询的结果不再发生变化来确定。在每次递归迭代时，系统会检查递归成员是否产生了新的结果，如果没有产生新的结果，递归查询就会终止。

参考案例：

一个array，比如【1,null，null，0，null，null，1，null】，null 需要向前继承，最后结果应该是[1,1,1,0,0,0,1,1] 

实现：

drop table if exists my_table;
create temp table my_table as
with t as (
	select unnest(string_to_array('1,1,,,0,0,,,,1,,0,,0,0', ','))  my_column
	,generate_series(1,array_length(string_to_array('1,1,,,0,0,,,,1,,0,,0,0', ','), 1)) id
)
select
	id
	,case when my_column = '' then null else my_column end as my_column
from t
;


do
$body$
declare
	$1 int4;
begin
	
with t as (
	select
		id - row_number() over (order by id) as offset_c
	from my_table
	where my_column isnull
)
, c as (
	select count(*) as c from t group by offset_c
)
select max(c) from c into $1; --计算最小递归深度
	
execute format($sql$
	drop table if exists tmp_res;
	create temp table tmp_res as
	with recursive rr as (
	select 
		id
		,my_column
		,coalesce(my_column, lag(my_column) over (order by id)) res
		,1 as c
	from my_table
	union all
	select 
		t.id
		,t.my_column
		,coalesce(t.my_column, lag(t1.res) over (order by t.id)) res
		,t1.c + 1 as c
	from my_table t
	inner join rr t1
		on t.id = t1.id
	where c < %1$s::int4
	)
	select * from rr
$sql$, $1);
end
$body$;


select * from tmp_res where c = (select max(c) from tmp_res) order by id

效果：