■■■概要■■■
■ウィンドウ関数のRANK関数の利用■
例）商品価格で順位付け
・item_purchase_logテーブル
item_name|item_category|action_date|user_id|price|
---------|-------------|-----------|-------|-----|
コーヒー |beverage | 2019-01-29|UID0001| 280|
緑茶 |food | 2019-01-29|UID0001| 280|
緑茶 |food | 2019-01-30|UID0004| 200|
コーヒー |beverage | 2019-01-30|UID0004| 750|
砂糖 |food | 2019-01-30|UID0011| 200|
コーヒー |beverage | 2019-01-30|UID0011| 750|
メイプルシロップ |food | 2019-02-01|UID0011| 450|
メイプルシロップ |food | 2019-02-02|UID0011| 450|
ティーカップ |zakka | 2019-02-03|UID0002| 980|
砂糖 |food | 2019-02-04|UID0006| 280|
・SQL
SELECT
item_name,
item_category,
price,
RANK() OVER(ORDER BY price) AS ranking
FROM
item_purchase_log;
・結果
item_name|item_category|price|ranking|
---------|-------------|-----|-------|
緑茶 |food | 200| 1|
砂糖 |food | 200| 1|
コーヒー |beverage | 280| 3|
緑茶 |food | 280| 3|
砂糖 |food | 280| 3|
メイプルシロップ |food | 450| 6|
メイプルシロップ |food | 450| 6|
コーヒー |beverage | 750| 8|
コーヒー |beverage | 750| 8|
ティーカップ |zakka | 980| 10|
■ウィンドウ関数の構文■
・item_category毎に、前日と当日売上の平均を計算するSQL
SELECT
item_category,
action_date,
price,
AVG(price) OVER(
PARTITION BY item_category
ORDER BY action_date
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW # 平均計算の範囲
) AS moving_avg
FROM
item_purchase_log;
・結果
item_category|action_date|price|moving_avg|
-------------|-----------|-----|----------|
beverage | 2019-01-29| 280| 280.0000|
beverage | 2019-01-30| 750| 515.0000|
beverage | 2019-01-30| 750| 750.0000|
food | 2019-01-29| 280| 280.0000|
food | 2019-01-30| 200| 240.0000|
food | 2019-01-30| 200| 200.0000|
food | 2019-02-01| 450| 325.0000|
food | 2019-02-02| 450| 450.0000|
food | 2019-02-04| 280| 365.0000|
zakka | 2019-02-03| 980| 980.0000|
ウィンドウ関数がかけるのは、SELECT句の中のみ。これは、ウィンドウ関数がWHERE句やGROUP BY句などの処理が終わった結果に対して作用するため。
■■■ウィンドウ関数の範囲と順序を指定するPARTITION BYとORDER BY■■■
■PARTITION BYでグループ分け■
- ウィンドウーPARTITION BYにより分割されたグループ。GROUP BYと違い、行の集約は行わない。
- 例）カテゴリごとに、ウィンドウを設定して購入金額合計を求める
SELECT
item_name,
item_category,
price,
SUM(price) OVER(PARTITION BY item_category) AS sum_category
FROM
item_purchase_log;
- 結果
item_name|item_category|price|sum_category|
---------|-------------|-----|------------|
コーヒー |beverage | 280| 1780|
コーヒー |beverage | 750| 1780|
コーヒー |beverage | 750| 1780|
緑茶 |food | 280| 1860|
緑茶 |food | 200| 1860|
砂糖 |food | 200| 1860|
メイプルシロップ |food | 450| 1860|
メイプルシロップ |food | 450| 1860|
砂糖 |food | 280| 1860|
ティーカップ |zakka | 980| 980|
■PARTITION BYを指定しない場合のウィンドウ■
ーウィンドウがテーブル全体に適用される
- 例）表全体の合計値を新しいカラムとして追加
SELECT
item_name,
item_category,
price,
SUM(price) OVER() AS sum_all
FROM
item_purchase_log;
- 結果
item_name|item_category|price|sum_all|
---------|-------------|-----|-------|
コーヒー |beverage | 280| 4620|
緑茶 |food | 280| 4620|
緑茶 |food | 200| 4620|
コーヒー |beverage | 750| 4620|
砂糖 |food | 200| 4620|
コーヒー |beverage | 750| 4620|
メイプルシロップ |food | 450| 4620|
メイプルシロップ |food | 450| 4620|
ティーカップ |zakka | 980| 4620|
砂糖 |food | 280| 4620|
※PARTTION BYは、対象となるテーブルを複数のウィンドウに分け（グループに分け）て、関数を適用する必要があるという場合に指定する。
■ORDER BYで並べ変える■
RANK関数など、順序付けされたウィンドウに対してウィンドウ関数を適用する場合は、各ウィンドウ内での順序を指定するためのORDER BY句が必要になる。ORDER BYは、ウィンドウ内で行の並べ替えを行う。
例）商品価格ごとにソートしてランク付け
・テーブル
item_name|item_category|action_date|user_id|price|
---------|-------------|-----------|-------|-----|
コーヒー |beverage | 2019-01-29|UID0001| 280|
緑茶 |food | 2019-01-29|UID0001| 280|
緑茶 |food | 2019-01-30|UID0004| 200|
コーヒー |beverage | 2019-01-30|UID0004| 750|
砂糖 |food | 2019-01-30|UID0011| 200|
コーヒー |beverage | 2019-01-30|UID0011| 750|
メイプルシロップ |food | 2019-02-01|UID0011| 450|
メイプルシロップ |food | 2019-02-02|UID0011| 450|
ティーカップ |zakka | 2019-02-03|UID0002| 980|
砂糖 |food | 2019-02-04|UID0006| 280|
・SQL
SELECT
item_name,
item_category,
price,
RANK() OVER(ORDER BY price) AS ranking
FROM
item_purchase_log;
・結果
item_name|item_category|price|ranking|
---------|-------------|-----|-------|
緑茶 |food | 200| 1|
砂糖 |food | 200| 1|
コーヒー |beverage | 280| 3|
緑茶 |food | 280| 3|
砂糖 |food | 280| 3|
メイプルシロップ |food | 450| 6|
メイプルシロップ |food | 450| 6|
コーヒー |beverage | 750| 8|
コーヒー |beverage | 750| 8|
ティーカップ |zakka | 980| 10|
■■■ウィンドウ関数に変身する関数と専用の関数■■■
- RANK, DENSE_RANKE, ROW_NUMBERなどのウィンドウ専用関数
- 集約関数(SUM, AVG, COUNT, MAX, MIN)をウィンドウ関数として利用
■順序を扱うウィンドウ関数専用の関数■
- RANK() - ランキング（同率で番号を飛ばす）を付与
- DENSE_RANK() - ランキング（同率で番号を飛ばさない）を付与
- ROW_NUMBER() - 各行に一意の行番号を付与
※ウィンドウ専用関数は順序を用いることが必須なので、ORDER BY句を指定する。
・priceに適用した３種の例
SELECT
item_name,
item_category,
price,
RANK() OVER(ORDER BY price) AS ranking,
DENSE_RANK() OVER(ORDER BY price) AS dense_ranking,
ROW_NUMBER() OVER(ORDER BY price) AS row_num
FROM
item_purchase_log;
・結果
item_name|item_category|price|ranking|dense_ranking|row_num|
---------|-------------|-----|-------|-------------|-------|
緑茶 |food | 200| 1| 1| 1|
砂糖 |food | 200| 1| 1| 2|
コーヒー |beverage | 280| 3| 2| 3|
緑茶 |food | 280| 3| 2| 4|
砂糖 |food | 280| 3| 2| 5|
メイプルシロップ |food | 450| 6| 3| 6|
メイプルシロップ |food | 450| 6| 3| 7|
コーヒー |beverage | 750| 8| 4| 8|
コーヒー |beverage | 750| 8| 4| 9|
ティーカップ |zakka | 980| 10| 5| 10|
■集約関数からウィンドウ関数に変身する関数■
集約関数かウィンドウ関数かを見分けるのは、OVER句があるかどうか。また、集約関数はテーブル全体やGROUP BY句によるグループごとに値を集約し、１行として返すが、ウィンドウ関数として働く場合は、集約した値を計算してから、各行に新しくカラムとして付与する。
・例）ウィンドウ関数に変身する関数
・SUM - ウィンドウ内の合計値を算出
・MIN - ウィンドウ内の最小値を算出
・MAX - ウィンドウ内の最大値を算出
・COUNT - ウィンドウ内の行数を数える
・priceの平均、合計値、最大値を計算するSQL
SELECT
item_name,
item_category,
price,
AVG(price) OVER() AS avg,
SUM(price) OVER() AS sum,
COUNT(price) OVER() AS count,
MAX(price) OVER() AS max
FROM
item_purchase_log
ORDER BY
price;
・結果
item_name|item_category|price|avg |sum |count|max|
---------|-------------|-----|--------|----|-----|---|
緑茶 |food | 200| 462.0000| 4620| 10|980|
砂糖 |food | 200| 462.0000| 4620| 10|980|
コーヒー |beverage | 280| 462.0000| 4620| 10|980|
緑茶 |food | 280| 462.0000| 4620| 10|980|
砂糖 |food | 280| 462.0000| 4620| 10|980|
メイプルシロップ |food | 450| 462.0000| 4620| 10|980|
メイプルシロップ |food | 450| 462.0000| 4620| 10|980|
コーヒー |beverage | 750| 462.0000| 4620| 10|980|
コーヒー |beverage | 750| 462.0000| 4620| 10|980|
ティーカップ |zakka | 980| 462.0000| 4620| 10|980|
■■■フレーム句■■■
■フレーム句の構文とフレームのイメージ■
・構文
ROW BETWEEN <開始点> AND <終了点>
・使える開始点、終了点の指定
・CURRENT ROW - 現在行
・<n> PRECEDING - 現在行より<n>行前、RANGEの場合は<n>値前
・<n> FOLLOWING - 現在行より<n>行後、RANGEの場合は<n>値後
・UNBOUNDED PRECECING - ウィンドウの先頭の行（終了点では使えない）
・UNBOUNDED FOLLOWING - ウィンドウの末尾の行（開始点では使えない）
■フレームで移動平均を求める■
移動平均：時系列データにおいて、ある一定区間ごとの平均値を区間をずらしながら求めたもの。
例）直前の２つの購買履歴を含めた３履歴分の購入価格の移動平均を求める。
・item_purchase_log_dateテーブル
item_name|item_category|action_date|price|
---------|-------------|-----------|-----|
コーヒー |beverage | 2019-04-29| 280|
緑茶 |food | 2019-04-29| 280|
緑茶 |food | 2019-04-30| 200|
コーヒー |beverage | 2019-04-30| 750|
砂糖 |food | 2019-04-30| 200|
コーヒー |beverage | 2019-04-30| 750|
メイプルシロップ |food | 2019-05-01| 450|
メイプルシロップ |food | 2019-05-02| 450|
ティーカップ |zakka | 2019-05-03| 980|
砂糖 |food | 2019-05-04| 280|
・SQL
SELECT
item_category,
item_name,
action_date,
price,
AVG(price) OVER(
ORDER BY action_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg
FROM
item_purchase_log_date;
・結果
item_category|item_name|action_date|price|moving_avg|
-------------|---------|-----------|-----|----------|
beverage |コーヒー | 2019-04-29| 280| 280.0000|
food |緑茶 | 2019-04-29| 280| 280.0000|
food |緑茶 | 2019-04-30| 200| 253.3333|
beverage |コーヒー | 2019-04-30| 750| 410.0000|
food |砂糖 | 2019-04-30| 200| 383.3333|
beverage |コーヒー | 2019-04-30| 750| 566.6667|
food |メイプルシロップ | 2019-05-01| 450| 466.6667|
food |メイプルシロップ | 2019-05-02| 450| 550.0000|
zakka |ティーカップ | 2019-05-03| 980| 626.6667|
food |砂糖 | 2019-05-04| 280| 570.0000|
例）フレーム句で開始点 / 終了点となる行を指定しない場合（CURRENT ROWを指定したことになる）
・開始点のみ指定したSQL（終了点は自動的にCURRENT ROWになる）
SELECT
item_category,
item_name,
action_date,
price,
AVG(price) OVER(
ORDER BY action_date
ROWS 2 PRECEDING
) AS moving_avg
FROM
item_purchase_log_date;
・結果
上記例の結果と同じ。
例）現在の行とその前後の行から、３行分の購入価格の移動平均を求める。
・SQL
SELECT
item_category,
item_name,
action_date,
price,
AVG(price) OVER(
ORDER BY action_date
ROWS
BETWEEN 1 PRECEDING AND 1 FOLLOWING
) AS moving_avg
FROM
item_purchase_log_date;
・結果
item_category|item_name|action_date|price|moving_avg|
-------------|---------|-----------|-----|----------|
beverage |コーヒー | 2019-04-29| 280| 280.0000|
food |緑茶 | 2019-04-29| 280| 253.3333|
food |緑茶 | 2019-04-30| 200| 410.0000|
beverage |コーヒー | 2019-04-30| 750| 383.3333|
food |砂糖 | 2019-04-30| 200| 566.6667|
beverage |コーヒー | 2019-04-30| 750| 466.6667|
food |メイプルシロップ | 2019-05-01| 450| 550.0000|
food |メイプルシロップ | 2019-05-02| 450| 626.6667|
zakka |ティーカップ | 2019-05-03| 980| 570.0000|
food |砂糖 | 2019-05-04| 280| 630.0000|
■フレームで直近の日付を求める■
フレーム句により、「最新の日付の行」「スコアの最も高い行」「スコアが下から２番目の行」など簡単に指定できる。
例）各行の直前の購入日を求める
・SQL
SELECT
action_date AS current_action_date,
MIN(action_date) OVER(
ORDER BY
action_date ASC
ROWS
BETWEEN 1 PRECEDING AND 1 PRECEDING
) AS latest_date
FROM
item_purchase_log_date;
・結果
current_action_date|latest_date|
-------------------|-----------|
2019-04-29| |
2019-04-29| 2019-04-29|
2019-04-30| 2019-04-29|
2019-04-30| 2019-04-30|
2019-04-30| 2019-04-30|
2019-04-30| 2019-04-30|
2019-05-01| 2019-04-30|
2019-05-02| 2019-05-01|
2019-05-03| 2019-05-02|
2019-05-04| 2019-05-03|
例）各行の直近の購入日とその時の購入価格
・SQL
SELECT
action_date AS current_action_date,
price AS current_price,
MIN(action_date) OVER(
ORDER BY
action_date ASC
ROWS
BETWEEN 1 PRECEDING AND 1 PRECEDING
) AS latest_date,
MIN(price) OVER(
ORDER BY
action_date ASC
ROWS
BETWEEN 1 PRECEDING AND 1 PRECEDING
) AS latest_price
FROM
item_purchase_log_date;
・結果
current_action_date|current_price|latest_date|latest_price|
-------------------|-------------|-----------|------------|
2019-04-29| 280| | |
2019-04-29| 280| 2019-04-29| 280|
2019-04-30| 200| 2019-04-29| 280|
2019-04-30| 750| 2019-04-30| 200|
2019-04-30| 200| 2019-04-30| 750|
2019-04-30| 750| 2019-04-30| 200|
2019-05-01| 450| 2019-04-30| 750|
2019-05-02| 450| 2019-05-01| 450|
2019-05-03| 980| 2019-05-02| 450|
2019-05-04| 280| 2019-05-03| 980|
■RANGEによる値単位での行指定■
フレーム句では、行単位での指定を行うROWSの他に、カラムの値単位での指定が可能がRANGEがある。
例）各行の日付より古い中で最新の購入日と購入価格（日にちが同じ場合はその中での最小価格）を求める。
・SQL
SELECT
action_date AS current_action_date,
price AS current_action_price,
MIN(action_date) OVER(
ORDER BY
action_date ASC
RANGE
BETWEEN INTERVAL '1' DAY PRECEDING
AND INTERVAL '1' DAY PRECEDING
) AS day1_before,
MIN(price) OVER(
ORDER BY
action_date ASC
RANGE
BETWEEN INTERVAL '1' DAY PRECEDING
AND INTERVAL '1' DAY PRECEDING
) AS price_day1_before
FROM
item_purchase_log_date;
・結果
current_action_date|current_action_price|day1_before|price_day1_before|
-------------------|--------------------|-----------|-----------------|
2019-04-29| 280| | |
2019-04-29| 280| | |
2019-04-30| 200| 2019-04-29| 280|
2019-04-30| 750| 2019-04-29| 280|
2019-04-30| 200| 2019-04-29| 280|
2019-04-30| 750| 2019-04-29| 280|
2019-05-01| 450| 2019-04-30| 200|
2019-05-02| 450| 2019-05-01| 450|
2019-05-03| 980| 2019-05-02| 450|
2019-05-04| 280| 2019-05-03| 980|
0コメント