Teradata - ฟังก์ชัน OLAP

ฟังก์ชัน OLAP คล้ายกับฟังก์ชันการรวมยกเว้นว่าฟังก์ชันการรวมจะส่งคืนค่าเพียงค่าเดียวในขณะที่ฟังก์ชัน OLAP จะให้แต่ละแถวนอกเหนือจากการรวม

ไวยากรณ์

ต่อไปนี้เป็นไวยากรณ์ทั่วไปของฟังก์ชัน OLAP

<aggregate function> OVER  
([PARTITION BY] [ORDER BY columnname][ROWS BETWEEN 
UNBOUDED PRECEDING AND UNBOUNDED FOLLOWING)

ฟังก์ชันการรวมสามารถเป็น SUM, COUNT, MAX, MIN, AVG

ตัวอย่าง

พิจารณาตารางเงินเดือนต่อไปนี้

พนักงาน ขั้นต้น การหักเงิน NetPay
101 40,000 4,000 36,000
102 80,000 6,000 74,000
103 90,000 7,000 83,000
104 75,000 5,000 70,000

ต่อไปนี้เป็นตัวอย่างในการค้นหาผลรวมสะสมหรือยอดรวมของ NetPay บนตารางเงินเดือน เร็กคอร์ดถูกจัดเรียงตาม EmployeeNo และผลรวมสะสมจะคำนวณในคอลัมน์ NetPay

SELECT  
EmployeeNo, NetPay, 
SUM(Netpay) OVER(ORDER BY EmployeeNo ROWS  
UNBOUNDED PRECEDING) as TotalSalary 
FROM Salary;

เมื่อดำเนินการค้นหาข้างต้นจะสร้างผลลัพธ์ต่อไปนี้

EmployeeNo     NetPay     TotalSalary 
-----------  -----------  ----------- 
   101         36000        36000 
   102         74000        110000 
   103         83000        193000 
   104         70000        263000 
   105         18000        281000

อันดับ

ฟังก์ชัน RANK จะเรียงลำดับระเบียนตามคอลัมน์ที่ให้ไว้ ฟังก์ชัน RANK ยังสามารถกรองจำนวนระเบียนที่ส่งคืนตามอันดับ

ไวยากรณ์

ต่อไปนี้เป็นไวยากรณ์ทั่วไปในการใช้ฟังก์ชัน RANK

RANK() OVER 
([PARTITION BY columnnlist] [ORDER BY columnlist][DESC|ASC])

ตัวอย่าง

พิจารณาตารางพนักงานต่อไปนี้

พนักงาน ชื่อจริง นามสกุล วันที่เข้าร่วม DepartmentID วันที่เกิด
101 ไมค์ เจมส์ 27/3/2548 1 1/5/1980
102 โรเบิร์ต วิลเลียมส์ 25/4/2550 2 5/3/1983
103 ปีเตอร์ พอล 21/3/2550 2 1/1/2553
104 อเล็กซ์ สจวร์ต 1/2/2561 2 11/6/1984
105 โรเบิร์ต เจมส์ 1/4/2561 3 1/12/1984

การสืบค้นต่อไปนี้จะสั่งให้บันทึกตารางพนักงานตามวันที่เข้าร่วมและกำหนดการจัดอันดับในวันที่เข้าร่วม

SELECT EmployeeNo, JoinedDate,RANK() 
OVER(ORDER BY JoinedDate) as Seniority 
FROM Employee;

เมื่อดำเนินการค้นหาข้างต้นจะสร้างผลลัพธ์ต่อไปนี้

EmployeeNo   JoinedDate   Seniority 
-----------  ----------  ----------- 
   101       2005-03-27       1 
   103       2007-03-21       2 
   102       2007-04-25       3 
   105       2008-01-04       4 
   104       2008-02-01       5

PARTITION BY clause จัดกลุ่มข้อมูลตามคอลัมน์ที่กำหนดในส่วนคำสั่ง PARTITION BY และดำเนินการฟังก์ชัน OLAP ภายในแต่ละกลุ่ม ต่อไปนี้เป็นตัวอย่างของแบบสอบถามที่ใช้คำสั่ง PARTITION BY

SELECT EmployeeNo, JoinedDate,RANK() 
OVER(PARTITION BY DeparmentNo ORDER BY JoinedDate) as Seniority 
FROM Employee;

เมื่อดำเนินการค้นหาข้างต้นจะสร้างผลลัพธ์ต่อไปนี้ คุณจะเห็นได้ว่าอันดับจะถูกรีเซ็ตสำหรับแต่ละแผนก

EmployeeNo  DepartmentNo  JoinedDate   Seniority 
-----------  ------------  ----------  ----------- 

    101           1        2005-03-27       1 
    103           2        2007-03-21       1 
    102           2        2007-04-25       2 
    104           2        2008-02-01       3 
    105           3        2008-01-04       1