データベースとSQL（18/20）

はじめに

　データ分析を行う場合、データの管理運用、蓄積も重要な論点となる。そのためにはSQLやデータベースの知識が求められる。そこでまずは

SQL 第2版 ゼロからはじめるデータベース操作

を基にSQLの勉強をしていく。

前回

power-of-awareness.com

15.　ウィンドウ関数

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

15.1　ウィンドウ関数とは

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

15.2　ウィンドウ関数の構文

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

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

	集約関数
	ウィンドウ専用関数	__

15.3　構文の基本的な使い方

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

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

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

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

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

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

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

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

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

次回

power-of-awareness.com

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

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