The Clean SQL

ถ้าจะถามภาษาที่นิยมที่สุด มาเป็นเวลาอย่างยาวนานแล้ว มันมีภาษานึงครับที่ยอดฮิตตั้งแต่อดีตจนถึงปัจจุบัน และในอนาคตอีกหลาย 10 ปีครับ ภาษานั้นก็คือ ภาษา SQL ครับ สำหรับตัว SQL หรือ Structured Query Language มีไว้ เพื่อจัดการกับข้อมูลเป็นหลักครับ แม้ว่าตัว SQL ช่วยให้เราจัดการชีวิตได้ง่ายขึ้นแล้ว อยากได้ข้อมูลเขียน

ได้ข้อมูลมาแล้วครับ ถ้าในยุคแรกๆนี้เขียน Relational Algebra เพื่อมาหาข้อมูลกันเลย

แต่ตัว SQL มันมีปัญหาในตัวเองครับ

  • กว่าจะรู้ว่าเขียน Query ไม่ใช่ตอน Complie Time นะครับ แต่เป็นตอน Run Time
  • จากข้อที่แล้ว ปัญหานี้แก้ได้โดยการใช้ ORM เข้ามาช่วยครับ แต่มันช่วยได้ระดับนึงครับ
  • เพราะ งานจริงๆ SQL มันไม่ได้สั้นๆ 2-3 บรรทัดครับ แต่มันยาว 1 หน้า A4 ครับ

SQL ที่ไม่ดีเป็นยังไง

  • อ่านยาก  SQL ขนาด 1 หน้า A4 แต่ย่อตบๆให้เหลือ 2 บรรทัด
  • การตั้งชื่อตัวแปร หรือ Alias ที่ไม่สื่อความหมายเลย

ทำยังไงใช้ SQL มัน Clean ได้หละ

  • Naming ตั้งชื่อให้มันสื่อ
    • Column นี้ เพื่ออะไร AccountCode หรือ A-Code ชื่อไหนง่ายกว่ากัน
    • Alias ไม่ต้องตั้ง A B C D ตั้งชื่อย่อ ที่เราและทีมที่ทำด้วยกัน เข้าใจครับ
  • ชื่อ Column , Table ใช้ camelCase ก็ได้ อย่าไปใช้ – มันมีปัญหากับพวก ORM บางตัว
    • Bad
    • Good
  • ชื่อ Function หรือ Store Procudure ตั้งให้มันชัดเจนไปเลย อาจจะใช้เป็น Verb มาช่วยอธิบาย
    • Bad – fn_exchangeRate()
    • Good – getExchangeRate () //อันนี้น่าเข้าใจง่ายกว่านะ
  • จัด Format บ้าง ใช้มีเยื้อง มี Tab ให้อ่านง่ายครับ
    • Bad
    • Good
  • ถ้า SQL ยาวๆ และซับซ้อนควรมี Comment บอกด้วย ว่า Join มาเพื่ออะไร
    • ถ้าใช้งานบ่อยๆ มี 10 Query ที่ต้อง Join Table Portfolio Company ประจำ เราสามารถแยกเป็น View ก็ได้นะ
  • ใน T-SQL ใช้ CTE เท่าที่จำเป็น ถ้ามี CTE ที่ใช้งานเรื่องเดียวกันบ่อย ถ้าไปแปะแยกตามแต่ละ Query มันจะ Maintain ยาก พยายามย้ายไปเป็น Function หรือ Store Procedure
  • Business บางอย่างของระบบ ถ้ามีโอกาสแก้บ่อย หรือตัวระบบเองใช้กับ DBMS ได้หลายค่าย ให้ยกไปไว้ในส่วนของ Application จัดการดีกว่าครับ ยอมเสีย Performance (ถ้าจัดการในตัว DBMS บางงานทำได้เร็วกว่า การส่งข้อมูลโยนให้ Application ครับ) แต่แลกมาด้วยการ Maintain ที่ง่าย มี Code ที่ต้องตามแก้ไขน้อยครับ

จำไว้เสมอว่าเขียน Code ไว้ เพื่อให้คนอื่นอ่าน
เพราะ มันช่วยลดเวลาในการทำความเข้าใจได้เยอะครับ
เมื่อ เราต้องกลับมาแก้ไขมันอีกครั้งครับ

Reference

[DB2] แค่เปลี่ยน Query ชีวิตก็ดีขึ้นแล้ว JOIN vs Sub Query

