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

Ngày đăng: 25/05/2023

 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 

   

       

Binders 

   

Green 

   

18.504 

   

       

Appliances 

   

Yellow 

   

114.9 

   

       

Tables 

   

Brown 

   

1706.184 

   

       

Phones 

   

Red 

   

911.424 

   

       

Paper 

   

White 

   

15.552 

   

       

Binders 

   

Black 

   

407.976 

   

       

Appliances 

   

Yellow 

   

68.81 

   

       

Binders 

   

Green 

   

2.544 

   

       

Storage 

   

Orange 

   

665.88 

   

       

Storage 

   

Orange 

   

55.5 

   

       

Phones 

   

Black 

   

213.48 

   

       

Binders 

   

Green 

   

22.72 

   

       

Appliances 

   

Green 

   

60.34 

   

       

Chairs 

   

Dark Brown 

   

71.372 

   

       

Furniture 

   

Orange 

   

190.92 

   

 

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 

       

   

Appliances 

   

Yellow 

   

242 

       

   

Appliances 

   

Yellow 

   

242 

       

   

Appliances 

   

Green 

   

242 

       

   

Binders 

   

Green 

   

691 

       

   

Binders 

   

Black 

   

691 

       

   

Binders 

   

Green 

   

691 

       

   

Binders 

   

Green 

   

691 

       

   

Chairs 

   

Dark Brown 

   

762 

       

   

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 

       

   

Appliances 

   

Yellow 

   

       

   

Appliances 

   

Yellow 

   

       

   

Appliances 

   

Green 

   

       

   

Binders 

   

Green 

   

       

   

Binders 

   

Black 

   

       

   

Binders 

   

Green 

   

       

   

Binders 

   

Green 

   

       

   

Chairs 

   

Dark Brown 

   

       

   

Furniture 

   

Orange 

   

       

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: 

2.5 Thực hành với function Rank() và Dense_Rank() 

2.5.1 Rank() 

Khi sử dụng function Rank(), ta cần xác định tên cột trong thứ tự theo hàm, lấy đó là cơ sở xác định giá trị của hàm. Ví dụ, trong lệnh truy vấn dưới đây, ta có thể dùng cột Color trong hàm Order by. Lệnh truy vấn Select sau đó sẽ dùng thứ tự đó để xác định giá trị xếp hạng cho từng hàng. 

Lệnh truy vấn: 

 

				
					SELECT 
Category, 
Color, 
rank() 
OVER (order by Color) AS Item_Rank 
FROM PhamTienManh
				
			

Kết quả: 

 
           

  

       

Category 

       

Color 

       

Avg_Sales 

       

   

Phones 

   

Black 

   

       

   

Binders 

   

Black 

   

       

   

Phones 

   

Black 

   

       

   

Tables 

   

Brown 

   

       

   

Chairs 

   

Dark Brown 

   

       

   

Binders 

   

Green 

   

       

   

Appliances 

   

Green 

   

       

   

Binders 

   

Green 

   

       

   

Binders 

   

Green 

   

       

10 

   

Storage 

   

Orange 

   

10 

       

11 

   

Storage 

   

Orange 

   

10 

       

12 

   

Furniture 

   

Orange 

   

10 

       

13 

   

Phones 

   

Red 

   

13 

       

14 

   

Paper 

   

White 

   

14 

       

15 

   

Appliances 

   

Yellow 

   

15 

       

16 

   

Appliances 

   

Yellow 

   

15 

Hình minh họa: 

2.5.2 Dense_Rank() 

Xem xét kết quả của function Rank(), ta có thể thấy được: Hàm Order by phân loại Category theo màu sắc. Tất cả giá trị màu giống nhau đều có chung xếp hạng, còn khác màu có xếp hạng riêng.  

Màu Black xuất hiện 3 lần trong Dataset, thay vì được xếp thành 1,2,3, các mục có màu Black đều được xếp chung là 1. Tuy nhiên, màu Brown xếp sau Black lại được gán thứ tự là 4, không phải 2.  

Ta có thể thấy được, Rank() bỏ qua giá trị xếp hạng trong hàm Order by. Để gán một giá trị xếp hạng có ý nghĩa hơn, ta có thể dùng DENSE_RANK() 

Tương tự ví dụ với RANK() 

Lệnh truy vấn:  

				
					SELECT  
Category,  
Color, 
dense_rank() 
OVER (order by color) AS Item_Rank 
FROM PhamTienManh 
				
			

Kết quả: 

 
           

  

       

Category 

       

Color 

       

Avg_Sales 

       

   

Phones 

   

Black 

   

       

   

Binders 

   

Black 

   

       

   

Phones 

   

Black 

   

       

   

Tables 

   

Brown 

   

       

   

Chairs 

   

Dark Brown 

   

       

   

Binders 

   

Green 

   

       

   

Appliances 

   

Green 

   

       

   

Binders 

   

Green 

   

       

   

Binders 

   

Green 

   

       

10 

   

Storage 

   

Orange 

   

       

11 

   

Storage 

   

Orange 

   

       

12 

   

Furniture 

   

Orange 

   

       

13 

   

Phones 

   

Red 

   

       

14 

   

Paper