Khám phá sức mạnh của ‘window functions trong SQL’ – những hàm phân tích độc đáo cho phép bạn thực hiện các phép toán trên tập con của kết quả truy vấn. Tìm hiểu cách chúng tạo ra các giải pháp truy vấn thông minh hơn và hiệu quả hơn
Window functions là gì?
Hầu hết khi bắt đầu tìm hiểu và học hỏi vể SQL nói chung và MySQL nói riêng chúng ta đều sẽ sử dụng đến các Aggregate Functions, đây là các hàm tổng hợp được sử dụng để tóm tắt hoặc nhóm dữ liệu trên nhiều hàng và trả về duy nhất một giá trị duy nhất của mỗi nhóm(khi kết hợp mệnh đề GROUP BY). Ví dụ như hàm SUM sẽ tính tổng các giá trị của cột nhất định trong nhóm đó. Vậy trong trường hợp ta muốn tính tỉ lệ giá trị của của cột đó trong từng bản ghi so với tổng gía trị nói trên thì sao? Ta nhất định phải viết thêm 1 subquery tính tổng rồi thực hiện phép chia à? Cũng rất hay những với MySQL thì nó còn giúp đỡ ta thực hiện việc này rất tốt nhờ Window Functions.
Windows functions trong SQL được sử dụng để thực hiện các phép tính các dòng có liên quan đến dòng hiện tại. Khác với Aggregate Functions tính toán tất cả các hàng, Windows Functions được sử dụng để tính toán theo từng hàng. Một Windows Function được định nghĩa khi có mệnh đề OVER() đi kèm sau lệnh gọi hàm.
Sự khác nhau giữa Window Functions và Aggregate Functions sẽ thể hiện ở 1 vài góc độ như bảng sau:
| Aggregate Functions | Window Functions | |
| Mục đích | Aggregate Functions được sử dụng để tóm tắt hoặc nhóm dữ liệu trên nhiều hàng và trả về một giá trị duy nhất cho mỗi nhóm. Các hàm tổng hợp phổ biến bao gồm SUM, COUNT, AVG, MIN và MAX. | Window Functions được sử dụng để thực hiện các phép tính trên một tập hợp các hàng liên quan đến hàng hiện tại mà không giảm số hàng trong kết quả. Hàm Window thêm một cột mới vào tập kết quả, giữ nguyên các hàng gốc và hiển thị kết quả tính toán cùng với mỗi hàng. |
| Phạm vi | Các Aggregate Functions thu gọn nhiều hàng thành một hàng duy nhất bằng cách áp dụng hàm trên một nhóm các hàng được xác định bởi mệnh đề GROUP BY. | Window Functions hoạt động trên một tập hợp được xác định của các hàng liên quan đến hàng hiện tại, được gọi là khung cửa sổ. Khung cửa sổ này được xác định bởi mệnh đề OVER và có thể dựa trên một thứ tự cụ thể, phân vùng hoặc phạm vi các hàng. |
| Tập kết quả | Tập kết quả của Aggregate Functions bao gồm một hàng duy nhất cho mỗi nhóm, trong đó mỗi hàng đại diện cho một nhóm và chứa giá trị đã được tổng hợp. | Tập kết quả của Window Functions chứa cùng số hàng với tập dữ liệu gốc nhưng bao gồm một cột bổ sung với kết quả tính toán của hàm Window cho mỗi hàng. |
| Mệnh đề GROUP BY | Khi sử dụng các Aggregate Functions, bạn thường cần chỉ định mệnh đề GROUP BY để xác định cách nhóm dữ liệu cho việc tổng hợp. | Window Functions không yêu cầu mệnh đề GROUP BY, nhưng nó yêu cầu mệnh đề OVER để xác định khung cửa sổ nơi thực hiện các phép tính. |
Cú pháp của Window Functions
Cú pháp của Window Functions như sau:
SELECT columnname1, {window_function}(columnname2) OVER([PARTITION BY columnname1] [ORDER BY columnname3]) AS new_column FROM table_name;Trong đó:
- coulmnname1 là tên cột đầu tiên bạn muốn chọn.
- {window_function} là tên của một hàm tổng hợp như sum, avg, count, row_number, rank hoặc dense_rank.
- columnname2 là tên của cột mà bạn áp dụng window function.
- columnname3 là tên cột thứ ba, sẽ tạo cơ sở cho phân vùng.
- new_column là nhãn cho cột mới mà bạn có thể áp dụng bằng từ khóa AS.
- table_name là tên của bảng nguồn.
Phân loại Window Funtions
Dựa vào chức năng của các Window Function, chúng ta có thể chia Window Function thành 3 loại như sau:
| Function name | Tác dụng | |
| Aggregate Functions | AVG() | Trả về giá trị trung bình |
| COUNT() | Đếm các giá trị | |
| MAX() | Trả về giá trị lớn nhất | |
| MIN() | Trả về giá trị nhỏ nhất | |
| SUM() | Tính tổng các giá trị | |
| Ranking Functions | RANK() | Xếp hạng các giá trị theo thứ tự tăng dần nhưng sẽ trả về thứ hạng giống nhau với các giá trị giống nhau và bỏ qua thứ hạng đó |
| DENSE_RANK () | Xếp hạng các giá trị theo thứ tự tăng dần nhưng sẽ trả về thứ hạng giống nhau với các giá trị giống nhau và không bỏ qua thứ hạng đó | |
| ROW_NUMBER () | Xếp hạng các giá trị trong từng partition theo thứ tự tăng dần mà không quan tâm đến giá trị giống nhau | |
| CUME_DIST () | Tinh tỷ lệ các giá trị nhỏ hơn hoặc bằng giá trị hiện tại | |
| PERCENT_RANK | (rank -1)/ (row-1) Trong đó: + rank là thứ tự của giá trị đó theo thứ tự tăng dần (các giá trị giống nhau trả về thứ hạng giống nhau) + row: tổng số dòng (xét trong 1 partition) | |
| Analytical Functions | FIRST_VALUE (expression) | Lấy giá trị đầu trong từng partition |
| LAST_VALUE (expression) | Lấy giá trị cuối trong từng partition | |
| LAG (expression, offset) | Trả ra giá trị dòng trước của dòng hiện tại | |
| LEAD (expression, offset) | Trả ra giá trị dòng sau của dòng hiện tại |
Kết luận
Trên đây là phần giới thiệu về tác dụng và cấu trúc của window function trong MySQL. Đây là một tiện ích giúp chúng ta có thể tiết kiệm rất nhiều thời gian và tài nguyên nếu tận dụng được nó. SmartData sẽ tiếp tục seri về Window Function bằng các bài viết về cách sử dụng của từng hàm trong các bài chia sẻ tiếp theo. Mong bạn đọc tiếp tục theo dõi.
Nếu bạn là newbie có thể tham khảo bài viết này để tìm hiểu lộ trình học DA trong 3 tháng của SmartData
Nếu bạn thấy bài viết hay và hữu ích, bạn có thể tham gia các kênh sau của SmartData để nhận được nhiều hơn nữa:
