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

データサイエンス勉強会_SQL

a-ishimura
March 05, 2019
34

 データサイエンス勉強会_SQL

a-ishimura

March 05, 2019
Tweet

Transcript

  1. 本番環境 分析環境 想定している環境 3 Copyright© 2018 GxP, Inc. システムの利用ユーザ システム担当者

    システム DBMS データ ログファイル 分析担当者 DBアクセス ツール データ分析 ツール SQL結果 DBMS データ
  2. 本番環境 分析環境 想定している環境 4 Copyright© 2018 GxP, Inc. システムの利用ユーザ システム担当者

    システム DBMS データ ログファイル 分析担当者 DBアクセス ツール データ分析 ツール SQL結果 DBMS データ 参加者のみなさん
  3. 本番環境 分析環境 想定している環境 5 Copyright© 2018 GxP, Inc. システムの利用ユーザ システム担当者

    システム DBMS データ ログファイル 分析担当者 DBアクセス ツール データ分析 ツール SQL結果 DBMS データ 本番とは別に、分析用 の環境が作られている
  4. 本番環境 分析環境 想定している環境 6 Copyright© 2018 GxP, Inc. システムの利用ユーザ システム担当者

    システム DBMS データ ログファイル 分析担当者 DBアクセス ツール データ分析 ツール SQL結果 DBMS データ コミュニケーションがとれる。 どんなデータがあるのか、 どんな構成なのかなどを教え てもらえる。
  5. 本番環境 分析環境 想定している環境 7 Copyright© 2018 GxP, Inc. システムの利用ユーザ システム担当者

    システム DBMS データ ログファイル 分析担当者 DBアクセス ツール データ分析 ツール SQL結果 DBMS データ 同等環境を作り 時々必要な最新データをコピー (これは誰かがやってくれている ハズ)
  6. 今日デモする環境とデータ OS Windows 10 RDBMS PostgreSQL 10.7.1 DBアクセスツール A5:SQL Mk-2

    https://a5m2.mmatsubara.com/ データ http://www.postgresqltutorial.com/postgresql-sample-database/ (PostgreSQLのDVDレンタルを想定したチュートリアルデータ) 8 Copyright© 2018 GxP, Inc.
  7. RDBMSとNoSQL ◼RDBMS(Relational DataBase Management System) •RDBの管理システムのこと •Oracle、MySQL、PostgreSQLなどがある ◼NoSQL(Not only SQL)

    •RDB以外のデータベース •NoSQLの中にさらに分類があり、Key Value Store、ドキュメントDB…などがある •Redis、MongoDB、Cassandraなどがある •BigDataはRDBでは難しいのでこっちで作る 10 Copyright© 2018 GxP, Inc. 今回の話はこっち データベース RDB NoSQL Key Value Store ドキュメントDB
  8. SQLの種類 ◼DDL(Data Definition Language) •データベースのスキーマ構造を作成・変更する •CREATE/DROP/ALTERなど ◼DML(Data Manipulation Language) •データの参照や更新を行う

    •SELECT/INSERT/UPDATE/DELETEなど ◼DCL(Data Control Language) •トランザクション制御を行う •BEGIN/COMMIT/ROLLBACKなど 12 Copyright© 2018 GxP, Inc. 今回の話 SQL DDL DML DCL
  9. DML(Data Manipulation Language) ◼データの参照や更新を行う ◼SELECT • 登録されているデータを参照する ◼ INSERT/UPDATE/DELETE •

    データを登録・更新・削除する 14 Copyright© 2018 GxP, Inc. SQL DDL DML DCL SELECT INSERT UPDATE DELETE 今回の話
  10. SELECT文 ◼どのテーブルから、 どんなデータを取得したいかを記述する 例 ◼顧客の一覧を取得する SELECT * FROM customer; ◼特定の顧客のレンタル履歴を取得する

    SELECT rental_date, return_date, customer_id FROM rental WHERE customer_id = 5; ◼顧客ごとのレンタル件数を集計する SELECT customer.customer_id, customer.first_name, customer.last_name, count(*) as レンタル件数 FROM customer LEFT OUTER JOIN rental ON customer.customer_id = rental.customer_id GROUP BY customer.customer_id, customer.first_name, customer.last_name; 20 Copyright© 2018 GxP, Inc.
  11. SELECT文 ◼顧客ごとのレンタル件数を集計する SELECT customer.customer_id, customer.first_name, customer.last_name, count(*) as レンタル件数 FROM

    customer LEFT OUTER JOIN rental ON customer.customer_id = rental.customer_id GROUP BY customer.customer_id, customer.first_name, customer.last_name; 25 Copyright© 2018 GxP, Inc.
  12. SELECT文 ◼顧客ごとのレンタル件数を集計する SELECT customer.customer_id, customer.first_name, customer.last_name, count(*) as レンタル件数 FROM

    customer LEFT OUTER JOIN rental ON customer.customer_id = rental.customer_id GROUP BY customer.customer_id, customer.first_name, customer.last_name; 31 Copyright© 2018 GxP, Inc. カウント結果にasで別名をつけている
  13. FROM句~INNER JOIN~ ◼顧客の氏名と顧客の住所を取得する SELECT customer.customer_id, first_name, last_name, address FROM customer

    INNER JOIN address ON customer.address_id = address.address_id; 40 Copyright© 2018 GxP, Inc. customerテーブルとaddressテーブルを address_idで結合
  14. FROM句~INNER JOIN~ ◼顧客の氏名と顧客の住所を取得する SELECT customer.customer_id, first_name, last_name, country, district, city,

    address FROM customer INNER JOIN address ON customer.address_id = address.address_id INNER JOIN city ON address.city_id = city.city_id INNER JOIN country ON city.country_id = country.country_id; 42 Copyright© 2018 GxP, Inc. おまけ
  15. FROM句~OUTER JOIN~ ◼ OUTER JOINの種類は、「条件に合致しない行も取得したいテーブル」はどれ?で選 ぶ。 ◼ 改行しない状態で、左に書いているか、右に書いているかが基準 • FROM

    テーブルA LEFT OUTER JOIN テーブルB ON 結合条件 →テーブルAが左、テーブルBが右 ◼ LEFT OUTER JOIN → 左にあるテーブルは条件に合致しなくても取得 ◼ RIGHT OUTER JOIN → 右にあるテーブルは条件に合致しなくても取得 ◼ FULL OUTER JOIN → どっちも条件に合致しなくても取得 よく使うのはLEFT。LEFTでもRIGHTでも同じことが表現できるが、混乱しないように基 本的にはLEFTを使うといいです。 FULLは、必要になる機会がそんなに多くない。 45 Copyright© 2018 GxP, Inc.
  16. FROM句~OUTER JOIN~ ◼filmにlanguageの情報を付加するときはINNER JOINでOK SELECT film_id, title, film.language_id, name FROM

    film INNER JOIN language ON film.language_id = language.language_id; 51 Copyright© 2018 GxP, Inc.
  17. FROM句~OUTER JOIN~ ◼languageにfilmの情報を付加するときはOUTER JOINを使う SELECT language.language_id, name, film_id, title FROM

    language LEFT OUTER JOIN film ON language.language_id = film.language_id ORDER BY language.language_id desc; 53 Copyright© 2018 GxP, Inc.
  18. FROM句~OUTER JOIN~ ◼countするときなんかの例がわかりやすい? SELECT language.language_id, name, count(film_id) AS フィルム数 FROM

    language LEFT OUTER JOIN film ON language.language_id = film.language_id GROUP BY language.language_id, name ORDER BY language.language_id desc; 55 Copyright© 2018 GxP, Inc.
  19. FROM句~OUTER JOIN~ 56 Copyright© 2018 GxP, Inc. INNER JOINにすると、このフィルム 数が0である、という情報が取れな

    くなってしまう 顧客マスタ×利用履歴 従業員マスタ×出勤履歴 のように、履歴がなくてもマスタの情 報は全部もってきたい!という場合に 使うことが多いハズ
  20. FROM句~OUTER JOIN~ SELECT name, language.language_id AS language_language_id, film.language_id AS film_language_id,

    film_id, title FROM language LEFT OUTER JOIN film ON language.language_id = film.language_id ORDER BY language.language_id desc; 57 Copyright© 2018 GxP, Inc. 慣れないうちは、結合条件を両方出力 してあげると、何が起きているのかち ょっとわかるかも
  21. そのほか 今日割愛するもので、次のステップとして知っていくとよさそうなものの例 ◼NULL •欠損値みたいなもん。WHEREとかGROUPBY使う前に知っておきたい。 •あとでいいって書いてるサイトも見かけるけど、知っておかないと取得データが意図通りでないことに気 付けないこともある ◼LIMIT、ROWNUM •取得する行数を絞る。RDBMSによって書き方が違う。 ◼WHERE句で使える条件 •等号(=)、不等号(>、<)など

    •論理演算(AND、ORなど) •IN、BETWEEN、EXISTSなどは必要になったらでよさそう ◼集計関数、GROUP BY、HAVING •集計は、合計や平均を計算したり、件数をカウントできる。 ◼必要になったらでよさそう •ORDER BY、UNION、DISTINCT、IF、CASE… •副問い合わせ、サブクエリ 61 Copyright© 2018 GxP, Inc.
  22. まずかんたんにできる対策 ◼取得するカラム(項目、列)を減らす •とりあえず全部取得するのをやめる •テーブル定義書を見て、データ型・サイズを確認する •サイズが大きいのにたいして必要じゃないカラムもあったりする 例:画像を登録できるシステムで、画像データが入ったカラムを取得している 例:説明文などの長い文章が入ったカラムを取得している ◼取得する行を減らす •とりあえず全部取得するのをやめる •欲しい行が何かの条件で絞り込めないか確認する

    例:何年もデータを溜めているテーブルから、直近のデータだけ取得する •全部まとめて持ってくるのではなく、分割して取得すると良い場合も ◼1年分ずつ、とか、店舗IDごと、とか ◼分割する場合は有識者にその分割の仕方で正しく取得できそうか見てもらうとよいと思います 持ってくるデータが多すぎる 64 Copyright© 2018 GxP, Inc.
  23. インデックスとは ◼テーブルの中のよく使うカラムの分身みたいなもの •WHEREやJOINでよく使うカラムのこと。IDや日付などによく作られる。 •よく閲覧するカラムとは違うので、名前などのカラムにはふつう作られない。 ◼インデックスを使うと絞り込みや結合が速くなる •テーブルよりもインデックスの方が列が少ないので小さい •インデックスの方が仕組み的に速い インデックス使えてない 65 Copyright©

    2018 GxP, Inc. 内部キー film_id title … rating … 1 133 Chamber Italian NC-17 2 384 Grosse Wonderful R 3 8 Airport Pollock R 4 98 Bright Encounters PG-13 5 1 Academy Dinosaur PG 6 2 Ace Goldfinger G title 内部キー Chamber Italian 1 Grosse Wonderful 2 Airport Pollock 3 Bright Encounters 4 Academy Dinosaur 5 Ace Goldfinger 6 テーブル インデックス
  24. ◼使える例 select * from film where title = 'Academy Dinosaur';

    インデックス使えてない 66 Copyright© 2018 GxP, Inc. ① titleが ‘Academy Dinosaur’ のものを探す テーブル インデックス 内部キー film_id title … rating … 1 133 Chamber Italian NC-17 2 384 Grosse Wonderful R 3 8 Airport Pollock R 4 98 Bright Encounters PG-13 5 1 Academy Dinosaur PG 6 2 Ace Goldfinger G title 内部キー Chamber Italian 1 Grosse Wonderful 2 Airport Pollock 3 Bright Encounters 4 Academy Dinosaur 5 Ace Goldfinger 6 ②インデックスで 見つけた行を 内部キーを使って 取得する
  25. ◼使えてない例 select * from film where rating= 'PG'; インデックス使えてない 67

    Copyright© 2018 GxP, Inc. ①インデックスにratingがないので テーブルを全件読み込んで探す テーブル インデックス 内部キー film_id title … rating … 1 133 Chamber Italian NC-17 2 384 Grosse Wonderful R 3 8 Airport Pollock R 4 98 Bright Encounters PG-13 5 1 Academy Dinosaur PG 6 2 Ace Goldfinger G title 内部キー Chamber Italian 1 Grosse Wonderful 2 Airport Pollock 3 Bright Encounters 4 Academy Dinosaur 5 Ace Goldfinger 6
  26. ◼1テーブルにつき1つしか使えません select * from film where title = 'Academy Dinosaur'

    and language_id=1; インデックスの話~おまけ~ 70 Copyright© 2018 GxP, Inc. こう書いたらtitleのインデックスとlanguage_id のインデックス両方使われると思うかもしれ ませんが片方しか使われません
  27. ◼順番合ってないと使われません select * from film order by release_year, title; インデックスの話~おまけ~

    71 Copyright© 2018 GxP, Inc. こう書いたらtitleのインデックス使われると思 うかもしれませんが、使われません
  28. ◼WHERE句のように順番を入れ替えても同じ意味になる場合は入れ替えてくれる select * from film where release_year=2006 and title =

    'Academy Dinosaur'; インデックスの話~おまけ~ 72 Copyright© 2018 GxP, Inc. 先にtitleのインデックスつかって絞り込んで から、release_yearの絞り込みをしてくれます
  29. ◼実行計画 •インデックスが使われてるかどうかがわかる •どのインデックスが使われてるのかがわかる •どんな風にデータを取ってこようとしているかがわかる Index Scan using idx_title on film

    (cost=0.28..8.29 rows=1 width=384) (actual time=0.032..0.033 rows=1 loops=1) Index Cond: ((title)::text = 'Academy Dinosaur'::text) Planning time: 0.156 ms Execution time: 0.059 ms インデックスの話~おまけ~ 73 Copyright© 2018 GxP, Inc.