Pivot แบบไดนามิกเมื่อไม่ทราบคอลัมน์ล่วงหน้า
สร้างคอลัมน์ของ pivot เมื่อยังไม่ทราบหมวดหมู่ล่วงหน้า
Pivot แบบไดนามิกเมื่อไม่ทราบคอลัมน์ล่วงหน้า เป็นบทเรียน Coding Interview Prep ฟรีบน CoddyKit นี่คือบทเรียนที่ 4 จากทั้งหมด 4 บทเรียน คุณสามารถอ่านบทเรียนทั้งหมดด้านล่างฟรี — จากนั้นลองปฏิบัติด้วยตัวคุณเองในเบราว์เซอร์พร้อมตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7 บทเรียนนี้เป็นส่วนหนึ่งของเส้นทางการเรียน Coding Interview Prep และความก้าวหน้าของคุณจะซิงค์ข้ามเว็บและแอป CoddyKit คอร์ส Coding Interview Prep มีบทเรียนทั้งหมด 4 บทเรียน
โจทย์พิวอตที่ยาก
พิวอตแบบคงที่ทุกชนิด ไม่ว่าจะเป็นการรวมค่าด้วย CASE การใช้ PIVOT ของ SQL Server หรือ crosstab ของ PostgreSQL ล้วนมีข้อจำกัดร่วมกันอย่างหนึ่งคือ คุณต้องระบุคอลัมน์ผลลัพธ์เมื่อเขียนคำสั่ง
แต่ถ้าไม่ทราบหมวดหมู่ล่วงหน้า เช่น ชื่อสินค้าที่เปลี่ยนทุกสัปดาห์ หรือการมีหนึ่งคอลัมน์ต่อหนึ่งเดือนที่กำลังใช้งานอยู่ล่ะ นั่นคือ พิวอตแบบไดนามิก และเป็นคำถามสัมภาษณ์ระดับผู้มีประสบการณ์สูง เพราะ SQL แบบปกติไม่สามารถส่งคืนผลลัพธ์ที่รายการคอลัมน์ถูกกำหนดขณะทำงานได้
เหตุใด SQL เพียงอย่างเดียวจึงทำไม่ได้
SQL มีชนิดข้อมูลแบบคงที่ในระดับชุดผลลัพธ์ ตัววางแผนการทำงานจึงต้องทราบคอลัมน์และชนิดข้อมูลของคอลัมน์เหล่านั้นก่อนเริ่มทำงาน คำสั่งเดียวไม่สามารถระบุว่า ให้สร้างหนึ่งคอลัมน์สำหรับแต่ละค่าที่ค้นพบ ได้
ดังนั้นวิธีทั่วไปคือ สร้างข้อความ SQL ในสองขั้นตอน ขั้นแรกให้ค้นหาหมวดหมู่ที่ไม่ซ้ำกัน จากนั้นสร้างสตริงคำสั่งพิวอตจากหมวดหมู่เหล่านั้นแล้วเรียกใช้สตริงดังกล่าว
ขั้นตอนที่ 1: รวบรวมหมวดหมู่
ขั้นตอนแรกคือการใช้คำสั่งค้นหาปกติเพื่อแสดงค่าที่ไม่ซ้ำกันซึ่งจะกลายเป็นคอลัมน์ โดยทั่วไปควรจัดเรียงค่าเหล่านี้เพื่อให้รูปแบบคอลัมน์คงที่
ผลลัพธ์นี้จะถูกส่งต่อให้ขั้นตอนการสร้างสตริง ในระบบจริง คุณจะเรียกใช้คำสั่งนี้ เก็บแถวผลลัพธ์ไว้ แล้วประกอบคำสั่งถัดไปจากข้อมูลเหล่านั้น
SELECT DISTINCT quarter
FROM sales
ORDER BY quarter;
-- e.g. Q1, Q2, Q3, Q4ขั้นตอนที่ 2: สร้างรายการคอลัมน์
ถัดไป ให้แปลงค่าเหล่านั้นเป็นรายการนิพจน์ CASE ที่คั่นด้วยจุลภาค (หรือเป็นชื่อในวงเล็บเหลี่ยมสำหรับ PIVOT) ฐานข้อมูลมีฟังก์ชันรวมสตริงสำหรับทำงานนี้ภายใน SQL
ใน PostgreSQL คือ string_agg ใน MySQL คือ GROUP_CONCAT และใน SQL Server คือ STRING_AGG หรือเทคนิคเก่าอย่าง FOR XML PATH
-- Postgres: build the SELECT-list fragment
SELECT string_agg(
format('SUM(CASE WHEN quarter = %L THEN amount END) AS %I',
quarter, quarter),
', '
)
FROM (SELECT DISTINCT quarter FROM sales ORDER BY 1) q;ขั้นตอนที่ 3: ประกอบและเรียกใช้
นำส่วนย่อยที่สร้างขึ้นมาต่อกันเป็นสตริงคำสั่งแบบเต็ม จากนั้นเรียกใช้ด้วยการทำงานแบบไดนามิก โดยใช้ EXECUTE ใน PL/pgSQL, sp_executesql ใน SQL Server หรือ PREPARE/EXECUTE ใน MySQL
นี่คือหัวใจของพิวอตแบบไดนามิก: SQL เขียน SQL แล้วเรียกใช้ SQL นั้น
-- SQL Server pattern
DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(quarter), ',')
FROM (SELECT DISTINCT quarter FROM sales) q;
SET @sql = N'SELECT region, ' + @cols + '
FROM (SELECT region, quarter, amount FROM sales) s
PIVOT (SUM(amount) FOR quarter IN (' + @cols + ')) p;';
EXEC sp_executesql @sql;ตัวอย่างเต็มรูปแบบของ PostgreSQL
ใน PostgreSQL คุณสามารถครอบทั้งสามขั้นตอนไว้ในบล็อก DO หรือฟังก์ชันได้ สร้างรายการคอลัมน์ด้วย string_agg แทรกรายการนั้นลงในคำสั่ง แล้วเรียกใช้ด้วย EXECUTE
เนื่องจากไม่ทราบคอลัมน์ผลลัพธ์จนกว่าจะถึงเวลาทำงาน ฟังก์ชันที่ส่งคืนผลลัพธ์ลักษณะนี้จึงมักใช้ RETURNS SETOF record หรือส่งคืนแถวในรูปแบบ json แล้วให้ผู้เรียกขยายข้อมูลเหล่านั้น
DO $do$
DECLARE
cols text;
qry text;
BEGIN
SELECT string_agg(
format('SUM(CASE WHEN quarter=%L THEN amount END) AS %I', quarter, quarter), ', ')
INTO cols
FROM (SELECT DISTINCT quarter FROM sales ORDER BY 1) q;
qry := format('SELECT region, %s FROM sales GROUP BY region', cols);
EXECUTE qry;
END $do$;MySQL กับคำสั่งที่เตรียมไว้
MySQL ไม่มีตัวดำเนินการพิวอต ดังนั้นพิวอตแบบไดนามิกจึงสร้างสตริงสำหรับการรวมแบบมีเงื่อนไขด้วย GROUP_CONCAT แล้วเรียกใช้ผ่านคำสั่งที่เตรียมไว้
GROUP_CONCAT มีขีดจำกัดความยาวคือ group_concat_max_len ซึ่งผู้สัมภาษณ์อาจถามถึง หากมีหลายหมวดหมู่ คุณควรเพิ่มค่าขีดจำกัดนี้
SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
CONCAT('SUM(CASE WHEN quarter=''', quarter,
''' THEN amount END) AS ', QUOTE(quarter))
) INTO @sql FROM sales;
SET @sql = CONCAT('SELECT region, ', @sql,
' FROM sales GROUP BY region');
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;ความเสี่ยงจากการแทรกคำสั่ง SQL
เนื่องจากคุณนำค่าข้อมูลมาต่อเป็น SQL ที่เรียกใช้ได้ พิวอตแบบไดนามิกจึงมี ความเสี่ยงจากการแทรกคำสั่ง หากค่าหมวดหมู่มีเครื่องหมายอัญประกาศหรือข้อความอันตราย ก็อาจทำให้คำสั่งที่สร้างขึ้นเสียหายหรือถูกยึดการควบคุมได้
ให้หลีกเลี่ยงอักขระพิเศษในตัวระบุและค่าลิเทอรัลด้วยตัวช่วยที่ปลอดภัยของระบบฐานข้อมูลเสมอ เช่น format('%I', ...) และ %L ใน PostgreSQL และ QUOTENAME ใน SQL Server ห้ามนำค่าดิบมาต่อเป็นสตริงโดยตรง
-- Safe quoting prevents injection / breakage
-- Postgres: %I identifier, %L literal
format('SUM(CASE WHEN k=%L THEN v END) AS %I', cat, cat)
-- SQL Server: QUOTENAME(cat)การส่งคืนคอลัมน์ที่ไม่ทราบล่วงหน้า
อีกประเด็นที่ยากคือ ผู้เรียกไม่สามารถทราบรูปแบบผลลัพธ์ล่วงหน้าได้ กลยุทธ์ทั่วไปที่ผู้สัมภาษณ์ยอมรับมีดังนี้:
- ส่งคืนแถวในรูปแบบ
JSONแล้วให้ชั้นแอปพลิเคชันขยายคีย์ต่างๆ - ให้กระบวนงานแสดงหรือสร้างคำสั่ง แล้วเรียกใช้คำสั่งนั้นเป็นขั้นตอนที่สอง
- ทำพิวอตขั้นสุดท้ายในโค้ดแอปพลิเคชัน เช่น pandas หรือเครื่องมือ BI เมื่อทราบหมวดหมู่แล้ว
ไม่มีวิธีที่สะอาดในการส่งคืนคอลัมน์ที่กำหนดได้ตามต้องการจากการเรียกแบบคงที่เพียงครั้งเดียว
ตัวอย่างที่ทำเสร็จแล้ว: พิวอตตามสินค้า
สมมติว่าสินค้ามีการเปลี่ยนแปลงเข้าออก และรายงานต้องมีคอลัมน์รายได้หนึ่งคอลัมน์ต่อสินค้าที่มีอยู่ใน sales ขณะนั้น คุณไม่สามารถเขียนรายการคอลัมน์แบบตายตัวได้ จึงต้องสร้างรายการขึ้นมา PostgreSQL ทำให้เขียนได้อ่านง่าย โดยสร้างส่วนย่อยของ CASE ด้วย string_agg และการใส่เครื่องหมายอัญประกาศอย่างปลอดภัย แทรกส่วนย่อยนั้นลงในคำสั่ง แล้วจึงใช้ EXECUTE
อธิบายขั้นตอนให้ผู้สัมภาษณ์ฟังตามลำดับ: ค้นหาสินค้า จัดรูปแบบสินค้าแต่ละรายการให้เป็นคอลัมน์ที่มีเครื่องหมายอัญประกาศ ประกอบคำสั่ง แล้วเรียกใช้ โครงสร้างเดียวกันนี้ใช้ได้กับทุกระบบ เพียงตัวช่วยที่ใช้จะแตกต่างกัน
DO $do$
DECLARE cols text; qry text;
BEGIN
SELECT string_agg(
format('SUM(CASE WHEN product=%L THEN amount END) AS %I',
product, product), ', ')
INTO cols
FROM (SELECT DISTINCT product FROM sales ORDER BY 1) p;
qry := format('SELECT region, %s FROM sales GROUP BY region', cols);
EXECUTE qry;
END $do$;เมื่อใดควรหลีกเลี่ยงพิวอตแบบไดนามิก
ผู้สมัครที่มีความสามารถจะรู้ว่าเมื่อใด ไม่ควรทำสิ่งนี้ใน SQL SQL แบบไดนามิกอ่านยากกว่า ทดสอบยากกว่า รักษาความปลอดภัยได้ยากกว่า และแคชได้ยากกว่า คำตอบที่มักดีกว่าคือ:
- ส่งคืนข้อมูลในรูปแบบยาวจาก SQL แล้วทำพิวอตในแอปพลิเคชันหรือชั้นรายงาน
- หากชุดหมวดหมู่มีขนาดเล็กและเปลี่ยนแปลงไม่บ่อย ให้ใช้พิวอตแบบคงที่และปรับปรุงเป็นครั้งคราว
ควรใช้พิวอตแบบไดนามิกกับชุดหมวดหมู่ที่เปิดกว้างและเปลี่ยนแปลงอยู่เสมออย่างแท้จริงเท่านั้น
ตรวจสอบอย่างรวดเร็ว
ทดสอบเหตุผลสำคัญที่ทำให้ต้องมีพิวอตแบบไดนามิก
สรุปทบทวน
พิวอตแบบไดนามิกใช้จัดการกับชุดคอลัมน์ที่ไม่ทราบล่วงหน้า:
- พิวอตแบบคงที่ทำงานไม่ได้ เพราะคอลัมน์ผลลัพธ์ต้องถูกกำหนดก่อนเริ่มทำงาน
- รูปแบบคือ ค้นหาหมวดหมู่ที่ไม่ซ้ำกัน สร้างสตริง SQL สำหรับพิวอต แล้วเรียกใช้แบบไดนามิก
- ใช้
string_agg/GROUP_CONCAT/STRING_AGGเพื่อสร้างรายการคอลัมน์ - หลีกเลี่ยงการแทรกคำสั่ง SQL ด้วยการจัดการค่าให้ปลอดภัย (
%I/%L,QUOTENAME) - บ่อยครั้งการส่งคืนข้อมูลในรูปแบบยาวแล้วทำพิวอตในชั้นแอปจะสะอาดกว่า
คำถามที่พบบ่อย
บทเรียน “Pivot แบบไดนามิกเมื่อไม่ทราบคอลัมน์ล่วงหน้า” ฟรีหรือไม่
ใช่ — ข้อความเต็มของ “Pivot แบบไดนามิกเมื่อไม่ทราบคอลัมน์ล่วงหน้า” ฟรีให้อ่านที่นี่บนเว็บ เพื่อปฏิบัติแบบโต้ตอบ (ตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7) และปลดล็อคส่วนที่เหลือของคอร์ส Coding Interview Prep ให้อัปเกรดเป็น CoddyKit PRO คอร์ส Coding Interview Prep มีบทเรียนทั้งหมด 4 บทเรียน
คุณจะเรียนรู้อะไรในบทเรียน “Pivot แบบไดนามิกเมื่อไม่ทราบคอลัมน์ล่วงหน้า”
สร้างคอลัมน์ของ pivot เมื่อยังไม่ทราบหมวดหมู่ล่วงหน้า คุณปฏิบัติ Coding Interview Prep ด้วยโค้ดที่ใช้งานได้จริงที่คุณเรียกใช้โดยตรงในเบราว์เซอร์ และติวเตอร์ AI ตลอด 24/7 ตอบคำถามของคุณขณะที่คุณไปผ่านบทเรียน
คุณต้องมีประสบการณ์ก่อนที่จะเริ่มเรียน Coding Interview Prep หรือไม่
ไม่จำเป็นต้องมีประสบการณ์มาก่อน Coding Interview Prep บน CoddyKit ออกแบบมาสำหรับผู้เริ่มต้นไปจนถึงผู้เรียนขั้นสูง คุณสามารถเริ่มต้นที่นี่หรือเริ่มจากตัวแรกและเรียนด้วยความเร็วของคุณเอง นี่คือบทเรียนที่ 4 จากทั้งหมด 4 บทเรียน
บทเรียน “Pivot แบบไดนามิกเมื่อไม่ทราบคอลัมน์ล่วงหน้า” ใช้เวลานานแค่ไหน
บทเรียน CoddyKit ส่วนใหญ่ใช้เวลาประมาณ 5–10 นาที แต่ละบทเรียนจึงสั้นและเป็นแบบโต้ตอบ คุณสามารถก้าวหน้าอย่างต่อเนื่องและกลับมาเรียนต่อจากตรงที่เพิ่งหยุดบนเว็บและแอปได้เลย
ฉันเขียนและรันโค้ดในบทเรียน Coding Interview Prep นี้ได้ไหม
ได้ บทเรียน Coding Interview Prep ทุกบทมีตัวแก้ไขโค้ดในตัว คุณจึงเขียนและรันโค้ดจริงได้เลยในเบราว์เซอร์ และได้รับข้อเสนอแนะจาก AI ในทันที — ไม่ต้องติดตั้งในเครื่องของคุณ
บทเรียนทั้งหมดในหลักสูตรนี้
- การทำ Pivot ด้วยการรวมตามเงื่อนไข
- ไวยากรณ์ PIVOT และ Crosstab เฉพาะระบบ
- เปลี่ยนคอลัมน์กลับเป็นแถว
- Pivot แบบไดนามิกเมื่อไม่ทราบคอลัมน์ล่วงหน้า