Window Function trong SQL: Mọi điều bạn cần biết

Window Function trong SQL là gì? Hãy cùng nhau khám phá cách dùng các window function để tiến hành phân tích thống kê chỉ bằng một truy vấn SQL.

Tính linh hoạt của SQL dưới dạng ngôn ngữ truy vấn DBMS ngày càng được sử dụng nhiều suốt những năm qua. Tiện ích mở rộng của nó và tính linh hoạt khiến nó trở thành lựa chọn yêu thích cho mọi nhà phân tích dữ liệu.

Hiện có một số hàm cấp nâng cao bên cạnh những hàm thông thường của SQL. Những hàm này thường được gọi là window function. Nếu đ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, bạn có thể dùng chúng để tận dụng dữ liệu tốt nhất.

Tầm quan trọng của window function

Một số window function có sẵn trong SQL. Mỗi window function sẽ giúp bạn thực hiện một chuỗi phép tính. Từ việc tạo các phần tới xếp hạng hàng hoặc gắn số hàng. Những window function này đều có thể mỗi thứ một ít.

Window function hữu ích khi bạn áp dụng các hàm tổng hợp trên một tập hợp dữ liệu cụ thể hoặc bộ sưu tập các hàng. Những function này vượt xa các hàm tổng hợp mà GROUP By cung cấp. Tuy nhiên, đó chính là sự khác biệt chính, khác hàm nhóm, dữ liệu của bạn không được kết hợp thành một hàng đơn lẻ.

Bạn không thể dùng các window function trong lệnh WHERE, FROM và GROUP BY.

Công thức của Window Function

Khi tham chiếu tới window function bất kỳ, bạn cần làm theo cấu trúc cú pháp mặcđinh để nó chạy chính xác. Nếu sai cấu trúc lệnh, bạn sẽ gặp lỗi và không thể chạy code.

Đây là cú pháp mặc định:

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.

Các window function khác với một số lệnh SQL cơ bản. Không giốn 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 nâng cao.

Chuẩn bị dataset

Bạn có thể dùng lệnh CREATE TABLE để tạo một bảng mới trong SQL. Dưới đây là một dataset mẫu mà hướng dẫn này sẽ dùng để xác định một số window function:

Order Date	Category	Color	Sale Price	Quantity
08-11-2016	Phones	Black	907.152	6
12-06-2016	Binders	Green	18.504	3
11-10-2015	Appliances	Yellow	114.9	5
11-10-2015	Tables	Brown	1706.184	9
09-06-2014	Phones	Red	911.424	4
09-06-2014	Paper	White	15.552	3
09-06-2014	Binders	Black	407.976	3
09-06-2014	Appliances	Yellow	68.81	5
09-06-2014	Binders	Green	2.544	3
09-06-2014	Storage	Orange	665.88	6
09-06-2014	Storage	Orange	55.5	2
15-04-2017	Phones	Black	213.48	3
05-12-2016	Binders	Green	22.72	4
22-11-2015	Appliances	Green	60.34	7
22-11-2015	Chairs	Dark Brown	71.372	2
13-05-2014	Furniture	Orange	190.92	5

Giải thích chi tiết hàm Sum

Giả sử bạn muốn tính tổng doanh số cho mỗi giá trị trong cột thư mục. Dưới đây là cách bạn có thể làm việc này:

SELECT category, color,
sum(sale_price)
OVER (order by category) AS total_sales
FROM sahil.sample;

Ở code trên, lệnh SQL lấy danh mục và màu sắc từ dataset gốc. Hàm sum thêm cột sale_price. Nó làm việc này theo danh mục bởi mệnh đề OVER xác định thứ tự theo cột danh mục. Kết quả cuối cùng như sau:

Cách dùng window function Avg()

Giống như hàm sum, bạn có thể tính trung bình mỗi hàng của dữ liệu bằng hàm avg. Thay vì tổng số, bạn sẽ có một cột chứa doanh thu trung bình.

