MySQL Get First value or Last value in each group

ใน 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

จบปิ๊ง !! ^__^

ป.ล.

• ไปเล่นกันได้ที่ https://www.db-fiddle.com/f/7P1LFYkebGbPpHPnG3jjJa/0
• First Value Function