はじめに
前回の続きです
色々なwindow関数
一通りリストで書いて、後ほど一つづつ説明していきます。
const, max, min, sum, avgのおなじみの関数の説明は省きます。
| 関数名 | 説明 |
|---|---|
| count | 行数をカウント |
| max | 最大値 |
| min | 最小値 |
| sum | 合計値 |
| avg | 平均値 |
| row_number | 行番号 |
| rank | 順位づけ(同着後に飛ばす) |
| dense_rank | 順位づけ(同着後に飛ばさない) |
| Lag | 前の行の値 |
| Lead | 後ろの行の値 |
| First_Value | 指定したソートキーでの最初の行の値 |
| Last_Value | 指定したソートキーでの最後の行の値 |
row_number
初めの行から現在の行数を返します。
SELECT product_name, amount, date, row_number()
OVER() as number
FROM product_orders;
結果
+--------------+--------+------------+------------+
| product_name | amount | date | row_bumber |
+--------------+--------+------------+------------+
| banana | 3 | 2019-03-01 | 1 |
| banana | 2 | 2019-04-01 | 2 |
| banana | 4 | 2019-05-01 | 3 |
| apple | 8 | 2019-03-01 | 4 |
| apple | 2 | 2019-04-01 | 5 |
| apple | 5 | 2019-05-01 | 6 |
| orange | 4 | 2019-03-01 | 7 |
| orange | 1 | 2019-04-01 | 8 |
| orange | 3 | 2019-05-01 | 9 |
+--------------+--------+------------+------------+
PARTITION BYで区切ったら分割されたwindowごとに行数を返します。
SELECT product_name, amount, date, Row_Number()
OVER(
PARTITION BY product_name
) as sum_amount
FROM product_orders;
+--------------+--------+------------+------------+
| product_name | amount | date | sum_amount |
+--------------+--------+------------+------------+
| apple | 8 | 2019-03-01 | 1 |
| apple | 2 | 2019-04-01 | 2 |
| apple | 5 | 2019-05-01 | 3 |
| banana | 3 | 2019-03-01 | 1 |
| banana | 2 | 2019-04-01 | 2 |
| banana | 4 | 2019-05-01 | 3 |
| orange | 4 | 2019-03-01 | 1 |
| orange | 1 | 2019-04-01 | 2 |
| orange | 3 | 2019-05-01 | 3 |
+--------------+--------+------------+------------+
rank, dense_rank
どちらも現在の行の順位を返す関数です。
違いは一つ上の順位のものが複数あった場合に、rankは次の順位を飛ばし、dense_rankは飛ばしません。
実際にみてみましょう。
新しいテーブルを作ります。
CREATE TABLE stocks (
product_name varchar(255),
amount int
);
INSERT INTO stocks VALUES
('banana', 3),
('orange', 2),
('apple', 4),
('grape', 8),
('pineapple', 2),
('peach', 5),
('pear', 8),
('melon', 4);
rank
商品の個数が多い順に並び替えます。
SELECT product_name, amount, rank()
OVER(
order by amount DESC
) as grade
FROM stocks;
結果
+--------------+--------+-------+
| product_name | amount | grade |
+--------------+--------+-------+
| grape | 8 | 1 |
| pear | 8 | 1 |
| peach | 5 | 3 |
| apple | 4 | 4 |
| melon | 4 | 4 |
| banana | 3 | 6 |
| orange | 2 | 7 |
| pineapple | 2 | 7 |
+--------------+--------+-------+
peachやbananaを見ていただければわかると思いますが、一つ上の順位が2つあるのでpeachは3位、bananaは6位になっています。
次にdense_rankを試してみます。
SELECT product_name, amount, dense_rank()
OVER(
order by amount DESC
) as grade
FROM stocks;
結果
+--------------+--------+-------+
| product_name | amount | grade |
+--------------+--------+-------+
| grape | 8 | 1 |
| pear | 8 | 1 |
| peach | 5 | 2 |
| apple | 4 | 3 |
| melon | 4 | 3 |
| banana | 3 | 4 |
| orange | 2 | 5 |
| pineapple | 2 | 5 |
+--------------+--------+-------+
rankと違い、同着後のgradeも連番になっていることがわかります。
lag, lead
lagは前の行の指定した値を返します。
下のクエリは一つ前のフルーツのamountを返すクエリです。
SELECT product_name, amount, lag(amount)
OVER(
order by amount DESC
) as before_amount
FROM stocks;
結果
+--------------+--------+---------------+
| product_name | amount | before_amount |
+--------------+--------+---------------+
| grape | 8 | NULL |
| pear | 8 | 8 |
| peach | 5 | 8 |
| apple | 4 | 5 |
| melon | 4 | 4 |
| banana | 3 | 4 |
| orange | 2 | 3 |
| pineapple | 2 | 2 |
+--------------+--------+---------------+
こんな感じになります。
現在の行のフルーツのamountが次の行のbefore_amountになっています。
grapeは前の行がないのでnullを返しています。
一方でleadはというと、
SELECT product_name, amount, lead(amount)
OVER(
order by amount DESC
) as before_amount
FROM stocks;
+--------------+--------+---------------+
| product_name | amount | before_amount |
+--------------+--------+---------------+
| grape | 8 | 8 |
| pear | 8 | 5 |
| peach | 5 | 4 |
| apple | 4 | 4 |
| melon | 4 | 3 |
| banana | 3 | 2 |
| orange | 2 | 2 |
| pineapple | 2 | NULL |
+--------------+--------+---------------+
結果はlagの逆になります。
また、この二つの関数は第二引数に数字を取ることによってその分だけ前もしくは後ろの行の値を取りことができます。
SELECT product_name, amount, lag(amount, 2)
OVER(
order by amount DESC
) as before_amount
FROM stocks;
+--------------+--------+---------------+
| product_name | amount | before_amount |
+--------------+--------+---------------+
| grape | 8 | NULL |
| pear | 8 | NULL |
| peach | 5 | 8 |
| apple | 4 | 8 |
| melon | 4 | 5 |
| banana | 3 | 4 |
| orange | 2 | 4 |
| pineapple | 2 | 3 |
+--------------+--------+---------------+
first_value, last_value
指定したソートキーでの最初もしくは最後の行の値を返します。
SELECT product_name, amount, date,first_value(amount)
OVER(
PARTITION BY product_name
order by amount DESC
) as before_amount
FROM product_orders;
+--------------+--------+------------+---------------+
| product_name | amount | date | before_amount |
+--------------+--------+------------+---------------+
| apple | 8 | 2019-03-01 | 8 |
| apple | 5 | 2019-05-01 | 8 |
| apple | 2 | 2019-04-01 | 8 |
| banana | 4 | 2019-05-01 | 4 |
| banana | 3 | 2019-03-01 | 4 |
| banana | 2 | 2019-04-01 | 4 |
| orange | 4 | 2019-03-01 | 4 |
| orange | 3 | 2019-05-01 | 4 |
| orange | 1 | 2019-04-01 | 4 |
+--------------+--------+------------+---------------+
参考にしたサイト
第89回 WINDOW関数を使ってみる MySQL道普請便り
【MySQL8】【新機能】Window関数がMySQLでも使えるようになったよ。
MySQL王国に黒船（Window関数）がやってきた！