Teradata - OLAP Functions

Advertisements

Previous Page
Next Page  

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

Following is the general syntax of the OLAP function.

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

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

Example

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

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 function orders the records based on the column provided. RANK function can also filter the number of records returned based on the rank.

Syntax

Following is the generic syntax to use the RANK function.

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

Example

Consider the following Employee table.

EmployeeNo FirstName LastName JoinedDate DepartmentID BirthDate
101 Mike James 3/27/2005 1 1/5/1980
102 Robert Williams 4/25/2007 2 3/5/1983
103 Peter Paul 3/21/2007 2 4/1/1983
104 Alex Stuart 2/1/2008 2 11/6/1984
105 Robert James 1/4/2008 3 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 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
Advertisements