วัตถุประสงค์การเรียนรู้
เมื่อจบบทนี้ ผู้เรียนจะสามารถ
- หมุนตารางจากแนวตั้งเป็นแนวนอนด้วย PIVOT เพื่อสร้างรายงาน crosstab
- แปลงตารางจากแนวนอนกลับเป็นแนวตั้งด้วย UNPIVOT
บทนำ
ในการวิเคราะห์ข้อมูลทางธุรกิจ ข้อมูลจำนวนมากมักถูกจัดเก็บในรูปแบบ “แนวยาว” (long format) กล่าวคือ แต่ละแถวแทนค่าของตัวแปรเพียงหนึ่งรายการ เช่น ยอดขายของสาขาหนึ่งในเดือนหนึ่ง หรือคะแนนประเมินของหัวข้อหนึ่งในแบบสอบถามหนึ่งชุด รูปแบบนี้เหมาะกับการจัดเก็บและประมวลผลในฐานข้อมูล แต่ในหลายสถานการณ์ ผู้วิเคราะห์และผู้บริหารกลับต้องการมองข้อมูลในรูปแบบ “แนวกว้าง” (wide format) ที่สรุปค่าไว้ในหลายคอลัมน์เพื่อให้อ่าน เปรียบเทียบ และตีความได้ง่ายขึ้น
คำสั่ง PIVOT และ UNPIVOT ถูกออกแบบมาเพื่อแก้ปัญหานี้โดยเฉพาะ PIVOT ใช้สำหรับหมุนข้อมูลจากแถวให้กลายเป็นคอลัมน์ เช่น การแสดงยอดขายแยกตามช่องทางการขายในคอลัมน์เดียวกันของแต่ละสาขา ส่วน UNPIVOT ใช้ในทิศทางตรงกันข้าม คือการแปลงข้อมูลที่กระจายอยู่หลายคอลัมน์ให้กลับมาอยู่ในรูปแบบแถว เพื่อให้เหมาะกับการวิเคราะห์ต่อ การจัดกลุ่ม หรือการสร้างกราฟ
ในทางปฏิบัติ เครื่องมือทั้งสองมีประโยชน์อย่างมากในงานรายงานและการเตรียมข้อมูล ตัวอย่างเช่น ผู้บริหารอาจต้องการดูรายได้ของแต่ละสาขาแยกตาม dine-in, to-go และ delivery ในตารางเดียวกัน ซึ่งเหมาะกับการใช้ PIVOT หรือในอีกกรณีหนึ่ง ผู้วิเคราะห์อาจได้รับข้อมูลแบบรายงานที่เก็บยอดขายรายเดือนแยกเป็นหลายคอลัมน์ เช่น jan_sales, feb_sales, mar_sales และต้องการแปลงกลับเป็นรูปแบบมาตรฐานเพื่อวิเคราะห์แนวโน้มรายเดือน ซึ่งเหมาะกับการใช้ UNPIVOT
ดังนั้น การทำความเข้าใจ PIVOT และ UNPIVOT จึงเป็นทักษะสำคัญในการแปลงรูปแบบข้อมูลให้เหมาะกับวัตถุประสงค์ของการใช้งาน ไม่ว่าจะเป็นการจัดทำรายงานสำหรับผู้บริหาร หรือการเตรียมข้อมูลสำหรับการวิเคราะห์ในขั้นถัดไป
PIVOT: หมุนตาราง Revenue เพื่อสร้าง Crosstab Report
คำสั่ง PIVOT ใช้สำหรับแปลงข้อมูลจากรูปแบบแถวให้กลายเป็นรูปแบบคอลัมน์ เพื่อสร้างตารางสรุปแบบ crosstab ที่ช่วยให้มองเห็นรูปแบบของข้อมูลได้ชัดเจนขึ้น โดยเฉพาะในงานรายงานที่ต้องการเปรียบเทียบค่าระหว่างหลายหมวดหมู่ภายในตารางเดียว
ไวยากรณ์
ในโครงสร้างข้างต้น ON ใช้ระบุคอลัมน์ที่ค่าภายในคอลัมน์นั้นจะถูกนำไปใช้เป็นชื่อคอลัมน์ใหม่ ส่วน USING ใช้ระบุฟังก์ชันสรุปที่ต้องการนำมาใช้ เช่น SUM, COUNT หรือ AVG เพื่อคำนวณค่าที่จะแสดงในแต่ละช่องของตาราง
DuckDB รองรับไวยากรณ์ PIVOT โดยตรง ทำให้สามารถสร้างตารางสรุปในลักษณะนี้ได้อย่างสะดวก อย่างไรก็ตาม ในระบบฐานข้อมูลบางประเภท อาจไม่มีคำสั่ง PIVOT โดยตรง และต้องใช้วิธีเขียน conditional aggregation ด้วย CASE ภายใน SUM หรือ aggregate function อื่นแทน
เราจะใช้ PIVOT เพื่อสร้างมุมมองรายได้ในหลายมิติ เช่น รายได้ตาม สาขา × ช่องทางการขาย, ชั่วโมง × วันในสัปดาห์, และ เดือน × ช่องทาง เพื่อช่วยให้ผู้บริหารอ่านรูปแบบของธุรกิจได้รวดเร็วขึ้น และนำผลลัพธ์ไปใช้ประกอบการตัดสินใจได้อย่างมีประสิทธิภาพ
รายได้แยกช่องทางแต่ละสาขา (Crosstab)
แทนที่จะเห็น 18 แถว (6 สาขา x 3 ช่องทาง) ผู้บริหารต้องการ 6 แถวที่มี 3 คอลัมน์แยกตามช่องทาง เหมือน Pivot Table ใน Excel ที่อ่านแล้วเห็นภาพทันที
-- แสดงรายได้แยกตามช่องทางการขายของแต่ละสาขาในรูปแบบตาราง pivot
PIVOT (
SELECT
s.store_name, -- ชื่อสาขา (จะกลายเป็น row)
t.channel, -- ช่องทางการขาย (จะกลายเป็น column)
ROUND(SUM(t.net_sales_thb), 0) AS revenue -- รายได้รวม (บาท)
FROM transactions t
JOIN stores s ON t.store_id = s.store_id -- เชื่อมตารางสาขาเพื่อดึงชื่อสาขา
GROUP BY s.store_name, t.channel -- จัดกลุ่มตามสาขาและช่องทาง
)
ON channel USING SUM(revenue) -- pivot ค่า channel เป็น column โดยรวมรายได้
ORDER BY store_name; -- เรียงตามชื่อสาขา| store_name | delivery | dine_in | to_go |
|---|---|---|---|
| PiCha Exchange Tower | 573,086 | 67,212 | 703,563 |
| PiCha ICONSIAM | 917,245 | 455,380 | 391,301 |
| PiCha Park Silom | 618,342 | 73,446 | 759,089 |
| PiCha Siam Square | 1,399,430 | 168,056 | 744,373 |
| PiCha Vanit Village | 741,464 | 167,627 | 556,672 |
| PiCha centralwOrld | 1,309,803 | 570,371 | 570,940 |
จากตารางจะเห็นว่า Siam Square มีรายได้ delivery สูงสุด ขณะที่ Park Silom และ Exchange Tower พึ่งพา to_go เป็นหลัก ส่วน ICONSIAM มี dine_in สูงสุด สะท้อนทำเลและพฤติกรรมลูกค้าแต่ละสาขา สาขา office มี to_go เป็นช่องทางหลักตามพฤติกรรมพนักงานที่แวะซื้อก่อนเข้าหรือหลังเลิกงาน ส่วนสาขาห้างฯ มี delivery สูงเพราะ rider ครอบคลุมพื้นที่โดยรอบได้ดี ในเชิงบริหาร กลยุทธ์ช่องทางจึงควรออกแบบให้ต่างกัน เช่น สาขา office มี room to grow ด้าน delivery ขณะที่สาขาห้างอาจเน้นปรับปรุง seating area เพื่อดึง dine_in โดยต้องพิจารณา margin แต่ละช่องทางควบคู่ไปด้วย
Revenue crosstab ชั่วโมง x วันในสัปดาห์
รายงาน crosstab ช่วยให้ผู้บริหารเห็นว่าช่วงเวลาไหนของสัปดาห์ทำรายได้สูงสุดและต่ำสุด ข้อมูลนี้ใช้วางแผนจำนวนพนักงาน (staffing) และตั้งเวลา promotion ได้แม่นยำขึ้น
-- แสดงรายได้แยกตามชั่วโมงและวันในสัปดาห์ในรูปแบบตาราง pivot (crosstab)
PIVOT (
SELECT
hour, -- ชั่วโมงของวัน (0–23)
day_of_week, -- วันในสัปดาห์ (จะกลายเป็น column)
ROUND(SUM(net_sales_thb), 0) AS revenue -- รายได้รวม (บาท)
FROM transactions
GROUP BY hour, day_of_week -- จัดกลุ่มตามชั่วโมงและวัน
)
ON day_of_week USING SUM(revenue) -- pivot ค่า day_of_week เป็น column โดยรวมรายได้
ORDER BY hour; -- เรียงตามชั่วโมงจาก 0 ถึง 23| hour | Friday | Monday | Saturday | Sunday | Thursday | Tuesday | Wednesday |
|---|---|---|---|---|---|---|---|
| 9 | 64,047 | 73,666 | 45,866 | 58,497 | 72,039 | 88,096 | 95,765 |
| 10 | 81,110 | 82,521 | 84,745 | 74,771 | 82,623 | 92,567 | 97,045 |
| 11 | 154,006 | 138,893 | 140,553 | 154,866 | 149,584 | 155,918 | 167,329 |
| 12 | 185,207 | 169,261 | 195,566 | 220,321 | 188,929 | 196,364 | 208,380 |
| 13 | 157,870 | 130,863 | 151,799 | 188,149 | 156,144 | 151,435 | 183,967 |
| 14 | 109,218 | 103,433 | 105,482 | 123,426 | 107,925 | 114,510 | 103,542 |
| 15 | 99,223 | 83,466 | 106,512 | 106,596 | 89,957 | 104,026 | 98,448 |
| 16 | 85,261 | 68,783 | 93,765 | 98,287 | 75,572 | 88,983 | 86,852 |
| 17 | 128,515 | 112,865 | 144,578 | 151,518 | 125,231 | 113,490 | 134,237 |
| 18 | 148,919 | 108,855 | 164,938 | 189,791 | 137,706 | 138,522 | 148,779 |
| 19 | 142,765 | 87,710 | 146,751 | 169,384 | 120,560 | 106,272 | 125,203 |
| 20 | 94,629 | 67,990 | 106,052 | 117,312 | 85,908 | 76,050 | 90,362 |
| 21 | 64,548 | 40,554 | 79,429 | 78,622 | 54,956 | 53,715 | 56,631 |
| 22 | 26,374 | 16,820 | 33,608 | 40,100 | 18,330 | 22,178 | 24,623 |
ชั่วโมง 12:00 น. วันอาทิตย์มีรายได้สูงสุดที่ 220,321 บาท รองลงมาคือ 12:00 น. วันพุธ 208,380 บาท ซึ่งสูงสุดในวันธรรมดา ขณะที่วันจันทร์ต่ำสุดในแทบทุกช่วงเวลา crosstab แสดง peak สองช่วงชัดเจนในทุกวัน ได้แก่ 11:00–13:00 น. (lunch peak) และ 17:00–19:00 น. (evening peak) โดยวันเสาร์–อาทิตย์ช่วงเย็นแข็งแกร่งเป็นพิเศษ สอดคล้องกับพฤติกรรม weekend outing ในเชิงบริหาร ข้อมูลนี้ใช้วางแผน staffing โดยเพิ่มพนักงานช่วง peak และลดช่วง 21:00–22:00 น. โดยเฉพาะวันจันทร์ รวมถึงใช้กำหนด promotion ในช่วงยอดขายต่ำ เช่น จันทร์ 14:00–16:00 น. แทนการอัดโปรโมชั่นช่วงที่ capacity เต็มอยู่แล้ว
รายได้รายเดือนแยกช่องทาง
ตัวอย่างสุดท้ายในส่วน PIVOT เน้นการอ่าน “trend ข้ามเดือน” แยกตามช่องทาง โดยยังอยู่ในรูป wide format เพื่อให้เห็นการเปลี่ยนแปลงแต่ละ channel ชัดเจน
-- แสดงรายได้แยกตามช่องทางการขายของแต่ละเดือนในรูปแบบตาราง pivot
PIVOT (
SELECT
month, -- เดือน (จะกลายเป็น row)
channel, -- ช่องทางการขาย (จะกลายเป็น column)
ROUND(SUM(net_sales_thb), 0) AS revenue -- รายได้รวม (บาท)
FROM transactions
GROUP BY month, channel -- จัดกลุ่มตามเดือนและช่องทาง
)
ON channel USING SUM(revenue) -- pivot ค่า channel เป็น column โดยรวมรายได้
ORDER BY month; -- เรียงตามเดือน| month | delivery | dine_in | to_go |
|---|---|---|---|
| 2 | 2,552,982 | 686,406 | 1,666,565 |
| 3 | 2,901,389 | 790,818 | 1,968,710 |
| 4 | 104,999 | 24,868 | 90,664 |
ทุกช่องทางมีรายได้เพิ่มขึ้นจากกุมภาพันธ์ไปมีนาคม โดย delivery เพิ่ม 348,407 บาท (+13.6%), dine_in เพิ่ม 104,412 บาท (+15.2%) และ to_go เพิ่ม 302,145 บาท (+18.1%) ทำให้ to_go เป็นช่องทางที่โตเร็วที่สุดในเชิงเปอร์เซ็นต์ สิ่งนี้น่าจะสะท้อนพฤติกรรมพนักงานออฟฟิศที่กลับมาทำงานหลังช่วงเทศกาล สอดคล้องกับข้อค้นพบก่อนหน้าว่าสาขา office มี to_go เป็นช่องทางหลัก ข้อมูลเดือนเมษายนยังไม่ครบเดือนจึงไม่ควรใช้เปรียบเทียบโดยตรง แต่หาก PiCha ต้องการเร่งการเติบโต การลงทุนเพิ่มในช่องทาง to_go ที่สาขา office ย่านธุรกิจคือจุดที่มีศักยภาพชัดเจนที่สุดในข้อมูลชุดนี้
UNPIVOT: แปลงคอลัมน์ Flags เป็นแถว
ส่วน PIVOT เน้นการ “หมุน” ตารางเพื่ออ่านสรุป ส่วนนี้จะกลับด้าน มองจากมุม product manager ที่ต้องการ “แตก” wide table ให้กลายเป็น long format เพื่อวิเคราะห์ attribute ของสินค้าได้ยืดหยุ่นขึ้น
นับเมนูต่อ category เพื่อวางแผน Bundle Promotion
ทีม product manager ต้องการรู้ว่า PiCha มีเมนูกี่รายการในแต่ละ category (milk, fruit, cold brew, blended) เพื่อวางแผน bundle promotion ระหว่าง category ข้อมูลเมนูเก็บ attribute เหล่านี้เป็น flag columns แยก (milk_flag, fruit_flag, cold_brew_flag, blended_flag) ใน wide format ซึ่งไม่สะดวกต่อการ GROUP BY โดยตรง
UNPIVOT แปลง wide format เป็น long format ทำให้ GROUP BY flag_name ตามด้วย SUM นับจำนวนเมนูต่อ category ได้ในขั้นตอนเดียว
-- แปลง flag columns ให้เป็น rows เพื่อดูคุณสมบัติของแต่ละ SKU
FROM menus
UNPIVOT INCLUDE NULLS ( -- แปลง column เป็น row รวมถึงค่า NULL
flag_value -- ค่าของ flag (จะกลายเป็น column)
FOR flag_name IN (milk_flag, fruit_flag, cold_brew_flag, blended_flag) -- column ที่ต้องการ unpivot
)
WHERE sku_id <= 5 OR sku_id = 20 -- กรองเฉพาะ SKU 1–5 และ SKU 20
ORDER BY sku_id, flag_name; -- เรียงตาม SKU แล้วตามชื่อ flag| sku_id | sku_name | family | tea_base | size_code | size_ml | list_price_thb | flag_name | flag_value |
|---|---|---|---|---|---|---|---|---|
| 1 | Glutinous Green Milk Tea R | milk_tea | glutinous_green | R | 500 | 85 | blended_flag | 0 |
| 1 | Glutinous Green Milk Tea R | milk_tea | glutinous_green | R | 500 | 85 | cold_brew_flag | 0 |
| 1 | Glutinous Green Milk Tea R | milk_tea | glutinous_green | R | 500 | 85 | fruit_flag | 0 |
| 1 | Glutinous Green Milk Tea R | milk_tea | glutinous_green | R | 500 | 85 | milk_flag | 1 |
| 2 | Glutinous Green Milk Tea L | milk_tea | glutinous_green | L | 700 | 100 | blended_flag | 0 |
| 2 | Glutinous Green Milk Tea L | milk_tea | glutinous_green | L | 700 | 100 | cold_brew_flag | 0 |
| 2 | Glutinous Green Milk Tea L | milk_tea | glutinous_green | L | 700 | 100 | fruit_flag | 0 |
| 2 | Glutinous Green Milk Tea L | milk_tea | glutinous_green | L | 700 | 100 | milk_flag | 1 |
| 3 | Tie Guan Yin Milk Tea R | milk_tea | tie_guan_yin | R | 500 | 110 | blended_flag | 0 |
| 3 | Tie Guan Yin Milk Tea R | milk_tea | tie_guan_yin | R | 500 | 110 | cold_brew_flag | 0 |
| 3 | Tie Guan Yin Milk Tea R | milk_tea | tie_guan_yin | R | 500 | 110 | fruit_flag | 0 |
| 3 | Tie Guan Yin Milk Tea R | milk_tea | tie_guan_yin | R | 500 | 110 | milk_flag | 1 |
| 4 | Tie Guan Yin Milk Tea L | milk_tea | tie_guan_yin | L | 700 | 130 | blended_flag | 0 |
| 4 | Tie Guan Yin Milk Tea L | milk_tea | tie_guan_yin | L | 700 | 130 | cold_brew_flag | 0 |
| 4 | Tie Guan Yin Milk Tea L | milk_tea | tie_guan_yin | L | 700 | 130 | fruit_flag | 0 |
| 4 | Tie Guan Yin Milk Tea L | milk_tea | tie_guan_yin | L | 700 | 130 | milk_flag | 1 |
| 20 | White Peach Cold Brew R | fruit_tea | white_peach | R | 500 | 120 | blended_flag | 0 |
| 20 | White Peach Cold Brew R | fruit_tea | white_peach | R | 500 | 120 | cold_brew_flag | 1 |
| 20 | White Peach Cold Brew R | fruit_tea | white_peach | R | 500 | 120 | fruit_flag | 1 |
| 20 | White Peach Cold Brew R | fruit_tea | white_peach | R | 500 | 120 | milk_flag | 0 |
จากตัวอย่างจะเห็นว่า SKU 1–4 (milk_tea) มี milk_flag = 1 และ flag อื่นเป็น 0 ส่วน SKU 20 (White Peach Cold Brew R) มี fruit_flag และ cold_brew_flag เป็น 1 พร้อมกัน ทำให้หลัง UNPIVOT เมนูนี้ปรากฏเป็น 2 แถวที่มี flag_value = 1 นี่คือพฤติกรรมสำคัญของ UNPIVOT: เมนูที่มีหลาย flag จะกระจายออกเป็นหลายแถว ซึ่งช่วยให้ GROUP BY flag_name นับจำนวนเมนูต่อ attribute ได้ทันที
ต่อจาก UNPIVOT ขั้นแรก ทีม product manager ต้องการตอบคำถามสรุป นับว่ามีเมนูกี่รายการในแต่ละ flag category
-- นับจำนวนเมนูที่มีแต่ละ flag เป็น 1 เพื่อดูว่าแต่ละประเภทมีกี่เมนู
SELECT
flag_name, -- ชื่อ flag (ประเภทคุณสมบัติของเมนู)
SUM(flag_value) AS menu_count -- จำนวนเมนูที่มี flag นั้นเป็น 1
FROM (
FROM menus
UNPIVOT ( -- แปลง flag columns ให้เป็น rows
flag_value FOR flag_name IN (milk_flag, fruit_flag, cold_brew_flag, blended_flag)
)
)
WHERE flag_value = 1 -- กรองเฉพาะเมนูที่มี flag นั้น
GROUP BY flag_name -- จัดกลุ่มตามประเภท flag
ORDER BY menu_count DESC; -- เรียงจาก flag ที่มีเมนูมากสุดไปน้อยสุดSKU 1–4 (milk_tea) มี milk_flag = 1 และ flag อื่นเป็น 0 ส่วน SKU 20 (White Peach Cold Brew R) มี fruit_flag และ cold_brew_flag เป็น 1 พร้อมกัน ทำให้หลัง UNPIVOT เมนูนี้ปรากฏเป็น 2 แถวที่มี flag_value = 1 นี่คือพฤติกรรมสำคัญของ UNPIVOT: เมนูที่มีหลาย flag จะกระจายออกเป็นหลายแถว ซึ่งช่วยให้ GROUP BY flag_name นับจำนวนเมนูต่อ attribute ได้ทันที ในเชิงบริหาร ทีม product manager ที่ต้องการรู้ว่ามีเมนู cold brew กี่รายการ หรือเมนูใดมีทั้ง milk และ fruit เพื่อออกแบบ bundle promotion ไม่สามารถตอบคำถามนี้จาก wide format โดยตรง ต้อง UNPIVOT ก่อน แล้วจึงกรองหรือ GROUP BY ตาม flag ที่ต้องการ ข้อควรระวัง: หากตาราง menus มีเมนูหลายพันรายการ UNPIVOT จะขยาย row count เป็น 4 เท่า ควรกรอง WHERE flag_value = 1 ก่อนทำ GROUP BY เพื่อประสิทธิภาพ
ใน long format สามารถ
WHERE flag_value = 1แล้วGROUP BY flag_nameเพื่อดูจำนวนเมนูต่อ attribute หรือกรองflag_name IN ('milk_flag','fruit_flag')เพื่อหาเมนูที่ข้ามสอง category ซึ่งทำไม่ได้ใน wide format โดยตรง
Behaviors & Pitfalls ของ UNPIVOT
- หากตาราง menus มีเมนูหลายพันรายการ UNPIVOT จะขยาย row count เท่ากับจำนวน flag columns (เช่น 4 เท่าในตัวอย่างนี้)
- เพื่อประสิทธิภาพ ควรกรอง
WHERE flag_value = 1ก่อนทำ GROUP BY เพื่อลดจำนวนแถวที่ต้อง aggregate - การออกแบบ schema ให้เหมาะกับการใช้ UNPIVOT ช่วยให้การตอบคำถามเชิง attribute ทำได้ใน query เดียว แทนการเขียนเงื่อนไขหลายชั้นบน wide table
PIVOT vs UNPIVOT ใช้เมื่อไหร่
ตารางนี้สรุปว่าในสถานการณ์แบบไหนควรใช้เครื่องมือใดเป็นหลัก
| สถานการณ์ | ใช้ | เหตุผล |
|---|---|---|
| ต้องการรายงานสรุปแบบ crosstab สำหรับผู้บริหาร | PIVOT | หมุนแถวเป็นคอลัมน์ อ่านง่าย |
| ต้องการทำความสะอาดข้อมูลที่มี flag columns หลายคอลัมน์ | UNPIVOT | หมุนคอลัมน์เป็นแถว วิเคราะห์ pattern ได้ |
| ต้องการ conditional aggregation แบบยืดหยุ่น | CASE ใน SUM | ใช้ได้ทุก RDBMS ไม่ต้องพึ่ง PIVOT syntax |
สรุป
PIVOT และ UNPIVOT เป็นเครื่องมือสำหรับแปลง “รูปแบบ” ของข้อมูลให้เหมาะกับวัตถุประสงค์ของการใช้งาน PIVOT ใช้เปลี่ยนข้อมูลจากรูปแบบ long ให้เป็น wide โดยหมุนค่าจากแถวให้กลายเป็นคอลัมน์ เพื่อสร้างตารางสรุปแบบเปรียบเทียบข้ามหมวดหมู่ได้อย่างชัดเจน ส่วน UNPIVOT ใช้แปลงข้อมูลจาก wide กลับเป็น long เพื่อให้เหมาะกับการวิเคราะห์ต่อ การจัดกลุ่ม การสร้างกราฟ และการนำไปใช้ร่วมกับเครื่องมือวิเคราะห์ข้อมูล เช่น Power BI หรือ Tableau
| แนวคิด | Syntax | การอุปมา | การใช้ธุรกิจ |
|---|---|---|---|
PIVOT |
PIVOT (...) ON col USING SUM(val) |
หมุนตารางจากแถวให้กลายเป็นคอลัมน์ | ใช้สร้างรายงานแบบ crosstab เพื่อเปรียบเทียบข้อมูลข้ามหมวดหมู่ |
UNPIVOT |
UNPIVOT (val FOR name IN (...)) |
หมุนตารางกลับจากคอลัมน์ให้เป็นแถว | ใช้แปลงข้อมูลจาก wide format ให้กลับมาอยู่ในรูปแบบ long format เพื่อการวิเคราะห์ต่อ |
คำศัพท์สำคัญ
| ศัพท์ | ความหมาย |
|---|---|
PIVOT |
การแปลงค่าที่อยู่ในแถวให้กลายเป็นคอลัมน์ |
UNPIVOT |
การแปลงค่าที่อยู่ในคอลัมน์ให้กลับมาอยู่ในรูปแบบแถว |
คำถามท้ายบท
- ใช้
PIVOTเพื่อแสดงยอดขายสุทธิรวม (net_sales_thb) ของแต่ละสาขา โดยให้แต่ละช่องทาง (dine_in,to_go,delivery) เป็นคอลัมน์แยกกัน เรียงตามdine-inสูงไปต่ำ
ผลลัพธ์ที่คาดหวัง
| store_name | delivery | dine_in | to_go |
|---|---|---|---|
| PiCha CentralWorld | 1,309,819 | 570,487 | 571,026 |
| PiCha ICONSIAM | 917,248 | 455,393 | 391,326 |
| PiCha Siam Square | 1,399,425 | 168,090 | 744,458 |
| PiCha Vanit Village | 741,485 | 167,625 | 556,660 |
| PiCha Park Silom | 618,343 | 73,449 | 759,105 |
| PiCha Exchange Tower | 573,093 | 67,214 | 703,577 |
คลิกเพื่อดูเฉลย
-- แสดงรายได้แยกตามช่องทางการขายของแต่ละสาขา เรียงตาม dine_in สูงสุด
PIVOT (
SELECT
s.store_name, -- ชื่อสาขา (จะกลายเป็น row)
t.channel, -- ช่องทางการขาย (จะกลายเป็น column)
ROUND(t.net_sales_thb, 0) AS revenue -- รายได้สุทธิ (บาท)
FROM transactions t
JOIN stores s ON t.store_id = s.store_id -- เชื่อมตารางสาขาเพื่อดึงชื่อสาขา
)
ON channel -- pivot ค่า channel เป็น column
USING SUM(revenue) -- รวมรายได้ในแต่ละ channel
GROUP BY store_name -- จัดกลุ่มตามสาขา
ORDER BY dine_in DESC; -- เรียงตามรายได้ dine_in จากมากไปน้อย- ใช้
PIVOTเพื่อนับจำนวน orders (transaction_id) ของแต่ละสาขา โดยให้แต่ละวันในสัปดาห์เป็นคอลัมน์แยกกัน เรียงตามชื่อสาขา
ผลลัพธ์ที่คาดหวัง
| store_name | Friday | Monday | Saturday | Sunday | Thursday | Tuesday | Wednesday |
|---|---|---|---|---|---|---|---|
| PiCha Exchange Tower | 827 | 1,323 | 166 | 147 | 1,357 | 1,609 | 1,651 |
| PiCha ICONSIAM | 1,311 | 702 | 1,786 | 2,104 | 977 | 858 | 964 |
| PiCha Park Silom | 940 | 1,414 | 197 | 149 | 1,439 | 1,717 | 1,755 |
| PiCha Siam Square | 1,671 | 1,118 | 1,999 | 2,139 | 1,232 | 1,195 | 1,384 |
| PiCha Vanit Village | 1,037 | 833 | 1,253 | 1,368 | 889 | 947 | 930 |
| PiCha CentralWorld | 1,832 | 1,055 | 2,325 | 2,600 | 1,464 | 1,244 | 1,415 |
คลิกเพื่อดูเฉลย
-- นับจำนวนธุรกรรมแยกตามวันในสัปดาห์ของแต่ละสาขาในรูปแบบตาราง pivot
PIVOT (
SELECT
s.store_name, -- ชื่อสาขา (จะกลายเป็น row)
t.day_of_week, -- วันในสัปดาห์ (จะกลายเป็น column)
t.transaction_id -- รหัสธุรกรรม (ใช้นับจำนวน)
FROM transactions t
JOIN stores s ON t.store_id = s.store_id -- เชื่อมตารางสาขาเพื่อดึงชื่อสาขา
)
ON day_of_week -- pivot ค่า day_of_week เป็น column
USING COUNT(transaction_id) -- นับจำนวนธุรกรรมในแต่ละวัน
GROUP BY store_name -- จัดกลุ่มตามสาขา
ORDER BY store_name; -- เรียงตามชื่อสาขา- จากผลลัพธ์ PIVOT ในข้อที่ 1 จงใช้
UNPIVOTเพื่อแปลงตารางกลับให้อยู่ในรูป long format โดยมีคอลัมน์store_name,channel, และrevenueเรียงตามชื่อสาขาและช่องทาง
ผลลัพธ์ที่คาดหวัง
| store_name | channel | revenue |
|---|---|---|
| PiCha Exchange Tower | delivery | 573,093 |
| PiCha Exchange Tower | dine_in | 67,214 |
| PiCha Exchange Tower | to_go | 703,577 |
| PiCha ICONSIAM | delivery | 917,248 |
| PiCha ICONSIAM | dine_in | 455,393 |
| PiCha ICONSIAM | to_go | 391,326 |
| PiCha Park Silom | delivery | 618,343 |
| PiCha Park Silom | dine_in | 73,449 |
| PiCha Park Silom | to_go | 759,105 |
| PiCha Siam Square | delivery | 1,399,425 |
| PiCha Siam Square | dine_in | 168,090 |
| PiCha Siam Square | to_go | 744,458 |
| PiCha Vanit Village | delivery | 741,485 |
| PiCha Vanit Village | dine_in | 167,625 |
| PiCha Vanit Village | to_go | 556,660 |
| PiCha CentralWorld | delivery | 1,309,819 |
| PiCha CentralWorld | dine_in | 570,487 |
| PiCha CentralWorld | to_go | 571,026 |
คลิกเพื่อดูเฉลย
-- แปลงตาราง pivot กลับเป็น long format เพื่อให้ query และ filter ได้ง่ายขึ้น
UNPIVOT ( -- แปลง wide format กลับเป็น long format
PIVOT ( -- สร้างตาราง pivot รายได้แยกตามช่องทางและสาขาก่อน
SELECT
s.store_name, -- ชื่อสาขา
t.channel, -- ช่องทางการขาย
ROUND(t.net_sales_thb, 0) AS revenue -- รายได้สุทธิ (บาท)
FROM transactions t
JOIN stores s ON t.store_id = s.store_id -- เชื่อมตารางสาขาเพื่อดึงชื่อสาขา
)
ON channel -- pivot ค่า channel เป็น column
USING SUM(revenue) -- รวมรายได้ในแต่ละ channel
GROUP BY store_name -- จัดกลุ่มตามสาขา
)
ON delivery, dine_in, to_go -- column ที่ต้องการ unpivot กลับเป็น rows
INTO
NAME channel -- ชื่อ column ที่เก็บชื่อช่องทาง
VALUE revenue -- ชื่อ column ที่เก็บรายได้
ORDER BY store_name, channel; -- เรียงตามสาขา แล้วตามช่องทาง