ウィンドウ関数
ウィンドウ関数
名前に聞きなじみがあまりない
SQL Serverでは分析関数のように呼ばれている 参考
直近の日付latest_dateを取得するサンプル
code:sql
sample_date smalldatetime,
load_val int
);
(sample_date, load_val)
values
('2018-02-01', 1024),
('2018-02-02', 2366),
('2018-02-05', 2366),
('2018-02-07', 985),
('2018-02-08', 780),
('2018-02-12', 1000)
;
select
sample_date as cur_date,
min(sample_date) over (
order by sample_date asc
rows between 1 preceding and 1 preceding
) as latest_date
table:ㅤ
sample_date load_val
2018-02-01 00:00:00 1024
2018-02-02 00:00:00 2366
2018-02-05 00:00:00 2366
2018-02-07 00:00:00 985
2018-02-08 00:00:00 780
2018-02-12 00:00:00 1000
table:ㅤ
cur_date latest_date
2018-02-01 00:00:00 NULL
2018-02-02 00:00:00 2018-02-01 00:00:00
2018-02-05 00:00:00 2018-02-02 00:00:00
2018-02-07 00:00:00 2018-02-05 00:00:00
2018-02-08 00:00:00 2018-02-07 00:00:00
2018-02-12 00:00:00 2018-02-08 00:00:00
どうなってんだ…
ウィンドウとは…
暗黙的なウィンドウ定義(無名構文)が行われているため、ウィンドウが登場していないように見える
明示的にウィンドウを定義することもできる
名前付きのウィンドウを定義できるDBMSもあるが、エラーになるDBMSもある
無名構文の方が普及したため、そちらが標準語になってしまったらしい
ウィンドウ関数ができる事
GROUP BY - 集約 = PARTITION BY
ORDER BY句によるレコードの順序付け
普段使っているであろうORDER BYと考え方は同じ
フレーム句によるカレントレコードを中心としたサブセットの定義
ウィンドウ関数の概念
P.1059 Figure 1: Window function concepts: partitioning, ordering, framing. The current (gray) row can access rows in its frame. The frame of a tuple can only encompass tuples from that partition
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
フレーム句のオプション
ROWS
RANGES
n PRECEDING
n FOLLOWING
UNBOUNDED PRECEDING
UNBOUNDED FOLLOWING
CURRENT ROW
上記のsampletblで色々いじってみた
code:sql
select
sample_date as cur_date,
load_val as cur_load,
min(sample_date) over (
order by sample_date asc
rows between 2 preceding and 2 preceding
) as latest_date,
min(load_val) over (
order by sample_date asc
rows between 2 preceding and 2 preceding
) as latest_load,
min(sample_date) over (
order by sample_date asc
rows between 2 following and 2 following
) as next_date,
min(load_val) over (
order by sample_date asc
rows between 2 following and 2 following
) as next_load
table:ㅤ
cur_date cur_load latest_date latest_load next_date next_load
2018-02-01 00:00:00 1024 NULL NULL 2018-02-05 00:00:00 2366
2018-02-02 00:00:00 2366 NULL NULL 2018-02-07 00:00:00 985
2018-02-05 00:00:00 2366 2018-02-01 00:00:00 1024 2018-02-08 00:00:00 780
2018-02-07 00:00:00 985 2018-02-02 00:00:00 2366 2018-02-12 00:00:00 1000
2018-02-08 00:00:00 780 2018-02-05 00:00:00 2366 NULL NULL
2018-02-12 00:00:00 1000 2018-02-07 00:00:00 985 NULL NULL