Split comma separated string to multiple rows

สำหรับใครที่เคยใช้ฟังก์ชั่น GROUP_CONCAT() ใน  MySQL มาก่อนก็พอจะเดาผลลัพธ์จากฟังก์ชั่นนี้ได้ว่า ผลลัพธ์จะเป็นค่าตามคอลัมภ์ที่ถูกกรุ๊ป (GROUP BY) และนำมาต่อกันด้วยเครื่องหมายที่ระบุ ปกติค่าดีฟอลต์จะเป็น comma ‘,’ รูปแบบคำสั่งก็จะประมาณนี้

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

ตัวอย่าง


**ภาพจาก mysqltutorial

ข้างบนนี่คือต้นเหตุ มักจะมีกรณีที่เราได้ผลลัพธ์มาแล้ว นั่นคือ “A,B,C” และเราต้องการแยกข้อความที่ได้มาออกเป็นแต่ละแถว (ตาราง t ก่อนที่จะผ่านฟังก์ชั่น GROUP_CONCAT() นั่นแหล่ะ) ดูตัวอย่างกัน

CREATE TABLE DEMO (
    ID INTEGER PRIMARY KEY AUTO_INCREMENT,
    PID VARCHAR(20) NOT NULL,
    ICD10LIST VARCHAR(255) DEFAULT NULL
);
 
INSERT INTO DEMO(ID, PID, ICD10LIST) VALUES(NULL, '101', 'E119,E112,I10'), (NULL, '102', 'E119,E112'), (NULL, '103', 'E119,I10');

ข้อมูลในตารางเป็นแบบนี้ (คุ้น ๆ กันไหม 5555)

**แนวคิดของคำสั่งก็คือทำการสร้างตารางค่า Index ที่อยู่ในชุด/เซตข้อความเพื่อแยกแต่ละไอเท็มออกมา

SELECT
  DEMO.ID,
  DEMO.PID,
  SUBSTRING_INDEX(SUBSTRING_INDEX(DEMO.ICD10LIST, ',', numbers.n), ',', -1) AS ICD10
FROM
  (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) numbers INNER JOIN DEMO
  ON CHAR_LENGTH(DEMO.ICD10LIST)-CHAR_LENGTH(REPLACE(DEMO.ICD10LIST, ',', '')) >= numbers.n-1
ORDER BY
  PID, n

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

จบปิ๊ง !!!

ป.ล.

  • ตาราง numbers จะสร้างไว้ก่อนก็ได้ ปกติก็ใช้บ่อย ๆ นะครับจะด้วย RECURSIVE CTE ก็ได้หรือตัวนี้
  • ตัว Split String จะทำเป็นฟังก์ชั่นไว้ก็ได้นะ ^_^
  • ข้อมูลเป็นข้อมูลสมมุติจาก Data Exchange สมมุติแห่งหนึ่ง

 

 

ปรับปรุงข้อมูลวันหยุดราชการ (Holiday) กัน

สำหรับประชาชนคนไทยแล้วคำว่า “ในเวลาราชการ” นี่น่าจะคุ้นเคยกันเป็นอย่างดี เรามักเห็นคำนี้ติดตามหน่วยงานราชการ แล้วคำว่าในเวลาราชการนี่มันยังไง เอาแบบกำปั้นทุบดิน ก็คือวันที่ไม่ใช่วันหยุดราชการนั่นแหล่ะ 😛

แล้ววันหยุดราชการนี่เราก็เริ่มยึดตาม “ประกาศกำหนดวันหยุดราชการนักขัตฤกษ์ พระพุทธศักราช ๒๔๕๖” ซึ่งพระบาทสมเด็จพระมงกุฎเกล้าเจ้าอยู่หัว รัชกาลที่ ๖ ทรงออกประกาศดังกล่าว ถ้าใครจะศึกษารายละเอียดบล็อกนี้ก็อธิบายไว้เป็นอย่างดี

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