วันนี้พอดีมาประจำการที่ Site และได้ดูเคสบ่นๆจาก User ว่าทำไมหน้า Exchange Rate มันช้าจังเลย เปิดทีรอ 2-3 นาทีได้ ตอนแรกเราก็เฉยๆอยู่ เพราะงานหลักของเรา คือ BOT DMS (DataSet กับ DataFile 2 พี่น้อง) แต่พอสักสายๆ เข้า BA บอกว่าเปิดไม่ได้เลย เค้าลอง Interface feed ข้อมูล ย้อนหลัง 10 ปี เข้าไป น่าจะประมาน 100,000 Record ได้

หลังจากผมเข้าไปดูที่เครื่อง BA สิ่งที่ผมเข้าใจได้ คือ Query มัน Timeout ครับ รีบไปเปิด Code ทันทีเลยครับ มาดู Query กัน

จาก Query ข้างต้นนะครับ เท่าที่ดูมาใช้ LEFT JOIN ปกตินะ แต่มีอยู่ก้อนนึง ที่มีความพิเศษเป็นการ Join ที่เกิดจากการ SELECT และก้อนที่มีการ SELECT มา JOIN เนี่ย มีใช้ Sub Query ต่ออีก โดยผมขอตัดมาให้ดูนะครับ

ถ้ามองที่ Query แล้ว โอ้วมีการใช้ Sub Query ที่มีการ Join กับ Query หลักด้วย (AND MEX.COMMODITYID = ME2.COMMODITYID  AND MEX.QUOTEDID = ME2.QUOTEDID) คิดดูเล่นๆ ข้อมูล 100,000 Record มาก็เอามา Cross กับอีก 100,000 Record โอ้วมหาศาลมากครับ เพื่อที่จะหาข้อมูลที่ตรงเงื่อนไข ลองมาส่องดูจาก Execution Plan ดูครับ

Execution Plan จาก Query อันแรกครับ

คราวนี้ผมลองมาปรับ Query ครับ จากเดิมที่ Cross กับ Sub Query ตรงๆ ผมเปลี่ยนให้มีจัดกลุ่ม (Group ฺฺฺBy) ข้อมูลก่อนครับ แล้วค่อยหา Max จากข้อมูลเต็ม 100,000 Record เนี่ย มันเหลือน้อยลงครับ(น่าจะเหลือตาม Currency Pair ที่มีในระบบประมาณ 27 อันครับ) แล้วมา Join กับ Query หลักเพื่อให้ได้ผลลัพธ์ออกมา

ลองมาดู Execution Plan ของ Query ที่มีการปรับปรุงดูครับ Step น้อยลงกว่าเดิมเยอะครับ

Execution Plan จาก Query อันแรกครับ

ลองเอา Execution Plan ของทั้ง 2 แบบเทียบกันดูครับ พบว่า Step น้อยลงกว่าเดิมครับ

  • ของเก่าช่วง Sub Query ในกรอบสีแดง มี Step เยอะมาก และใช้ Index หลายจุดครับ ตรงนี้ใช้ Cost น้อยครับ แต่ตรง NLJOIN นี่ใช้ Cost เยอะมากกกกกกกครับ 10,246.17
  • ของใหม่ช่วง JOIN ในกรอบสีน้ำเงิน มี Step น้อย ใช้ Cost เยอะกว่าอันแรก แต่ตรง HSJOIN ใช้ Cost น้อยครับ เพียง 619.37 น้อยกว่าแบบเดิมมากกว่า 10 เท่าเลย
  • สำหรับตัว NLJOIN กับ HSJOIN ผมขอแตกไปเขียน Blog แยกนะครับ เขียนต่อคงยาวเกิ้น 5555

เปรียบเทียบ Execution Plan ของทั้ง 2 Query แบบหมัดต่อหมัด

 

และสุดท้าย Query ที่ทำการปรับปรุงแล้ว จากเดิมใช้เวลาประมาน 2-3 นาที เหลือประมาณ < 1 วินาทีเท่านั้นครับ

[DB2] User Define Function – SQL Scalar Example

พอดีมีรุ่นน้องให้สอนการเขียน Function บน DB2 ครับ โดย Function ที่ผมเขียนเป็นแบบ SQL Scalar Function โดยเจ้า SQL Scalar Function เป็น Function ที่คืนค่าผลลัพธ์ มีการคืนผลลัพธ์ออกมาค่าเดียวเสมอ โดยมีรูปแบบข้อมูล เช่น datetime, int, varchar เป็นต้นครับ ตัวอย่าง Scalar Function ที่มีในระบบพวก ABS() ที่ส่งตัวเลข เข้าไป คืนผลลัพธ์ที่ตัวเครื่องหมายแล้ว ออกมาค่าเดียวครับ เช่น ABS(-5) = 5

