PostgreSQL聚合函数和缺失的窗口行

Question

PostgreSQL聚合函数和缺失的窗口行

postgresqlaggregate-functionswindow-functions

4

我正在尝试定义一个 PostgreSQL 聚合函数，该函数能够识别帧子句中被要求但缺失的行。具体来说，考虑一个聚合函数 framer，它的作用是返回一个由通过它聚合的值组成的数组，其中框架中任何缺失的值都将返回为 null。

select
    n,
    v,
    framer(v) over (order by v rows between 2 preceding and 2 following) arr
from (values (1, 3200), (2, 2400), (3, 1600), (4, 2900), (5, 8200)) as v (n, v)
order by v

应该返回

"n" "v" "arr"
3   1600    {null,null,1600,2400,2900}
2   2400    {null,1600,2400,2900,3200}
4   2900    {1600,2400,2900,3200,8200}
1   3200    {2400,2900,3200,8200,null}
5   8200    {2900,3200,8200,null,null}

基本上，我想获取每个值周围的一定范围内的数值，并且知道左侧或右侧（或两侧）是否缺失对我非常重要。看起来很简单。我预期应该可以使用类似以下的方法：

create aggregate framer(anyelement) (
    sfunc = array_append,
    stype = anyarray,
    initcond = '{}'
);

但是它返回的结果是：

"n" "v" "arr"
3   1600    {1600,2400,2900}
2   2400    {1600,2400,2900,3200}
4   2900    {1600,2400,2900,3200,8200}
1   3200    {2400,2900,3200,8200}
5   8200    {2900,3200,8200}

当两个值缺失时，实际上只调用了三次sfunc。

我一直没有想到任何非荒谬的方式来捕获这些缺失的行。似乎应该有一个简单的解决方案，比如在聚合运行之前在数据中加入/添加一些哨兵空值，或者可能以某种方式将索引（和帧值）以及实际值传递给函数...

我希望将其实现为聚合，因为对于我想要做的事情，它提供了最好的用户体验。有更好的方法吗？

顺便说一下，我使用的是postgres 9.6。

- Chucky Ellison

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Sevanteri · Accepted Answer

好的，这是一个有趣的问题。 :)

我创建了一个聚合函数framer(anyarray, anyelement, int)，这样我们就可以根据窗口大小定义数组大小。

首先，我们用自己的framer_msfunc替换array_append：

CREATE OR REPLACE FUNCTION public.framer_msfunc(arr anyarray, val anyelement, size_ integer)
 RETURNS anyarray
 LANGUAGE plpgsql
AS $function$
DECLARE
    result ALIAS FOR $0;
    null_ val%TYPE := NULL; -- NULL of the same type as `val`
BEGIN

    IF COALESCE(array_length(arr, 1), 0) = 0 THEN
        -- create an array of nulls with the size of `size_`
        result := array_fill(null_, ARRAY[size_]);
    ELSE
        result := arr;
    END IF;

    IF result[size_] IS NULL THEN
        -- first run or after `minvfunc`.
        -- a NULL inserted at the end in `minvfunc` so we want to replace that.
        result[size_] := val;
    ELSE
        -- `minvfunc` not yet called so we just append and drop the first.
        result := (array_append(result, val))[2:];
    END IF;

    RETURN result;

END;
$function$

我们需要创建一个 minvfunc，因为它在移动聚合时是必需的。

CREATE OR REPLACE FUNCTION public.framer_minvfunc(arr anyarray, val anyelement, size_ integer)
 RETURNS anyarray
 LANGUAGE plpgsql
AS $function$
BEGIN

    -- drop the first in the array and append a null
    RETURN array_append(arr[2:], NULL);

END;
$function$

然后我们使用移动聚合参数定义聚合。

create aggregate framer(anyelement, int) (
    sfunc = framer_msfunc,
    stype = anyarray,
    msfunc = framer_msfunc,
    mstype = anyarray,
    minvfunc = framer_minvfunc,
    minitcond = '{}'
);

由于需要sfunc，我们也将framer_msfunc作为sfunc放置，但实际上它并不起作用。可以用一个函数代替，该函数接受相同的参数，但实际上只是在内部调用了array_append，因此才会真正起作用。

以下是带有更多输入值的示例。

帧大小应至少为窗口大小。如果尺寸较小，则不起作用。

select
    n,
    v,
    framer(v, 5) over (order by v rows between 2 preceding and 2 following) arr
from (values (1, 3200), (2, 2400), (3, 1600), (4, 2900), (5, 8200), (6, 2333), (7, 1500)) as v (n, v)
order by v
;
 n |  v   |            arr
---+------+----------------------------
 7 | 1500 | {NULL,NULL,1500,1600,2333}
 3 | 1600 | {NULL,1500,1600,2333,2400}
 6 | 2333 | {1500,1600,2333,2400,2900}
 2 | 2400 | {1600,2333,2400,2900,3200}
 4 | 2900 | {2333,2400,2900,3200,8200}
 1 | 3200 | {2400,2900,3200,8200,NULL}
 5 | 8200 | {2900,3200,8200,NULL,NULL}
(7 rows)

如果能从窗口大小推断出大小就好了，但我找不到是否可以这样做。