pgsql array_agg|极客教程

pgsql array_agg

1. 介绍

在 PostgreSQL 中，array_agg 是一个非常有用的聚合函数，它用于将一组值聚合为一个数组。这个函数在处理大量数据时非常有用，尤其是在需要将多个值合并为一个数组时。

在本文中，我们将详细介绍 array_agg 函数的用法、语法和示例。

2. 语法

array_agg 函数的语法如下所示：

array_agg(expression)
array_agg(DISTINCT expression)
array_agg(expression ORDER BY sort_expression)
array_agg(DISTINCT expression ORDER BY sort_expression)

参数解释：

expression：要聚合为数组的表达式。
DISTINCT：可选参数，用于指定是否要去除重复的值。
ORDER BY sort_expression：可选参数，用于指定排序规则。

3. 示例

为了更好地理解 array_agg 函数的用法，我们将给出一些示例。

3.1. 基本用法

假设我们有一个名为 “students” 的表，其中包含学生的信息，如下所示：

CREATE TABLE students (
   id SERIAL PRIMARY KEY,
   name VARCHAR(50),
   subjects VARCHAR[]
);

首先，我们插入一些数据到这个表中：

INSERT INTO students (name, subjects) VALUES
   ('John', ARRAY['Math', 'Physics', 'Chemistry']),
   ('Alice', ARRAY['Physics', 'Geography']),
   ('Bob', ARRAY['Math', 'Physics']),
   ('Kate', ARRAY['Chemistry', 'Physics']);

现在，我们想要获取每个学生学习的课程列表。

使用 array_agg 函数，我们可以轻松实现这个目标：

SELECT name, array_agg(subjects) AS subjects_list
FROM students
GROUP BY name;

运行以上查询，将得到以下结果：

| name  | subjects_list                         |
|-------|---------------------------------------|
| Alice | {"Physics","Geography"}                | 
| Bob   | {"Math","Physics"}                     | 
| John  | {"Math","Physics","Chemistry"}         | 
| Kate  | {"Chemistry","Physics"}                |

3.2. 去重

如果我们想要在数组中去除重复的值，可以使用 DISTINCT 参数。

例如，以下查询将获取每个学生唯一的课程列表：

SELECT name, array_agg(DISTINCT subjects) AS unique_subjects_list
FROM students
GROUP BY name;

运行以上查询，将得到以下结果：

| name  | unique_subjects_list                 |
|-------|--------------------------------------|
| Alice | {"Physics","Geography"}               | 
| Bob   | {"Physics","Math"}                    | 
| John  | {"Physics","Math","Chemistry"}        | 
| Kate  | {"Physics","Chemistry"}               |

3.3. 排序

我们还可以使用 ORDER BY 子句对数组进行排序。

例如，以下查询将按字母顺序获取每个学生的课程列表：

SELECT name, array_agg(subjects ORDER BY subjects) AS sorted_subjects_list
FROM students
GROUP BY name;

运行以上查询，将得到以下结果：

| name  | sorted_subjects_list                          |
|-------|-----------------------------------------------|
| Alice | {"Geography","Physics"}                        | 
| Bob   | {"Math","Physics"}                             | 
| John  | {"Chemistry","Math","Physics"}                 | 
| Kate  | {"Chemistry","Physics"}                        |