「大人の教養・知識・気付き」を伸ばすブログ

※今月(8月)は一部コンテンツを隔週更新にします(夏休みです…)。 一流の大人(ビジネスマン、政治家、リーダー…)として知っておきたい、教養・社会動向を意外なところから取り上げ学ぶことで“気付く力”を伸ばすブログです。

MENU

データベースとSQL(18/20)

15. ウィンドウ関数

  • ウィンドウ関数は、ランキング、連番精製など通常の集約関数ではできない高度な操作を行う。
  • \mathrm{PARTITION\ BY}および\mathrm{ORDER\ BY}の意味を理解することが重要である。

15.1 ウィンドウ関数とは

 ウィンドウ関数(\mathrm{OLAP}関数)はデータ分析を行う処理のための関数である。

15.2 ウィンドウ関数の構文

-- ウィンドウ関数の基礎的な構文
-- <ウィンドウ関数> OVER ([PARTITION BY <列リスト>]
--                                            ORDER BY <ソート用列リスト>)

 以下の2種類がウィンドウ関数として利用できる:

  集約関数 \mathrm{SUM,\ AVG,\ COUNT,\ MAX,\ MIN}
  ウィンドウ専用関数 \mathrm{RANK,\ }\mathrm{DENSE}_\mathrm{RANK,\ ROW}_\mathrm{NUMBER}

15.3 構文の基本的な使い方

 順位を返す\mathrm{RANK}関数を用いてウィンドウ関数の構文を実際に扱ってみる。\mathrm{PARTITION\ BY}は順位を付ける対象範囲を指定*1し、\mathrm{ORDER\ BY}でどの列をどのような順序で順位を付けるかを指定した*2
 \mathrm{PARTITION\ BY}で区切られたレコードの集合を「ウィンドウ」という。

SELECT A.a, A.b, A.c,
             RANK () OVER (PARTITION BY A.b
                                      ORDER BY A.c desc) AS ranking
FROM  ABC AS A

 もし\mathrm{PARTITION\ BY}が無ければ\mathrm{ORDER\ BY}で指定した列の全値で序列をつけることになる。すなわち\mathrm{PARTITION\ BY}は省略することもできる。なおウィンドウ関数の構文内にある\mathrm{ORDER\ BY}は構文内の順序を決めるものであり、結果の順序を付けるための\mathrm{ORDER\ BY}は別途追加しなければならない。

15.4 代表的なウィンドウ専用関数

 ウィンドウ専用関数は引数を指定しないことに注意。

   \mathrm{RANK} ランクを算出する。同順位が複数レコード存在した場合、後続の順位が飛ぶ。
   \mathrm{DENSE}_\mathrm{RANK} ランクを算出する。同順位が複数レコード存在した場合、後続の順位を飛ばさない。
   \mathrm{ROW}_\mathrm{NUMBER} 一意な連番を付与する。

15.5 ウィンドウ関数の利用場所

 ウィンドウ関数は\mathrm{WHERE}句や\mathrm{GROUP\ BY}句による処理が終わった結果に対して作用するように作られているため、\mathrm{SELECT}文のみ利用可能で\mathrm{WHERE}句や\mathrm{GROUP\ BY}句では利用できない。

15.6 集約関数をウィンドウ関数として利用する

 すべての集約関数はウィンドウ関数として利用でき、その場合は累計値を出力する。
 またウィンドウの中で集計範囲を更に細かく指定する「フレーム」というオプション機能がある。

-- 移動平均の例
-- 2 PRECEDING:「2つ」「前」の
-- ROWS:「行」とで計算する
SELECT  A.id, A.b, A.c, AVG(A.d) OVER (ORDER BY A.id
                                                              ROWS 2 PRECEDING) AS moving_avg
FROM ABC AS A
ORDER BY A.id

*1:対象の値毎にレコードを分割してそれぞれのレコードにRANKを付ける。

*2:デフォルトでは昇順。

プライバシーポリシー お問い合わせ