Trong bài viết này, SmartData sẽ hướng dẫn các bạn sử dụng kết hợp CASE WHEN và GROUP BY trong SQL đáp ứng những nhu cầu phân loại phức tạp.
Dữ liệu thô, về bản chất, không phải lúc nào cũng có thể dễ dàng đọc và hiểu. Chẳng hạn như là các mã SKUs của sản phẩm, với những người không có đủ chuyên môn thì những mã SKU này gần như vô nghĩa và không thể đọc được nếu chúng được đưa vào các báo cáo thông thường.
Có những tình huống, những dữ liệu thô cần được cấu trúc lại để dễ dàng đọc được.Ví dụ, 1 danh sách 50 sản phẩm trong 1 cửa hàng có thể được cấu trúc lại thành 5 phân loại hàng, qua đó làm cho các báo cáo dễ dàng được tiếp cận hơn. Và trong những tình huống như vậy, chúng ta có thể sử dụng mệnh đề CASE WHEN và GROUP BY để cấu trúc dữ liệu và thêm những dữ liệu mới vào dataset có sẵn.
Để có thể hiểu rõ hơn cách thức những mệnh đề này được hoạt động thế nào, chúng ta sẽ cùng nhau tìm hiểu sơ lược về từng mệnh đề.
Sơ Lược về Mệnh Đề CASE WHEN
Mệnh đề CASE WHEN có thể được hiểu như là 1 mệnh đề logic If…Then, tức là nó sẽ đánh giá các điều kiện và khi điều kiện được đánh giá là đúng thì sẽ trả về các kết quả được cho trước tương ứng.
Có 3 thành phần quan trọng trong mệnh đề CASE trong SQL, bao gồm: CASE WHEN, THEN, và END. Đây là các thành phần bắt buộc, thiếu nó sẽ làm cho đoạn query bị lỗi.
Bạn bắt đầu câu lệnh với CASE WHEN, để xác định điều kiện logic của mình. Sau đó, bạn sử dụng THEN để xác định giá trị nếu điều kiện đó là đúng. Sau câu lệnh THEN cuối cùng của bạn, bạn sử dụng END để đóng mệnh đề.
Một mệnh đề tùy chọn khác là mệnh đề ELSE. Nếu tất cả các điều kiện logic trong câu lệnh CASE WHEN không thành công, bạn có thể sử dụng ELSE để gán giá trị cho dữ liệu này. Nếu dữ liệu của bạn không đáp ứng tất cả các điều kiện và bạn không sử dụng ELSE, dữ liệu sẽ trả về giá trị NULL. Cách sử dụng CASE WHEN như sau:
CASE
WHEN
product = ‘Shirt’
THEN
‘Clothing’
WHEN
product = ‘Hat’
THEN
‘Accessories’
ELSE
‘Other’
END
Nếu sản phẩm là ‘Shirt’, CASE WHEN gán nó vào danh mục ‘Clothing’. Nếu sản phẩm là ‘Hat’, CASE WHEN chỉ định nó vào danh mục ‘Accessories’. Nếu không có điều kiện nào đúng thì CASE WHEN chỉ định sản phẩm vào danh mục ‘Other’.
1 ví dụ khác để minh hoạ rõ hơn, giả sử chúng ta có 1 bảng Cities như sau:
city | state | price_to_income_ratio | mortgage_as_pct_of_income | homeowner_pct | population |
---|---|---|---|---|---|
Santa Barbara | CA | 13.3 | 103.7 | 53% | 88,000 |
Brooklyn | NY | 11.2 | 89.9 | 30% | 2,533,862 |
Queens | NY | 11.1 | 91.3 | 45% | 2,271,000 |
New York | NY | 10.4 | 85.9 | 24% | 8,468,000 |
Oakland | CA | 9.4 | 77.5 | 41% | 433,800 |
Sunnyvale | CA | 9.3 | 76.5 | 45% | 152,300 |
San Diego | CA | 8.2 | 66.3 | 54% | 1,382,000 |
San Francisco | CA | 9.2 | 73.2 | 38% | 815,200 |
Long Beach | CA | 8.5 | 69.6 | 41% | 456,000 |
Buffalo | NY | 6.5 | 53 | 43% | 276,800 |
Nếu chúng ta muốn phân loại số lượng công dân trong từng thành phố về 3 loại low, medium, và high chúng ta sẽ có 1 query như sau:
SELECT
city,
population,
CASE
WHEN
population < 500000
THEN
‘Low’
WHEN
population >= 500000
and
population < 1500000
THEN
‘Medium’
WHEN
population >= 1500000
THEN
‘High’
END
as
population_level
FROM
cities
Trong câu query trên, chúng ta thêm những điều kiện dựa vào trường Population . Nếu trường Population ít hơn 500,000 sẽ xếp vào phân loại ‘Low’, từ 500,000 đến 1,500,000 sẽ vào loại ‘medium’ và cao hơn 1,500,000 sẽ được xếp vào ‘high’. Sau đó chúng ta đặt tên cho trường phân loại này là Population_level . Kết quả sẽ như sau:
city | population | population_level |
---|---|---|
Santa Barbara | 88,000 | Low |
Brooklyn | 2,533,862 | High |
Queens | 2,271,000 | High |
New York | 8,468,000 | High |
Oakland | 433,800 | Low |
Sunnyvale | 152,300 | Low |
San Diego | 1,382,000 | Medium |
San Francisco | 815,200 | Medium |
Long Beach | 456,000 | Low |
Buffalo | 276,800 | Low |
Nếu như chúng ta lại muốn tìm trung bình cho mỗi Population_level ? Trong tình huống này, chúng ta có thể thực hiện điều đó bằng cách kết hợp mệnh đề GROUP BY vào câu truy vấn. Chúng ta sẽ cùng xem xét nó sau đây.
Sử Dụng Kết Hợp CASE WHEN Và GROUP BY Trong SQL
Ví dụ 1: Tổng Hợp Các Danh Mục Tuỳ Chỉnh
Qua ví dụ trên, chúng ta đã có danh mục tuỳ chỉnh Population_level , bây giờ chúng ta có thể tính toán nhiều chỉ số cho nó.Trong ví dụ này, chúng ta sẽ tính trunh bình dân số cho mỗi Population_level .
Chúng ta cũng sẽ sử dụng cùng 1 câu truy vấn Population_level trên, thêm vào đó 1 phép tính trung bình dân số và áp dụng mệnh đề GROUP BY cho 1 câu truy vấn Population_level nữa. Câu truy vấn đầy đủ sẽ như sau:
SELECT
CASE
WHEN
population < 500000
THEN
‘Low’
WHEN
population >= 500000
and
population < 1500000
THEN
‘Medium’
WHEN
population >= 1500000
THEN
‘High’
END
as
population_level,
AVG
(population)
as
average_population
FROM
cities
GROUP
BY
CASE
WHEN
population < 500000
THEN
‘Low’
WHEN
population >= 500000
and
population < 1500000
THEN
‘Medium’
WHEN
population >= 1500000
THEN
‘High’
END
Khi truy vấn này được chạy, trước tiên, dữ liệu trong các điều kiện logic của câu lệnh CASE WHEN sẽ được đánh giá và được gán một giá trị cho Population_level . Sau đó, giá trị trung bình được tính trên mỗi cấp độ này với GROUP BY. Ta sẽ được kết quả như sau:
population_level | average_population |
---|---|
Low | 281,380 |
Medium | 1,098,600 |
High | 4,424,287 |
Ví dụ 2: CASE WHEN với ELSE kết hợp GROUP BY
Thêm 1 cách để viết 1 câu truy vấn cho ví dụ trên đó là sử dụng với mệnh đề ELSE. Đầu tiên, chúng ta sẽ xác định điều kiện và kết quả cho 2 Population_level đầu tiên, sau đó chúng ta sử dụng mệnh đề ELSE cho điều kiện cuối cùng. Câu truy vấn sẽ như sau :
SELECT
CASE
WHEN
population < 500000
THEN
‘Low’
WHEN
population >= 500000
and
population < 1500000
THEN
‘Medium’
ELSE
‘High’
END
as
population_level,
AVG
(population)
as
average_population
FROM
cities
GROUP
BY
CASE
WHEN
population < 500000
THEN
‘Low’
WHEN
population >= 500000
and
population < 1500000
THEN
‘Medium’
WHEN
population >= 1500000
THEN
‘High’
END
Ví dụ 3: Mệnh Đề CASE WHEN bên trong 1 hàm tổng hợp (SUM,MAX,MIN…)
Chúng ta có thể sử dụng mệnh đề CASE WHEN bên trong 1 hàm tổng hợp để đếm các bản ghi đạt 1 điều kiện cho trước. Tiếp tục với đề bài ở Ví Dụ 1, hãy cùng nhau xem cách để đếm các thành phố trong mỗi Population_level.
Chúng ta sẽ cần 3 mệnh đề CASE WHEN cho ví dụ này , mỗi mệnh đề ứng với 1 điều kiện: Low , Medium , and High .
SELECT
SUM
(
CASE
WHEN
population < 500000
THEN
1
ELSE
0
END
)
as
low_pop_ct,
SUM
(
CASE
WHEN
population >= 500000
and
population < 1500000
THEN
1
ELSE
0
END
)
as
medium_pop_ct,
SUM
(
CASE
WHEN
population >= 1500000
THEN
1
ELSE
0
END
)
as
high_pop_ct
FROM
cities
Hãy phân tích từng dòng để hiểu rõ chuyện gì đang xảy ra. Trong dòng đầu tiên, ta nhận định nếu có bất cứ thành phố nào ít hơn 500,000 dân thì kết quả trả về là 1, kêu không thì trả về 0. Sau đó lưu kết quả đó dưới 1 cột được đặt tên sẵn là low_pop_ct. Sau đó hàm tổng hợp Sum sẽ tự động cộng lại những kết quả được hàm CASE WHEN trả về. Đối với low_pop_ct này thì có 5 thành phố đủ điều kiện nên sẽ trả về 1 và tổng của nó sẽ là 5. Như vậy là ta đã hoàn thành việc đếm thành phố có điều kiện ‘Low’, tương tự như thế cho 2 điều kiện ‘Medium’ và ‘High’ còn lại và ta được kết quả như sau:
low_pop_ct | medium_pop_ct | high_pop_ct |
---|---|---|
5 | 2 | 3 |
Ví dụ 4: CASE WHEN trong GROUP BY
Hãy cùng nhau xem 1 ví dụ ứng dụng mệnh đề CASE WHEN trong GROUP BY thế nào nhé. Giả sử ta có 1 bảng products sau:
sku | description | price | status |
---|---|---|---|
978568952 | cowl neck sweater | 59 | in stock |
978548759 | embroidered v neck blouse | 49 | in stock |
978125698 | notched collar button down blazer | 79 | in stock |
979156258 | oversized stripe shirt | 29 | sale |
979145875 | polka dot maxi dress | 109 | back ordered |
978457852 | rib knit t shirt | 19 | sale |
978333562 | cropped denim jacket | 99 | back ordered |
978142154 | sleeveless midi dress | 89 | in stock |
979415858 | utility jumpsuit | 59 | sale |
978112546 | scoop neck sweater | 49 | in stock |
Hãy cùng nhau phân loại bảng này thành 1 cột product_category. Việc này sẽ giúp chúp ta gộp những sản phẩm có tính chất giống nhau thành 1 cấp bậc cao hơn để dễ dàng tính toán các tổng hợp. Để thêm cột product_category ta sử dụng câu truy vấn sau:
SELECT
*,
CASE
WHEN
description
LIKE
'%sweater%'
OR
description
LIKE
'%blazer%'
OR
description
LIKE
'%jacket%'
THEN
'Outerwear'
WHEN
description
LIKE
'%dress%'
OR
description
LIKE
'%jumpsuit%'
THEN
'Dresses'
WHEN
description
LIKE
'%shirt%'
OR
description
LIKE
'%blouse%'
THEN
'Tops'
END
as
product_category
FROM
products
Chúng ta sử dụng toán tử LIKE để tìm kiếm những giá trị đáp ứng những điều kiện được cho sẵn trong dấu ngoặc kép ”.Toán tử % có nghĩa là có thể có bất kỳ thứ gì trước hoặc sau. Các kết quả được hiển thị dưới đây:
sku | description | price | status | product_category |
---|---|---|---|---|
978568952 | cowl neck sweater | 59 | in stock | Outerwear |
978548759 | embroidered v neck blouse | 49 | in stock | Tops |
978125698 | notched collar button down blazer | 79 | in stock | Outerwear |
979156258 | oversized stripe shirt | 29 | sale | Tops |
979145875 | polka dot maxi dress | 109 | back ordered | Dresses |
978457852 | rib knit t shirt | 19 | sale | Tops |
978333562 | cropped denim jacket | 99 | back ordered | Outerwear |
978142154 | sleeveless midi dress | 89 | in stock | Dresses |
979415858 | utility jumpsuit | 59 | sale | Dresses |
978112546 | scoop neck sweater | 49 | in stock | Outerwear |
Bây giờ chúng ta đã có 1 product_category , chúng ta có thể đếm số sản phẩm trong từng loại bằng cách sử dụng GROUP_BY:
SELECT
CASE
WHEN
description
LIKE
'%sweater%'
OR
description
LIKE
'%blazer%'
OR
description
LIKE
'%jacket%'
THEN
'Outerwear'
WHEN
description
LIKE
'%dress%'
OR
description
LIKE
'%jumpsuit%'
THEN
'Dresses'
WHEN
description
LIKE
'%shirt%'
OR
description
LIKE
'%blouse%'
THEN
'Tops'
END
as
product_category,
COUNT
(
DISTINCT
description)
as
number_of_products
FROM
products
GROUP
BY
CASE
WHEN
description
LIKE
'%sweater%'
OR
description
LIKE
'%blazer%'
OR
description
LIKE
'%jacket%'
THEN
'Outerwear'
WHEN
description
LIKE
'%dress%'
OR
description
LIKE
'%jumpsuit%'
THEN
'Dresses'
WHEN
description
LIKE
'%shirt%'
OR
description
LIKE
'%blouse%'
THEN
'Tops'
END
Chạy truy vấn này, ta được kết quả:
product_category | number_of_products |
---|---|
Outerwear | 4 |
Tops | 3 |
Dresses | 3 |
Ở ví dụ này, chúng ta đã sử dụng CASE WHEN cho mệnh đề SELECT, nhưng nó không thực sự cần thiết lắm. Chúng ta có thể đơn giản câu truy vấn như thế này:
SELECTCOUNT
(
DISTINCT
description)
as
number_of_products
FROM
products
GROUP
BY
CASE
WHEN
description
LIKE
'%sweater%'
OR
description
LIKE
'%blazer%'
OR
description
LIKE
'%jacket%'
THEN
'Outerwear'
WHEN
description
LIKE
'%dress%'
OR
description
LIKE
'%jumpsuit%'
THEN
'Dresses'
WHEN
description
LIKE
'%shirt%'
OR
description
LIKE
'%blouse%'
THEN
'Tops'
END
Kết quả ta được:
number_of_products |
---|
4 |
3 |
3 |
Tổng Kết
Như các bạn cũng có thể thấy, có rất nhiều trường hợp có thể sử dụng hiệu quả kết hợp mệnh đề CASE WHEN và mệnh đề GROUP BY. Nó cho phép thêm những trường dữ liệu mới và qua đó tính toán được các số liệu phục vụ cho nhu cầu kinh doanh. Bạn có thể sử dụng mệnh đề CASE WHEN cả trong và ngoài các hàm tổng hợp dữ liệu, chỉ cần tuân thủ đúng cú pháp của hàm, bắt đầu với CASE WHEN để xác định điều kiện, sau đó dùng mệnh đề THEN/ELSE để xác định kết quả, cuối cùng là đóng mệnh đề với END , nếu muốn đặt tên thì ta dùng AS .
Chúc các bạn áp dụng thành công !
CASE WHEN bạn là newbie THEN có thể tham khảo bài viết này để tìm hiểu lộ trình học DA trong 3 tháng của SmartData.
WHEN bạn thấy bài viết hay và hữu ích THEN bạn có thể tham gia các kênh sau của SmartData để nhận được nhiều hơn nữa: