catch-img

WINDOW FUNCTIONの理解と活用方法 - 日本エクセム株式会社 Oracle 技術情報

Oracle技術情報

Oracle技術情報


目次[非表示]

  1. 1.基本情報
    1. 1.1.Window Function Syntax
    2. 1.2.テストに関連するScript
  2. 2.COUNT()
  3. 3.問題と解決
    1. 3.1.SQL[1]. パフォーマンスの問題が発生した SQL ビルドの種類
    2. 3.2.SQL[2]. パフォーマンスの問題が発生した SQL ビルドの種類
    3. 3.3.SQL[1]. SQLビルドタイプは、SQL[2] のパフォーマンスの問題を解決する
  4. 4.RANK(), ROW_NUMNER(), DENSE_RANK()
    1. 4.1.SQL[1]
    2. 4.2.SQL[1]. RANKを利用する
    3. 4.3.SQL[2]. ROW_NUMBERを使用する
    4. 4.4.SQL[3]. DENSE_RANKを利用する
    5. 4.5.Analytic Functionの使用
  5. 5.結論


基本情報

　Window Functionは、行と行の関係を簡単に定義できるよう設計された関数である。Window Function を使用すると、複雑な SQLを1つの SQL文に変更でき、 アクセスを繰り返す非効率性の問題を簡単に解決できる。このホワイトペーパーでは、Window Functionのランク (RANK、ROW_NUMBER、DENSE_RANK) および集計 (COUNT) に関連するセクションについて説明する。


Window Function Syntax

　Window FunctionはOver()関数が、基本的に含まれなければならない。Over()関数と一緒に Window Functionが一緒に使われる。

SELECT WINDOW_FUNCTION（ARGUMENTS）OVER
（[PARTITION BYコラム][ORDER BY句][WINDOWING節]）
FROMテーブル名;
・partition by：順位を定める基準カラム（GROUP BYと同じ）   
・またはorder by：基準カラム(partition by)のデータのうち、order by句に記述されたカラムで順位を定める。


テストに関連するScript

drop table t3 purge ;drop table t4 purge ;

create table t3 (c1 number, c2 char(1)) ;

create table t4 (c1 number, c2 char(1), c3 number) ;

insert into t3 values (1, ‘A’);
insert into t3 values (1, ‘B’);
insert into t3 values (1, ‘C’);
insert into t3 values (2, ‘D’);
insert into t3 values (2, ‘E’);
insert into t3 values (2, ‘F’);
insert into t3 values (3, ‘G’);
insert into t3 values (3, ‘H’);
insert into t3 values (3, ‘I’);
commit;

insert into t4 values (1, ‘A’, 5);
insert into t4 values (1, ‘A’, 10);
insert into t4 values (1, ‘A’, 10);
insert into t4 values (1, ‘A’, 15);
insert into t4 values (1, ‘B’, 20);
insert into t4 values (1, ‘B’, 25);
insert into t4 values (1, ‘C’, 30);
insert into t4 values (2, ‘D’, 1);
insert into t4 values (2, ‘D’, 2);
insert into t4 values (2, ‘E’, 3);
insert into t4 values (2, ‘F’, 5);
insert into t4 values (2, ‘E’, 4);
insert into t4 values (3, ‘G’, 1);
insert into t4 values (3, ‘G’, 2);
insert into t4 values (3, ‘I’, 1);
commit;


COUNT()

SELECT C1,C2,C3 FROM T4 WHERE C1 = 1
AND C2 = ‘A‘ ;

C1 C2                      C3
——————— 1 A             5
1A
15
1A
10
1A
10
 


SELECT C1, C2, COUNT(*) FROM T4
WHERE C2 = ‘A’ GROUP BY C1, C2 ORDER BY C1, C2 ;

C1 C2         COUNT(*)
————————————
1 A                          4

SELECT C1, C2, C3, COUNT(*) FROM T4
WHERE C1 = 1 AND C2 = ‘A’
GROUP BY C1, C2, C3 ORDER BY C1, C2, C3 ;

C1 C2                      C3        COUNT(*)
————————————
1A
5
1
1A
10
2
1A
15
1


＜最終的な抽出データ＞

SELECT C1, C2, C3 FROM T4
WHERE C1 = 1 AND C2 = ‘A’ ;

C1 C2                      C3            C3_CNT TOTAL_CNT
—————————————————————
1A
5
1
4
1A
10
2
4
1A
10
2
4
1A
15
1
4

　上記の<抽出データ>の図は、c1 = 1 and c2 = ‘A’であるデータをすべて抽出しc1、c2カラムにグループ化された件数とc1、c2、c3にグループ化された件数を図にしたものである。単純に以下のSQL [ 1 ]のようにT4テーブルのデータを重複アクセス（COUNT実行するために）実行し、処理することもできる。もちろん、<抽出データ>のデータを示すことはできるがSQLの性能は低下します。


問題と解決

