เทคนิคการสรุปข้อมูลด้วย MySql ที่เก็บข้อมูลเป็นรายครั้ง เช่น รายการ order ของสินค้า ตามรูป

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

SELECT
YEAR(`last_update`) AS `year`,
SUM(IF(MONTH(`last_update`)=1,`total`,NULL)) AS `1`,
SUM(IF(MONTH(`last_update`)=2,`total`,NULL)) AS `2`,
SUM(IF(MONTH(`last_update`)=3,`total`,NULL)) AS `3`,
SUM(IF(MONTH(`last_update`)=4,`total`,NULL)) AS `4`,
SUM(IF(MONTH(`last_update`)=5,`total`,NULL)) AS `5`,
SUM(IF(MONTH(`last_update`)=6,`total`,NULL)) AS `6`,
SUM(IF(MONTH(`last_update`)=7,`total`,NULL)) AS `7`,
SUM(IF(MONTH(`last_update`)=8,`total`,NULL)) AS `8`,
SUM(IF(MONTH(`last_update`)=9,`total`,NULL)) AS `9`,
SUM(IF(MONTH(`last_update`)=10,`total`,NULL)) AS `10`,
SUM(IF(MONTH(`last_update`)=11,`total`,NULL)) AS `11`,
SUM(IF(MONTH(`last_update`)=12,`total`,NULL)) AS `12`
FROM `orders`
GROUP BY `year`

ผลลัพท์ที่ได้

พระเอกของเราคือคำสั่ง IF

IF(condition, value_true, value_false)

ซึ่งเป็นฟังก์ชั่นที่ลดรูปมาจาก คำสั่ง CASE WHEN ของ SQL

CASE WHEN condition THEN value_true ELSE value_false END

ถ้า condition เป็นจริง จะคืนค่า value_true ถ้าไม่จริง จะคืนค่า value_false (ทั้งสองวิธีสามารถใช้แทนกันได้นะครับ เนื่องจาก MySQL รุ่นเก่าๆ อาจไม่มีคำสั่ง IF)

หลักการก็คือ

SUM( IF( MONTH(`last_update`) = 1, `total`, NULL ) ) AS `1`

ถ้าเดือนเท่ากับ 1 คืนค่า total ของรายการนั้นๆ เอาไปไปบวกกัน (SUM) แล้วคืนค่าไปที่ คอลัมน์ 1 ถ้าเดือนไม่เท่ากับ 1 จะคืนค่า NULL ซึ่งคำสั่ง SUM ก็จะไม่นำไปรวมเป็นผลลัพท์ ซึ่งเดือนอื่นๆก็ทำเช่นเดียวกัน ก็จะได้คอลัมน์มาทั้งหมด 12 คอลัมน์ตามรายเดือน อีกสักตัวอย่าง คล้ายๆกันเป็นการนับจำนวนการทำรายการรายเดือน

SELECT
YEAR(`last_update`) AS `year`,
SUM(IF(MONTH(`last_update`)=1,1,0)) AS `1`,
SUM(IF(MONTH(`last_update`)=2,1,0)) AS `2`,
SUM(IF(MONTH(`last_update`)=3,1,0)) AS `3`,
SUM(IF(MONTH(`last_update`)=4,1,0)) AS `4`,
SUM(IF(MONTH(`last_update`)=5,1,0)) AS `5`,
SUM(IF(MONTH(`last_update`)=6,1,0)) AS `6`,
SUM(IF(MONTH(`last_update`)=7,1,0)) AS `7`,
SUM(IF(MONTH(`last_update`)=8,1,0)) AS `8`,
SUM(IF(MONTH(`last_update`)=9,1,0)) AS `9`,
SUM(IF(MONTH(`last_update`)=10,1,0)) AS `10`,
SUM(IF(MONTH(`last_update`)=11,1,0)) AS `11`,
SUM(IF(MONTH(`last_update`)=12,1,0)) AS `12`
FROM `orders`
GROUP BY `year`

ผลลัพท์ที่ได้