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