CTE trong SQL: Một số ứng dụng nâng cao trong phân tích

Nếu bạn đang hướng đến việc trở thành một Data Analyst hoặc Business Intelligence (BI) Developer, thì việc hiểu và sử dụng thành thạo lệnh WITH (Common Table Expression – CTE) vào các nhu cầu phân tích nâng cao là một trong những kỹ năng quan trọng và không thể thiếu. Hãy cùng DATAPOT tìm hiểu bài viết dưới đây để nắm rõ hơn về các ứng dụng nâng cao của CTE trong SQL bạn nhé!

CTEs là gì?

Common Table Expression (CTEs) là một tính năng quan trọng trong SQL, cho phép người dùng đặt tên và sử dụng một bảng tạm thời trong phạm vi của một truy vấn cụ thể. CTEs được sử dụng trong việc xử lý các câu truy vấn phức tạp, thường kết hợp với các câu lệnh SELECT, INSERT, UPDATE hoặc DELETE giúp tăng tính rõ ràng, dễ đọc hiểu và quản lý các đoạn mã SQL.

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

So sánh CTEs và các kỹ thuật khác trong SQL

CTEs với các truy vấn lồng (Sub-queries)

CTEs với bảng tạm (Temp Table)

Các loại CTE trong SQL

Non-Recursive CTEs

CTEs không đệ quy (Non-Recursive CTEs) được sử dụng để đơn giản hóa các truy vấn phức tạp, giúp người dùng đọc hiểu mã dễ dàng hơn. Chúng hoạt động như một tập hợp kết quả tạm thời, cho phép các người dùng chia nhỏ và sắp xếp các phần khác nhau của truy vấn một cách hợp lý, có thể được tham chiếu sau trong cùng một truy vấn.

Recursive CTEs

CTEs đệ quy (Recursive CTEs) là một công cụ mạnh mẽ cho phép  xử lý các cấu trúc dữ liệu có tính chất phân cấp hoặc lặp lại, ví dụ như duyệt cây genealogy hoặc tìm đường đi ngắn nhất trong biểu đồ. CTEs đệ quy được chia thành hai phần: phần ban đầu, không đệ quy (được gọi là “thành viên neo”), và phần đệ quy (được gọi là “thành viên đệ quy”), cả hai đều tham chiếu đến chính CTE.

Ví dụ: Ứng dụng CTE đệ quy tìm kiếm các thành phần phụ thuộc trong cơ cấu tổ chức của một công ty:

CREATE TABLE Employees (
  EmployeeID INT PRIMARY KEY,
  EmployeeName VARCHAR(255),
  ManagerID INT
);

INSERT INTO Employees (EmployeeID, EmployeeName, ManagerID)
VALUES (1, 'CEO', NULL),
       (2, 'VP of Sales', 1),
       (3, 'Sales Manager', 2),
       (4, 'Sales Representative', 3),
       (5, 'VP of Engineering', 1),
       (6, 'Engineering Manager', 5),
       (7, 'Software Engineer', 6);
-- Tạo CTE đệ quy để tạo cây tổ chức
WITH RecursiveOrg AS (
  SELECT EmployeeID, EmployeeName, ManagerID, 0 AS Level
  FROM Employees
  WHERE ManagerID IS NULL
  UNION ALL
  SELECT E.EmployeeID, E.EmployeeName, E.ManagerID, R.Level + 1
  FROM Employees AS E
  JOIN RecursiveOrg AS R ON E.ManagerID = R.EmployeeID
)
-- Truy vấn cây tổ chức
SELECT EmployeeID, EmployeeName, ManagerID, Level
FROM RecursiveOrg
ORDER BY Level, EmployeeID;

Trong đó:

“Thành viên neo” bao gồm các hàng trong bảng Employees mà có ManagerID bằng NULL. Điều này tương ứng với CEO – đại diện cho điểm khởi đầu của quá trình đệ quy.

“Thành viên đệ quy” chứa tất cả các nhân viên (bao gồm các cấp bậc) trong tổ chức công ty – được tạo ra bằng các kết hợp thông tin trong bảng Employees và dựa trên quan hệ ManagerID để xác định cấp bậc cho mỗi nhân viên.

CTE RecursiveOrg để bắt đầu từ CEO và sau đó lặp đi lặp lại qua các cấp bậc của cơ cấu tổ chức. Kết quả là một bảng chứa tất cả các nhân viên với thông tin về cấp bậc của họ trong tổ chức.

Một số ứng dụng nâng cao của CTEs trong phân tích

Trong bài viết này, chúng ta sẽ xem ví dụ với các thông tin của bảng và dữ liệu như sau:

Thông tin các bảng:

CREATE TABLE sales_data (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id VARCHAR(100),
    total_amount DECIMAL(10, 2)
);

CREATE TABLE relationships (
    user_id INT,
    friend_id INT
);

