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
Leave a Reply