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