Author: benjawan.n

  • วิธีการคำนวณหาอายุด้วย Oracle Datetime Functions

    benjawan.n

    อายุเป็นเพียงตัวเลข มีคนพูดไว้แบบนี้ แล้วถ้าเราอยากจะรู้ล่ะว่าไอ้ตัวเลข จำนวนปี จำนวนเดือน และจำนวนวัน ของอายุเรามันเป็นตัวเลขอะไร ถ้าไม่อะไรมากใช้เครื่องคิดเลข หรือนับเอาก็ได้อยู่ แต่ถ้าต้องคำนวณของทุกคนในองค์กรล่ะจะนับเอง หรือเครื่องคิดเลขก็คงไม่ไหวแล้ว ดังนั้นวันนี้จึงจะขอแนะนำ Oracle Datetime Functions ที่สามารถนำมาประยุกต์ใช้ เพื่อคำนวณหาอายุของเรากันค่ะ

    Oracle Datetime Functions ที่จะแนะนำในวันนี้คือ ฟังก์ชัน TRUNC (date), ADD_MONTHS และฟังก์ชัน MONTHS_BETWEEN โดยมีรายละเอียดของฟังก์ชันดังนี้

    ฟังก์ชันคำอธิบาย
    ADD_MONTHSเพิ่มจำนวนเดือนลงในวันที่ที่ระบุ
    MONTHS_BETWEEN หาจำนวนเดือนระหว่าง 2 วันที่ที่ระบุ
    TRUNC (date)คืนค่าวันที่พร้อมส่วนเวลาของวันที่ตัดให้เป็นหน่วยที่ระบุ

    ตัวอย่างการใช้งานฟังก์ชัน ADD_MONTHS
    Syntax :
    ADD_MONTHS(date, integer)

    รูปแสดงตัวอย่างการเขียน query และผลลัพธ์ที่ได้ของฟังก์ชัน ADD_MONTHS

    ตัวอย่างการใช้งานฟังก์ชัน MONTHS_BETWEEN
    Syntax :
    MONTHS_BETWEEN(date1, date2)

    รูปแสดงตัวอย่างการเขียน query และผลลัพธ์ที่ได้ของฟังก์ชัน MONTHS_BETWEEN

    ตัวอย่างการใช้งานฟังก์ชัน TRUNC (date)
    Syntax :
    TRUNC ( date [, format ] )

    โดยที่ format ไม่ต้องระบุก็ได้ กรณีไม่ระบุวันที่จะถูกตัดเป็นวันที่ใกล้ที่สุด สามารถแสดงตัวอย่างของ format ได้ดังต่อไปนี้

    รูปแบบ Formatหน่วยปัดเศษ
    SYYYY, YYYY, YEAR. SYEAR, YYY, YY, Y ปี
    MONTH, MON, MM, RM เดือน
    DDD, DD, J วัน
    DAY, DY, D วันเริ่มต้นของสัปดาห์
    HH, HH12, HH24 ชั่วโมง
    MIนาที

    รูปแสดงตัวอย่างการเขียน query และผลลัพธ์ที่ได้ของฟังก์ชัน TRUNC (date)

    จากตัวอย่าง query การใช้งานทั้ง 3 ฟังก์ชันข้างต้น เราสามารถประยุกต์ใช้เพื่อคำนวณหาจำนวนปี จำนวนเดือน และจำนวนวันของอายุได้ดังนี้

    รูปแสดงตัวอย่าง query การหาจำนวนปี จำนวนเดือน และจำนวนวันของอายุ

    คิดว่านอกเหนือจากการคำนวณหาอายุแล้วก็ยังไปประยุกต์ใช้สำหรับการหาช่วงเวลาอื่นๆ ได้อีกเช่น ระยะเวลาในการปฏิบัติงานเป็นต้น

  • SQL Expression สำหรับหาช่วงเวลาที่ต้องการ

    benjawan.n

    ใครที่เคยสร้าง Materialized View จะทราบว่าต้องมีการกำหนดช่วงเวลาเพื่อให้ Materialized View นั้นทำการ Refresh ข้อมูลเพื่อให้ได้ข้อมูลที่เป็นปัจจุบัน หรือแม้แต่ Job ก็เช่นกันต้องกำหนดช่วงเวลาเพื่อให้ Job ทำงานตามช่วงเวลาที่กำหนด บางคนอาจจะเจอปัญหาว่าไม่รู้ว่าต้องกำหนดอย่างไร เช่น อยากให้ทำงานทุก 8 โมงเช้า หรืออยากให้ทำงานทุกเที่ยงคืน เป็นต้น เพื่อให้ปัญหานั้นหมดไป เรามาทำความเข้าใจเกี่ยวกับช่วงเวลาบน Oracle กันดีกว่า

    จากที่เราทราบกันว่า 1 วันมี 24 ชั่วโมง 1 ชั่วโมงมี 60 นาที และ 1 นาทีมี 60 วินาที บน Oracle เราสามารถเขียนเป็น Expression ได้ตามตัวอย่างข้างล่างนี้

    ช่วงเวลา : PeriodExpression 1Expression 2
    1 Day11
    1 Hour1/241/24
    1 Minute1/(24*60)1/1440
    1 Second1/(25*60*60)1/86400

    ดังนั้นจาก Expression ข้างต้นเราก็สามารถเขียนช่วงเวลา ด้วย SQL Expression ดังตัวอย่างต่อไปนี้

    ช่วงเวลาที่ต้องการSQL Expression
    Nowsysdate
    Tomorrowsysdate+1
    Yesterdaysysdate-1
    One hour from nowsysdate + 1/24
    Ten minutes from nowsysdate + 10/1440
    Thirty seconds from nowsysdate + 30/86400
    Tomorrow at 12 Midnighttrunc(sysdate+1)
    Tomorrow at 8 AMtrunc(sysdate+1) + 8/24
    Yesterday at 10 AMtrunc(sysdate-1) + 10/24
    Next Monday at 12 Noonnext_day(trunc(sysdate),’MONDAY’) + 12/24

    คราวนี้เราลองมาเขียนคำสั่งในการ select ข้อมูลตามช่วงเวลาที่ต้องการกันดีกว่าค่ะ

    ตัวอย่างที่ 1 ต้องการ query ข้อมูลเพื่อหาข้อมูลช่วงเวลาดังนี้ วันเวลาปัจจุบัน, หนึ่งชั่วโมงถัดไปจากปัจจุบัน, พรุ่งนี้ตอนเที่ยงคืน และพรุ่งนี้ตอน 8 โมงเช้า สามารถเขียน query เพื่อให้ได้ช่วงเวลาที่ต้องการได้ดังนี้ค่ะ

    ผลลัพธ์ที่ได้จาก query ตัวอย่างที่ 1

    ตัวอย่างที่ 2 ต้องการ query ข้อมูลเพื่อหาข้อมูลช่วงเวลาดังนี้ วันเวลาปัจจุบัน, สิบนาทีถัดไปจากปัจจุบัน, 30 วินาทีถัดไปจากปัจจุบัน และเวลาเที่ยงของวันศุกร์ถัดไป สามารถเขียน query เพื่อให้ได้ช่วงเวลาที่ต้องการได้ดังนี้ค่ะ

    ผลลัพธ์ที่ได้จาก query ตัวอย่างที่ 2

    จาก 2 ตัวอย่างข้างต้นคิดว่าก็พอจะเป็นแนวทางให้กับทุกท่านได้สามารถกำหนดช่วงเวลาที่ต้องการได้ไม่มากก็น้อย

  • Transpose rows into columns using the Oracle PIVOT operator

    benjawan.n

    ก่อนหน้านี้เราเคยนำเสนอบทความวิธีการทรานสโพส (หมุน) ข้อมูลจากแถวเป็นคอลัมน์ ด้วยการใช้ฟังก์ชัน SUM และ DECODE กันมาแล้ววันนี้ลองมาใช้อีกวิธีในการแสดงผลดังกล่าวด้วย PIVOT ก่อนอื่นมาดูในส่วน syntax ของ PIVOT กันก่อน

     

    PIVOT Syntax

    SELECT * FROM
