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

https://data-science-discussion.connpass.com/event/121248/
での発表資料

Transcript

1. SQL データサイエンス勉強会 第4回 Copyright© 2018 Growth xPartners, Inc. All rights reserved..
2. 自己紹介 ◼名前 •石村麻莉彩（いしむら ありさ） ◼仕事 •株式会社 GxP（この会場が職場） •ITエンジニア（プログラマ→SE→PLとかPMとかPOもどきとか） •データサイエンスちっくなことはまだまだこれからなので勉強中 ◼データサイエンス勉強会 •主催者です •現場の人同士で一緒に悩んでいける場をつくりたくてやってます •ありがたい話を聞くための勉強会はあまり目指してません 1 Copyright© 2018 GxP, Inc.
3. 想定している環境 Copyright© 2018 Growth xPartners, Inc. All rights reserved. 2 2015/1/1
4. 本番環境 分析環境 想定している環境 3 Copyright© 2018 GxP, Inc. システムの利用ユーザ システム担当者 システム DBMS データ ログファイル 分析担当者 DBアクセス ツール データ分析 ツール SQL結果 DBMS データ
5. 本番環境 分析環境 想定している環境 4 Copyright© 2018 GxP, Inc. システムの利用ユーザ システム担当者 システム DBMS データ ログファイル 分析担当者 DBアクセス ツール データ分析 ツール SQL結果 DBMS データ 参加者のみなさん
6. 本番環境 分析環境 想定している環境 5 Copyright© 2018 GxP, Inc. システムの利用ユーザ システム担当者 システム DBMS データ ログファイル 分析担当者 DBアクセス ツール データ分析 ツール SQL結果 DBMS データ 本番とは別に、分析用 の環境が作られている
7. 本番環境 分析環境 想定している環境 6 Copyright© 2018 GxP, Inc. システムの利用ユーザ システム担当者 システム DBMS データ ログファイル 分析担当者 DBアクセス ツール データ分析 ツール SQL結果 DBMS データ コミュニケーションがとれる。 どんなデータがあるのか、 どんな構成なのかなどを教え てもらえる。
8. 本番環境 分析環境 想定している環境 7 Copyright© 2018 GxP, Inc. システムの利用ユーザ システム担当者 システム DBMS データ ログファイル 分析担当者 DBアクセス ツール データ分析 ツール SQL結果 DBMS データ 同等環境を作り 時々必要な最新データをコピー （これは誰かがやってくれている ハズ）
9. 今日デモする環境とデータ 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.
10. SQL概要 Copyright© 2018 Growth xPartners, Inc. All rights reserved. 9 2015/1/1
11. 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
12. SQLとは ◼RDBMSにおける、データベースの定義やデータの操作を行うための言語 ◼標準SQL規格があるため、基本的な構文はどのRDBMSでも同じ ◼RDBMSによって、SQL構文や動作がやや異なる場合があるので注意 •PostgreSQLで動くSQLがOracleで動くとは限らない、ということ 11 Copyright© 2018 GxP, Inc.
13. 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
14. DDL（Data Definition Language） ◼データベースのスキーマ構造を作成・変更する ◼CREATE/DROP/ALTERなど →SELECTしかしないなら気にしなくてよい。 テーブルの作成や変更などを行う必要が出たら調べてください。 13 Copyright© 2018 GxP, Inc. SQL DDL DML DCL CREATE DROP ALTER
15. DML（Data Manipulation Language） ◼データの参照や更新を行う ◼SELECT • 登録されているデータを参照する ◼ INSERT/UPDATE/DELETE • データを登録・更新・削除する 14 Copyright© 2018 GxP, Inc. SQL DDL DML DCL SELECT INSERT UPDATE DELETE 今回の話
16. DCL（Data Control Language） ◼トランザクション制御を行う ◼BEGIN/COMMIT/ROLLBACKなど →SELECTしかしないなら気にしなくてよい。 INSERT/UPDATE/DELETEといった更新操作を行うときに調べてください。 15 Copyright© 2018 GxP, Inc. SQL DDL DML DCL BEGIN COMMIT ROLLBACK
17. まずはデータ構造を知ろう Copyright© 2018 Growth xPartners, Inc. All rights reserved. 16 2015/1/1
18. まずはデータ構造を知ろう いきなりSQLを書き始めることはできません。 まず、対象システムのデータ構造を知りましょう。 以下のような資料を確認します。 ◼ER図（Entity Relationship Diagram） •テーブル間の関係を表した図。全体の構造を掴むのによいです。 ◼テーブル定義書 •ひとつひとつのテーブルの定義が書かれている。テーブルや各項目の説明が記載されていることも。 ◼DDL文 •これしかない場合は、ツールを利用してER図を起こしてみるとよいです。 17 Copyright© 2018 GxP, Inc.
19. ER図（Entity Relationship Diagram） ◼こんなやつです 18 Copyright© 2018 GxP, Inc. どんなデータをどんなふう に保持しているかが大体 わかります
20. SELECTデモ Copyright© 2018 Growth xPartners, Inc. All rights reserved. 19 2015/1/1
21. 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.
22. SELECT文 ◼顧客の一覧を取得する SELECT * FROM customer; 21 Copyright© 2018 GxP, Inc.
23. SELECT文 22 Copyright© 2018 GxP, Inc. ◼結果サンプル
24. SELECT文 ◼特定の顧客のレンタル履歴を取得する SELECT rental_date, return_date, customer_id FROM rental WHERE customer_id = 5; 23 Copyright© 2018 GxP, Inc.
25. SELECT文 24 Copyright© 2018 GxP, Inc. ◼結果サンプル
26. 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.
27. SELECT文 26 Copyright© 2018 GxP, Inc. ◼結果サンプル
28. SELECT文の基礎～SELECT句～ Copyright© 2018 Growth xPartners, Inc. All rights reserved. 27 2015/1/1
29. SELECT句 ◼取得したいカラム（項目、列）を指定する ◼複数のテーブルから取得する場合は「テーブル名.カラム名」で書く •取得対象のテーブルが1つだけ、など 「テーブル名.」を省略してもどのテーブルのカラムなのかが自明な場合は省略ができる ◼ちなみに •「*」ですべてのカラムを指定 •「AS XXXXX」で取得結果のカラム名を変更 28 Copyright© 2018 GxP, Inc.
30. SELECT文 ◼顧客の一覧を取得する SELECT * FROM customer; 29 Copyright© 2018 GxP, Inc. *ですべてのカラムを取得
31. SELECT文 ◼特定の顧客のレンタル履歴を取得する SELECT rental_date, return_date, customer_id FROM rental WHERE customer_id = 5; 30 Copyright© 2018 GxP, Inc. 取得するカラムを指定
32. 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で別名をつけている
33. SELECT文の基礎～FROM句～ Copyright© 2018 Growth xPartners, Inc. All rights reserved. 32 2015/1/1
34. FROM句 ◼取得したいテーブルを指定する ◼複数のテーブルの情報を併せて取得したい場合は結合する •INNER JOIN •OUTER JOIN 33 Copyright© 2018 GxP, Inc.
35. SELECT文 ◼顧客の一覧を取得する SELECT * FROM customer; 34 Copyright© 2018 GxP, Inc. customerテーブルから取得
36. FROM句～INNER JOIN～ ◼単に「JOIN」と言うことも多い •「NATURAL JOIN」は「INNER JOIN」の仲間 •内部結合と呼ばれるもの ◼指定した条件でテーブル同士をくっつけて 条件に合致するレコードのみを取得する ◼ 構文 FROM テーブルA INNER JOIN テーブルB ON 結合条件 35 Copyright© 2018 GxP, Inc.
37. FROM句～INNER JOIN～ ◼顧客の氏名と顧客の住所を取得したい場合 36 Copyright© 2018 GxP, Inc. 住所の情報はcustomerテーブルには なく、address_idしか持っていない
38. FROM句～INNER JOIN～ ◼顧客の氏名と顧客の住所を取得したい場合 37 Copyright© 2018 GxP, Inc. IDじゃなくて実際の住所がほしい
39. FROM句～INNER JOIN～ ◼顧客の氏名と顧客の住所を取得する 38 Copyright© 2018 GxP, Inc. 実際の住所はaddressテーブルにある
40. FROM句～INNER JOIN～ ◼顧客の氏名と顧客の住所を取得したい場合 39 Copyright© 2018 GxP, Inc. address_idごとに、addressの値が保管 されている
41. 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で結合
42. FROM句～INNER JOIN～ ◼顧客の氏名と顧客の住所を取得したい場合 41 Copyright© 2018 GxP, Inc. 顧客ごとに名前と住所を取得できた
43. 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. おまけ
44. FROM句～INNER JOIN～ ◼顧客の氏名と顧客の住所を取得したい場合 43 Copyright© 2018 GxP, Inc. おまけ
45. FROM句～OUTER JOIN～ ◼指定した条件でテーブル同士をくっつけて 条件に合致しないレコードも取得する •外部結合と呼ばれるもの ◼主に使うのは、LEFT OUTER JOINだが、ほかにも2種類ある •RIGHT OUTER JOIN •FULL OUTER JOIN ◼ 構文 FROM テーブルA LEFT OUTER JOIN テーブルB ON 結合条件 44 Copyright© 2018 GxP, Inc.
46. 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.
47. FROM句～OUTER JOIN～ ◼ERからINNERかOUTERのどちらを使うべきか判断できる INNER JOINでOK OUTER JOINを使わないと、行が欠落する 46 Copyright© 2018 GxP, Inc.
48. FROM句～OUTER JOIN～ ◼ERはテーブル同士のつながり方を表す ◼線がつながってるテーブル同士はJOINして参照できる ◼図のような一本線は、かならず1件つながっているよ、という意味 47 Copyright© 2018 GxP, Inc. filmからlanguageのつながりは 必ず1件
49. FROM句～OUTER JOIN～ ◼まるいのがついてたら、0件、つまりつながってないかもしれないという意味 ◼枝別れしていたら、たくさんつながってるかもしれないという意味 48 Copyright© 2018 GxP, Inc. languageからfilmへのつながりは 0件かもしれないし、たくさんあるかもしれない
50. FROM句～OUTER JOIN～ ◼まるいのと一本線の場合は、0件または1件つながっている 49 Copyright© 2018 GxP, Inc. categoryからfilm_categoryへのつながりは 0件か１件
51. FROM句～OUTER JOIN～ ◼ERからINNERかOUTERのどちらを使うべきか判断できる まるがないので、INNER JOINでOK まるがあるので、OUTER JOINを使わないと、行が欠落する 50 Copyright© 2018 GxP, Inc.
52. 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.
53. FROM句～OUTER JOIN～ 52 Copyright© 2018 GxP, Inc. このlanguage_idからlanguageは必 ず1件取れる つまりlanguageの nameは必ず取れる
54. 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.
55. FROM句～OUTER JOIN～ 54 Copyright© 2018 GxP, Inc. このlanguageのfilmはない、という 情報が取れる INNER JOINに変えるとこの行は取 れなくなる
56. 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.
57. FROM句～OUTER JOIN～ 56 Copyright© 2018 GxP, Inc. INNER JOINにすると、このフィルム 数が0である、という情報が取れな くなってしまう 顧客マスタ×利用履歴 従業員マスタ×出勤履歴 のように、履歴がなくてもマスタの情 報は全部もってきたい！という場合に 使うことが多いハズ
58. 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. 慣れないうちは、結合条件を両方出力 してあげると、何が起きているのかち ょっとわかるかも
59. SELECT文の基礎～WHERE句～ Copyright© 2018 Growth xPartners, Inc. All rights reserved. 58 2015/1/1
60. WHERE句 ◼取得したい行の条件を指定する ◼すべての行を取得したいわけではない場合に使用する 59 Copyright© 2018 GxP, Inc.
61. SELECT文 ◼特定の顧客のレンタル履歴を取得する SELECT rental_date, return_date, customer_id FROM rental WHERE customer_id = 5; 60 Copyright© 2018 GxP, Inc. customer_idが5の行だけ取得する
62. そのほか 今日割愛するもので、次のステップとして知っていくとよさそうなものの例 ◼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.
63. ちょっとパフォーマンスの話 Copyright© 2018 Growth xPartners, Inc. All rights reserved. 62 2015/1/1
64. 持ってくるデータが多すぎるときに起こること ◼データベースサーバーがいっぱいいっぱいになる •自分のパソコンでも、サイズの大きなファイルを開こうとすると、遅かったりしますよね？ ◼データベースサーバーから分析用のPCに運ぶのが大変 •重いファイルをインターネット上からダウンロードしようとすると、遅かったりしますよね？ •ネットワークが貧弱だといつまでたっても終わらないなんてことも データベースサーバー 持ってくるデータが多すぎる 63 Copyright© 2018 GxP, Inc. DBMS データ 分析用のPC DBアクセス ツール
65. まずかんたんにできる対策 ◼取得するカラム（項目、列）を減らす •とりあえず全部取得するのをやめる •テーブル定義書を見て、データ型・サイズを確認する •サイズが大きいのにたいして必要じゃないカラムもあったりする 例：画像を登録できるシステムで、画像データが入ったカラムを取得している 例：説明文などの長い文章が入ったカラムを取得している ◼取得する行を減らす •とりあえず全部取得するのをやめる •欲しい行が何かの条件で絞り込めないか確認する 例：何年もデータを溜めているテーブルから、直近のデータだけ取得する •全部まとめて持ってくるのではなく、分割して取得すると良い場合も ◼１年分ずつ、とか、店舗IDごと、とか ◼分割する場合は有識者にその分割の仕方で正しく取得できそうか見てもらうとよいと思います 持ってくるデータが多すぎる 64 Copyright© 2018 GxP, Inc.
66. インデックスとは ◼テーブルの中のよく使うカラムの分身みたいなもの •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 テーブル インデックス
67. ◼使える例 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 ②インデックスで 見つけた行を 内部キーを使って 取得する
68. ◼使えてない例 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
69. インデックスの話～おまけ～ Copyright© 2018 Growth xPartners, Inc. All rights reserved. 68 2015/1/1
70. ◼参考：filmテーブルのインデックス インデックスの話～おまけ～ 69 Copyright© 2018 GxP, Inc.
71. ◼1テーブルにつき1つしか使えません select * from film where title = 'Academy Dinosaur' and language_id=1; インデックスの話～おまけ～ 70 Copyright© 2018 GxP, Inc. こう書いたらtitleのインデックスとlanguage_id のインデックス両方使われると思うかもしれ ませんが片方しか使われません
72. ◼順番合ってないと使われません select * from film order by release_year, title; インデックスの話～おまけ～ 71 Copyright© 2018 GxP, Inc. こう書いたらtitleのインデックス使われると思 うかもしれませんが、使われません
73. ◼WHERE句のように順番を入れ替えても同じ意味になる場合は入れ替えてくれる select * from film where release_year=2006 and title = 'Academy Dinosaur'; インデックスの話～おまけ～ 72 Copyright© 2018 GxP, Inc. 先にtitleのインデックスつかって絞り込んで から、release_yearの絞り込みをしてくれます
74. ◼実行計画 •インデックスが使われてるかどうかがわかる •どのインデックスが使われてるのかがわかる •どんな風にデータを取ってこようとしているかがわかる 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.
75. さいごに Copyright© 2018 Growth xPartners, Inc. All rights reserved. 74 2015/1/1
76. ◼今日理解してもらいたかった話 •RDBの位置づけ •SQLの種類 •SELECT句の書き方 •FROM句の書き方 •WHERE句ちょっと •パフォーマンスちょっと（とりあえず全部取ってくるのやめる） ◼次聞きたいテーマあったら教えてください •WHERE句 •集計関数/GROUPBY/HAVING •サブクエリ •インデックス •実行計画 •などなど さいごに 75 Copyright© 2018 GxP, Inc.
77. CONFIDENTIAL •本文書は、株式会社GxPが著作権その他の権利を有する営業秘密（含サプライヤー等第三者が権利を有するもの）です。 •当社の許可なく複製し利用すること、また漏洩することは「著作権法」「不正競争防止法」によって禁じられております。 •本資料内の社名・製品名は各社の登録商標です。 76 Copyright© 2018 Growth xPartners, Inc. All rights reserved.