実際に使うSQLの書き方 徹底解説 / pgcon21j-tutorial

- PostgreSQLカンファレンス 2021 - チュートリアル
- https://www.postgresql.jp/jpug-pgcon2021
- 詳細はこちら
https://github.com/soudai/pgcon21j-tutorial

Transcript

1. 実際に使うSQLの書き方 徹底解説 ～ PostgreSQLのSQL入門 ～ PostgreSQLカンファレンス 2021 - チュートリアル
2. 今日は主に参照（SELECT）の話をします
 
 
 What is it?
3. サンプルを実際に実行しながら説明します
 
 
 What is it?
4. 
 
 
 今日の実際のサンプルはこちら
 
 
 What is it? https://github.com/soudai/pgcon21j-tutorial
5. サンプルを実際に
 
 実行しながら説明します
 What is it?
6. 1. 自己紹介
 2. SQLの基本的な知識
 3. 分析クエリでよく使う構文
 4. 大規模な集計を高速化するコツ
 5. まとめ
 あじぇんだ
7. 1. 自己紹介
 2. SQLの基本的な知識
 3. 分析クエリでよく使う構文
 4. 大規模な集計を高速化するコツ
 5. まとめ
 あじぇんだ
8. 自己紹介
 曽根 壮大（37歳）
 Have Fun Tech LLC 代表社員
 
 そ　 ね　　た け と も
 • 日本PostgreSQLユーザ会 勉強会分科会 担当
 • 3人の子供がいます（長女、次女、長男）
 • 技術的にはWeb/LL言語/RDBMSが好きです
 • コミュニティが好き
10. 本書きました

11. 1. 自己紹介
 2. SQLの基本的な知識
 3. 分析クエリでよく使う構文
 4. 大規模な集計を高速化するコツ
 5. まとめ
 あじぇんだ
12. SQLのことを知る
 
 
 SQLの基本的な知識
13. SQLの実行順を知れば
 
 SQLのエラーがわかる
 SQLの基本的な知識
14. SQLの実行順
 
 
 SQLの基本的な知識
15. 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
16. 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
17. 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句のラベルを利用できる
18. SQLチューニングの基礎
 
 
 SQLの基本的な知識
19. SQLの基本的な知識
20. 動画や資料も公開されているので
 
 過去のチュートリアルをチェック！
 SQLの基本的な知識
21. 1. 自己紹介
 2. SQLの基本的な知識
 3. 分析クエリでよく使う構文
 4. 大規模な集計を高速化するコツ
 5. まとめ
 あじぇんだ
22. PostgreSQLは便利
 
 
 分析クエリでよく使う構文
23. 
 
 
 
 
 
 
 https://amzn.to/3qq3hMY
 
 分析クエリでよく使う構文 標準的なSQLがベースの本です。 PostgreSQLでも活用できる例が、たくさん 出てきます。 本日はPostgreSQLにカスタマイズして、ご 紹介します。
24. 今日のセッションに興味が出た人は
 
 ぜひSQLパズルを読んでみてください
 分析クエリでよく使う構文
25. 1. 連日の欠勤のデータを抽出する
 
 
 分析クエリでよく使う構文
26. 連続したデータの検索 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に保存された欠勤 内容に対して、連日欠勤した データを検索したい。 本の内容から調整しています
27. 連続したデータの検索 SELECT * 
 FROM 欠勤 AS t1
 WHERE EXISTS
 (
 SELECT * 
 FROM 欠勤 AS t2
 WHERE t1.欠勤id = t2.欠勤id
 AND t1.欠勤日 = (t2.欠勤日 + INTERVAL ‘1’ DAY)
 )
 　このTableに保存された欠勤 内容に対して、連日欠勤した データを検索したい。
28. 連続したデータの検索 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)
 )
 　実際の連続した欠勤では金 曜日と月曜日のように週末を 挟んだ連続した欠勤もある。 　そのような場合に有効なのが カレンダーテーブルを作って比 較する
29. PostgreSQLでカレンダーを作る
 ↓
 generate_series()を使う
 分析クエリでよく使う構文
