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。

示例

考慮以下 Salary 表。

以下是如何在 Salary 表上查詢 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])

示例

考慮以下 Employee 表。

以下查詢按入職日期對員工表中的記錄進行排序，並在入職日期上分配排名。

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 子句按 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