MySQL 関数と応用

###関連記事

- [MySQL 基礎コマンド](https://qiita.com/zgoovw_/items/2bd571fa38773de4f992)
- [MySQL 正規化と整合性とINDEX](https://qiita.com/zgoovw_/items/542b98ca1d2b74ff84d4)
- [MySQL CTE](https://qiita.com/zgoovw_/items/baa2d062cd0ef7203dab)

#集計関数

```sql
CREATE TABLE books (
  PRIMARY KEY (id),
  id INT NOT NULL AUTO_INCREMENT,
  title CHAR(100), 
  author CHAR(100),
  likes INT
);

INSERT INTO books (title, author, likes) VALUES
  ('Good-bye', 'Dazai', 144),
  ('Kappa', 'Akutagawa', 123),
  ('Botchan', 'Natsume', 105),
  ('Kumonoito', 'Akutagawa', 96),
  ('Kokoro', 'Natsume', 110),
  ('Hashiremeros', 'Dazai', 82);


SELECT SUM(likes) FROM books; -- likesの合計
SELECT AVG(likes) FROM books; -- likesの平均
SELECT MAX(likes) FROM books; -- likesの最大値
SELECT MIN(likes) FROM books; -- likesの最小値
SELECT COUNT(カラム名) FROM books -- レコードの総数、引数がカラム名になる
SELECT DISTINCT author FROM books; -- authorの重複を省いて取得
SELECT author, SUM(likes) FROM books GROUP BY author; -- author毎のlikesの合計
SELECT author, SUM(likes) FROM books WHERE likes > 110 GROUP BY author; -- likesが110より上のレコードのauthor毎の合計
SELECT author, SUM(likes) FROM books GROUP BY author HAVING SUM(likes) > 110; -- author毎のlikesの合計が110より上のレコード
```

#条件分岐

```sql
SELECT
  *, -- likesが１１０以上ならbest、それ以外はgood、カラム名はpopularity
  IF(likes >= 110, 'best', 'good') AS popularity
FROM 
  books;

SELECT
  *,
  CASE -- 複数条件
    WHEN likes >= 120 THEN 'best'
    WHEN likes >= 100 THEN 'good'
    ELSE 'not good'
  END AS popularity
FROM 
  books;
```

#取得結果を別テーブルで切り出す
authorが'Akutagawa'のレコードだけのテーブルを作ります。

```sql
CREATE TABLE books_Akutagawa AS SELECT * FROM books WHERE author = 'Akutagawa';
```
#VIEW
CREATE TABLEで取得結果を切り出した場合、元のテーブルに変更があったても影響がありません。
CREATE VIEWで作成したテーブルでは元のテーブルを参照し続けているため元のテーブルの変更を反映します。

```sql
-- 元のテーブルbooksで'Kumonoito'のlikesが+1されると、こちらの'Kumonoito'のlikesも+1されます。
CREATE TABLE books_Akutagawa_view AS SELECT * FROM books WHERE author = 'Akutagawa';
```

#UNION ALL
複数の取得結果をまとめる。
全てのSELECTのカラム数が同じでないといけない。
またそれぞれのSELECTのカラムが違う場合、最初のSELECTのカラム名が使われる。

```sql
(SELECT id, title FROM books WHERE likes >= 100)
UNION ALL
(SELECT author, likes FROM books ORDER BY likes LIMIT 1);
-- 結果
-- +-------+----------+
-- | id    | title    |
-- +-------+----------+
-- | 1     | Good-bye |
-- | 2     | Kappa    |
-- | 3     | Botchan  |
-- | 5     | Kokoro   |
-- | Dazai | 82       |
-- +-------+----------+
```

#サブクエリ
###特定の条件のレコードを取得

```sql
SELECT
  *
FROM
  books
WHERE -- likesが最小のレコードを取得
  likes = (SELECT MIN(likes) FROM books);
```

###特定のカラムの平均を計算する

```sql
SELECT 
  *, -- avgにlikesの平均を取得
  (SELECT AVG(likes) FROM books) AS avg 
FROM 
  books;
-- 結果
-- +----+--------------+-----------+-------+----------+
-- | id | title        | author    | likes | avg      |
-- +----+--------------+-----------+-------+----------+
-- |  1 | Good-bye     | Dazai     |   144 | 110.0000 |
-- |  2 | Kappa        | Akutagawa |   123 | 110.0000 |
-- |  3 | Botchan      | Natsume   |   105 | 110.0000 |
-- |  4 | Kumonoito    | Akutagawa |    96 | 110.0000 |
-- |  5 | Kokoro       | Natsume   |   110 | 110.0000 |
-- |  6 | Hashiremeros | Dazai     |    82 | 110.0000 |
-- +----+--------------+-----------+-------+----------+
```

#相関サブクエリ
author毎のlikesの平均を計算

```sql
SELECT 
  *, 
  (SELECT AVG(likes) FROM books) AS avg,
  (SELECT AVG(likes) FROM books AS t2 WHERE t1.author = t2.author) AS author_avg
FROM 
  books AS t1;
-- 結果
-- +----+--------------+-----------+-------+----------+------------+
-- | id | title        | author    | likes | avg      | author_avg |
-- +----+--------------+-----------+-------+----------+------------+
-- |  1 | Good-bye     | Dazai     |   144 | 110.0000 |   113.0000 |
-- |  2 | Kappa        | Akutagawa |   123 | 110.0000 |   109.5000 |
-- |  3 | Botchan      | Natsume   |   105 | 110.0000 |   107.5000 |
-- |  4 | Kumonoito    | Akutagawa |    96 | 110.0000 |   109.5000 |
-- |  5 | Kokoro       | Natsume   |   110 | 110.0000 |   107.5000 |
-- |  6 | Hashiremeros | Dazai     |    82 | 110.0000 |   113.0000 |
-- +----+--------------+-----------+-------+----------+------------+
```

#特定の取得結果をサブクエリとして扱う
autho毎のlikesの合計をサブクエリ`n`として、その平均値を取得している。

```sql
-- SELECT author, SUM(likes) AS n FROM books GROUP BY author;
-- 結果
-- +-----------+------+
-- | author    | n    |
-- +-----------+------+
-- | Akutagawa |  219 |
-- | Dazai     |  226 |
-- | Natsume   |  215 |
-- +-----------+------+

SELECT 
  AVG(n) 
FROM 
  (SELECT author, SUM(likes) AS n FROM books GROUP BY author) AS t;
-- 結果
-- +----------+
-- | AVG(n)   |
-- +----------+
-- | 220.0000 |
-- +----------+
```

#Window関数

```sql
CREATE TABLE books (
  PRIMARY KEY (id),
  id INT NOT NULL AUTO_INCREMENT,
  title CHAR(100), 
  author CHAR(100),
  likes INT
);

INSERT INTO books (title, author, likes) VALUES
  ('Good-bye', 'Dazai', 144),
  ('Kappa', 'Akutagawa', 123),
  ('Botchan', 'Natsume', 105),
  ('Kumonoito', 'Akutagawa', 96),
  ('Kokoro', 'Natsume', 110),
  ('Hashiremeros', 'Dazai', 82);

SELECT *, AVG(likes) OVER () AS avg FROM books;
-- 結果 　　　　　全てのレコードにlikesの平均をを追加
-- +----+--------------+-----------+-------+----------+
-- | id | title        | author    | likes | avg      |
-- +----+--------------+-----------+-------+----------+
-- |  6 | Hashiremeros | Dazai     |    82 | 110.0000 |
-- |  4 | Kumonoito    | Akutagawa |    96 | 110.0000 |
-- |  3 | Botchan      | Natsume   |   105 | 110.0000 |
-- |  5 | Kokoro       | Natsume   |   110 | 110.0000 |
-- |  2 | Kappa        | Akutagawa |   123 | 110.0000 |
-- |  1 | Good-bye     | Dazai     |   144 | 110.0000 |
-- +----+--------------+-----------+-------+----------+
```

#PARTITION
PARTITIONを使うと任意のレコードに分割した処理をすることができます。


```sql
SELECT *, AVG(likes) OVER (PARTITION BY author) AS avg FROM books;
-- 結果　　　　　　author毎のlikesの平均を取得
-- +----+--------------+-----------+-------+----------+
-- | id | title        | author    | likes | avg      |
-- +----+--------------+-----------+-------+----------+
-- |  2 | Kappa        | Akutagawa |   123 | 109.5000 |
-- |  4 | Kumonoito    | Akutagawa |    96 | 109.5000 |
-- |  6 | Hashiremeros | Dazai     |    82 | 113.0000 |
-- |  1 | Good-bye     | Dazai     |   144 | 113.0000 |
-- |  3 | Botchan      | Natsume   |   105 | 107.5000 |
-- |  5 | Kokoro       | Natsume   |   110 | 107.5000 |
-- +----+--------------+-----------+-------+----------+
```

また、同じPARTITIONを使用した処理が複数ある場合は次のように`WINDOW foo AS ()`で指定して使い回すことができます。

```sql
SELECT
  *, 
  AVG(likes) OVER foo AS author_avg,
  SUM(likes) OVER foo AS author_sum
FROM
 books
WINDOW foo AS (PARTITION BY author);
```

#FRAME
PRTITIONで分割したレコードのかたまりの中で処理をする範囲を指定することができ、この範囲のことを`フレーム`と言います。

まずフレーム内の例です。
次の取得結果では`count_sum`はそれぞれのPRTITIONの先頭から対象のレコードまでの累計になります。 

```sql
CREATE TABLE nums (
  PRIMARY KEY (id),
  id INT NOT NULL AUTO_INCREMENT,
  type CHAR(100), 
  count INT
);

INSERT INTO nums (type, count) VALUES
  ('A', 14),
  ('A', 23),
  ('B', 15),
  ('A', 6),
  ('B', 10),
  ('B', 2),
  ('B', 8),
  ('A', 1);
  
SELECT 
  *, 
  SUM(count) OVER (
    PARTITION BY type 
    ORDER BY count
  ) AS count_sum
FROM 
  nums;
  
-- 結果
-- +----+------+-------+-----------+
-- | id | type | count | count_sum |
-- +----+------+-------+-----------+
-- |  8 | A    |     1 |         1 |
-- |  4 | A    |     6 |         7 |
-- |  1 | A    |    14 |        21 |
-- |  2 | A    |    23 |        44 |
-- |  6 | B    |     2 |         2 |
-- |  7 | B    |     8 |        10 |
-- |  5 | B    |    10 |        20 |
-- |  3 | B    |    15 |        35 |
-- +----+------+-------+-----------+
```

次はフレームを指定した例です。
指定方法は次のように`ROWS ~`という形になります。
`BETWEEN ~` でフレームの開始位置と終了位置の両方を指定できます。

- CURRENT ROW - 現在行
- UNBOUNDED PRECEDING - PRTITIONの先頭
- UNBOUNDED FOLLOWING - PRTITIONの終端
- n PRECEDING - 現在行の n 行前
- n FOLLOWING - 現在行の n 行後

```sql
SELECT 
  *, 
  SUM(count) OVER (
    PARTITION BY type 
    ORDER BY count
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS count_sum
FROM 
  nums;
-- 結果   count_sumは、一つ前のcounto + 現在行のcount + 一つ後のcount
-- +----+------+-------+-----------+
-- | id | type | count | count_sum |  前　　　 　現　　 　　　後
-- +----+------+-------+-----------+
-- |  8 | A    |     1 |         7 |  0 +  1 +  6
-- |  4 | A    |     6 |        21 |  1 +  6 + 14
-- |  1 | A    |    14 |        43 |  6 + 14 + 23
-- |  2 | A    |    23 |        37 | 14 + 23 +  0
-- |  6 | B    |     2 |        10 |  0 +  2 +  8
-- |  7 | B    |     8 |        20 |  2 +  8 + 10
-- |  5 | B    |    10 |        33 |  8 + 10 + 15
-- |  3 | B    |    15 |        25 | 10 + 15 +  0
-- +----+------+-------+-----------+
```

#取得結果に連番をつける

- ROW_NUMBER() - 先頭から1,2,３...と割り振られる。
- DENSE_RANK() - 先頭から1,2,３...と割り振られる。同数の場合、同じ順位。
- RANK() - 先頭から1,2,３...と割り振られる。同数の場合、同じ順位でその次の順位は飛ばす。

```sql
DROP TABLE IF EXISTS nums;
CREATE TABLE nums (
  PRIMARY KEY (id),
  id INT NOT NULL AUTO_INCREMENT,
  type CHAR(100), 
  count INT
);

INSERT INTO nums (type, count) VALUES
  ('A', 8),
  ('A', 2),
  ('B', 15),
  ('A', 6),
  ('B', 10),
  ('B', 2),
  ('B', 8),
  ('A', 1);
  
SELECT
  *,
  ROW_NUMBER() OVER (ORDER BY count) as num,
  DENSE_RANK() OVER (ORDER BY count) as dense,
  RANK() OVER (ORDER BY count) as rank
FROM
  nums;
-- 結果
-- +----+------+-------+-----+-------+------+
-- | id | type | count | num | dense | rank |
-- +----+------+-------+-----+-------+------+
-- |  8 | A    |     1 |   1 |     1 |    1 |
-- |  6 | B    |     2 |   2 |     2 |    2 |
-- |  2 | A    |     2 |   3 |     2 |    2 |
-- |  4 | A    |     6 |   4 |     3 |    4 |
-- |  1 | A    |     8 |   5 |     4 |    5 |
-- |  7 | B    |     8 |   6 |     4 |    5 |
-- |  5 | B    |    10 |   7 |     5 |    7 |
-- |  3 | B    |    15 |   8 |     6 |    8 |
-- +----+------+-------+-----+-------+------+
```

#前後のレコードとの差異を取得

- LAG(T, N) - カラムTのN個前の値を取得
- LEAD(T, N) - カラムTのN個後の値を取得

次の例ではcountからlagを引いて前のcountとの差異を、leadからcountを引いて次のcountとの差異を取得。

```sql
SELECT
  *, 
  LAG(count) OVER (ORDER BY count) as lag,
  LEAD(count) OVER (ORDER BY count) as lead,
  count - LAG(count) OVER (ORDER BY count) as diff_before,
  LEAD(count) OVER (ORDER BY count) - count as diff_after
FROM
  nums;
-- 結果
-- +----+------+-------+------+------+-------------+------------+
-- | id | type | count | lag  | lead | diff_before | diff_after |
-- +----+------+-------+------+------+-------------+------------+
-- |  8 | A    |     1 | NULL |    2 |        NULL |          1 |
-- |  6 | B    |     2 |    1 |    2 |           1 |          0 |
-- |  2 | A    |     2 |    2 |    6 |           0 |          4 |
-- |  4 | A    |     6 |    2 |    8 |           4 |          2 |
-- |  1 | A    |     8 |    6 |    8 |           2 |          0 |
-- |  7 | B    |     8 |    8 |   10 |           0 |          2 |
-- |  5 | B    |    10 |    8 |   15 |           2 |          5 |
-- |  3 | B    |    15 |   10 | NULL |           5 |       NULL |
-- +----+------+-------+------+------+-------------+------------+
```

#最後に追加されたidを取得
`LAST_INSERT_ID()`で最後に追加されたレコードのidを取得します。

```sql
CREATE TABLE articles (
  id INT NOT NULL AUTO_INCREMENT,
  title VARCHAR(140), 
  PRIMARY KEY (id)
);

CREATE TABLE comments (
  id INT NOT NULL AUTO_INCREMENT,
  article_id INT,
  comment VARCHAR(140), 
  PRIMARY KEY (id)
);

INSERT INTO articles (title) VALUES 
  ('foo'),
  ('bar'),
  ('baz');

INSERT INTO comments (article_id, comment) VALUES 
  (1, 'fooは参考になった'),
  (1, 'fooはおもしろい'),
  (3, 'bazは斬新な内容だった');

INSERT INTO articles (title) VALUES 
  ('qux');

INSERT INTO comments (article_id, comment) VALUES 
  (LAST_INSERT_ID(), 'quxはつまらない');

SELECT * FROM articles;
SELECT * FROM comments;

-- 結果
-- +----+-------+
-- | id | title |
-- +----+-------+
-- |  1 | foo   |
-- |  2 | bar   |
-- |  3 | baz   |
-- |  4 | qux   |
-- +----+-------+
-- +----+------------+-----------------------------------+
-- | id | article_id | comment                           |
-- +----+------------+-----------------------------------+
-- |  1 |          1 | fooは参考になった　　　　　　　　　　               |
-- |  2 |          1 | fooはおもしろい　　　　　　　　　　                 |
-- |  3 |          3 | bazは斬新な内容だった　　　　　　　　　　　           |
-- |  4 |          4 | quxはつまらない　　　　　                   　|
-- +----+------------+-----------------------------------+

-- 最後に複数のデータをまとめて追加していた場合、まとめて追加した先頭のidが取得されます。
```