自定义PostgreSQL汇总的循环平均值

问题描述

我正在尝试在Postgres中实现一个自定义的聚合函数，该函数将平均方向以度为单位，即我想做到：

I'm trying to implement a custom aggregate function in Postgres which will average directions in degrees - i.e. I want to able to do:

SELECT circavg(direction) FROM sometable;

这可以使用以下公式完成：

This can be done using the formula:

xbar = atan2(sum(sin(xi), sum(cos(xi)))

我想我需要定义一个 sfunc ，它会指明一个方向，并将其正弦和余弦加到两个累加器中。函数然后使用atan2将这两个分量转换回一个方向。

I think I need to define an sfunc which will take a direction, and add the sine and cosine of that into two accumulators. The final function then converts the two components back into a direction using atan2.

我不知道如何定义 sfunc ，因此当前状态由两个组成，例如（float，float）。该文档在具体示例上有些短，因此

I can't work out how to define the sfunc so that the current state consists of two components e.g. (float, float). The documentation is a bit short on concrete examples, so any help is appreciated.

推荐答案

您可以在内部使用 ARRAY 类型。参数类型仍然可以可以是任何数值类型。用 float （= double precision ）进行演示：

You can make use of an ARRAY type internally. Argument type can still be any numeric type. Demonstrating with float (= double precision):

CREATE OR REPLACE FUNCTION f_circavg (float[], float)
  RETURNS float[] LANGUAGE sql STRICT AS
'SELECT ARRAY[$1[1] + sin($2), $1[2] + cos($2), 1]';

CREATE OR REPLACE FUNCTION f_circavg_final (float[])
  RETURNS float  LANGUAGE sql AS
'SELECT CASE WHEN $1[3] > 0 THEN atan2($1[1], $1[2]) END';

CREATE AGGREGATE circavg (float) (
   sfunc     = f_circavg
 , stype     = float[]
 , finalfunc = f_circavg_final
 , initcond  = '{0,0,0}'
);

转换函数 f_circavg（）被定义为 STRICT ，因此它将忽略输入 NULL 的行。它还设置了第三个数组元素，以标识具有一个或多个输入行的集合-否则 CASE 最终函数将返回 NULL 。

The transition function f_circavg() is defined STRICT, so it ignores rows with NULL input. It also sets a third array element to identify sets with one or more input rows - else the CASE the final function returns NULL.

测试临时表：

CREATE TEMP TABLE t (x float);
INSERT INTO t VALUES (2), (NULL), (3), (4), (5);

我把 NULL 值也测试 STRICT 魔术。调用：

I threw in a NULL value to also test the STRICT magic. Call:

SELECT circavg(x) FROM t;

       circavg
-------------------
 -2.78318530717959

交叉检查：

SELECT atan2(sum(sin(x)), sum(cos(x))) FROM t;

       atan2
-------------------
 -2.78318530717959

返回相同值。似乎可以工作。在具有更大表的测试中，具有常规聚合函数的最后一个表达式比自定义聚合快4倍。

Returns the same. Seems to work. In test with a bigger table the last expression with regular aggregate functions was 4x faster than the custom aggregate.

测试零个输入行/仅输入NULL：

Test for zero input rows / only NULL input:

SELECT circavg(x) FROM t WHERE false;     -- no input rows
SELECT circavg(x) FROM t WHERE x IS NULL; -- only NULL input

返回 NULL 两种情况。

这篇关于自定义PostgreSQL汇总的循环平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！