pgsql array_agg

什么是 array_agg

array_agg 是 PostgreSQL 中的一个聚合函数，用于将一组值转换为数组。

通常情况下，我们使用聚合函数将一组行中的某个字段的值进行汇总计算，如使用 sum 函数计算一组数值的总和。而 array_agg 函数则是将一组行中的某个字段的值放入一个数组中。

array_agg的语法

array_agg 的语法如下：

array_agg (expression)

其中，expression 是一个表达式，用于指定需要放入数组中的字段。

array_agg的返回类型

array_agg 返回一个数组，其元素类型为被汇总字段的类型。

array_agg的使用示例

接下来，我们通过一些示例来了解 array_agg 的使用方法。

假设我们有一个名为 employees 的表，其中包含员工的姓名和所属部门的信息。我们想要获取每个部门中的所有员工姓名，并将其放入一个数组中。

首先，我们创建 employees 表并插入一些示例数据：

CREATE TABLE employees (
   id SERIAL PRIMARY KEY,
   name VARCHAR(100) NOT NULL,
   department VARCHAR(100) NOT NULL
);

INSERT INTO employees (name, department) VALUES ('Alice', 'HR');
INSERT INTO employees (name, department) VALUES ('Bob', 'IT');
INSERT INTO employees (name, department) VALUES ('Charlie', 'IT');
INSERT INTO employees (name, department) VALUES ('David', 'HR');

接下来，我们使用 array_agg 函数来实现我们的需求：

SELECT department, array_agg(name) AS employees
FROM employees
GROUP BY department;

运行上述查询语句后，我们将获得以下结果：

 department |         employees
------------+-------------------------------
 IT         | {Bob,Charlie}
 HR         | {Alice,David}
(2 rows)

可以看到，我们获得了每个部门中的所有员工姓名，并将其放入了一个数组中。

array_agg 的更多用法

除了简单地将一组行中的某个字段放入数组中，array_agg 函数还可以用于更复杂的汇总需求。

汇总不同数据类型的字段

array_agg 函数不仅仅支持一般的数据类型，还可以用来汇总不同数据类型的字段。

CREATE TABLE products (
   id SERIAL PRIMARY KEY,
   name VARCHAR(100) NOT NULL,
   price NUMERIC(8,2) NOT NULL
);

INSERT INTO products (name, price) VALUES ('Apple', 3.99);
INSERT INTO products (name, price) VALUES ('Banana', 1.99);
INSERT INTO products (name, price) VALUES ('Cherry', 5.99);

假设我们有一个名为 orders 的表，其中包含了客户的订单信息，包括商品名称和订单数量。我们想要获取每个客户的所有订单以及对应的商品的总价，并将其放入一个数组中。

我们可以使用 array_agg 函数来实现这个需求：

SELECT customer_name, array_agg(product_name || ' ($' || (order_quantity * price)::varchar || ')') AS orders
FROM orders
JOIN products ON orders.product_id = products.id
GROUP BY customer_name;

运行上述查询语句后，我们将获得以下结果：

 customer_name |                               orders
---------------+-------------------------------------------------------------------
 Alice         | {Purple Pen (29.97),Blue Notebook (8.97)}
 Bob           | {Red Pen (2.99),Green Notebook (4.99)}
(2 rows)

这里，我们将每个客户的所有订单以及对应的商品的总价放入了一个数组中，并使用字符串拼接的方式将商品名称和价格合并起来。

处理 NULL 值

当某个字段的值为 NULL 时，使用 array_agg 函数会将其忽略，最终返回的数组中不会包含 NULL 值。

SELECT department, array_agg(name) AS employees
FROM employees
WHERE department = 'HR' OR department IS NULL
GROUP BY department;

运行上述查询语句后，我们将获得以下结果：

 department | employees
------------+------------
 HR         | {Alice,David}
(1 row)

注意，这里我们将名为 HR 的部门和名为 NULL 的部门的员工放入了同一个数组中，而 NULL 值被忽略了。

按照特定顺序汇总

默认情况下，array_agg 函数会按照输入顺序将值放入数组中。如果我们想要按照特定的顺序汇总，可以使用 ORDER BY 子句。

SELECT department, array_agg(name ORDER BY name DESC) AS employees
FROM employees
GROUP BY department;

运行上述查询语句后，我们将获得以下结果：

 department |        employees
------------+---------------------------
 HR         | {David,Alice}
 IT         | {Charlie,Bob}
(2 rows)

这里我们按照姓名的降序将值放入数组中。

总结

array_agg 函数是 PostgreSQL 中一个非常有用的聚合函数，可以将一组值转换为数组。它的用法非常灵活，可以用于多种汇总需求，并提供了许多功能和选项来满足不同场景下的要求。通过合理运用 array_agg 函数，我们可以更便捷地进行数据操作和计算分析。