Pivot Table in MySQL (somewhat)
ช่วงนี้ใช้เวลาส่วนใหญ่ไปกับโปรแกรม database เนื่องจากว่าต้องปรับระบบจัดเก็บสินค้า เพราะก่อนหน้านี้ทำงานออกแนวโชห่วย พอถึงเวลาก็ตามหาของที่จะขายไม่เจอ เนื่องจากหมดไปแล้วบ้าง หรือส่งไปขายที่ไหนเท่าไหร่ก็ลำบากที่จะหา เพราะต้องเปิดดูจาก paper record อันที่จริงเริ่มปรับระบบข้อมูลมาสักประมาณเดือนครึ่งได้แล้ว แต่เพิ่งจะ input ข้อมูลเสร็จเมื่อ 1-2 วันที่ผ่านมา
โดยระบบปกติที่ถูกสอนถูกฝึกและปฏิบัติกันมา เราก็จะเขียน database ให้แยกเก็บสินค้า (products), ชนิดเอกสาร (ในที่นี้ invoices) และ รายการของเอกสารแต่ละใบ (lineitems) รายการก็จะเป็นประมาณแบบนี้
Product, Invoices and Lineitems
และเราก็สามารถเลือกจับกลุ่มดูเอกสารและสินค้าได้ ประมาณแบบนี้
Join All
หรือถ้าจะดูเฉพาะใบ ก็ใส่ WHERE
เข้าไป
แต่ถ้าสมมติว่า เราอยากจะดูรายงานรวมของเอกสารและสินค้า โดยให้ header ของตารางเป็นหมายเลขเอกสาร (inv_number
) และแต่ละบรรทัดก็แสดงแต่ละรายการหละ?
คุณคนเขียนก็นั่งงมไปงมมา ค้นหาตามเวบไซต์ต่าง ๆ เค้าก็มีพูดถึงว่า MySQL ไม่สนับนุนการทำ pivot table แต่สามารถเขียนคล้าย ๆ กับ prepared statement เพื่อให้ทำงานก่อนคำสั่งหลักได้
หลังจากนั่งทำความเข้าใจก็จับใจความได้ว่า ต้องเริ่มจากใช้ฟังก์ชั่นพวกรวมกลุ่ม (aggregate functions - MAX, SUM, อะไรต่าง ๆ นานา) ร่วมกับ CASE
เช่น ถ้า inv_number
คือ INV-0001 (อ้างอิงจาก invoice_id
) ก็ให้หยิบเอา quantity
column มาแสดง แล้วใช้ INV-0001 เป็น column header และที่สำคัญ ต้องไม่ลืม GROUP BY
ด้วย
พูดแล้วก็ งง เอาเป็นว่ามันเป็นแบบนี้ครับ
SELECT products.code, MAX( CASE WHEN invoice_items.invoice_id = 1 THEN invoice_items.quantity END ) AS 'INV-0001' FROM products LEFT JOIN invoice_items ON products.id = invoice_items.product_id LEFT JOIN invoices ON invoice_items.invoice_id = invoices.id GROUP BY products.code;
ได้ผลประมาณนี้
Aggregate, one at a time
แต่ SQL ข้างบน เราต้องรู้ว่า id
และ inv_number
ของเอกสารแต่ละใบคืออะไร เช่น ถ้าต้องการดูของ INV-0002 ก็ต้องเพิ่มบรรทัด MAX(CASE WHEN invoice_items.invoice_id = 2 THEN invoice_items.quantity END) AS 'INV-0002
เข้าไป
SELECT products.`code`, MAX( CASE WHEN invoice_items.invoice_id = 1 THEN invoice_items.quantity END ) AS 'INV-0001', MAX( CASE WHEN invoice_items.invoice_id = 2 THEN invoice_items.quantity END ) AS 'INV-0002' FROM products LEFT JOIN invoice_items ON products.id = invoice_items.product_id LEFT JOIN invoices ON invoice_items.invoice_id = invoices.id GROUP BY products.`code`;
ซึ่งการทำแบบนี้แทบเป็นไปไม่ได้เลยสำหรับกรณีที่มีเอกสารเยอะแยะมากมาย แต่เราสามารถเขียนส่วนของ aggregation เตรียมไว้ก่อนได้ โดยใช้ CONCAT
และ GROUP_CONCAT
มาช่วยรวบรวม string ลักษณะของ query ก็ประมาณนี้
SELECT GROUP_CONCAT( DISTINCT CONCAT( 'MAX(CASE WHEN invoice_items.invoice_id = ', invoices.id, ' THEN invoice_items.quantity END) AS \'', inv_number, '\'' ) ) FROM invoices LEFT JOIN invoice_items ON invoice_items.invoice_id = invoices.id;
ผลที่ได้ก็จะเป็น string ยาว ๆ ของ MAX(CASE WHEN .....),MAX(CASE WHEN .....)
แต่ด้วยความที่ string อันนี้อาจจะยาวมาก มันก็อาจจะไปเกิน limit ของ group_concat_max_len
ที่ MySQL มีกำหนดไว้ เราก็อาจจะต้องไป override ตัวแปรนี้ชั่วคราวสำหรับ session นี้ไปก่อนด้วย
ส่วน query หลัก เราก็ใช้อันเดียวกับข้างบน เพียงแต่แทนส่วนของ MAX(CASE WHEN .....)
ด้วย aggregation statements ที่เตรียมไว้ ก็ต้องมีตัวแปรเพิ่มเล็กน้อย เขียนรวม ๆ แล้วก็ได้ประมาณนี้
SET SESSION group_concat_max_len = 2000; SET @sql = NULL; SELECT GROUP_CONCAT( DISTINCT CONCAT( 'MAX(CASE WHEN invoice_items.invoice_id = ', invoices.id, ' THEN invoice_items.quantity END) AS \'', inv_number, '\'' ) ) INTO @sql FROM invoices LEFT JOIN invoice_items ON invoice_items.invoice_id = invoices.id; SET @sql = CONCAT( 'SELECT products.code, ', @sql, ' FROM products LEFT JOIN invoice_items ON products.id = invoice_items.product_id LEFT JOIN invoices ON invoice_items.invoice_id = invoices.id GROUP BY products.code' ); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
ก็จะได้ผลลัพธ์ดังนี้ ลองไปเล่นกันได้ที่ SQL Fiddle ครับ
Pivot Table
หรือถ้าไม่ชอบ NULL ก็เปลี่ยนจาก CASE
เป็น IF
จะได้มี false condition ได้ ก็จะได้ผลประมาณนี้
Pivot Table with 0 instead of NULL.
เขียนมาถึงตรงนี้แล้ว นึกขึ้นได้ว่าเขียน database schema เป็นอีกแบบก็ได้นี่นา … จะย้อนกลับไปแก้ “ตั้งแต่ต้น” เลยดีมะ? -“-
สวัสดีครับ