สำหรับ Function นี้ ผมขอเอาตัวอย่างจริงเลยและกัน(ขี้เกียจคิดครับ ฮ่าๆ) โดย Function นี้มีหน้าที่แปลงรอบการจ่าย COUPON FREQUENCY(รอบการจ่ายดอกเบี้ย) ของ BOND ออกมาเป็น TENOR CODE(ช่วงเวลาครับ เช่น 1 เดือน / 2 เดือน เป็นต้นครับ) เพราะในระบบเดิมมันเก็บ COUPON FREQUENCY ใน Table FIXINCOME ดังนี้ครับ

 ID ในระบบ ความหมาย
1Monthly
2Two Month
3Quarterly
4TRI-ANNUALLY
5SEMI-ANNUALLY
6ANNUALLY
7AT CALL

ซึ่งมันไม่สื่อถึงเวลาครับ โดยเจ้าข้อมูลที่สื่อถึงเวลามันอยู่ใน Table TENOR ดังนี้ครับ โดยมี Field ที่จำเป็น ดังนี้

Field Nameความหมาย
TENOR IDID ของ TENOR
TENOR CODEชื่อ TENOR เช่น 1 Week
START_NUMจำนวนของ TENOR ของเวลาเริ่มต้น โดยต้องตีความร่วมกับ START_UNIT
START_UNIT1 = Days, 2 = Weeks. 3 = Months, 4 = Years
END_NUMจำนวนของ TENOR ของเวลาสิ้นสุด โดยต้องตีความร่วมกับ END_UNIT
END_UNIT1 = Days, 2 = Weeks. 3 = Months, 4 = Years

ตัวอย่างแรก ถ้ามี TENOR ช่วง 1ปี-3 ปี มีการจัดเก็บข้อมูล ดังนี้

COLUMN NAMETENOR IDTENOR CODESTART_NUMSTART_UNITEND_NUMEND_UNIT
VALUE11-3 Years1434

ตัวอย่างสอง ถ้ามี TENOR ช่วง 1 ปี มีการจัดเก็บข้อมูล (โดยข้อมูล TENOR ที่ตรงกัน COUPON FREQUENCY จะถูก Config ไว้ในรูปแบบนี้ครับ) ดังนี้

COLUMN NAMETENOR IDTENOR CODESTART_NUMSTART_UNITEND_NUMEND_UNIT
VALUE11 Years1414

โดยแต่ละ TENOR ถ้ามีข้อมูลเรียงลำดับกันเป็นชุด เช่น 1 Month, 2 Month ผมได้จัดลงใน Table TENOR GROUP และ TENOR GROUP DETAIL (อันนี้ไม่ขอลงในรายละเอียดครับ) หลังจากที่ปูความเข้าใจด้าน Business แล้ว เรามาเข้าเรื่องการเขียน Function กันครับ

เจ้า Function ของผมที่เขียนมีลำดับการทำงานประมาณนี้ครับ

  • แปลง COUPON FREQUENCY มาเป็นหน่วยของ TENOR ก่อนครับ คือ TENOR_NUM และ TENOR_UNIT
  • เอา TENOR_NUM และ TENOR_UNIT เข้าไปหา TENOR CODE ตาม TENOR GROUP ที่เราระบุไว้ครับ

