I am trying to create user-defined aggregate functions that use multiple columns as their input, and output a single column.
我正在尝试创建使用多列作为输入的用户定义聚合函数,并输出单个列。
For example, to calculate a weighted average, we might use two columns called num_samples and quantity, with a query like this:
例如,要计算加权平均值,我们可能会使用名为num_samples和quantity的两列,并使用如下查询:
SELECT sum(num_samples * quantity) / sum(num_samples) AS weighted_avg FROM table;
However, the functions I want to define are quite complex (e.g. weighted standard deviation) and are used many times. I'd like to be define my own aggregate functions so that they can be easily used in select queries. For example, if I wanted to find the weighted average and total sum, I'd use a query like this:
但是,我想要定义的函数非常复杂(例如加权标准偏差)并且被多次使用。我想定义自己的聚合函数,以便在select查询中轻松使用它们。例如,如果我想找到加权平均值和总和,我会使用如下查询:
SELECT weighted_avg(num_samples, quantity), sum(quantity)
However, from the documentation it looks like user-defined aggregates are only allowed a single state variable, but this example would require two state variables: one for the running total of quantity and one for the running total of num_samples.
但是,从文档中看,用户定义的聚合看起来只允许一个状态变量,但是这个例子需要两个状态变量:一个用于运行总数量,一个用于运行总数num_samples。
Is it possible to achieve what I want with user-defined aggregate functions, or is there a better way? I'm using PostgreSQL 8.3.
是否有可能通过用户定义的聚合函数实现我想要的,或者有更好的方法吗?我正在使用PostgreSQL 8.3。
2 个解决方案
#1
2
See this: How to create multi-column aggregates, which is available since PostgreSQL 8.2
请参阅:如何创建自列PostgreSQL 8.2以来可用的多列聚合
As for multiple state variables, as Jack said, you can use an array as the state variable.
至于多个状态变量,正如杰克所说,你可以使用数组作为状态变量。
#2
0
From your link: "avg (average) is a more complex example of an aggregate. It requires two pieces of running state: the sum of the inputs and the count of the number of inputs. The final result is obtained by dividing these quantities. Average is typically implemented by using a two-element array as the state value."
从您的链接:“avg(平均值)是一个更复杂的聚合示例。它需要两个运行状态:输入的总和和输入数量的计数。最终结果是通过除以这些数量得到的。平均值通常通过使用双元素数组作为状态值来实现。“
How about doing something like that?
做那样的事情怎么样?
#1
2
See this: How to create multi-column aggregates, which is available since PostgreSQL 8.2
请参阅:如何创建自列PostgreSQL 8.2以来可用的多列聚合
As for multiple state variables, as Jack said, you can use an array as the state variable.
至于多个状态变量,正如杰克所说,你可以使用数组作为状态变量。
#2
0
From your link: "avg (average) is a more complex example of an aggregate. It requires two pieces of running state: the sum of the inputs and the count of the number of inputs. The final result is obtained by dividing these quantities. Average is typically implemented by using a two-element array as the state value."
从您的链接:“avg(平均值)是一个更复杂的聚合示例。它需要两个运行状态:输入的总和和输入数量的计数。最终结果是通过除以这些数量得到的。平均值通常通过使用双元素数组作为状态值来实现。“
How about doing something like that?
做那样的事情怎么样?