CREATE TABLE text_data (
    text_id INT PRIMARY KEY,
    text_content TEXT
);

Dữ liệu trong các bảng:

INSERT INTO sales_data (order_id, order_date, customer_id, total_amount)
VALUES
    (1, '2023-01-15', 'Cust123', 100.50),
    (2, '2023-01-18', 'Cust456', 75.25),
    (3, '2023-02-10', 'Cust789', 150.00),
    (4, '2023-02-15', 'Cust123', 200.75),
    (5, '2023-03-05', 'Cust456', 50.00),
    (6, '2023-03-20', 'Cust789', 125.50),
    (7, '2023-04-10', 'Cust123', 90.25),
    (8, '2023-04-15', 'Cust456', 300.00),
    (9, '2023-05-02', 'Cust789', 75.75),
    (10, '2023-05-20', 'Cust123', 150.00);

INSERT INTO relationships (user_id, friend_id)
VALUES
    (1, 2),
    (1, 3),
    (2, 4),
    (3, 5),
    (4, 6),
    (5, 7),
    (6, 8),
    (7, 9),
    (8, 10),
    (9, 10);

INSERT INTO text_data (text_id, text_content)
VALUES
    (1, 'Common Table Expressions (CTE) là một công cụ mạnh mẽ trong SQL.'),
    (2, 'CTE cho phép bạn tạo tên bảng tạm thời và sử dụng nó trong câu truy vấn.'),
    (3, 'CTE có thể được sử dụng để xây dựng các truy vấn phức tạp và tính toán.'),
    (4, 'Sử dụng CTE để phân tích và biến đổi dữ liệu văn bản là một ứng dụng phổ biến.');

Làm sạch, biến đổi dữ liệu

Với CTEs, người dùng có thể tạo các bước làm sạch và biến đổi dữ liệu phức tạp trong phục vụ cho từng nhu cầu phân tích dữ liệu.

Ví dụ: Loại bỏ các dòng trùng lặp, thay thế giá trị null, tính toán các chỉ số phức tạp.

WITH CleanedSalesData AS (
    -- CTE để làm sạch dữ liệu
    SELECT
        order_id,
        customer_id,
        TO_DATE(order_date, 'YYYY-MM-DD') AS cleaned_order_date,
        CASE
            WHEN total_amount < 0 THEN 0
            ELSE total_amount
        END AS cleaned_total_amount
    FROM
        sales_data
)
-- Truy vấn chính sử dụng dữ liệu đã làm sạch
SELECT
    order_id,
    customer_id,
    cleaned_order_date,
    cleaned_total_amount
FROM
    CleanedSalesData
WHERE
    cleaned_total_amount > 0;

Cụ thể, CTE CleanedSalesData sẽ thực hiện như sau:

Sử dụng hàm TO_DATE để chuẩn hóa giá trị trong cột order_date thành định dạng ngày tháng năm chuẩn.

Bên cạnh đó, mệnh đề CASE…WHEN đảm bảo rằng giá trị cột total_amount không bao giờ có giá trị âm. Nếu giá trị nhỏ hơn 0, sẽ gán giá trị bằng 0.

Cuối cùng, trong truy vấn chính, chúng ta sẽ truy xuất dữ liệu từ CTE CleanedSalesData để lấy ra các dòng có giá trị cleaned_total_amount lớn hơn 0.

Phân tích chuỗi thời gian

Xét bảng sales_date, với các cột order_id, order_date, và total_amount

Vấn đề cần giải quyết: Sử dụng CTE phân tích tổng doanh số bán hàng theo tháng và tổng doanh số bán hàng hàng tháng trung bình trong năm.

WITH MonthlySalesData AS (
    -- CTE để phân tích chuỗi thời gian
    SELECT
        EXTRACT(YEAR FROM order_date) AS year,
        EXTRACT(MONTH FROM order_date) AS month,
        SUM(total_amount) AS monthly_total
    FROM
        sales_data
    GROUP BY
        year,
        month
),
AverageMonthlySales AS (
    -- CTE để tính tổng doanh số trung bình hàng tháng trong năm
    SELECT
        year,
        AVG(monthly_total) AS average_monthly_sales
    FROM
        MonthlySalesData
    GROUP BY
        year
)
-- Truy vấn chính sử dụng dữ liệu đã phân tích
SELECT
    year,
    month,
    monthly_total,
    average_monthly_sales
FROM
    MonthlySalesData
JOIN
    AverageMonthlySales ON MonthlySalesData.year = AverageMonthlySales.year
ORDER BY
    year,
    month;

Cụ thể:

CTE MonthlySalesData sẽ phân tích dữ liệu theo năm và tháng, tính tổng doanh số hàng tháng.

CTE AverageMonthlySales sẽ tính tổng doanh số trung bình hàng tháng trong năm bằng cách sử dụng dữ liệu từ MonthlySalesData.

