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