SQL[1]. パフォーマンスの問題が発生した SQL ビルドの種類

SELECT T4.C1, T4.C2, T4.C3, T5.C3_CNT, T6.TOTAL_CNT
　　FROM T4, (
　　　　SELECT C1, C2, C3, COUNT(C3) AS C3_CNT FROM T4
　　　　WHERE C1 = 1 AND C2 = ‘A’
　　　　GROUP BY C1, C2, C3
　　) T5, (
　　　　SELECT C1, C2, COUNT(C3) AS TOTAL_CNT FROM T4
　　　　WHERE C1 = 1 AND C2 = ‘A’
　　　　GROUP BY C1, C2
　　) T6
　　WHERE T4.C1 = 1 AND T4.C2 = ‘A’ AND T4.C1 = T5.C1 AND T4.C2 = T5.C2 AND T4.C3 = T5.C3 AND T4.C1 = T6.C1 AND T4.C2 = T6.C2
　　ORDER BY C1,C2,C3 ;

C1 C2                       C3            C3_CNT TOTAL_CNT
——————————————————————
1A
5
1
4
1A
10
2
4
1A
10
2
4
1A
15
1
4

　次のSQL[2]は、T4テーブルのデータへの重複アクセスを避けるために、Oracle 9i以降のバージョンでは、SELECT AS WITH CLAUSEを使用すると、T4テーブルのデータに繰り返しアクセスすることなく、1行で予期されるデータを抽出できます。


SQL[2]. パフォーマンスの問題が発生した SQL ビルドの種類

WITH T1 AS (
　　SELECT C1,C2,C3 FROM T4
　　WHERE C1 = 1 AND C2 = ‘A’
)

SELECT T4.C1, T4.C2, T4.C3, T5.C3_CNT, T6.TOTAL_CNT FROM T1 T4, (
　　SELECT C1, C2, C3, COUNT(C3) AS C3_CNT FROM T1
　　GROUP BY C1, C2, C3
) T5, (
　　SELECT C1, C2, COUNT(C3) AS TOTAL_CNT
FROM T1
　　GROUP BY C1, C2
) T6
　　WHERE T4.C1 = T5.C1 AND T4.C2 = T5.C2 AND T4.C3 = T5.C3
　　AND T4.C1 = T6.C1 AND T4.C2 = T6.C2
　　ORDER BY C1,C2,C3 ;

C1 C2                       C3            C3_CNT TOTAL_CNT
————————————————————————
1A
5
1
4
1A
10
2
4
1A
10
2
4
1A
15
1
4

　しかし、問題がある。WITH CLAUSEを用いてT4テーブルのデータを1回だけ読むには、C1 = 1 AND C2 = ‘A’の条件に該当するすべてのデータをGLOBAL TEMPORARY TABLEに保存しておき、再使用する必要があるということである。

　ところが、もしC1 = 1 AND C2 =’A’の条件に該当するデータが多く、OLTP環境で頻繁に使用するSQLならGLOBAL TEMPORARY TABLEを作成して処理する作業自体が負担になることになる。また、対応するSQLがページング時に使用されるクエリ文であれば、部分範囲がされていない、また多くの他の問題が発生する可能性があります。

　上記のSQLでT4テーブルのデータを不必要に重複アクセス処理することなく、SQLの性能まで考慮したSQLの書き方は下記のようにCOUNT（* ）OVER （）を活用するものである。


SQL[1]. SQLビルドタイプは、SQL[2] のパフォーマンスの問題を解決する

SELECT C1, C2, C3,
　　COUNT(*) OVER (PARTITION BY C1,C2,C3)
　　AS C3_CNT, COUNT(*) OVER (PARTITION BY C1,C2)                                                                                  
　　AS TOTAL_CNT
FROM T4 WHERE C2 = ‘A’
　　ORDER BY C1,C2,C3 ;

C1 C2                      C3           C3_CNT TOTAL_CNT
————————————————————————
1 A                 5                      1             4
1A
10
2
4
1A
10
2
4
1A
15
1
4


RANK(), ROW_NUMNER(), DENSE_RANK()

SELECT T3.C1, T3.C2, T4.C3
FROM T3, T4
　　WHERE T3.C1 = T4.C1 AND T3.C2 = T4.C2
　　AND T3.C2 IN (‘A’,’E’,’G’)
　　ORDER BY 1,2,3 ;

C1 C2                       C3
——————————————
1 A                          15
2 E                           4
1A
5
1A
10
1A
10
1A
15
2E
3
2E
4
3G
1
3G ＜最終的な抽出データ＞
2
C1 C2
C3

　上記のデータを抽出する場合、C2 の値は “A”、”E”、”G”、 <C3 は最大値を抽出してデータ > を抽出する必要がある。Oracle 10g より前に、次のパターンを使用してSQLを記述してデータを抽出し、同じテーブルを2回繰り返し処理した。


SQL[1]

