PostgreSQL array_agg|极客教程

PostgreSQL array_agg

介绍

在 PostgreSQL 数据库中，array_agg 函数用于将一列值合并为一个数组。这对于在查询中聚合数据非常有用。array_agg 函数接受一个输入表达式，它将返回一个包含该列所有值的数组。

语法

array_agg 函数的基本语法如下：

array_agg(expression)

其中，expression 是要合并为数组的列或表达式。

示例

考虑一个包含学生和他们所修课程的表格。我们将使用这个表格来演示 array_agg 函数的用法。

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE student_courses (
    student_id INTEGER REFERENCES students(id),
    course_id INTEGER REFERENCES courses(id)
);

INSERT INTO students (name) VALUES ('Alice');
INSERT INTO students (name) VALUES ('Bob');
INSERT INTO students (name) VALUES ('Charlie');

INSERT INTO courses (name) VALUES ('Math');
INSERT INTO courses (name) VALUES ('Science');
INSERT INTO courses (name) VALUES ('History');

INSERT INTO student_courses (student_id, course_id) VALUES (1, 1);
INSERT INTO student_courses (student_id, course_id) VALUES (1, 2);
INSERT INTO student_courses (student_id, course_id) VALUES (2, 2);
INSERT INTO student_courses (student_id, course_id) VALUES (3, 1);
INSERT INTO student_courses (student_id, course_id) VALUES (3, 3);

现在我们有一个学生表格和一个课程表格，还有一个关联表格来存储学生和他们所修课程之间的关系。

我们可以使用 array_agg 函数来查询每个学生所修课程的列表。

SELECT students.name, array_agg(courses.name) AS courses
FROM students
JOIN student_courses ON students.id = student_courses.student_id
JOIN courses ON courses.id = student_courses.course_id
GROUP BY students.id;

这将返回一个结果集，其中包含每个学生的名称以及他们所修课程的列表。结果如下：

  name  |            courses
--------+------------------------------
 Alice  | {Math,Science}
 Bob    | {Science}
 Charlie| {Math,History}

在这个示例中，我们在 SELECT 语句中使用了 array_agg 函数，将课程名称合并为一个数组。GROUP BY 子句确保了每个学生只出现一次，并将他们的课程列表分组。

过滤 NULL 值

array_agg 函数默认会忽略 NULL 值，并将结果中的 NULL 值过滤掉。如果要包含 NULL 值，则需要使用 coalesce 函数将 NULL 值替换为一个非 NULL 值。

SELECT students.name, array_agg(coalesce(courses.name, '')) AS courses
FROM students
LEFT JOIN student_courses ON students.id = student_courses.student_id
LEFT JOIN courses ON courses.id = student_courses.course_id
GROUP BY students.id;

上面的查询使用了 LEFT JOIN 来包含所有学生，包括那些没有修课程的学生。coalesce 函数将 NULL 值替换为一个空字符串，以确保结果中的数组不会包含 NULL 值。

排序数组

默认情况下，array_agg 函数将按照输入顺序生成数组。如果需要按照特定的顺序对数组进行排序，可以使用 ORDER BY 子句。

SELECT students.name, array_agg(courses.name ORDER BY courses.name) AS courses
FROM students
JOIN student_courses ON students.id = student_courses.student_id
JOIN courses ON courses.id = student_courses.course_id
GROUP BY students.id;

这个示例中，我们使用 ORDER BY 子句按照课程名称对数组进行排序。