มีคำถามจาก Facebook ถึงวิธีเรียกดูข้อมูลจากฐานข้อมูล ผมเห็นว่าน่าสนใจดี เลยมาลองเขียน Query ดู

โจทย์ก็คือ ต้องการเรียกดูข้อมูลราคายา จากตาราง 2 ตาราง (ตัวอย่างไม่ได้คำนึงถึง Index และ ไม่มีการเชื่อมโยงข้อมูลใดๆมาให้) ที่มีโครงสร้างและข้อมูลตามรูปที่แนบมา จากการวิเคราะห์ข้อมูล พบว่า ราคายา ใช้ราคา ล่าสุดในสต๊อค ณ.วันที่ทำรายการเบิก หรือ Last In First Out (LIFO) ซึ่งมักจะใช้กับสินค้าที่มีอายุจำกัด เช่น ยา โดยที่สินค้าที่เข้าคลังทีหลัง จะถูกจ่ายออกไปก่อนเสมอ

ตารางแรก drug_catalog เก็บข้อมูล Stock รายการยา

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');

ตารางที่สอง drug_order เก็บข้อมูลการจ่ายยา

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');

ผลลัพท์ที่ต้องการ คือ ราคายา ณ.วันที่ทำรายการเบิกยา

เรามาดู Query กัน

SELECT drug_code,hn,date_effect,price FROM (
SELECT O.drug_code,O.hn,O.date_effect,G.price,G.date_effect AS stock_date  
FROM drug_order AS O
INNER JOIN drug_catalog AS G ON O.drug_code=G.drug_code AND O.date_effect>=G.date_effect
ORDER BY G.drug_code,G.date_effect DESC
LIMIT 99999999
) AS Q
GROUP BY drug_code,hn

อธิบายหลักการ เป็นการ JOIN ตาราง drug_order และ drug_catalog เข้าด้วยกัน โดยมีเงื่อนไขที่สำคัญคือ วันที่ของตาราง drug_order ต้องมากกว่าหรือเป็นวันเดียวกันกับ วันที่ของตาราง drug_catalog เนื่องจากในความเป็นจริงราคาของยาจะต้องเป็นราคาของยาที่มีอยู่ในสต๊อคก่อนหน้าอยู่แล้ว รายการที่เพิ่มเข้ามาทีหลังจะต้องไม่ถูกรวมเข้าไปด้วย จากนั้นทำการเรียงลำดับเอาวันที่มากที่สุดขึ้น แล้วค่อยตัดให้เหลือเพียงรายการเดียวในแต่ละ Order
ข้อสังเกตุ ใน Query จะเห็นอะไรแปลกๆอยู่อย่างหนึ่งคือ คำสั่ง LIMIT 99999999 คำสั่งนี้ใช้บังคับให้ ฐานข้อมูลทำการเรียงลำดับ Query ย่อย เนื่องจาก ในฐานข้อมูลรุ่นใหม่ จะไม่สามารถเรียงลำดับใน Query ย่อยได้แล้ว โดยที่ตัวเลข 99999999 จะต้องมากกว่าจำนวนผลลัพท์สูงสุดที่เป็นไปได้เสมอ

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

1. ในเรื่องของราคาของสินค้า หากเกิดกรณี สินค้าจ่ายออกตอนเช้า แต่มีสินค้าเข้าตอนบ่ายในวันเดียวกัน ราคาสินค้าจะผิดทันที หากราคาสินค้าใหม่มีราคาไม่เท่ากับราคาสินค้าล่าสุดก่อนหน้า เนื่องจากในตอนที่เบิกยา สินค้าใหม่ยังไม่เข้ามา ทำให้เราใช้ราคาของสินค้าเก่า แต่หลังจากที่มีสินค้าใหม่เข้ามาแล้ว หากเรามา Query ซ้ำ (หรือ Query ทีหลัง) ราคาที่ได้จะเป็นราคา ณ. วันที่จ่ายยาแทน
   การแก้ไข เราต้องทำการอ่านราคายา แล้วบันทึกลงในตาราง drug_order เลยตอนที่ทำรายการเบิก จะได้ไม่ต้องเสียเวลาอ่านราคายาอีก แถมยังได้ความถูกต้องตรงกันไม่ว่าสินค้าใน Stock จะเปลี่ยนแปลงไปยังไงด้วย ซึ่งข้อมูลในตาราง drug_order จริงๆ ควรเป็นไปตามผลลัพท์ที่ต้องการอยู่แล้ว
2. ปัญหาอีกข้อของฐานข้อมูลนี้ คือมีการใช้ drug_code ซึ่งเป็นอักขระแทนคีย์ของข้อมูล ซึ่งการใช้อักขระเป็นคีย์ตัว DBMS จะทำงานได้ช้ากว่าการใช้คีย์เป็นตัวเลข (Index ก็เล็กกว่าด้วย) แถมการใช้ชื่อเป็นคีย์ หากอนาคตตัวยาเดิมแต่เปลี่ยนชื่อใหม่ ก็จะถูกมองว่าเป็นคนละรายการทันที หรือ หากเผลอทำการแก้ไขชื่อยา ข้อมูลก็จะไม่สามารถ JOIN กันได้ทันที