Người viết: Ngô Công Tuyền - Học viên lớp Java 09
Email: tuyenemotion@gmail.com

Một window function trong MYSQL thường để tính toán một tập hợp các hàng liên quan đến hàng hiện tại. Hàng hiện tại là hàng mà hàm dựa vào để tính toán. Window function được thực hiện một phép tính tương tự như khi sử dụng các hàm tổng hợp (aggregate functions). Nhưng không giống các hàm tổng hợp thực hiện thao tác trên toàn bộ bảng, windown funtion không trả về kết quả được tổng hợp thành một hàng. Vd: Hàm count(row_name) trả về một hàng chứa số lượng hàng của câu truy vấn. Còn với window function nó thao tác với một tập hợp các hàng và tạo tạo một kết quả phù hợp với từng hàng. Điều này cho phép mỗi hàng có thể duy trì sự độc lập của mình.

1. Cú pháp của window function.

Dưới đây là mẫu cú pháp của window function:

   window_function_name(expression)   
   OVER (  
       [partition_defintion]  
       [order_definition]  
       [frame_definition]  
   )

Trong cấu trúc trên, đầu tiên là tên của hàm kèm theo bởi một biểu thức. Sau đó chúng ta chỉ định mệnh đề OVER bao gồm 3 biểu thức: partition_ definition, order_definition, frame_definition.
Các loại window function thường gặp là:

  • Rank(): trả về thứ tự của hàng hiện tại. Thứ tự được sắp xếp không liên tục. VD: 1,2,2,4,…
  • Desen_rank() trả về thứ tự được sắp xếp liên tục. VD: 1,2,2,3,…
  • Row_number() trả về số hàng : VD: 1,2,3,4
  • First_value() trả về giá trị đầu tiên.
  • Last_value() trả về giá trị cuối cùng.

2.Mệnh đề partition.

Mệnh đề này dùng để lọc bảng dựa trên các thuộc tính thành các vùng. Windown funtion được hoạt động trong từng vùng, khi vượt qua ranh giới của vùng đó, nó sẽ được khởi tạo lại. Dưới đây là cú pháp của mệnh đề:
PARTITION BY <expression>[{,<expression>...}]
Trong cú pháp này, chúng ta có thể truyền 1 hay nhiều biểu thức ngăn cách nhau bởi dấu phẩy.

3.Mệnh đề order by.

Mệnh đề này dùng để sắp xếp các hàng trong một vùng với cú pháp:
ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
Chúng ta có thể sắp xếp nhiều trường theo các thứ tụ khác nhau. Mệnh đề có thể định nghĩa vơi một hay nhiều điều kiện ngăn các nhau bời dấu phẩy.

4.Mệnh đề frame.

Một frame là một tập con hiện tại của vùng như hình vẽ:
Frame in partition
Chúng ta có thể tạo ra 1 frame theo cú pháp:
frame_unit {<frame_start>|<frame_between>}
Trong cú pháp frame_unit có 2 giá trị để lựa chọn ROWS hoặc RANGE:

  • ROWS tạo ra frame dựa trên số lượng với hàng hiện tại : n preceding, m fllowing
  • RANGE thì dựa trên tính chất của hàng: unbounded preceding, unbounded following.
    Nếu không khai báo giá trị mặc định cho mệnh đề này là:
    range between unbounded preceding and current row

5.Ví dụ.

Với bài Leetcode 185: Department Top Three Salaries.
Link: https://leetcode.com/problems/department-top-three-salaries
Với đề bài yêu cầu tìm ra 3 mức lương cao nhất tại mỗi bộ phận:
Leetcode 185
Ta có lời giải như sau:

select Department, Employee, Salary from 
(select dense_rank() over(partition by d.id order by e.salary desc ) as r, d.name as Department, e.name as Employee, e.salary from Employee e 
inner join Department d on d.id = e.departmentId) as t where t.r <4

6.Các link tham khảo:

  • Bài viết: https://www.javatpoint.com/mysql-window-functions
  • Ví dụ về frame: https://www.geeksforgeeks.org/frame-clause-in-sql/