自定义PostgreSQL汇总的循环平均值 [英] Custom PostgreSQL aggregate for circular average
问题描述
我正在尝试在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汇总的循环平均值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!