理解が曖昧になりがちだけど運用上重要な SQL 文法をもう一度
[履歴] [最終更新] (2019/02/22 01:34:08)
1
作品
410
技術情報
最近の投稿
ここは
趣味の電子工作を楽しむ人のためのハードウェア情報共有サイト

技術情報や作品の投稿機能、リアルタイム遠隔操作 API をご利用いただけます。
新着作品

概要

運用時などに必要になるけれど、使用頻度が低く理解が曖昧になりがちな SQL をチートシート化しておきます。

重複を省いて SELECT (distinct)

SELECT 時

SELECT DISTINCT columnname FROM tablename

COUNT との併用

SELECT COUNT(DISTINCT columnname) FROM tablename

同じようなテーブルを結合してから SELECT (union)

サブクエリの結果を結合して、一時的なテーブルを作成する際に重宝します。

  • UNION → 重複レコードは取得しない
  • UNION ALL → 重複レコードも取得する

UNION ALL で同じようなテーブルを結合して columnname の個数を集計する例

SELECT columnname, COUNT(*) FROM
  (
   SELECT columnname FROM tablename1 UNION ALL
   SELECT columnname FROM tablename2
   ) AS cname
  GROUP BY columnname

テーブルを結合 (SELECT 結果を INSERT)

tablename2 と tablename3 を結合して、何らかの用途で必要になった tablename1 を作成する例

INSERT INTO tablename1 (SELECT columnname1, columnnam2 FROM tablename2)
INSERT INTO tablename1 (SELECT columnname1, columnnam2 FROM tablename3)

特定の GROUP のみを表示

レコードは WHERE で条件指定しますが、グループ (GROUP BY した結果) は HAVING で条件指定します。

SELECT columnname, COUNT(*) FROM tablename GROUP BY columnname HAVING COUNT(*) > 1

JOIN の使い分け

tablename1

a 1
b 1
c 2

tablename2

1 D
3 E

LEFT JOIN

tablename1 に tablename2 を左外部結合 (LEFT JOIN、LEFT OUTER JOIN)

a 1 D
b 1 D
c 2 NULL

RIGHT JOIN

tablename1 に tablename2 を右外部結合 (RIGHT JOIN、RIGHT OUTER JOIN)

a    1 D
b    1 D
NULL 3 E

FULL OUTER JOIN

tablename1 と tablename2 を完全外部結合 (FULL OUTER JOIN)

a    1 D
b    1 D
c    2 NULL
NULL 3 E

INNER JOIN

tablename1 と tablename2 を内部結合 (INNER JOIN)

a 1 D
b 1 D

単に JOIN と記載すると INNER JOIN になります。結合される二つのテーブルのどちらかにしか存在しないレコードは抽出されません。

CROSS JOIN

外積と内積に加えて直積 (CROSS JOIN) もありますが、実運用で使用することはあまりありません。直積の性質上、外積や内積の場合と異なり ON などで結合条件を指定しません。

SELECT * FROM tablename1 AS t1 CROSS JOIN tablename2 AS t2

結合結果のレコード数は「tablename1 のレコード数 * tablename2 のレコード数」となります。

a 1 1 D
a 1 3 E
b 1 1 D
b 1 3 E
c 2 1 D
c 2 3 E

補足

同じテーブル同士を結合することもできます。自己結合とよびます。テーブル名を区別できないため AS によるエイリアスが必要です。

SELECT alias1.id, alias2.id FROM tablename AS alias1 LEFT JOIN tablename AS alias2 ON alias1.id = alias2.id

ウィンドウ関数

サンプルデータ

CREATE TABLE tbl (c1 VARCHAR(255), c2 VARCHAR(255));
INSERT INTO tbl (c1, c2) VALUES ('a','a'),('b','b'),('c','c'),('a','aa'),('b','bb'),('c','cc'),('a','aaa'),('b','bbb'),('c','ccc');

OVER に何も指定しないとウィンドウは一つです。

SELECT *, COUNT(*) OVER() FROM tbl;
+------+------+-----------------+
| c1   | c2   | COUNT(*) OVER() |
+------+------+-----------------+
| a    | a    |               9 |
| b    | b    |               9 |
| c    | c    |               9 |
| a    | aa   |               9 |
| b    | bb   |               9 |
| c    | cc   |               9 |
| a    | aaa  |               9 |
| b    | bbb  |               9 |
| c    | ccc  |               9 |
+------+------+-----------------+