และอีกเว็บไซต์ก็ myhora ซึ่งมีบริการ iCal บริการ

จะเห็นได้ว่ามีไฟล์ CSV (Comma-separated values) ให้เราได้ใช้ด้วย งั้นเราก็มาปรับปรุงวันหยุดราชการด้วยไฟล์ตัวนี้กัน

  1. เริ่มต้นก็ดาวน์โหลดมาซะ
    wget -O th_holiday.csv http://www.myhora.com/calendar/ical/holiday.aspx?2561.csv

    หน้าตาข้อมูลไฟล์คร่าว ๆ ก็ประมาณนี้

  2. นำเข้าข้อมูลข้างต้นลงในฐานข้อมูลของเราด้วยคำสั่ง LOAD DATA INFILE (สร้างตารางชั่วคราว th_holiday ไว้รอรับด้วย )
    DROP TABLE IF EXISTS th_holiday;
    CREATE TABLE IF NOT EXISTS th_holiday(
        Subject       	VARCHAR(255) NULL
        ,Start_Date    	DATE NOT NULL
        ,Start_Time    	TIME NULL
        ,End_Date      	DATE NULL
        ,End_Time      	TIME NULL
        ,All_day_event 	VARCHAR(4) NOT NULL
        ,Description   	VARCHAR(255) NOT NULL
        ,Show_time_as  	INTEGER  NOT NULL
        ,Location      	VARCHAR(30)
    );
    
    TRUNCATE th_holiday;
    LOAD DATA LOCAL INFILE 'th_holiday.csv' 
    INTO TABLE th_holiday
    FIELDS TERMINATED BY ',' 
    ENCLOSED BY '"'
    ESCAPED BY '"'
    LINES TERMINATED BY '\r\n'
    (`Subject`, @start_date, `Start_Time`, `End_Date`, `End_Time`, `All_day_event`, `Description`, `Show_time_as`, `Location`)
    SET start_date = STR_TO_DATE(@start_date, '%d/%m/%Y');
  3. เป็นอันเสร็จสิ้น ทีนี้ก็นำข้อมูลนี้ไปปรับปรุงในตารางของแต่ละ HIS

 

 

การหาปีงบประมาณแบบไทยสำหรับ MySQL

ปีงบประมาณ หรือ ปีงบการเงิน (อังกฤษ: fiscal year, financial year หรือ budget year) เป็นช่วงเวลาที่ใช้สำหรับคำนวณงบการเงินประจำปีในธุรกิจและองค์กรอื่น ๆ ในหลายเขตอำนาจตามกฎหมาย กฎข้อบังคับเกี่ยวกับการบัญชีและการเก็บภาษี จะต้องอาศัยรายงานเหล่านี้อย่างน้อยครั้งหนึ่งต่อสิบสองเดือน แต่ไม่จำเป็นว่าช่วงเวลาที่รายงานนี้จะต้องตรงกับปีตามปฏิทิน ซึงปีงบประมาณของไทยเริ่มตั้งแต่เดือนตุลาคมถึงเดือนกันยายนของปีถัดไป

เราสามารถเขียนฟังก์ชั่น (Function) เพื่อคำนวณหาปีงบประมาณจากฐานข้อมูล MySQL ตามนี้

DELIMITER $$

DROP FUNCTION IF EXISTS THGOVYEAR$$
CREATE FUNCTION THGOVYEAR(
    xdate DATETIME,
    th BIT)
RETURNS INT 
DETERMINISTIC
BEGIN
    RETURN IF(MONTH(xdate) < 10, YEAR(xdate), YEAR(DATE_ADD(xdate, INTERVAL 1 YEAR))) + (543*th);
END$$

DELIMITER ;

โดยตัวแปรที่รับในฟังก์ชั่นเองจะมี 2 ตัว คือ

  • xdate : วันที่ที่ต้องการคำนวณ
  • th : ต้องการแสดงผลแบบปี พ.ศ หรือไม่ (1 = ใช่, 0 = ไม่)

ที่นี้มาลองทดสอบดู โดยการสร้างตารางเก็บวันที่กัน

DROP TABLE IF EXISTS DemoDate;
CREATE TABLE DemoDate (
    fielddate DATETIME NOT NULL
);
INSERT INTO DemoDate (fielddate) VALUES
    ('2008-09-01 00:00:00'),
    ('2008-04-04 00:00:00'),
    ('2009-05-25 00:00:00'),
    ('2010-06-30 00:00:00'),
    ('2011-07-01 00:00:00'), 
    ('2010-10-01 00:00:00'),
    ('2011-10-01 00:00:00'), 
    ('2012-09-30 00:00:00'), 
    ('2012-10-01 00:00:00'),
    ('2013-09-30 00:00:00'),
    ('2012-10-01 00:00:00'),
    ('2013-09-30 00:00:00'),
    ('2013-10-01 00:00:00'),
    ('2014-09-30 00:00:00'),
    ('2014-10-01 00:00:00'),
    ('2015-09-30 00:00:00'),
    ('2015-10-01 00:00:00'),
    ('2016-09-30 00:00:00'),
    ('2016-10-01 00:00:00'),
    (NOW()),
    ('0000-00-00 00:00:00');

แล้วก็มาทดสอบกัน

ขอให้มีความสุขกับการทำงานกัน แด่วันแรงานนนนนนน

**ช่วงนี้ไม่ค่อยได้เขียนบล็อคเลย แต่เขียน ๆ หน่อยเหอะ เดือนละเรื่องก็ยังดี T_T …
**งานช่วงนี้เหรอ ถามว่าท้อไหมตอบเลยว่ามากกกกก (แต่ไม่เคยถอยนะ หันหลังแม่มมเลย 555)

ไตรมาสใน MySQL แบบปีงบประมาณของไทย

ไตรมาส เป็นช่วงระยะเวลาสามเดือน ซึ่งแบ่งช่วงเวลา หนึ่งปี ออกเป็น สี่ ไตรมาส ในเชิงธุรกิจการพิจารณาผลประกอบการนิยมใช้ช่วงเวลาไตรมาส ในการประเมินผล กรณีที่เป็นตามปีปฏิทินจะแบ่งได้

  • ไตรมาสที่ 1 หมายถึงช่วงเดือนมกราคมถึงมีนาคม
  • ไตรมาสที่ 2 หมายถึงช่วงเดือนเมษายนถึงมิถุนายน
  • ไตรมาสที่ 3 หมายถึงช่วงเดือนกรกฎาคมถึงกันยายน
  • ไตรมาสที่ 4 หมายถึงช่วงเดือนตุลาคมถึงธันวาคม

แต่ระบบราชการของไทย ระบบรายงานส่วนใหญ่ขึ้นกับปีงบประมาณ ^_^

ปีงบประมาณ หรือ ปีงบการเงิน (อังกฤษ: fiscal year, financial year หรือ budget year) เป็นช่วงเวลาที่ใช้สำหรับคำนวณงบการเงินประจำปีในธุรกิจและองค์กรอื่น ๆ ในหลายเขตอำนาจตามกฎหมาย กฎข้อบังคับเกี่ยวกับการบัญชีและการเก็บภาษี จะต้องอาศัยรายงานเหล่านี้อย่างน้อยครั้งหนึ่งต่อสิบสองเดือน แต่ไม่จำเป็นว่าช่วงเวลาที่รายงานนี้จะต้องตรงกับปีตามปฏิทิน ซึงปีงบประมาณของไทยเริ่มตั้งแต่เดือนตุลาคมถึงเดือนกันยายนของปีถัดไป

ดังนั้นระบบไตรมาสจึงแบ่งออกเป็น

  • ไตรมาสที่ 1 หมายถึงช่วงเดือนตุลาคมถึงธันวาคม
  • ไตรมาสที่ 2 หมายถึงช่วงเดือนมกราคมถึงมีนาคม
  • ไตรมาสที่ 3 หมายถึงช่วงเดือนเมษายนถึงมิถุนายน
  • ไตรมาสที่ 4 หมายถึงช่วงเดือนกรกฎาคมถึงกันยายน

ซึ่งใน MySQL เองก็มีฟังก์ชั่นที่คำนวณหาไตรมาสชื่อ QUARTER อยู่แล้วซึ่งอิงจากปีปฏิทิน

QUARTER(date) : Returns the quarter of the year for date, in the range 1 to 4.

mysql> SELECT QUARTER('2016-10-18');
+-----------------------+
| QUARTER('2016-10-18') |
+-----------------------+
|                     4 | 
+-----------------------+

ผลอิงจากปีปฏิทินก็ตามนั้น ^_^ อ้าวว งั้นเรามาสร้างฟังก์ชั่นหาไตรมาสจากปีงบประมาณกัน

CREATE DEFINER=`root`@`localhost` FUNCTION `THQUARTER`(
    xdate DATETIME,
    th BIT) RETURNS int(11)
DETERMINISTIC
BEGIN
    DECLARE qt INT;
    
    SET qt = QUARTER(xdate) + th;
    
    RETURN IF(qt > 4, qt - 4, qt);
END

-- Test
mysql> SELECT THQUARTER('2016-10-18');
+-----------------------+
| THQUARTER('2016-10-18') |
+-----------------------+
|                     1 | 
+-----------------------+

 

มาลองเรียกใช้งานกัน สมมุติโจทย์ต้องการหารายงานการรับบริการของผู้ป่วยในสถานบริการแยกรายไตรมาส (ครั้ง)

-- Generate dummy data
CREATE TABLE `visit` (
  `visitno` mediumint(8) unsigned NOT NULL auto_increment,
  `visitdate` varchar(255),
  `pid` varchar(255) default NULL,
  `pcucode` varchar(255) default NULL,
  PRIMARY KEY (`visitno`)
) AUTO_INCREMENT=1;

INSERT INTO `visit` (`visitdate`,`pid`,`pcucode`) VALUES ("2017-01-09","5","yellow"),("2017-06-11","9","orange"),("2016-11-28","9","yellow"),("2017-06-02","2","red"),("2017-01-09","10","orange"),("2017-05-03","10","orange"),("2017-07-06","8","red"),("2017-06-25","10","yellow"),("2017-04-19","3","orange"),("2017-06-12","3","yellow");
INSERT INTO `visit` (`visitdate`,`pid`,`pcucode`) VALUES ("2016-11-12","10","orange"),("2017-06-18","5","red"),("2017-05-20","9","yellow"),("2017-04-01","2","red"),("2016-12-31","6","red"),("2017-02-05","8","orange"),("2017-02-19","3","red"),("2017-03-13","5","red"),("2017-03-27","3","yellow"),("2017-06-22","8","yellow");
INSERT INTO `visit` (`visitdate`,`pid`,`pcucode`) VALUES ("2017-01-17","6","red"),("2016-10-15","5","yellow"),("2017-05-30","8","orange"),("2017-04-29","5","red"),("2017-01-02","3","orange"),("2017-09-04","4","red"),("2017-01-09","3","orange"),("2016-12-15","7","yellow"),("2017-01-31","8","orange"),("2016-11-08","6","yellow");
INSERT INTO `visit` (`visitdate`,`pid`,`pcucode`) VALUES ("2017-01-10","5","orange"),("2016-11-20","3","yellow"),("2017-07-09","8","red"),("2017-01-20","8","red"),("2017-05-17","8","red"),("2017-08-05","5","yellow"),("2017-07-19","7","red"),("2017-05-06","10","yellow"),("2017-06-01","6","orange"),("2016-11-06","9","red");
INSERT INTO `visit` (`visitdate`,`pid`,`pcucode`) VALUES ("2017-09-20","5","orange"),("2017-01-28","8","yellow"),("2017-02-20","7","red"),("2016-11-24","5","orange"),("2016-12-27","5","orange"),("2017-05-20","6","yellow"),("2017-07-03","1","red"),("2017-09-09","8","yellow"),("2017-06-04","9","red"),("2017-03-04","9","orange");
INSERT INTO `visit` (`visitdate`,`pid`,`pcucode`) VALUES ("2017-09-12","6","orange"),("2017-07-24","1","yellow"),("2017-02-27","5","red"),("2017-08-21","3","yellow"),("2017-01-26","2","orange"),("2017-05-08","8","orange"),("2016-12-26","2","red"),("2017-06-24","7","red"),("2017-02-20","6","red"),("2017-04-18","10","yellow");
INSERT INTO `visit` (`visitdate`,`pid`,`pcucode`) VALUES ("2017-03-01","6","yellow"),("2017-07-15","1","yellow"),("2016-11-11","4","yellow"),("2017-06-27","10","orange"),("2017-01-02","10","orange"),("2017-05-08","7","orange"),("2017-03-16","2","red"),("2017-02-27","4","orange"),("2017-02-14","4","orange"),("2017-04-23","9","yellow");
INSERT INTO `visit` (`visitdate`,`pid`,`pcucode`) VALUES ("2016-12-02","1","orange"),("2017-04-15","9","yellow"),("2017-01-25","6","yellow"),("2017-05-06","7","yellow"),("2017-07-31","8","orange"),("2017-07-23","4","yellow"),("2017-06-02","7","red"),("2017-02-06","7","yellow"),("2017-02-14","6","red"),("2017-07-27","3","yellow");
INSERT INTO `visit` (`visitdate`,`pid`,`pcucode`) VALUES ("2016-10-21","1","red"),("2017-01-13","5","red"),("2017-06-06","1","red"),("2017-01-16","7","yellow"),("2017-01-29","9","orange"),("2016-11-06","7","red"),("2017-01-06","2","red"),("2017-01-12","6","yellow"),("2017-06-29","5","red"),("2017-04-04","10","yellow");
INSERT INTO `visit` (`visitdate`,`pid`,`pcucode`) VALUES ("2017-03-11","10","red"),("2017-05-22","2","red"),("2017-03-21","2","yellow"),("2017-02-19","3","orange"),("2016-11-30","3","red"),("2017-06-30","2","yellow"),("2017-09-17","2","red"),("2017-04-21","7","orange"),("2017-02-12","1","yellow"),("2017-05-12","7","red");

ผลลัพธ์

mysql> SELECT
    ->     pcucode,
    ->     SUM(IF(THQUARTER(visitdate, 1) = 1, 1, 0)) AS Q1,
    ->     SUM(IF(THQUARTER(visitdate, 1) = 2, 1, 0)) AS Q2,
    ->     SUM(IF(THQUARTER(visitdate, 1) = 3, 1, 0)) AS Q3,
    ->     SUM(IF(THQUARTER(visitdate, 1) = 4, 1, 0)) AS Q4,
    ->     COUNT(visitno) AS Total
    -> FROM visit
    -> GROUP BY pcucode
    -> WITH ROLLUP;
+---------+------+------+------+------+-------+
| pcucode | Q1   | Q2   | Q3   | Q4   | Total |
+---------+------+------+------+------+-------+
| orange  |    4 |   13 |    9 |    3 |    29 |
| red     |    6 |   12 |   12 |    6 |    36 |
| yellow  |    6 |   10 |   12 |    7 |    35 |
| NULL    |   16 |   35 |   33 |   16 |   100 |
+---------+------+------+------+------+-------+
4 rows in set (0.02 sec)

ปั่นรายงานกันต่อ T_T

 

 

MySQL (Simple)Mask Function

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

** ส่วนตัวแล้วไม่แนะนำให้ใช้ (แล้วเขียนขึ้นมาทำไมฟระ 5555) คือถ้าจะใช้ก็มีให้ใช้ได้แต่ไม่แนะนำไง เป็นภาระการประมวลผลโดยใช่เหตุ ปล่อยให้เป็นหน้าที่ของฟรอนท์ไปหล่ะกัน
DELIMITER $$
DROP FUNCTION IF EXISTS SIMPLEMASK$$

CREATE FUNCTION SIMPLEMASK (
    xvalue VARCHAR(32), 
    xformat VARCHAR(32)
)
RETURNS CHAR(32) 
DETERMINISTIC
BEGIN

    DECLARE input_len TINYINT;
    DECLARE tc CHAR;
    DECLARE idx TINYINT;
    DECLARE yformat VARCHAR(32);
    DECLARE posinsert TINYINT;
    DECLARE newstring VARCHAR(32);
    
    # Initialize variables
    SET yformat = REPLACE(xformat, '#', '');
    SET input_len = LENGTH(yformat);
    SET idx = 1;
    SET posinsert = 0;
    
    # Construct formated string
    WHILE ( idx <= input_len ) DO
        SET tc = SUBSTR(yformat, idx, 1);
        SET posinsert = LOCATE(tc, xformat, (idx + posinsert));
        SET newstring = CONCAT(tc, SUBSTR(xvalue, posinsert, 1));
        SET xvalue = INSERT(xvalue, posinsert, 1, newstring);
        SET idx = idx + 1;
    END WHILE;
    
    RETURN xvalue;
    
END $$

DELIMITER ;

-- Test
SELECT SIMPLEMASK(1234567890123,'#-####-#####-##-#'); -- CID/1-2345-67890-12-3

ป.ล.

MySQL : Select the count of values grouped by ranges

ถ้าเราเคยเห็นรายงานหรือกราฟที่นำเสนอข้อมูลในรูปแบบของช่วงข้อมูล ที่เห็นกันบ่อย ๆ ก็เช่นรายงานปิรามิดประชากรอันนี้ชัดเลย ไม่คุ้นก็ประมาณนี้ (นี่อ้างอิงข้อมูลสุขภาพของจังหวัดเราเลยนะ)

GroupByRange_01

ข้อมูลปิรามิดประชากรบอกอะไร // เพิ่มสาระ

ปิรามิดประชากร แสดงเพื่อให้เปรียบเทียบเห็นความแตกต่างของข้อมูลระหว่างทั้งสองเพศได้ชัดเจน กราฟแท่งแทนข้อมูลของเพศชายและหญิงจะวางคู่กันไว้ด้านขวาและด้านซ้ายของแกนปิรามิด สำหรับแต่ละหมวดอายุ โดยอายุน้อยที่สุดจะอยู่แท่งล่างสุด เริ่มตั้งแต่หมวดอายุ 0 – 4 ปี, 5 – 9 ปี สูงขึ้นเรื่อยๆ ด้านบนสุดคือหมวดอายุที่สุงที่สุด ซึ่งจะมีจำนวนประชากรน้อยกว่าหมวดอายุอื่นๆ ทำให้ส่วนบนเป็นยอดแหลม จึงเรียกว่าปิรามิด
รูปร่างของปิรามิดจะแสดงให้เห็นถึงผลสะสมของการเกิด การตายและการย้ายถิ่น เช่น

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

คร่าว ๆ ก็ประมาณนี้ แต่ก่อนจะได้ข้อมูลลักษณะนี้มาเราก็มักจะมีแหล่งข้อมูลดิบว่าคนนี้เป็นใคร อายุเท่าไหร่เป็นร้อยเป็นล้านเรคคอร์ด แล้วค่อยมาแยกตามกลุ่มข้อมูลอีกที ในที่นี้เราจะแยกตามกลุ่มอายุเพื่อให้ได้ข้อมูลนำไปพล็อตกราฟในลักษณะปิรามิดข้างบน

สมมุตินะครับสมมุติเราจะแบ่งกลุ่มข้อมูลออกเป็นช่วง ๆ ละ 5 ปีหล่ะกัน จากข้อมูลที่เราสร้างแบบดัมมี่ขึ้นมา (เราใช้บริการจากเว็บ generatedata.com นะ สะดวกดี)

DROP TABLE `persondemo`;

CREATE TABLE `persondemo` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `age` mediumint default NULL,
  PRIMARY KEY (`id`)
) AUTO_INCREMENT=1;

INSERT INTO `persondemo` (`age`) VALUES (11),(88),(73),(75),(8),(45),(40),(61),(62),(69),(28),(1),(84),(5),(13),(75),(76),(78),(45),(35),(23),(52),(56),(40),(91),(6),(61),(41),(52),(42),(33),(90),(4),(55),(90),(75),(97),(28),(36),(2),(51),(46),(49),(79),(87),(22),(76),(3),(9),(56),(23),(45),(56),(85),(65),(30),(31),(21),(13),(36),(88),(22),(20),(16),(73),(95),(17),(56),(29),(94),(59),(38),(31),(36),(47),(37),(33),(3),(90),(22),(41),(85),(76),(90),(54),(58),(73),(30),(12),(4),(3),(38),(78),(78),(26),(85),(78),(68),(31),(11);

วิธีแรก คำสั่ง SQL ที่มักเห็นอยู่บ่อย ๆ ในการแบ่งชุดข้อมูลก็จะเป็นการใช้ CASE ..WHEN มีกี่ช่วงก็แบ่งไป

SELECT  SUM(CASE WHEN COL1 BETWEEN 0 AND 4 THEN 1 END)
,       SUM(CASE WHEN COL1 BETWEEN 5 AND 9 THEN 1 END)
,       SUM(CASE WHEN COL1 BETWEEN 10 AND 14 THEN 1 END)
,       SUM(CASE WHEN COL1 BETWEEN 15 AND 19 THEN 1 END)
,       SUM(CASE WHEN COL1 BETWEEN 20 AND 24 THEN 1 END)
... 
... 
... 
FROM YOURTABLE 
...

อีกวิธีนึง ก็ใช้ฟังก์ชันทางคณิตศาสตร์ของ SQL เอง วันนี้จะเสนอ FLOOR() เพื่อมาคำนวณหาช่วงข้อมูล ฟังก์ชันนี้ไปอ่านเพิ่มเติมเอาหล่ะกันนะ ^_^

SELECT
    CONCAT(5 * FLOOR((age/5)), ' - ', 5 * FLOOR((age/5)) + 4) AS `range`,
    COUNT(id) AS `NumberOfPerson`
FROM `persondemo`
GROUP BY 1
ORDER BY 5 * FLOOR((age/5))

ข้อมูลที่ได้ก็ประมาณนี้

+---------+----------------+
| range   | NumberOfPerson |
+---------+----------------+
| 0 - 4   |              7 |
| 5 - 9   |              4 |
| 10 - 14 |              5 |
| 15 - 19 |              2 |
| 20 - 24 |              7 |
| 25 - 29 |              4 |
| 30 - 34 |              7 |
| 35 - 39 |              7 |
| 40 - 44 |              5 |
| 45 - 49 |              6 |
| 50 - 54 |              4 |
| 55 - 59 |              7 |
| 60 - 64 |              3 |
| 65 - 69 |              3 |
| 70 - 74 |              3 |
| 75 - 79 |             11 |
| 80 - 84 |              1 |
| 85 - 89 |              6 |
| 90 - 94 |              6 |
| 95 - 99 |              2 |
+---------+----------------+
20 rows in set (0.00 sec)

ดึกมากแล้ว นอนเหอะ นะ ^_^