0

Dynamic pivoting in MySQL

คิดถึงรายงานคิดถึง Excel 555 … เรื่องความตั้งใจในการเรียน Microsoft Excel นี่มีมาสักพักแล้ว เริ่มด้วยการซื้อหนังสือมาก่อนแต่น่าจะหลายเดือนละ (จนลืมไปเลยว่าซื้อมาตอนไหน บาปมาก) แต่ก็นะความขี้เกียจครอบงำ ช่วงนี้ก็เลยเป็นช่วงอ่านหนังสือ Microsoft Excel แบบจริงจัง (แอบตั้งใจ)
เปิดดูคร่าว ๆ มีบทนึงพูดถึงเรื่องการทำ Pivot Table ในโปรแกรมตระกูลตารางคำนวณเรื่องนี้ทุกตัวในตลาดก็สามารถทำได้ ถือเป็นหนึ่งใน Killer Feature แต่เรื่องนี้แค่ประเด็นนิดหน่อย ^_^

สำหรับคนที่เขียนคำสั่งเรียกดูข้อมูล (SQL) ก็ทำเรื่องนี้อยู่เนือง ๆ หลายครั้งก็ทำแบบฮาร์ดคอร์ ณ ตอนนั้นเลย ตัวอย่างคร่าว ๆ สมมุติเรามีข้อมูลค่าใช้จ่ายภายในบ้านสรุปประมาณนี้ ในฐานข้อมูล (ด้านซ้าย) และต้องการแสดงผลเป็นอีกรูปด้านขวา

SQL แสดงรายงานแบบนี้ อนุมานจากข้อมูลดิบที่เก็บในฐานข้อมูล ก่อนที่เราจะสรุปได้ก็น่าจะมีประมาณนี้ (เราใช้บริการของ generatedata.com) เอาตัวอย่างข้อมูลสัก 100 รายการหล่ะกัน

DROP TABLE IF EXISTS `ExpenseCategory`;
CREATE TABLE `ExpenseCategory` (
  `CategoryId` varchar(255) NOT NULL,
  `CategoryName` varchar(255) default NULL,
  PRIMARY KEY (`CategoryId`)
);

INSERT INTO `ExpenseCategory` (`CategoryId`,`CategoryName`) VALUES ("01","ความบันเทิง"),("02","ของชำ"),("03","ครอบครัว"),("04","การคมนาคมขนส่ง");

DROP TABLE IF EXISTS `Expense`;
CREATE TABLE `Expense` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `CategoryId` varchar(255) default NULL,
  `ExpenseDate` varchar(255),
  `Amount` varchar(100) default NULL,
  PRIMARY KEY (`id`)
) AUTO_INCREMENT=1;

