0Pricing
SQL Interview Prep · บทเรียน

ไวยากรณ์ PIVOT และ Crosstab เฉพาะระบบ

PIVOT ของ SQL Server และ crosstab ของ Postgres พร้อมข้อจำกัดของแต่ละแบบ

ไวยากรณ์ PIVOT และ Crosstab เฉพาะระบบ เป็นบทเรียน SQL Interview Prep ฟรีบน CoddyKit นี่คือบทเรียนที่ 2 จากทั้งหมด 4 บทเรียน คุณสามารถอ่านบทเรียนทั้งหมดด้านล่างฟรี — จากนั้นลองปฏิบัติด้วยตัวคุณเองในเบราว์เซอร์พร้อมตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7 บทเรียนนี้เป็นส่วนหนึ่งของเส้นทางการเรียน SQL Interview Prep และความก้าวหน้าของคุณจะซิงค์ข้ามเว็บและแอป CoddyKit คอร์ส SQL Interview Prep มีบทเรียนทั้งหมด 4 บทเรียน

นอกเหนือจากการรวมค่าแบบมีเงื่อนไข

คุณรู้จักการหมุนตารางแบบพกพาด้วย CASE แล้ว แต่ผู้สัมภาษณ์ยังต้องการทราบว่าคุณใช้ ตัวดำเนินการหมุนตารางเฉพาะผู้ผลิต ได้หรือไม่เมื่อระบบรองรับ

เซิร์ฟเวอร์ SQL มีตัวดำเนินการ PIVOT โดยเฉพาะ ส่วน PostgreSQL มีฟังก์ชัน crosstab ในส่วนขยาย tablefunc การรู้จักทั้งสองแบบ รวมถึงจุดที่ต้องระวัง แสดงให้เห็นถึงประสบการณ์จากการใช้งานจริง

โครงสร้างของ PIVOT ในเซิร์ฟเวอร์ SQL

PIVOT ของเซิร์ฟเวอร์ SQL รับข้อมูลสามอย่าง:

  • ฟังก์ชันรวมค่าที่ทำงานกับคอลัมน์ค่า
  • ส่วนคำสั่ง FOR ที่ระบุชื่อคอลัมน์ซึ่งค่าของมันจะกลายเป็นคอลัมน์ใหม่
  • รายการ IN ของค่าคงที่ที่จะเปลี่ยนเป็นคอลัมน์

ต้องนำไปใช้กับตารางย่อยที่สร้างขึ้นซึ่งแสดงเฉพาะคีย์ คอลัมน์ที่จะกระจาย และค่าเท่านั้น ห้ามมีอย่างอื่น

SELECT region, [Q1], [Q2]
FROM (SELECT region, quarter, amount FROM sales) AS src
PIVOT (
  SUM(amount)
  FOR quarter IN ([Q1], [Q2])
) AS p;

การจัดกลุ่มโดยนัย

จุดที่มักพลาดของ PIVOT ซึ่งผู้สัมภาษณ์ใช้ทดสอบคือ การจัดกลุ่มเป็นแบบ โดยนัย เซิร์ฟเวอร์ SQL จะจัดกลุ่มตามทุกคอลัมน์ในแหล่งข้อมูลที่ไม่ใช่คอลัมน์ที่นำมารวมค่าและไม่ใช่คอลัมน์ของ FOR

ดังนั้น หากตารางย่อยที่สร้างขึ้นของคุณมีคอลัมน์ส่วนเกิน เช่น order_id โดยไม่ตั้งใจ การหมุนตารางก็จะจัดกลุ่มตามคอลัมน์นั้นด้วย และคุณจะได้จำนวนแถวมากกว่าที่คาดไว้มาก ควรตัดคำสั่งค้นหาด้านในให้เหลือเพียงคีย์ คอลัมน์ที่จะกระจาย และค่าเสมอ

-- WRONG: order_id leaks in and breaks grouping
FROM (SELECT region, quarter, amount, order_id FROM sales) AS src
PIVOT (SUM(amount) FOR quarter IN ([Q1],[Q2])) AS p;
-- The pivot now groups by region AND order_id

