Giải thích câu lệnh CASE trong SQL

Nếu bạn từng làm việc với cơ sở dữ liệu, hẳn bạn đã nhận ra dữ liệu hiếm khi hoàn hảo và thường cần được xử lý để rút ra những insight có ý nghĩa.

Để đáp ứng nhu cầu này, SQL cung cấp một cấu trúc mạnh mẽ gọi là câu lệnh CASE, cho phép bạn tạo cột mới hoặc biến đổi dữ liệu dựa trên các điều kiện áp dụng cho các cột hiện có.

Trong bài viết này, chúng tôi sẽ giải thích câu lệnh CASE là gì, vì sao nó quan trọng và khám phá nhiều trường hợp sử dụng để giúp bạn khai thác hết tiềm năng của nó. Bắt đầu nhé!

CASE Statement là gì?

Câu lệnh CASE trong SQL là một biểu thức có điều kiện, đưa logic ra quyết định vào truy vấn của bạn. Nó hoạt động tương tự như câu lệnh if-elif-else trong Python, cho phép bạn đánh giá nhiều điều kiện và trả về kết quả cụ thể dựa trên các điều kiện đó. 

Cú pháp tổng quát như sau:

CASE
	WHEN boolean_condition1 THEN result1
	WHEN boolean_condition2 THEN result2
	ELSE result3
END

Cách thức hoạt động:

• Từ khóa CASE đánh dấu phần bắt đầu của biểu thức, trong khi END đánh dấu phần kết thúc, bao lấy câu điều kiện như một khung.
• Mỗi mệnh đề WHEN đánh giá một điều kiện. Nếu điều kiện đúng, mệnh đề THEN tương ứng sẽ trả về kết quả được chỉ định.
• Nếu không có điều kiện nào trong các mệnh đề WHEN thỏa mãn, mệnh đề ELSE cung cấp một kết quả mặc định.

Vì sao câu lệnh CASE quan trọng?

Câu lệnh CASE WHEN là một công cụ hữu ích trong các truy vấn SQL, mang lại một số lợi ích chính:

• Biến đổi dữ liệu: cho phép bạn tạo các cột mới dựa trên giá trị của các cột hiện có.
• Gộp dữ liệu có điều kiện: cho phép bạn tạo các giá trị tổng hợp (ví dụ: tổng, đếm, giá trị lớn nhất) cho các tập dữ liệu con dựa trên điều kiện.
• Lọc dữ liệu: bạn có thể bao gồm hoặc loại trừ các hàng dựa trên tiêu chí cụ thể.
• Đơn giản hóa truy vấn và tăng khả năng đọc: giúp hợp nhất logic vào một truy vấn duy nhất, giảm nhu cầu dùng nhiều truy vấn, bảng tạm hoặc các phép join phức tạp.
• Tối ưu hiệu suất: bằng cách giảm độ phức tạp của truy vấn SQL, câu lệnh CASE cũng có thể góp phần tối ưu hiệu năng và giảm thời gian thực thi, đặc biệt khi tránh được các phép tính và biến đổi bổ sung.

CASE Statement đơn giản

Trong bài viết này, chúng ta sẽ sử dụng bảng film của cơ sở dữ liệu cinema. Giả sử chúng ta muốn tạo một cột phân loại mới dựa trên giá trị ROI (tỷ lệ gross/budget):

SELECT 
	title,
	gross,
	budget,
	gross / budget AS ROI,
CASE
	 WHEN gross / budget < 1 THEN 'low ROI'
	 WHEN gross / budget BETWEEN 1 AND 2 THEN 'medium ROI'
	 ELSE 'high ROI'
END as ROI_group
FROM cinema.films
WHERE budget > 0 AND gross > 0 AND release_year = 2015;

Với gross và budget, chúng ta đã tính ROI, đây là một chỉ số phổ biến về khả năng sinh lời, đo lường thu nhập tạo ra từ một khoản đầu tư.

Bên cạnh trường ROI, chúng ta cũng thu được phần phân loại để truyền đạt rõ ràng hơn:

• Nếu phim có ROI nhỏ hơn 1, giá trị là “low ROI”
• Các phim có ROI từ 1 đến 2 thuộc nhóm “medium ROI”
• “High ROI” là nhãn gán cho các phim có ROI lớn hơn hoặc bằng 2.

Chúng ta cũng đã lọc bỏ các phim có budget hoặc gross bằng 0 hay âm. Ngoài ra, kết quả được giới hạn ở các phim phát hành năm 2015.

Dùng CASE trong mệnh đề GROUP BY

Ở ví dụ đầu tiên, chúng ta đã tạo một trường mới, có thể dùng để gộp dữ liệu và thu được thêm insight.

Chẳng hạn, chúng ta có thể tính gross trung bình và budget trung bình cho từng nhóm ROI:

SELECT 
CASE
	 WHEN gross / budget < 1 THEN 'low ROI'
	 WHEN gross / budget BETWEEN 1 AND 2 THEN 'medium ROI'
	 ELSE 'high ROI'
