Trong thế giới của cơ sở dữ liệu quan hệ, các truy vấn đệ quy (recursive) đóng vai trò quan trọng trong việc giải quyết các vấn đề phức tạp, mà các câu truy vấn không thể giải quyết được. Ví dụ, chúng ta có dữ liệu phân cấp như nhân viên – trưởng nhóm – quản lý v.v. Việc truy vấn nhiều cấp đòi hỏi sẽ trở nên phức tạp khi có nhiều câu hỏi cho một câu truy vấn.
Giới thiệu đệ quy
Hôm nay, chúng ta sẽ tìm hiểu sâu hơn về đệ quy trong PostgreSQL và cách áp dụng nó thông qua ví dụ.
Đệ quy, trong bối cảnh lập trình, là một quy trình trong đó một hàm gọi chính nó làm một trong những bước của nó. Điều này tạo ra một vòng lặp, cho đến khi một điều kiện dừng được đạt được. Trong SQL, đệ quy thường được sử dụng để truy cập các cấu trúc dữ liệu phân cấp hoặc để thực hiện các phép toán động trên tập dữ liệu.
Để tạo một truy vấn đệ quy trong PostgreSQL, chúng ta sử dụng cấu trúc “WITH RECURSIVE”. Cấu trúc cơ bản của một truy vấn đệ quy như sau:
WITH RECURSIVE tên_cấu_trúc_dữ_liệu_dệ_quy AS (
-- Truy vấn cơ sở
UNION
-- Truy vấn đệ quy
)
SELECT * FROM tên_cấu_trúc_dữ_liệu_dệ_quy;Truy vấn cơ sở định nghĩa điểm bắt đầu của quá trình đệ quy, trong khi truy vấn đệ quy là bước tiếp theo của quá trình đó, áp dụng cho mỗi lần lặp.
Thử nghiệm
Chúng ta sẽ tạo một bản dữ liệu gồm dữ liệu bán hàng và nhân viên sales. Mỗi nhân viên có thể sẽ có cấp trên.
CREATE TABLE sales (
sale_id SERIAL PRIMARY KEY,
seller_id INT,
amount NUMERIC
);
CREATE TABLE sellers (
seller_id SERIAL PRIMARY KEY,
manager_id INT
);
-- Populate the sales table
INSERT INTO sales (seller_id, amount)
VALUES (1, 100), (1, 200), (2, 150), (2, 250), (3, 300), (3, 400), (4, 350), (4, 450);
-- Populate the sellers table
INSERT INTO sellers (seller_id, manager_id)
VALUES (1, NULL), (2, 1), (3, 2), (4, 3);
Đề bài: Hãy tính tổng số doanh thu của mỗi người sales, đồng thời thể hiện cấp trên trực tiếp của người sales đó (trừ người số 1, vì manager_id của 1 là null).
WITH RECURSIVE
seller_hierarchy AS (
SELECT seller_id, manager_id
FROM sellers
WHERE manager_id IS NULL
UNION ALL
SELECT s.seller_id, s.manager_id
FROM sellers s
INNER JOIN seller_hierarchy sh ON sh.seller_id = s.manager_id
),
total_sales AS (
SELECT seller_id, SUM(amount) AS total_amount
FROM sales
GROUP BY seller_id
)
SELECT ts.seller_id, sh.manager_id, ts.total_amount
FROM seller_hierarchy sh
LEFT JOIN total_sales ts ON ts.seller_id = sh.seller_id;Kết quả
Vì vậy, qua ví dụ trên, chúng ta có thể thấy rằng đệ quy trong PostgreSQL cho phép chúng ta xử lý dễ dàng các vấn đề tương đối phức tạp với dạng dữ liệu quan hệ cấp (như nhân viên – quản lý).
Lưu ý: sử dụng đệ quy sẽ tạo ra một vòng lặp truy vấn, nếu không kiểm soát tốt sẽ tạo thành vòng lặp truy vấn vô tận hoặc quá nặng. Điều này sẽ gây ảnh hưởng lớn tới các môi trường production.
Thử nghiệm với tệp dữ liệu “brazilian-ecommerce”
Giờ chúng ta sẽ thử nghiệm với tập dữ liệu quen thuộc. Đặt bài toán: phòng marketing mong muốn
- tìm ra các sản phẩm bán liên tục trong suốt 12 tháng,
- đồng thời trả ra doanh thu từng tháng,
- và doanh thu cộng dồn của các tháng trước đó. Ví dụ tháng 2 sẽ có phải có doanh thu dồn của tháng 1 và 2. Tháng 6 sẽ có doanh thu dồn của 1,2,3,5,6.
with recursive monthly_cal as (
select *, total_amount as cum_total_amount from pre_cal where a_month = '2017-01-01'
union all
select (m.a_month + interval '1month') as a_month, m.product_id, p.total_amount,
case when p.total_amount is null then null
else coalesce(p.total_amount, 0) + m.cum_total_amount
end as cum_total_amount
from monthly_cal m
left join pre_cal p on m.product_id = p.product_id and
m.a_month + interval '1month' = p.a_month
where p.a_month is not null
), pre_cal as (
select date_trunc('month', ood.order_purchase_timestamp::timestamp) as a_month,
ooid.product_id,
sum(ooid.price) as total_amount
from "brazilian-ecommerce".olist_orders_dataset ood
left join "brazilian-ecommerce".olist_order_items_dataset ooid
on ood.order_id = ooid.order_id
where date_trunc('year', ood.order_purchase_timestamp::timestamp) = '2017-01-01'
and ood.order_status = 'delivered'
group by 1, 2
)
select * from monthly_cal
where product_id in (select product_id from monthly_cal where a_month = '2017-12-01')Kết quả
Giải thích kết quả:
with recursive monthly_cal: Đoạn truy vấn sẽ khởi tạo truy vấn cơ sở với các product được bán vào tháng 1/2017, đồng thời tạo thêm một cột cộng dồn ở tháng 1 (bằng doanh thu tháng đó).
Sau đó sẽ bắt đầu truy vấn đệ quy, với dữ liệu tháng tiếp theo sẽ bằng tháng trước đó + 1, và liên kết với product (do đề bài chúng ta truy vấn theo product). Nếu có tháng sau đó thì doanh thu sẽ được cộng dồn, nếu không thì doanh thu cộng dồn sẽ bằng null (dùng để loại các product không có thu xuyên suốt.
pre_cal: Dùng để khởi tạo tập dữ liệu cần thiết. Bao gồm lấy đơn hàng đã giao hoàn thành và các product trong đơn hàng đó, và chỉ gói gọn trong năm 2017. Bạn có thể thấy lạ khi pre_cal lại nằm sau monthly_cal, đó là do cú pháp của Postgresql. Recursive phải được khai báo đầu tiên, lúc này thứ tự trong các CTE sẽ không còn quan trọng.
Tổng kết
Chúng ta đã đi sơ qua về đệ quy (recursive) trong PostgreSQL. Thông thường, các bài toán cần phải sử dụng đệ quy thường liên quan đến các dòng dữ liệu có sự liên kết với nhau và giá trị có thể liên quan tới nhau.
Như 02 ví dụ trên:
- Ở ví dụ 1: là sự liên kết giữa nhân viên và cấp quản lý trực tiếp
- Ở ví dụ 2: là sự liên kết giữa các tháng bán được hàng của một product nào đó.
Qua bài này, mình hy vọng các bạn có thể nắm thêm một giải pháp mạnh là đệ quy để có thể bỏ vào hộp công cụ Data Analysis của bản thân.