ชื่อคอลัมน์ในวงเล็บเหลี่ยม

ในเซิร์ฟเวอร์ SQL ชื่อคอลัมน์ที่ได้จากการหมุนตารางคือค่าคงที่จากข้อมูลที่ครอบด้วยวงเล็บเหลี่ยม หากค่าเริ่มต้นด้วยตัวเลขหรือมีช่องว่าง จำเป็นต้องใช้วงเล็บเหลี่ยม

คุณเลือกคอลัมน์เหล่านี้ด้วยชื่อที่อยู่ในวงเล็บเหลี่ยมแบบเดียวกันใน SELECT ด้านนอก นี่เป็นอีกเหตุผลที่ PIVOT ไม่สามารถจัดการค่าที่ไม่ทราบล่วงหน้าได้หากไม่มี SQL แบบไดนามิก เพราะรายการ IN ถูกกำหนดตายตัว

SELECT region, [2023], [2024]
FROM (SELECT region, yr, amount FROM sales) AS s
PIVOT (SUM(amount) FOR yr IN ([2023], [2024])) AS p;

การจัดตารางไขว้ของ PostgreSQL

PostgreSQL ไม่มีคีย์เวิร์ด PIVOT แต่ส่วนขยาย tablefunc มีฟังก์ชัน crosstab ซึ่งรับสตริง SQL แล้วปรับรูปแบบผลลัพธ์

คุณต้องเปิดใช้ส่วนขยายก่อน ฟังก์ชัน crosstab ต้องการให้คำสั่งค้นหาต้นทางคืนค่ามาสามคอลัมน์พอดี ได้แก่ ตัวระบุแถว หมวดหมู่ และค่า ตามลำดับนี้

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT *
FROM crosstab(
  'SELECT region, quarter, amount FROM sales ORDER BY 1, 2'
) AS ct(region text, q1 numeric, q2 numeric);

รายการกำหนดคอลัมน์

ส่วนที่ทำให้เกิดข้อผิดพลาดได้ง่ายที่สุดของ crosstab คือรายการกำหนดคอลัมน์ AS ct(...) ที่ต่อท้าย คุณต้องประกาศชื่อและชนิดของคอลัมน์ผลลัพธ์ด้วยตนเอง และต้องตรงกับจำนวนและลำดับของหมวดหมู่

หากแถวใดไม่มีหมวดหมู่หนึ่ง ฟังก์ชัน crosstab จะเติมค่าตามตำแหน่ง ซึ่งอาจทำให้ข้อมูลเหลื่อมตำแหน่ง เว้นแต่คุณจะใช้รูปแบบที่รับสองอาร์กิวเมนต์ด้านล่าง

SELECT *
FROM crosstab(
  'SELECT region, quarter, amount FROM sales ORDER BY 1, 2'
) AS ct(region text, q1 numeric, q2 numeric);
-- ct(...) MUST list every output column and its type

crosstab แบบสองอาร์กิวเมนต์

เพื่อหลีกเลี่ยงข้อมูลเหลื่อมตำแหน่งเมื่อบางแถวไม่มีบางหมวดหมู่ ให้ใช้รูปแบบที่รับสองอาร์กิวเมนต์ คำสั่งค้นหาที่สองจะคืนค่ารายการค่าหมวดหมู่ทั้งหมดตามลำดับ ทำให้ฟังก์ชัน crosstab ทราบแน่ชัดว่าค่าแต่ละค่าต้องอยู่ในคอลัมน์ใด

นี่คือรูปแบบที่มีความน่าเชื่อถือ ซึ่งผู้สัมภาษณ์คาดหวังเมื่อหมวดหมู่มีข้อมูลเบาบาง

SELECT *
FROM crosstab(
  'SELECT region, quarter, amount FROM sales ORDER BY 1, 2',
  'SELECT DISTINCT quarter FROM sales ORDER BY 1'
) AS ct(region text, q1 numeric, q2 numeric);