END AS ROI_group,
avg(gross) as avg_gross,
avg(budget) as avg_budget
FROM cinema.films
WHERE budget > 0 AND gross > 0 AND release_year = 2015
GROUP BY
CASE
	 WHEN gross / budget < 1 THEN 'low ROI'
	 WHEN gross / budget BETWEEN 1 AND 2 THEN 'medium ROI'
	 ELSE 'high ROI'
END
ORDER BY avg(gross) DESC
;

Để nhóm dữ liệu, chúng ta chỉ cần đặt câu lệnh CASE vào mệnh đề GROUP BY. Để có thêm insight, chúng ta sắp xếp các hàng theo thứ tự giảm dần dựa trên gross trung bình. Bằng cách này, ta thấy gross trung bình cao nhất được gán cho nhóm high ROI và ngược lại với nhóm low ROI.

Dùng CASE trong mệnh đề WHERE

Một lợi ích khác của câu lệnh CASE là cho phép lọc các hàng dựa trên điều kiện cụ thể. Ví dụ, chúng ta có thể áp dụng các ngưỡng ROI khác nhau tùy theo ngôn ngữ của phim. Người ta biết rằng các phim tiếng Anh thường có ROI cao. Vì vậy, ngưỡng nên cao hơn, trong khi ta giả định các ngôn ngữ khác có ROI thấp hơn.

SELECT 
    title,
    gross,
    budget,
    language,
    gross / budget AS ROI
FROM cinema.films
WHERE 
    CASE 
        WHEN language = 'English' THEN gross / budget > 2 -- English films need ROI > 2
        WHEN language = 'French' THEN gross / budget > 1.5 -- French films need ROI > 1.5
        ELSE gross / budget > 1.3 -- Default for other languages
    END;

Tính toán tổng hợp

Chúng ta cũng có thể quan tâm đến việc đếm số phim có ROI cao so với thấp phát hành năm 2015:

SELECT 
    SUM(CASE WHEN gross / budget > 2 THEN 1 ELSE 0 END) AS high_roi_films,
    SUM(CASE WHEN gross / budget BETWEEN 1 AND 2 THEN 1 ELSE 0 END) AS medium_roi_films,
    SUM(CASE WHEN gross / budget < 1 THEN 1 ELSE 0 END) AS low_roi_films
FROM cinema.films
WHERE budget > 0 AND gross > 0 AND release_year = 2015;

Bên trong hàm SUM, ta như đang tạo một trường mới có giá trị 1 khi ROI đạt ngưỡng đó, ngược lại là 0. Nếu lấy tổng của biến nhị phân này, ta sẽ thu được số lượng phim đáp ứng tiêu chí tương ứng.

Từ kết quả, có thể thấy phần lớn phim có ROI nhỏ hơn 1, trong khi chỉ một số ít phim có lợi nhuận rất cao.

CASE lồng nhau

Một trong những trường hợp nâng cao là CASE lồng nhau, tức là một CASE bên trong một CASE. Điều này rất hữu ích khi bạn cần chia dữ liệu thành các nhóm nhỏ và áp dụng logic khác nhau cho từng nhóm.

Tương tự ví dụ đầu tiên, chúng ta muốn tạo một cột mới chịu ảnh hưởng bởi các giá trị ROI. Như đã nói, ROI cho các phim tiếng Anh thường cao hơn trên phạm vi toàn cầu so với phim ở các ngôn ngữ khác. 

Vì lý do đó, sẽ tốt hơn nếu phân loại phim vào các nhóm ROI bằng cách xét cả ROI và ngôn ngữ

SELECT 
    title,
    language,
    gross / budget AS ROI,
    CASE
        WHEN language = 'English' THEN 
            CASE 
                WHEN gross / budget < 2 THEN 'low ROI'
                WHEN gross / budget BETWEEN 2 AND 3 THEN 'medium ROI'
                ELSE 'high ROI'
            END
        ELSE 
            CASE
                WHEN gross / budget < 1 THEN 'low ROI'
                WHEN gross / budget BETWEEN 1 AND 2 THEN 'medium ROI'
                ELSE 'high ROI'
            END
    END AS ROI_group
FROM cinema.films
WHERE budget > 0 AND gross > 0 and release_year = 2006;

Trong truy vấn, câu lệnh CASE bên ngoài dùng để đánh giá ngôn ngữ có phải tiếng Anh hay không. Tùy theo ngôn ngữ, CASE bên trong sẽ đánh giá ROI: các ngưỡng cao hơn cho phim tiếng Anh và thấp hơn cho phim không phải tiếng Anh. Điều kiện nhiều tầng này giúp cải thiện việc tách biệt các phim ROI thấp, trung bình và cao.

Kết luận

Câu lệnh CASE là một cấu trúc SQL không thể thiếu để rút ra insight có ý nghĩa từ dữ liệu của bạn. Phần lý thuyết đan xen với ví dụ thực hành giúp bạn hiểu sâu biểu thức điều kiện mạnh mẽ này. Nếu muốn tìm hiểu sâu hơn, hãy xem khóa học xử lý dữ liệu trong SQL. Khóa này bao quát câu lệnh CASE và các khía cạnh SQL quan trọng khác như truy vấn lồng và hàm cửa sổ. Cuối cùng, bạn nên xem SQL Fundamentals để có cái nhìn tổng quan về các khóa học giúp bạn thành thạo SQL.