ใน MySQL ตั้งแต่เวอร์ชั่น 8.0 เป็นต้นไป ถ้าต้องการดึงค่าแรกและค่าสุดท้ายในกรุ๊ปนั้น ๆ ออกมา สามารถใช้ Function
- FIRST_VALUE()
FIRST_VALUE(expr) [null_treatment] over_clause
Returns the value of expr from the first row of the window frame. - LAST_VALUE()
LAST_VALUE(expr) [null_treatment] over_clause
Returns the value of expr from the last row of the window frame.
ตัวอย่างการใช้งาน
โจทย์ มีข้อมูล CKD Stage (สมมุติ) เรียงตามรายปีงบประมาณ ถ้าต้องการทราบ Stage แรกและสุดท้ายที่ตรวจพบในรายนั้น ๆ หาได้จาก
เริ่มต้นคือสร้างตารางทดสอบ
CREATE TABLE ckdstage(vn INT(11) AUTO_INCREMENT,
hn VARCHAR(50) NOT NULL,
fiscal_year INT NOT NULL,
stage INT(1) NOT NULL,
PRIMARY KEY(vn)
);
INSERT INTO ckdstage(vn,hn,fiscal_year,stage)
VALUES
(NULL,'1945',2016, 4),
(NULL,'1945',2017, 3),
(NULL,'1945',2018, 5),
(NULL,'1945',2019, 3),
(NULL,'2311',2016, 4),
(NULL,'2311',2017, 5),
(NULL,'2311',2018, 3),
(NULL,'2311',2019, 2);
ข้อมูลที่ได้จะเป็นตามนี้ ถ้ามองด้วยสายตาค่าที่เราต้องการจะตามที่ลูกศรชี้
หาผลลัพธ์ตามโจทย์
SELECT
hn,
fiscal_year,
FIRST_VALUE(stage) OVER (
PARTITION BY hn
ORDER BY fiscal_year
) first_stage,
LAST_VALUE(stage) OVER (
PARTITION BY hn
ORDER BY fiscal_year
RANGE BETWEEN
UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING
) last_stage
FROM ckdstage;
จะเห็นได้ว่าผลลัพธ์ทีได้จะดึงค่าแรกสุดและค่าสุดท้ายตามกรุ๊ปของคอลัมภ์ hn แต่ข่าวร้ายก็คือฟังก์ชั่นนี้ไม่มีให้ใช้ใน MySQL เวอร์ชั่นเก่า 55555 ซึ่งก็สามารถแก้ปัญหาโดยการจำลองฟังก์ชั่น First(), Last() ขึ้นมาใช้งานเอง ตัวอย่าง
SELECT
t1.hn,
GROUP_CONCAT(t1.stage ORDER BY t1.vn ASC) AS stagelist_asc,
GROUP_CONCAT(t1.stage ORDER BY t1.vn DESC) AS stagelist_desc,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(t1.stage ORDER BY t1.vn ASC), ',', 1) , ',' , -1) AS first_stage,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(t1.stage ORDER BY t1.vn DESC), ',', 1) , ',' , -1) AS last_stage
FROM ckdstage AS t1
GROUP BY t1.hn
จบปิ๊ง !! ^__^
ป.ล.