SELECT T3.C1, T3.C2, T4.C3
FROM T3, T4, (
　　SELECT T3.C1, T3.C2, MAX(T4.C3) MAX_C3 　　
　　FROM T3, T4
　　　　WHERE T3.C1 = T4.C1 AND T3.C2 = T4.C2
　　　　　　　  AND T3.C2 IN (‘A’,’E’,’G’)
　　　　GROUP BY T3.C1, T3.C2
) T5
　　WHERE T3.C1 = T4.C1 AND T3.C2 = T4.C2
　　AND T4.C3 = T5.MAX_C3
　　AND T3.C2 IN (‘A’,’E’,’G’) ;

C1 C2                       C3
————————————————
1A
15
2E
4
3G
2

　Oracle 10gの後、上記の <抽出データ>抽出方法として新しいAnalytic Function関数で 開くと、SQLは1回だけ処理され、同じテーブルを繰り返し処理しないことが保証される。この時、使用されるFUNCTIONがRANK, DENSE_RANK, ROW_NUMBERである。


SQL[1]. RANKを利用する

SELECT T3.C1, T3.C2, T4.C3
FROM T3, (
　　SELECT C1, C2, C3,
　　rank() over (
　　　　partition by C1, C2
　　　　order by C3 desc
　　) as rank_check from T4
) T4
　　WHERE T3.C1 = T4.C1 AND T3.C2 = T4.C2
　　AND T3.C2 IN (‘A’,’E’,’G’) AND T4.rank_check = 1 ;

C1 C2                       C3
——————————————
1A
15
2E
4
3G
2


SQL[2]. ROW_NUMBERを使用する

SELECT C1, C2, C3
FROM (
　　SELECT T3.C1, T3.C2, T4.C3,
　　row_number() over( 
　　　　partition by T3.C1, T3.C2
　　　　order by T4.C3 desc
　　) as rnum
　　FROM T3, T4
　　　　WHERE T3.C1 = T4.C1 AND T3.C2 = T4.C2
　　　　AND T3.C2 IN (‘A’,’E’,’G’)
)
　　WHERE rnum <= 1;

C1 C2                       C3
————————————————
1A
15
2E
4
3G
2


SQL[3]. DENSE_RANKを利用する

SELECT T3.C1, T3.C2, T4.C3
FROM T3, (
　　SELECT C1, C2, C3,
　　dense_rank() over (
　　　　partition by C1, C2
　　　　order by C3 desc
　　) as rank_check from T4
) T4
　　WHERE T3.C1 = T4.C1 AND T3.C2 = T4.C2
　　AND T3.C2 IN (‘A’,’E’,’G’) AND T4.rank_check = 1;

C1 C2                       C3
————————————————
1A
15
2E
4
3G
2


Analytic Functionの使用

　SQL[1] 、SQL[2] 、SQL[3]は、＜抽出データ＞に合致するデータを抽出することができる。ここで使用されるAnalytic Functionは RANK, DENSE_RANK, ROW_NUMBERである。RANK, DENSE_RANK, ROW_NUMBER Analytic Functionは、データをランク付けする解析関数であるが、データの正確な目的を知る必要がある。

SELECT C1, C2, C3,
rank() over(
　　partition by C1, C2
　　order by C3 desc
) as rank,
dense_rank() over(
　　partition by C1, C2
　　order by C3 desc
) as dense_rank,
row_number() over(
　　partition by C1, C2
　　order by C3 desc
) as row_number
FROM T4
　　WHERE C2 = ‘A’ ;

C1 C2                       C3                 RANK DENSE_RANK ROW_NUMBER
————————————————————————————
1A
15
1
1
1
1A
10
2
2
2
1A
10
2
2
3
1A
5
4
3
4

・RANK: 同じンク (RANK – 2) に同じ値が 2 つある場合、次の値のランクは RANK – 4 である。
・DENSE_RANK: 同じランク(RANK-2)に同じ値が2つある場合、次の値のランクは RANK -3になる。
・ROW_NUMBER: 同じランクも順位が付けられる。


結論

ここまで、Window Functionの活用方法について簡単に説明しました。これは単純な例ですが、Window Functionをうまく利用すれば、 実際の作業SQLを書くときに、より効率的な SQLを記述することができます。

前の記事

prev-article-image

CMS Collector GC Log Sample - 日本エクセム株式会社 Oracle 技術情報

次の記事

next-article-image

SSD概念と活用 - 日本エクセム株式会社 Oracle 技術情報

CONTACT

他社に頼らず自社でデータベースを監視・運用をしませんか？
MaxGaugeがサポートします

お役立ち資料は
こちらから

資料ダウンロード

不明点がある方は、
こちらからお問い合わせください

お問い合わせ

お電話でのお問い合わせはこちら

03-3518-0771

平日 10時～18時

人気記事ランキング

タグ一覧

logo_img
ページトップへ戻る

©EXEM JAPAN Corp. All Rights Reserved.