Tất cả những gì bạn cần biết về cách sử dụng chúng

Tính linh hoạt của SQL với tư cách là ngôn ngữ truy vấn DBMS đã tăng lên trong những năm qua. Tiện ích mở rộng và tính linh hoạt của nó khiến nó trở thành ứng dụng yêu thích mọi thời đại của mọi nhà phân tích dữ liệu.

Có khá nhiều hàm cấp cao bên cạnh các hàm thông thường của SQL. Các chức năng này thường được gọi là chức năng cửa sổ. Nếu bạn đang 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ể sử dụng chúng để tận dụng tối đa dữ liệu của mình.

Tầm quan trọng của chức năng cửa sổ

Một số hàm cửa sổ có sẵn trong SQL và mỗi hàm sẽ giúp bạn thực hiện một loạt phép tính. Từ việc tạo phân vùng đến xếp hạng hàng hoặc gán số hàng, các chức năng cửa sổ này thực hiện một chút mọi thứ.

Các hàm cửa sổ hữu ích khi bạn đang áp dụng các hàm tổng hợp trên một tập dữ liệu cụ thể hoặc tập hợp các hàng. Các chức năng này vượt xa các chức năng tổng hợp mà GROUP BY cung cấp. Tuy nhiên, sự khác biệt chính là, không giống như chức năng nhóm, dữ liệu của bạn không được kết hợp thành một hàng.

Bạn không thể sử dụng các chức năng của cửa sổ trong Ở ĐÂU, TỪ, và NHÓM THEO các câu lệnh.

Cú pháp của một chức năng cửa sổ

Khi bạn đề cập đến bất kỳ chức năng cửa sổ nào, bạn cần tuân theo cấu trúc cú pháp mặc định để nó hoạt động chính xác. Nếu cấu trúc lệnh không chính xác, bạn sẽ gặp lỗi và mã của bạn sẽ không chạy được.

Đâ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; 

Ở đâu:

• tên cột1 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ặcdens_rank.
• tên cột2 là tên của cột mà bạn đang áp dụng chức năng cửa sổ.
• tên cột3 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 cách sử dụng BẰNG từ khóa.
• tên_bảng là tên của bảng nguồn.

Các chức năng của cửa sổ khác với một số lệnh SQL cơ bản nhất. Không giống như các hàm tổng hợp trong SQL, bạn có thể sử dụng các hàm cửa sổ này để thực hiện các hàm nâng cao.

Chuẩn bị bộ dữ liệu

Bạn có thể dùng TẠO BẢNG câu lệnh để tạo một bảng mới trong SQL. Đây là tập dữ liệu mẫu mà hướng dẫn này sẽ sử dụng để xác định một số chức năng của cửa sổ:

Giải thích hàm tổng

Giả sử bạn muốn tính tổng doanh số cho từng giá trị trong cột danh mục. Đây là cách bạn có thể làm điều đó:

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

Trong đoạn mã trên, câu lệnh SQL lấy danh mục và màu từ tập dữ liệu gốc. Hàm sum cộng cột sale_price. Nó làm như vậy theo danh mục, vì mệnh đề OVER chỉ định thứ tự theo cột danh mục. Kết quả cuối cùng như sau:

Cách sử dụng chức năng cửa sổ trung bình ()

Giống như hàm tính tổng, bạn có thể tính giá trị trung bình trên mỗi hàng dữ liệu bằng hàm trung bình hàm số. Thay vì tổng, bạn sẽ nhận được một cột có doanh thu trung bình.

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

Tìm hiểu cách sử dụng chức năng cửa sổ Count()

Tương tự như các hàm tổng và trung bình, hàm cửa sổ đếm trong SQL khá đơn giản và hoạt động giống như hai hàm còn lại. Khi bạn chuyển hàm đếm, bạn sẽ nhận được tổng số lượng của từng giá trị trong cột mới.

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

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

Hàm cửa sổ Row_Number()