ウィンドウを分割するためには PARTITION BY を指定します。

SELECT *, COUNT(*) OVER(PARTITION BY c1) FROM tbl;
+------+------+--------------------------------+
| c1   | c2   | COUNT(*) OVER(PARTITION BY c1) |
+------+------+--------------------------------+
| a    | a    |                              3 |
| a    | aa   |                              3 |
| a    | aaa  |                              3 |
| b    | b    |                              3 |
| b    | bb   |                              3 |
| b    | bbb  |                              3 |
| c    | c    |                              3 |
| c    | cc   |                              3 |
| c    | ccc  |                              3 |
+------+------+--------------------------------+

ウィンドウ内の各行を ORDER BY でソートすると集約対象がウィンドウ全体ではなく現在行までになります。この集約対象をフレームとよびます。

SELECT *, COUNT(*) OVER(PARTITION BY c1 ORDER BY c2) FROM tbl;
+------+------+--------------------------------------------+
| c1   | c2   | COUNT(*) OVER(PARTITION BY c1 ORDER BY c2) |
+------+------+--------------------------------------------+
| a    | a    |                                          1 |
| a    | aa   |                                          2 |
| a    | aaa  |                                          3 |
| b    | b    |                                          1 |
| b    | bb   |                                          2 |
| b    | bbb  |                                          3 |
| c    | c    |                                          1 |
| c    | cc   |                                          2 |
| c    | ccc  |                                          3 |
+------+------+--------------------------------------------+

各ウィンドウにおけるフレームは、既定でウィンドウ内の最初の行から現在行までですが、これを変更することができます。PRECEDINGFOLLOWING の片方だけ指定する場合は BETWEEN を省略できます。また、特殊な指定方法として、ウィンドウ内の先頭行 UNBOUNDED PRECEDING、最終行 UNBOUNDED FOLLOWING、現在行 CURRENT ROW があります。

SELECT *, COUNT(*) OVER(PARTITION BY c1 ORDER BY c2
  ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING) FROM tbl;
SELECT *, COUNT(*) OVER(PARTITION BY c1 ORDER BY c2
  ROWS 1 PRECEDING) FROM tbl;

+------+------+---------------------------------------------------------------------------------------+
| c1   | c2   | COUNT(*) OVER(PARTITION BY c1 ORDER BY c2
  ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING) |
+------+------+---------------------------------------------------------------------------------------+
| a    | a    |                                                                                     1 |
| a    | aa   |                                                                                     2 |
| a    | aaa  |                                                                                     2 |
| b    | b    |                                                                                     1 |
| b    | bb   |                                                                                     2 |
| b    | bbb  |                                                                                     2 |
| c    | c    |                                                                                     1 |
| c    | cc   |                                                                                     2 |
| c    | ccc  |                                                                                     2 |
+------+------+---------------------------------------------------------------------------------------+

代表的なウィンドウ関数

ROW_NUMBER()

ウィンドウ内における連番を付与できます。

SELECT *,
  ROW_NUMBER() OVER(PARTITION BY c1 ORDER BY c2 DESC)
FROM tbl
ORDER BY c2;

+------+------+-----------------------------------------------------+
| c1   | c2   | ROW_NUMBER() OVER(PARTITION BY c1 ORDER BY c2 DESC) |
+------+------+-----------------------------------------------------+
| a    | a    |                                                   3 |
| a    | aa   |                                                   2 |
| a    | aaa  |                                                   1 |
| b    | b    |                                                   3 |
| b    | bb   |                                                   2 |
| b    | bbb  |                                                   1 |
| c    | c    |                                                   3 |
| c    | cc   |                                                   2 |
| c    | ccc  |                                                   1 |
+------+------+-----------------------------------------------------+

RANK()、DENSE_RANK()

ウィンドウ内における ORDER BY でソートしたときの順位を付与できます。以下では PARTITION BY を指定していないためウィンドウは一つです。DENSE_RANK() は同順を詰めて順位付けします。

SELECT *,
  RANK() OVER(ORDER BY c1),
  DENSE_RANK() OVER(ORDER BY c1)
