Menu

Window function trong PostgreSQL: OVER, PARTITION BY

Window function tính một giá trị cho mỗi hàng từ một nhóm các hàng liên quan mà không gộp chúng lại: xếp hạng, tổng lũy kế, trung bình trượt và so sánh với hàng trước.

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.

Window function tính một giá trị cho mỗi hàng từ một tập các hàng liên quan, gọi là cửa sổ (window) của nó, mà không gộp các hàng như GROUP BY. Viết nó dưới dạng function() OVER (PARTITION BY ... ORDER BY ...). PARTITION BY chia các hàng thành nhóm còn ORDER BY đặt thứ tự bên trong mỗi nhóm.

Mọi hàng đều ở lại trong kết quả và mang theo tổng của vùng: 1000 cho north, 500 cho south. Khi đó tỷ trọng của mỗi nhân viên bán hàng chỉ cần một biểu thức (ana chiếm 50% của north). Bất kỳ hàm tổng hợp nào (sum, count, avg, min, max) cũng trở thành window function khi bạn thêm OVER. Một OVER () rỗng biến toàn bộ kết quả thành một cửa sổ.

Đánh số hàng với ROW_NUMBER, RANK và DENSE_RANK

Ba hàm xếp hạng chỉ khác nhau khi các hàng bằng nhau ở giá trị ORDER BY:

ben và cy đều có 85. row_number vẫn cho họ 2 và 3; ai nhận số 2 là không xác định trừ khi ORDER BY phân định được. rank cho cả hai số 2 và nhảy sang 4 cho dee. dense_rank cho cả hai số 2 và tiếp tục với 3.

HàmKhi bằng nhauNhảy số sau khi bằng nhau
row_number()số khác nhaukhông
rank()cùng sốcó
dense_rank()cùng sốkhông

Lấy N hàng đầu mỗi nhóm

Window function được tính sau WHERE, nên bạn không lọc trực tiếp trên chúng được. Hãy đánh số các hàng trong một truy vấn con hoặc CTE, rồi lọc ở bên ngoài:

Kết quả là hai nhân viên bán hàng giỏi nhất của mỗi vùng. Dùng rank() thay cho row_number() để giữ mọi hàng đồng hạng nhì. Khi chỉ cần hàng đầu tiên của mỗi nhóm, DISTINCT ON ngắn gọn hơn.

Tính tổng lũy kế hoặc trung bình trượt

Khi có ORDER BY bên trong OVER, một hàm tổng hợp chạy trên một frame. Mặc định frame đi từ hàng đầu tiên của partition tới hàng hiện tại, cho ra tổng lũy kế. Một mệnh đề ROWS BETWEEN đặt frame khác, như hàng hiện tại và hai hàng trước nó:

Tổng lũy kế đạt 130 vào ngày 5, và trung bình ba ngày ở ngày 5 là (30 + 20 + 50) / 3 = 33,3.

Frame mặc định là RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Nó bao gồm mọi hàng bằng hàng hiện tại ở giá trị ORDER BY, nên hai hàng cùng ngày có cùng tổng lũy kế. Hãy viết ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW để cộng chúng lần lượt từng hàng.

So sánh với hàng trước: LAG và LEAD

lag(col) đọc giá trị từ hàng đứng trước hàng hiện tại theo thứ tự của cửa sổ, còn lead(col) từ hàng đứng sau. Cả hai trả về NULL khi vượt quá biên, trừ khi bạn truyền giá trị mặc định ở đối số thứ ba.

Ngày 1 không có hàng trước, nên previous và change là NULL. Mệnh đề WINDOW định nghĩa một cửa sổ một lần, có tên, để nhiều hàm dùng chung.

first_value chạy đúng với frame mặc định, nhưng last_value thì không: frame mặc định kết thúc ở hàng hiện tại, nên nó trả về giá trị của hàng hiện tại. Thêm ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING để frame là toàn bộ partition. Các hàm chỉ dùng được như window function khác gồm ntile(n), percent_rank(), cume_dist() và nth_value(); tài liệu tham khảo window function của PostgreSQL liệt kê tất cả.

Lọc những hàng mà hàm tổng hợp đếm

FILTER (WHERE ...) cũng dùng được trên hàm tổng hợp dạng window, để có tổng theo điều kiện trên mọi hàng:

SELECT day,
       count(*) FILTER (WHERE status = 'error') OVER (ORDER BY day) AS errors_so_far
FROM requests;

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

GROUP BY và PARTITION BY khác nhau thế nào?

GROUP BY trả về một hàng cho mỗi nhóm. PARTITION BY bên trong OVER giữ lại mọi hàng và cho mỗi hàng một giá trị tính trên nhóm của nó, nên bạn có thể hiển thị số tiền của một hàng bên cạnh tổng của nhóm.

Có dùng window function trong WHERE được không?

Không. Window function chạy sau WHERE, GROUP BY và HAVING, nên chỉ được dùng trong danh sách SELECT và ORDER BY. Hãy tính giá trị trong một truy vấn con hoặc CTE rồi lọc ở truy vấn bên ngoài.

Vì sao LAST_VALUE trả về giá trị của hàng hiện tại?

Khi có ORDER BY trong OVER, frame mặc định kết thúc ở hàng hiện tại (và các hàng bằng nó), nên hàng cuối cùng của frame chính là hàng hiện tại. Thêm ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING để frame là toàn bộ partition.

ROWS và RANGE trong frame khác nhau thế nào?

ROWS đếm các hàng vật lý: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW là hàng hiện tại và hai hàng trước nó. RANGE làm việc trên giá trị của ORDER BY, nên những hàng có giá trị bằng nhau (peer) luôn cùng nằm trong frame.

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