(
  SELECT column1, column2
  FROM tables
  WHERE conditions
)
PIVOT 
(
  aggregate_function(column2)
  FOR column2
  IN ( expr1, expr2, ... expr_n) | subquery
)
ORDER BY expression [ ASC | DESC ];

    โดยที่

    aggregate_function คือ aggregate function เช่น SUM, COUNT, MIN, MAX, หรือ AVG

    IN ( expr1, expr2, … expr_n ) คือ รายการค่าของ column2 ที่ต้องการจะแสดงในส่วนคอลัมภ์

     

    คราวนี้ลองมาดูตัวอย่างกัน 

    สมมติเรามีข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่ซึ่งประกอบด้วย 6 ฟิลด์ข้อมูลแสดงดังตัวอย่างด้านล่าง
    ข้อมูล: ตาราง TEST_NEW_STUDENT เป็นตัวอย่างข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่จำนวน 16 รายการ

    โจทย์ เราต้องการจะแสดงจำนวนของนักศึกษาแยกตามคณะที่สังกัด โดยให้แสดงข้อมูลอยู่ในรูปแบบคอลัมภ์

    ดังนั้นจึงเราสามารถเขียนคิวรีโดยใช้ PIVOT เพื่อแก้ปัญหาข้างต้นได้ดังนี้

    SELECT *
    FROM (SELECT ‘TotalStudent’ fac_summary, fac_id FROM test_new_student)
    PIVOT
    (COUNT (fac_id)
    FOR fac_id
    IN (’06’ Engineering, ’08’ Science, ’12’ Natural_Resources))

     

    ผลลัพธ์ที่ได้ : แสดงข้อมูลจำนวนนักศึกษาซึ่งประกอบด้วย จำนวนนักศึกษาคณะวิศวกรรมศาสตร์ คณะวิทยาศาสตร์ และคณะทรัพยากรธรรมชาติ ตามลำดับ โดยแสดงข้อมูลในรูปแบบคอลัมภ์

  • Oracle : Transpose rows into columns using SUM and DECODE

    benjawan.n

    วันนี้เราจะนำเสนอวิธีการทรานสโพส (หมุน) ข้อมูลจากแถวเป็นคอลัมน์ โดยการประยุกต์ใช้ฟังก์ชัน SUM และ ฟังก์ชัน DECODE ก่อนอื่นจะขออธิบายในส่วนของฟังก์ชัน SUM และ DECODE กันก่อน

    ฟังก์ชัน SUM

    SUMจัดเป็นฟังก์ชันประเภท Aggregate Function ทำหน้าที่ในการคำนวณผลรวมของค่าในคอลัมภ์ มีรูปแบบการใช้งานดังตัวอย่างข้างล่างนี้

    SELECT SUM(aggregate_expression)
