Teradata - funkcje OLAP

Funkcje OLAP są podobne do funkcji agregujących, z tą różnicą, że funkcje agregujące zwracają tylko jedną wartość, podczas gdy funkcja OLAP zapewnia oprócz agregatów również pojedyncze wiersze.

Składnia

Poniżej przedstawiono ogólną składnię funkcji OLAP.

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

Funkcje agregacji mogą mieć wartości SUMA, COUNT, MAX, MIN, AVG.

Przykład

Rozważ poniższą tabelę wynagrodzeń.

Pracownik numer	obrzydliwy	Odliczenie	Płaca netto
101	40 000	4000	36.000
102	80 000	6000	74 000
103	90 000	7,000	83 000
104	75 000	5000	70 000

Poniżej znajduje się przykład znalezienia skumulowanej sumy lub sumy bieżącej NetPay w tabeli wynagrodzeń. Rekordy są sortowane według numeru pracownika, a skumulowana suma jest obliczana w kolumnie NetPay.

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

Kiedy powyższe zapytanie jest wykonywane, generuje następujące dane wyjściowe.

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

RANGA

Funkcja RANK porządkuje rekordy na podstawie podanej kolumny. Funkcja RANK może również filtrować liczbę zwracanych rekordów na podstawie rangi.

Składnia

Poniżej przedstawiono ogólną składnię używaną w funkcji POZYCJA.

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

Przykład

Rozważ poniższą tabelę Pracownik.

Pracownik numer	Imię	Nazwisko	JoinedDate	DepartmentID	Data urodzenia
101	Mikrofon	James	27.03.2005	1	05.01.1980
102	Robert	Williams	25.04.2007	2	05.03.1983
103	Piotr	Paweł	21.03.2007	2	01.04.1983
104	Alex	Stuart	01.02.2008	2	06.11.1984
105	Robert	James	1/4/2008	3	01.12.1984

Następujące zapytanie porządkuje rekordy tabeli pracowników według daty dołączenia i przypisuje ranking na dzień dołączenia.

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

Kiedy powyższe zapytanie jest wykonywane, generuje następujące dane wyjściowe.

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

Klauzula PARTITION BY grupuje dane według kolumn zdefiniowanych w klauzuli PARTITION BY i wykonuje funkcję OLAP w każdej grupie. Poniżej znajduje się przykład kwerendy używającej klauzuli PARTITION BY.

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

Kiedy powyższe zapytanie jest wykonywane, generuje następujące dane wyjściowe. Jak widać, ranga jest resetowana dla każdego działu.

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