อายุเป็นเพียงตัวเลข มีคนพูดไว้แบบนี้ แล้วถ้าเราอยากจะรู้ล่ะว่าไอ้ตัวเลข จำนวนปี จำนวนเดือน และจำนวนวัน ของอายุเรามันเป็นตัวเลขอะไร ถ้าไม่อะไรมากใช้เครื่องคิดเลข หรือนับเอาก็ได้อยู่ แต่ถ้าต้องคำนวณของทุกคนในองค์กรล่ะจะนับเอง หรือเครื่องคิดเลขก็คงไม่ไหวแล้ว ดังนั้นวันนี้จึงจะขอแนะนำ Oracle Datetime Functions ที่สามารถนำมาประยุกต์ใช้ เพื่อคำนวณหาอายุของเรากันค่ะ Oracle Datetime Functions ที่จะแนะนำในวันนี้คือ ฟังก์ชัน TRUNC (date), ADD_MONTHS และฟังก์ชัน MONTHS_BETWEEN โดยมีรายละเอียดของฟังก์ชันดังนี้ ฟังก์ชัน คำอธิบาย ADD_MONTHS เพิ่มจำนวนเดือนลงในวันที่ที่ระบุ MONTHS_BETWEEN หาจำนวนเดือนระหว่าง 2 วันที่ที่ระบุ TRUNC (date) คืนค่าวันที่พร้อมส่วนเวลาของวันที่ตัดให้เป็นหน่วยที่ระบุ ตัวอย่างการใช้งานฟังก์ชัน…
Author: เบญจวรรณ สุขหนู
SQL Expression สำหรับหาช่วงเวลาที่ต้องการ
ใครที่เคยสร้าง Materialized View จะทราบว่าต้องมีการกำหนดช่วงเวลาเพื่อให้ Materialized View นั้นทำการ Refresh ข้อมูลเพื่อให้ได้ข้อมูลที่เป็นปัจจุบัน หรือแม้แต่ Job ก็เช่นกันต้องกำหนดช่วงเวลาเพื่อให้ Job ทำงานตามช่วงเวลาที่กำหนด บางคนอาจจะเจอปัญหาว่าไม่รู้ว่าต้องกำหนดอย่างไร เช่น อยากให้ทำงานทุก 8 โมงเช้า หรืออยากให้ทำงานทุกเที่ยงคืน เป็นต้น เพื่อให้ปัญหานั้นหมดไป เรามาทำความเข้าใจเกี่ยวกับช่วงเวลาบน Oracle กันดีกว่า จากที่เราทราบกันว่า 1 วันมี 24 ชั่วโมง 1 ชั่วโมงมี 60 นาที และ 1 นาทีมี 60 วินาที บน…
Transpose rows into columns using the Oracle PIVOT operator
ก่อนหน้านี้เราเคยนำเสนอบทความวิธีการทรานสโพส (หมุน) ข้อมูลจากแถวเป็นคอลัมน์ ด้วยการใช้ฟังก์ชัน 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…
Oracle : Transpose rows into columns using SUM and DECODE
วันนี้เราจะนำเสนอวิธีการทรานสโพส (หมุน) ข้อมูลจากแถวเป็นคอลัมน์ โดยการประยุกต์ใช้ฟังก์ชัน 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;…
Oracle / PLSQL: LISTAGG Function
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 คือ คอลัมภ์ที่ต้องการนำค่าข้อมูลมาเรียงต่อกัน…
Oracle : ROLLUP Extension to GROUP 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 รายการ โจทย์ เราต้องการนับจำนวนนักศึกษาใหม่แยกตามคณะที่นักศึกษาสังกัด และรหัส สน.ที่สอบได้ เราสามารถใช้ประโยค…
Oracle: retrieve top n records for each group
วิธีการเขียน 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…
Oracle: retrieve top n records from a query
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 อันดันแรกจากข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่จำนวน…
Removing duplicate records by using Oracle’s ROWID
ถ้าคุณมีตารางข้อมูลอยู่ และรู้ว่ามีบางแถวที่มีข้อมูลซ้ำซ้อนกัน ทางไหนเป็นวิธีที่ดีที่จะสามารถหาและกำจัดแถวที่มีข้อมูลซ้ำนี้ออกไปจากตารางของฐานข้อมูล 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 นี้เราจะกำหนดว่าให้ค่าในคอลัมภ์…