INSERT INTO `Expense` VALUES (1,'04','2018-07-19 02:55:09','83369'),(2,'01','2018-01-17 19:18:21','28001'),(3,'04','2018-06-29 03:32:27','17058'),(4,'04','2018-07-04 17:44:03','64619'),(5,'04','2018-07-22 17:38:56','24050'),(6,'04','2018-08-15 03:43:50','80585'),(7,'03','2018-06-11 23:52:59','79427'),(8,'02','2018-04-29 04:03:38','63177'),(9,'01','2018-09-03 04:02:46','77975'),(10,'04','2018-07-04 21:49:15','61752'),(11,'01','2018-02-21 11:39:34','59245'),(12,'04','2018-01-05 05:42:14','15695'),(13,'02','2018-09-13 09:07:51','22132'),(14,'02','2018-08-12 13:24:09','58156'),(15,'01','2018-08-14 18:34:16','7802'),(16,'01','2018-04-04 04:26:02','44733'),(17,'03','2018-11-03 02:59:01','73158'),(18,'04','2018-01-23 09:59:45','69724'),(19,'02','2018-11-16 07:39:50','98963'),(20,'03','2018-01-04 11:20:59','93659'),(21,'02','2018-08-25 22:01:10','13945'),(22,'04','2018-04-08 22:46:07','90735'),(23,'01','2018-09-13 10:20:59','76856'),(24,'01','2018-09-02 08:45:17','54668'),(25,'03','2018-12-06 12:19:30','45571'),(26,'02','2018-05-27 09:48:40','24694'),(27,'01','2018-04-15 03:01:29','73961'),(28,'01','2018-03-11 06:05:34','53546'),(29,'02','2018-12-27 06:57:17','91375'),(30,'04','2018-02-05 05:14:19','44997'),(31,'02','2018-12-15 02:35:52','54144'),(32,'02','2018-08-01 02:06:37','95898'),(33,'02','2018-02-18 08:50:43','79249'),(34,'02','2018-03-26 04:39:35','66184'),(35,'01','2018-07-26 13:17:03','44670'),(36,'02','2018-12-26 13:50:39','88500'),(37,'04','2018-04-19 20:26:25','31414'),(38,'01','2018-07-28 13:53:24','56802'),(39,'02','2018-04-27 06:18:15','67682'),(40,'01','2018-10-11 12:25:37','98233'),(41,'03','2018-09-28 12:36:05','43118'),(42,'02','2018-08-17 16:06:34','94942'),(43,'02','2018-12-16 16:28:16','25039'),(44,'04','2018-04-14 15:47:06','87368'),(45,'03','2018-11-21 16:53:01','49355'),(46,'04','2018-01-06 17:08:54','63354'),(47,'03','2018-02-02 11:06:33','26540'),(48,'01','2018-10-19 03:50:49','32534'),(49,'01','2018-05-15 23:40:11','58038'),(50,'03','2018-04-28 23:53:24','2915'),(51,'01','2018-03-30 18:57:12','10229'),(52,'03','2018-04-22 07:47:45','20004'),(53,'03','2018-08-16 23:42:08','69185'),(54,'02','2018-03-09 20:38:52','92533'),(55,'02','2018-12-25 23:51:10','66589'),(56,'04','2018-09-22 05:40:21','94759'),(57,'01','2018-06-03 13:24:32','76588'),(58,'03','2018-04-21 07:27:01','66573'),(59,'02','2018-09-25 03:49:02','76607'),(60,'01','2018-08-11 10:48:24','47755'),(61,'04','2018-11-02 02:37:54','69570'),(62,'02','2018-08-31 14:44:15','25560'),(63,'02','2018-03-23 07:24:59','78550'),(64,'04','2018-06-07 17:39:12','18455'),(65,'02','2018-10-31 11:14:30','71227'),(66,'01','2018-10-31 14:23:16','2454'),(67,'04','2018-08-14 21:16:17','54700'),(68,'04','2018-06-05 01:04:59','59554'),(69,'01','2018-01-23 10:33:50','37798'),(70,'04','2018-06-09 12:00:56','59983'),(71,'04','2018-02-19 15:56:33','56754'),(72,'04','2018-12-24 13:14:33','54684'),(73,'02','2018-05-15 23:08:16','66531'),(74,'02','2018-06-25 19:08:32','25407'),(75,'02','2018-11-25 18:53:14','96119'),(76,'01','2018-05-28 02:43:26','30991'),(77,'04','2018-03-18 02:03:29','81845'),(78,'02','2018-02-18 20:23:03','1057'),(79,'01','2018-11-15 16:32:26','52151'),(80,'01','2018-07-25 19:54:37','91069'),(81,'04','2018-04-18 14:02:13','51134'),(82,'02','2018-06-04 07:27:50','4609'),(83,'04','2018-07-07 20:22:38','62669'),(84,'02','2018-08-13 10:40:36','93161'),(85,'03','2018-03-29 12:51:47','75717'),(86,'04','2018-04-03 15:35:12','67327'),(87,'04','2018-02-06 15:56:58','87785'),(88,'01','2018-12-16 04:38:52','34503'),(89,'03','2018-07-25 02:52:09','87144'),(90,'04','2018-08-21 21:48:58','37756'),(91,'01','2018-07-25 20:38:00','5070'),(92,'01','2018-05-23 17:56:31','40138'),(93,'01','2018-01-10 22:47:36','35626'),(94,'01','2018-03-20 22:01:19','94446'),(95,'01','2018-05-20 23:55:32','65411'),(96,'02','2018-08-06 07:52:03','48145'),(97,'02','2018-05-26 22:38:07','17564'),(98,'01','2018-05-31 21:40:52','59694'),(99,'03','2018-03-03 16:47:55','18672'),(100,'01','2018-04-29 06:15:51','52622');

ปกติ(เรา)เขียน SQL เพื่อสรุปค่าใช้จ่ายแยกตามประเภทและรายเดือนตามตารางรูปขวา

SELECT
    CategoryName, 
    SUM(IF(M = 1, TotalAmount, 0 )) AS '1',
    SUM(IF(M = 2, TotalAmount, 0 )) AS '2',
    SUM(IF(M = 3, TotalAmount, 0 )) AS '3',
    SUM(IF(M = 4, TotalAmount, 0 )) AS '4',
    SUM(IF(M = 5, TotalAmount, 0 )) AS '5',
    SUM(IF(M = 6, TotalAmount, 0 )) AS '6',
    SUM(IF(M = 7, TotalAmount, 0 )) AS '7',
    SUM(IF(M = 8, TotalAmount, 0 )) AS '8',
    SUM(IF(M = 9, TotalAmount, 0 )) AS '9',
    SUM(IF(M = 10, TotalAmount, 0 )) AS '10',
    SUM(IF(M = 11, TotalAmount, 0 )) AS '11',
    SUM(IF(M = 12, TotalAmount, 0 )) AS '12',
    SUM(TotalAmount) AS Total
