自定义汇总功能 [英] Custom aggregate function

问题描述

我试图理解聚合函数，并且需要帮助。

I am trying to understand aggregate functions and I need help.

因此，例如以下示例：

CREATE OR REPLACE FUNCTION array_median(timestamp[])
  RETURNS timestamp AS
$$
    SELECT CASE WHEN array_upper($1,1) = 0 THEN null ELSE asorted[ceiling(array_upper(asorted,1)/2.0)] END
    FROM (SELECT ARRAY(SELECT ($1)[n] FROM
generate_series(1, array_upper($1, 1)) AS n
    WHERE ($1)[n] IS NOT NULL
            ORDER BY ($1)[n]
) As asorted) As foo ;
$$
  LANGUAGE 'sql' IMMUTABLE;


CREATE AGGREGATE median(timestamp) (
  SFUNC=array_append,
  STYPE=timestamp[],
  FINALFUNC=array_median
)

我不了解需要在聚合函数本身中进入select语句的结构/逻辑。有人可以解释一下流程/逻辑是什么吗？

I am not understanding the structure/logic that needs to go into the select statement in the aggregate function itself. Can someone explain what the flow/logic is?

我正在写一个聚合，一个奇怪的事实，即返回始终是它看到的第一个字符串。

I am writing an aggregate, a strange one, that the return is always the first string it ever sees.

推荐答案

您正在显示中值计算，但是想要看到的第一个文本值吗？

You're showing a median calculation, but want the first text value you see?

以下是操作方法。假设您想要第一个非空值。如果没有，则需要跟踪是否已经有一个值。

Below is how to do that. Assuming you want the first non-null value, that is. If not, you'll need to keep track of if you've got a value already or not.

累加器函数被编写为plpgsql和sql-plpgsql一个使您可以使用变量名并对其进行调试。它仅对先前的累加值和新值使用COALESCE，并返回第一个非空值。所以-一旦累加器中有非null值，其他所有内容都会被忽略。

The accumulator function is written as plpgsql and sql - the plpgsql one lets you use variable names and debug it too. It simply uses COALESCE against the previous accumulated value and the new value and returns the first non-null. So - as soon as you have a non-null in the accumulator everything else gets ignored.

您可能还想考虑 first_value窗口函数用于此类如果您使用的是现代（8.4+）版本的PostgreSQL，则该问题。

You may also want to consider the "first_value" window function for this sort of thing if you're on a modern (8.4+) version of PostgreSQL.

http://www.postgresql.org/docs/9.1/static/functions-window.html

HTH

BEGIN;

CREATE FUNCTION remember_first(acc text, newval text) RETURNS text AS $$
BEGIN
    RAISE NOTICE '% vs % = %', acc, newval, COALESCE(acc, newval);
    RETURN COALESCE(acc, newval);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE FUNCTION remember_first_sql(text,text) RETURNS text AS $$
    SELECT COALESCE($1, $2);
$$ LANGUAGE SQL IMMUTABLE;

-- No "initcond" means we start out with null
--      
CREATE AGGREGATE first(text) (
    sfunc = remember_first,
    stype = text
);

CREATE TEMP TABLE tt (t text);
INSERT INTO tt VALUES ('abc'),('def'),('ghi');

SELECT first(t) FROM tt;

ROLLBACK;

这篇关于自定义汇总功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！