MySQL ไม่มีทั้งสองแบบ

หากผู้สัมภาษณ์ถามเกี่ยวกับ MySQL คำตอบตรงไปตรงมาคือ MySQL ไม่มี PIVOT และไม่มี crosstab ทางเลือกเดียวคือการรวมค่าแบบมีเงื่อนไขด้วย CASE (หรือรูปแบบย่อ SUM(... ) + IF())

นี่คือเหตุผลที่รูปแบบ CASE ซึ่งใช้ข้ามระบบได้มีคุณค่ามาก เพราะเป็นตัวเลือกพื้นฐานที่สุดที่ทำงานได้ทุกที่

-- MySQL: only conditional aggregation works
SELECT
  region,
  SUM(IF(quarter = 'Q1', amount, 0)) AS q1,
  SUM(IF(quarter = 'Q2', amount, 0)) AS q2
FROM sales
GROUP BY region;

ตัวอย่างแบบลงมือทำ: การนับสถานะในเซิร์ฟเวอร์ SQL

ตัวอย่างข้อกำหนดด้านรายงาน: “หนึ่งแถวต่อหนึ่งภูมิภาค พร้อมคอลัมน์ที่นับคำสั่งซื้อในแต่ละสถานะ” ในเซิร์ฟเวอร์ SQL ให้ป้อนตารางย่อยที่ตัดคอลัมน์ส่วนเกินออกแล้วเข้า PIVOT โดยใช้ COUNT

เนื่องจากคุณนับคอลัมน์สถานะโดยตรง แถวสถานะที่ไม่ใช่ NULL ทุกแถวในกลุ่มจึงถูกนับ คำสั่ง SELECT ด้านนอกจะแสดงแต่ละสถานะเป็นคอลัมน์ในวงเล็บเหลี่ยม วิธีนี้กระชับกว่าการเขียนนิพจน์ COUNT(CASE ...) สามชุด

SELECT region, [pending], [shipped], [delivered]
FROM (SELECT region, status FROM orders) AS src
PIVOT (
  COUNT(status)
  FOR status IN ([pending], [shipped], [delivered])
) AS p;

ข้อจำกัดร่วมกัน

ทั้ง PIVOT และ crosstab มีข้อจำกัดหลักเดียวกับการรวมค่าแบบมีเงื่อนไข: ต้องทราบคอลัมน์ผลลัพธ์ขณะที่เขียนคำสั่งค้นหา

  • เซิร์ฟเวอร์ SQL: รายการ IN เป็นค่าคงที่
  • การจัดตารางไขว้ของ PostgreSQL: รายการกำหนดคอลัมน์เป็นค่าคงที่

ไม่มีแบบใดค้นหาหมวดหมู่ขณะทำงานได้ การทำเช่นนั้นต้องสร้างสตริง SQL แบบไดนามิก

ควรเลือกใช้แบบใด

คำตอบที่ดีในการสัมภาษณ์ควรเปรียบเทียบแต่ละแบบอย่างตรงไปตรงมา:

  • การรวมค่าด้วย CASE: ใช้ข้ามระบบ อ่านเข้าใจง่าย และทำงานได้ในทุกระบบเอนจิน เป็นตัวเลือกเริ่มต้น
  • PIVOT ของเซิร์ฟเวอร์ SQL: กระชับเมื่อมีหลายคอลัมน์ แต่การจัดกลุ่มโดยนัยอาจทำให้เกิดความประหลาดใจ
  • การจัดตารางไขว้ของ PostgreSQL: ทรงพลังแต่มีรายละเอียดมาก ต้องใช้ส่วนขยายและรายการกำหนดคอลัมน์

เมื่อไม่แน่ใจ ให้เลือกการรวมค่าแบบมีเงื่อนไข และกล่าวถึงตัวดำเนินการของผู้ผลิตว่าเป็นทางเลือก

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

ทำความเข้าใจพฤติกรรมของ SQL Server PIVOT ที่ผู้สัมภาษณ์มักเจาะถามให้ชัดเจน

สรุป

