SQL trong Data Analysis: Cơ bản về Window Functions

Xin chào các bạn đã quay lại với chuỗi bài SQL trong Data Analysis. Ở bài trước chúng ta đã học về các từ khóa truy vấn dữ liệu (GROUP BY, ORDER BY, HAVING) cũng như tính toán dữ liệu (Aggregation Functions: COUNT, AVG, SUM, MAX, MIN).

Ở bài này, chúng ta sẽ nói về các từ khóa nâng cao, và cũng khá quan trọng trong Data Analysis, thuộc về nhóm Window Functions.

Giới thiệu Window Functions

Để hiểu rõ Window Functions, chúng ta sẽ thử làm sử dụng SUM từ Aggregation Functions nhé. Các Aggregation Functions sẽ nhóm dữ liệu từ nhiều dòng vào một dòng.

select payment_type , avg(payment_value) from "brazilian-ecommerce".olist_order_payments_dataset ooid group by 1;

Kết quả

Như bạn thấy ở trên, AVG tính trung bình tiền của tất cả payment_type, và trả kết quả ứng với số lượng payment_type. Với Window Functions cũng sẽ tương tự về cách tính, tuy nhiên nó sẽ không trả về số lượng dòng ứng với payment_type ở AVG(), mà sẽ trả về toàn bộ (do chưa có WHERE).

select payment_type, payment_value, AVG (payment_value) over (partition by payment_type)
from "brazilian-ecommerce".olist_order_payments_dataset 
group by 1,2;

Kết quả

Cú pháp Window Functions

window_function(arg1, arg2,..) OVER (
   [PARTITION BY partition_expression]
   [ORDER BY sort_expression [ASC | DESC] [NULLS {FIRST | LAST }])  

window_function(arg1,arg2,…)

Là tên của Window Functions, trong cấu trúc này, một window function có thể không nhận tham số nào (như COUNT).

Từ khóa PARTITION BY

Sẽ phân dữ liệu thành các nhóm dựa trên điều kiện partition_expression. Nếu không sử dụng, câu truy xuất sẽ nhóm toàn bộ dữ liệu thành một.

Từ khóa ORDER BY

Từ khóa xác định thứ tự các hàng trong mỗi phân vùng mà chức năng cửa sổ được áp dụng. Từ khóa ORDER BY có thể tùy chọn sử dụng NULLS FIRST hoặc NULLS LAST để chỉ định các giá trị có thể rỗng có nên được đưa lên đầu tiên hay cuối cùng trong tập kết quả. Mặc định là tùy chọn NULLS LAST.

frame_clause

Một frame_clause được xác định là một tập các dòng dữ liệu trong phân vùng hiện tại mà functions được áp dụng. Tập con này được gọi là khung (frame). Định nghĩa này sẽ được mở rộng ở các bài sau.

Một số cách sử dụng Window functions

Nếu bạn muốn sử dụng nhiều WF trong cùng một query

SELECT
    <window_function_1>() OVER(PARTITION BY c1 ORDER BY c2),
    <window_function_2>() OVER(PARTITION BY c1 ORDER BY c2)
FROM table_name;

Thì bạn cũng có thể sử dụng câu lệnh như sau, kết quả sẽ tương tự

SELECT 
   <window_function_1>() OVER w,
   <window_function_2>() OVER w,
FROM table_name
WINDOW w AS (PARTITION BY c1 ORDER BY c2);

Thực tế: việc viết câu truy xuất sẽ thường rất dài, có khi lên đến 1000 dòng là chuyện bình thường. Chúng ta có thể rút ngắn truy xuất để dễ dàng đọc hiểu sau này hơn.

Các Window Functions cơ bản

Bao gồm AVG, MAX, MIN, SUM, COUNT.

select order_id, payment_type, payment_value, AVG(payment_value) over w, max(payment_value) over w,
min(payment_value) over w, sum(payment_value) over w, count(payment_value) over w
from "brazilian-ecommerce".olist_order_payments_dataset
window w as (partition by payment_type)

Kết quả

Tóm tắt

Ở bài này chúng ta đã cơ bản biết thêm về Window Functions. Chức năng này của SQL sẽ giúp chung ta rất nhiều trong việc xây dựng các dữ liệu báo cáo. Thay vì chúng sẽ phải xây dựng từ nhiều câu truy vấn, thì với Window Functions chúng ta có thể rút ngắn số lượng truy vấn cần thiết.

Ở bài tiếp theo chúng ta sẽ đi sâu về các từ khóa khác trong nhóm Window Functions này.

Xin cám ơn.