CASE WHEN và GROUP BY trong SQL

Bởi Kiên Smart Data
0 Nhận xét
casewhen và groupby sql

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 WHENGROUP 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 WHENGROUP 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:

citystateprice_to_income_ratiomortgage_as_pct_of_incomehomeowner_pctpopulation
Santa BarbaraCA13.3103.753%88,000
BrooklynNY11.289.930%2,533,862
QueensNY11.191.345%2,271,000
New YorkNY10.485.924%8,468,000
OaklandCA9.477.541%433,800
SunnyvaleCA9.376.545%152,300
San DiegoCA8.266.354%1,382,000
San FranciscoCA9.273.238%815,200
Long BeachCA8.569.641%456,000
BuffaloNY6.55343%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:

citypopulationpopulation_level
Santa Barbara88,000Low
Brooklyn2,533,862High
Queens2,271,000High
New York8,468,000High
Oakland433,800Low
Sunnyvale152,300Low
San Diego1,382,000Medium
San Francisco815,200Medium
Long Beach456,000Low
Buffalo276,800Low

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_levelaverage_population
Low281,380
Medium1,098,600
High4,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 ENDas low_pop_ct,

  SUM(CASE

      WHEN population >= 500000 and population < 1500000

      THEN 1  

      ELSE ENDas medium_pop_ct,

  SUM(CASE

      WHEN population >= 1500000

      THEN 1

      ELSE ENDas 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_ctmedium_pop_cthigh_pop_ct
523

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:

skudescriptionpricestatus
978568952cowl neck sweater59in stock
978548759embroidered v neck blouse49in stock
978125698notched collar button down blazer79in stock
979156258oversized stripe shirt29sale
979145875polka dot maxi dress109back ordered
978457852rib knit t shirt19sale
978333562cropped denim jacket99back ordered
978142154sleeveless midi dress89in stock
979415858utility jumpsuit59sale
978112546scoop neck sweater49in 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:

skudescriptionpricestatusproduct_category
978568952cowl neck sweater59in stockOuterwear
978548759embroidered v neck blouse49in stockTops
978125698notched collar button down blazer79in stockOuterwear
979156258oversized stripe shirt29saleTops
979145875polka dot maxi dress109back orderedDresses
978457852rib knit t shirt19saleTops
978333562cropped denim jacket99back orderedOuterwear
978142154sleeveless midi dress89in stockDresses
979415858utility jumpsuit59saleDresses
978112546scoop neck sweater49in stockOuterwear

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_categorynumber_of_products
Outerwear4
Tops3
Dresses3

Ở 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:


SELECT
 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

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:

Bài viết liên quan

Để lại nhận xét

Copyright @2022 – Bản quyền thuộc Học viện dữ lệu Smart Data