Trong quá trình phân tích dữ liệu sử dụng RDBMS, chúng ta thường phải viết những câu query phức tạp đến mức lâu lâu nhìn lại không biết lúc đó mình đã viết những gì như hình bên dưới?

The Finished Dish

Thử tưởng tượng một đoạn code khá phức tạp thì có thể viết thành nhiều method khác nhau để làm giảm độ phức tạp về mặt tổ chức của chương trình. Vậy với SQL liệu chúng ta có thể làm được điều tương tự như vậy không?

Common Table Expression:

Trong SQL có khái niệm gọi là Common Table Expression (CTE) nhằm giúp chúng ta đơn giản hóa trong việc viết những câu query có nhiều joins và sub-queries.

CTE có thể được xem như là một tập dữ liệu tạm chỉ tồn tại trong scope của 1 câu lệnh SELECT, INSERT, UPDATE, DELETE, hoặc CREATE VIEW. Bạn cũng có thể xem CTE là một table tạm có khả năng "self-referencing" và được phép sử dụng nhiều lần trong cùng 1 query.

CTE được dùng để làm gì?

  • Tạo "recursive query": http://r.grokking.org/article-recursive-ctes-explained
  • Thay thế cho việc sử dụng VIEW khi chúng ta không cần tái sử dụng data ở những query khác => không cần lưu metadata
  • Sử dụng kết quả của một query khác nhiều lần trong cùng 1 query
  • Cho phép sử dụng câu lệnh "group by" trên column sinh ra từ "scalar subselect" (subquery có kết quả là duy nhất một column và 1 row)

Example:

  • Dùng sub-query:
-- sub-query style
select m.yyyymm, c.country, c.total
from (
	select yyyymm, country, total, rank() over (partition by yyyymm order by total desc)
	from (
		select to_char(orderdate, 'yyyy-MM') yyyymm, country, sum(totalamount) total
		from customers c join orders o on c.customerid = o.customerid
		where date_part('year', orderdate) = 2009
		group by yyyymm, country
		order by 1, 2, 3
	) as monthly_country_order_amount
) as c
right join (
	select to_char(day, 'yyyy-MM') yyyymm
	from generate_series('2009-01-01', '2010-12-01', interval '1 month') day
) m
on c.yyyymm = m.yyyymm
where rank = 1 or rank is null
  • Dùng CTE:
-- CTE style
WITH monthly_calendar as (

	select to_char(day, 'yyyy-MM') yyyymm
	from generate_series('2009-01-01', '2010-12-01', interval '1 month') day
), monthly_country_order_amount AS (

	select to_char(orderdate, 'yyyy-MM') yyyymm, country, sum(totalamount) total
	from customers c join orders o on c.customerid = o.customerid
	where date_part('year', orderdate) = 2009
	group by yyyymm, country
	order by 1, 2, 3

), monthly_country_rank AS (

	select yyyymm, country, total, rank() OVER (PARTITION BY yyyymm ORDER BY total DESC)
	from monthly_country_order_amount
)

-- find the best selling country by month, if no data was available for this specific month return null for that month
select c.yyyymm, r.country, r.total
from monthly_country_rank r
right join monthly_calendar c
on r.yyyymm = c.yyyymm
where rank = 1 or rank is null
order by 1

References: