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

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.

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.

• 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 Name	Miêu tả
Aggregate Functions		AVG()	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 Functions		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 đó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 nhauVí 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 Functions	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
	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ỏ quaTrong đó: 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ỏ quaTrong đó: 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.

Ả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