Teradata-OLAP関数

OLAP関数は、集計関数が1つの値のみを返すのに対し、OLAP関数は集計に加えて個々の行を提供することを除いて、集計関数に似ています。

構文

以下は、OLAP関数の一般的な構文です。

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

集計関数には、SUM、COUNT、MAX、MIN、AVGを指定できます。

例

次の給与表を検討してください。

従業員番号	キモい	控除	給料
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

以下は、給与テーブルのNetPayの累積合計または累計を見つける例です。レコードは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])

例

次のEmployeeテーブルについて考えてみます。

従業員番号	ファーストネーム	苗字	JoinedDate	DepartmentID	誕生日
101	マイク	ジェームズ	2005年3月27日	1	1980年1月5日
102	ロバート	ウィリアムズ	2007年4月25日	2	1983年3月5日
103	ピーター	ポール	2007年3月21日	2	1983年4月1日
104	アレックス	スチュアート	2008年2月1日	2	1984年11月6日
105	ロバート	ジェームズ	2008年1月4日	3	1984年12月1日

次のクエリは、Joined Dateによってemployeeテーブルのレコードを並べ替え、JoinedDateにランキングを割り当てます。

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機能を実行します。以下は、PARTITIONBY句を使用するクエリの例です。

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