FROM tables
[WHERE conditions];

    โดยที่

    aggregate_expression คือ คอลัมภ์ที่ต้องการค่าผลรวม โดยที่ข้อมูลในคอลัมภ์จะต้องเป็นชนิดตัวเลขเท่านั้น

    มาดูตัวอย่างการใช้งานกัน

    SELECT fac_id, SUM (eng_score) sum_eng_score
    FROM test_new_student
    GROUP BY fac_id;

    ผลลัพธ์ที่ได้ : แสดงผลรวมของคะแนนภาษาอังกฤษของแต่ละคณะ

    ฟังก์ชัน DECODE

    DECODE เป็นการเขียนเงื่อนไขบนชุดคำสั่ง select  โดยมีลักษณะการทำงานเหมือน IF-THEN-ELSE โดยถ้าเข้าเงื่อนไขที่กำหนด ต้องการให้ทำอะไรก็ให้ระบุลงไป

    Syntax:

    DECODE( expression , search , result [, search , result]... [, default] )

    โดยที่

    expression คือ ค่าที่จะเอามาใช้เปรียบเทียบเงื่อนไข
    compare_value คือ ค่าที่สามารถจะมาเปรียบเทียบได้กับ expression
    return_value คือค่าที่จะแสดงออกมาเมื่อ ค่าของ expression = compare_value
    default_return_value คือค่าที่จะแสดง เมื่อ expression <> compare_value

     

    ลองมาดูตัวอย่างการใช้งานกัน 

    สมมติเรามีข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่ซึ่งประกอบด้วย 6 ฟิลด์ข้อมูลแสดงดังตัวอย่างด้านล่าง
    ข้อมูล: ตาราง TEST_NEW_STUDENT เป็นตัวอย่างข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่จำนวน 16 รายการ

     

    ตัวอย่างคิวรีในการใช้ฟังก์ชัน DECODE

    SELECT fac_id,
    DECODE (
    fac_id,
    ’06’, ‘คณะวิศวกรรมศาสตร์’,
    ’08’, ‘คณะวิทยาศาสตร์’,
    ’12’, ‘คณะทรัพยากรธรรมชาติ’,’null’)
    fac_name,stud_name_thai,stud_sname_thai,sn_code,eng_score
    FROM test_new_student;

    ผลลัพธ์ที่ได้

    โดยความหมายของด้านบนคือ

    ถ้าค่า fac_id ของตาราง test_new_student เป็น ’06’ ก็ให้แสดง คำว่า ‘คณะวิศวกรรมศาสตร์’

    ถ้าค่า fac_id ของตาราง test_new_student เป็น ’08’ ก็ให้แสดง คำว่า ‘คณะคณะวิทยาศาสตร์’

    ถ้าค่า fac_id ของตาราง test_new_student เป็น ’12’ ก็ให้แสดง คำว่า ‘คณะทรัพยากรธรรมชาติ’

     

    โดยปกติถ้าเราเขียนคิวรีเพื่อนับจำนวนนักศึกษาโดยแยกตามคณะที่สังกัดของนักศึกษา เราสามารถเขียนคิวรีได้ดังนี้

    SELECT fac_id, COUNT (*) fac_summary
    FROM test_new_student
    GROUP BY fac_id;

    ผลลัพธ์ที่ได้ : แสดงจำนวนนักศึกษาแยกตามคณะที่สังกัด การแสดงผลปกติจะแสดงในรูปแบบแถว

     

    คราวนี้ลองมาตั้งโจทย์ใหม่โดยยังมีความต้องการเหมือนเดิมคือ นับจำนวนนักศึกษาโดยแยกตามคณะที่สังกัดของนักศึกษา แต่เปลี่ยนจากการแสดงในรูปแบบแถวให้แสดงในรูปแบบคอลัมภ์แทน

    จากโจทย์เราสามารถประยุกต์ใช้งานฟังก์ชัน sum และ decode เพื่อจะแปลงข้อมูลในรูปแบบแถวให้อยู่ในรูปแบบ
    คอลัมภ์ได้ โดยเราสามารถเขียนคิวรีได้ดังนี้

    SELECT ‘TotalStudent’ faculty,
    SUM (DECODE (fac_id, ’06’, 1)) Engineering,
    SUM (DECODE (fac_id, ’08’, 1)) Science,
    SUM (DECODE (fac_id, ’12’, 1)) Natural_Resources
    FROM test_new_student

    ผลลัพธ์ที่ได้ : แสดงข้อมูลจำนวนนักศึกษาซึ่งประกอบด้วย จำนวนนักศึกษาคณะวิศวกรรมศาสตร์ คณะวิทยาศาสตร์ และคณะทรัพยากรธรรมชาติ ตามลำดับ โดยแสดงข้อมูลในรูปแบบคอลัมภ์

  • Oracle / PLSQL: LISTAGG Function

    benjawan.n

    LISTAGG เป็นฟังก์ชันการรวมสตริงของ Oracle ที่นำค่าข้อมูลในคอลัมภ์ที่ระบุมาเรียงต่อกัน และดำเนินการจัดเรียงลำดับของข้อมูลที่นำมาต่อกันตามคอลัมภ์ใน order_by_clause ซึ่งฟังก์ชัน LISTAGG สามารถแสดงผลได้หลายรูปแบบดังนี้

    • Single-set aggregate function : LISTAGG เป็นฟังก์ชันที่ดำเนินการกับข้อมูลแล้วคืนค่ากลับมาเพียงเร็คคอร์ดเดียว
    • Group-set aggregate function : LISTAGG เป็นฟังก์ชันที่ดำเนินการกับข้อมูลและคืนค่ากลับมาหลายเร็คคอร์ดตามกลุ่มที่กำหนดในเงื่อนไข GROUP BY 
    • Analytic function : LISTAGG เป็นฟังก์ชันที่ดำเนินการจัดแยกผลการค้นหาออกเป็นกลุ่มตามเงื่อนไขที่กำหนดใน query_partition_clause

    Syntax

    LISTAGG (measure_column [, 'delimiter'])
  WITHIN GROUP (order_by_clause) [OVER (query_partition_clause)]

    โดยที่ 

    • measure_column คือ คอลัมภ์ที่ต้องการนำค่าข้อมูลมาเรียงต่อกัน โดยจะดำเนินเฉพาะค่าที่ไม่เป็น null เท่านั้น
    • delimiter คือ ตัวเลือกที่ให้สามารถระบุตัวคั่นระหว่างค่าข้อมูลที่จะนำมาเรียงต่อกัน
    • order_by_clause คือ ค่าที่นำมาเรียงต่อกันจะเรียงตามค่าในคอลัมภ์ที่กำหนดใน order_by_clause

     

    ตัวอย่างการใช้งาน 

    สมมติเรามีข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่ซึ่งประกอบด้วย 6 ฟิลด์ข้อมูลแสดงดังตัวอย่างด้านล่าง
    ข้อมูล: ตาราง TEST_NEW_STUDENT เป็นตัวอย่างข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่จำนวน 16 รายการ

    Single-set aggregate function

    ตัวอย่างในส่วนนี้จะแสดงชื่อ-นามสกุลนักศึกษาทุกคนที่อยู่ในคณะ ’06’ โดยจัดเรียงลำดับข้อมูลตามชื่อนักศึกษา(stud_name_thai)

    SELECT LISTAGG (stud_name_thai || ‘ ‘ || stud_sname_thai, ‘,’)
    WITHIN GROUP (ORDER BY stud_name_thai) “NAME LIST”
    FROM test_new_student
    WHERE fac_id = ’06’;

    ผลลัพธ์ที่ได้ : จะคืนค่ากลับมาเพียงเร็คคอร์ดเดียวตามเงือนไขที่ระบุ

    Group-set aggregate function 

    ตัวอย่างในส่วนนี้จะแสดงชื่อ-นามสกุลนักศึกษาทุกคนแยกตามคณะที่สังกัด โดยจัดเรียงลำดับข้อมูลตามชื่อนักศึกษา(stud_name_thai)

    SELECT fac_id,LISTAGG (stud_name_thai || ‘ ‘ || stud_sname_thai, ‘,’)
    WITHIN GROUP (ORDER BY stud_name_thai) “NAME LIST”
    FROM test_new_student
    GROUP BY fac_id
    ORDER BY fac_id;

    ผลลัพธ์ที่ได้ : จะคืนค่ากลับมาหลายเร็คคอร์ดตามเงื่อนไข GROUP BY

    Analytic function

    ตัวอย่างในส่วนนี้จะแสดงข้อมูลเฉพาะคนที่ได้คะแนนมากกว่า 60 และจัดแยกผลการค้นหาตามคณะที่สังกัด

    SELECT fac_id,stud_name_thai||’ ‘||stud_sname_thai student_name,eng_score,
    LISTAGG (stud_name_thai||’ ‘||stud_sname_thai, ‘,’)
    WITHIN GROUP (ORDER BY eng_score, stud_name_thai)
    OVER (PARTITION BY fac_id) AS “NAME_LIST”
    FROM test_new_student
    WHERE eng_score > 60
    ORDER BY fac_id, eng_score

    ผลลัพธ์ที่ได้ : ดำเนินการจัดแยกผลการค้นหาออกเป็นกลุ่มตามเงื่อนไขที่กำหนดใน PARTITION BY 

  • Oracle : ROLLUP Extension to GROUP BY

    benjawan.n

    การจัดกลุ่มข้อมูลด้วย GROUP BY

    เมื่อต้องการจัดกลุ่มข้อมูล เราสามารถใช้ประโยค GROUP BY เพื่อทำการแบ่งออกเป็นรายการย่อย ๆ การคิวรีที่รวมประโยค GROUP BY จะเรียกว่าการคิวรีแบบกลุ่ม เพราะว่ามันจะรวมกลุ่มข้อมูลจากคำสั่ง SELECT แล้วสร้างเป็นเร็คคอร์ดสรุปเพียงเร็คคอร์ดเดียวให้กับแต่ละกลุ่ม 

    ส่วนขยาย ROLLUP 

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

    ROLLUP Syntax

    SELECT…GROUP BY ROLLUP(grouping_column_reference_list)

    ตัวอย่างการใช้งาน

    สมมติเรามีข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่ซึ่งประกอบด้วย 6 ฟิลด์ข้อมูลแสดงดังตัวอย่างด้านล่าง
    ข้อมูล: ตาราง TEST_NEW_STUDENT เป็นตัวอย่างข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่จำนวน 16 รายการ

    โจทย์ เราต้องการนับจำนวนนักศึกษาใหม่แยกตามคณะที่นักศึกษาสังกัด และรหัส สน.ที่สอบได้ เราสามารถใช้ประโยค GROUP BY เพื่อแก้ปัญหาข้อนี้ ดังตัวอย่างคิวรีต่อไปนี้

    SELECT fac_id, sn_code, COUNT (*) NUM_STUDENT
    FROM test_new_student
    GROUP BY fac_id, sn_code
    ORDER BY fac_id, sn_code;

    ผลลัพธ์ที่ได้ : แสดงจำนวนนักศึกษาแยกตามคณะที่สังกัด และรหัส สน.ที่สอบได้ แสดงเรียงตามรหัสคณะ และรหัส สน.ที่สอบได้

    โจทย์ หากเราต้องการนับจำนวนนักศึกษาใหม่แยกตามคณะที่นักศึกษาสังกัด และรหัส สน.ที่สอบได้ พร้อมทั้งแสดงผลรวมของนักศึกษาแต่ละคณะ และหาผลรวมของนักศึกษาทั้งหมดด้วย

    เราสามารถใช้ส่วนขยายที่เรียกว่า ROLLUP เพื่อแก้ปัญหาข้อนี้ ดังตัวอย่างคิวรีต่อไปนี้

    SELECT fac_id, sn_code, COUNT (*) NUM_STUDENT
    FROM test_new_student
    GROUP BY ROLLUP (fac_id, sn_code)
    ORDER BY fac_id, sn_code;

    ผลลัพธ์ที่ได้ : แสดงจำนวนนักศึกษาแยกตามคณะที่สังกัด และรหัส สน.ที่สอบได้ แสดงเรียงตามรหัสคณะ และผลรวมของนักศึกษาแต่ละคณะ และผลรวมนักศึกษาทั้งหมด

    ลองใช้งานกันดูนะคะ  สำหรับส่วนขยาย ROLLUP เพื่อหาสรุปผลรวมที่มาจากผลลัพธ์ทั้งหมดในตอนท้ายของการคิวรี

  • Oracle: retrieve top n records for each group

    benjawan.n

    วิธีการเขียน Query เพื่อดึงข้อมูลข้อมูลสูงสุดหรือต่ำสุด N ลำดับแรกของแต่ละกลุ่มออกมาจากตาราง
    สมมติว่าเรามีข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่ซึ่งประกอบด้วย 5 ฟิลด์ข้อมูลดังตัวอย่างข้างล่าง
    ข้อมูล: ตาราง TEST_NEW_STUDENT เป็นตัวอย่างข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่จำนวน 773 รายการ

    โจทย์: ต้องการดึงข้อมูลนักศึกษาที่ได้คะแนนภาษาอังกฤษสูงสุดแยกตามคณะจากข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่จำนวน 773 รายการนี้

    เริ่มต้นด้วย query ดังนี้

    SELECT a.*,
    ROW_NUMBER ()
    OVER (PARTITION BY fac_id ORDER BY eng_score DESC)
    AS val_row_number
    FROM test_new_student a

    จุดสำคัญของ query ข้างต้นก็คือฟังก์ชัน ROW_NUMBER ซึ่งเป็นฟังก์ชันที่จะให้เลขบรรทัดของผลลัพธ์ออกมาตามการจัดกลุ่มข้อมูลหรือการเรียงลำดับที่เรากำหนดไว้ด้วยคำสั่ง OVER, PARTITION BY และ ORDER BY ที่ตามมา

    จากตัวอย่างข้างต้นหมายความว่า เรากำลัง Select * จากตาราง TEST_NEW_STUDENT พร้อมกับดึงเลข ROW_NUMBER ออกมา โดยเป็นเลขบรรทัดที่ให้แบ่งกลุ่มด้วยคณะ และให้เรียงลำดับด้วยคะแนนภาษาอังกฤษจากมากไปน้อย ซึ่งผลที่ได้จะเป็นดังนี้

    สังเกตที่ฟิลด์ VAL_ROW_NUMBER จะเห็นว่ามันแสดงตามอันดับของคะแนนภาษาอังกฤษ และถูกแบ่งตามคณะอย่างเรียบร้อย แค่นี้เราก็สามารถที่จะ select เอา Top ที่เท่าไหร่ของแต่ละกลุ่มได้แล้ว โดยเลือกเอา VAL_ROW_NUMBER ที่ต้องการ ซึ่งสามารถทำได้โดยเขียน select…where ซ้อน query ข้างต้นเข้าไปอีกทีดังนี้

    SELECT *
    FROM (SELECT a.*,
    ROW_NUMBER ()
    OVER (PARTITION BY fac_id ORDER BY eng_score DESC)
    AS val_row_number
    FROM test_new_student a)
    WHERE val_row_number <= 1;

    ผลลัพธ์:

    สรุปจากความต้องการที่ตั้งไว้ เราสามารถแก้ได้โดยใช้ Window Function ซึ่งมันสามารถหาเลขลำดับบางอย่างภายในกลุ่มข้อมูลออกมาให้ได้รูปแบบทั่วไปของ query คือ

    1 SELECT *,
    2 WFUNCTION OVER (PARTITION BY GROUP_FIELDS ORDER BY ORDER_FIELDS) n
    3 FROM SOURCE

    โดยที่

    SOURCE คือตัวข้อมูล
    WFUNCTION  หมายถึง Window Function ตัวอย่างที่เราเลือกใช้คือ ROW_NUMBER()
    GROUP_FIELDS คือรายการฟิลด์ที่จะแบ่งกลุ่ม เขียนเหมือนตอนที่เราจะ group by สามารถแบ่งด้วยหลายฟิลด์ก็ได้
    ORDER_FIELDS คือรายการฟิลด์ที่ใช้เรียงลำดับ, สามารถเรียงด้วยหลายฟิลด์ก็ได้, ใส่ ASC หรือ DESC ได้เหมือนคำสั่ง order by ปกติเราไม่จำเป็นต้องใส่ทั้ง partition by และ order by อาจจะใส่แค่อันใดอันหนึ่ง แล้วแต่ว่าต้องการแบ่งกลุ่มหรือเรียงลำดับหรือไม่
    n เป็นชื่อ alias ของผลลัพธ์ จะตั้งชื่ออย่างไรก็ได้ เมื่อเราได้เลขลำดับ n ของแต่ละกลุ่มออกมาแล้ว ทีนี้จะเอาไปหา Top N หรือพลิกแพลงยังไง ก็แล้วแต่จะ query ออกมา

  • Oracle: retrieve top n records from a query

    benjawan.n

    Top-N queries เป็นวิธีการดึงข้อมูลสูงสุดหรือต่ำสุด N ลำดับแรกออกมาจากตาราง โดยวิธีการดึงข้อมูลแบบ Top-N นั้นมีได้หลายวิธี แต่ในบทความนี้จะนำเสนอวิธีการดึงข้อมูลแบบ Top-N records เพียง 3 วิธีการดังนี้

    1. Inline View and ROWNUM
    2. WITH Clause and ROWNUM
    3. ROW_NUMBER

     

    สมมติว่าเรามีข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่ซึ่งประกอบด้วย 5 ฟิลด์ข้อมูลดังตัวอย่างข้างล่าง
    ข้อมูล: ตาราง TEST_NEW_STUDENT เป็นตัวอย่างข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่จำนวน 773 รายการ

    โจทย์: ต้องการดึงข้อมูลนักศึกษาที่ได้คะแนนภาษาอังกฤษสูงสุด 5 อันดันแรกจากข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่จำนวน 773 รายการนี้

     

    เริ่มต้น Top-N query ตามลำดับเพื่อแก้โจทย์กันค่ะ

    Inline View and ROWNUM
    Classic Top-N style query

    SELECT a.*,rownum
    FROM (SELECT *
    FROM test_new_student
    ORDER BY eng_score desc) a
    WHERE ROWNUM <= 5;

    ผลลัพธ์:

    • จากผลลัพธ์ที่ได้ข้อมูลจะถูกจัดเรียงจากคะแนนจากมากไปน้อยก่อนด้วย ORDER BY clause และหลังจากนั้นก็จะจำกัดจำนวนข้อมูลที่ต้องการด้วย ROWNUM
    • Pseudocolumn ROWNUM เป็นค่าตัวเลขแสดงลำดับที่ของการดึงข้อมูลจากตาราง
    • กรณีที่ต้องการข้อมูลคะแนนภาษาอังกฤษต่ำสุด ใส่ ASC แทน DESC ตรง ORDER BY clause

     

    WITH Clause and ROWNUM
    จากตัวอย่างข้างต้นเรายังสามารถเขียน query ด้วย WITH clause แทนที่ inline view ได้ดังนี้

    WITH ordered_query AS
    (SELECT *
    FROM test_new_student
    ORDER BY eng_score desc)
    SELECT ordered_query.*,rownum
    FROM ordered_query
    WHERE rownum <= 5;

     

    ROW_NUMBER
    ฟังก์ชัน ROW_NUMBER เป็นฟังก์ชันที่กำหนดค่าลำดับของข้อมูลที่จัดเรียงตามข้อมูลที่กำหนดไว้ใน order_by_clause โดยจะมีค่าเริ่มต้นเท่ากับ 1 โดยเราจะมีวิธีการเขียน query ได้ดังนี้

    SELECT *
    FROM (SELECT a.*, row_number() OVER (ORDER BY eng_score DESC) AS val_row_number
    FROM test_new_student a)
    WHERE val_row_number <= 5;

    ผลลัพธ์:

    สังเกตที่ฟิลด์ VAL_ROW_NUMBER จะเห็นว่ามันแสดงตามอันดับของคะแนนภาษาอังกฤษแล้ว แค่นี้เราก็สามารถที่จะ select เอา Top ที่เท่าไหร่ได้แล้ว โดยเลือกเอา VAL_ROW_NUMBER ที่ต้องการ

  • Removing duplicate records by using Oracle’s ROWID

    benjawan.n

    ถ้าคุณมีตารางข้อมูลอยู่ และรู้ว่ามีบางแถวที่มีข้อมูลซ้ำซ้อนกัน ทางไหนเป็นวิธีที่ดีที่จะสามารถหาและกำจัดแถวที่มีข้อมูลซ้ำนี้ออกไปจากตารางของฐานข้อมูล Oracle ?

     

    การหาแถวที่มีข้อมูลซ้ำซ้อน

    เราสามารถหาข้อมูลแถวที่มีข้อมูลซ้ำซ้อนกันได้โดยใช้คำสั่ง select ดังนี้

     

    select a,b,count(*)

    from test

    group by a,b

    having count(*) > 1;

    ผลลัพธ์ที่ได้ :

    A          B   COUNT(*)

    ———- ———- ———-

    1          2        259

    2          2          5

     

    จากตัวอย่างในตาราง test นี้เราจะกำหนดว่าให้ค่าในคอลัมภ์ a และ b จะต้องมีค่าไม่ซ้ำ ซึ่งผลลัพธ์ที่ปรากฏคือ มีข้อมูลซ้ำ 258 แถว และ 4 แถว

     

    การกำจัดแถวที่มีข้อมูลซ้ำซ้อน
    เราสามารถกำจัดแถวที่มีข้อมูลซ้ำซ้อนกันได้โดยการใช้ rowid เข้ามาช่วย คราวนี้คุณต้องเลือกว่าจะเลือกเก็บข้อมูลแถวไหนไว้

    เราลองมาดูข้อมูลที่ควรจะเป็นที่ไม่ซ้ำกันว่ามีข้อมูลอะไรบ้าง โดยสามารถใช้คำสั่งได้ดังนี้

    select a,b,count(*) from test

    group by a,b;

    A          B   COUNT(*)

    ———- ———- ———-

    1          2        259

    2          2          5

    3          0          1

     

    กรณีที่ต้องการลบและคงเหลือไว้เฉพาะแถวแรกที่ซ้ำสามารถใช้คำสั่งได้ดังนี้

     

    — เราต้องการกำจัด 258 แถวที่ซึ่ง A = 1 และ B = 2 บวกกับ

    — 4 แถวที่ซึ่ง A = 2 และ B = 2

    — ลองมา select แถวที่เราจะคงไว้ดูก่อน

    select min(rowid),a,b from test

    group by a,b;

    MIN(ROWID)                             A          B

    ——————————- ———- ———-

    AAAAyvAAGAAAABYAAA          1          2

    AAAAyvAAGAAAABYAED          2          2

    AAAAyvAAGAAAABYAEI           3          0

     

    — คราวนี้ก็ถึงเวลาลบข้อมูลกันแล้ว

    — เริ่มกันเลย

    delete from test where rowid not in (

    select min(rowid) from test group by a,b);

    262 rows deleted.

     

    — คราวนี้มาตรวจสอบกันว่าข้อมูลที่คงเหลือถูกต้องหรือไม่

    select rowid,a,b from test;

    ROWID                                      A          B

    ——————————— ———- ———-

    AAAAyvAAGAAAABYAAA          1          2

    AAAAyvAAGAAAABYAED          2          2

    AAAAyvAAGAAAABYAEI           3          0

     

    กรณีที่ต้องการลบและคงเหลือไว้เฉพาะแถวสุดท้ายที่ซ้ำสามารถใช้คำสั่งได้ดังนี้

    delete from test where rowid not in (

    select max(rowid) from test group by a,b);

    262 rows deleted.

     

    แต่เพื่อป้องกันการเกิดการซ้ำซ้อนของข้อมูลเหล่านี้ เราสามารถที่จะใช้ unique constraints หรือ primary key ช่วยได้ ปัญหาข้อมูลซ้ำซ้อนแบบนี้ก็จะไม่มีทางเกิดขึ้นให้เกิดความปวดหัวได้อีก