in บันทึก

หาจำนวนรายใหม่โดยใช้การเปรียบเทียบระหว่างแถวใน MySQL

วันนี้บล็อกสั้น ๆ จากหัวข้อไม่งงกันใช่ไหม ^__^  อธิบายคร่าว ๆ คือกรณีเรามีตารางที่เก็บจำนวนสะสมไว้ในแต่ละวัน เวลาเราต้องการทราบว่าแล้ววันนี้มีเพิ่มเข้ามาใหม่เท่าไหร่ โดยทั่วไปทำมือก็อาจจะ

  • นับจำนวนเฉพาะรายที่มาใหม่ไง
  • หรือเอาจำนวนสะสมของวันนี้/ตอนนี้ ไปลบกับวันที่แล้ว

ใช่นั่นแหล่ะทำด้วยมือ แล้วในภาษา SQL เค้าทำยังไงกัน ให้เห็นภาพมากขึ้นเราจะเอาข้อมูลรายงานเคสโควิด 19 ของประเทศไทยในแต่ละวันมาเป็นข้อมูลดัมมี่หล่ะกัน (ข้อมูลจริงนะ นี่  ศบค. รายงานทุกวัน อยากได้ข้อมูลก็ไปที่กรมควบคุมโรค ที่นี่เลย https://covid19.ddc.moph.go.th/th/api)

*เราตัดเฉพาะยอดสะสมของแต่ละวันในเดือนพฤษภาคมหล่ะกัน เพื่อจะได้ง่ายขึ้น

CREATE TABLE `covid19timeline` (
  `ConfirmedDate` date NOT NULL,
  `Confirmed` int(11) NOT NULL,
  `Recovered` int(11) NOT NULL,
  `Hospitalized` int(11) NOT NULL,
  `Deaths` int(11) NOT NULL,
  PRIMARY KEY (`ConfirmedDate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `covid19timeline` (`ConfirmedDate`,`Confirmed`,`Recovered`,`Hospitalized`,`Deaths`) VALUES ('2020-05-01',2960,2719,187,54);
INSERT INTO `covid19timeline` (`ConfirmedDate`,`Confirmed`,`Recovered`,`Hospitalized`,`Deaths`) VALUES ('2020-05-02',2966,2732,180,54);
INSERT INTO `covid19timeline` (`ConfirmedDate`,`Confirmed`,`Recovered`,`Hospitalized`,`Deaths`) VALUES ('2020-05-03',2969,2739,176,54);
INSERT INTO `covid19timeline` (`ConfirmedDate`,`Confirmed`,`Recovered`,`Hospitalized`,`Deaths`) VALUES ('2020-05-04',2987,2740,193,54);
INSERT INTO `covid19timeline` (`ConfirmedDate`,`Confirmed`,`Recovered`,`Hospitalized`,`Deaths`) VALUES ('2020-05-05',2988,2747,187,54);
INSERT INTO `covid19timeline` (`ConfirmedDate`,`Confirmed`,`Recovered`,`Hospitalized`,`Deaths`) VALUES ('2020-05-06',2989,2761,173,55);
INSERT INTO `covid19timeline` (`ConfirmedDate`,`Confirmed`,`Recovered`,`Hospitalized`,`Deaths`) VALUES ('2020-05-07',2992,2772,165,55);
INSERT INTO `covid19timeline` (`ConfirmedDate`,`Confirmed`,`Recovered`,`Hospitalized`,`Deaths`) VALUES ('2020-05-08',3000,2784,161,55);
INSERT INTO `covid19timeline` (`ConfirmedDate`,`Confirmed`,`Recovered`,`Hospitalized`,`Deaths`) VALUES ('2020-05-09',3004,2787,161,56);
INSERT INTO `covid19timeline` (`ConfirmedDate`,`Confirmed`,`Recovered`,`Hospitalized`,`Deaths`) VALUES ('2020-05-10',3009,2794,159,56);
INSERT INTO `covid19timeline` (`ConfirmedDate`,`Confirmed`,`Recovered`,`Hospitalized`,`Deaths`) VALUES ('2020-05-11',3015,2796,163,56);
INSERT INTO `covid19timeline` (`ConfirmedDate`,`Confirmed`,`Recovered`,`Hospitalized`,`Deaths`) VALUES ('2020-05-12',3017,2798,163,56);
INSERT INTO `covid19timeline` (`ConfirmedDate`,`Confirmed`,`Recovered`,`Hospitalized`,`Deaths`) VALUES ('2020-05-13',3017,2844,117,56);
INSERT INTO `covid19timeline` (`ConfirmedDate`,`Confirmed`,`Recovered`,`Hospitalized`,`Deaths`) VALUES ('2020-05-14',3018,2850,112,56);
INSERT INTO `covid19timeline` (`ConfirmedDate`,`Confirmed`,`Recovered`,`Hospitalized`,`Deaths`) VALUES ('2020-05-15',3025,2854,115,56);
INSERT INTO `covid19timeline` (`ConfirmedDate`,`Confirmed`,`Recovered`,`Hospitalized`,`Deaths`) VALUES ('2020-05-16',3025,2855,114,56);
INSERT INTO `covid19timeline` (`ConfirmedDate`,`Confirmed`,`Recovered`,`Hospitalized`,`Deaths`) VALUES ('2020-05-17',3028,2856,116,56);
INSERT INTO `covid19timeline` (`ConfirmedDate`,`Confirmed`,`Recovered`,`Hospitalized`,`Deaths`) VALUES ('2020-05-18',3031,2857,118,56);

ข้อมูลก็ตามนี้

ทีนี้เราก็หาจำนวนรายใหม่กันว่ามีจำนวนเท่าไหร่โดยการเปรียบเทียบกับวันที่แล้ว

SELECT 
	c1.*,
    COALESCE(c1.Confirmed - c2.Confirmed, 0) AS NewConfirmed
FROM covid19timeline c1 
	LEFT JOIN covid19timeline c2 ON c2.ConfirmedDate = DATE_SUB(c1.ConfirmedDate, INTERVAL 1 DAY)
ORDER BY c1.ConfirmedDate;

ผลลัพธ์ที่ได้ตามนี้ โดย NewConfirmed คือจำนวนรายใหม่ที่เพิ่มเข้ามา

จบปิ๊ง !! ^__^

ป.ล.

  • แล้วเราจะเอามาใช้ตอนไหน โดยปกติส่วนใหญ่ก็มักอยู่ในรายงานที่ต้องการดูความเปลี่ยนแปลงเป็นหลักแต่ข้อมูลที่เรามีมักเป็นข้อมูลสะสมอย่างเดียว อย่างเคสการระบาดของโควิด 19 เป็นต้น (แต่ api เค้ามีแจ้งรายใหม่นะตัดออกเฉย ๆ)
  • ไปลองเล่นกันได้ที่ https://www.db-fiddle.com/f/n3Zbf8FmThZxPGDgSGhmVN/0