DataRockie

รีวิวเทคนิคการเขียน Aggregate Functions + CASE ด้วย SQL

Kasidis Satangmongkol

Hello Friends! บทความนี้มาเรียนวิธีการเขียน Aggregate Functions (count, sum) ร่วมกับ CASE statement เพื่อนับหรือหาผลรวมของคอลัมน์แบบมีเงื่อนไข เขียนง่าย และมีโอกาสได้ใช้บ่อยเลยในชีวิตจริง

  • Aggregate Functions คือฟังก์ชันคำนวณค่าสถิติง่ายๆของ SQL ตัวหลักๆที่เราใช้บ่อยๆมี 5 ตัวคือ AVG, SUM, MIN, MAX และ COUNT
  • CASE คือการเขียนเงื่อนไขเหมือนฟังก์ชัน IF/ IFS ของ Excel

Tip – เราเขียน CASE เพื่อสร้างคอลัมน์ใหม่ เช่น segments ลูกค้าเป็น high, medium, low values และใช้ SUM หรือ COUNT เพื่อหาผลรวมหรือนับจำนวน segments ในคอลัมน์นั้น

Example Table

สำหรับตัวอย่างในบทความนี้ เราใช้ customers table ใน chinook.db เพื่อนๆสามารถดาวน์โหลดไฟล์ database ได้ที่นี่

พรีวิวข้อมูล customers ที่เราใช้ในบทความนี้

บทความนี้มี 7 ตัวอย่างการใช้งานจริงของ Aggregate Functions + CASE

  • วิธีการเขียน CASE เบื้องต้น
  • SUM CASE
  • SUM CASE 1/0 (ได้ผลลัพธ์เหมือนกับการ COUNT CASE)
  • AVG CASE 1/0 (หา % ของ CASE ที่เราต้องการ)
  • COUNT CASE
  • Another Example
  • Pivot Table (long – wide format)

Basic CASE

CASE ใช้สำหรับสร้างคอลัมน์ใหม่ (derived column) แบบมีเงื่อนไข ด้านล่างคือวิธีการเขียน CASE เบื้องต้น

  • ขึ้นต้นด้วย CASE ปิดท้ายด้วย END
  • WHEN ถ้าเงื่อนไขนี้เป็นจริง .. THEN แสดงค่านี้ ..
  • ELSE แสดงค่านี้สำหรับเงื่อนไขอื่นๆ i.e. all other conditions
  • เราสามารถตั้งชื่อคอลัมน์ใหม่ได้หลัง END
CASE 
  WHEN ... THEN ...
  WHEN ... THEN ...
  ELSE ...
END AS new_column

ถ้าดูที่คอลัมน์ company ใน customers table จะเห็นว่าลูกค้าบางคนจะขึ้นเป็นค่า NULL ถ้ามองในมุม business logic เราอาจ assume ได้ว่า

  • ลูกค้าที่ company IS NULL เป็นลูกค้าแบบ B2C i.e. end customers
  • ลูกค้าที่ company IS NOT NULL เป็นลูกค้าแบบ B2B i.e. corporate
สร้างคอลัมน์ segment ด้วย CASE statement

เราสามารถสร้าง result set นี้ได้ง่ายด้วย query นี้ แอดเขียน CASE WHEN สองเงื่อนไข ข้อแรกสำหรับลูกค้า end customer และข้อสองสำหรับลูกค้า corporate

Tip – การเขียนเงื่อนไขใน WHEN เหมือนกับที่เราเขียนใน WHERE clause

-- basic case statement
SELECT
  company,
  CASE 
    WHEN company IS NULL THEN 'end_customer'
    WHEN company IS NOT NULL THEN 'corporate'
  END AS segment
FROM customers;

หรือเขียน ELSE แทนเงื่อนไขข้อที่สองแบบนี้ (ELSE = all else or other conditions)

-- we can use ELSE to achieve the same result
SELECT
  company,
  CASE 
    WHEN company IS NULL THEN 'end_customer'
    ELSE 'corporate'
  END AS segment
FROM customers;

SUM CASE

มาลองดู basic query ง่ายๆตัวแรกกันก่อน query นี้เราต้องการหาผลรวมของ total invoice ในปี 2009 ทั้งสอง queries ด้านล่างได้ผลลัพธ์เหมือนกัน

  • SUM + CASE หาผลรวมของ total invoice ในปี 2009
  • SUM + WHERE หาผลรวมของคอลัมน์ total invoice กรองเฉพาะ transactions ของปี 2009
-- sum total invoice in 2009
SELECT 
  SUM(CASE WHEN strftime('%Y', invoicedate) = '2009' 
      THEN total END) total_invoice_2009
FROM invoices;

-- another way to write this query
SELECT SUM(total) total_invoice_2009
FROM invoices 
WHERE strftime('%Y', invoicedate) = '2009';

SUM CASE 1/0

ตัวอย่างนี้เป็นทริคที่แอดใช้บ่อยๆเพื่อนับจำนวน rows ที่ตรงกับเงื่อนไขที่เราต้องการ ด้วยการประยุกต์ใช้ SUM CASE 1/0 (hint – query นี้เทียบเท่ากับการเขียน COUNT(*) ในหัวข้อถัดไป) โดย logic ของเลข 1/0 คือ

  • 1 = เงื่อนไขเป็นจริง (TRUE) 0 = เงื่อนไขไม่เป็นจริง (FALSE)
  • SUM( 1, 0, 0, 1, 1, … ) คือการนับจำนวน rows ทั้งหมดที่ตรงกับเงื่อนไขของเรา
-- if row meets condition then 1 else 0
SELECT
  SUM(CASE WHEN company IS NULL THEN 1 ELSE 0 END) end_customer,
  SUM(CASE WHEN company IS NOT NULL THEN 1 ELSE 0 END) corporate