รูปแบบโครงสร้างของ Function ใน DB2 ครับ (ขอสรุปสั้น ตามนี้ครับ เพราะ มันยาวมาก ถ้าอยากศึกษาเพิ่มเติมกดดูได้เลยครับ

การ Config เพิ่มเติมสำหรับ Function ครับ

  • NOT SECURED: บอกให้ตัว DBMS เข้าใจว่า Parameter ของ Function นี้ ไม่ได้อ้างอิงถึง Column ที่ถูกกำหนดสิทธิในส่วน Column Access Control (ถ้า Column ที่ถูกกำหนดสิทธิถูกส่งเข้ามาเป็น Parameter ของ Function ตัว DBMS จะ Raise Exception เกี่ยวกับสิทธิออกมาครับ)
  • LANGUAGE SQL: บอกภาษาที่เราใช้เขียนไส้ในของ Function ครับ โดยถ้าเป็นภาษา C หรือ Java เราสามารถกำหนดให้ไปชี้ Path ที่เก็บ Library เพื่อ Run คำสั่งได้ครับ
  • INHERIT SPECIAL REGISTERS: ให้ Function ของเราเนี่ย ใช้งานตัว SPECIAL REGISTERS ของ DB2 ได้เลยครับ เช่น SESSION TIME ZONE หรือ SQLRULES เป็นต้นครับ

สำหรับพวก BEGIN ATOMIC อ่านได้จากบทความนี้ครับ

อ๋อ และก็ถ้าดูใน QUERY ที่มันต้องมี FETCH FIRST 1 ROWS ONLY เพราะ เนื่องจาก Scalar Function มันมีข้อกำหนดว่าเราต้อ Return ได้ค่าเดียวเท่านั้นครับ

ตัวอย่างของ Function ครับ

CREATE OR REPLACE FUNCTION INVEST.FINDTENOROFCOUPONFREQUENCY (
  COUPONFREQUENCY INTEGER,
  TENORGROUPCODE VARCHAR(30) )
  RETURNS VARCHAR(30)
  NOT SECURED
  LANGUAGE SQL
  INHERIT SPECIAL REGISTERS
  BEGIN ATOMIC
    DECLARE TENOR_NUM INTEGER;
    DECLARE TENOR_UNIT INTEGER;
    IF COUPONFREQUENCY = 1 THEN      --1 = Monthly
      SET TENOR_NUM = 1;
      SET TENOR_UNIT = 3;
    ELSEIF COUPONFREQUENCY = 2 THEN  --2 = Two Month
      SET TENOR_NUM = 2;
      SET TENOR_UNIT = 3;
    ELSEIF COUPONFREQUENCY = 3 THEN  --3 = Quarterly (3 เดือน)
      SET TENOR_NUM = 3;
      SET TENOR_UNIT = 3;
    ELSEIF COUPONFREQUENCY = 4 THEN  --4 = TRI-ANNUALLY (4 เดือน)
      SET TENOR_NUM = 4;
      SET TENOR_UNIT = 3;
    ELSEIF COUPONFREQUENCY = 5 THEN  --5 = SEMI-ANNUALLY (6 เดือน)
      SET TENOR_NUM = 6;
      SET TENOR_UNIT = 3;
    ELSEIF COUPONFREQUENCY = 6 THEN  --6 = ANNUALLY (1 ปี)
      SET TENOR_NUM = 12;
      SET TENOR_UNIT = 3;
    ELSEIF COUPONFREQUENCY = 7 THEN  --7 = AT CALL (ครบกำหนด)
      SET TENOR_NUM = 999;
      SET TENOR_UNIT = 4;
    END IF;
    RETURN (SELECT TENORCODE
            FROM TENORGROUP TG
            INNER JOIN TENORGROUPDETAIL TGD
              ON TG.TENORGROUPID = TGD.TENORGROUPID
                AND TG.CREATETIME = TGD.GROUPCREATETIME
            INNER JOIN TENOR T
              ON TGD.TENORTYPEID = T.TENORID
                AND TGD.TYPECREATETIME = T.CREATETIME
                AND T.ACTIVEFLAG = 'A'
            WHERE TG.TENORGROUPCODE = TENORGROUPCODE
              AND TG.ACTIVEFLAG = 'A'
              AND T.STARTNUM = TENOR_NUM
              AND T.STARTUNIT = TENOR_UNIT
              AND T.ENDNUM = TENOR_NUM
              AND T.ENDUNIT = TENOR_UNIT
            FETCH FIRST 1 ROWS ONLY);
  END;

ตัวอย่างการเรียกใช้งานครับ โดย SYSIBM.SYSDUMMY1 มัน คือ Temp ของ DB2 มองว่าเป็นกระดาษทดครับ เพราะ เวลาเรียกใช้ Function จริง เราต้องเรียกใช้คำสั่ง SQL ที่มีพวก SELECT…FROM แต่ที่นี้ ผมเอาคำสั่ง SQL จริงมาแสดงให้ดูไม่ได้ เพราะมันยาวมากกก 555 อ่านไปเดี๋ยวงงครับ เอาแบบนี้สั้นกระชับดีครับ (สรุปคือให้มองง่ายๆ ว่ามันเรียกใช้เหมือนพวกคำสั่ง SUM(), AVG(), MIN(), MAX() แหละครับ)

SELECT "INVEST"."FINDTENOROFCOUPONFREQUENCY"(1, 'BIBOR_BOND') FROM SYSIBM.SYSDUMMY1;

2015-05-24_150708
Reference

[DB2] SQL สำหรับตรวจสอบ Version ของ DB2

ช่วงนี้ไป UAT บ่อย เริ่มเจอปัญหาแปลกของ Enviroment ของ Production กับ Enviroment ที่ใช้ในการ Development ที่บริษัท แตกต่างกัน ทำให้เวลาที่เราฝั่งคำสั่งบางตัว อาจจะเกิดปัญหาขึ้นได้ครับ โดยวันนี้ผมขอแปะ Query สำหรับดู Version และ Fix Pack ของ DB Server ดังนี้ครับ

SELECT SERVICE_LEVEL
     , FIXPACK_NUM
FROM TABLE (SYSPROC.ENV_GET_INST_INFO()) AS INSTANCEINFO

โดยผลลัพธ์ที่ได้ จาก Query มี ดังนี้ครับ
2015-03-20_224623

[PowerShell] Convert Unicode Text or SQL File To ASCII

สำหรับที่มาของ Script ชุดนี้นี่ทำ Team วุ่นวายเลย เรื่องก็มีอยู่ว่าเวลาพัฒนาระบบต่างๆมันมีในส่วนของ Source Code และ SQL Script ซึ่งต้องนำไป Run ใน Enviromemt ของ Test, Dev หรือ Production ก็ตาม แต่เพราะความสะเพร่าของผมเองที่ Save File Script เป็นแบบ Unicode เกิดปัญหาขึ้นเมื่อนำไป Run บน Database Server DB2 บนเครือง AIX จะเกิด Error Message ดังนี้

เพราะ End of File ของแต่ละ Encoding ในระบบปฏิบัติการ(Windows, Linux หรือ Unix) แต่ละแบบนั้นไม่เหมือนกัน ทางแก้ คือ เปลี่ยนมาใช้ Encoding ที่เป็น ANSI เพราะเป็นมาตรฐานกลางครับ แต่งานงานนึง มันคงไม่ได้มี Script แค่อันเดียว ผมเลยทำ PowerShell Script สำหรับแปลง Encoding ของ Text File หรือ SQL File จาก Unicode ไปเป็น ANSI ครับ (จริงๆอยากเขียนเป็น Unix Shell เพราะไม่ได้ทำนานและ แต่ขึ้เกียจลง VM 55)

ต่อมาเราก็มาทดสอบกัน

ขั้นแรก: เตรียมไฟล์ที่มี Encoding Unicode ดังรูป

ข้อมูลก่อนการ Run Script
ข้อมูลก่อนการ Run Script

ขั้นที่สอง: ลองรัน Script ดังรูป

ทดสอบ Run Power Shell Script
ทดสอบ Run Power Shell Script

ขั้นสุดท้าย: ตรวจสอบผลลัพธ์จ้า ดังรูป

ข้อมูลหลังการทดสอบ
ข้อมูลหลังการทดสอบ

สุดท้ายแล้วสำหรับคนที่ทำงานบน Base ของ Windows สามารถที่จะแก้ปัญหาเล็กๆน้อยๆได้ โดยไม่ต้องคลิกแหลกแล้ว 55 สำหรับ Script อันนี้ สามารถนำไปโมต่อได้นะ เช่น Export ผลลัพธ์ออกไปเป็น Excel เป็นต้น

[SQL SERVER] Invalid character value for cast specification

วันนี้ผมแก้ App เก่าๆของที่บริษัทแล้วเกิดเจอปัญหา Invalid character value for cast specification ตอนนั้นไล่ข้อมูลแทบตาย เจอมหามหา Function Call และไปพบว่ามันผิดที่วันที่ (Date) ที่มีการจัด Format ผิด โดยไอ้เจ้าปัญหา

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

ตัวอย่างที่ผมเจอข้อมูลเกี่ยวกับพวกวันที่ Date พา Death เลยแหละ

  • ข้อมูลที่ส่งไปเก็บลง DB: 07/31/2014
  • ข้อมูลที่ควรจะเก็บใน DB: 2014-07-31

ปัญหา คือ ข้อมูลวันที่ที่เราส่งไป SQL Server ไม่รองรับ Format ข้อมูลแบบนี้ แต่รองรับข้อมูลรูปแบบ ดังนี้

  • แบบที่ 1: yyyy-MM-ddT00:00:00
  • แบบที่ 2: yyyy-MM-dd 00:00:00

ทางแก้ไข คือ พยายามจัดข้อมูลรูปแบบของข้อมูลในอยู่ในรูปแบบที่ SQL Server นั้นรองรับ ซึ่งก็คือ สองแบบข้างต้นนี้เอง ดังนั้นข้อมูลที่เราส่งไปเก็บมีรูปแบบจาก 07/31/2014 ไปเป็น 2014-07-31

หมายเหตุ วิธีการแบบนี้สามารถส่งข้อมูลปีที่เป็น ค.ศ. และ พ.ศ. ได้เลย โดยระบบจะปรับแก้วันที่ตาม Regional Setting ได้เองอัตโนมัติ แต่ต้องส่งข้อมูลชนิด Date เข้าไปเท่านั้น ห้ามส่งเป็น String เข้าไปเด็ดขาด(บางครังมีคนหัวหมอไปจัด String แล้วค่อยเอาไปเก็บ) เพราะระบบไม่มีการแก้ข้อมูลตาม Regional Setting ซึ่งส่งผลให้ App ของเราทำงานผิดภายในหลังได้

 

SELECT ซ้อน SELECT

หลายครั้งที่เราได้โจทย์ หรือปัญหามาแก้โดยใช้วิธีการ Query จากฐานข้อมูลมา ซึ่งมีรูปแบบการ Query หลายแบบ ได้แก่ การเขียน JOIN ตาราง, UNION หรือ ทำเป็น Sub Query เป็นต้น ในวันนี้ผมขอนำเสนอวิธีการเขียน SELECT IN SELECT ซึ่งเป็นวิธีการเขียน Query แบบหนึ่งที่ช่วยแก้ปัญหาได้มากมาย และลดการเขียน Coding ที่ไม่จำเป็นได้อีก

ตัวอย่างปัญหา เราต้องการดูข้อมูลการจ่ายดอกเบี้ยทั้งหมด เพื่อนำมาออกรายงาน โดยมีเงื่อนไขดังนี้

  • ข้อมูลการจ่ายดอกเบี้ยในเดือนเดือนกัน ให้นำมารวมกัน
  • ข้อมูลการจ่ายดอกเบี้ยช่วงระหว่างเดือน ให้แยกกลุ่มออกมา เช่น จ่ายดอกเบี้ยระหว่างวันที่ 2014-04-21 ถึง 2014-05-06 ระบบต้องตีความเป็นการจ่ายดอกเบี้ยระหว่างเดือน เมษายน ถึง เดือนพฤษภาคม

ข้อมูลที่ใช้กับตัวอย่างนี้

============================================================================================
TX ID |  TX TYPE | TX CLASS | TX SUBCLASS | CUSTOMER | COSTAMOUNT |  DATE FROM |  DATE TO
============================================================================================
 1001 | WITHDRAW |   CASH   |   INTEREST  |   CUS01  |  50,000.00 | 2014-04-01 | 2014-04-12
 1002 | DEPOSIT  |   CASH   |     RENT    |   CUS01  |  20,000.00 | 2014-04-01 | 2014-04-04
 1003 | WITHDRAW |   CASH   |   INTEREST  |   CUS02  |  34,200.00 | 2014-04-01 | 2014-04-08
 1004 | WITHDRAW |   CASH   |   INTEREST  |   CUS01  |  12,000.00 | 2014-04-15 | 2014-04-23
 1005 | WITHDRAW |   CASH   |   INTEREST  |   CUS02  |  50,000.00 | 2014-04-09 | 2014-04-18
 1006 | WITHDRAW |   CASH   |   INTEREST  |   CUS01  |  20,000.00 | 2014-04-15 | 2014-05-16
 1007 | WITHDRAW |   CASH   |   INTEREST  |   CUS02  |  12,300.00 | 2014-04-21 | 2014-05-16

ขั้นตอนการแก้ปัญหา (ถ้ามีการเขียน SQL ผมขอใช้ SYNTAX ของ SQL Server นะครับ)

  • Query ข้อมูลการตัดจ่ายดอกเบี้ย โดยต้องกำหนดเงื่อนไขของ DATE FROM กับ DATE TO ว่าอยู่ในเดือนเดียวกัน หรือระหว่างเดือน ซึ่งจากข้อมูลที่ให้มาผลลัพธ์ที่ได้ ควรออกมา ดังนี้
===============================================================================
TX ID |  TX TYPE | TX CLASS | TX SUBCLASS | CUSTOMER | COSTAMOUNT |MONTH RANGE
===============================================================================
 1001 | WITHDRAW |   CASH   |   INTEREST  |   CUS01  |  50,000.00 | APR
 1003 | WITHDRAW |   CASH   |   INTEREST  |   CUS02  |  34,200.00 | APR
 1004 | WITHDRAW |   CASH   |   INTEREST  |   CUS01  |  12,000.00 | APR
 1005 | WITHDRAW |   CASH   |   INTEREST  |   CUS02  |  50,000.00 | APR
 1006 | WITHDRAW |   CASH   |   INTEREST  |   CUS01  |  20,000.00 | APR - MAY
 1007 | WITHDRAW |   CASH   |   INTEREST  |   CUS02  |  12,300.00 | APR - MAY

โดยใช้ Query ที่มีการตรวจสอบเงื่อนไขของ DATE FROM กับ DATE TO ว่าเป็นเดือนเดียวกัน หรือต่างเดือนกัน ดังนี้

SELECT TXID,
       TXTYPE,
       TXCLASS,
       TXSUBCLASS,
       COSTAMOUNT,
       CASE
         WHEN MONTH(DATEFROM) = MONTH(DATETO)
              AND YEAR(DATEFROM) = YEAR(DATETO) THEN CONVERT(VARCHAR(3),
                                                     DATENAME(MONTH, DATEFROM))
         ELSE CONVERT(VARCHAR(3), DATENAME(MONTH, DATEFROM))
              + ' - '
              + CONVERT(VARCHAR(3), DATENAME(MONTH, DATETO))
       END AS MONTHRANGE
FROM   TXTEST CTX
WHERE  CTX.TXTYPE = 'WITHDRAW'
       AND CTX.TXCLASS = 'CASH'
       AND CTX.TXSUBCLASS = 'INTEREST'
  • Query ข้อมูลที่ได้จากข้อที่แล้วมา Query ซ้อนอีกครั้ง เพื่อหาผลรวมในแต่และเดือน และระหว่างเดือนมีการจ่ายดอกเบี้ยออกไปเท่าไหร่ ซึ่งจากข้อมูลในข้อที่แล้วผลลัพธ์ที่ได้ควรออกมา ดังนี้
==============================================================
  TX TYPE | TX CLASS | TX SUBCLASS | COSTAMOUNT | MONTH RANGE
==============================================================
 WITHDRAW |   CASH   |  INTEREST   | 146,200.00 | APR
 WITHDRAW |   CASH   |  INTEREST   |  32,300.00 | APR - MAY

โดยจะ SELECT ซ้อนครอบอีกขั้นนึง เพื่อที่เอาผลลัพธ์ที่ได้จาก Query ก้อนด้านในมา SUM หาผลรวมอีกที ดังนี้

SELECT TMP.TXTYPE,
       TMP.TXCLASS,
       TMP.TXSUBCLASS,
       SUM(TMP.COSTAMOUNT),
       TMP.MONTHRANGE
FROM   (SELECT TXID,
               TXTYPE,
               TXCLASS,
               TXSUBCLASS,
               COSTAMOUNT,
               CASE
                 WHEN MONTH(DATEFROM) = MONTH(DATETO)
                      AND YEAR(DATEFROM) = YEAR(DATETO) THEN CONVERT(VARCHAR(3),
                 DATENAME(MONTH, DATEFROM))
                 ELSE CONVERT(VARCHAR(3), DATENAME(MONTH, DATEFROM))
                      + ' - '
                      + CONVERT(VARCHAR(3), DATENAME(MONTH, DATETO))
               END AS MONTHRANGE
        FROM   TXTEST CTX
        WHERE  CTX.TXTYPE = 'WITHDRAW'
               AND CTX.TXCLASS = 'CASH'
               AND CTX.TXSUBCLASS = 'INTEREST') TMP
GROUP  BY TMP.TXTYPE,
          TMP.TXCLASS,
          TMP.TXSUBCLASS,
          TMP.MONTHRANGE

หมายเหตุ: โจทย์นี้อาจจะมีวิธีการอื่นๆในการเขียน Query เพื่อให้ได้ผลลัพธ์ที่เหมือนกัน แต่ที่ผมยกตัวอย่างนี้มา เพื่อที่จะสร้าง Idea และแนวทางให้การปรับประยุกต์ใช้ต่อไปนะครับ

SQL JOINS

ตอนแรกทำงานไป ทำงานมา ก็เกิดสงสัยว่า JOIN นี่มีกี่แบบ โดยมีคำถามหลายข้อเลย โดยผมขอสรุปเป็นข้อๆ ดังนี้
คำถาม: LEFT JOIN กับ LEFT OUTER JOIN ต่างกันอย่างไร
คำตอบ: ไม่ต่างกันเลย การทำงานเหมือนกัน

คำถาม: INNER JOIN กับ OUTER JOIN ต่างๆกันอย่างไร
คำตอบ: INNER JOIN  เอาข้อมูลมาที่เหมือนกันท้ังสองฝั่งมาเทรวมกัน(Intersect) กัน แต่ OUTER JOIN เป็นการเทข้อมูลทั้งฝั่งใดฝั่งหนึ่ง หรือทั้งสองฝั่งมารวมกัน(Union)

คำถาม: แล้วมีการ JOIN แบบอื่นๆ หรือไม่
คำตอบ: มี โดยผมขอเกริ่นสรุปรูปแบบการ JOIN ของ TABLE ก่อน เพื่อเป็นการท้าวความหลังก่อนนะครับ

  • INNER JOIN หรือ JOIN ดู Key ที่มีเหมือนกันทั้งสองกัน
  • OUTER JOIN ดู Key ที่มีในฝั่งใดฝั่งหนึ่งเป็นหลัก หรือ ทั้งสองฝั่ง
  • CROSS JOIN(Cartesian JOIN) ใช้เมื่อไม่มี Key ที่สามารถเขื่อมโยงตารางทั้งสองได้ ก็จะเอาข้อมูลของทั้งสองฝั่งมา JOIN กันในทุกๆ กรณีที่เป็นไปได้(คล้ายๆกับความน่าจะเป็น) ยกตัวอย่างเช่น ตาราง A มี 2 แุถว และตาราง B มี 3 แถว ผลลัพธ์ที่ได้จะออกมี 6 แุถว

ตาราง A

ตาราง B

ผลลัพธ์ที่ได้จากการ CROSS JOIN (A, B)

  • และแบบสุดท้าย SELF JOIN คือ การ JOIN ในตัวมันเอง หรือการ JOIN ในตารางเดียวกัน

หมายเหตุ

  1. การ JOIN แบบ CROSS JOIN และ SELF JOIN อาจจะไม่มีครบในทุกๆ DBMS ก็ได้นะครับ เวลานำไปใช้งานจริง อาจจะไม่หาคำสั่งพวกนี้ไม่เจอนะครับ
  2. ผมได้แนบตารางสรุปการ JOINS ว่ามีลักษณะอย่างไร ดังรูป

SQL Joins

[SQL] การบอกลำดับของ Record จากการ Select ข้อมูล

วันนี้มีเพื่อนที่ทำงานถามว่าจะเพิ่มคอลัมน์ เพื่อบอกลำดับของข้อมูลอย่างไร เพราะใน DB ก็ไม่ได้เก็บอยู่แล้ว จะเขียนใน Query หรือ มา Loop และ Count เองใน Code ดี

ข้อมูลใน DB (Input)

ผลลัพธ์ที่ต้องการ (Output)

จากที่เกริ่นทิ้งไว้ ว่ามี 2 ทางให้ทำ คือ Query ออกมาเลย หรือ เอาข้อมูลมา Loop Code เอาเอง ซึ่งในกรณีนี้ ผมขอ Query และกัน เพราะไม่เปลือง Performance ของระบบ และไม่ต้องมาเขียน Code นั่งไล่ Loop ใน App ของเราด้วย โดยผมได้ลองใน DBMS 2 ระบบ ได้แก่

  • MySQL ใช้คำสั่งดังนี้
  • Microsoft SQL Server ใช้คำสั่งดังนี้

[MSSQL] SQL กับการบวก/ลบวันที่

วันนี้ผมได้งานจากที่่ทำงานมาให้เขียน Query เพื่อแจ้งเตือนสินค้าที่หมดอายุล่วงหน้าตามวันที่กำหนดไว้ ระหว่างวันที่กำหนดจนถึงจำนวนวันที่แจ้งเตือนล่วงหน้า โดยเอาผลัลพธ์ที่ได้มาจัดโปรโมชั่นลดแลกแจกแถมกัน (สมมติว่าเป็น 7 วัน) โดยผมค่อยๆแนะนำไปทีละขั้นตอน ดังนี้

เตรียมข้อมูล

  • กำหนดโครงสร้างตาราง ดังรูป
    โครงสร้างตารางที่ใช้ในบทความนี้
  • ใส่ช้อมูลเตรียมลงไป ดังรูป
    ข้อมูลที่ใช้ในบทความนี้

(more…)