Cuối cùng, trong truy vấn chính, chúng ta sẽ kết hợp dữ liệu từ 2 CTE trên sẽ giải quyết được nhu cầu phân tích trên.

Phân tích đồ thị

Đối với ứng dụng này, thường dựa vào dữ liệu có cấu trúc dạng đồ thị, trong đó các yếu tố hoặc đối tượng có mối quan hệ với nhau.

Xét bảng relationships lưu trữ thông tin về mối quan hệ giữa các đối tượng, ví dụ: người dùng và bạn bè của họ. Bảng này có hai cột: user_id và friend_id, trong đó user_id là người dùng và friend_id là người bạn của họ.

Vấn đề cần giải quyết: Sử dụng CTE để tìm tất cả các người bạn cấp 2 của một người dùng cụ thể: người bạn của bạn bè của họ.

WITH FriendLevel1 AS (
    -- CTE để lấy tất cả bạn bè của người dùng cấp 1
    SELECT DISTINCT friend_id AS friend_level_1
    FROM relationships
    WHERE user_id = 1  -- Thay đổi '1' thành người dùng cụ thể bạn quan tâm
),
FriendLevel2 AS (
    -- CTE để lấy tất cả bạn bè của người dùng cấp 2
    SELECT DISTINCT r.friend_id AS friend_level_2
    FROM relationships r
    JOIN FriendLevel1 f1 ON r.user_id = f1.friend_level_1
    WHERE r.friend_id <> 1  -- Loại trừ người dùng cụ thể
)
-- Truy vấn chính sử dụng dữ liệu đã phân tích
SELECT DISTINCT f2.friend_level_2 AS friend_of_friend
FROM FriendLevel2 f2
WHERE f2.friend_level_2 NOT IN (SELECT DISTINCT friend_level_1 FROM FriendLevel1);

Cụ thể:

CTE FriendLevel1 sẽ lấy tất cả bạn bè của người dùng cấp 1 (người bạn trực tiếp).

CTE FriendLevel2 sẽ lấy tất cả bạn bè của người dùng cấp 2 (người bạn của bạn bè của họ) bằng cách kết hợp dữ liệu từ bảng relationships và dữ liệu từ CTE FriendLevel1.

Cuối cùng, trong truy vấn chính, chúng ta sẽ sử dụng toán tử NOT IN để loại những kết quả là bạn bè cấp 1, kết quả cuối cùng chính là danh sách bạn bè cấp 2 của người dùng.

Phân tích chuỗi văn bản

Xét bảng text_data chứa các đoạn văn bản.

Vấn đề cần giải quyết: Sử dụng CTE để tìm các từ xuất hiện nhiều lần trong các đoạn văn bản đó.

WITH WordCounts AS (
    -- CTE để đếm từ xuất hiện
    SELECT
        text_id,
        word,
        COUNT(*) AS count
    FROM (
        SELECT
            text_id,
            regexp_split_to_table(text_content, E'\\s+') AS word
        FROM
            text_data
    ) AS words
    GROUP BY
        text_id, word
)
-- Truy vấn chính sử dụng dữ liệu đã phân tích
SELECT
    text_id,
    word,
    count
FROM
    WordCounts
ORDER BY
    text_id, count DESC;

Cụ thể:

CTE WordCounts có chức năng đếm từ xuất hiện trong đoạn văn bản kết hợp với hàm regexp_split_to_table để chia các đoạn văn bản thành từng từ riêng lẻ.

Trong truy vấn chính, chúng ta sẽ truy xuất dữ liệu từ CTE WordCounts để lấy kết quả của việc đếm từ xuất hiện.

Kết luận

Bài viết này đã giới thiệu về ưu điểm của CTEs so với các kỹ thuật khác trong SQL, cũng như nêu ra một vài ví dụ về các ứng dụng phân tích nâng cao mà CTEs có thể giải quyết được. Hi vọng bài viết này đã cung cấp cho các bạn thêm những kinh nghiệm mới, có thể ứng dụng để giải quyết các bài toán phức tạp.

Các bạn có thể đón đọc các bài hướng dẫn khác của Datapot về SQL tại đây.

Nếu như bạn đang tìm kiếm khóa học SQL cơ bản hay nâng cao, hãy tham khảo ngay hai khóa học dưới đây của Datapot:

Khóa học DP-080: Querying Data with Microsoft T-SQL: Khóa học tiêu chuẩn của Microsoft, được thiết kế để cung cấp các khái niệm và ứng dụng cơ bản trong phân tích dữ liệu của ngôn ngữ SQL.

Khóa học Advanced Query with T-SQL: Khóa học được thiết kế bởi Datapot nhằm hệ thống hóa, cung cấp các bài tập thực hành và kỹ năng nâng cao cho học viên muốn nằm sâu hơn về SQL nói chung, Transact SQL nói riêng.