ウィンドウ関数を使って競馬アプリごとの売上前年比を調べる

環境

MySQL 8.0

テーブル作成

架空の競馬アプリJRA-SANの2018年 ~ 2022年の売上情報を持ったjra_san_salesテーブルを作成します

CREATE TABLE jra_san_sales (
    year VARCHAR(4),
    earnings INTEGER
);

INSERT INTO jra_san_sales VALUES('2022', 200);
INSERT INTO jra_san_sales VALUES('2021', 180);
INSERT INTO jra_san_sales VALUES('2020', 200);
INSERT INTO jra_san_sales VALUES('2019', 100);
INSERT INTO jra_san_sales VALUES('2018', 80);

各、架空の競馬アプリの年間売上情報を持ったhorce_race_app_salesテーブルを作成します

CREATE TABLE horce_race_app_sales (
    name VARCHAR(36),
    year VARCHAR(4),
    earnings INTEGER
);

INSERT INTO horce_race_app_sales VALUES('熱闘 keiba', '2022', 200);
INSERT INTO horce_race_app_sales VALUES('熱闘 keiba', '2021', 180);
INSERT INTO horce_race_app_sales VALUES('熱闘 keiba', '2020', 170);
INSERT INTO horce_race_app_sales VALUES('JRA-SAN', '2022', 150);
INSERT INTO horce_race_app_sales VALUES('JRA-SAN', '2021', 180);
INSERT INTO horce_race_app_sales VALUES('競馬！DO', '2022', 90);
INSERT INTO horce_race_app_sales VALUES('競馬！DO', '2021', 95);

まずは、基本的な使い方から

各行の直近の年を求めてみます。
1行前の年を出力した結果がこちらです

 current_year | latest_year
--------------+-------------
 2018         |
 2019         | 2018
 2020         | 2019
 2021         | 2020
 2022         | 2021

SELECT
    year AS current_year,
    MIN(year) OVER (
        ORDER BY year ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
    ) AS latest_year
FROM jra_san_sales;

OVER句・・・分析関数。行セットのパーティション処理と並べ替えを決定します

分析関数 OVER(ROWS BETWEEN　開始位置 AND 終了位置)・・・フレームを指定します

ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING

上の例では、現在の行の前の1行だけを含むフレームを指定することを意味します
その結果、ウィンドウ関数が現在の行とその前の1行のみを対象として集計を実行するように指定することができます

ちなみに、MIX(earnings)の部分はMAX(earnings)でも同じ結果が得られます

集約関数MINやMAXをしようする理由としては、
フレーム範囲が複数になる場合があるので、集約関数を利用しています。

それぞれのアプリの売上前年比を求める

欲しいデータが以下です。year_on_yearは前年比です

    name    | year | earnings | year_on_year
------------+------+----------+-------------
 JRA-SAN    | 2021 |      180 |
 JRA-SAN    | 2022 |      150 |    -30
 熱闘 keiba  | 2020 |      170 |
 熱闘 keiba  | 2021 |      180 |     10
 熱闘 keiba  | 2022 |      200 |     20
 競馬！DO     | 2021 |       95 |
 競馬！DO     | 2022 |       90 |     -5

SELECT 
    name,
    year,
    earnings,
    earnings - MAX(earnings) OVER (
        PARTITION BY name 
        ORDER BY year
        ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
    ) AS year_on_year
FROM horce_race_app_sales;

• PARTITION BY・・・グループごとに分けて集計を行います。

PARTITION BY name ORDER BY year

ここではPARTITION BYでアプリ名(name)毎に対して、ORDER BYでyearの昇順で並べ替えています

• 分析関数 OVER(PARTITION BY カラム名,・・・)・・・集計範囲を指定しています。

earnings - MAX(earnings) OVER (
        PARTITION BY name 
        ORDER BY year
        ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
    )

その結果、前年比(year_on_year) = 現在の列の売上 - 前年の売上が得れます