如何将前向填充作为PL/PGSQL函数 [英] How to do forward fill as a PL/PGSQL function

问题描述

我正在尝试创建一个等同于 pandas ‘ffill’函数的pl/pgsql。该函数应转发填充空值。在本例中，我可以进行正向填充，但在尝试从我的过程创建函数时出现错误。该函数似乎准确地反映了过程，但我在... as $1部分遇到语法错误。

为什么？我应该读些什么来澄清？

-- Forward fill experiment
DROP TABLE IF EXISTS example;
create temporary table example(id int, str text, val integer);
insert into example values
(1, 'a', null),
(1, null, 1),
(2, 'b', 2),
(2,null ,null );

select * from example

select (case
            when str is null
            then lag(str,1) over (order by id)
            else str
            end) as str,
            (case
            when val is null
            then lag(val,1) over (order by id)
            else val
            end) as val
from example

-- Forward fill function
create or replace function ffill(text, text, text) -- takes column to fill, the table, and the ordering column 
returns text as $$
    begin
        select (case
            when $1 is null
            then lag($1 ,1) over (order by $3)
            else $1
            end) as $1
        from $2;
    end;
$$ LANGUAGE plpgsql;

更新1：我采用不同的方法进行了一些额外的实验。代码如下。它使用与上面相同的示例表。

CREATE OR REPLACE FUNCTION GapFillInternal( 
    s anyelement, 
    v anyelement) RETURNS anyelement AS 
$$
declare 
    temp alias for $0 ;
begin
    RAISE NOTICE 's= %, v= %', s, v;
    if v is null and s notnull then
        temp := s;
    elsif s is null and v notnull then
        temp := v;
    elsif s notnull and v notnull then 
        temp := v;
    else
        temp := null;
    end if;
    RAISE NOTICE 'temp= %', temp;
    return temp;
END; 
$$ LANGUAGE PLPGSQL; 

CREATE AGGREGATE GapFill(anyelement) ( 
  SFUNC=GapFillInternal, 
  STYPE=anyelement 
);

select id, str, val, GapFill(val) OVER (ORDER by id) as valx
from example;

结果表如下：

我不明白valx列第一行中的"1"来自哪里。从raise notice输出应为Null，这似乎是CREATE AGGREGATE docs的正确预期。

推荐答案

正确调用

您显示的查询似乎不正确，测试用例太少，无法显示它。

假设您要按id进行前向填充(&Q；)，则必须这样说：

SELECT row_num, id
     , str, gap_fill(str) OVER w AS strx
     , val, gap_fill(val) OVER w AS valx
FROM   example
WINDOW w AS (PARTITION BY id ORDER BY row_num);  -- !

WINDOW子句只是语法上的便利，以避免重复拼写相同的窗口框架。重要的是增加了PARTITION子句。

功能更简单

实际上要简单得多：

CREATE OR REPLACE FUNCTION gap_fill_internal(s anyelement, v anyelement)
  RETURNS anyelement
  LANGUAGE plpgsql AS
$func$
BEGIN
RETURN COALESCE(v, s);  -- that's all!
END
$func$;

CREATE AGGREGATE gap_fill(anyelement) ( 
  SFUNC = gap_fill_internal, 
  STYPE = anyelement 
);

在快速测试中略快。

标准SQL

无自定义函数：

SELECT row_num, id
     , str, first_value(str) OVER (PARTITION BY id, ct_str ORDER BY row_num) AS strx
     , val, first_value(val) OVER (PARTITION BY id, ct_val ORDER BY row_num) AS valx
FROM (     
   SELECT *, count(str) OVER w AS ct_str, count(val) OVER w AS ct_val
   FROM   example
   WINDOW w AS (PARTITION BY id ORDER BY row_num)
   ) sub;

使用子查询，查询会变得更加复杂。表现也差不多。快速测试中的速度稍慢。

这些相关答案中的更多说明：

• Carry over long sequence of missing values with Postgres
• Retrieve last known value for each column of a row
• SQL group table by "leading rows" without pl/sql

db<；>；fiddlehere-使用扩展的测试用例显示全部

这篇关于如何将前向填充作为PL/PGSQL函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！