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

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 ได้ที่นี่

บทความนี้มี 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

เราสามารถสร้าง 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)

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 ตามลำดับ

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 ด้วยนะคร้าบ