ไวยากรณ์การหมุนตารางเฉพาะผู้ผลิตในหน้าเดียว:

  • เซิร์ฟเวอร์ SQL: PIVOT (SUM(x) FOR col IN ([a],[b])) พร้อมการจัดกลุ่มโดยนัยเหนือคอลัมน์ที่เหลือ
  • PostgreSQL: crosstab() จาก tablefunc ซึ่งต้องมีรายการกำหนดคอลัมน์ ใช้รูปแบบที่รับสองอาร์กิวเมนต์กับข้อมูลเบาบาง
  • MySQL: ไม่มีทั้งสองแบบ ให้ใช้ CASE
  • ทั้งสามแบบต้องทราบคอลัมน์ตั้งแต่ตอนเขียนคำสั่ง

คำถามที่พบบ่อย

บทเรียน “ไวยากรณ์ PIVOT และ Crosstab เฉพาะระบบ” ฟรีหรือไม่

ใช่ — ข้อความเต็มของ “ไวยากรณ์ PIVOT และ Crosstab เฉพาะระบบ” ฟรีให้อ่านที่นี่บนเว็บ เพื่อปฏิบัติแบบโต้ตอบ (ตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7) และปลดล็อคส่วนที่เหลือของคอร์ส SQL Interview Prep ให้อัปเกรดเป็น CoddyKit PRO คอร์ส SQL Interview Prep มีบทเรียนทั้งหมด 4 บทเรียน

คุณจะเรียนรู้อะไรในบทเรียน “ไวยากรณ์ PIVOT และ Crosstab เฉพาะระบบ”

PIVOT ของ SQL Server และ crosstab ของ Postgres พร้อมข้อจำกัดของแต่ละแบบ คุณปฏิบัติ SQL Interview Prep ด้วยโค้ดที่ใช้งานได้จริงที่คุณเรียกใช้โดยตรงในเบราว์เซอร์ และติวเตอร์ AI ตลอด 24/7 ตอบคำถามของคุณขณะที่คุณไปผ่านบทเรียน

คุณต้องมีประสบการณ์ก่อนที่จะเริ่มเรียน SQL Interview Prep หรือไม่

ไม่จำเป็นต้องมีประสบการณ์มาก่อน SQL Interview Prep บน CoddyKit ออกแบบมาสำหรับผู้เริ่มต้นไปจนถึงผู้เรียนขั้นสูง คุณสามารถเริ่มต้นที่นี่หรือเริ่มจากตัวแรกและเรียนด้วยความเร็วของคุณเอง นี่คือบทเรียนที่ 2 จากทั้งหมด 4 บทเรียน

บทเรียน “ไวยากรณ์ PIVOT และ Crosstab เฉพาะระบบ” ใช้เวลานานแค่ไหน

บทเรียน CoddyKit ส่วนใหญ่ใช้เวลาประมาณ 5–10 นาที แต่ละบทเรียนจึงสั้นและเป็นแบบโต้ตอบ คุณสามารถก้าวหน้าอย่างต่อเนื่องและกลับมาเรียนต่อจากตรงที่เพิ่งหยุดบนเว็บและแอปได้เลย

ฉันเขียนและรันโค้ดในบทเรียน SQL Interview Prep นี้ได้ไหม

ได้ บทเรียน SQL Interview Prep ทุกบทมีตัวแก้ไขโค้ดในตัว คุณจึงเขียนและรันโค้ดจริงได้เลยในเบราว์เซอร์ และได้รับข้อเสนอแนะจาก AI ในทันที — ไม่ต้องติดตั้งในเครื่องของคุณ

บทเรียนทั้งหมดในหลักสูตรนี้

  1. การทำ Pivot ด้วยการรวมตามเงื่อนไข
  2. ไวยากรณ์ PIVOT และ Crosstab เฉพาะระบบ
  3. เปลี่ยนคอลัมน์กลับเป็นแถว
  4. Pivot แบบไดนามิกเมื่อไม่ทราบคอลัมน์ล่วงหน้า
← กลับไปที่ SQL Interview Prep