FROM (
    SELECT
        MONTH(Expense.ExpenseDate) AS M,
        MONTHNAME(Expense.ExpenseDate) AS MN,
        Expense.CategoryId, 
        ExpenseCategory.CategoryName, 
        SUM(Amount) AS TotalAmount
    FROM Expense INNER JOIN ExpenseCategory ON Expense.CategoryId = ExpenseCategory.CategoryId
    GROUP BY Expense.CategoryId, MONTH(Expense.ExpenseDate)
) T 
GROUP BY T.CategoryId 
ORDER BY T.CategoryId ASC;

ผลลัพธ์ก็จะประมาณนี้

  • แถวเป็นประเภทค่าใช้จ่าย
  • คอลัมภ์เป็นยอดรวมรายเดือน

แบบนี้บ่อย ๆ ก็เหนื่อยเหมือนกันนะ  (อาจมีคนเขียน SQL สั้นและง่ายกว่านี้แหล่ะ  ก็ช่วยแนะนำด้วยหล่ะกันครับ) มันต้องมีคนเคยเหนื่อยเหมือนเรานะ และก็มีจริงด้วย คุณ Rick James เค้าก็ทำ Function เพื่อทำ Pivot Table ไว้เหมือนกัน (Blog นี้แนะนำให้อ่านเลย)

DELIMITER //
DROP   PROCEDURE IF EXISTS PIVOTTABLE //
CREATE PROCEDURE PIVOTTABLE(
    IN tbl_name VARCHAR(99),       -- table name (or db.tbl)
    IN base_cols VARCHAR(99),      -- column(s) on the left, separated by commas
    IN pivot_col VARCHAR(64),      -- name of column to put across the top
    IN tally_col VARCHAR(64),      -- name of column to SUM up
    IN where_clause VARCHAR(99),   -- empty string or "WHERE ..."
    IN order_by VARCHAR(99)        -- empty string or "ORDER BY ..."; usually the base_cols
    )
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
    -- Find the distinct values
    -- Build the SUM()s
    SET @subq = CONCAT('SELECT DISTINCT ', pivot_col, ' AS val ',
                    ' FROM ', tbl_name, ' ', where_clause, ' ORDER BY 1');
    -- select @subq;

    SET @cc1 = "CONCAT('SUM(IF(&p = ', &v, ', &t, 0)) AS ', &v)";
    SET @cc2 = REPLACE(@cc1, '&p', pivot_col);
    SET @cc3 = REPLACE(@cc2, '&t', tally_col);
    -- select @cc2, @cc3;
    SET @qval = CONCAT("'\"', val, '\"'");
    -- select @qval;
    SET @cc4 = REPLACE(@cc3, '&v', @qval);
    -- select @cc4;

    SET SESSION group_concat_max_len = 10000;   -- just in case
    SET @stmt = CONCAT(
            'SELECT  GROUP_CONCAT(', @cc4, ' SEPARATOR ",\n")  INTO @sums',
            ' FROM ( ', @subq, ' ) AS top');
     select @stmt;
    PREPARE _sql FROM @stmt;
    EXECUTE _sql;                      -- Intermediate step: build SQL for columns
    DEALLOCATE PREPARE _sql;
    -- Construct the query and perform it
    SET @stmt2 = CONCAT(
            'SELECT ',
                base_cols, ',\n',
                @sums,
                ',\n SUM(', tally_col, ') AS Total'
            '\n FROM ', tbl_name, ' ',
            where_clause,
            ' GROUP BY ', base_cols,
            '\n WITH ROLLUP',
            '\n', order_by
        );
    select @stmt2;                    -- The statement that generates the result
    PREPARE _sql FROM @stmt2;
    EXECUTE _sql;                     -- The resulting pivot table ouput
    DEALLOCATE PREPARE _sql;
    -- For debugging / tweaking, SELECT the various @variables after CALLing.
END;
//
DELIMITER ;

วิธีเรียกใช้งานก็ง่าย ๆ เลยแบบนี้

CALL PIVOTTABLE('TEMP', 'CategoryName', 'M', 'TotalAmount', '', ' ORDER BY CategoryId');

ตาราง TEMP ก็สร้างขึ้นมาจากสรุป (ตารางฝั่งซ้ายจากรูปแรก)

DROP TABLE IF EXISTS TEMP;
CREATE TABLE TEMP (
    SELECT
        MONTH(Expense.ExpenseDate) AS M,
        MONTHNAME(Expense.ExpenseDate) AS MN,
        Expense.CategoryId AS CategoryId, 
        ExpenseCategory.CategoryName AS CategoryName, 
        SUM(Amount) AS TotalAmount
    FROM Expense INNER JOIN ExpenseCategory ON Expense.CategoryId = ExpenseCategory.CategoryId
    GROUP BY Expense.CategoryId, MONTH(Expense.ExpenseDate)
);

SELECT * FROM TEMP;

หลังจากเรียกฟังก์ชั่นเสร็จผลลัพธ์ก็เหมือนที่เราเขียนเองนั่นแหล่ะ ^_^ แต่ลดแรงงานได้อีก

CALL PIVOTTABLE('TEMP', 'CategoryName', 'M', 'TotalAmount', '', ' ORDER BY CategoryId');

ป.ล.