Slide 1
Slide 1 text
実際に使うSQLの書き方 徹底解説
～ PostgreSQLのSQL入門 ～
PostgreSQLカンファレンス 2021 - チュートリアル
Slide 2
Slide 2 text
今日は主に参照（SELECT）の話をします
What is it?
Slide 3
Slide 3 text
サンプルを実際に実行しながら説明します
What is it?
Slide 4
Slide 4 text
今日の実際のサンプルはこちら
What is it?
https://github.com/soudai/pgcon21j-tutorial
Slide 5
Slide 5 text
サンプルを実際に
実行しながら説明します
What is it?
Slide 6
Slide 6 text
1. 自己紹介
2. SQLの基本的な知識
3. 分析クエリでよく使う構文
4. 大規模な集計を高速化するコツ
5. まとめ
あじぇんだ
Slide 7
Slide 7 text
1. 自己紹介
2. SQLの基本的な知識
3. 分析クエリでよく使う構文
4. 大規模な集計を高速化するコツ
5. まとめ
あじぇんだ
Slide 8
Slide 8 text
自己紹介
曽根 壮大（37歳）
Have Fun Tech LLC 代表社員
そ ね た け と も
● 日本PostgreSQLユーザ会 勉強会分科会 担当
● 3人の子供がいます（長女、次女、長男）
● 技術的にはWeb/LL言語/RDBMSが好きです
● コミュニティが好き
Slide 9
Slide 9 text
No content
Slide 10
Slide 10 text
本書きました
Slide 11
Slide 11 text
1. 自己紹介
2. SQLの基本的な知識
3. 分析クエリでよく使う構文
4. 大規模な集計を高速化するコツ
5. まとめ
あじぇんだ
Slide 12
Slide 12 text
SQLのことを知る
SQLの基本的な知識
Slide 13
Slide 13 text
SQLの実行順を知れば
SQLのエラーがわかる
SQLの基本的な知識
Slide 14
Slide 14 text
SQLの実行順
SQLの基本的な知識
Slide 15
Slide 15 text
SQLの実行順
1. FROM句
2. ON句
3. JOIN句
4. WHERE句
5. GROUP BY句
6. HAVING句
7. SELECT句
8. DISTINCT句
9. ORDER BY句
10. LIMIT句
1.から順に実行される。
例えばWHERE句が実行され
てからGROUP BY句が実行さ
れるのでWHERE句で直接
SUMはできない。
ORDER BY句はSELECT句の
後に実行されるのでSELECT
句でAS句で指定されたラベル
を利用できる
https://www.postgresql.jp/document/current/html/sql-select.html
Slide 16
Slide 16 text
SQLの実行順
SELECT
hoge.id AS pk
sum(foo.count) AS count
FROM hoge
INNER JOIN foo
ON hoge.id = foo.hoge_id
WHERE
hoge.name LIKE ‘sone%’
GROUP BY hoge.category_id
ORDER BY pk
LIMIT 10
1. FROM句で検索対象を指
定する
2. ON句とJOIN句で条件と
対象を指定する
3. WHERE句で絞り込む
… と続いて行く
https://www.postgresql.jp/document/current/html/sql-select.html
Slide 17
Slide 17 text
SQLの実行順
SELECT
hoge.id AS pk
sum(foo.count) AS count
FROM hoge
INNER JOIN foo
ON hoge.id = foo.hoge_id
WHERE
hoge.name LIKE ‘sone%’
GROUP BY hoge.category_id
ORDER BY pk
LIMIT 10
例えばWHERE句が実行され
てからGROUP BY句が実行さ
れるのでWHERE句で直接
SUMはできない。
ORDER BY句はSELECT句の
後に実行されるのでSELECT
句でAS句で指定されたラベル
を利用できる
https://www.postgresql.jp/document/current/html/sql-select.html
SELECT句は10番目。
GROUP BY句やWHERE句では
SELECT句の結果を利用できない
ORDER BY句ではSELECT句の後なので
SELECT句のラベルを利用できる
Slide 18
Slide 18 text
SQLチューニングの基礎
SQLの基本的な知識
Slide 19
Slide 19 text
SQLの基本的な知識
Slide 20
Slide 20 text
動画や資料も公開されているので
過去のチュートリアルをチェック！
SQLの基本的な知識
Slide 21
Slide 21 text
1. 自己紹介
2. SQLの基本的な知識
3. 分析クエリでよく使う構文
4. 大規模な集計を高速化するコツ
5. まとめ
あじぇんだ
Slide 22
Slide 22 text
PostgreSQLは便利
分析クエリでよく使う構文
Slide 23
Slide 23 text
https://amzn.to/3qq3hMY
分析クエリでよく使う構文
標準的なSQLがベースの本です。
PostgreSQLでも活用できる例が、たくさん
出てきます。
本日はPostgreSQLにカスタマイズして、ご
紹介します。
Slide 24
Slide 24 text
今日のセッションに興味が出た人は
ぜひSQLパズルを読んでみてください
分析クエリでよく使う構文
Slide 25
Slide 25 text
1. 連日の欠勤のデータを抽出する
分析クエリでよく使う構文
Slide 26
Slide 26 text
連続したデータの検索
CREATE TABLE 欠勤
(
社員id INTEGER NOT NULL,
欠勤日 DATE NOT NULL,
理由 CHAR(40) NOT NULL,
罰点 INTEGER NOT NULL
CHECK (罰点 BETWEEN 0 AND 4),
PRIMARY KEY(社員id, 欠勤日)
);
このTableに保存された欠勤
内容に対して、連日欠勤した
データを検索したい。
本の内容から調整しています
Slide 27
Slide 27 text
連続したデータの検索
SELECT *
FROM 欠勤 AS t1
WHERE EXISTS
(
SELECT *
FROM 欠勤 AS t2
WHERE t1.欠勤id = t2.欠勤id
AND t1.欠勤日 = (t2.欠勤日 +
INTERVAL ‘1’ DAY)
)
このTableに保存された欠勤
内容に対して、連日欠勤した
データを検索したい。
Slide 28
Slide 28 text
連続したデータの検索
SELECT *
FROM 欠勤 AS t1
INNER JOIN カレンダー AS c
ON c.日付 = t1.欠勤日
AND c.タイプ = ‘平日’
EXISTS
(
SELECT *
FROM 欠勤 AS t2
WHERE t1.欠勤id = t2.欠勤id
AND t1.欠勤日 = (t2.欠勤日 +
INTERVAL ‘1’ DAY)
)
実際の連続した欠勤では金
曜日と月曜日のように週末を
挟んだ連続した欠勤もある。
そのような場合に有効なのが
カレンダーテーブルを作って比
較する
Slide 29
Slide 29 text
PostgreSQLでカレンダーを作る
↓
generate_series()を使う
分析クエリでよく使う構文
Slide 30
Slide 30 text
連続したデータの検索
WITH カレンダー AS (
SELECT
gen AS 日付
FROM
generate_series(
current_date,
current_date + interval '1 YEAR'
) AS gen
)
SELECT ~ (略)
generate_seriesを利用して
連続した日付を生成。
生成した連続した日付を
WITH句を使って仮想のテーブ
ルとしてカレンダーとして保存
する。
WITH句で作成したテーブル
はViewのような振る舞いをす
る
Slide 31
Slide 31 text
generate_series()と
カレンダーテーブルの応用
分析クエリでよく使う構文
Slide 32
Slide 32 text
連続した日付の生成
SELECT gen
FROM generate_series
(
current_date ,
current_date + interval '1 month',
'10 minutes'::interval
) AS gen
generate_seriesの第3引数
を調整すれば間隔を調整でき
る。
また日付以外にも連続した文
字や数字などを生成することも
できるため、様々な用途に活
用できる
Slide 33
Slide 33 text
generate_series()は
PostgreSQLの重要な関数
分析クエリでよく使う構文
Slide 34
Slide 34 text
2. 完了したテスト結果を探す
分析クエリでよく使う構文
Slide 35
Slide 35 text
完了したテスト結果を探す
CREATE TABLE テスト結果
(
テスト名 CHAR(40) NOT NULL,
テスト_ステップ INTEGER NOT NULL,
完了日 DATE NULL, -- nullは未完了
PRIMARY KEY
(テスト名, テスト_ステップ)
);
すべてのステップを完了して
いるテストを見つける
Slide 36
Slide 36 text
SELECT DISTINCT テスト名
FROM テスト結果 AS t1
WHERE NOT EXISTS
(
SELECT *
FROM テスト結果 AS t2
WHERE t1.テスト名 = t2.テスト名
AND t1.完了日 IS NULL
)
完了したテスト = 完了してい
ないテストステップが一つも存
在していない。
逆転の発想で条件を指定す
ることで検索することができ
る。
完了したテスト結果を探す
Slide 37
Slide 37 text
SELECT テスト名
FROM テスト結果
GROUP BY テスト名
HAVING COUNT(*) = COUNT(完了日)
1. COUNT(*)はNULLを数
える
2. COUNT(column)は
NULLを数えない
これを活用すると完了したテ
ストのみが同数になるので完
了したテストだけを抽出でき
る。
完了したテスト結果を探す
Slide 38
Slide 38 text
RDBMSは集合を扱う
↓
データを集合として捉えることが大事
分析クエリでよく使う構文
Slide 39
Slide 39 text
その他にもWindow関数や
CASE式も便利
分析クエリでよく使う構文
Slide 40
Slide 40 text
SQLの基本的な知識
Slide 41
Slide 41 text
分析クエリでよく使う構文
https://www.slideshare.net/SoudaiSone/web-postgre-sql-3
Slide 42
Slide 42 text
分析クエリでよく使う構文
https://speakerdeck.com/soudai/postgresqlfalsesqlnben-falsetuku
https://github.com/soudai/pgconf.asia-tutorial
類似の内容ですが動画は無いですが
資料はこっちのわかりやすいと思いま
す。
またgithubにサンプルがあります。
Slide 43
Slide 43 text
小計を出すためのCUBE句やROLLUP句も
この機会に調べてみましょう
分析クエリでよく使う構文
Slide 44
Slide 44 text
1. 自己紹介
2. SQLの基本的な知識
3. 分析クエリでよく使う構文
4. 大規模な集計を高速化するコツ
5. まとめ
あじぇんだ
Slide 45
Slide 45 text
集計クエリが難しいときは
設計が悪いか無知かのどちらか
大規模な集計を高速化するコツ
Slide 46
Slide 46 text
17章 複雑なクエリ
“複雑なクエリが生まれるには理由があ
ります。その理由はクエリを紐 解くこと
で見えてきますが、おもに次の2つに分
けられるでしょう。”
● 無知ゆえの豪腕
スキル不足に起因した、力技による解
決としての複雑なクエリ
● 腐ったテーブルの腐ったクエリ
テーブル設計に問題を抱えており、目
的を達成するため結果的に 複雑に
なったクエリ
Slide 47
Slide 47 text
良いクエリは
良い設計に宿る
大規模な集計を高速化するコツ
Slide 48
Slide 48 text
既にデータ構造が難しい場合
↓
加工済みテーブルを用意する
大規模な集計を高速化するコツ
Slide 49
Slide 49 text
Extract（抽出）
Transform（変換）
Load（書き出し）
大規模な集計を高速化するコツ
Slide 50
Slide 50 text
Extract（抽出）
Load（書き出し）
Transform（変換）
大規模な集計を高速化するコツ
Slide 51
Slide 51 text
ETLとELT
データを保存するときに加工する
or
データを保存した後で加工する
大規模な集計を高速化するコツ
Slide 52
Slide 52 text
加工済みのViewを使う
大規模な集計を高速化するコツ
Slide 53
Slide 53 text
加工済みのViewを使う
↓
集計がシンプルになる
大規模な集計を高速化するコツ
Slide 54
Slide 54 text
加工済みのViewを使う
↓
一時的なテーブルならWITH句を使う
大規模な集計を高速化するコツ
Slide 55
Slide 55 text
高速化のためにViewを実体化させる
↓
Materialized View
大規模な集計を高速化するコツ
Slide 56
Slide 56 text
マテリアライズド・ビューには
INDEXが設定できる
大規模な集計を高速化するコツ
Slide 57
Slide 57 text
マテリアライズド・ビューを活用して
集計したいデータの元を作る
大規模な集計を高速化するコツ
Slide 58
Slide 58 text
ETLは？
大規模な集計を高速化するコツ
Slide 59
Slide 59 text
ETLは？
↓
時間が無いので今日は割愛します
大規模な集計を高速化するコツ
Slide 60
Slide 60 text
分析クエリでよく使う構文
https://speakerdeck.com/soudai/challenge-to-rdbms
ETLの話はこちらでしています。
AWSの活用事例ですが一般的な話も
多く出てきます。
Slide 61
Slide 61 text
キャッシュは麻薬
大規模な集計を高速化するコツ
Slide 62
Slide 62 text
16章 キャッシュ中毒
“キャッシュは前述のとおり、採用することで
データの参照が高速化されます。
これは、省略した計算処理量が多ければ多
いほど劇的な効果を発揮します。
この効果は絶大で、前述のとおりその効果に
魅了される人も少なくありません。
また、キャッシュは一度使い始めると辞める
ことが難しく、魅力と辞めることの難しさ、つ
まり中毒性の高さから 「キャッシュは麻薬」と
比喩されることもあります。”
Slide 63
Slide 63 text
16章 キャッシュ中毒
“マテリアライズド・ビューをもとに新たな
マテリアライズド・ビューも作れますが、
それはキャッシュを多段に持つことにな
り、複雑度が掛け算のように上がってい
きます。よほど正当な理由がない限り、
マテリアライズド・ビューの多段化はや
めましょう。これは筆者が最も後悔した
RDBの設計の1つで、間違いなくアンチ
パターンです”
Slide 64
Slide 64 text
マテリアライズド・ビューは便利ですが
用法用量を守りましょう
大規模な集計を高速化するコツ
Slide 65
Slide 65 text
他にもLATERAL句を活用して
サブクエリを中から呼ぶなどもある
大規模な集計を高速化するコツ
Slide 66
Slide 66 text
1. 自己紹介
2. SQLの基本的な知識
3. 分析クエリでよく使う構文
4. 大規模な集計を高速化するコツ
5. まとめ
あじぇんだ
Slide 67
Slide 67 text
PostgreSQLは便利
まとめ
Slide 68
Slide 68 text
マテリアライズド・ビューの乱用は
本当に辞めましょう
まとめ
Slide 69
Slide 69 text
良いクエリは
正しい知識と設計に宿る
まとめ
Slide 70
Slide 70 text
だからこそ、正しく使うことが大切です
まとめ
Slide 71
Slide 71 text
SQLの書き方にはパターンがある
まとめ
Slide 72
Slide 72 text
SQLの書き方にはパターンがある
↓
パターンを覚えましょう
まとめ
ミックさんの本が読みやすいのでオススメです。
何冊か読んでみましょう
Slide 73
Slide 73 text
まとめ
どちらか読んだ上でSQLパズル本に挑戦することをオス
スメします。
今日の話が難しいなって思った人にはオススメ。
CASEやWindow関数の使い方も出てきます。
Slide 74
Slide 74 text
データベースの寿命は
アプリケーションより長い
まとめ
Slide 75
Slide 75 text
SQLを覚えて
どんどん仕事に活用しましょう！
まとめ
Slide 76
Slide 76 text
ご清聴ありがとうございました
まとめ