ใช้ MySQL หาค่าค่าสุดท้ายเมื่อตอนอดีตแต่ตอนนี้คือปัจจุบัน(ซึ่งมันเปลี่ยนไปเยอะแล้ว)

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

จากที่ดูโจทย์

  • ตาราง drug_catalog เก็บรายการยาและการเปลี่ยนแปลงราคา
  • ตาราง drug_order เก็บรายการสั่งยาของผู้ป่วยแต่ละคน
  • ผลลัพธ์ที่ต้องการคือต้องการราคายา ณ วันที่สั่ง ซึ่งวันที่สั่งคือราคาล่าสุดแต่เดี๋ยวก่อนซึ่งมันดันเป็นอดีตไปแล้วสำหรับราคายาและการสั่งยาแต่ละรายการ ปัจจุบันรายการยามีการปรับปรุงราคามาหลายรอบแล้ว
  • จากในเมนท์ได้รู้ว่า ไม่สามารถเปลี่ยนโครงสร้างให้ดีกว่าได้ ^_^ , มีการบันทึกราคาในตารางสั่งยาผิดนี่คือต้องการแก้ไขเอาราคาที่ถูก ณ เวลาที่สั่งยาเข้าไป ซึ่งก็นั่นแหล่ะเลยพอจะเดาได้ว่านี่แค่ดัมมี่ข้อมูลเพื่อเอามาถามหา Solution แก้ไขเฉย ๆ (ในความเป็นจริงแล้วคนที่อยู่ในฟิลด์โปรแกรมด้านการพยาบาลมันละเอียดกว่านี้เยอะ)
  • แต่ช่างเหอะ มาดูการหาคำตอบกัน ซึ่งแต่ละวิธีมีทั้งผิดและถูก คือถ้าถูกก็ยังอาจไม่จบคือผลลัพธ์ถูกแต่มันมีปัจจัยเรื่อง Performance อะไรอีกเยอะแยะ ข้อมูลจริงอาจมีเป็นหลายล้านเรคคอร์ดซึ่งก็ต้องไปหาสิ่งที่เหมาะสมต่อไป
  • ทำไมถึงเอาโจทย์นี้มาทำ ? ไอ้รายการยา การสั่งยา ผู้ป่วย ไรเทือกนี้คุ้นชินอยู่และไม่ช้าอาจจะเจอเคสแบบนี้ รวมทั้งการแก้โจทย์มันเป็นหนึ่งในการพัฒนาสกิลด้วย ทำบ่อย ๆ ค่อย ๆ ชิน   #ความรักก็เช่นกัน

เริ่มหาคำตอบกันโดยผลลัพธ์แบบชัด ๆ

คือขยายเฉย  ๆ มันใช่เหรอ 5555 มันต้องลองเอามาทำ Timeline ดูเพื่ออธิบายเพิ่มสิ

ลองสร้างตารางข้อมูลตามโจทย์ดู (ในนี้มีคนสร้างไว้ด้วย)

CREATE TABLE `drug_catalog` (
  `drug_code` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `price` int(11) NOT NULL,
  `date_effect` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `drug_catalog` (`drug_code`, `price`, `date_effect`) VALUES
('Albumin', 15, '2018-01-01'),
('Albumin', 20, '2018-02-15'),
('Albumin', 25, '2019-01-01'),
('Penicillin', 5, '2018-01-02'),
('Penicillin', 6, '2019-01-02'),
('Bromhexin', 11, '2018-02-01'),
('Bromhexin', 13, '2019-03-01'),
('Bromhexin', 15, '2019-04-01'),
('Bupropion', 50, '2019-02-05'),
('Bupropion', 40, '2019-03-06');


CREATE TABLE `drug_order` (
  `drug_code` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `hn` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
  `date_effect` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `drug_order` (`drug_code`, `hn`, `date_effect`) VALUES
('Albumin', '2482', '2018-02-15'),
('Albumin', '1526', '2019-01-01'),
('Penicillin', '2482', '2019-04-01'),
('Albumin', '4458', '2020-01-01'),
('Bromhexin', '8697', '2018-05-01'),
('Bupropion', '2482', '2020-01-01');

เราจะได้วิธีแรก คือการหาราคาสุดท้ายโดยเปรียบเทียบที่ date_effect ของตารางราคายา

SELECT 
    drug_code,
    dr.hn,
    (SELECT price FROM drug_catalog dc WHERE dc.drug_code=dr.drug_code AND dc.date_effect <= dr.date_effect ORDER BY dc.date_effect DESC LIMIT 1) AS 'price', dr.date_effect
FROM drug_order dr

และวิธีที่ 2 คือการหาราคาสุดท้าย ณ วันที่สั่งยาโดยใช้ความสามารถของ MySQL Version 8 เป็นต้นไปเพื่อเอาลำดับแรกสุดของราคายา โดยเปรียบเทียบที่ date_effect ของตารางราคายา

WITH drugprice AS (
    SELECT
  		dr.*,
  		dc.price,
  		ROW_NUMBER() OVER(
        PARTITION BY dc.drug_code, dr.hn ORDER BY dc.date_effect DESC
        ) AS row_num
  	FROM drug_order dr 
  		INNER JOIN drug_catalog dc ON dr.drug_code = dc.drug_code 
  		AND dc.date_effect <= dr.date_effect
)
SELECT * FROM drugprice WHERE row_num = 1;

ผลลัพธ์

ไปลองเล่นกันได้ที่

จบปิ๊ง !!!