2021.09.27
WINDOW FUNCTIONの理解と活用方法
WINDOW FUNCTIONの理解と活用方法
エクセムコンサルティング本部/ DBコンサルティングチーム
概要
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
1 A | 15 |
1 A | 10 |
1 A | 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(*)
—– — ———- ———-
1 A | 5 | 1 |
1 A | 10 | 2 |
1 A | 15 | 1 |
< 最終的な抽出データ>
select c1,c2,c3 from T4
WHERE C1 = 1 AND C2 = ‘A’ ;
C1 C2 C3 C3_CNT TOTAL_CNT
——— — ———- ———- ———-
1 A | 5 | 1 | 4 |
1 A | 10 | 2 | 4 |
1 A | 10 | 2 | 4 |
1 A | 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
——– — ———- ———- ———-
1 A | 5 | 1 | 4 |
1 A | 10 | 2 | 4 |
1 A | 10 | 2 | 4 |
1 A | 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
——— — ———- ———- ———-
1 A | 5 | 1 | 4 |
1 A | 10 | 2 | 4 |
1 A | 10 | 2 | 4 |
1 A | 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
1 A | 10 | 2 | 4 |
1 A | 10 | 2 | 4 |
1 A | 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 | 5 |
1 A | 10 |
1 A | 10 |
1 A | 15 |
2 E | 3 |
2 E | 4 |
3 G | 1 |
3 G < 最終的な抽出データ> | 2 |
C1 C2 | C3 |
——– — ———-
1 A 15
2 E 4
上記のデータを抽出する場合、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
——– — ———-
1 A | 15 |
2 E | 4 |
3 G | 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
——– — ———-
1 A | 15 |
2 E | 4 |
3 G | 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
—— — ———-
1 A | 15 |
2 E | 4 |
3 G | 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
—— — ———-
1 A | 15 |
2 E | 4 |
3 G | 2 |
S Q L [ 1 ] 、S Q L [ 2 ] 、S Q L [ 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
——- — ———- ———- ———- ———-
1 A | 15 | 1 | 1 | 1 |
1 A | 10 | 2 | 2 | 2 |
1 A | 10 | 2 | 2 | 3 |
1 A | 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 を記述することができる。