คลังเก็บบล็อก

กลับแถวตาราง (Crosstab)


เวลาทำรายงานให้แสดงรายรับ รายจ่าย รายปี โดยแสดงข้อมูลของแต่ละเดือนให้อยู่ในแถวเดียวกัน แบบนี้

cyear month1 month2 month3 month4 month5 month6 month7 month8 month9 month10 month11 month12
2554 0 5 3 0 34 0 32 32 0 0 0 0
2555 29 765 97 99 0 0 98 9 98 0 0 22

แต่ในตารางเก็บแยกข้อมูลเป็นเดือนละแถว

cyear cmonth expense income
2554 2 5 77
2554 3 3 15
2554 5 34 5
2554 7 32 64
2554 8 32 64
2554 9 0 0
2555 1 29 38
2555 2 765 32
2555 3 97 24
2555 4 99 8
2555 7 98 76
2555 8 9 89
2555 9 98 8
2555 11 0 98
2555 12 22 0

ถ้าคิวรี่ตามปกติ แถวหนึ่งก็ต้องคิวรี่ครั้งหนึ่ง แสดง 1 ปีก็คิวรี่ 12 ครั้ง ถ้าต้องการคิวรี่แค่ครั้งเดียวสามารถใช้วิธี crosstab ช่วยได้ ก่อนอื่นสร้างข้อมูลตัวอย่างก่อน

CREATE TABLE IF NOT EXISTS `account` (
  `cyear` int(4) NOT NULL,
  `cmonth` int(11) NOT NULL,
  `expense` decimal(10,0) NOT NULL,
  `income` decimal(10,0) NOT NULL,
  PRIMARY KEY (`cyear`,`cmonth`)
) ENGINE=InnoDB;

INSERT INTO `account` (`cyear`, `cmonth`, `expense`, `income`) VALUES
(2554, 2, 5, 77),
(2554, 3, 3, 15),
(2554, 5, 34, 5),
(2554, 7, 32, 64),
(2554, 8, 32, 64),
(2554, 9, 0, 0),
(2555, 1, 29, 38),
(2555, 2, 765, 32),
(2555, 3, 97, 24),
(2555, 4, 99, 8),
(2555, 7, 98, 76),
(2555, 8, 9, 89),
(2555, 9, 98, 8),
(2555, 11, 0, 98),
(2555, 12, 22, 0);

จากนั้นก็คิวรี่โดยใช้

SELECT `cyear`
,SUM(IF(`cmonth` = 1 ,expense ,0)) AS month1
,SUM(IF(`cmonth` = 2 ,expense ,0)) AS month2
,SUM(IF(`cmonth` = 3 ,expense ,0)) AS month3
,SUM(IF(`cmonth` = 4 ,expense ,0)) AS month4
,SUM(IF(`cmonth` = 5 ,expense ,0)) AS month5
,SUM(IF(`cmonth` = 6 ,expense ,0)) AS month6
,SUM(IF(`cmonth` = 7 ,expense ,0)) AS month7
,SUM(IF(`cmonth` = 8 ,expense ,0)) AS month8
,SUM(IF(`cmonth` = 9 ,expense ,0)) AS month9
,SUM(IF(`cmonth` = 10 ,expense ,0)) AS month10
,SUM(IF(`cmonth` = 11 ,expense ,0)) AS month11
,SUM(IF(`cmonth` = 12 ,expense ,0)) AS month12
FROM `account`
GROUP BY `cyear`;

จุดสังเกตคือ

  1. ใช้ GROUP BY เพื่อรวมข้อมูลแต่ละที่สนใจให้อยู่แถวเดียวกัน ในที่นี้คือ ปี
  2. การแสดงผลหลัง select จะใช้รูปแบบ ,SUM(IF(ฟิลย์ที่เป็นเงื่อนไข (เดือน) = ค่าที่สนใจ (ลำดับของเดือน) , ฟิลย์ที่ต้องการแสดง (expense) ,ค่าเริ่มต้นถ้าไม่มีข้อมูล)) AS ชื่อใหม่
โฆษณา

mysql ใครบอกว่ารับงานหนักๆ ไม่ได้


เคยไปรับงานลูกค้าซึ่งเป็นองค์กรรัฐกึ่งเอกชนแหน่งหนึ่ง โดยมีความต้องการขัดใจโปรแกรมเมอร์อย่างหนึ่งคือย้ายจาก mysql เป็น microsoft sql server ซึ่งทำให้การ query ซับซ้อนขึ้นมาก โดยเหตุผลของทางลูกค้าคือไม่มั่นใจว่าจะรองรับข้อมูลจำนวนมากได้ ผมจำได้ว่าแม้แต่ google ที่มีข้อมูลมหาศาลยังใช้ mysql แต่ขณะนั้นไม่มีหลักฐานที่จะแย้งลูกค้าได้ จำใจต้อมยอมรับเงื่อนไขไป

จนวันนี้ได้อ่านเจอว่าระบบเว็บที่มีคนใช้งานติดอันดับต้นๆ ของโลกอย่าง youtube เจ้าของคือ google ใช้ mysql เป็น database ลองเข้าไปอ่าน YouTube Architecture จะเห็นว่าถ้ามี knowleage และการจัดการที่ถูกต้องคนจำนวนน้อยตัวหลักแค่ 10 คน โปรแกรม opensource ก็ไม่น้อยหน้าระบบที่ขายอยู่เป็นหลักแสน หลักล้าน

%d bloggers like this: