in Developer Note

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)

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