Window 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 và trả về một giá trị duy nhất, Windows Functions được sử dụng để tính toán theo từng hàng, trả về một giá trị cho mỗi hàng.
- 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ó 4 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 | ||
| Distribution Functions | 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 | ||
| NTH_VALUE(expr, n) | Lấy giá trị thứ n trong từng partition | ||
| NTILE(n) | Chia các hàng trong một phân vùng càng đều nhau càng tốt thành n nhóm và gán số nhóm cho mỗi hàng. | ||
| LAG (expr, offset,default) | 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, offset = 1, default = NULL) | ||
| LEAD (expr, offset,default) | 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, offset = 1, default = NULL) | ||
WINDOW FRAME
Cú pháp:
ROWS | RANGE | GROUPS BETWEEN lower_bound AND upper_bound
(lower_bound phải ở trước upper_bound)
Giới hạn bao gồm:
- UNBOUNDED PRECEDING
- N PRECEDING
- CURRENT ROW
- M FOLLOWING
- UNBOUNDED FOLLOWING
Ví dụ:
SELECT column1, column2,
SUM(column3) OVER (PARTITION BY column1 ORDER BY column2 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS running_total
FROM your_table;
Trong ví dụ này, chúng ta sử dụng hàm SUM() như một window function để tính tổng cho mỗi dòng dữ liệu dựa trên giá trị của column1, và sắp xếp theo column2. WINDOW FRAME được đặt trong mệnh đề ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING, cho phép tính toán tổng cho hàng hiện tại và hàng liền trước, cũng như hàng liền sau.
Có nhiều loại WINDOW FRAME khác nhau như ROWS UNBOUNDED PRECEDING, ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING, RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING
– Xem ngay Lộ trình học Data Analyst chuyển ngành thành công trong vòng 6 tháng
– Xem ngay Lộ trình học Marketing Automation & Analytics Coaching 1 on 1 để upgrade kỹ năng phân tích cho Marketer
– Tham gia Vietnam Data Analyst Forum – #1 Informative Group để học hỏi và chia sẻ kiến thức về Data Analytics
– Cập nhật lịch khai giảng, chương trình ưu đãi và nhận tư vấn chuyển ngành miễn phí tại Data Coaching 1 on 1 – UniGap
