Menu

DISTINCT ON trong PostgreSQL: một hàng cho mỗi nhóm

SELECT DISTINCT ON (key) ... ORDER BY key, sort giữ lại hàng đầu tiên của mỗi nhóm, ví dụ đơn hàng mới nhất của từng khách hàng. Đây là cú pháp riêng của PostgreSQL.

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.

SELECT DISTINCT ON (expr) ... trả về một hàng cho mỗi giá trị khác nhau của expr: hàng đầu tiên của nhóm đó theo thứ tự ORDER BY. Sắp xếp theo khóa nhóm trước, rồi theo tiêu chí quyết định hàng nào là "đầu tiên". Với đơn hàng mới nhất của từng khách hàng, đó là ORDER BY customer, created_at DESC.

Kết quả có ba hàng: đơn 2 của asha, đơn 4 của ben và đơn 5 của chen, mỗi người là đơn gần nhất. DISTINCT ON là phần mở rộng của PostgreSQL. Các cơ sở dữ liệu khác cần cách viết bằng window function ở bên dưới.

ORDER BY quyết định giữ hàng nào

PostgreSQL sắp xếp các hàng, rồi giữ hàng đầu tiên mỗi khi giá trị DISTINCT ON thay đổi. Vì vậy:

  • ORDER BY phải bắt đầu bằng các biểu thức của DISTINCT ON, nếu không PostgreSQL báo lỗi "SELECT DISTINCT ON expressions must match initial ORDER BY expressions".
  • Nếu ORDER BY không có thêm cột nào sau các biểu thức đó, hàng được giữ lại là không đoán trước được và có thể đổi giữa các lần chạy.

Đổi created_at DESC thành total DESC và cùng truy vấn đó trả về đơn hàng lớn nhất của từng khách hàng:

Bây giờ hàng của asha là đơn 1 (40) và của ben là đơn 3 (60).

Chủ động xử lý trường hợp hòa

Khi hai hàng bằng nhau ở cột sắp xếp, hàng nào cũng có thể được chọn. Hãy thêm một khóa sắp xếp cuối cùng có giá trị duy nhất, như khóa chính, để lần nào kết quả cũng giống nhau:

Hàng 2 và hàng 3 đều có 95 điểm. id ở vị trí khóa cuối giúp hàng 2 được chọn.

Nhóm theo nhiều cột

Liệt kê nhiều biểu thức để lấy một hàng cho mỗi tổ hợp:

Mỗi cặp cảm biến và loại giữ lại lần đo mới nhất: s1 humidity 40, s1 temp 22, s2 temp 19.

Cách viết dùng được ở mọi nơi với ROW_NUMBER

ROW_NUMBER() đánh số các hàng bên trong mỗi phân vùng (partition). Giữ số 1 cho ra cùng kết quả với DISTINCT ON, và cách này cũng chạy trên MySQL 8, SQL Server và SQLite:

SELECT customer, id, total, created_at
FROM (
    SELECT o.*,
           ROW_NUMBER() OVER (PARTITION BY customer ORDER BY created_at DESC) AS rn
    FROM orders o
) ranked
WHERE rn = 1;

Dùng cách viết này khi bạn cần n hàng đầu của mỗi nhóm (rn <= 3) hoặc khi truy vấn phải chạy trên một cơ sở dữ liệu khác. Trang window function giải thích PARTITION BY.

Làm cho nó chạy nhanh

DISTINCT ON sắp xếp dữ liệu đầu vào, nên trên bảng lớn hãy cho nó một index khớp với ORDER BY:

CREATE INDEX orders_customer_latest ON orders (customer, created_at DESC);

Khi đó PostgreSQL có thể đọc các hàng theo thứ tự thay vì phải sắp xếp. Khi có ít nhóm và nhiều hàng mỗi nhóm, một join LATERAL lấy LIMIT 1 cho mỗi khách hàng từ cùng index đó thường đọc ít hàng hơn. Trang join giới thiệu LATERAL. Hãy so sánh cả hai bằng EXPLAIN ANALYZE trên dữ liệu của bạn.

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

DISTINCT và DISTINCT ON khác nhau thế nào?

DISTINCT loại bỏ những hàng giống hệt nhau ở mọi cột được chọn. DISTINCT ON (a) giữ một hàng cho mỗi giá trị của a, và các cột khác lấy từ hàng nào được sắp xếp đứng đầu trong nhóm đó.

DISTINCT ON có phải SQL chuẩn không?

Không. Đây là phần mở rộng của PostgreSQL, nên không chạy trên MySQL hay SQL Server. Cách viết dùng được ở mọi nơi là ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) trong một truy vấn con, rồi lọc lấy số thứ tự 1.

Vì sao lại gặp lỗi "SELECT DISTINCT ON expressions must match initial ORDER BY expressions"?

ORDER BY phải bắt đầu bằng đúng các biểu thức của DISTINCT ON, theo cùng thứ tự. Hãy viết DISTINCT ON (customer) ... ORDER BY customer, created_at DESC, chứ không chỉ ORDER BY created_at DESC.

DISTINCT ON có trả về 3 hàng đầu của mỗi nhóm được không?

Không, nó trả về đúng một hàng cho mỗi nhóm. Để lấy n hàng đầu, hãy đánh số các hàng bằng ROW_NUMBER() và giữ rn <= 3, hoặc dùng một truy vấn con LATERAL với LIMIT 3.

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