Menu

string_agg và array_agg trong PostgreSQL (GROUP_CONCAT)

string_agg nối các giá trị của một nhóm thành một chuỗi và array_agg gom chúng thành một mảng. Gồm sắp xếp, DISTINCT, FILTER, cách xử lý NULL và các lỗi hay gặp.

Trang này có trình soạn thảo chạy trực tiếp. Sửa, chạy và xem đầu ra ngay lập tức.

string_agg(expression, separator) nối các giá trị của mỗi nhóm thành một chuỗi, còn array_agg(expression) gom chúng thành một mảng. Đặt ORDER BY bên trong lời gọi để điều khiển thứ tự: string_agg(name, ', ' ORDER BY name).

IT trả về Bo, Kim, Li và {6100,4800,3900}; Sales trả về Ana, Raj và {5000,4200}. Cả hai đều là hàm tổng hợp thông thường như count hay sum: chúng tính theo từng nhóm khi có GROUP BY, và trên cả bảng khi không có.

Sắp xếp các giá trị

Không có ORDER BY bên trong hàm tổng hợp, các giá trị xuất hiện theo thứ tự các hàng đi tới, thứ tự này có thể đổi giữa các lần chạy. Một ORDER BY ở cuối truy vấn sắp xếp các hàng kết quả, không phải các giá trị bên trong mỗi chuỗi. ORDER BY bên trong đứng sau đối số cuối cùng, không có dấu phẩy:

string_agg(name, ', ' ORDER BY hired_on DESC)
array_agg(name ORDER BY score DESC, name)

Bỏ trùng lặp với DISTINCT

Ada đã mua bút hai lần, nhưng DISTINCT chỉ liệt kê một lần: book, pen. Với DISTINCT, ORDER BY chỉ được dùng chính giá trị được tổng hợp. Sắp xếp theo một cột khác sẽ báo lỗi "in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list".

Cách NULL và nhóm rỗng hoạt động

string_agg bỏ qua các giá trị NULL và trả về Bo, Li. array_agg giữ lại chúng: {Bo,Li,NULL}. Để bỏ NULL khỏi array_agg, hãy thêm FILTER (WHERE name IS NOT NULL).

Trên tập không có hàng nào, cả hai trả về NULL, không phải chuỗi rỗng hay mảng rỗng. Hãy bọc chúng lại khi bên gọi cần một giá trị: COALESCE(string_agg(...), '') hoặc COALESCE(array_agg(...), '{}').

Chỉ tổng hợp một số hàng với FILTER

FILTER (WHERE ...) giới hạn một hàm tổng hợp vào những hàng khớp, trong khi phần còn lại của truy vấn vẫn thấy tất cả:

IT có 3 người và liệt kê Bo, Li; Sales có 2 người và liệt kê Ana.

Gắn một danh sách vào mỗi hàng của bảng khác

Để hiển thị mỗi khách hàng cùng danh sách sản phẩm của họ, hãy tổng hợp trong một truy vấn con, khi đó truy vấn bên ngoài không cần GROUP BY:

SELECT c.id, c.name, c.email,
       (SELECT string_agg(o.product, ', ' ORDER BY o.product)
        FROM orders o
        WHERE o.customer_id = c.id) AS products
FROM customers c;

Những khách hàng không có đơn hàng vẫn nằm trong kết quả, với products là NULL. Một join với GROUP BY chỉ giữ họ lại nếu đó là LEFT JOIN.

Lỗi hay gặp và các biến thể

  • Số: string_agg nhận text, nên string_agg(id, ',') báo lỗi "function string_agg(integer, unknown) does not exist". Hãy ép kiểu: string_agg(id::text, ',').
  • N giá trị đầu mỗi nhóm: cắt mảng đã sắp xếp, (array_agg(name ORDER BY salary DESC))[1:2].
  • Đầu ra JSON: json_agg và jsonb_agg gom các hàng thành một mảng JSON, còn jsonb_object_agg(key, value) dựng một đối tượng; xem JSONB.
  • Đã có sẵn mảng: array_to_string(arr, ', ') nối một mảng có sẵn, và unnest biến nó trở lại thành các hàng (mảng).
  • Phiên bản window: cả hai cũng chạy được như window function, ví dụ string_agg(name, ', ') OVER (PARTITION BY dept) để hiển thị toàn bộ danh sách trên mỗi hàng.

Câu hỏi thường gặp

Hàm nào trong PostgreSQL tương đương GROUP_CONCAT?

string_agg(column, ','). Khác với GROUP_CONCAT của MySQL, dấu phân cách là bắt buộc, và thứ tự được ghi bên trong lời gọi: string_agg(name, ', ' ORDER BY name).

Làm sao dùng string_agg với số?

Ép giá trị sang text: string_agg(id::text, ','). string_agg chỉ nhận text (hoặc bytea), nên một đối số kiểu integer sẽ báo lỗi "function string_agg(integer, unknown) does not exist".

string_agg có giới hạn độ dài không?

Không có giới hạn riêng; kết quả là một giá trị text bình thường, chứa được tới 1 GB. GROUP_CONCAT của MySQL cắt ở group_concat_max_len, còn PostgreSQL không cắt.

Làm sao lấy N giá trị đầu tiên với array_agg?

Sắp xếp bên trong hàm tổng hợp rồi cắt kết quả: (array_agg(name ORDER BY score DESC))[1:3] trả về ba tên có điểm cao nhất của mỗi nhóm.

Hình minh họa các ngôn ngữ lập trình của Coddy

Học lập trình cùng Coddy

Bắt đầu