CodeGym /课程 /SQL SELF /数组复杂查询示例：聚合、过滤、排序

数组复杂查询示例：聚合、过滤、排序

SQL SELF

第 36 级 , 课程 3

可用

今天咱们来聊聊怎么在 SQL 查询里把数组用到极致：把值聚合成组、按内容过滤、甚至直接在数组里排序。这不是理论课，这些技巧在报表、分析、个性化还有各种实际场景里都超常见。只要你理解了原理，其实很简单——我们现在就来搞懂它。

数组数据聚合

数组的威力，尤其体现在你需要分组数据的时候。你不用拿到一堆行——而是把需要的值都聚成一个整齐的数组。这样分析起来更方便，结果也更紧凑，很多时候还能省掉多余的子查询。来看看实际怎么搞。

例子 1：用 `array_agg()` 把数据分组进数组

当你想把一组行里的值聚成一个数组时，array_agg() 就是你的好帮手。这绝对是数组聚合里最有用的函数之一。

-- 我们有个 students 表，字段有 id、name 和 course
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    course VARCHAR(100)
);

-- 插入几行数据
INSERT INTO students (name, course) VALUES
('爱丽丝', '数学'),
('鲍勃', '数学'),
('查理', '物理'),
('戴夫', '物理'),
('艾玛', '数学');

-- 按课程把学生分组进数组
SELECT course, array_agg(name) AS students
FROM students
GROUP BY course;

结果：

course	students
数学	{爱丽丝, 鲍勃, 艾玛}
物理	{查理, 戴夫}

把值聚成数组很方便，比如你想把数据传成 JSON 格式，后面解析起来也简单。

例子 2：创建嵌套数组

那如果我们还有另一个表，想把两个表的数据都聚成数组呢？比如有个 courses 表，记录老师信息。

-- 创建老师表
CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    teacher VARCHAR(100)
);

-- 插入数据
INSERT INTO courses (name, teacher) VALUES
('数学', '明教授'),
('物理', '彼得森教授');

-- 嵌套查询，生成数组
SELECT
    c.name AS course_name,
    array_agg(s.name) AS students,
    c.teacher
FROM
    courses c
LEFT JOIN
    students s
ON
    c.name = s.course
GROUP BY
    c.name, c.teacher;

结果：

course_name	students	teacher
数学	{爱丽丝, 鲍勃, 艾玛}	明教授
物理	{查理, 戴夫}	彼得森教授

现在我们有了一个很方便的表，能看到课程、老师和学生，学生是数组格式。

数组数据过滤

数组本身就很强大，但真正的魔法是你能基于数组来过滤数据。比如只选兴趣列表里有某个词的用户？或者订单里每个价格都超过某个阈值？这些都能直接在 SQL 里搞定——不用在应用层写额外逻辑。

例子 1：按数组元素过滤行

比如我们想找出数组里包含某个值的行，比如找报名了数学课的学生。

-- 用 `ANY` 过滤报名了这些课程的学生
SELECT *
FROM students
WHERE course = ANY(ARRAY['数学', '物理']);

这里 ANY 让你指定一个值数组，查询会返回 course 至少等于数组里一个值的行。

例子 2：检查数组交集

假设我们有个 student_interests 表，学生兴趣是数组。我们想找兴趣和我们目标有交集的学生。

-- 创建学生兴趣表
CREATE TABLE student_interests (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    interests TEXT[]
);

-- 插入数据
INSERT INTO student_interests (name, interests) VALUES
('爱丽丝', ARRAY['编程', '音乐']),
('鲍勃', ARRAY['运动', '编程']),
('查理', ARRAY['阅读', '摄影']),
('艾玛', ARRAY['音乐', '运动']);

-- 查找对编程或音乐感兴趣的学生
SELECT *
FROM student_interests
WHERE interests && ARRAY['编程', '音乐'];

操作符 && 检查两个数组有没有交集。只要左边数组有一个元素和右边数组一样，这行就会被选中。

结果：

id	name	interests
1	爱丽丝	{编程, 音乐}
2	鲍勃	{运动, 编程}
4	艾玛	{音乐, 运动}

数组排序

有时候数组里的顺序很重要——比如你把不同行聚成数组，或者想让数据显示更好看。PostgreSQL 允许你直接在查询里排序数组元素，不用再额外处理。

例子 1：数组内部排序

有时候你想把数组里的元素排序。比如我们把学生兴趣数组按字母排序。

-- 用 `array_sort()` 排序数组元素
SELECT 
    name,
    array_sort(interests) AS sorted_interests
FROM 
    student_interests;

结果：

name	sorted_interests
爱丽丝	{音乐, 编程}
鲍勃	{编程, 运动}
查理	{摄影, 阅读}
艾玛	{音乐, 运动}

例子 2：按数组长度排序行

假设我们想按兴趣数量给学生排序——从最有爱好到最“无聊”。

-- 按数组长度排序行
SELECT 
    name, 
    interests, 
    array_length(interests, 1) AS interests_count
FROM 
    student_interests
ORDER BY 
    interests_count DESC;

结果：