Menu
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:
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 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.
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.
683
Fortunately, PostgreSQL is really flexible and allows end users to create your own aggregation functions, which can help to move your business logic to PostgreSQL. The “CREATE AGGREGATE” command is there to create all kinds of aggregations.
For each group, you can apply an aggregate function e.g., SUM() to calculate the sum of items or COUNT() to get the number of items in the groups. In this syntax: First, select the columns that you want to group e.g., column1 and column2 , and column that you want to apply an aggregate function ( column3 ).
To define a new aggregate function, one selects a data type for the state value, an initial value for the state, and a state transition function. The state transition function takes the previous state value and the aggregate's input value(s) for the current row, and returns a new state value.
For aggregation purposes, there are the SQL aggregate functions. And for multi-level aggregation, you'd use (at least) two aggregate functions at the same time. If you're interested in quality reporting, you'll need much more than SQL's aggregate functions. However, they're certainly the basis of good reporting.
See this: How to create multi-column aggregates, which is available since PostgreSQL 8.2
As for multiple state variables, as Jack said, you can use an array as the state variable.
177
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
