SQL trong Data Analysis: Sử dụng CTE (Common Table Expression)

Xin chào các bạn đã quay lại chuỗi bài SQL trong Data Analysis. Ở bài này, chúng ta sẽ tập làm quen sử dụng Common Table Expression (CTE) trong SQL.

Giới thiệu

Common Table Expression là một công cụ trong SQL cho phép bạn tạo một tên bảng tạm thời và sử dụng nó trong câu truy vấn hiện tại. CTE có thể được sử dụng để xây dựng các truy vấn phức tạp, thường đi kèm với các câu lệnh SELECT, INSERT, UPDATE hoặc DELETE.

Cú pháp của CTE như sau:

WITH cte_name (column_list) AS (
    CTE_query_definition 
)
statement;

Thực hành

Làm quen

Chúng ta sẽ thử sử dụng CTE vào câu truy vấn đơn giản như sau: Lấy các reviews gắn liền với các đơn hàng được trong năm 2017. Lưu ý: thực tế đơn được đặt năm 2017, thì khách hàng vẫn có thể để lại review ở năm 2017, hoặc thậm chí 2019, tuỳ theo hệ thống “linh động” như thế nào.

with only_2017 as (
	select * from "brazilian-ecommerce".olist_orders_dataset as d
	where extract(year from d.order_purchase_timestamp::date) = '2017'
)
select * from "brazilian-ecommerce".olist_order_reviews_dataset oord  
	inner join only_2017 as ol on oord.order_id = ol.order_id;

Kết quả

Nếu bạn để ý, sẽ thấy yêu cầu truy vấn sẽ tương tự như đoạn SQL dưới đây:

select * from "brazilian-ecommerce".olist_orders_dataset as d
	inner join "brazilian-ecommerce".olist_order_reviews_dataset as o 
	on o.order_id = d.order_id
where extract(year from d.order_purchase_timestamp::date) = '2017'

Thực ra, mục tiêu của CTE được dùng để đơn giản hoá việc viết SQL đối với câu lệnh truy vấn JOINs trên nhiều bảng hoặc các SubQueries phức tạp.

Vấn đề nâng cao

Ví dụ như đoạn dưới đây, với các viết truy vấn CTE thì chúng ta sẽ thấy cách viết sẽ đơn giản và dễ hiểu hơn hẳn. Đề bài: phòng trải nghiệm khách hàng (CX) muốn tìm hiểu các yếu tố nào ảnh hưởng tới lượt review thấp, trong đó có giả thuyết thời gian giao hàng quá lâu sẽ ảnh hưởng trực tiếp tới sự hài lòng của khách hàng.

with only_deliveried_2017 as (
	select *, EXTRACT(epoch from (d."order_delivered_customer_date"::timestamp - d."order_purchase_timestamp"::timestamp))/3600 as hours_2_d
	from "brazilian-ecommerce".olist_orders_dataset as d
	where extract(year from d.order_purchase_timestamp::date) = '2017'
	and d.order_delivered_customer_date <> ''
), review_lt_3 as (
	select * from "brazilian-ecommerce".olist_order_reviews_dataset oord  
		inner join only_deliveried_2017 as ol on oord.order_id = ol.order_id
		where oord.review_score < 3
), review_gt_3 as (
	select * from "brazilian-ecommerce".olist_order_reviews_dataset oord  
	inner join only_deliveried_2017 as ol on oord.order_id = ol.order_id
	where oord.review_score > 3
)
select avg(review_lt_3.hours_2_d) as lt_3_avg, avg(review_gt_3.hours_2_d) as gt_3_avg
from review_lt_3, review_gt_3;

Kết quả

Đoạn truy vấn trên tính tập dữ liệu đơn hàng xuất hiện trong 2017, và tính trung bình tỉ lệ reviews với nhóm đơn hàng 2017 thành 02 chỉ số cho nhóm dưới 03 và nhóm trên 03. Như vậy, nếu không dùng CTE, chúng ta phải viết 02 câu truy vấn khác nhau (thay vào chỗ from review_lt_3, review_gt_3, và tệp đơn hàng 2017 sẽ phải xuất hiện 02 lần).

Fact 1: để trả lời được một câu hỏi đơn giản như thế, chúng ta phải chuẩn bị dữ liệu rất nhiều. Và thậm chí đoạn truy vấn trên cũng tốn thời gian để tính toán (Ở trên máy bàn CPU i7 đời thứ 12 và 32GB RAM vẫn cần tới ~25 giây để tính toán xong, nếu máy tính các bạn không đủ mạnh, có thể không cần chạy truy vấn này, tuy nhiên mình khuyến khích nên chạy và thử nghiệm).

Fact 2: để chứng minh sự liên hệ giữa 02 chỉ số với nhau, chúng ta có thể tính tỉ lệ thuận/nghịch không thay đổi của 02 chỉ số này. Trong trường hợp này chúng ta thấy để trung bình reviews trên 3 thì thời gian giao hàng trung bình phải ít hơn 281 giờ và ngược lại. Để đảm bảo, chúng ta có thể sẽ phải tính lại trên nhiều điều kiện dữ liệu khác nhau, ví dụ 2018 hoặc chỉ một nhóm hàng nhất định.

Thực hành + Ôn tập

Lần này chúng ta sẽ thử kết hợp với Window Functions đã học trước đó. Đặt giả sử, chúng ta cho là cận nặng của sản phẩm sẽ ảnh hưởng tới việc mua hàng. Cho nên chúng ta sẽ cần chạy dữ liệu để chứng minh nếu cân nặng của sản phẩm tỷ lệ thuận/tỷ lệ nghịch với số lượng mua.

with product_weight_ranking as (
	select product_id, product_weight_g, CUME_DIST() OVER (
			ORDER by product_weight_g 
		) as weight_ranking
	from "brazilian-ecommerce".olist_products_dataset
),
sale_data as (
	select product_id, count(product_id) as sold_count 
	from "brazilian-ecommerce".olist_order_items_dataset group by 1
),
ranking_product_sale as (
	select product_id, sold_count, cume_dist () over (
		order by sold_count
	) as sale_ranking from sale_data
)
select s.product_id, sold_count, sale_ranking, product_weight_g, weight_ranking from ranking_product_sale s
	inner join product_weight_ranking w on s.product_id = w.product_id;

Kết quả

Kết luận: Chà có vẻ không phải cái gì chúng ta nghĩ cũng đúng nhỉ.

Tổng kết

Qua bài này chúng ta đã học thêm về Common Table Expression (CTE). Một số lợi ích khi sử dụng CTE bao gồm: giúp chúng ta dễ sắp xếp và đọc code hơn, giúp chúng ta tái sử dụng những đoạn truy vấn trùng, và còn là một trong những cách truy vấn đệ quy (ví dụ, cùng 01 bảng dữ liệu nhân viên, cần sắp xếp kiếm theo cấp bậc: giám đốc, quản lý, nhân viên).

Ngoài ra, chúng ta cũng đã đi “dạo” một vòng công việc của một Data Analyst thực thụ. Khi chúng ta đã thử rút trích “insight” từ dữ liệu thô. Như tỉ lệ mức độ hài lòng liên quan tới thời gian giao hàng, và cân nặng của sản phẩm không liên quan gì số lượng bán.

Xin cám ơn.