Window Function và những ứng dụng của Window Function trong SQL

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.

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: