Giới thiệu Windows Function trong SQL

SQL đã trở thành ngôn ngữ truy vấn cho hệ quản trị cơ sở dữ liệu phổ biến và linh hoạt ngày càng được ưa chuộng trong những năm gần đây. Tính mở rộng và tính linh hoạt của SQL là những yếu tố quan trọng khiến nó trở thành lựa chọn hàng đầu của các nhà phân tích dữ liệu.

Trong SQL, hiện có một số hàm nâng cao được gọi là window function, điều này bổ sung cho các hàm thông thường. Nếu bạn đang làm việc với dữ liệu phức tạp và cần thực hiện các phép tính tiên tiến, các hàm này sẽ giúp bạn tận dụng tối đa tiềm năng của dữ liệu.

1. Giới thiệu về Windows Functions trong SQL

Windows functions trong SQL được dùng để tính toán các giá trị liên quan đến dòng hiện tại, khác với Aggregate Functions mà tính toán trên tất cả các dòng, Windows Functions được sử dụng để tính toán theo từng dòng. Một Windows Function được định nghĩa bằng cách sử dụng mệnh đề OVER() sau lệnh gọi hàm.

2. 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 định để 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;

Trong đó:

– `columnname1`: Tên của cột đầu tiên mà bạn muốn chọn.
– `{window_function}`: Tên của hàm window function như sum, avg, count, row_number, rank hoặc dense_rank.
– `columnname2`: Tên của cột mà bạn muốn áp dụng window function.
– `columnname3`: Tên của cột thứ ba, sẽ tạo cơ sở cho phân vùng.
– `new_column`: Nhãn cho cột mới được tạo, có thể áp dụng bằng từ khóa AS.
– `table_name`: Tên của bảng nguồn.

Các window function khác biệt so với các hàm tổng hợp cơ bản trong SQL, cho phép bạn thực hiện các tính toán nâng cao trên dữ liệu.

3. 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:

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 sắp xếp thứ tự các mục theo màu sắc, trong khi hàm rank xếp hạng các mục theo từng màu. Tuy nhiên, các mục có cùng màu sẽ có cùng một xếp hạng, trong khi màu khác sẽ có xếp hạng khác. Ví dụ, màu đen xuất hiện 3 lần trong tập dữ liệu; thay vì gán xếp hạng 1, 2, 3 cho các mục màu đen, chúng sẽ có cùng xếp hạng 1.

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

Hàm dense_rank là một window function thực tế hơn vì nó gán một giá trị xếp hạng có ý nghĩa cho tất cả các mục trong danh sách.

Dưới đây là cách bạn có thể sử 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ả:

Bài viết này đã giới thiệu về Windows functions trong SQL, đưa ra ví dụ và ứng dụng của nó, từ đó giúp chúng ta có thể vận dụng nó trong việc giải quyết các bài toán.