Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

soudai sone
November 12, 2021

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

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

soudai sone

November 12, 2021
Tweet

More Decks by soudai sone

Other Decks in Technology

Transcript

  1. 自己紹介
 曽根 壮大(37歳)
 Have Fun Tech LLC 代表社員
 
 そ 

    ね  た け と も
 • 日本PostgreSQLユーザ会 勉強会分科会 担当
 • 3人の子供がいます(長女、次女、長男)
 • 技術的にはWeb/LL言語/RDBMSが好きです
 • コミュニティが好き
  2. 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
  3. 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
  4. 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句のラベルを利用できる
  5. 
 
 
 
 
 
 
 https://amzn.to/3qq3hMY
 
 分析クエリでよく使う構文

    標準的なSQLがベースの本です。 PostgreSQLでも活用できる例が、たくさん 出てきます。 本日はPostgreSQLにカスタマイズして、ご 紹介します。
  6. 連続したデータの検索 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に保存された欠勤 内容に対して、連日欠勤した データを検索したい。 本の内容から調整しています
  7. 連続したデータの検索 SELECT * 
 FROM 欠勤 AS t1
 WHERE EXISTS


    (
 SELECT * 
 FROM 欠勤 AS t2
 WHERE t1.欠勤id = t2.欠勤id
 AND t1.欠勤日 = (t2.欠勤日 + INTERVAL ‘1’ DAY)
 )
  このTableに保存された欠勤 内容に対して、連日欠勤した データを検索したい。
  8. 連続したデータの検索 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)
 )
  実際の連続した欠勤では金 曜日と月曜日のように週末を 挟んだ連続した欠勤もある。  そのような場合に有効なのが カレンダーテーブルを作って比 較する
  9. 連続したデータの検索 WITH カレンダー AS (
 SELECT
 gen AS 日付
 FROM


    generate_series(
 current_date,
 current_date + interval '1 YEAR'
 ) AS gen
 )
 
 SELECT ~ (略)
 
 
  generate_seriesを利用して 連続した日付を生成。  生成した連続した日付を WITH句を使って仮想のテーブ ルとしてカレンダーとして保存 する。  WITH句で作成したテーブル はViewのような振る舞いをす る
  10. 連続した日付の生成 SELECT gen
 FROM generate_series
 (
 current_date ,
 current_date +

    interval '1 month',
 '10 minutes'::interval
 ) AS gen
 
  generate_seriesの第3引数 を調整すれば間隔を調整でき る。  また日付以外にも連続した文 字や数字などを生成することも できるため、様々な用途に活 用できる
  11. 完了したテスト結果を探す CREATE TABLE テスト結果
 (
 テスト名 CHAR(40) NOT NULL,
 テスト_ステップ

    INTEGER NOT NULL,
 完了日 DATE NULL, -- nullは未完了
 PRIMARY KEY
 (テスト名, テスト_ステップ)
 );
 
  すべてのステップを完了して いるテストを見つける
  12. SELECT DISTINCT テスト名
 FROM テスト結果 AS t1
 WHERE NOT EXISTS


    (
 SELECT * 
 FROM テスト結果 AS t2
 WHERE t1.テスト名 = t2.テスト名
 AND t1.完了日 IS NULL
 )
  完了したテスト = 完了してい ないテストステップが一つも存 在していない。  逆転の発想で条件を指定す ることで検索することができ る。 完了したテスト結果を探す
  13. SELECT テスト名
 FROM テスト結果
 GROUP BY テスト名
 HAVING COUNT(*) =

    COUNT(完了日)
 1. COUNT(*)はNULLを数 える 2. COUNT(column)は NULLを数えない  これを活用すると完了したテ ストのみが同数になるので完 了したテストだけを抽出でき る。 完了したテスト結果を探す
  14. 17章 複雑なクエリ “複雑なクエリが生まれるには理由があ ります。その理由はクエリを紐 解くこと で見えてきますが、おもに次の2つに分 けられるでしょう。” • 無知ゆえの豪腕 スキル不足に起因した、力技による解

    決としての複雑なクエリ • 腐ったテーブルの腐ったクエリ テーブル設計に問題を抱えており、目 的を達成するため結果的に 複雑に なったクエリ