Sử dụng Window Function trong PostgreSQL (phần 2)

07 tháng 09, 2019 - 1451 lượt xem

Trong phần trước, chúng ta đã làm quen với Window Function trong PostgreSQL và sử dụng nó trong việc tính mức chênh lệch giá của từng sản phẩm so với mức giá trung bình của nhóm sản phẩm:

select product_groups.group_id, products.product_name, products.price, 
AVG(price) OVER (PARTITION BY product_groups.group_id) AS average_price, 
( products.price - AVG(price) OVER (PARTITION BY product_groups.group_id) ) AS price_diff
from products, product_groups
where products.group_id = product_groups.group_id

Trong bài viết này, chúng ta tiếp tục tìm hiểu các ứng dụng của Window Fuction thông qua các ví dụ.

1. Sắp xếp các sản phẩm thuộc cùng một nhóm

Với các yêu cầu sắp xếp thông thường, ta sử dụng ORDER BY:

SELECT product_groups.group_id, products.product_name, products.price
FROM products, product_groups
WHERE products.group_id = product_groups.group_id
ORDER BY products.price ASC
Sắp xếp tất cả sản phẩm
Sắp xếp tất cả sản phẩm

Lệnh ORDER BY theo giá sản phẩm ở trên đang được áp dụng cho tất cả các sản phẩm. Giả sử yêu cầu đặt ra là sắp xếp các sản phẩm, vẫn là theo mức giá tăng dần, nhưng lần này ta chỉ sắp xếp cho từng nhóm sản phẩm: 

Sắp xếp cho từng nhóm
Sắp xếp cho từng nhóm

Để làm được điều này, ta sử dụng Window Function như sau:

SELECT product_groups.group_id, products.product_name, products.price, 
RANK() OVER (PARTITION BY product_groups.group_id ORDER BY products.price ASC)
FROM products, product_groups
WHERE products.group_id = product_groups.group_id

 thu được kết quả:

Chưa được chính xác 100%
Chưa được chính xác 100%

Chưa được chính xác 100% như mong đợi: 2 sản phẩm Lenovo Thinkpad và Sony VAIO có giá bằng nhau nên được xếp cùng mức 1, tuy nhiên đến lượt sản phẩm Dell Vostro thì lại được xếp mức 3, trong khi mong muốn của chúng ta là nó được xếp mức 2. Do đó, ta sẽ chỉnh sửa một chút câu query ở trên:

SELECT product_groups.group_id, products.product_name, products.price, 
DENSE_RANK() OVER (PARTITION BY product_groups.group_id ORDER BY products.price ASC)
FROM products, product_groups
WHERE products.group_id = product_groups.group_id

thu được kết quả như mong đợi:

Kết quả như mong muốn
Kết quả như mong muốn

Bằng mệnh đề PARTITION BY product_groups.group_id, ta phân chia các sản phẩm ra thành 3 khối (window). Trong mỗi khối, trước tiên ta sắp xếp từng sản phẩm theo giá tăng dần bằng mệnh đề ORDER BY products.price ASC, rồi sau đó áp dụng hàm RANK() và DENSE_RANK() cho từng bản ghi trong mỗi khối.

2. Lấy giá cao nhất của mỗi nhóm sản phẩm

Ta sử dụng hàm FIRST_VALUE() kết hợp với phân nhóm dữ liệu theo group_id (PARTITION BY product_groups.group_id) và sắp xếp dữ liệu trong từng nhóm (ORDER BY products.price DESC)

SELECT product_groups.group_id, products.product_name, products.price,
FIRST_VALUE(price) OVER (
   PARTITION BY product_groups.group_id ORDER BY products.price DESC
)
FROM products, product_groups
WHERE products.group_id = product_groups.group_id

Trong mỗi nhóm dữ liệu được phân theo product_groups.group_id, sản phẩm được sắp xếp theo giá giảm dần, do đó để lấy ra sản phẩm có giá lớn nhất trong từng nhóm, ta truyền vào hàm FIRST_VALUE cột price, khi đó hàm FIRST_VALUE(price) sẽ lấy ra giá trị đầu tiên (cũng là giá trị lớn nhất) trong mỗi nhóm

Lấy FIRST_VALUE trong từng nhóm
Lấy FIRST_VALUE trong từng nhóm

Bên cạnh hàm FIRST_VALUE() lấy ra giá trị đầu tiên trong mỗi nhóm, ta còn có hàm LAST_VALUE() để lấy ra giá trị cuối cùng trong nhóm, cách sử dụng tương tự hàm FIRST_VALUE()

3. So sánh giá của sản phẩm liền kề

Ta sử dụng hàm LAG() để lấy giá của sản phẩm liền trước:

SELECT product_groups.group_id, products.product_name, products.price, 
LAG(price,1) OVER (
	PARTITION BY product_groups.group_id ORDER BY products.price ASC
) AS previous_price
FROM products, product_groups
WHERE products.group_id = product_groups.group_id
Lấy giá sản phẩm liền trước
Lấy giá sản phẩm liền trước

Các sản phẩm đứng đầu mỗi nhóm có previous_price là null do không có sản phẩm nào đứng trước nó. Để thay giá trị null bằng 0, ta truyền thêm tham số vào hàm LAG:

SELECT product_groups.group_id, products.product_name, products.price, 
LAG(price,1,0::numeric) OVER (
	PARTITION BY product_groups.group_id ORDER BY products.price ASC
) AS previous_price
FROM products, product_groups
WHERE products.group_id = product_groups.group_id
Thay null bằng 0 với các dòng đầu tiên của mỗi nhóm
Thay null bằng 0 với các dòng đầu tiên của mỗi nhóm

Để lấy giá của sản phẩm liền sau, thay vì dùng hàm LAG() thì ta sẽ dùng hàm LEAD(), cách sử dụng tương tự.


Sau đây là 1 phút dành cho quảng cáo :v

Techmaster đang có Lộ trình đào tạo Full-Stack Developer trong 8 tháng, đào tạo từ cơ bản đến nâng cao, phù hợp cho các bạn chưa có kiến thức nền tảng về lập trình, các bạn học trái ngành hoặc chuyển nghề. Trung tâm cam kết việc làm cho các bạn học viên tốt nghiệp. Học viên đã tốt nghiệp sau 2 tháng nếu vẫn không xin được việc thì sẽ được hoàn lại 100% học phí.

Bình luận

avatar
Nguyễn Hàn Duy 2020-03-19 13:18:05.334009 +0000 UTC
+1
Avatar
* Vui lòng trước khi bình luận.
Ảnh đại diện
  +2 Thích
+2