Windows functions trong SQL và ứng dụng

Windows functions trong SQL và ứng dụng của nó

Ngày đăng: 14/03/2022

Windows functions trong SQL ngày càng được sử dụng rộng rãi vì sự ngắn gọn của nó. Bài viết này sẽ giúp tìm hiểu về Windows functions và ứng dụng của nó.

Bài viết được viết bởi bạn Dương Thu Trang – học viên Khóa 1, Chương trình vệ tinh “Khoa học dữ liệu trong Kinh tế và Kinh doanh”. Đây là bài thu hoạch trong môn Cơ Sở Dữ Liệu được học viên của chương trình tự tổng hợp.

Tìm hiểu thêm về chương trình đào tạo “Khoa học dữ liệu trong Kinh tế và Kinh doanh” của đại học Ngoại thương tại đây

tổng quan chương trình

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

Windows 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 Windows Function được định nghĩa khi có mệnh đề OVER() đi kèm sau lệnh gọi hàm.

Windows functions trong SQL với mệnh đề Over

  • Cú pháp của Windows Functions:
Windows Functions () OVER (

[PARTITION BY partition_expression, ... ]

ORDER BY sort_expression [ASC | DESC], ...)
  • Trong đó:

– Partition by clause: dùng để nhóm các hàng có liên quan đến nhau thành 1 partition để thực hiện việc tính toán

– Order by clause: dùng để sắp xếp các hàng có trong từng partition đó

Khi sử dụng Wimdows Functions, các kết quả trả về được tính toán trong từng partition.

  • Có 3 loại Windows Functions:
 Function NameMiêu tả
Aggregate FunctionsAVG()Trả về 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ị
Ranking FunctionsRANK()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 đó

Ví dụ: rank(): 1,1,3,4,5

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 đó

Ví dụ: dense_rank (): 1,1,2,3,4

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

Ví dụ: row_number (): 1,2,3,4,5

CUME_DIST()Tinh 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)

Analytic FunctionsFIRST_VALUE (expression)Lấy giá trị đầu trong từng partition
LAST_VALUE (expression)Lấy giá trị cuối trong từng partition
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)

  • Ví dụ

Truy vấn danh sách khách hàng có tổng doanh thu tháng xếp từ cao xuống thấp trong hệ thống theo mỗi tháng.

Windows functions trong SQL: ví dụ câu lệnh và kết quả trả về

Ảnh: Câu lệnh và kết quả trả về trên SQL

Ứng dụng của Windows Functions trong SQL

Windows functions trong SQL được sử dụng khi muốn tính toán trong từng nhóm, có thể là theo tháng như ở ví dụ trên. Trong ví dụ đó, kết quả trả về là thứ hạng các khách hàng có lượng mua nhiều nhất theo từng tháng.

Kết luận

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.

Tìm hiểu thêm về Windows functions trong SQL: Sự khác biệt giữa Window Functions và Group by

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *