Author: benjawan.n

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

อายุเป็นเพียงตัวเลข มีคนพูดไว้แบบนี้ แล้วถ้าเราอยากจะรู้ล่ะว่าไอ้ตัวเลข จำนวนปี จำนวนเดือน และจำนวนวัน ของอายุเรามันเป็นตัวเลขอะไร ถ้าไม่อะไรมากใช้เครื่องคิดเลข หรือนับเอาก็ได้อยู่ แต่ถ้าต้องคำนวณของทุกคนในองค์กรล่ะจะนับเอง หรือเครื่องคิดเลขก็คงไม่ไหวแล้ว ดังนั้นวันนี้จึงจะขอแนะนำ 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 การหาจำนวนปี จำนวนเดือน และจำนวนวันของอายุ

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

June 28, 2019

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

ใครที่เคยสร้าง 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/(256060)	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 ตัวอย่างข้างต้นคิดว่าก็พอจะเป็นแนวทางให้กับทุกท่านได้สามารถกำหนดช่วงเวลาที่ต้องการได้ไม่มากก็น้อย

June 24, 2019

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
)
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))

ผลลัพธ์ที่ได้ : แสดงข้อมูลจำนวนนักศึกษาซึ่งประกอบด้วย จำนวนนักศึกษาคณะวิศวกรรมศาสตร์ คณะวิทยาศาสตร์ และคณะทรัพยากรธรรมชาติ ตามลำดับ โดยแสดงข้อมูลในรูปแบบคอลัมภ์
June 26, 2018
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;

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

ฟังก์ชัน 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

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

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

June 11, 2018
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 ข้างต้นก็คือฟังก์ชัน 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 ออกมา

July 14, 2017
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 อันดันแรกจากข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่จำนวน 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 ที่ต้องการ

July 14, 2017

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 นี้เราจะกำหนดว่าให้ค่าในคอลัมภ์ 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 ช่วยได้ ปัญหาข้อมูลซ้ำซ้อนแบบนี้ก็จะไม่มีทางเกิดขึ้นให้เกิดความปวดหัวได้อีก

January 12, 2017