🔖
【PostgreSQL】ウインドウ関数について
準備
sales
| id | product | sale_date | amount |
|---|---|---|---|
| 1 | A | 2024-02-01 | 1000 |
| 2 | A | 2024-02-02 | 1500 |
| 3 | A | 2024-02-03 | 1200 |
| 4 | B | 2024-02-01 | 2000 |
| 5 | B | 2024-02-02 | 1800 |
-- sales テーブルの作成
CREATE TABLE sales (
id SERIAL PRIMARY KEY,
product VARCHAR(10) NOT NULL,
sale_date DATE NOT NULL,
amount INT NOT NULL
);
-- データの挿入
INSERT INTO sales (product, sale_date, amount) VALUES
('A', '2024-02-01', 1000),
('A', '2024-02-02', 1500),
('A', '2024-02-03', 1200),
('B', '2024-02-01', 2000),
('B', '2024-02-02', 1800);
ウィンドウ関数とは
SQLの集計関数（SUM, AVG, COUNT など）を使いながらも、元の行を保持したまま計算を行うことが出来る機能。
特徴
SQLの集計関数(SUM, AVG, COUNT など)を使いながらも、元の行を保持したまま計算を行うことが出来る(結果行の集約を行わない)
基本構文
ウィンドウ関数名(列名) OVER (PARTITION BY 分け方 ORDER BY 並び順)
- ウィンドウ関数名：SUM, AVG, ROW_NUMBER, RANK など
- OVER：各行を残しますよという指示
- PARTITION BY：ここで指定したグループ(例：商品)ごとに集計などが行われる
- ORDER BY：並び順に応じて計算を行う(例：日付順)
実験してみた
SUM関数で全データの売上金額の合計を求めてみる
SELECT SUM(amount) FROM sales;
OVER句を追加してみる、()の中には何も記述しない
各行に売上金額の合計が表示されている
SELECT product, sale_date, amount,
SUM(amount) OVER () AS cumulative_sales
FROM sales;
OVER句の()の中にPARTITION BYを記述
商品ごとの売上金額が表示されている(例：A商品の合計は3700円など)
SELECT product,amount, SUM(amount) OVER(PARTITION BY product) AS cumulative_sales
FROM sales;
OVER句の()の中にORDER BY sale_dateを記述してみる
日付ごとの売上金額の累計が表示される
1．2024-02-01の合計→3000円
2．2024-02-02の合計→3300円(3000円+3300円=6300円)
3．2024-02-03の合計→1200円(6300円+1200円→7500円)
SELECT product, sale_date, amount,
SUM(amount) OVER (ORDER BY sale_date) AS cumulative_sales
FROM sales;
各商品の売上を日付順に累積していく
- PARTITION BY product→商品ごとに
- ORDER BY sale_date→sale_dateの順で
- SUM(amount)→金額の合計をして(累計)
SELECT product,sale_date,amount,
SUM(amount) OVER (PARTITION BY product ORDER BY sale_date) AS cumulative_sales
FROM sales;
＜出力例＞
各商品ごとに売上の順位をつける（ランキング）
RANK() 関数を使うと、売上が高い順に順位をつけることが出来る。
SELECT
product,
sale_date,
amount,
RANK() OVER (PARTITION BY product ORDER BY amount DESC) AS ranking
FROM sales;
＜出力例＞
Udemyで講座を公開中！
X(旧Twitter)
Zenn 本
Youtube
Discussion