30. 連続したデータの検索 WITH カレンダー AS (
 SELECT
 gen AS 日付
 FROM
 generate_series(
 current_date,
 current_date + interval '1 YEAR'
 ) AS gen
 )
 
 SELECT ~ (略)
 
 
 　generate_seriesを利用して 連続した日付を生成。 　生成した連続した日付を WITH句を使って仮想のテーブ ルとしてカレンダーとして保存 する。 　WITH句で作成したテーブル はViewのような振る舞いをす る
31. generate_series()と
 
 カレンダーテーブルの応用
 分析クエリでよく使う構文
32. 連続した日付の生成 SELECT gen
 FROM generate_series
 (
 current_date ,
 current_date + interval '1 month',
 '10 minutes'::interval
 ) AS gen
 
 　generate_seriesの第3引数 を調整すれば間隔を調整でき る。 　また日付以外にも連続した文 字や数字などを生成することも できるため、様々な用途に活 用できる
33. generate_series()は
 
 PostgreSQLの重要な関数
 分析クエリでよく使う構文
34. 2. 完了したテスト結果を探す
 
 
 分析クエリでよく使う構文
35. 完了したテスト結果を探す CREATE TABLE テスト結果
 (
 テスト名 CHAR(40) NOT NULL,
 テスト_ステップ INTEGER NOT NULL,
 完了日 DATE NULL, -- nullは未完了
 PRIMARY KEY
 (テスト名, テスト_ステップ)
 );
 
 　すべてのステップを完了して いるテストを見つける
36. SELECT DISTINCT テスト名
 FROM テスト結果 AS t1
 WHERE NOT EXISTS
 (
 SELECT * 
 FROM テスト結果 AS t2
 WHERE t1.テスト名 = t2.テスト名
 AND t1.完了日 IS NULL
 )
 　完了したテスト = 完了してい ないテストステップが一つも存 在していない。 　逆転の発想で条件を指定す ることで検索することができ る。 完了したテスト結果を探す
37. SELECT テスト名
 FROM テスト結果
 GROUP BY テスト名
 HAVING COUNT(*) = COUNT(完了日)
 1. COUNT(*)はNULLを数 える 2. COUNT(column)は NULLを数えない 　これを活用すると完了したテ ストのみが同数になるので完 了したテストだけを抽出でき る。 完了したテスト結果を探す
38. RDBMSは集合を扱う
 ↓
 データを集合として捉えることが大事
 分析クエリでよく使う構文
39. その他にもWindow関数や
 
 CASE式も便利
 分析クエリでよく使う構文
40. SQLの基本的な知識
41. 分析クエリでよく使う構文 https://www.slideshare.net/SoudaiSone/web-postgre-sql-3
42. 分析クエリでよく使う構文 https://speakerdeck.com/soudai/postgresqlfalsesqlnben-falsetuku https://github.com/soudai/pgconf.asia-tutorial 類似の内容ですが動画は無いですが 資料はこっちのわかりやすいと思いま す。 またgithubにサンプルがあります。
43. 小計を出すためのCUBE句やROLLUP句も
 
 この機会に調べてみましょう
 分析クエリでよく使う構文
44. 1. 自己紹介
 2. SQLの基本的な知識
 3. 分析クエリでよく使う構文
 4. 大規模な集計を高速化するコツ
 5. まとめ
 あじぇんだ
45. 集計クエリが難しいときは
 
 設計が悪いか無知かのどちらか
 大規模な集計を高速化するコツ
46. 17章 複雑なクエリ “複雑なクエリが生まれるには理由があ ります。その理由はクエリを紐 解くこと で見えてきますが、おもに次の2つに分 けられるでしょう。” • 無知ゆえの豪腕 スキル不足に起因した、力技による解 決としての複雑なクエリ • 腐ったテーブルの腐ったクエリ テーブル設計に問題を抱えており、目 的を達成するため結果的に 複雑に なったクエリ
47. 良いクエリは
 
 良い設計に宿る
 大規模な集計を高速化するコツ