FROM customers;

Tip – จริงๆ ELSE 0 ในตัวอย่างนี้เป็น optional ไม่ต้องเขียนก็ได้ 😝

AVG CASE 1/0

ถ้าเราเปลี่ยนฟังก์ชัน SUM เป็น AVG จะเป็นการหาค่าเฉลี่ยของคอลัมน์ 1/0 เทียบเท่ากับการคำนวณ % ของเงื่อนไขที่เราต้องการ เช่น % ของลูกค้า end customer vs. corporate

Query นี้แอดใช้ ROUND( ) เพื่อปรับจำนวนทศนิยมเป็นสองตำแหน่ง end customer = .83 และ corporate = .17 รวมกันเป็น 1.0 หรือ 100% พอดี

-- find % of end customer and corporate
SELECT
  ROUND(AVG(CASE WHEN company IS NULL THEN 1 ELSE 0 END), 2) end_customer,
  ROUND(AVG(CASE WHEN company IS NOT NULL THEN 1 ELSE 0 END), 2) corporate
FROM customers;

COUNT CASE

การเขียน SUM(CASE 1/0) ในตัวอย่างด้านบนเทียบเท่ากับการเขียน COUNT(*) + GROUP BY โดย query ด้านล่าง แอดเขียน CASE เพื่อสร้างคอลัมน์ segment และนับจำนวนลูกค้าแต่ละ segment ง่ายๆ

-- use count case to achieve the same result as sum case 1/0
SELECT
  CASE WHEN company IS NOT NULL THEN 'corporate'
       ELSE 'end_customer' 
  END AS segment,
  COUNT(*) n
FROM customers
GROUP BY 1;

Another Example

มาลองดูอีกตัวอย่าง การเขียน SUM + CASE แบบมากกว่าหนึ่งเงื่อนไข ตัวอย่าง query นี้ แอดนับจำนวนลูกค้าในประเทศอเมริกาที่เป็นลูกค้า corporate (B2B customers = company IS NOT NULL)

โดยทั่วไป เราสามารถเขียน SQL queries ได้หลายแบบเพื่อให้ได้คำตอบแบบเดียวกัน

  • SUM + CASE เพื่อหาผลรวม (1/0) จำนวน usa_corp
  • COUNT + WHERE เพื่อนับจำนวน records ที่ตรงกับเงื่อนไขของเรา

ลองรันสอง queries ด้านล่างจะได้จำนวนลูกค้า usa_corp = 3 เท่ากัน จะใช้ SUM(1/0) หรือ COUNT(*) ก็ได้ผลลัพธ์เหมือนกันในกรณีนี้

-- count USA corporate in the table
SELECT 
  SUM(CASE WHEN company IS NOT NULL 
           AND country = 'USA' THEN 1 ELSE 0 END) AS 'usa_corp'
FROM customers;

-- this query will do the same
SELECT COUNT(*) usa_corp
FROM customers
WHERE company IS NOT NULL AND country = 'USA';

Pivot Table

ตัวอย่างสุดท้ายมาดูวิธีการเขียน SUM + CASE เพื่อเปลี่ยนหน้าตาข้อมูลแบบ long เป็น wide format

  • Long format เหมาะสมสำหรับการวิเคราะห์ข้อมูล (raw data)
  • Wide format เหมาะสมสำหรับการทำรายงาน สรุปผลสถิติเบื้องต้น (report)
ตัวอย่างการปรับหน้าตาข้อมูลจาก long เป็น wide format

Query นี้เราหาผลรวม SUM(jazz_revenue) แบ่งตามปี และจับกลุ่มผลรวมนี้ด้วยคอลัมน์ fullname ใน GROUP BY clause; assume ว่าเรามีตาราง long_format อยู่ใน database ของเราแล้ว

-- pivot long to wide format for reporting
SELECT 
  fullname,
  SUM(CASE WHEN year = '2009' THEN jazz_revenue END) '2009',
  SUM(CASE WHEN year = '2010' THEN jazz_revenue END) '2010',
  SUM(CASE WHEN year = '2011' THEN jazz_revenue END) '2011',
  SUM(CASE WHEN year = '2012' THEN jazz_revenue END) '2012',
  SUM(CASE WHEN year = '2013' THEN jazz_revenue END) '2013',
  SUM(jazz_revenue) total_sales  
FROM long_format
GROUP BY 1 ORDER BY total_sales DESC;

อ่านบทความล่าสุดของเราเรื่อง long-wide data transformation ได้ที่ลิ้งด้านล่าง แอดสอนทำใน R, Excel (Pivot Table) และ SQL ตามลำดับ

Long-Wide Data Transformation

เรียนเทคนิคการปรับหน้าตาข้อมูลบนคอนเซ็ปต์ tidy data ด้วย R, Excel, SQL

อ่านบทความ

Summary

บทความนี้ เราได้เรียนวิธีการเขียน Aggregate Functions + CASE หลายๆแบบ โดยตัวหลักๆที่เราใช้กันเวลาวิเคราะห์ข้อมูลคือ SUM + CASE และ COUNT + CASE

  • SUM + CASE หาผลรวมปกติ
  • หรือ SUM + CASE 1/0 เพื่อนับจำนวน rows ที่ตรงกับเงื่อนไข เทียบเท่ากับการเขียน COUNT + CASE
  • หรือ AVG + CASE 1/0 เพื่อหาค่าร้อยละ (%) ของ case ที่เราเขียน
  • ในชีวิตจริง อย่าลืมทำความสะอาดค่า NULL ก่อนใช้ Aggregate Functions ด้วยนะคร้าบ

Leave a Reply