- Looker から Snowflake への OAuth 接続
- Looker (Google Cloud core) 30日間 フリートライアル設定
- AWS 落穂拾い (Security)
- AWS 落穂拾い (Storage)
- マイクロマウス
概要
運用時などに必要になるけれど、使用頻度が低く理解が曖昧になりがちな 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 |
+------+------+--------------------------------------------+
各ウィンドウにおけるフレームは、既定でウィンドウ内の最初の行から現在行までですが、これを変更することができます。PRECEDING と FOLLOWING の片方だけ指定する場合は 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 |
+------+------+-----------------------------------------------------+