48. 既にデータ構造が難しい場合
 ↓
 加工済みテーブルを用意する
 大規模な集計を高速化するコツ
49. Extract（抽出） 
 Transform（変換） 
 Load（書き出し）
 大規模な集計を高速化するコツ
50. Extract（抽出） 
 Load（書き出し）
 Transform（変換） 
 大規模な集計を高速化するコツ
51. ETLとELT
 
 データを保存するときに加工する
 or
 データを保存した後で加工する
 
 大規模な集計を高速化するコツ
52. 加工済みのViewを使う
 
 
 大規模な集計を高速化するコツ
53. 加工済みのViewを使う
 ↓
 集計がシンプルになる
 大規模な集計を高速化するコツ
54. 加工済みのViewを使う
 ↓
 一時的なテーブルならWITH句を使う
 大規模な集計を高速化するコツ
55. 高速化のためにViewを実体化させる
 ↓
 Materialized View
 大規模な集計を高速化するコツ
56. マテリアライズド・ビューには
 
 INDEXが設定できる
 大規模な集計を高速化するコツ
57. マテリアライズド・ビューを活用して
 
 集計したいデータの元を作る
 大規模な集計を高速化するコツ
58. ETLは？
 
 
 大規模な集計を高速化するコツ
59. ETLは？
 ↓
 時間が無いので今日は割愛します
 大規模な集計を高速化するコツ
60. 分析クエリでよく使う構文 https://speakerdeck.com/soudai/challenge-to-rdbms ETLの話はこちらでしています。 AWSの活用事例ですが一般的な話も 多く出てきます。
61. キャッシュは麻薬
 
 
 大規模な集計を高速化するコツ

62. 16章 キャッシュ中毒 “キャッシュは前述のとおり、採用することで データの参照が高速化されます。 これは、省略した計算処理量が多ければ多 いほど劇的な効果を発揮します。 この効果は絶大で、前述のとおりその効果に 魅了される人も少なくありません。 また、キャッシュは一度使い始めると辞める ことが難しく、魅力と辞めることの難しさ、つ まり中毒性の高さから 「キャッシュは麻薬」と 比喩されることもあります。”
63. 16章 キャッシュ中毒 “マテリアライズド・ビューをもとに新たな マテリアライズド・ビューも作れますが、 それはキャッシュを多段に持つことにな り、複雑度が掛け算のように上がってい きます。よほど正当な理由がない限り、 マテリアライズド・ビューの多段化はや めましょう。これは筆者が最も後悔した RDBの設計の1つで、間違いなくアンチ パターンです”
64. マテリアライズド・ビューは便利ですが
 
 用法用量を守りましょう
 大規模な集計を高速化するコツ

65. 他にもLATERAL句を活用して
 
 サブクエリを中から呼ぶなどもある
 大規模な集計を高速化するコツ

66. 1. 自己紹介
 2. SQLの基本的な知識
 3. 分析クエリでよく使う構文
 4. 大規模な集計を高速化するコツ
 5. まとめ
 あじぇんだ
67. PostgreSQLは便利
 
 
 まとめ
68. マテリアライズド・ビューの乱用は
 
 本当に辞めましょう
 まとめ
69. 良いクエリは
 
 正しい知識と設計に宿る
 まとめ
70. だからこそ、正しく使うことが大切です
 
 
 まとめ
71. SQLの書き方にはパターンがある
 
 
 まとめ
72. SQLの書き方にはパターンがある
 ↓
 パターンを覚えましょう
 まとめ ミックさんの本が読みやすいのでオススメです。 何冊か読んでみましょう
73. まとめ どちらか読んだ上でSQLパズル本に挑戦することをオス スメします。 今日の話が難しいなって思った人にはオススメ。 CASEやWindow関数の使い方も出てきます。
74. データベースの寿命は
 
 アプリケーションより長い
 まとめ
75. SQLを覚えて
 
 どんどん仕事に活用しましょう！
 まとめ
76. ご清聴ありがとうございました
 
 
 まとめ