Trong bài viết trước chúng ta đã tìm hiểu tổng quan về Window Functions trong SQL bao gồm tác dụng và cú pháp cũng như phần loại các Window Functions. Để đi vào chi tiết, trong bài viết này SmartData sẽ tiếp tục chia sẻ rõ hơn về nhóm các Aggregate Functions được sử dụng kết hợp với từ khoá OVER để trở thành một Window Function. Các ví dụ của bài viết được thực hiện trên hệ quản trị cơ sở dữ liệu MySQL
Chuẩn bị dữ liệu
Ở bài này chúng ta sẽ thực hiện thao tác trên tập dữ liệu như sau:
Hàm Sum trong Window Function trong SQL
Phần này ta đặt ra đề bài: lấy ra danh sách thông tin category, color, sale_price, tổng tiền bán hàng nhóm theo category
Sử dụng Aggregate Functions thì ta cần phân tích phải xử lý các bước sau:
Bước 1: lấy được thông tin tổng tiền bán hàng theo từng category (gọi tạm là bảng tmp)
Bước 2: lấy thêm các thông tin color, sale_price sau khi join bảng gốc với bảng tmp
SQL> SELECT s.category, s.color, s.sale_price, tmp.sum_sale_price FROM sample s join ( select category, sum(sale_price) as sum_sale_price from sample group by category ) as tmp on s.category = tmp.category ORDER BY categoryKết quả:
Sử dụng Window Functions: ta chỉ cần tính tổng sale_price cho windows function bằng câu lệnh ‘partition by category’ nằm trong OVER như sau:
SQL> SELECT category, color,sale_price, sum(sale_price) OVER (partition by category order by category) AS total_sales FROM sampleKết quả:
Ta có thể thấy window function đã giúp ra tính tổng theo category ngay trên từng dòng mà không cần ta phải tính toán riêng lẻ.
Cả 2 cách sẽ cho cùng 1 kết quả, nhưng bạn thấy đấy sử dụng Window Functions tiện gọn hơn đúng không. Nếu bạn hiểu đc ý nghĩa và cách dùng của Window Function thì tôi cá là các bạn sẽ chọn nó thay vì viết subquery dài như cái bơm.
Hàm Avg trong Window Function
Tương tự với hàm Sum, hàm Avg khi sử dụng kết hợp với OVER sẽ giúp ta tính toán giá trị trung bình của từng nhóm dữ liệu
Ví dụ: lấy danh sách category, color, sale_price, trung bình giá bán hàng theo category
SQL> SELECT category, color,sale_price, avg(sale_price) OVER (partition by category order by category) AS avg_sales FROM sample ;Kết quả:
Hàm Count trong Window Function
Tương tự với hàm Sum và hàm Avg, hàm Count khi sử dụng kết hợp với OVER sẽ giúp ta tính toán giá trị số lượng bản ghi của từng nhóm dữ liệu
Ví dụ: lấy danh sách category, color, số lượng theo category
SQL> SELECT category, color, count(category) OVER (PARTITION by category order by category) AS item_count FROM sample
Ta có thể thấy hàm count ở đây trả về số lượng bản ghi trong cùng một nhóm ở mỗi dòng.
Hàm Min, Max trong Window Function
Các hàm Min, Max khi sử dụng trong Window function cũng vậy, nó sẽ giúp ta trả về ngay giá trị nhỏ nhất và lớn nhất trong nhóm giá trị mà ta quy định
Dưới đây là một ví dụ: để lấy giá trị min, max trường sale_price theo nhóm category
SQL> SELECT category, color, sale_price, min(sale_price) OVER (PARTITION by category ) AS min_sale_price, max(sale_price) OVER (PARTITION by category ) AS max_sale_price FROM sample;Kết quả:
Kết luận
Các hàm sum, avg, … sẽ dẫn mạnh mẽ khi ta sử dụng nó trong Window function, điều này sẽ giúp ta rút ngắn được rất nhiều sự phục tạp khi làm việc.
Nếu bạn là newbie có thể tham khảo bài viết này để tìm hiểu lộ trình học DA trong 3 tháng của SmartData
Nếu bạn thấy bài viết hay và hữu ích, bạn có thể tham gia các kênh sau của SmartData để nhận được nhiều hơn nữa:
