Windows functions trong SQL và ứng dụng của nó
Windows functions trong SQL ngày càng được sử dụng rộng rãi vì sự ngắn gọn của nó. Bài viết này sẽ giúp tìm hiểu về Windows functions và ứng dụng của nó.
Bài viết được viết bởi bạn Dương Thu Trang – học viên Khóa 1, Chương trình vệ tinh “Khoa học dữ liệu trong Kinh tế và Kinh doanh”. Đây là bài thu hoạch trong môn Cơ Sở Dữ Liệu được học viên của chương trình tự tổng hợp.
Tìm hiểu thêm về chương trình đào tạo “Khoa học dữ liệu trong Kinh tế và Kinh doanh” của đại học Ngoại thương tại đây
tổng quan chương trìnhMục lục
Giới thiệu về Windows Functions trong SQL
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.
- Cú pháp của Windows Functions:
Windows Functions () OVER ( [PARTITION BY partition_expression, ... ] ORDER BY sort_expression [ASC | DESC], ...)
- Trong đó:
– Partition by clause: dùng để nhóm các hàng có liên quan đến nhau thành 1 partition để thực hiện việc tính toán
– Order by clause: dùng để sắp xếp các hàng có trong từng partition đó
Khi sử dụng Wimdows Functions, các kết quả trả về được tính toán trong từng partition.
- Có 3 loại Windows Functions:
| Function Name | Miêu tả | ||
| 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 đó
Ví dụ: rank(): 1,1,3,4,5 |
|
| 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 đó
Ví dụ: dense_rank (): 1,1,2,3,4 |
||
| 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
Ví dụ: row_number (): 1,2,3,4,5 |
||
| 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) |
||
| Analytic 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) | Sắp xếp các giá trị theo thứ tự tăng dần và trả về các giá trị không bị bỏ qua
Trong đó: offset: số giá trị bỏ qua tính từ trên xuống (Nếu tham số này bị bỏ qua, mặc định là 1) |
||
| LEAD (expression, offset) | Sắp xếp các giá trị theo thứ tự giảm dần và trả về các giá trị không bị bỏ qua
Trong đó: offset: số giá trị bỏ qua tính từ trên xuống (Nếu tham số này bị bỏ qua, mặc định là 1) |
||
- Ví dụ
Truy vấn danh sách khách hàng có tổng doanh thu tháng xếp từ cao xuống thấp trong hệ thống theo mỗi tháng.
Ảnh: Câu lệnh và kết quả trả về trên SQL
Ứng dụng của Windows Functions trong SQL
Windows functions trong SQL được sử dụng khi muốn tính toán trong từng nhóm, có thể là theo tháng như ở ví dụ trên. Trong ví dụ đó, kết quả trả về là thứ hạng các khách hàng có lượng mua nhiều nhất theo từng tháng.
Kết luận
Bài viết này đã giới thiệu về Windows functions trong SQL, đưa ra ví dụ và ứng dụng của nó, từ đó giúp chúng ta có thể vận dụng nó trong việc giải quyết các bài toán.
Tìm hiểu thêm về Windows functions trong SQL: Sự khác biệt giữa Window Functions và Group by