FROM tbl;

+------+------+--------------------------+--------------------------------+
| c1   | c2   | RANK() OVER(ORDER BY c1) | DENSE_RANK() OVER(ORDER BY c1) |
+------+------+--------------------------+--------------------------------+
| a    | a    |                        1 |                              1 |
| a    | aa   |                        1 |                              1 |
| a    | aaa  |                        1 |                              1 |
| b    | b    |                        4 |                              2 |
| b    | bb   |                        4 |                              2 |
| b    | bbb  |                        4 |                              2 |
| c    | c    |                        7 |                              3 |
| c    | cc   |                        7 |                              3 |
| c    | ccc  |                        7 |                              3 |
+------+------+--------------------------+--------------------------------+

OVER が複数ある場合は WINDOW で作成したエイリアスを利用するとクエリが綺麗になります。

SELECT *,
  RANK() OVER(w),
  DENSE_RANK() OVER(w)
FROM tbl
WINDOW
  w AS (ORDER BY c1);

RANK() の応用例として、各ウィンドウ内で一位の行を抜き出すことができます。

SELECT t.* FROM
  (SELECT *,
    RANK() OVER(w) AS rnk,
    DENSE_RANK() OVER(w) AS drnk
  FROM tbl
  WINDOW
    w AS (ORDER BY c1)) AS t
WHERE
  t.rnk = 1;

+------+------+-----+------+
| c1   | c2   | rnk | drnk |
+------+------+-----+------+
| a    | a    |   1 |    1 |
| a    | aa   |   1 |    1 |
| a    | aaa  |   1 |    1 |
+------+------+-----+------+

LAG()、LEAD()、FIRST_VALUE()、LAST_VALUE()、NTH_VALUE()

ウィンドウ内の現在行から前 LAG()LEAD() に1行ずれた行の値を取得できます。

SELECT *,
  LAG(c2, 1) OVER(w),
  LEAD(c2, 1) OVER(w)
FROM tbl
WINDOW
  w AS (PARTITION BY c1 ORDER BY c2);

+------+------+--------------------+---------------------+
| c1   | c2   | LAG(c2, 1) OVER(w) | LEAD(c2, 1) OVER(w) |
+------+------+--------------------+---------------------+
| a    | a    | NULL               | aa                  |
| a    | aa   | a                  | aaa                 |
| a    | aaa  | aa                 | NULL                |
| b    | b    | NULL               | bb                  |
| b    | bb   | b                  | bbb                 |
| b    | bbb  | bb                 | NULL                |
| c    | c    | NULL               | cc                  |
| c    | cc   | c                  | ccc                 |
| c    | ccc  | cc                 | NULL                |
+------+------+--------------------+---------------------+

ウィンドウ内の最初の行、最後の行、ある指定した行の値を取得できます。

SELECT *,
  FIRST_VALUE(c2) OVER(w),
  LAST_VALUE(c2) OVER(w),
  NTH_VALUE(c2, 2) OVER(w)
FROM tbl
WINDOW
  w AS (PARTITION BY c1 ORDER BY c2);

+------+------+-------------------------+------------------------+--------------------------+
| c1   | c2   | FIRST_VALUE(c2) OVER(w) | LAST_VALUE(c2) OVER(w) | NTH_VALUE(c2, 2) OVER(w) |
+------+------+-------------------------+------------------------+--------------------------+
| a    | a    | a                       | aaa                    | aa                       |
| a    | aa   | a                       | aaa                    | aa                       |
| a    | aaa  | a                       | aaa                    | aa                       |
| b    | b    | b                       | bbb                    | bb                       |
| b    | bb   | b                       | bbb                    | bb                       |
| b    | bbb  | b                       | bbb                    | bb                       |
| c    | c    | c                       | ccc                    | cc                       |
| c    | cc   | c                       | ccc                    | cc                       |
| c    | ccc  | c                       | ccc                    | cc                       |
+------+------+-------------------------+------------------------+--------------------------+
関連ページ
    概要 こちらのページで使い方を把握した MyBatis を、こちらのページで使い方を把握した Spring Boot で利用するための基本的な設定およびサンプルコードをまとめます。サンプルコードにおいては、特に MySQL を対象とします。 MyBatis Spring-Boot-Starter チュートリアル