Pivot Table in MySQL (somewhat) - macsf.net || venting the brain

ช่วงนี้ใช้เวลาส่วนใหญ่ไปกับโปรแกรม 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;

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`;

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;

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;

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 เป็นอีกแบบก็ได้นี่นา … จะย้อนกลับไปแก้ “ตั้งแต่ต้น” เลยดีมะ? -“-

สวัสดีครับ