PostgreSQL 教程: 创建自定义聚合函数

九月 20, 2024

摘要：在本教程中，您将学习如何在 PostgreSQL 中创建自己的聚合函数。

聚合函数的基础知识

这些不错的函数可以支持您聚合一个表的多行。假设您有一个名为 employees 的表，有一个列 salary，并且您想要获得所有 salary 的总和。您可以手动求和（当然只是玩笑），也可以使用标准的聚合函数（如 SUM），该函数对找到的每个数据集执行运算，从而计算出总和。

要使用标准化的聚合函数，您可以这样做：

SELECT SUM(salary) AS total_sum FROM employees;

那么，这个 sum 运算需要做些什么呢？聚合函数的最大优点是，它们可以组合多行，而不是最大组合列。在我们的示例中，SUM 操作对所有 salary 求和，这些 salary 是查询行的一部分。除了聚合函数之外，没有其他方法可以归入行，除非您在应用程序方面处理一些事情。

PostgreSQL 的一大优势是您可以创建自定义的聚合函数，从而满足您想要的组合计算。

快速开始

在阅读本段之前，请预先查看有关 CREATE AGGREGATE 的官方文档。基于此，这里会尝试解释下关键要点，并在最重要的步骤中为您提供帮助。

CREATE AGGREGATE 的概要如下：

CREATE AGGREGATE name ( input_data_type [ , ... ] ) (
    SFUNC = sfunc,
    STYPE = state_data_type
    [ , FINALFUNC = ffunc ]
    [ , INITCOND = initial_condition ]
    [ , SORTOP = sort_operator ]
)

想象一下 AGGREGATE 是一个流程，它有一个定义的状态（STYPE 类型，稍后会详细介绍），该状态在遍历所选的数据集期间会发生变化。具体将更改的内容，是通过函数 SFUNC 定义的。最终函数 FINALFUNC 将在聚合后应用，其返回值将作为聚合的结果返回 - FINALFUNC 的默认行为是返回最后一个已知状态。

为了补充官方文档，这里为您查找正确值提供一些提示。

STYPE：数据类型，聚合器以该类型来记录其当前状态。此状态（类型 STYPE）将作为第一个参数，传递给函数 SFUNC。
SFUNC：函数的名称，需要 (state_data_type, input_data_type(s)) 作为参数，并返回 STYPE 类型的值。
```
SFUNC(state<STYPE>, next input_data_type(s)) ---> new state<STYPE>
```
要获取所有可用函数的列表，请使用 psql 连接，键入 \df <begin_of_expression>，并按 <Tab>（类似于 Unix 自动完成）。如果按下 <Enter>，将显示有关该函数的详细信息。

要列出可用的函数 (SFUNC)，可执行以下操作：
```
testdb=> \df ip4
ip4                       ip4_in
ip4_and                   ip4_le
ip4_cmp                   ip4_xor
[...]

testdb=> \df ip4_xor
 schema |  name   | result data type | argument data types
--------+---------+------------------+--------------------
 public | ip4_xor | ip4              | ip4, ip4
```
有关更多详细信息，您可以查看 PostgreSQL 系统表 pg_proc。
FINALFUNC：内部聚合器的最后一个状态（聚合后）与聚合返回值之间的连接器。可以对最后一个状态值做强制转换，还可以对其执行最终的操作。
```
FINALFUNC(state<STYPE>) ---> aggregator return value
```
INITCOND：内部状态的起始值。强烈建议您声明这一点。

示例

假设我们在创建自定义聚合函数时，有一个表，其中有许多包含了 IP 地址的数据集。一个简单的想法是，构建一个所有 IP 地址的哈希码，来对数据集进行校验，这可以通过对所有 IP 地址进行 XOR 来轻松完成。

最后，您可以定义用于“哈希” IPv4 地址的聚合器，如下所示：

01  CREATE AGGREGATE hash_ips ( ip4 ) (
02	SFUNC = ip4_xor,
03	STYPE = ip4,
04	INITCOND = '0.0.0.0'
05  )

描述：

聚合器称为 hash_ips（第 1 行），它需要一个 ip4 类型的参数（第 1 行，有关 ip4 的更多信息，请查看项目）。它的初始状态本身也是 ip4 类型（第 3 行），初始值为 IP 地址 0.0.0.0（第 4 行）。对于每个数据集，将其进行 XOR 运算（通过传递函数 ip4_xor，第 2 行），并将在迭代后返回（因为未声明 FINALFUNC）。

在创建后，您可以像其他聚合函数一样使用该聚合函数：

SELECT hash_ips(column_with_ip) FROM mytable;

有关更多示例，请查看官方示例。

了解更多

PostgreSQL 文档：CREATE AGGREGATE

PostgreSQL 教程：聚合函数

聚合函数的基础知识

快速开始

示例

了解更多

搜索

分类

标签