陣列進階查詢範例：聚合、過濾、排序

今天我們要來搞懂，怎麼在查詢裡把陣列用到極致：把值分組、根據內容過濾，甚至直接在陣列裡排序。這不是紙上談兵，這些技巧在報表、analytics、個人化、還有一堆實戰場景都超常見。只要抓到訣竅，其實很簡單——我們現在就來拆解給你看。

用陣列做資料聚合

陣列的威力，最明顯就是在你要分組資料的時候。你不用拿到一堆 row——直接把需要的值收進一個乾淨的陣列裡。這樣分析起來超方便，結果也更精簡，還常常可以省掉多餘的子查詢。來看看實際怎麼做。

範例 1：用 array_agg() 把資料分組進陣列

如果你想把多個 row 的值收進同一組的陣列，array_agg() 就是你的好朋友。這大概是處理陣列聚合時最實用的 function 了。

-- 我們有一個 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;

結果：

把值分組進陣列很方便，像是你想把資料丟成 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;

結果：

現在我們有一個很直觀的表格，直接看到課程、老師跟學生，全部都用陣列包起來。

用陣列過濾資料

陣列本身就很強大，但真正的魔法，是你學會怎麼根據陣列內容來過濾資料。想找出興趣清單裡有某個關鍵字的用戶？或是每個訂單裡的價格都超過某個門檻？這些都能直接在 SQL 裡搞定——不用在應用程式端多寫邏輯。

範例 1：根據陣列元素過濾 row

假設我們想找出所有陣列裡包含特定值的 row，比如找出有選修數學課的學生。

-- 用 `ANY` 過濾有選修課程的學生
SELECT *
FROM students
WHERE course = ANY(ARRAY['數學', '物理']);

這裡 ANY 讓你指定一個值的陣列，查詢會回傳那些 course 有符合陣列裡任一個值的 row。

範例 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['程式設計', '音樂'];

運算子 && 會檢查兩個陣列有沒有交集。只要左邊陣列有任一元素跟右邊陣列一樣，這 row 就會被選出來。

結果：

陣列排序

有時候你會在意陣列裡元素的順序——尤其是你從不同 row 收集資料進陣列，或是要準備資料給前端顯示。PostgreSQL 讓你直接在查詢裡排序陣列元素，完全不用額外處理。

範例 1：排序陣列裡的值

有時候你想把陣列元素排序。比如我們來把學生的興趣陣列按字母順序排好。

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

結果：

範例 2：依陣列長度排序 row

現在假設我們想依學生的興趣數量排序——從最有興趣到最「無聊」的。

-- 依陣列長度排序 row
SELECT 
    name, 
    interests, 
    array_length(interests, 1) AS interests_count
FROM 
    student_interests
ORDER BY 
    interests_count DESC;

結果：

雖然這個例子裡大家的興趣數都一樣，不過這種查詢在大表裡就很有用了，可以隨便改來玩。