Month: June 2018

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

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

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)

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