PostgreSQL聚合有多个参数 [英] PostgreSQL Aggregates with Multiple Parameters

问题描述

我一直试图在PostgreSQL（8.4或9.1）中创建接受一个或多个选项参数的聚合。

I've been trying to wrap my head around creating aggregates in PostgreSQL (either 8.4 or 9.1) that accept one or more option parameters.

一个例子是创建 PL / R 扩展以计算第p个分位数，其中 0< = p< = 1 。看起来像 quantile（x，p），并作为查询的一部分：

An example would be creating a PL/R extension to compute the p-th quantile, with 0 <= p <= 1. This would look something like quantile(x,p), and as part of a query:

select category,quantile(x,0.25)
from TABLE
group by category
order by category;

在 TABLE（category：text，x：float）。

建议？

推荐答案

希望此示例将帮帮我。您需要一个接受（累加器，聚合参数）并返回新累加器值的函数。试试看下面的代码，它应该使您感觉如何组合。

Hopefully this example will help. You need a function that takes (accumulator, aggregate-arguments) and returns the new accumulator value. Play around with the code below and that should give you a feel for how it all fits together.

BEGIN;

CREATE FUNCTION sum_product_fn(int,int,int) RETURNS int AS $$
    SELECT $1 + ($2 * $3);
$$ LANGUAGE SQL;           

CREATE AGGREGATE sum_product(int, int) (
    sfunc = sum_product_fn,
    stype = int, 
    initcond = 0
);

SELECT 
    sum(i) AS one,     
    sum_product(i, 2) AS double,
    sum_product(i,3) AS triple
FROM generate_series(1,3) i;

ROLLBACK;      

应该会给你这样的东西：

That should give you something like:

 one | double | triple 
-----+--------+--------
   6 |     12 |     18

这篇关于PostgreSQL聚合有多个参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！