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。
例子
考虑下面的薪水表。
| 职工号 | Gross | 扣除 | 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 |
以下是在 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 函数的通用语法。
RANK() OVER ([PARTITION BY columnnlist] [ORDER BY columnlist][DESC|ASC])
例子
考虑下面的 Employee 表。
| 职工号 | 名 | LastName | 加入日期 | 部门编号 | 出生日期 |
|---|---|---|---|---|---|
| 101 | Mike | James | 2005 年 3 月 27 日 | 1 | 1/5/1980 |
| 102 | Robert | Williams | 2007 年 4 月 25 日 | 2 | 3/5/1983 |
| 103 | Peter | Paul | 2007 年 3 月 21 日 | 2 | 4/1/1983 |
| 104 | Alex | Stuart | 2/1/2008 | 2 | 11/6/1984 |
| 105 | Robert | James | 1/4/2008 | 3 | 1984 年 12 月 1 日 |
以下查询按加入日期对员工表的记录进行排序，并在加入日期分配排名。
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