0Pricing
SQL Academy · บทเรียน

รูปแบบตารางไขว้ (PostgreSQL crosstab())

สร้างตาราง Pivot ที่แท้จริงด้วยฟังก์ชัน crosstab() ของส่วนขยาย tablefunc

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

เหตุใดจึงต้องใช้ crosstab จริง ๆ

pivot ด้วย CASE บังคับให้คุณระบุคอลัมน์เป้าหมายแต่ละคอลัมน์ สำหรับ pivot ที่มีคอลัมน์จำนวนมากจริง ๆ เช่น หนึ่งคอลัมน์ต่อผลิตภัณฑ์ ส่วนขยาย tablefunc และ crosstab() คือเครื่องมือที่เหมาะสม

การเปิดใช้ส่วนขยาย

tablefunc มาพร้อมกับ PostgreSQL contrib:

CREATE EXTENSION IF NOT EXISTS tablefunc;

รูปแบบพื้นฐานของ crosstab

crosstab รับสตริง SQL ที่มี 3 คอลัมน์ ได้แก่ row_key, category และ value แล้วคืนค่า row_key ตามด้วยหนึ่งคอลัมน์ต่อหมวดหมู่:

SELECT * FROM crosstab(
  $$
    SELECT user_id, status, COUNT(*)::INT
    FROM orders
    GROUP BY user_id, status
    ORDER BY user_id, status
  $$
) AS ct (
  user_id BIGINT,
  paid    INT,
  pending INT,
  cancelled INT
);

เหตุใดจึงต้องประกาศคอลัมน์ผลลัพธ์

SQL เป็นระบบชนิดข้อมูลแบบคงที่ ตัววางแผนจึงต้องทราบคอลัมน์ผลลัพธ์ตั้งแต่ตอนแยกวิเคราะห์ ดังนั้นคุณต้องระบุโครงสร้างในส่วนคำสั่ง AS รวมถึงชนิดข้อมูลด้วย

crosstab แบบสองอาร์กิวเมนต์ (พร้อมชุดหมวดหมู่)

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

SELECT * FROM crosstab(
  $$
    SELECT user_id, status, COUNT(*)::INT
    FROM orders GROUP BY user_id, status
    ORDER BY user_id
  $$,
  $$ VALUES ('paid'), ('pending'), ('cancelled') $$
) AS ct (
  user_id BIGINT, paid INT, pending INT, cancelled INT
);

เมื่อ CASE เหนือกว่า crosstab

สำหรับชุดหมวดหมู่ขนาดเล็กที่ทราบแน่ชัด CASE/FILTER จะเรียบง่ายกว่า โดยไม่ต้องใช้ส่วนขยายและไม่ต้องกังวลกับข้อผิดพลาดของรูปแบบสองอาร์กิวเมนต์ ควรใช้ crosstab เมื่อ:

  • คุณมีหมวดหมู่จำนวนมาก
  • หมวดหมู่ถูกโหลดแบบไดนามิก
  • คุณกำลังสร้างข้อมูลสำหรับเครื่องมือ pivot ภายนอก

Pivot แบบไดนามิก

สำหรับหมวดหมู่ที่ไม่ทราบในขณะทำงาน ให้สร้าง SQL ในแอปของคุณ หรือใช้ PL/pgSQL ร่วมกับ format() + EXECUTE

-- Build the SQL dynamically:
SELECT string_agg(format('SUM(CASE WHEN status = %L THEN 1 END) AS %I',
                          status, status), ', ')
FROM (SELECT DISTINCT status FROM orders) s;

ทำ pivot แบบกว้างสำหรับสเปรดชีต

รายงานสำหรับนักวิเคราะห์มักต้องการรูปแบบกว้าง คุณสามารถสร้างรูปแบบนี้ใน SQL หรือส่งต่อข้อมูลแบบยาว แล้วให้เครื่องมือ BI ทำ pivot

Unpivot: การแปลงกลับ

หากต้องการแปลงจากกว้าง → ยาว ให้ใช้ UNION ALL หรือ jsonb_each_text() ของ PostgreSQL:

SELECT id, key AS month, (value)::NUMERIC AS revenue
FROM monthly_wide,
     jsonb_each_text(to_jsonb(monthly_wide) - 'id');

ประสิทธิภาพ

crosstab() จะเรียกใช้ SQL ด้านในหนึ่งครั้งแล้วทำ pivot ในหน่วยความจำ คอขวดจึงเหมือนกับคำค้นที่ใช้ GROUP BY ทั่วไป

ข้อจำกัดของ crosstab

PostgreSQL ไม่มีคีย์เวิร์ด PIVOT ในตัว ต่างจาก Oracle/SQL Server ดังนั้น crosstab() จึงเป็นวิธีแก้ปัญหา

สรุป

สำหรับ pivot ส่วนใหญ่ CASE/FILTER เป็นคำตอบที่กระชับและชัดเจน ส่วน crosstab() เหมาะเมื่อมีหมวดหมู่จำนวนมากหรือยังไม่ทราบหมวดหมู่ล่วงหน้า

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

ส่วนขยายใดมีฟังก์ชัน crosstab() ของ PostgreSQL

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

บทเรียน “รูปแบบตารางไขว้ (PostgreSQL crosstab())” ฟรีหรือไม่

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

คุณจะเรียนรู้อะไรในบทเรียน “รูปแบบตารางไขว้ (PostgreSQL crosstab())”

สร้างตาราง Pivot ที่แท้จริงด้วยฟังก์ชัน crosstab() ของส่วนขยาย tablefunc คุณปฏิบัติ SQL Academy ด้วยโค้ดที่ใช้งานได้จริงที่คุณเรียกใช้โดยตรงในเบราว์เซอร์ และติวเตอร์ AI ตลอด 24/7 ตอบคำถามของคุณขณะที่คุณไปผ่านบทเรียน

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

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

บทเรียน “รูปแบบตารางไขว้ (PostgreSQL crosstab())” ใช้เวลานานแค่ไหน

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

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

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

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

  1. UNION, INTERSECT, EXCEPT
  2. UNION ALL เทียบกับ UNION (ต้นทุนการลบข้อมูลซ้ำ)
  3. นิพจน์ CASE และคำค้นแบบ Pivot
  4. รูปแบบตารางไขว้ (PostgreSQL crosstab())
← กลับไปที่ SQL Academy