SELECT category, color,
avg(sale_price)
OVER (order by category) AS avg_sales
FROM sahil.sample;

Cách dùng window function Count()

Tương tự như hàm sum và avg, window function count trong SQL khá đơn giản và hoạt động giống hai hàm còn lại. Khi chuyển sang hàm count, bạn sẽ nhận được tổng số lượng từng giá trị trong cột mới.

Đây là cách bạn có thể tính tổng số:

SELECT category, color,
count(category)
OVER (order by category) AS item_count
FROM sahil.sample;

Window Function Row_Number()

row_number() hoạt động hơi khác một chút so với các window function kể trên. Hàm row_number() gắn một số hàng cho từng hàng, phụ thuộc vào thứ tự mệnh đề. Số hàng khởi đầu là 1. row_number gắn một giá trị tương ứng cho từng hàng cho tới khi kết thúc.

Đây là cấu trúc cơ bản của một hàm row_number():

SELECT category, color,
row_number()
OVER (order by category) AS item_number
FROM sahil.sample;

Thế nhưng điều gì xảy ra nếu bạn muốn gán các số hàng riêng biệt cho từng mục trong danh mục? Cú pháp trên thiết lập một số seri luân phiên, không phân biệt các mặt hàng được lưu trữ trong danh mục. Ví dụ, danh mục thiết bị cần được đánh số riêng…

Bạn có thể dùng hàm partition để thực hiện nhiệm vụ đơn giản nhưng thực tế này. Từ khóa partition gắn số hàng được chỉ định dựa trên mỗi mặt hàng trong danh mục.

SELECT category, color,
row_number()
OVER (partition by category order by category) AS item_number
FROM sahil.sample;

Hàm Rank() và Dense_Rank()

Hàm rank() hoạt động khác hàm row_number(). Bạn cần xác định tên cột trong thứ tự theo hàm, để dùng nó làm cơ sở xác định giá trị hàm. Ví dụ, trong code dưới đây, bạn có thể dùng cột màu sắc trong hàm order by. Truy vấn này sau đó sẽ dùng thứ tự đó để gắn giá trị xếp hạng cho từng hàng.

Bạn có thể dùng cú pháp code bên dưới để chuyển một hàm xếp hạng trong SQL:

SELECT category, color,
rank()
OVER (order by color) AS item_rank
FROM sahil.sample;

Kết quả:

Hàm order by phân loại thư mục màu sắc, còn hàm rank xếp hạng theo từng màu. Tuy nhiên, tất cả giá trị màu giống nhau đều có chung xếp hạng, còn màu khác có xếp hạng riêng. Màu đen xuất hiện 3 lần trong dataset; thay vì gắn một giá trị xếp hạng 1, 2, 3, các mục màu đen được xếp hạng 1.

Tuy nhiên, màu nâu Brown sẽ là 4, không phải 2. Rank function bỏ qua các giá trị và gắn giá trị theo trình tự thời gian cho các mục khác nhau. Nếu muốn gắn một giá trị xếp hạng ý nghĩa hơn, bạn có thể dùng hàm dense_rank().

Hàm dense_rank không bỏ qua bất kỳ giá trị xếp hạn trong hàm order by. Ví dụ, 3 mục màu đầu tiên sẽ có xếp hạng 1. Tuy nhiên, màu (Brown) tiếp theo sẽ không có rank 4, mà là rank 2, là thứ tự thời gian tiếp theo trong danh mục đánh số. Hàm dense_rank là một window function thực tế hơn bởi nó gắn một giá trị có ý nghĩa cho tất cả danh sách các mục.

Dưới đây là cách bạn có thể dùng hàm dense_rank trong SQL:

SELECT category, color,
dense_rank()
OVER (order by color) AS item_rank
FROM sahil.sample;

Kết quả:

Trên đây là mọi điều bạn cần biết về Windows Function trong SQL. Hi vọng bài viết hữu ích với các bạn.