OLAP 指定の使用

例: ランキングおよび行の番号付け

 SELECT EMPNO, SALARY, 
        RANK() OVER(ORDER BY SALARY DESC),
        DENSE_RANK() OVER(ORDER BY SALARY DESC),
        ROW_NUMBER() OVER(ORDER BY SALARY DESC)  
 FROM EMPLOYEE
 FETCH FIRST 10 ROWS ONLY

この照会は次の情報を戻します。

この例では、上位 10 名の SALARY が降順で戻されます。 RANK 列は各給与の相対ランキングを示します。2 の位置に同じ給与の行が 2 つあるのがわかります。どちらの行にも同じランキングが割り当てられています。 次の行は 4 の値が割り当てられています。RANK は 1 つの行に対し 1 つの値を戻します。 この値は対象としている行の前に存在する行の合計数に 1 を加えた数です。重複ができると常に、順序を示す番号に、抜けている数があることになります。

対照的に DENSE_RANK 列では、重複していた 2 位の行の次に 3 という値を表示します。 DENSE_RANK は、対象行の前にある別の内容の行の値に 1 を加えた数を戻します。順序を示す番号は常に連続しています。

ROW_NUMBER は各行に対して固有の番号を戻します。 指定された順序に従って値が重複する行では、行番号の割り当ては任意になります。 照会が再度実行される際には、重複する行に対して異なる順序で行番号を割り当てることができます。

例: ランキング・グループ

平均給与が最も高い部署と各部署の平均給与の変位値を知りたいとします。次の照会は、 データを部署別にグループ化し、各部署の平均給与を算出し、結果の平均値をランク付けし、平均給与の変位値を表示します。

SELECT WORKDEPT, INT(AVG(SALARY)) AS AVERAGE, 
         RANK() OVER(ORDER BY AVG(SALARY) DESC) AS AVG_SALARY, 
         NTILE(3) OVER(ORDER BY AVG(SALARY) DESC) AS QUANTILE 
   FROM EMPLOYEE 
   GROUP BY  WORKDEPT 

この照会は次の情報を戻します。

この例では、NTILE 関数の引数が 3 になっていますが、 これは同じサイズの 3 つのセットに結果がグループ分けされることを意味します。 結果セットは変位値の数で均等に分割できないため、値が最も低い 2 つの変位値のそれぞれには 1 つずつ追加行が組み込まれます。

例: 部署内でのランキング

SELECT LASTNAME, WORKDEPT, BONUS, 
        DENSE_RANK() OVER(PARTITION BY WORKDEPT ORDER BY BONUS DESC) 
          AS BONUS_RANK_IN_DEPT 
   FROM EMPLOYEE
   WHERE WORKDEPT LIKE 'E%'

この照会は次の情報を戻します。

例: 表式の結果によるランキングおよび順序付け

給与が高い上位 5 名の従業員を部署名とともに検索しようとしているとします。 部署名は department 表に存在するため、結合操作が必要になります。順序付けはネストされた表の式ですでに完了しているため、ROW_NUMBER 値の判別に順序付けを用いることも可能です。 これを実現するためには ORDER BY ORDER OF 表 文節が使用されます。

SELECT ROW_NUMBER() OVER(ORDER BY ORDER OF EMP),
             EMPNO, SALARY, DEPTNO, DEPTNAME      
   FROM (SELECT EMPNO, WORKDEPT, SALARY 
             FROM EMPLOYEE        
             ORDER BY SALARY DESC     
             FETCH FIRST 5 ROWS ONLY) EMP,
        DEPARTMENT 
   WHERE DEPTNO = WORKDEPT

この照会は次の情報を戻します。

例: OLAP 集約および CUME_DIST の使用

部署 D11 の従業員の給与の移動合計と給与分布を知りたいとします。

SELECT ROW_NUMBER() OVER() AS ROW, LASTNAME, SALARY,
     SUM(SALARY) OVER(ORDER BY SALARY 
         RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ROLLING_TOTAL_RANGE,
     SUM(SALARY) OVER(ORDER BY SALARY 
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ROLLING_TOTAL_ROWS,
     DECIMAL(CUME_DIST() OVER (ORDER BY SALARY),4,3) AS DISTRIBUTION
  FROM EMPLOYEE
  WHERE WORKDEPT = 'D11'
  ORDER BY SALARY

この照会は次の情報を戻します。

この例では、グループの値を計算するときに使用されるウィンドウを定義する 2 つの方法が示されています。

最初の方法では RANGE を使用してウィンドウが定義されます。これは、ORDER BY の値が同じであるすべての行に対してグループを定義します。行番号 5 と 6 は、給与の値が同じであるため、 1 つのグループとして扱われます。これらの行の給与が合算され、前の合計に加算されて、 ROLLING_TOTAL_RANGE 列に示されているように、これらの行のそれぞれに対して同じ値が生成されます。

2 番目の方法では、 ROWS を使用してウィンドウが定義されます。これは、各行を 1 つのグループとして扱います。ROLLING_TOTAL_ROWS 列では、現在行までの合計が各行に示されています。行 5 と 6 のように給与値が同じ行の場合、返される順序は不定です。

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW は、デフォルトの集約グループであり、ROLLING_TOTAL_RANGE 指定から省略可能です。

ORDER BY が照会全体に指定されることで、行が順序付けられて返されることが保証されます。

例: RANGE ウィンドウ操作指定の使用

部署 D11 について給与をもっと詳細に分析したいとします。

次の例では、 各行の給与よりも 1000 少ないか多い給与値を一緒に処理するために、RANGE を使用したウィンドウ操作によって給与をグループ化します。 合計を計算するために使用される、グループの最初と最後の給与値も返します。

SELECT LASTNAME, SALARY,
       SUM(SALARY) OVER(ORDER BY SALARY) AS ROLLING_TOTAL, 
       SUM(SALARY) OVER(ORDER BY SALARY 
           RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING) AS WINDOWED_TOTAL,
       FIRST_VALUE(SALARY) OVER(ORDER BY SALARY 
           RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING),
       LAST_VALUE(SALARY) OVER(ORDER BY SALARY 
           RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING)
  FROM EMPLOYEE
  WHERE WORKDEPT = 'D11'
  ORDER BY SALARY

この照会は次の情報を戻します。

従業員ごとに 1 つのグループが定義され、 そのグループには、その従業員の給与よりも 1000 少ない (PRECEDING)、または 1000 多い (FOLLOWING) 範囲内の給与である、部署 D11 のすべての他の従業員が含まれます。WINDOWED_TOTAL 列には、 そのグループ内のすべての給与の合計が返されます。FIRST_VALUE 列には、グループのうち最低の給与値が返されます。 LAST_VALUE 列には、グループのうち最高の給与値が返されます。 最も近い他の給与との差が 1000 を超える給与の従業員はその従業員自身で 1 つのグループになります。

ORDER BY が照会全体に指定されることで、行が順序付けられて返されることが保証されます。

例: ROWS ウィンドウ操作指定の使用

次の例では、各従業員給与の 1 行前と 1 行後にある給与値を一緒に処理するために、ROWS を使用したウィンドウ操作によって給与をグループ化します。3 つの行の平均が返されます。

SELECT LASTNAME, SALARY,
       DECIMAL(AVG(SALARY) OVER(ORDER BY SALARY 
           ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)7,2) AS AVG_SALARY
  FROM EMPLOYEE
  WHERE WORKDEPT = 'D11'
  ORDER BY SALARY

この照会は次の情報を戻します。