Tiếp tục chuỗi bài SQL trong Data Analysis, ở bài trước chúng ta đã học qua về cách sử dụng các Window Functions ở mức cơ bản. Trong bài này, chúng ta sẽ tiếp tục các Window Functions này ở dạng nâng cao hơn nhé.
Thực hành
ROW_NUMBER
Từ khoá ROW_NUMBER được dùng để đếm các dòng trong cùng một phân nhóm:
select product_id, product_category_name, ROW_NUMBER () OVER (
PARTITION BY product_category_name
ORDER by product_photos_qty
)
from "brazilian-ecommerce".olist_products_dataset;Kết quả:
RANK/DENSE_RANK/PERCENT_RANK
Giống như ROW_NUMBER, từ khoá RANK cũng sẽ gán số xếp hạng cho các dòng trong một phân nhóm. Tuy nhiên, nếu giá trị sắp xếp bằng nhau thì sẽ cùng có số như nhau.
select product_id, product_category_name, product_name_lenght, RANK () OVER (
PARTITION BY product_category_name
ORDER by product_name_lenght
)
from "brazilian-ecommerce".olist_products_dataset;Kết quả
Từ khoá DENSE_RANK tương tự như RANK, tuy nhiên, bạn nhận thấy nếu 2 dòng trước xếp hạng trùng nhau thì thứ hạng tiếp theo sẽ nhảy (như hình trên là từ 6 -> 8). DENSE_RANK sẽ giữ thứ hạng là một dãy số liên tiếp.
select product_id, product_category_name, product_name_lenght, DENSE_RANK () OVER (
PARTITION BY product_category_name
ORDER by product_name_lenght
)
from "brazilian-ecommerce".olist_products_dataset;Kết quả
Với PERCENT_RANK, việc bạn tính tỉ lệ phân bổ % cho mỗi nhóm phân loại sẽ dễ dàng hơn. Lưu ý: phân nhóm đầu tiên hoặc phân nhóm có giá trị null sẽ luôn là 0%.
select product_id, product_category_name, product_name_lenght, PERCENT_RANK () OVER (
PARTITION BY product_category_name
ORDER by product_name_lenght
)
from "brazilian-ecommerce".olist_products_dataset;Kết quả
FIRST_VALUE và LAST_VALUE
Từ khoá FIRST_VALUE sẽ trả về kết quả đầu tiên trong nhóm phân loại, LAST_VALUE sẽ trả kết quả cuối cùng trong nhóm phân loại. Tuy nhiên cách truy vấn sẽ có một chút khác biệt.
select product_id, product_category_name, product_name_lenght, FIRST_VALUE(product_name_lenght) OVER (
PARTITION BY product_category_name
ORDER by product_name_lenght
)
from "brazilian-ecommerce".olist_products_dataset;Kết quả
Bạn cũng có thể dùng câu truy vấn với FIRST_VALUE như thế này, kết quả cũng sẽ tương tự.
select product_id, product_category_name, product_name_lenght, FIRST_VALUE(product_name_lenght) OVER (
PARTITION BY product_category_name
ORDER by product_name_lenght RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
from "brazilian-ecommerce".olist_products_dataset;Lý do: vì mặc định, RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW được sử dụng. Đoạn truy vấn này có nghĩa là tính từ đầu phân nhóm cho tới dòng hiện tại, tức là dòng đang tính. Ví dụ sẽ rõ hơn khi ta đi qua LAST_VALUE.
select product_id, product_category_name, product_name_lenght, LAST_VALUE(product_name_lenght) OVER (
PARTITION BY product_category_name
ORDER by product_name_lenght RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
from "brazilian-ecommerce".olist_products_dataset;
Xem kết quả, chúng ta thấy là cột LAST_VALUE cuối cùng có giá trị sai, đáng lẽ số phải lớn hơn, nhưng nó bằng với giá trị của chính nó. Vậy chúng ta sẽ đổi sang query này nhé:
select product_id, product_category_name, product_name_lenght, LAST_VALUE(product_name_lenght) OVER (
PARTITION BY product_category_name
ORDER by product_name_lenght RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
from "brazilian-ecommerce".olist_products_dataset;
LAG và LEAD
Với từ khoá LAG, chúng ta có thể lấy dữ liệu từ dòng trước đó (nếu là dòng đầu tiên trong phân nhóm thì sẽ là null). Và từ khoá LEAD sẽ lấy dòng dữ liệu tiếp theo (nếu là dòng cuối cũng trong phân nhóm thì sẽ là null).
Chúng ta sẽ thử truy vấn với LAG
select product_id, product_category_name, product_name_lenght, LAG(product_name_lenght, 1) OVER (
PARTITION BY product_category_name
ORDER by product_name_lenght
),
product_name_lenght - LAG(product_name_lenght, 1) OVER (
PARTITION BY product_category_name
ORDER by product_name_lenght
)
from "brazilian-ecommerce".olist_products_dataset;Đoạn truy vấn trên sẽ lấy lấy product_id, product_category_name, length của product đó và length của product trước. Đồng thời lấy length của product hiện tại trừ cho lenght của product trước đó. Kết quả như hình dưới.
Giờ chúng ta sẽ thử với LEAD nhé:
select product_id, product_category_name, product_name_lenght, LEAD(product_name_lenght, 1) OVER (
PARTITION BY product_category_name
ORDER by product_name_lenght
),
product_name_lenght - LEAD(product_name_lenght, 1) OVER (
PARTITION BY product_category_name
ORDER by product_name_lenght
)
from "brazilian-ecommerce".olist_products_dataset;Kết quả
Tổng kết
Qua bài này chúng ta đã học thêm các từ khoá trong Window Functions: ROW_NUMBER, RANK/DENSE_RANK/PERCENT_RANK, FIRST_VALUE và LAST_VALUE, LAG và LEAD.
Tránh tình trạng cần phải viết truy vấn quá phức tạp chỉ để giải quyết các bài toán đã có sẵn giải pháp. Các từ khoá này sẽ hỗ trợ bạn trong việc tính toán dữ liệu khá nhiều. Đặc biệt các bài toán RANKING trong thực tế, sẽ có nhiều yêu cầu từ các bộ phận đưa ra.
Hy vọng bạn sẽ thực hành tốt bài này.
Xin cám ơn.
