LoginSignup
0
0

More than 1 year has passed since last update.

@zgoovw_(ゆうき)

MySQL 関数と応用

Last updated at Posted at 2021-07-07

関連記事

集計関数

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より上のレコード

条件分岐

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'のレコードだけのテーブルを作ります。

CREATE TABLE books_Akutagawa AS SELECT * FROM books WHERE author = 'Akutagawa';

VIEW

CREATE TABLEで取得結果を切り出した場合、元のテーブルに変更があったても影響がありません。
CREATE VIEWで作成したテーブルでは元のテーブルを参照し続けているため元のテーブルの変更を反映します。

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

UNION ALL

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

(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       |
-- +-------+----------+

サブクエリ

特定の条件のレコードを取得

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

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

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の平均を計算

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として、その平均値を取得している。

-- 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関数

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を使うと任意のレコードに分割した処理をすることができます。

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 ()で指定して使い回すことができます。

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の先頭から対象のレコードまでの累計になります。 

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 行後
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,３...と割り振られる。同数の場合、同じ順位でその次の順位は飛ばす。
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との差異を取得。

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を取得します。

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が取得されます。
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0