分析関数と言われているものですが、具体的にどう言ったものか分かってなかったので学習しました。
概要と具体的な使い方などを説明します
ウィンドウ関数とは？
特定の範囲のデータのみ参照できるようにする機能をWindowと呼んでいます。
このWindowに対して、最大、最小、平均などの集計を行う関数をWindow（ウィンドウ）関数と呼びます。
Group byとは違う集計関数….って理解で問題なさそうです。
基本構文は、「Window関数() Over() 」
具体例があった方分かりやすいと思うので、サンプルを。
create temp table school (
id integer unique,
name varchar(10),
test_number INTEGER,
class varchar(10)
);
insert into school values (1, '一郎', 100, 'A');
insert into school values (2, '次郎', 10, 'A');
insert into school values (3, '三郎', 20, 'B');
insert into school values (4, '四郎', 30, 'A');
insert into school values (5, '吾郎', 40, 'C');
insert into school values (6, '六郎', 50, 'B');
insert into school values (7, '七太郎', 60, 'A');
insert into school values (8, '八郎', 90, 'C');
insert into school values (9, 'Q太郎', 80, 'C');
insert into school values (10, '重太', 70, 'B');
こちらのSQLを実施することで以下のようなクラス毎に生徒の点数のランク付けが行えます！
select
name,
class,
rank() over(partition by class order by test_number desc)
from school;
over()の中で、classカラムごとにpartition by（グルーピング）し、
text_numberカラムでorder by（ソート）してます。
ウィンドウ関数は、rank()です。
over()の中でグルーピングした内容に対して順位付けしています。
Group Byより使いやすい！！
実は、ウィンドウの定義は暗黙的に行われている
先ほどのSQL文では、以下のように記載していました。
select
name,
class,
rank() over(partition by class order by test_number desc)
from school;実は次のように書くこともできます。
select
name,
class,
rank() over win
from school
WINDOW win as (partition by class order by test_number desc);つまり、ウィンドウの定義は暗黙的に行われてます。
簡略的にウィンドウの定義は省略できている状態で、無名関数に近いものと考えて問題ないです。
…で！これが何が嬉しいの？
Window定義の使い回しが出来るのが嬉しい！
例えばウィンドウ関数で「クラスの合計値」と「クラスの平均」を取りたい場合
省略形と、Window定義を使った場合を記載します。
[省略形を使った場合]
select
class,
sum(test_number) over (partition by class),
avg(test_number) over (partition by class)
from school
[window定義した場合]
select
class,
sum(test_number) over win,
avg(test_number) over win
from school
WINDOW win as (partition by class);後者の方が、まとめられていますし
何より、修正がWINDOWの中だけで済むのでバグも減らせます！
集計範囲の指定して、次のレコードの値を持ってくる
ROWSを使うと、次の行の値を取得できるようになります！
具体的にSQL文で書きます。
select
name,
class,
min(name) over(ROWS BETWEEN 1 FOLLOWING and 1 FOLLOWING)
from school;
このSQLは、次の行の名前を表示しています！
なので、10行目は次のデータがないのでNULLが表示されている訳です。
構文は以下です
ROWS BETWEEN 「開始点」 AND 「終了点」
ROWSは、移動単位を行数で設定するため、次の行を取得したり出来ているわけです。
逆にRANGEは、移動単位を列で設定するので、カラムを指定して取得できます
（PostgreSQLではまだ未実装で使えないらしいですが…）
「1 FOLLOWING」となっている部分は、「次の行」を指定しています。
ROWS BETWEEN 1 FOLLOWING and 1 FOLLOWINGつまり、これは今の行から開始点1行〜終了点1行までを指定している訳です（＝次の1行）
ちなみに前の行は、「PRECEDING」です。
まとめ
・ウィンドウの基本構文は、「windows関数() over()」
・ウィンドウの定義は普段省略されている。省略しない書き方もできる
・partition by、order by、rowなどを使って集計範囲を指定できる。
・widows関数で、集計、合計、ランク付ができる
です！
使いこなせると、プログラミング言語側でゴリゴリしていた処理をSQLだけで完結できる可能性もあります！
ぜひ、使いこなしましょう！！
