นิพจน์ CASE และคำค้นแบบ Pivot

CASE: If/Else แบบแทรกใน SQL

-- Searched CASE
CASE WHEN x > 0 THEN 'positive'
     WHEN x < 0 THEN 'negative'
     ELSE 'zero' END

-- Simple CASE
CASE status
  WHEN 'A' THEN 'active'
  WHEN 'P' THEN 'pending'
  ELSE 'unknown'
END

CASE ใน SELECT

SELECT id, total,
       CASE
         WHEN total >= 1000 THEN 'whale'
         WHEN total >=  100 THEN 'regular'
         ELSE 'small'
       END AS bucket
FROM orders;

CASE ใน WHERE และ ORDER BY

-- Custom sort:
ORDER BY
  CASE WHEN status = 'urgent' THEN 0 ELSE 1 END,
  created_at DESC;

-- Conditional filter:
WHERE CASE WHEN $1 = 'paid' THEN status = 'paid'
           ELSE status IN ('pending','paid')
      END;

CASE ภายในฟังก์ชันรวม: การนับตามเงื่อนไข

SELECT user_id,
       COUNT(*) AS total,
       SUM(CASE WHEN status = 'paid'      THEN 1 ELSE 0 END) AS paid,
       SUM(CASE WHEN status = 'pending'   THEN 1 ELSE 0 END) AS pending,
       SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders
GROUP BY user_id;

FILTER: ทางเลือกที่อ่านง่ายกว่า

SELECT user_id,
       COUNT(*)                                     AS total,
       COUNT(*) FILTER (WHERE status = 'paid')      AS paid,
       COUNT(*) FILTER (WHERE status = 'pending')   AS pending,
       COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled
FROM orders
GROUP BY user_id;

ทำ pivot เดือนเป็นคอลัมน์

SELECT year,
       SUM(CASE WHEN month =  1 THEN revenue END) AS jan,
       SUM(CASE WHEN month =  2 THEN revenue END) AS feb,
       -- ...
       SUM(CASE WHEN month = 12 THEN revenue END) AS dec
FROM monthly_revenue
GROUP BY year
ORDER BY year;

ทำ pivot ตามหมวดหมู่

SELECT user_id,
       SUM(total) FILTER (WHERE status = 'paid')      AS paid_total,
       SUM(total) FILTER (WHERE status = 'pending')   AS pending_total
FROM orders GROUP BY user_id;

ข้อจำกัดของ pivot ด้วย CASE

CASE เทียบกับ COALESCE / NULLIF

ความเข้ากันได้ของชนิดข้อมูล

SELECT CASE WHEN x THEN 1::TEXT ELSE 'no' END;

CASE ซ้อนกัน

SELECT CASE
  WHEN amount IS NULL THEN 'no payment'
  WHEN amount = 0     THEN 'free'
  WHEN amount < 10    THEN 'cheap'
  ELSE
    CASE WHEN paid_at IS NULL THEN 'overdue' ELSE 'paid' END
END AS status FROM invoices;

สรุป

ตรวจสอบความเข้าใจ