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	6000	74 000
103	90 000	7 000	83 000
104	75 000	5 000	70 000

Ниже приведен пример для нахождения кумулятивной суммы или промежуточной суммы NetPay в таблице Salary. Записи сортируются по 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.03.2005	1	05.01.1980
102	Роберт	Уильямс	25.04.2007	2	05.03.1983
103	Питер	Павел	21.03.2007	2	01.04.1983
104	Alex	Стюарт	01.02.2008	2	06.11.1984
105	Роберт	Джеймс	04.01.2008	3	01.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 группирует данные по столбцам, определенным в предложении 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