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 tên 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 Color từ Dataset gốc. Hàm SUM thêm cột total_sales. 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 AVG, function Count trong SQL khá đơn giản, hoạt động giống hai hàm đã nêutrê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: