Teradata 简明教程

Teradata - OLAP Functions

OLAP 函数与聚合函数类似，只不过聚合函数仅返回一个值，而 OLAP 函数将提供各个行和聚合。

OLAP functions are similar to aggregate functions except that the aggregate functions will return only one value whereas the OLAP function will provide the individual rows in addition to the aggregates.

Syntax

以下是 OLAP 函数的一般语法。

Following is the general syntax of the OLAP function.

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

聚合函数可以是 SUM、COUNT、MAX、MIN、AVG。

Aggregation functions can be SUM, COUNT, MAX,MIN, AVG.

Example

考虑以下 Salary 表格。

Consider the following Salary table.

EmployeeNo	Gross	Deduction	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 列上计算累积和。

Following is an example to find the cumulative sum or running total of NetPay on Salary table. Records are sorted by EmployeeNo and cumulative sum is calculated on NetPay column.

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

执行以上查询后，将产生以下输出。

When the above query is executed, it produces the following output.

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

RANK

RANK 函数根据提供的列对记录进行排序。RANK 函数还可以根据排名过滤返回的记录数。

RANK function orders the records based on the column provided. RANK function can also filter the number of records returned based on the rank.

Syntax

下面是使用 RANK 函数的通用语法。

Following is the generic syntax to use the RANK function.

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

Example

考虑以下 Employee 表。

Consider the following Employee table.

EmployeeNo

FirstName

LastName

JoinedDate

DepartmentID

BirthDate

101

Mike

James

3/27/2005

1/5/1980

102

Robert

Williams

4/25/2007

3/5/1983

103

Peter

Paul

3/21/2007

4/1/1983

104

Alex

Stuart

2/1/2008

11/6/1984

105

Robert

James

1/4/2008

12/1/1984

下面的查询按加入日期对员工表中的记录排序，并按加入日期分配排名。

Following query orders the records of the employee table by Joined Date and assigns the ranking on Joined Date.

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

执行以上查询后，将产生以下输出。

When the above query is executed, it produces the following output.

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 子句的查询示例。

PARTITION BY clause groups the data by the columns defined in the PARTITION BY clause and performs the OLAP function within each group. Following is an example of the query that uses PARTITION BY clause.

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

执行上述查询时，它会产生以下输出。可以看到每个部门的排名都已重置。

When the above query is executed, it produces the following output. You can see that the Rank is reset for each Department.

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