[DB2] เรียง ID ของแถวตามเงื่อนไขด้วย Function ROW_NUMBER()

จริงๆช่วงนี้มีอะไรให้เขียน Blog ได้หลายตอนครับ แต่เวลา และปริมาณงานนี่สิไม่เคยพอ เข้าเรื่องเลยดีกว่า ตอนนี้ผมมีหน้าจอนึง ซึ่งแต่ละแถวใน Grid จะมี Column ซ่อนไว้ ชื่อ "SEQNO" เอาไว้เป็น Key คู่กับ Column "COMPANYID" เพื่อเอาไปใช้อ้างอิงในหน้าจออื่นๆที่เรียกใช้งาน ตอนแรกดูเหมือนว่า IDEA นี้น่าจะดี

แต่มันมีสิ่งที่ผิดพลาดเรื่องนึงครับ คือ Column "SEQNO" แทนที่มันจะ Auto Increment มันดันเอา Mx + 1 ครับ มันเลยเกิดปัญหาที่ว่า ถ้า User มีข้อมูลใน Grid 4 แถว Column "SEQNO" เรียง 1 ถึง 4 ตามลำดับ จากนั้นลบข้อมูลในแถวที่ 4 หละ แล้วเพิ่มข้อมูลใหม่ แถวใหม่ควรจะได้  "SEQNO" เป็น 5 แต่ระบบเดิมดันกำหนด "SEQNO" เป็น 4 มาให้แทนทำให้หน้าจออื่นที่เคยมีการอ้างอิงเอารายการที่ 4 ไปใช้ แสดงผลผิดครับ

หลังจากทราบที่มาของปัญหาแล้ว ผมเข้ามาจัดการเก็บกวาดงานของคนเก่าปรับ Code เรียบร้อยและ แต่ปัญหา คือว่า ข้อมูลเหล่านี้ลุกนำขึ้นเตรียมสำหรับ UAT และ Migrate ต่อไปครับ ซวยละที่นี้ ผมต้องปรับข้อมูลเหล่านี้ให้ถูกต้องก่อนระบบขึ้น Production ครับ โดยคำสั่งที่ผมเอามาใช้ในการจัดการข้อมูลเหล่านี้ คือ

ROW_NUMBER() มีหน้าที่ในการ Generate ตัวเลขขึ้นมา โดยสามารถกำหนดเงื่อนไขได้จากคำสั่ง OVER โดยมีรายละเอียด ดังนี้

• PARTITION BY คือ ให้เริ่มนับใหม่ โดยใหห้ดูจาก Column อะไร
• ORDER BY คือ การกำหนดให้ ROW_NUMBER() Generate ตัวเลข โดยให้เรียงตาม Column ไหนครับ

อธิบายคร่าวๆไปแล้ว ลองมาดูตัวอย่างกันดีกว่าครับ

• ตัวอย่างแรก การกำหนด ID ของ Column "SEQNO" โดยดูลำดับจาก Column "COMPANYID" ครับ

--REORDER COMPANYACCOUNT_CASH_DETAIL SEQNO
UPDATE INVEST.COMPANYACCOUNT_CASH_DETAIL AS CD
SET SEQNO = ROW_NUMBER() OVER(ORDER BY COMPANY_ID)
WHERE ACTIVEFLAG = 'A';

• ตัวอย่างที่สอง การกำหนด ID ของ Column "SEQNO" โดยให้มีการนับ 1 ใหม่ทุกครั้ง ตาม "COMPANY_ID" ครับ

--REORDER COMPANYACCOUNT_CASH_DETAIL SEQNO
UPDATE INVEST.COMPANYACCOUNT_CASH_DETAIL AS CD
SET SEQNO = ROW_NUMBER() OVER(PARTITION BY COMPANY_ID ORDER BY COMPANY_ID)
WHERE ACTIVEFLAG = 'A';

Reference

• https://www.db2tutorial.com/db2-window-functions/db2-row_number/