Không giống như một số chức năng cửa sổ khác được liệt kê ở trên, row_number() hoạt động hơi khác một chút. Hàm row_number() gán một số hàng cho mỗi hàng, tùy thuộc vào thứ tự của mệnh đề. Số hàng bắt đầu là 1; row_number gán một giá trị tương ứng cho mỗi hàng cho đến khi kết thúc.

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

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

Nhưng điều gì sẽ xảy ra nếu bạn muốn gán các số hàng riêng biệt cho từng hạng mục? Cú pháp trên thiết lập một số xê-ri 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 của thiết bị phải được đánh số riêng, tiếp theo là chất kết dính, v.v.

Bạn có thể dùng vách ngăn chức năng để thực hiện nhiệm vụ đơn giản nhưng thiết thực này. Từ khóa phân vùng chỉ định số hàng được chỉ định dựa trên mỗi mục 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()

Các cấp() chức năng hoạt động khác với chức năng số lượng hàng() hàm số. Bạn cần chỉ định tên cột trong thứ tự theo chức năng, để sử dụng nó làm cơ sở để xác định các giá trị xếp hạng. Ví dụ: trong ví dụ mã sau, bạn có thể sử dụng cột màu trong thứ tự theo chức năng. Sau đó, truy vấn sẽ sử dụng thứ tự đó để gán giá trị xếp hạng cho mỗi hàng.

Bạn có thể sử dụng cú pháp mã bên dưới để chuyển hàm xếp hạng trong SQL:

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

Hãy xem đầu ra để hiểu chức năng này hoạt động như thế nào.

Thứ tự theo chức năng sắp xếp danh mục màu, trong khi chức năng xếp hạng chỉ định thứ hạng cho từng màu. Tuy nhiên, tất cả các giá trị màu giống nhau có cùng thứ hạng, trong khi các màu khác nhau có thứ hạng riêng biệt. Màu đen xuất hiện ba lần trong tập dữ liệu; thay vì gán giá trị xếp hạng là 1, 2 và 3, các mục màu đen được xếp hạng 1.

Tuy nhiên, màu tiếp theo Màu nâu có thứ hạng 4 thay vì thứ hạng 2. Hàm xếp hạng bỏ qua các giá trị và gán giá trị theo trình tự thời gian tiếp theo cho các mục nhập khác nhau. Nếu bạn muốn chỉ định một giá trị xếp hạng có ý nghĩa hơn, bạn có thể sử dụng dày đặc_rank() hàm số.

Hàm density_rank không bỏ qua bất kỳ giá trị xếp hạng nào trong khi sắp xếp theo thứ tự của hàm. Ví dụ: ba mục màu đầu tiên (Đen) sẽ có thứ hạng 1. Tuy nhiên, màu tiếp theo (Nâu) sẽ không có thứ hạng 4 mà là thứ hạng 2, là thứ tự thời gian tiếp theo trong danh sách đánh số. Hàm thick_rank là một hàm cửa sổ thực tế hơn vì nó gán một giá trị có ý nghĩa cho danh sách các mục.

Đây là cách bạn có thể sử dụng hàm thick_rank trong SQL:

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

Và đây là một ví dụ về kết quả đầu ra từ chức năng này sẽ như thế nào:

Các hàm SQL để giải cứu

Các chức năng cửa sổ của SQL rất lý tưởng để thực hiện các hoạt động phân tích nâng cao. Tuy nhiên, bạn có thể sử dụng nhiều lệnh SQL khác để đảm bảo kỹ năng tính toán của mình ở mức tốt nhất. Khi bạn kết hợp và tính toán nhiều kết quả trong một lần, không có gì tốt hơn là sử dụng các truy vấn con của SQL.

Truy vấn phụ là một công cụ tuyệt vời để thực hiện các chức năng nâng cao, nâng cao chất lượng kết quả của bạn. Tùy thuộc vào nhu cầu của giờ, bạn có thể tùy chỉnh các truy vấn của mình và làm cho chúng hiệu quả hơn để phù hợp với yêu cầu của bạn.