วัตถุประสงค์การเรียนรู้
เมื่อจบบทนี้ ผู้เรียนจะสามารถ
- เขียน
simple CASE expressionเพื่อแปลงค่าหมวดหมู่ - เขียน
searched CASE expressionพร้อมเงื่อนไขเพื่อจัดกลุ่มและวิเคราะห์ระดับบริการ - ใช้
CASEภายใน aggregate functions และ conditional aggregation สร้างรายงานแบบ pivot รวมถึงวิเคราะห์ satisfaction distribution และค่าจัดส่ง - ใช้
CASEหลายตัวสร้างรายงาน crosstab หลายมิติ และคำนวณ effective rate ด้วยNULLIF
บทนำ
CASE expression เป็นโครงสร้างพื้นฐานสำหรับควบคุมตรรกะในภาษา SQL ที่เปิดโอกาสให้ผู้ใช้กำหนดเงื่อนไขได้หลายกรณี และเลือกผลลัพธ์ให้สอดคล้องกับเงื่อนไขที่เป็นจริงในแต่ละกรณี แนวคิดนี้มีลักษณะใกล้เคียงกับคำสั่ง if-elif-else ในภาษาการเขียนโปรแกรมทั่วไป โดย CASE เป็นส่วนหนึ่งของมาตรฐาน SQL-92 และได้รับการรองรับอย่างแพร่หลายในระบบจัดการฐานข้อมูลหลัก ไม่ว่าจะเป็น MySQL, PostgreSQL, SQL Server, Oracle หรือ DuckDB
บทนี้มุ่งนำเสนอรูปแบบการใช้ CASE ที่สำคัญในงานวิเคราะห์ข้อมูลเชิงธุรกิจ ซึ่งสามารถแบ่งได้เป็น 3 กลุ่มหลัก ดังนี้
- ใช้
CASEเพื่อแปลงค่าและจัดกลุ่มข้อมูล (bucketing) เช่น การแปลงรหัสช่องทางการขาย การแบ่งช่วงเวลา การจัดระดับรายได้ และการจัดหมวดหมู่เวลารอ - ใช้
CASEภายใน aggregate functions เพื่อสร้างตารางสรุปในลักษณะคล้าย pivot table สำหรับวิเคราะห์การกระจายตัวของคะแนนความพึงพอใจ และการคำนวณสัดส่วนเงินสนับสนุนค่าจัดส่ง - ใช้
CASEหลายชุดภายใน query เดียวกัน เพื่อสร้างรายงานแบบ crosstab ที่วิเคราะห์ข้อมูลหลายมิติพร้อมกัน รวมถึงการคำนวณตัวชี้วัดอย่าง effective commission rate โดยใช้NULLIFเพื่อป้องกันปัญหาการหารด้วยศูนย์
CASE Expression
CASE expression มีสองรูปแบบหลัก ได้แก่ Simple CASE และ Searched CASE ที่ประเมินเงื่อนไขเชิงตรรกะแบบอิสระ ช่วยให้สามารถแปลงข้อมูล สร้างคอลัมน์คำนวณใหม่ จัดกลุ่มข้อมูล หรือจัดการกับข้อมูลที่ขาดหายได้อย่างมีประสิทธิภาพ โดยสามารถฝังในคำสั่ง SELECT, WHERE, GROUP BY, ORDER BY และส่วนอื่น ๆ ของ SQL ได้
| ประเภท | แนวคิดหลัก | โครงสร้างโดยย่อ | ใช้เมื่อไหร่ |
|---|---|---|---|
| Simple CASE | เปรียบเทียบ “ค่าเดียว” กับ “ค่าทางเลือกหลายค่า” | CASE expr WHEN value1 THEN … WHEN value2 THEN … ELSE … END | เมื่อเป็นการเทียบค่าเท่ากัน (คล้าย switch/case) |
| Searched CASE | ตรวจ “เงื่อนไข Boolean หลายรูปแบบ” | CASE WHEN condition1 THEN … WHEN condition2 THEN … ELSE … END | เมื่อแต่ละเงื่อนไขเป็น expression ซับซ้อน (>, <, BETWEEN, AND) |
1. Simple CASE
เริ่มจากตัวอย่างการใช้ Simple CASE เพื่อแปลงรหัสช่องทางการขายให้เป็นชื่อภาษาไทย พร้อมการสรุปจำนวนออเดอร์และรายได้รวมของแต่ละช่องทาง
SELECT
channel, -- ช่องทางการขายต้นฉบับ
CASE channel -- แปลงข้อความในคอลัมน์ channel ให้เป็นภาษาไทย
WHEN 'dine_in' THEN 'นั่งทานที่ร้าน' -- แปลง dine_in เป็นภาษาไทย
WHEN 'to_go' THEN 'รับกลับไป' -- แปลง to_go เป็นภาษาไทย
WHEN 'delivery' THEN 'จัดส่ง' -- แปลง delivery เป็นภาษาไทย
ELSE 'อื่น ๆ' -- กรณีอื่น ๆ ที่ไม่ตรงเงื่อนไข
END AS channel_thai, -- คอลัมน์ชื่อช่องทางภาษาไทย
COUNT(*) AS order_count, -- นับจำนวนคำสั่งซื้อทั้งหมด
SUM(basket_qty) AS total_qty, -- จำนวนเครื่องดื่มที่สั่งซื้อทั้งหมด
ROUND(SUM(basket_qty) / COUNT(*), 1) AS avg_qty_per_order, -- จำนวนเครื่องดื่มที่สั่งเฉลี่ยต่ออเดอร์
ROUND(SUM(net_sales_thb), 0) AS total_revenue, -- ยอดขายสุทธิรวม ปัดเศษเป็นทศนิยม 0 ตำแหน่ง
ROUND(SUM(net_sales_thb) / COUNT(*), 0) AS aov_thb -- ยอดขายเฉลี่ยต่อออร์เดอร์
FROM transactions -- ตารางข้อมูลธุรกรรมการขาย
GROUP BY channel, channel_thai -- จัดกลุ่มตามช่องทางและชื่อภาษาไทย
ORDER BY total_revenue DESC; -- เรียงลำดับจากยอดขายสูงสุดไปต่ำสุด| channel | channel_thai | order_count | total_qty | avg_qty_per_order | total_revenue | aov_thb |
|---|---|---|---|---|---|---|
| delivery | จัดส่ง | 18,795 | 41,439 | 2.2 | 5,559,370.0 | 296.0 |
| to_go | รับกลับไป | 24,552 | 34,377 | 1.4 | 3,725,939.0 | 152.0 |
| dine_in | นั่งทานที่ร้าน | 9,976 | 13,915 | 1.4 | 1,502,092.0 | 151.0 |
ช่องทาง delivery แม้ไม่ใช่ช่องทางที่มีออร์เดอร์มากที่สุด แต่ครองรายได้รวมอันดับ 1 โดยสร้างรายได้รวม 5.56 ล้านบาท คิดเป็น 52% ของรายได้ทั้งหมด ซึ่งส่วนใหญ่มาจาก AOV ที่สูงถึง 296 บาทต่อออร์เดอร์ ซึ่ง สูงกว่า to_go และ dine_in เกือบ 2 เท่า
ช่องทาง to_go มีจำนวนออร์เดอร์สูงสุด (24,552 ออร์เดอร์) แต่ AOV เพียง 152 บาท ใกล้เคียงกับ dine_in มาก แสดงว่าลูกค้าที่รับกลับและนั่งทานที่ร้านมีพฤติกรรมการสั่งคล้ายกัน คือ สั่งน้อยชิ้น ราคาต่ำกว่า
ช่องทาง delivery ถึงมี AOV ถึงสูงกว่ามาก เพราะ ลูกค้าที่สั่ง delivery มักสั่งหลายแก้วพร้อมกัน (สั่งให้ทั้งกลุ่ม)
ดังนั้น PiCha ควรลงทุนกับช่องทาง delivery ต่อไป เช่น จัดโปรโมชัน bundle หรือ free delivery เมื่อซื้อครบ 300 บาท เพื่อรักษา AOV สูง และควรหาทางเพิ่ม AOV ของ dine_in โดยให้พนักงาน upsell ขนาด L หรือ add-on ณ จุดสั่ง เพื่อลดช่องว่างระหว่างสองช่องทางนี้
2. Searched CASE
เมื่อเงื่อนไขไม่ใช่การเทียบค่าตรงตัว แต่เป็นช่วงหรือเงื่อนไขซับซ้อน จะใช้ Searched CASE ในส่วนนี้จะแสดงตัวอย่างสามแบบ ได้แก่ การจัดกลุ่มตามช่วงเวลา ระดับรายได้ และเวลารอเพื่อวิเคราะห์ระดับบริการ
ตัวอย่างต่อไปแบ่งช่วงเวลาเป็น 5 ช่วง (เช้า กลางวัน บ่าย เย็น ค่ำ) โดยใช้ BETWEEN ตรวจสอบเงื่อนไขภายใน WHEN เช่น WHEN hour BETWEEN 9 AND 10 THEN 'เช้า (9-10)' เพื่อดูจำนวนออเดอร์และรายได้ในแต่ละช่วงเวลา
-- สร้างกลุ่มช่วงเวลา (bucket) ตามชั่วโมงของคำสั่งซื้อ
WITH hour_buckets AS (
SELECT
-- แปลงชั่วโมง (hour) ให้เป็นชื่อช่วงเวลาแบบอ่านง่าย
CASE
WHEN hour BETWEEN 9 AND 10 THEN 'เช้า (9-10)' -- ช่วงเช้า
WHEN hour BETWEEN 11 AND 13 THEN 'กลางวัน (11-13)' -- ช่วงกลางวัน
WHEN hour BETWEEN 14 AND 16 THEN 'บ่าย (14-16)' -- ช่วงบ่าย
WHEN hour BETWEEN 17 AND 19 THEN 'เย็น (17-19)' -- ช่วงเย็น
WHEN hour BETWEEN 20 AND 22 THEN 'ค่ำ (20-22)' -- ช่วงค่ำ
ELSE 'อื่น ๆ' -- นอกช่วงที่สนใจ
END AS time_of_day, -- เลเบลช่วงเวลา (ใช้แสดงในรายงาน)
-- กำหนดลำดับตัวเลขให้แต่ละช่วงเวลา เพื่อใช้เรียงใน ORDER BY
CASE
WHEN hour BETWEEN 9 AND 10 THEN 1 -- เช้า
WHEN hour BETWEEN 11 AND 13 THEN 2 -- กลางวัน
WHEN hour BETWEEN 14 AND 16 THEN 3 -- บ่าย
WHEN hour BETWEEN 17 AND 19 THEN 4 -- เย็น
WHEN hour BETWEEN 20 AND 22 THEN 5 -- ค่ำ
ELSE 6 -- อื่น ๆ (วางไว้ท้ายสุด)
END AS time_order,
net_sales_thb -- ยอดขายสุทธิแต่ละออเดอร์ (เป็นตัวเลขใช้คำนวณ)
FROM transactions -- ตารางคำสั่งซื้อทั้งหมด
)
-- สรุปยอดตามช่วงเวลาในวัน
SELECT
time_of_day, -- ชื่อช่วงเวลา
-- นับจำนวนคำสั่งซื้อในแต่ละช่วงเวลา
COUNT(*) AS order_count,
-- ยอดขายรวมในช่วงเวลานั้น (ปัดทศนิยม 0 ตำแหน่ง)
ROUND(SUM(net_sales_thb), 0) AS total_revenue,
-- ค่าเฉลี่ยยอดต่อออเดอร์ในแต่ละช่วงเวลา
ROUND(AVG(net_sales_thb), 0) AS avg_order_value,
-- สัดส่วนจำนวนออเดอร์ของช่วงนั้น เทียบกับทุกออเดอร์ทั้งหมด (%)
ROUND(
100.0 * COUNT(*) / (SELECT COUNT(*) FROM transactions),
1
) AS pct_of_total
FROM hour_buckets
GROUP BY time_of_day, time_order -- สรุปเป็นรายช่วงเวลา
ORDER BY time_order; -- เรียงตามลำดับช่วงเวลา (เช้า → ค่ำ → อื่น ๆ)| time_of_day | order_count | total_revenue | avg_order_value | pct_of_total |
|---|---|---|---|---|
| เช้า (9-10) | 5,615 | 1,093,356 | 195 | 10.5 |
| กลางวัน (11-13) | 17,481 | 3,545,399 | 203 | 32.8 |
| บ่าย (14-16) | 10,156 | 2,053,268 | 202 | 19.0 |
| เย็น (17-19) | 13,994 | 2,846,591 | 203 | 26.2 |
| ค่ำ (20-22) | 6,077 | 1,248,788 | 205 | 11.4 |
กลางวันและเย็นเป็นสองช่วง peak ที่สำคัญที่สุดร่วมกันคิดเป็นเกือบ 60% ของยอดขายทั้งวัน ขณะที่ค่า AOV แทบไม่ต่างกันระหว่างช่วงเวลา แสดงว่าการเพิ่มรายได้ต้องเน้นเพิ่มจำนวนออเดอร์ในช่วงที่ยังมีศักยภาพ เช่น เช้าและค่ำ มากกว่าการพยายาม upsell ให้ลูกค้าซื้อเพิ่มในแต่ละออเดอร์
ตัวอย่างถัดไปเป็นการสร้างตาราง crosstab แสดงรายได้รายชั่วโมงแยกตามวันในสัปดาห์ เพื่อระบุว่า cell ไหน (ชั่วโมง x วัน) ที่ต้องจัดกำลังคนให้เหมาะสมที่สุด
-- สร้างตาราง crosstab ยอดขายแยกตามชั่วโมงและวันในสัปดาห์ (Pivot แนวนอน)
SELECT
hour, -- ชั่วโมงของวัน (0-23)
SUM(CASE WHEN day_of_week = 'Monday' THEN net_sales_thb ELSE 0 END) AS mon, -- ยอดขายวันจันทร์ (บาท)
SUM(CASE WHEN day_of_week = 'Tuesday' THEN net_sales_thb ELSE 0 END) AS tue, -- ยอดขายวันอังคาร (บาท)
SUM(CASE WHEN day_of_week = 'Wednesday' THEN net_sales_thb ELSE 0 END) AS wed, -- ยอดขายวันพุธ (บาท)
SUM(CASE WHEN day_of_week = 'Thursday' THEN net_sales_thb ELSE 0 END) AS thu, -- ยอดขายวันพฤหัสบดี (บาท)
SUM(CASE WHEN day_of_week = 'Friday' THEN net_sales_thb ELSE 0 END) AS fri, -- ยอดขายวันศุกร์ (บาท)
SUM(CASE WHEN day_of_week = 'Saturday' THEN net_sales_thb ELSE 0 END) AS sat, -- ยอดขายวันเสาร์ (บาท)
SUM(CASE WHEN day_of_week = 'Sunday' THEN net_sales_thb ELSE 0 END) AS sun, -- ยอดขายวันอาทิตย์ (บาท)
ROUND(SUM(net_sales_thb), 0) AS total -- ยอดขายรวมทุกวันในแต่ละชั่วโมง (บาท)
FROM transactions -- ตารางหลักที่เก็บข้อมูลธุรกรรม
GROUP BY hour -- จัดกลุ่มตามชั่วโมง
ORDER BY hour; -- เรียงตามชั่วโมงจาก 0 ถึง 23| hour | mon | tue | wed | thu | fri | sat | sun | total |
|---|---|---|---|---|---|---|---|---|
| 9 | 73,666 | 88,096 | 95,765 | 72,039 | 64,047 | 45,866 | 58,497 | 497,976 |
| 10 | 82,521 | 92,567 | 97,045 | 82,623 | 81,110 | 84,745 | 74,771 | 595,380 |
| 11 | 138,893 | 155,918 | 167,329 | 149,584 | 154,006 | 140,553 | 154,866 | 1,061,147 |
| 12 | 169,261 | 196,364 | 208,380 | 188,929 | 185,207 | 195,566 | 220,321 | 1,364,026 |
| 13 | 130,863 | 151,435 | 183,967 | 156,144 | 157,870 | 151,799 | 188,149 | 1,120,226 |
| 14 | 103,433 | 114,510 | 103,542 | 107,925 | 109,218 | 105,482 | 123,426 | 767,538 |
| 15 | 83,466 | 104,026 | 98,448 | 89,957 | 99,223 | 106,512 | 106,596 | 688,227 |
| 16 | 68,783 | 88,983 | 86,852 | 75,572 | 85,261 | 93,765 | 98,287 | 597,503 |
| 17 | 112,865 | 113,490 | 134,237 | 125,231 | 128,515 | 144,578 | 151,518 | 910,433 |
| 18 | 108,855 | 138,522 | 148,779 | 137,706 | 148,919 | 164,938 | 189,791 | 1,037,510 |
| 19 | 87,710 | 106,272 | 125,203 | 120,560 | 142,765 | 146,751 | 169,384 | 898,646 |
| 20 | 67,990 | 76,050 | 90,362 | 85,908 | 94,629 | 106,052 | 117,312 | 638,302 |
| 21 | 40,554 | 53,715 | 56,631 | 54,956 | 64,548 | 79,429 | 78,622 | 428,453 |
| 22 | 16,820 | 22,179 | 24,623 | 18,330 | 26,374 | 33,608 | 40,100 | 182,033 |
ชั่วโมง 12:00 เป็น peak สูงสุดทุกวัน โดยวันอาทิตย์ 12:00 สูงสุดที่ 220,321 บาท ขณะที่วันจันทร์ 12:00 ต่ำสุดใน peak ที่ 169,261 บาท ชั่วโมง 18:00 วันอาทิตย์สูงรองลงมาที่ 189,791 บาท และ 22:00 วันอาทิตย์ (40,100 บาท) สูงกว่าวันจันทร์ถึง 2.4 เท่า (16,820 บาท) วันอาทิตย์ช่วงเย็นเป็น prime time ของ PiCha โดยชั่วโมง 12:00 + 13:00 + 18:00 + 19:00 ของวันอาทิตย์รวมกันสร้างรายได้ 767,645 บาท คิดเป็น 43% ของรายได้วันอาทิตย์ทั้งวัน แม้วันจันทร์จะมีรายได้ต่ำสุดทุกชั่วโมง แต่ช่วงเย็นวันจันทร์ (17:00–19:00) ยังสูงกว่าช่วงเช้าของวันอื่น ๆ อยู่ดี ในเชิงบริหาร ตารางนี้ใช้วางแผน staff scheduling ได้โดยตรง โดยเสาร์-อาทิตย์ 11:00–19:00 ต้องการพนักงานสูงสุด ส่วนจันทร์เช้าสามารถลดจำนวนได้ ข้อสังเกตเพิ่มเติมคือช่วง 9:00 วันเสาร์ (45,866 บาท) ต่ำผิดปกติเมื่อเทียบกับวันอื่น อาจเป็นเพราะพฤติกรรมตื่นสายหลังวันศุกร์ จึงไม่ควรลงทุนโปรโมทช่วงเช้าวันเสาร์
ในทำนองเดียวกัน เราสามารถใช้ CASE เพื่อจัดกลุ่มออเดอร์ตามระดับมูลค่ารายได้ และวิเคราะห์สัดส่วนออเดอร์และรายได้ในแต่ละกลุ่ม
-- จัดกลุ่มคำสั่งซื้อตามระดับรายได้ และวิเคราะห์สัดส่วนคำสั่งซื้อและรายได้ในแต่ละกลุ่ม
WITH tiers AS (
SELECT
CASE
WHEN net_sales_thb >= 400 THEN 'High (400+)' -- กลุ่มรายได้สูง
WHEN net_sales_thb >= 200 THEN 'Medium (200-399)' -- กลุ่มรายได้ปานกลาง
WHEN net_sales_thb >= 100 THEN 'Low (100-199)' -- กลุ่มรายได้ต่ำ
ELSE 'Very Low (<100)' -- กลุ่มรายได้ต่ำมาก
END AS revenue_tier,
CASE
WHEN net_sales_thb >= 400 THEN 1 -- ลำดับสำหรับเรียง: High
WHEN net_sales_thb >= 200 THEN 2 -- ลำดับสำหรับเรียง: Medium
WHEN net_sales_thb >= 100 THEN 3 -- ลำดับสำหรับเรียง: Low
ELSE 4 -- ลำดับสำหรับเรียง: Very Low
END AS tier_order,
net_sales_thb
FROM transactions -- ตารางหลักที่เก็บข้อมูลธุรกรรม
)
SELECT
revenue_tier, -- ระดับรายได้
COUNT(*) AS order_count, -- จำนวนคำสั่งซื้อในแต่ละกลุ่ม
ROUND(SUM(net_sales_thb), 0) AS total_revenue, -- รายได้รวมของแต่ละกลุ่ม (บาท)
ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM transactions), 1) AS pct_orders, -- สัดส่วนคำสั่งซื้อ (%)
ROUND(100.0 * SUM(net_sales_thb) / (SELECT SUM(net_sales_thb) FROM transactions), 1) AS pct_revenue -- สัดส่วนรายได้ (%)
FROM tiers -- ใช้ข้อมูลจาก CTE ที่จัดกลุ่มไว้แล้ว
GROUP BY revenue_tier, tier_order -- จัดกลุ่มตามระดับรายได้และลำดับ
ORDER BY tier_order; -- เรียงจากกลุ่มรายได้สูงสุดไปต่ำสุด| revenue_tier | order_count | total_revenue | pct_orders | pct_revenue |
|---|---|---|---|---|
| High (400+) | 3,315 | 1,558,880 | 6.2 | 14.5 |
| Medium (200-399) | 19,354 | 5,533,335 | 36.3 | 51.3 |
| Low (100-199) | 23,335 | 3,024,705 | 43.8 | 28.0 |
| Very Low (<100) | 7,319 | 670,482 | 13.7 | 6.2 |
ผลลัพธ์จากตารางที่ได้สรุปได้ว่า กลุ่ม Medium เป็นแกนกลางของรายได้ แม้มีเพียง 36% ของออเดอร์แต่สร้างรายได้มากกว่าครึ่ง ขณะที่กลุ่ม Low มีจำนวนออเดอร์มากที่สุดแต่สร้างรายได้ต่ำกว่าส่วนแบ่งจำนวนออเดอร์ แสดงว่าการออกแบบโปรโมชันเพื่อดันออเดอร์จากกลุ่ม Low ให้ขยับเป็น Medium สามารถเพิ่มรายได้โดยใช้ฐานลูกค้าเดิม
นอกจากการจัดกลุ่มตามช่วงเวลาและระดับรายได้ Searched CASE ยังนำมาใช้ในการวิเคราะห์มิติด้านคุณภาพบริการได้เช่นกัน ตัวอย่างนี้จัดกลุ่มเวลารอเป็น 4 ระดับ (Excellent, Good, Fair, Poor) แล้ววิเคราะห์ความสัมพันธ์กับ satisfaction score แยกตามช่องทาง dine-in และ to-go
-- วิเคราะห์ความสัมพันธ์ระหว่างเวลารอและความพึงพอใจ แยกตามช่องทาง dine-in และ to-go
WITH wait_buckets AS (
SELECT
channel,
-- จัดกลุ่มเวลารอเป็น 4 ระดับตามประสบการณ์ลูกค้า
CASE
WHEN wait_mins BETWEEN 0 AND 5 THEN 'Excellent (0-5 min)' -- รอไม่เกิน 5 นาที
WHEN wait_mins BETWEEN 6 AND 10 THEN 'Good (6-10 min)' -- รอ 6-10 นาที
WHEN wait_mins BETWEEN 11 AND 15 THEN 'Fair (11-15 min)' -- รอ 11-15 นาที
ELSE 'Poor (16+ min)' -- รอนานกว่า 15 นาที
END AS wait_time_category,
CASE
WHEN wait_mins BETWEEN 0 AND 5 THEN 1 -- ลำดับ: Excellent
WHEN wait_mins BETWEEN 6 AND 10 THEN 2 -- ลำดับ: Good
WHEN wait_mins BETWEEN 11 AND 15 THEN 3 -- ลำดับ: Fair
ELSE 4 -- ลำดับ: Poor
END AS wait_order,
net_sales_thb,
satisfaction_score
FROM transactions
WHERE channel IN ('dine_in', 'to_go') -- เฉพาะช่องทางที่วัดเวลารอหน้าร้านได้
)
SELECT
channel, -- ช่องทางการขาย
wait_time_category, -- ระดับเวลารอ
COUNT(*) AS order_count, -- จำนวนคำสั่งซื้อ
ROUND(AVG(satisfaction_score), 2) AS avg_satisfaction, -- คะแนนความพึงพอใจเฉลี่ย
ROUND(AVG(net_sales_thb), 0) AS avg_order_value -- มูลค่าคำสั่งซื้อเฉลี่ย (บาท)
FROM wait_buckets
GROUP BY channel, wait_time_category, wait_order -- จัดกลุ่มตามช่องทางและระดับเวลารอ
ORDER BY channel, wait_order; -- เรียงตามช่องทางและระดับเวลารอ| channel | wait_time_category | order_count | avg_satisfaction | avg_order_value |
|---|---|---|---|---|
| dine_in | Excellent (0-5 min) | 7,920 | 4.32 | 141 |
| dine_in | Good (6-10 min) | 1,795 | 4.20 | 181 |
| dine_in | Fair (11-15 min) | 230 | 3.43 | 224 |
| dine_in | Poor (16+ min) | 31 | 3.45 | 281 |
| to_go | Excellent (0-5 min) | 17,682 | 4.11 | 138 |
| to_go | Good (6-10 min) | 6,049 | 3.96 | 177 |
| to_go | Fair (11-15 min) | 771 | 3.11 | 256 |
| to_go | Poor (16+ min) | 50 | 3.22 | 305 |
เวลารอที่ยาวขึ้นสัมพันธ์กับคะแนนความพึงพอใจที่ลดลงอย่างชัดเจน โดยเฉพาะในกลุ่ม to-go ที่คะแนนเฉลี่ยลดลงจากมากกว่า 4 เหลือประมาณ 3.1 เมื่อเวลารอเกิน 10 นาที ขณะที่ออเดอร์ที่รอนานมักมีมูลค่าต่อออเดอร์สูงกว่า จึงเป็นกลุ่มที่ควรบริหารความคาดหวังของลูกค้าให้ดี เช่น การแจ้งเวลารอโดยประมาณตั้งแต่ต้น เมื่อต้องการแสดงหลายกลุ่มเป็นคอลัมน์แนวนอนพร้อมกันใน query เดียว จำเป็นต้องฝัง CASE เข้าไปใน aggregate function โดยตรง ซึ่งเป็น pattern ที่เรียกว่า conditional aggregation
3. CASE ภายใน Aggregate
เมื่อเราต้องการสร้าง pivot table เช่น รายได้แต่ละช่องทางในแต่ละสาขา การแตกข้อมูลเป็นหลายแถวจะอ่านยาก จึงนิยมใช้ CASE ภายในฟังก์ชัน SUM หรือ COUNT เพื่อรวมเฉพาะแถวที่เข้าเงื่อนไขหนึ่ง ๆ เทคนิคนี้เรียกว่า conditional aggregation
Pattern หลักมีรูปแบบดังนี้
ทุกแถวที่เข้าเงื่อนไขจะส่งค่า value เข้า SUM ส่วนแถวที่ไม่เข้าเงื่อนไขส่ง 0 ทำให้ได้ผลรวมเฉพาะกลุ่มที่ต้องการในคอลัมน์เดียว ในส่วนนี้จะแสดงตัวอย่างสี่แบบ ตั้งแต่ pivot รายได้ตามช่องทาง การนับออเดอร์วันทำงานเทียบวันหยุด satisfaction distribution และ subsidy ค่าจัดส่ง
ตัวอย่างต่อไปสรุปรายได้ของแต่ละสาขาแยกตามช่องทาง dine_in, to_go และ delivery
-- สรุปรายได้แยกตามช่องทางการขายในแต่ละสาขา (Pivot แนวนอน)
SELECT
s.store_name, -- ชื่อสาขา
ROUND(SUM(CASE WHEN t.channel = 'dine_in' THEN t.net_sales_thb ELSE 0 END), 0) AS dine_in_revenue, -- รายได้ช่องทาง Dine-in (บาท)
ROUND(SUM(CASE WHEN t.channel = 'to_go' THEN t.net_sales_thb ELSE 0 END), 0) AS to_go_revenue, -- รายได้ช่องทาง To-go (บาท)
ROUND(SUM(CASE WHEN t.channel = 'delivery' THEN t.net_sales_thb ELSE 0 END), 0) AS delivery_revenue, -- รายได้ช่องทาง Delivery (บาท)
ROUND(SUM(t.net_sales_thb), 0) AS total_revenue -- รายได้รวมทุกช่องทาง (บาท)
FROM transactions t
INNER JOIN stores s ON t.store_id = s.store_id -- เชื่อมตารางสาขาเพื่อดึงชื่อสาขา
GROUP BY s.store_name -- จัดกลุ่มตามชื่อสาขา
ORDER BY total_revenue DESC; -- เรียงจากสาขาที่มีรายได้รวมสูงสุดไปต่ำสุด| store_name | dine_in_revenue | to_go_revenue | delivery_revenue | total_revenue |
|---|---|---|---|---|
| PiCha centralwOrld | 570,371 | 570,940 | 1,309,803 | 2,451,113 |
| PiCha Siam Square | 168,056 | 744,373 | 1,399,430 | 2,311,860 |
| PiCha ICONSIAM | 455,380 | 391,301 | 917,245 | 1,763,926 |
| PiCha Vanit Village | 167,627 | 556,672 | 741,464 | 1,465,764 |
| PiCha Park Silom | 73,446 | 759,089 | 618,342 | 1,450,877 |
| PiCha Exchange Tower | 67,212 | 703,563 | 573,086 | 1,343,861 |
ตารางนี้แสดงให้เห็นว่า centralwOrld และ Siam Square พึ่งพาช่องทาง delivery สูงมาก ขณะที่ Park Silom และ Exchange Tower ซึ่งเป็นสาขาที่ต้องอยู่ใน office มีสัดส่วนรายได้จาก to_go สูงกว่า delivery อย่างชัดเจน ทำให้เห็นความแตกต่างของ channel mix ตามประเภททำเล
ต้องการดูว่าสาขา office, mall, street มีรูปแบบการใช้ช่วงเวลาต่างกันหรือไม่ เพื่อออกแบบ promotion และจัดกำลังคนที่เหมาะกับแต่ละประเภท
-- สรุปจำนวนออเดอร์แยกตามช่วงเวลาและสัดส่วน Peak Hours ในแต่ละสาขา
SELECT
s.branch_type, -- ประเภทสาขา
s.store_name, -- ชื่อสาขา
SUM(CASE WHEN t.hour BETWEEN 11 AND 13 THEN 1 ELSE 0 END) AS lunch_orders, -- จำนวนออเดอร์ช่วงมื้อกลางวัน (11-13 น.)
SUM(CASE WHEN t.hour BETWEEN 17 AND 19 THEN 1 ELSE 0 END) AS dinner_orders, -- จำนวนออเดอร์ช่วงเย็น (17-19 น.)
SUM(CASE WHEN t.hour BETWEEN 9 AND 10 THEN 1 ELSE 0 END) AS morning_orders, -- จำนวนออเดอร์ช่วงเช้า (9-10 น.)
SUM(CASE WHEN t.peak_flag = 1 THEN 1 ELSE 0 END) AS total_peak, -- จำนวนออเดอร์รวมในชั่วโมงเร่งด่วน
COUNT(*) AS total_orders, -- จำนวนออเดอร์รวมทุกช่วงเวลา
ROUND(100.0 * SUM(CASE WHEN t.peak_flag = 1 THEN 1 ELSE 0 END) / COUNT(*), 1) AS peak_pct -- สัดส่วนออเดอร์ในชั่วโมงเร่งด่วน (%)
FROM transactions t
JOIN stores s ON t.store_id = s.store_id -- เชื่อมตารางสาขาเพื่อดึงชื่อและประเภทสาขา
GROUP BY s.branch_type, s.store_name -- จัดกลุ่มตามประเภทและชื่อสาขา
ORDER BY total_peak DESC; -- เรียงจากสาขาที่มีออเดอร์ Peak Hours มากสุด| branch_type | store_name | lunch_orders | dinner_orders | morning_orders | total_peak | total_orders | peak_pct |
|---|---|---|---|---|---|---|---|
| mall | PiCha centralwOrld | 3,645 | 3,552 | 668 | 7,197 | 11,935 | 60.3 |
| street | PiCha Siam Square | 3,131 | 3,105 | 958 | 6,236 | 10,738 | 58.1 |
| mall_flagship | PiCha ICONSIAM | 2,617 | 2,592 | 534 | 5,209 | 8,702 | 59.9 |
| mixed_use | PiCha Vanit Village | 2,320 | 2,101 | 732 | 4,421 | 7,257 | 60.9 |
| office | PiCha Park Silom | 3,000 | 1,338 | 1,387 | 4,338 | 7,611 | 57.0 |
| office | PiCha Exchange Tower | 2,768 | 1,306 | 1,336 | 4,074 | 7,080 | 57.5 |
ตัวเลขนี้บอกชัดว่าสาขา office และสาขาห้างฯ ต้องการ staffing model คนละแบบ สาขา office ควรเน้น lunch deal และ morning deal ไม่ต้องลงทุน dinner promotion เพราะ demand ไม่มีพอ การจัด shift ควรเป็น “heavy morning + heavy lunch + light evening” ส่วนสาขาห้างฯ ควรกระจาย staff ให้ครอบคลุม 11:00–19:00 สาขา office อาจพิจารณาปิดก่อน 21:00 เพื่อลดค่าใช้จ่าย ถ้า revenue ช่วง 20:00–22:00 ต่ำกว่าต้นทุน staff + เช่า ก็ไม่คุ้ม
Vanit Village มี peak_pct สูงสุด 60.9% ตามด้วย centralwOrld 60.3% สาขา office มี peak_pct ต่ำสุด 57.0–57.5% แต่มี morning_orders สูงสุด (1,336–1,387) สูงกว่าสาขาอื่นเท่าตัว สาขา office มี dinner_orders ต่ำมาก (1,306–1,338) เทียบกับสาขาห้างฯ (2,101–3,552) เพราะพนักงานเลิกงาน 17:00–18:00 แล้วเดินทางกลับบ้าน ไม่อยู่แถวสาขา ที่น่าสังเกตคือ lunch_orders ของ Park Silom (3,000) สูงใกล้เคียง centralwOrld (3,645) ทั้งที่มี total_orders น้อยกว่ามาก lunch concentration ของสาขา office จึงสูงผิดปกติ ซึ่งยืนยัน staffing model ข้างต้น
ตัวอย่างต่อไปใช้ conditional aggregation นับจำนวนออเดอร์แยกวันทำงานและวันหยุดสุดสัปดาห์สำหรับแต่ละช่องทาง
-- เปรียบเทียบจำนวนคำสั่งซื้อวันธรรมดาและวันหยุดสุดสัปดาห์แยกตามช่องทางการขาย
SELECT
t.channel, -- ช่องทางการขาย
SUM(CASE WHEN t.weekend_flag = 0 THEN 1 ELSE 0 END) AS weekday_orders, -- จำนวนคำสั่งซื้อวันธรรมดา
SUM(CASE WHEN t.weekend_flag = 1 THEN 1 ELSE 0 END) AS weekend_orders, -- จำนวนคำสั่งซื้อวันหยุดสุดสัปดาห์
COUNT(*) AS total_orders, -- จำนวนคำสั่งซื้อรวม
ROUND(100.0 * SUM(CASE WHEN t.weekend_flag = 1 THEN 1 ELSE 0 END) / COUNT(*), 1) AS weekend_pct -- สัดส่วนคำสั่งซื้อวันหยุดสุดสัปดาห์ (%)
FROM transactions t
GROUP BY t.channel -- จัดกลุ่มตามช่องทางการขาย
ORDER BY total_orders DESC; -- เรียงจากช่องทางที่มีคำสั่งซื้อมากสุดไปน้อยสุด| channel | weekday_orders | weekend_orders | total_orders | weekend_pct |
|---|---|---|---|---|
| to_go | 19,342 | 5,210 | 24,552 | 21.2 |
| delivery | 12,446 | 6,349 | 18,795 | 33.8 |
| dine_in | 5,302 | 4,674 | 9,976 | 46.9 |
จากตารางนี้เห็นว่าด้วย pattern เดียวกัน เราสามารถเปรียบเทียบพฤติกรรมวันทำงานและวันหยุดของแต่ละช่องทางได้อย่างชัดเจน เช่น dine-in พึ่งพาวันหยุดสุดสัปดาห์มากที่สุด ขณะที่ to-go กระจุกตัวในวันทำงาน pattern เดียวกันนี้ยังนำมาสร้าง pivot ของข้อมูล categorical scores ได้เช่นกัน ดังที่จะเห็นในตัวอย่างถัดไป
ตัวอย่างถัดไปแสดงการใช้ conditional aggregation เพื่อสร้างตารางแสดงคะแนนความพึงพอใจและคำนวณสัดส่วน Top-2-Box (คะแนน 4–5) แยกตามช่องทาง
-- วิเคราะห์การกระจายคะแนนความพึงพอใจและสัดส่วน Top-2-Box แยกตามช่องทางการขาย
SELECT
t.channel, -- ช่องทางการขาย
SUM(CASE WHEN t.satisfaction_score = 5 THEN 1 ELSE 0 END) AS score_5, -- จำนวนออเดอร์ที่ได้คะแนน 5
SUM(CASE WHEN t.satisfaction_score = 4 THEN 1 ELSE 0 END) AS score_4, -- จำนวนออเดอร์ที่ได้คะแนน 4
SUM(CASE WHEN t.satisfaction_score = 3 THEN 1 ELSE 0 END) AS score_3, -- จำนวนออเดอร์ที่ได้คะแนน 3
SUM(CASE WHEN t.satisfaction_score = 2 THEN 1 ELSE 0 END) AS score_2, -- จำนวนออเดอร์ที่ได้คะแนน 2
SUM(CASE WHEN t.satisfaction_score = 1 THEN 1 ELSE 0 END) AS score_1, -- จำนวนออเดอร์ที่ได้คะแนน 1
COUNT(*) AS total, -- จำนวนออเดอร์รวม
ROUND(100.0 * SUM(CASE WHEN t.satisfaction_score >= 4 THEN 1 ELSE 0 END) / COUNT(*), 1) AS top2_box_pct -- สัดส่วน Top-2-Box
FROM transactions t
GROUP BY t.channel -- จัดกลุ่มตามช่องทาง
ORDER BY top2_box_pct DESC; -- เรียงจากช่องทางที่มี Top-2-Box สูงสุด| channel | score_5 | score_4 | score_3 | score_2 | score_1 | total | top2_box_pct |
|---|---|---|---|---|---|---|---|
| dine_in | 3,752 | 5,232 | 967 | 25 | 0 | 9,976 | 90.1 |
| to_go | 5,980 | 13,816 | 4,535 | 216 | 5 | 24,552 | 80.6 |
| delivery | 4,289 | 8,994 | 4,869 | 635 | 8 | 18,795 | 70.7 |
จากตารางนี้เห็นได้อย่างรวดเร็วว่า dine-in ให้ประสบการณ์ดีที่สุด (Top-2-Box มากกว่า 90%) ขณะที่ delivery มีสัดส่วนคะแนน 4–5 ต่ำสุดและคะแนนต่ำ (1–2) สูงกว่า ทำให้เห็นจุดแข็งและจุดอ่อนของแต่ละช่องทางในมุมคุณภาพบริการ โดยใช้ CASE ร่วมกับ aggregate เพียง query เดียว conditional aggregation ยังนำมาวิเคราะห์พฤติกรรมแพลตฟอร์มในการให้ subsidy ค่าจัดส่งได้เช่นกัน โดยใช้ CASE ตรวจว่า subsidy มีค่ามากกว่าศูนย์หรือไม่ เพื่อคำนวณสัดส่วนออเดอร์ที่ได้รับ subsidy
ตัวอย่างสุดท้ายของการใช้ searched CASE เพื่อแบ่งออเดอร์แบบ delivery ตามมูลค่า และวิเคราะห์ค่าจัดส่งและ subsidy จากแพลตฟอร์มในแต่ละระดับ
-- วิเคราะห์ค่าจัดส่งและ Subsidy จากแพลตฟอร์มแยกตามระดับมูลค่าออเดอร์ (เฉพาะช่องทาง Delivery)
SELECT
order_segment, -- ระดับมูลค่าออเดอร์
order_count, -- จำนวนคำสั่งซื้อ
avg_delivery_fee, -- ค่าจัดส่งเฉลี่ยที่ลูกค้าจ่าย (บาท)
avg_subsidy, -- Subsidy ค่าจัดส่งเฉลี่ยจากแพลตฟอร์ม (บาท)
subsidy_rate_pct -- สัดส่วนออเดอร์ที่ได้รับ Subsidy (%)
FROM (
SELECT
CASE
WHEN t.net_sales_thb >= 300 THEN 'Premium (300+)' -- ออเดอร์มูลค่าสูง
WHEN t.net_sales_thb >= 200 THEN 'Standard (200-299)' -- ออเดอร์มูลค่าปานกลาง
WHEN t.net_sales_thb >= 150 THEN 'Basic (150-199)' -- ออเดอร์มูลค่าต่ำ
ELSE 'Budget (<150)' -- ออเดอร์มูลค่าต่ำมาก
END AS order_segment,
CASE
WHEN t.net_sales_thb >= 300 THEN 1 -- ลำดับสำหรับเรียง: Premium
WHEN t.net_sales_thb >= 200 THEN 2 -- ลำดับสำหรับเรียง: Standard
WHEN t.net_sales_thb >= 150 THEN 3 -- ลำดับสำหรับเรียง: Basic
ELSE 4 -- ลำดับสำหรับเรียง: Budget
END AS seg_order,
COUNT(*) AS order_count, -- จำนวนคำสั่งซื้อในแต่ละกลุ่ม
ROUND(AVG(t.delivery_fee_paid_by_customer), 2) AS avg_delivery_fee, -- ค่าจัดส่งเฉลี่ยที่ลูกค้าจ่าย (บาท)
ROUND(AVG(t.platform_subsidy_thb), 2) AS avg_subsidy, -- Subsidy เฉลี่ยจากแพลตฟอร์ม (บาท)
ROUND(
100.0 * SUM(CASE WHEN t.platform_subsidy_thb > 0 THEN 1 ELSE 0 END) / COUNT(*),
1
) AS subsidy_rate_pct -- สัดส่วนออเดอร์ที่ได้รับ Subsidy (%)
FROM transactions t
WHERE t.channel = 'delivery' -- กรองเฉพาะช่องทาง Delivery
GROUP BY order_segment, seg_order -- จัดกลุ่มตามระดับมูลค่าออเดอร์และลำดับ
) -- Subquery: จัดกลุ่มและคำนวณเมตริกหลัก
ORDER BY seg_order; -- เรียงจากระดับมูลค่าสูงสุดไปต่ำสุด| order_segment | order_count | avg_delivery_fee | avg_subsidy | subsidy_rate_pct |
|---|---|---|---|---|
| Premium (300+) | 8,099 | 32.28 | 8.83 | 50 |
| Standard (200-299) | 6,108 | 29.43 | 7.6 | 47 |
| Basic (150-199) | 2,487 | 35.13 | 0.04 | 0.5 |
| Budget (<150) | 2,101 | 24.02 | 0 | 0 |
จากตารางจะเห็นว่าแพลตฟอร์มให้ subsidy ค่าจัดส่งอย่างมีนัยสำคัญกับออเดอร์ตั้งแต่ 200 บาทขึ้นไป (ประมาณครึ่งหนึ่งของออเดอร์ได้รับ subsidy) แต่แทบไม่ให้กับออเดอร์ต่ำกว่า 200 บาท ข้อมูลนี้ชี้โอกาสในการออกแบบข้อความหรือโปรโมชันที่ชวนให้ลูกค้าเพิ่มยอดสั่งซื้อจากช่วง 150–199 บาทให้เกิน 200 บาทเพื่อใช้ประโยชน์จาก subsidy และเพิ่มรายได้ของร้านไปพร้อมกัน เมื่อเชี่ยวชาญการฝัง CASE หนึ่งตัวใน aggregate แล้ว ขั้นต่อไปคือการใช้ CASE หลายตัวพร้อมกันใน query เดียว เพื่อวิเคราะห์สองมิติพร้อมกันในแถวเดียว
4. Multiple CASE และ Crosstab Analytics
สามารถใช้ CASE หลายตัวใน query เดียว เพื่อสร้างตาราง crosstab ที่แสดงความสัมพันธ์ระหว่างสองมิติพร้อมกัน เช่น กลุ่มลูกค้า × ขนาดตะกร้าสินค้า หรือระดับความหวาน × รายได้เฉลี่ยต่อรายการ นอกจากนี้ยังนำมาคำนวณ effective rate ที่ต้องป้องกัน division-by-zero ด้วย NULLIF ได้อีกด้วย
ตัวอย่างนี้แบ่งลูกค้าเป็น 5 กลุ่ม (Anonymous, Gold, Silver, Member, Non-Member) และแบ่ง basket size ตามจำนวนรายการที่สั่ง แล้วสรุปจำนวนออเดอร์และรายได้รวมในแต่ละเซลล์ของ cross-tab
-- วิเคราะห์รายได้และพฤติกรรมการสั่งซื้อแยกตามกลุ่มลูกค้าและขนาดตะกร้าสินค้า
SELECT
customer_segment, -- กลุ่มลูกค้า
basket_size, -- ขนาดตะกร้าสินค้า
order_count, -- จำนวนคำสั่งซื้อ
avg_order_value, -- มูลค่าคำสั่งซื้อเฉลี่ย (บาท)
total_revenue -- รายได้รวม (บาท)
FROM (
SELECT
CASE
WHEN c.customer_id IS NULL THEN 'Anonymous' -- ลูกค้าที่ไม่ระบุตัวตน
WHEN c.loyalty_tier = 'gold' THEN 'Gold Member' -- สมาชิกระดับ Gold
WHEN c.loyalty_tier = 'silver' THEN 'Silver Member' -- สมาชิกระดับ Silver
WHEN c.loyalty_tier = 'member' THEN 'Member' -- สมาชิกทั่วไป
ELSE 'Non-Member' -- ลูกค้าที่ไม่ได้เป็นสมาชิก
END AS customer_segment,
CASE
WHEN t.basket_qty >= 4 THEN 'Full Basket (4)' -- สั่งซื้อ 4 รายการขึ้นไป
WHEN t.basket_qty = 3 THEN 'Triple (3)' -- สั่งซื้อ 3 รายการ
WHEN t.basket_qty = 2 THEN 'Double (2)' -- สั่งซื้อ 2 รายการ
ELSE 'Single (1)' -- สั่งซื้อ 1 รายการ
END AS basket_size,
COUNT(*) AS order_count, -- จำนวนคำสั่งซื้อ
ROUND(AVG(t.net_sales_thb), 0) AS avg_order_value, -- มูลค่าคำสั่งซื้อเฉลี่ย (บาท)
ROUND(SUM(t.net_sales_thb), 0) AS total_revenue -- รายได้รวม (บาท)
FROM transactions t
LEFT JOIN customers c ON t.customer_id = c.customer_id -- LEFT JOIN เพื่อรวมลูกค้าที่ไม่มีข้อมูล
GROUP BY customer_segment, basket_size -- จัดกลุ่มตามกลุ่มลูกค้าและขนาดตะกร้า
) -- Subquery: จัดกลุ่มและคำนวณเมตริกหลัก
ORDER BY total_revenue DESC -- เรียงจากกลุ่มที่มีรายได้รวมสูงสุด
LIMIT 15; -- แสดงเพียง 15 แถวแรก| customer_segment | basket_size | order_count | avg_order_value | total_revenue |
|---|---|---|---|---|
| Anonymous | Double (2) | 10,591 | 246 | 2,605,582 |
| Anonymous | Single (1) | 21,534 | 117 | 2,517,206 |
| Anonymous | Triple (3) | 6,083 | 357 | 2,171,213 |
| Anonymous | Full Basket (4) | 1,413 | 502 | 709,464 |
| Member | Double (2) | 1,291 | 248 | 320,726 |
ตารางนี้ชี้ให้เห็นว่า anonymous orders มีสัดส่วนรายได้จำนวนมาก โดยเฉพาะเมื่อสั่งตั้งแต่ 2 แก้วขึ้นไป ซึ่งมี AOV ใกล้เคียงหรือสูงกว่าสมาชิก การแปลงลูกค้า anonymous ที่มี basket ใหญ่ให้กลายเป็นสมาชิกจึงมีศักยภาพสูง โดยไม่จำเป็นต้องเพิ่มการใช้จ่ายต่อออเดอร์มากนัก
ตัวอย่างถัดไปใช้ CASE จัดระดับความหวานจากเปอร์เซ็นต์น้ำตาล (sweetness_pct) เพื่อดู distribution ของ preference และรายได้เฉลี่ยต่อรายการในแต่ละกลุ่ม
-- วิเคราะห์รายได้และส่วนลดแยกตามระดับความหวานที่ลูกค้าเลือก
SELECT
sweetness_bucket, -- ระดับความหวาน
line_count, -- จำนวนรายการสินค้า
avg_line_revenue, -- รายได้เฉลี่ยต่อรายการ (บาท)
avg_discount -- ส่วนลดเฉลี่ยต่อรายการ (บาท)
FROM (
SELECT
CASE
WHEN oi.sweetness_pct = 0 THEN 'No Sugar (0%)' -- ไม่มีน้ำตาล
WHEN oi.sweetness_pct <= 25 THEN 'Light (1-25%)' -- หวานน้อย
WHEN oi.sweetness_pct <= 50 THEN 'Medium (26-50%)' -- หวานปานกลาง
WHEN oi.sweetness_pct <= 75 THEN 'Sweet (51-75%)' -- หวาน
ELSE 'Very Sweet (76-100%)' -- หวานมาก
END AS sweetness_bucket,
CASE
WHEN oi.sweetness_pct = 0 THEN 1 -- ลำดับสำหรับเรียง: No Sugar
WHEN oi.sweetness_pct <= 25 THEN 2 -- ลำดับสำหรับเรียง: Light
WHEN oi.sweetness_pct <= 50 THEN 3 -- ลำดับสำหรับเรียง: Medium
WHEN oi.sweetness_pct <= 75 THEN 4 -- ลำดับสำหรับเรียง: Sweet
ELSE 5 -- ลำดับสำหรับเรียง: Very Sweet
END AS bucket_order,
COUNT(*) AS line_count, -- จำนวนรายการสินค้า
ROUND(AVG(oi.net_line_sales_thb), 2) AS avg_line_revenue, -- รายได้เฉลี่ยต่อรายการ (บาท)
ROUND(AVG(oi.line_discount_thb), 2) AS avg_discount -- ส่วนลดเฉลี่ยต่อรายการ (บาท)
FROM order_items oi -- ตารางรายการสินค้าในแต่ละคำสั่งซื้อ
GROUP BY sweetness_bucket, bucket_order -- จัดกลุ่มตามระดับความหวานและลำดับ
) -- Subquery: จัดกลุ่มและคำนวณเมตริกหลัก
ORDER BY bucket_order; -- เรียงจากระดับความหวานน้อยสุดไปมากสุด| sweetness_bucket | line_count | avg_line_revenue | avg_discount |
|---|---|---|---|
| No Sugar (0%) | 2,495 | 115.39 | 2.58 |
| Light (1-25%) | 10,194 | 115.19 | 2.88 |
| Medium (26-50%) | 27,011 | 114.86 | 2.90 |
| Sweet (51-75%) | 31,715 | 114.67 | 2.95 |
| Very Sweet (76-100%) | 28,510 | 114.85 | 2.95 |
กลุ่ม Sweet และ Very Sweet รวมกันคิดเป็นมากกว่า 60% ของ line items แสดงว่าลูกค้าส่วนใหญ่ยังนิยมความหวานค่อนข้างสูง ในขณะที่กลุ่ม No Sugar และ Light มีสัดส่วนรวมกันไม่ถึง 15% แต่มีค่าเฉลี่ยรายได้ต่อรายการใกล้เคียงกลุ่มอื่น แสดงว่านี่เป็น segment ที่มีมูลค่าแต่ยังเล็ก การออกแบบผลิตภัณฑ์หรือแคมเปญเฉพาะสำหรับกลุ่มหวานน้อยจึงเป็นโอกาสขยายฐานลูกค้าโดยไม่ลดรายได้ต่อรายการ CASE หลายตัวไม่ได้จำกัดเฉพาะการสร้าง label ใน SELECT เท่านั้น แต่ยังนำมาคำนวณเมตริกทางธุรกิจที่ต้องป้องกัน division-by-zero ได้อีกด้วย โดยใช้ NULLIF เป็น guard
ตัวอย่างสุดท้ายและการวิเคราะห์ผลกระทบของค่าคอมมิชชันแพลตฟอร์มต่อกำไรส่วนเกินแยกตามช่องทางและแพลตฟอร์ม
มีการใช้ฟังก์ชัน NULLIF โดย NULLIF(a, b) คืนค่า NULL เมื่อ a = b และคืนค่า a ตามปกติในทุกกรณีอื่น เมื่อนำไปเป็นตัวหาร การหารจะได้ NULL แทนที่จะเกิด error เมื่อตัวหารเป็นศูนย์ ตัวอย่างนี้ใช้ NULLIF คำนวณ effective commission rate และเปรียบเทียบกำไรส่วนเกินหลังหักค่าคอมมิชชันของแต่ละช่องทางและแพลตฟอร์ม
-- วิเคราะห์ผลกระทบของค่าคอมมิชชันแพลตฟอร์มต่อกำไรส่วนเกินแยกตามช่องทางและแพลตฟอร์ม
SELECT
t.channel, -- ช่องทางการขาย
t.platform, -- ชื่อแพลตฟอร์ม
COUNT(*) AS order_count, -- จำนวนคำสั่งซื้อ
ROUND(AVG(t.net_sales_thb), 0) AS avg_revenue, -- รายได้เฉลี่ยต่อคำสั่งซื้อ (บาท)
ROUND(AVG(t.platform_commission_thb), 2) AS avg_commission, -- ค่าคอมมิชชันแพลตฟอร์มเฉลี่ย (บาท)
ROUND(AVG(t.contribution_margin_thb), 0) AS avg_cm, -- กำไรส่วนเกินเฉลี่ยหลังหักค่าคอมมิชชัน (บาท)
ROUND(
AVG(t.contribution_margin_thb) + AVG(t.platform_commission_thb),
0
) AS cm_without_commission, -- กำไรส่วนเกินเฉลี่ยก่อนหักค่าคอมมิชชัน (บาท)
ROUND(
100.0 * AVG(t.platform_commission_thb) / NULLIF(AVG(t.net_sales_thb), 0), -- หาร NULLIF เพื่อป้องกัน division by zero
1
) AS effective_commission_pct -- อัตราค่าคอมมิชชันที่แท้จริง (%)
FROM transactions t
GROUP BY t.channel, t.platform -- จัดกลุ่มตามช่องทางและแพลตฟอร์ม
ORDER BY t.channel, avg_cm DESC; -- เรียงตามช่องทาง จากนั้นเรียงตามกำไรส่วนเกินสูงสุด| channel | platform | order_count | avg_revenue | avg_commission | avg_cm | cm_without_commission | effective_commission_pct |
|---|---|---|---|---|---|---|---|
| delivery | C2U | 7,648 | 296.0 | 51.3 | 142.0 | 193.0 | 17.3 |
| delivery | YumDash | 11,147 | 296.0 | 62.07 | 131.0 | 193.0 | 21.0 |
| dine_in | direct | 9,976 | 151.0 | 0.0 | 95.0 | 95.0 | 0.0 |
| to_go | direct | 24,552 | 152.0 | 0.0 | 96.0 | 96.0 | 0.0 |
แม้รายได้เฉลี่ยต่อออเดอร์ของ C2U และ YumDash จะเท่ากัน แต่ค่าคอมมิชชันเฉลี่ยของ YumDash สูงกว่า 10.77 บาทต่อออเดอร์ (62.07 – 15.3) ส่งผลให้กำไรส่วนเกินหลังหักค่าคอมมิชชันของ YumDash ต่ำกว่า C2U การมองข้อมูลในระดับนี้ช่วยให้ผู้บริหารประเมิน trade-off ระหว่าง volume ที่แพลตฟอร์มดึงมาให้กับต้นทุนค่าคอมมิชชันที่ต้องจ่าย
ค่าคอมมิชชันกิน contribution margin ของ delivery อย่างมีนัยสำคัญ แม้ delivery จะมี avg_revenue สูงถึง 296 บาท แต่เมื่อหักค่าคอมมิชชันแล้ว กำไรส่วนเกิน (avg_cm) เหลือเพียง 131–142 บาท ต่ำกว่า dine_in และ to_go ที่ทำได้ 95–96 บาทโดยไม่ต้องจ่ายค่าคอมมิชชันเลย
YumDash แพงกว่า C2U อย่างชัดเจน ทั้งที่ avg_revenue ไม่ต่างกัน แต่ค่าคอมมิชชันของ YumDash สูงกว่า C2U เกือบ 11 บาทต่อออร์เดอร์ คิดเป็นอัตราคอมมิชชัน 21.0% เทียบกับ 17.3% และเมื่อพิจารณาจากจำนวนออร์เดอร์ของ YumDash ที่มีถึง 11,147 ออร์เดอร์ ส่วนต่างนี้สะสมเป็น กำไรที่หายไปกว่า 120,000 บาท ตลอดช่วงข้อมูล 2 เดือนที่ผ่านมา
หากไม่มีค่าคอมมิชชัน delivery จะมี contribution margin ถึง 193 บาทต่อออร์เดอร์ ซึ่งสูงกว่า dine_in และ to_go ถึง 2 เท่า แสดงว่าตัวสินค้าและ pricing ของ delivery มีศักยภาพสูงมาก ปัญหาอยู่ที่โครงสร้างค่าธรรมเนียมแพลตฟอร์มเท่านั้น
ดังนั้น PiCha ควรเจรจาต่อรองอัตราคอมมิชชันกับ YumDash ให้เข้าใกล้ระดับของ C2U หรือตั้งราคาขายบน YumDash สูงกว่า C2U เพื่อชดเชยส่วนต่าง และในระยะยาวควรผลักดันให้ลูกค้าสั่งผ่านช่องทาง direct ของทางร้าน เพื่อประหยัดค่าคอมมิชชันและรักษา contribution margin
สรุป
ในบทนี้ เราได้เรียนรู้การใช้ CASE และฟังก์ชันที่เกี่ยวข้องเพื่อเปลี่ยนข้อมูลดิบให้กลายเป็นข้อมูลที่ตีความได้ง่ายขึ้น ทั้งในมุมของการจัดหมวดหมู่ การสรุปผล และการสร้างตัวชี้วัดทางธุรกิจจากเงื่อนไขที่ซับซ้อนขึ้น ฟังก์ชันเหล่านี้มีบทบาทสำคัญมากในงานวิเคราะห์ข้อมูล เพราะช่วยให้เรากำหนด “ความหมายทางธุรกิจ” ลงไปใน query ได้โดยตรง
แนวคิดสำคัญอย่าง Simple CASE เหมาะกับสถานการณ์ที่ค่าต้นทางมีชุดคำตอบชัดเจนอยู่แล้ว เช่น ประเภทช่องทางขายหรือสถานะสภาพอากาศ ซึ่งเราต้องการแปลงให้เป็นข้อความที่อ่านเข้าใจง่ายขึ้น ส่วน Searched CASE เหมาะกับงานที่ต้องใช้เงื่อนไขเชิงตรรกะมากกว่า เช่น การแบ่งช่วงเวลา การจัดระดับรายได้ หรือการสร้างกลุ่มตามช่วงค่าตัวเลข
อีกแนวคิดหนึ่งที่สำคัญคือ Conditional Aggregation ซึ่งช่วยให้เราสรุปข้อมูลแบบแนวนอนได้ใน query เดียว เช่น การเปรียบเทียบรายได้ตามช่องทางขายและสาขา หรือการคำนวณตัวชี้วัดเฉพาะกลุ่มอย่าง Top-2-Box และอัตราส่วนเงินสนับสนุน ขณะที่การใช้ Multiple CASE ในคำสั่งเดียวกันช่วยให้เราวิเคราะห์ข้อมูลหลายมิติพร้อมกันได้ เช่น การดูความสัมพันธ์ระหว่างกลุ่มลูกค้ากับขนาดตะกร้าสินค้า หรือการคำนวณอัตราค่าคอมมิชชันที่มีเงื่อนไขประกอบหลายชั้น
นอกจากนี้ NULLIF เป็นฟังก์ชันเล็ก ๆ ที่มีประโยชน์มากในทางปฏิบัติ เพราะช่วยป้องกันข้อผิดพลาดจากการหารด้วยศูนย์ ทำให้ query มีความปลอดภัยและเหมาะกับการนำไปใช้ในรายงานหรือ pipeline จริงมากขึ้น
| แนวคิด | ใช้เมื่อ | ตัวอย่างในบทนี้ |
|---|---|---|
Simple CASE |
เมื่อต้องการแปลงค่าหมวดหมู่ที่มีค่าตายตัวและรู้ล่วงหน้า | แปลง channel และ weather_flag เป็นชื่อภาษาไทย |
Searched CASE |
เมื่อต้องการจัดกลุ่มค่าต่อเนื่องหรือเขียนเงื่อนไขที่ซับซ้อนขึ้น | แบ่งช่วงเวลา, revenue_tier, wait_time_category |
Conditional Aggregation |
เมื่อต้องการสรุปข้อมูลแบบ pivot table แนวนอนหรือคำนวณค่าตามเงื่อนไขภายใน aggregate | รายได้ channel × store, Top-2-Box, subsidy_rate_pct |
Multiple CASE |
เมื่อต้องการวิเคราะห์หลายมิติภายใน query เดียว | customer_segment × basket_size, effective_commission_pct |
NULLIF |
เมื่อต้องการป้องกันปัญหา division-by-zero | คำนวณ effective_commission_pct |
เมื่อมองภาพรวม เครื่องมือเหล่านี้ไม่ได้มีไว้เพียงเพื่อเขียนเงื่อนไขใน SQL เท่านั้น แต่ยังช่วยให้เราสร้าง logic ทางธุรกิจลงไปในข้อมูลได้อย่างชัดเจน ตั้งแต่การตั้งชื่อกลุ่ม การแบ่งระดับ การเปรียบเทียบผลลัพธ์ ไปจนถึงการคำนวณตัวชี้วัดที่พร้อมใช้ในการตัดสินใจ
คำถามท้ายบท
- ใช้
simple CASEแปลงweather_flagเป็นภาษาไทย (sunny = แดดจัด, rainy = ฝนตก, cloudy = เมฆมาก) และสรุปจำนวนออเดอร์และรายได้รวมในแต่ละสภาพอากาศ
ผลลัพธ์ที่คาดหวัง
| weather_thai | order_count | total_revenue |
|---|---|---|
| แดดจัด | 39,592 | 7,994,476 |
| ฝนตก | 10,065 | 2,031,867 |
| เมฆมาก | 3,666 | 761,058 |
คลิกเพื่อดูเฉลย
-- สรุปจำนวนคำสั่งซื้อและรายได้แยกตามสภาพอากาศ (แสดงชื่อภาษาไทย)
SELECT
CASE weather_flag
WHEN 'sunny' THEN 'แดดจัด' -- วันแดดจัด
WHEN 'rainy' THEN 'ฝนตก' -- วันฝนตก
WHEN 'cloudy' THEN 'เมฆมาก' -- วันเมฆมาก
ELSE 'อื่น ๆ' -- สภาพอากาศอื่น
END AS weather_thai,
COUNT(*) AS order_count, -- จำนวนคำสั่งซื้อ
ROUND(SUM(net_sales_thb), 0) AS total_revenue -- รายได้รวม (บาท)
FROM transactions -- ตารางหลักที่เก็บข้อมูลธุรกรรม
GROUP BY weather_flag, weather_thai -- จัดกลุ่มตามสภาพอากาศ
ORDER BY total_revenue DESC; -- เรียงจากสภาพอากาศที่มีรายได้รวมสูงสุด- ใช้
searched CASEจัดกลุ่มรายได้ต่อออเดอร์เป็น 3 ระดับ: สูง (>=250), ปานกลาง (150-249), ต่ำ (<150) พร้อมแสดงจำนวนออเดอร์และรายได้รวมในแต่ละระดับ
ผลลัพธ์ที่คาดหวัง
| revenue_level | order_count | total_revenue |
|---|---|---|
| สูง (250+) | 16,655 | 5,747,620 |
| ต่ำ (<150) | 25,379 | 2,794,765 |
| ปานกลาง (150-249) | 11,289 | 2,245,016 |
คลิกเพื่อดูเฉลย
-- จัดกลุ่มคำสั่งซื้อตามระดับรายได้และสรุปยอดรวมในแต่ละกลุ่ม
SELECT
CASE
WHEN net_sales_thb >= 250 THEN 'สูง (250+)' -- กลุ่มรายได้สูง
WHEN net_sales_thb >= 150 THEN 'ปานกลาง (150-249)' -- กลุ่มรายได้ปานกลาง
ELSE 'ต่ำ (<150)' -- กลุ่มรายได้ต่ำ
END AS revenue_level,
COUNT(*) AS order_count, -- จำนวนคำสั่งซื้อในแต่ละกลุ่ม
ROUND(SUM(net_sales_thb), 0) AS total_revenue -- รายได้รวมของแต่ละกลุ่ม (บาท)
FROM transactions -- ตารางหลักที่เก็บข้อมูลธุรกรรม
GROUP BY revenue_level -- จัดกลุ่มตามระดับรายได้
ORDER BY total_revenue DESC; -- เรียงจากกลุ่มที่มีรายได้รวมสูงสุด- ใช้ conditional aggregation สร้างตารางแสดงจำนวนออเดอร์แยกตาม
channel(3 คอลัมน์) และbranch_type(แถว)
ผลลัพธ์ที่คาดหวัง
| branch_type | dine_in_orders | to_go_orders | delivery_orders | total_orders |
|---|---|---|---|---|
| office | 922 | 9,614 | 4,155 | 14,691 |
| mall | 3,824 | 3,737 | 4,374 | 11,935 |
| street | 1,109 | 4,901 | 4,728 | 10,738 |
| mall_flagship | 3,036 | 2,657 | 3,009 | 8,702 |
| mixed_use | 1,085 | 3,643 | 2,529 | 7,257 |
คลิกเพื่อดูเฉลย
-- สรุปจำนวนคำสั่งซื้อแยกตามช่องทางการขายในแต่ละประเภทสาขา (Pivot แนวนอน)
SELECT
s.branch_type, -- ประเภทสาขา
SUM(CASE WHEN t.channel = 'dine_in' THEN 1 ELSE 0 END) AS dine_in_orders, -- จำนวนคำสั่งซื้อช่องทาง Dine-in
SUM(CASE WHEN t.channel = 'to_go' THEN 1 ELSE 0 END) AS to_go_orders, -- จำนวนคำสั่งซื้อช่องทาง To-go
SUM(CASE WHEN t.channel = 'delivery' THEN 1 ELSE 0 END) AS delivery_orders, -- จำนวนคำสั่งซื้อช่องทาง Delivery
COUNT(*) AS total_orders -- จำนวนคำสั่งซื้อรวมทุกช่องทาง
FROM transactions t
JOIN stores s ON t.store_id = s.store_id -- เชื่อมตารางสาขาเพื่อดึงประเภทสาขา
GROUP BY s.branch_type -- จัดกลุ่มตามประเภทสาขา
ORDER BY total_orders DESC; -- เรียงจากประเภทสาขาที่มีคำสั่งซื้อมากสุดไปน้อยสุด- ใช้
COALESCEร่วมกับCASEสร้างรายงานที่แสดง “ลูกค้าสมาชิก” สำหรับloyalty_tierที่ไม่ใช่NULLและ “ลูกค้าทั่วไป” สำหรับ NULL
ผลลัพธ์ที่คาดหวัง
| customer_group | order_count | avg_order_value | avg_satisfaction |
|---|---|---|---|
| ลูกค้าสมาชิก | 10,703 | 203.0 | 4.07 |
| ลูกค้าทั่วไป | 42,620 | 202.0 | 4.03 |
คลิกเพื่อดูเฉลย
-- เปรียบเทียบพฤติกรรมการสั่งซื้อและความพึงพอใจระหว่างลูกค้าสมาชิกและลูกค้าทั่วไป
SELECT
CASE
WHEN COALESCE(c.loyalty_tier, 'none') != 'none' THEN 'ลูกค้าสมาชิก' -- มีข้อมูล loyalty tier
ELSE 'ลูกค้าทั่วไป' -- ไม่มีข้อมูลหรือ tier เป็น none
END AS customer_group,
COUNT(*) AS order_count, -- จำนวนคำสั่งซื้อ
ROUND(AVG(t.net_sales_thb), 0) AS avg_order_value, -- มูลค่าคำสั่งซื้อเฉลี่ย (บาท)
ROUND(AVG(t.satisfaction_score), 2) AS avg_satisfaction -- คะแนนความพึงพอใจเฉลี่ย
FROM transactions t
LEFT JOIN customers c ON t.customer_id = c.customer_id -- LEFT JOIN เพื่อรวมคำสั่งซื้อที่ไม่มีข้อมูลลูกค้า
GROUP BY customer_group -- จัดกลุ่มตามประเภทลูกค้า
ORDER BY avg_order_value DESC; -- เรียงจากกลุ่มที่มีมูลค่าคำสั่งซื้อเฉลี่ยสูงสุด- ใช้
NULLIFคำนวณ ratio ของ item_discount ต่อ order_count สำหรับแต่ละสาขา โดยป้องกันกรณี order_count = 0
ผลลัพธ์ที่คาดหวัง
| store_name | order_count | total_item_discount | discount_per_order |
|---|---|---|---|
| PiCha ICONSIAM | 8,702 | 80,931.0 | 9.3 |
| PiCha Siam Square | 10,738 | 50,591.0 | 4.71 |
| PiCha centralwOrld | 11,935 | 55,873.0 | 4.68 |
| PiCha Vanit Village | 7,257 | 25,270.0 | 3.48 |
| PiCha Exchange Tower | 7,080 | 24,314.0 | 3.43 |
| PiCha Park Silom | 7,611 | 25,744.0 | 3.38 |
คลิกเพื่อดูเฉลย
-- สรุปส่วนลดรายการสินค้าเฉลี่ยต่อคำสั่งซื้อแยกตามสาขา
SELECT
s.store_name, -- ชื่อสาขา
COUNT(*) AS order_count, -- จำนวนคำสั่งซื้อ
ROUND(SUM(t.item_discount_thb), 0) AS total_item_discount, -- ส่วนลดรายการสินค้ารวม (บาท)
ROUND(
SUM(t.item_discount_thb) / NULLIF(COUNT(*), 0), -- หาร NULLIF เพื่อป้องกัน division by zero
2
) AS discount_per_order -- ส่วนลดเฉลี่ยต่อคำสั่งซื้อ (บาท)
FROM transactions t
JOIN stores s ON t.store_id = s.store_id -- เชื่อมตารางสาขาเพื่อดึงชื่อสาขา
GROUP BY s.store_name -- จัดกลุ่มตามชื่อสาขา
ORDER BY discount_per_order DESC; -- เรียงจากสาขาที่มีส่วนลดเฉลี่ยต่อออเดอร์สูงสุด