はじめに
先日Udemyでmysqlの基礎講座を受けてSQLの基本的な構文はある程度かけるようになった者です。
次は下の本を読んでもっと勉強するぞー！と意気込んでいましたが、第1章でwindow関数というみたことのないSQLに出会って早速つまづきました...
達人に学ぶ SQL徹底指南書 (CodeZine BOOKS)
そこで今回はwindow関数について色々調べてみたのでまとめていきたいなーと思います。
window関数とは？
簡単に言いますと、集合関数と同じ集計動作をそれぞれの行に制限範囲で実行するものです。
集約関数とは、GroupBy句で区切られた集合に対しての処理で、「分割」と「集約」をやっています。
一方でwidow関数は分割のみ行って集約はやりません。
どういうことか簡単なテーブルを用いて解説していきます。
まず適当にテーブルを作り、レコードを入れます。
CREATE TABLE product_orders (
product_name varchar(255),
amount int,
date DATE
);
INSERT INTO product_orders VALUES
('banana', 3, '2019-03-01'),
('banana', 2, '2019-04-01'),
('banana', 4, '2019-05-01'),
('apple', 8, '2019-03-01'),
('apple', 2, '2019-04-01'),
('apple', 5, '2019-05-01'),
('orange', 4, '2019-03-01'),
('orange', 1, '2019-04-01'),
('orange', 3, '2019-05-01');
集約関数とwindow関数の比較
まずは普通にGroup By句で集約してみましょう。
SELECT product_name, sum(amount)
FROM product_orders
group by product_name;
結果
+--------------+-------------+
| product_name | sum(amount) |
+--------------+-------------+
| banana | 9 |
| apple | 15 |
| orange | 8 |
+--------------+-------------+
はい。特に不思議なことはありませんね。
group byでproduct_nameをグループ化し、そのamountの合計を出力しています。
次にwindow関数を使ってみましょう。
SELECT product_name, sum(amount)
OVER(
PARTITION BY product_name
) as amount
FROM product_orders;
これがwindow関数の基本形です。
関数() OVER()の形をとります。
OVER()の中にあるPARTITION BYは分割するカラムを指定します。
これを実行した結果がこちらです
+--------------+--------+
| product_name | amount |
+--------------+--------+
| apple | 15 |
| apple | 15 |
| apple | 15 |
| banana | 9 |
| banana | 9 |
| banana | 9 |
| orange | 8 |
| orange | 8 |
| orange | 8 |
+--------------+--------+
group by句を使ったクエリとの出力結果の違いはすぐにわかりますね。
group by句と同じく、amountはそのフルーツの売れた個数ですが、出力結果はproduct_nameを1つに集約しているのに対し、こちらは集約していません。
これがはじめに言った違いの、集約関数は「分割」と「集約」を行い、window関数は「分割」のみ行うということです。
over句について
over句はPartition By , Order By, Frameの三つの方法で集計の範囲指定をすることができます。
一つづつみていきましょう
Partition By
上でちょろっと説明しましたね。
Windowをどのように分けるのかを指定します。
集合関数でいうGroup Byと同じような動きをします。
指定しなければWindowで区切られない状態(つまり全体)で関数が実行されます。
Order By
従来のOrder By句はASCやDESCで出力結果を昇順や降順に並び替えるものとして使っていましたが、window関数のOrder By句は少し違います。
window関数のOrder By句は、指定したキーで並び替え、初めの行から現在の行までを集計します。
実際にクエリを叩いてみましょう。
SELECT product_name, amount, date,sum(amount)
OVER(
PARTITION BY product_name
ORDER BY date
) as sum_amount
FROM product_orders;
結果
+--------------+--------+------------+------------+
| product_name | amount | date | sum_amount |
+--------------+--------+------------+------------+
| apple | 8 | 2019-03-01 | 8 |
| apple | 2 | 2019-04-01 | 10 |
| apple | 5 | 2019-05-01 | 15 |
| banana | 3 | 2019-03-01 | 3 |
| banana | 2 | 2019-04-01 | 5 |
| banana | 4 | 2019-05-01 | 9 |
| orange | 4 | 2019-03-01 | 4 |
| orange | 1 | 2019-04-01 | 5 |
| orange | 3 | 2019-05-01 | 8 |
+--------------+--------+------------+------------+
こんな感じになります。
sum_amountに注目してください。
何が起きているかというと、PARTITION BYでproduct_nameごとに集計をしていて、各product_nameの1行目は1行目のみ、2行目は1行目との合計、3行目は2行目までの合計との合計を出力しています。
まとめ
長くなるので2回に分けます。
第二回では色々なwindow関数の使い方について書いていきたいなーと思います。
参考にしたサイト
第89回 WINDOW関数を使ってみる MySQL道普請便り
【MySQL8】【新機能】Window関数がMySQLでも使えるようになったよ。
MySQL王国に黒船（Window関数）がやってきた！