Những hàm nâng cao dùng để truy vấn DBMS trong SQL thường được gọi là Window Function. Nếu bạn đang phải xử lý dữ liệu phức tạp và muốn thực hiện các phép tính nâng cao, chúng ta có thể dùng Window function để tận dụng dữ liệu tốt nhất. Window 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 Window Function được định nghĩa khi có mệnh đề OVER() đi kèm sau lệnh gọi hàm.
Mục lục
1. Công thức của Window Function.
Khi tham chiếu tới một Window Function bất kỳ, ta cần viết truy vấn theo cấu trúc cú pháp mặc định. Khi viết chính xác, nó sẽ chạy theo đúng ý muốn. Nếu viết sai cấu trúc lệnh, ta sẽ gặp lỗi và dòng truy vấn sẽ không hoạt động (error)
Công thức:
SELECT columnname1,
{window_function}(columnname2)
OVER([PARTITION BY columnname1] [ORDER BY columnname3]) AS new_column
FROM table_name;
Cụ thể:
- 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.
- Partition by: Dùng để nhóm các hàng có liên quan đến nhau đê thực hiện việc tính toán
- Order By: Dùng để sắp xếp các hàng có trong từng partition đó
Các window function là một dạng truy vấn khác so với các câu lệnh SQL cơ bản. Không giống hàm tổng hợp trong SQL, bạn có thể dùng những Window Function để triển khai các hàm truy vấn nâng cao.
2. Các loại Window Function
2.1 Aggregate Functions (tính toán).
Tên Function | Giá trị |
AVG() | Trả về các 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ị |
2.2 Ranking Functions (xếp hạng)
- 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(): Tính 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)
- 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
2.3 Analytic Functions (Thống kê)
- 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)
3. Ứng dụng Window Function trong thực tế
3.1 Chuẩn bị Dataset
Ở trong SQL Sever, bảng sau có tên là PhamTienManh
Category | Color | Sale Price | Quantity |
Phones | Black | 907.152 | 6 |
Binders | Green | 18.504 | 3 |
Appliances | Yellow | 114.9 | 5 |
Tables | Brown | 1706.184 | 9 |
Phones | Red | 911.424 | 4 |
Paper | White | 15.552 | 3 |
Binders | Black | 407.976 | 3 |
Appliances | Yellow | 68.81 | 5 |
Binders | Green | 2.544 | 3 |
Storage | Orange | 665.88 | 6 |
Storage | Orange | 55.5 | 2 |
Phones | Black | 213.48 | 3 |
Binders | Green | 22.72 | 4 |
Appliances | Green | 60.34 | 7 |
Chairs | Dark Brown | 71.372 | 2 |
Furniture | Orange | 190.92 | 5 |
3.2 Thực hành với function SUM
Giả sử: Tính tổng doanh số cho mỗi giá trị trong cột Category.
Lệnh truy vấn:
SELECT Category,
Color,
sum(SalePrice)
OVER (order by Category) total_sales
FROM PhamTienManh
Ở câu truy vấn trên, ta lấy Category và Color từ Dataset gốc. Hàm SUM thêm cột total_sales. Nó làm việc này theo Category, bởi mệnh đề OVER xác định thứ tự theo cột Category.
Kết quả:
| Category | Color | total_sales |
1 | Appliances | Yellow | 242 |
2 | Appliances | Yellow | 242 |
3 | Appliances | Green | 242 |
4 | Binders | Green | 691 |
5 | Binders | Black | 691 |
6 | Binders | Green | 691 |
7 | Binders | Green | 691 |
8 | Chairs | Dark Brown | 762 |
9 | Furniture | Orange | 952 |
10 | Paper | White | 967 |
11 | Phones | Red | 2998 |
12 | Phones | Black | 2998 |
13 | Phones | Black | 2998 |
14 | Storage | Orange | 3718 |
15 | Storage | Orange | 3718 |
16 | Tables | Brown | 5424 |
2.4 Thực hành với function COUNT()
Tương tự SUM và AVG, function Count trong SQL khá đơn giản, hoạt động giống hai hàm đã nêu ở trên. Khi sử dụng hàm này trong truy vấn, ta sẽ nhận được tổng số lượng giá trị trong 1 cột mới
Lệnh truy vấn:
SELECT
Category,
Color,
count(Category)
OVER (order by Category) as Item_Count
FROM PhamTienManh
Kết quả:
| Category | Color | Avg_Sales |
1 | Appliances | Yellow | 3 |
2 | Appliances | Yellow | 3 |
3 | Appliances | Green | 3 |
4 | Binders | Green | 7 |
5 | Binders | Black | 7 |
6 | Binders | Green | 7 |
7 | Binders | Green | 7 |
8 | Chairs | Dark Brown | 8 |
9 | Furniture | Orange | 9 |
10 | Paper | White | 10 |
11 | Phones | Red | 13 |
12 | Phones | Black | 13 |
13 | Phones | Black | 13 |
14 | Storage | Orange | 15 |
15 | Storage | Orange | 15 |
16 | Tables | Brown | 16 |
Hình minh họa: