Postgresql 中文操作指南

F.19. intagg — integer aggregator and enumerator #

intagg 模块提供一个整数聚合器和一个枚举器。intagg 现已过时，因为有内置函数提供其功能的超集。然而，模组仍然作为内置函数的兼容性包装而提供。

The intagg module provides an integer aggregator and an enumerator. intagg is now obsolete, because there are built-in functions that provide a superset of its capabilities. However, the module is still provided as a compatibility wrapper around the built-in functions.

F.19.1. Functions #

聚合器是一个聚合函数 int_array_aggregate(integer)，产生一个包含其被喂入的每个整数的整数数组。这是 array_agg 的一个包装，array_agg 对任何数组类型执行相同操作。

The aggregator is an aggregate function int_array_aggregate(integer) that produces an integer array containing exactly the integers it is fed. This is a wrapper around array_agg, which does the same thing for any array type.

枚举器是一个返回 setof integer 的函数 int_array_enum(integer[])。它本质上是聚合器的反向操作：给定一个整数数组，将其展开到一组行中。这是一个包装器，适用于 unnest，它对任何数组类型都会执行相同操作。

The enumerator is a function int_array_enum(integer[]) that returns setof integer. It is essentially the reverse operation of the aggregator: given an array of integers, expand it into a set of rows. This is a wrapper around unnest, which does the same thing for any array type.

F.19.2. Sample Uses #

许多数据库系统都具有多对一表的概念。这种表通常位于两个索引表之间，例如：

Many database systems have the notion of a one to many table. Such a table usually sits between two indexed tables, for example:

CREATE TABLE left (id INT PRIMARY KEY, ...);
CREATE TABLE right (id INT PRIMARY KEY, ...);
CREATE TABLE one_to_many(left INT REFERENCES left, right INT REFERENCES right);

通常会像这样使用：

It is typically used like this:

SELECT right.* from right JOIN one_to_many ON (right.id = one_to_many.right)
  WHERE one_to_many.left = item;

这将为左手表中的条目返回右手表中的所有条目。这是 SQL 中非常常见的构造函数。

This will return all the items in the right hand table for an entry in the left hand table. This is a very common construct in SQL.

现在，当 one_to_many 表中的条目数量非常多时，这种方法可能会很笨拙。通常，这样的连接会导致索引扫描并且必须为表中的每个右手条目获取一个左手条目。如果您有一个非常动态的系统，那么您无能为力。但是，如果您有一些相当静态的数据，则可以使用聚合器创建一个摘要表。

Now, this methodology can be cumbersome with a very large number of entries in the one_to_many table. Often, a join like this would result in an index scan and a fetch for each right hand entry in the table for a particular left hand entry. If you have a very dynamic system, there is not much you can do. However, if you have some data which is fairly static, you can create a summary table with the aggregator.

CREATE TABLE summary AS
  SELECT left, int_array_aggregate(right) AS right
  FROM one_to_many
  GROUP BY left;

这将创建一个表，其中每个左手项有一行，而右手项有一个数组。如果不用某种方法使用数组，那么这样做非常无用；这就是数组枚举器的原因。您可以执行

This will create a table with one row per left item, and an array of right items. Now this is pretty useless without some way of using the array; that’s why there is an array enumerator. You can do

SELECT left, int_array_enum(right) FROM summary WHERE left = item;

使用 int_array_enum 的上述查询会产生与

The above query using int_array_enum produces the same results as

SELECT left, right FROM one_to_many WHERE left = item;

相同的结果。不同之处在于，针对摘要表的查询只必须获取表中的一行，而针对 one_to_many 的直接查询必须为每个条目进行索引扫描并获取一行。

The difference is that the query against the summary table has to get only one row from the table, whereas the direct query against one_to_many must index scan and fetch a row for each entry.

在一个系统上，一个 EXPLAIN 显示出一个成本为 8488 的查询已降低到成本 329。原始查询是一个涉及 one_to_many 表的连接，它已被替换为：

On one system, an EXPLAIN showed a query with a cost of 8488 was reduced to a cost of 329. The original query was a join involving the one_to_many table, which was replaced by:

SELECT right, count(right) FROM
  ( SELECT left, int_array_enum(right) AS right
    FROM summary JOIN (SELECT left FROM left_table WHERE left = item) AS lefts
         ON (summary.left = lefts.left)
  ) AS list
  GROUP BY right
  ORDER BY count DESC;