ใครที่เคยสร้าง Materialized View จะทราบว่าต้องมีการกำหนดช่วงเวลาเพื่อให้ Materialized View นั้นทำการ Refresh ข้อมูลเพื่อให้ได้ข้อมูลที่เป็นปัจจุบัน หรือแม้แต่ Job ก็เช่นกันต้องกำหนดช่วงเวลาเพื่อให้ Job ทำงานตามช่วงเวลาที่กำหนด บางคนอาจจะเจอปัญหาว่าไม่รู้ว่าต้องกำหนดอย่างไร เช่น อยากให้ทำงานทุก 8 โมงเช้า หรืออยากให้ทำงานทุกเที่ยงคืน เป็นต้น เพื่อให้ปัญหานั้นหมดไป เรามาทำความเข้าใจเกี่ยวกับช่วงเวลาบน Oracle กันดีกว่า จากที่เราทราบกันว่า 1 วันมี 24 ชั่วโมง 1 ชั่วโมงมี 60 นาที และ 1 นาทีมี 60 วินาที บน Oracle เราสามารถเขียนเป็น Expression ได้ตามตัวอย่างข้างล่างนี้ ช่วงเวลา : Period Expression 1 Expression 2 1 Day 1 1 1 Hour 1/24 1/24 1 Minute 1/(24*60) 1/1440 1 Second 1/(25*60*60) 1/86400 ดังนั้นจาก Expression ข้างต้นเราก็สามารถเขียนช่วงเวลา ด้วย SQL Expression ดังตัวอย่างต่อไปนี้ ช่วงเวลาที่ต้องการ SQL Expression Now sysdate Tomorrow sysdate+1 Yesterday sysdate-1 One hour from now sysdate + 1/24 Ten minutes from now sysdate + 10/1440 Thirty seconds from now sysdate + 30/86400 Tomorrow at 12 Midnight trunc(sysdate+1) Tomorrow at 8 AM trunc(sysdate+1) + 8/24 Yesterday at 10 AM trunc(sysdate-1) + 10/24 Next Monday at 12 Noon next_day(trunc(sysdate),’MONDAY’) + 12/24 คราวนี้เราลองมาเขียนคำสั่งในการ select ข้อมูลตามช่วงเวลาที่ต้องการกันดีกว่าค่ะ ตัวอย่างที่ 1 ต้องการ query ข้อมูลเพื่อหาข้อมูลช่วงเวลาดังนี้ วันเวลาปัจจุบัน, หนึ่งชั่วโมงถัดไปจากปัจจุบัน, พรุ่งนี้ตอนเที่ยงคืน และพรุ่งนี้ตอน 8 โมงเช้า สามารถเขียน query เพื่อให้ได้ช่วงเวลาที่ต้องการได้ดังนี้ค่ะ ผลลัพธ์ที่ได้จาก query ตัวอย่างที่ 1 ตัวอย่างที่ 2 ต้องการ query ข้อมูลเพื่อหาข้อมูลช่วงเวลาดังนี้ วันเวลาปัจจุบัน, สิบนาทีถัดไปจากปัจจุบัน, 30 วินาทีถัดไปจากปัจจุบัน และเวลาเที่ยงของวันศุกร์ถัดไป สามารถเขียน query เพื่อให้ได้ช่วงเวลาที่ต้องการได้ดังนี้ค่ะ ผลลัพธ์ที่ได้จาก query ตัวอย่างที่ 2 จาก 2 ตัวอย่างข้างต้นคิดว่าก็พอจะเป็นแนวทางให้กับทุกท่านได้สามารถกำหนดช่วงเวลาที่ต้องการได้ไม่มากก็น้อย

ก่อนหน้านี้เราเคยนำเสนอบทความวิธีการทรานสโพส (หมุน) ข้อมูลจากแถวเป็นคอลัมน์ ด้วยการใช้ฟังก์ชัน 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))   ผลลัพธ์ที่ได้ : แสดงข้อมูลจำนวนนักศึกษาซึ่งประกอบด้วย จำนวนนักศึกษาคณะวิศวกรรมศาสตร์ คณะวิทยาศาสตร์ และคณะทรัพยากรธรรมชาติ ตามลำดับ โดยแสดงข้อมูลในรูปแบบคอลัมภ์

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 

การจัดกลุ่มข้อมูลด้วย 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 เพื่อหาสรุปผลรวมที่มาจากผลลัพธ์ทั้งหมดในตอนท้ายของการคิวรี