実務家のためのSQL / SQL for Beginers

Slide 1

Slide 1 text

࣮຿ՈͷͨΊͷSQLೖ໳ @yoskhdia

Slide 2

Slide 2 text

About me • ԞాՂڗʢYoshitaka Okudaʣ • גࣜձࣾSocketʢKDDI Syn.ϗʔϧσΟϯάεάϧʔϓʣ  ΞʔΩςΫτ • Twitter @yoskhdia • interested in DDD & Reactive System & Team Building & more !

Slide 3

Slide 3 text

஫ҙࣄ߲ このスライドは、開発者でない営業や広報や運用の人間がSQLをカジュアルに書けるようになることを目的としています。まず使えるようになることを第一義とするため、厳密性よりハードルを下げることに注力します。標準SQLに準拠し各製品固有拡張には触れません。（そうと知らず使っていたらご指摘ください…）

Slide 4

Slide 4 text

͓͠ͳ͕͖ データベース、集合論と群論 SQL、まずはこれだけ SQL、書き方のコツ

Slide 5

Slide 5 text

͜͜Ͱѻ͏αϯϓϧσʔλ

Slide 6

Slide 6 text

id name division 1 ೾ฏ Ӧۀ 2 ւฏ ։ൃ 3 αβΤ ӡ༻ 4 ϚεΦ Ӧۀ Users id user_id button_id clicked 1 10 A TRUE 2 11 A TRUE 3 10 B FALSE 4 20 B TRUE දࣔཤྺ id title A Happy Birthday! B Welcome! Buttons

Slide 7

Slide 7 text

σʔλϕʔεɺ ू߹࿦ͱ܈࿦ IT'S "ؔ܎"Ϟσϧ NOT "ද"Ϟσϧ

Slide 8

Slide 8 text

ਤͰ֮͑Α͏

Slide 9

Slide 9 text

"ςʔϒϧ" ද ͱ ू߹ id name division 1 ೾ฏ Ӧۀ 2 ւฏ ։ൃ 3 αβΤ ӡ༻ 4 ϚεΦ Ӧۀ Users ͜ͷ1ͭͷ఺͕σʔλ ྫ͑͹ɺ೾ฏ Users

Slide 10

Slide 10 text

"ؔ܎" Relation id user_id button_id clicked 1 10 A TRUE 2 11 A TRUE 3 10 B FALSE 4 20 B TRUE දࣔཤྺ Buttons Users දࣔཤྺ ू߹ͱू߹ͷؔ܎ ྫ͑͹ɺදࣔཤྺ͸user_idͰ Usersͱؔ܎͍ͯ͠Δ

Slide 11

Slide 11 text

ද͸ू߹͔ΒσʔλΛऔΓग़ͨ݁͠Ռ w w ͘Β͍ʹͱΒ͑Δ id user_id button_id clicked title 1 10 A TRUE Happy Birthday! 2 11 A TRUE Happy Birthday! 3 10 B FALSE Welcome! 4 20 B TRUE Welcome! SELECT දࣔཤྺ.*, Buttons.title FROM දࣔཤྺ INNER JOIN Buttons ON දࣔཤྺ.button_id = Buttons.id

Slide 12

Slide 12 text

খ·ͱΊ 集合としてイメージする（表、テーブルと言葉では言うもののSQLを書く過程では集合としてイメージする）集合の関係を活用したり、集合からどんなデータを抽出するのかを記述するものがSQL

Slide 13

Slide 13 text

SQLɺ ·ͣ͸͜Ε͚ͩ جૅߏจ ࣮ફͷҰา໨

Slide 14

Slide 14 text

جૅߏจ ·ͣ͸͜Ε͚ͩ SELECT  何を FROM  どこから JOIN ~ ON  関連させて WHERE  どれを SELECT Buttons.id, Buttons.title, 表示履歴.clicked FROM 表示履歴 INNER JOIN Buttons ON 表示履歴.button_id = Buttons.id WHERE 表示履歴.clicked = TRUE id title clicked A Happy Birthday! TRUE A Happy Birthday! TRUE B Welcome! TRUE

Slide 15

Slide 15 text

جૅߏจ + α AS  名前付け GROUP BY  再集合化 HAVING  再集合の  どれを SELECT Buttons.id, Buttons.title, COUNT(*) AS click_count FROM 表示履歴 INNER JOIN Buttons ON 表示履歴.button_id = Buttons.id WHERE 表示履歴.clicked = TRUE GROUP BY Buttons.id, Buttons.title HAVING COUNT(*) >= 2 id title click_count A Happy Birthday! 2

Slide 16

Slide 16 text

id user_id button_id clicked title 1 10 A TRUE Happy Birthday! 2 11 A TRUE Happy Birthday! 3 10 B FALSE Welcome! 4 20 B TRUE Welcome! id title clicked A Happy Birthday! TRUE A Happy Birthday! TRUE B Welcome! TRUE id title click_count A Happy Birthday! 2 ΫϦοΫͨ͠ ΋ͷʹߜͬͯ 2ճҎ্ΫϦοΫͯ͠ Δ΋ͷʹߋʹߜͬͨ

Slide 17

Slide 17 text

ू߹ͰΠϝʔδ͢Δͱ

Slide 18

Slide 18 text

දࣔཤྺ clicked = TRUE COUNT(*) >= 2 Buttons 1.WHEREで絞る 2.GROUP BYでまとめる今回はButtonごとに 4.JOINで関連づける 3.HAVINGで絞る

Slide 19

Slide 19 text

Ճ޻Ͱ͸ͳ͘ߜΓࠐΈʂ

Slide 20

Slide 20 text

౓ʑग़ͯ͘Δ *(ΞελϦεΫ) ͱ͸Կ͔ とりあえず「ぜんぶ」の意で覚えよう SELECT * FROMに指定したテーブルの全ての列を対象とする A.*とすると「Aテーブルの全ての列」 SELECT COUNT(*) 特定の列に依らず行のまとまりを対象とする

Slide 21

Slide 21 text

JOINͷछྨ ·ͣ͸͜Ε͚ͩ INNER JOIN INNER JOIN ON <条件>としたとき、条件を満たさないデータは結果から除外される LEFT OUTER JOIN LEFT OUTER JOIN ON <条件>としたとき、条件を満たしていなくてもAのデータは全て結果に含まれ、B側はNULLとなる

Slide 22

Slide 22 text

͞Βͬͱग़͖ͯͨNULLͱ͸Կ͔ 魔物虚無捉え方は色々あれど、「データが無い」くらいで、まずは覚える

Slide 23

Slide 23 text

৚݅ࣜΛॻ͘ಓ۩ ·ͣ͸͜Ε͚ͩ =, !=, >, >=, <, <=, AND, OR, NOT != はExcelでいうと <> IS NULL, IS NOT NULL a = NULLはマッチしない。NULLは無。 IN (<値やサブクエリ>), EXISTS (<サブクエリ>) カッコ"()"の中を満たせばマッチ

Slide 24

Slide 24 text

ྫ WHERE id = 1 WHERE title = 'Welcome!' OR title = 'Thanks!' WHERE name IS NULL WHERE button_id IN ('A', 'B', 'C') WHERE EXISTS (SELECT 1 FROM Buttons WHERE Buttons.id = button_id)

Slide 25

Slide 25 text

ߴ౓ͳSQL΁ͷҰา CASE式場合分けを書ける。どこにでも！サブクエリ SELECTをネストして書ける。 COUNT関数, SUM関数, MAX関数, MIN関数 Excelにもありますよね。

Slide 26

Slide 26 text

CASEࣜ ʢSELECTͷதͰ࢖͏ྫʣ SELECT button_id, SUM(CASE WHEN clicked = TRUE THEN 1 ELSE 0 END) AS clicked, SUM(CASE WHEN clicked = TRUE THEN 0 ELSE 1 END) AS not_clicked FROM 表示履歴 GROUP BY button_id id clicked not_clicked A 2 0 B 1 1 CASE式で場合分けをして、一度の問い合わせで複数の集計を行っている

Slide 27

Slide 27 text

CASEࣜ ʢGROUP BYͰ࢖͏ྫʣ SELECT COUNT(*) AS clicked FROM 表示履歴 WHERE clicked = TRUE GROUP BY (CASE WHEN button_id IN ('A', 'C') THEN 0 ELSE 1 END) clicked 2 1 CASE式で場合分けをして、任意のボタンでグルーピングしている ※この例は数だけしか取得していないので見分けがつかず微妙… CASE button_id WHEN 'A' THEN 0 WHEN 'C' THEN 0 ELSE 1 END のようにWHENは続けて書くこともできる

Slide 28

Slide 28 text

αϒΫΤϦ SELECT id, button_id, (SELECT title FROM Buttons WHERE button_id = Buttons.id) AS button_title FROM 表示履歴 SELECT id FROM 表示履歴 AS H1 WHERE EXISTS (SELECT 1 FROM 表示履歴 AS H2 WHERE H1.button_id = H2.button_id AND H1.id < H2.id) id button_id button_title 1 A Happy Birthday! 2 A Happy Birthday! 3 B Welcome! 4 B Welcome! id 1 3 この例は同じテーブルで比較して重複データの抽出をしている（自己相関）

Slide 29

Slide 29 text

αϒΫΤϦ͸ͱͯ΋໘ന͍ ࣗ෼Ͱू߹Λ࡞ΕΔ ৽ੈք Buttons Users දࣔཤྺ

Slide 30

Slide 30 text

খ·ͱΊ 集合としてイメージする大きな集合を小さく絞り込むデータをグルーピングする集合同士を関連付ける CASE式とサブクエリは表現の幅をぐっと広げる覚えるとやりたいことの8割はカバーできる

Slide 31

Slide 31 text

SQLɺ ॻ͖ํͷίπ ू߹ΛΠϝʔδ͠ͳ͕Βॻ͘ʹ͸

Slide 32

Slide 32 text

͜ͷॱʹॻ͘ 1. FROM まずはどのデータを使うかを決める（主軸） 2. WHERE その中の何が欲しいかを決める 3. SELECT どんな結果として欲しいかを決める 4. （必要に応じて）JOIN マスタなど関係したデータを決める（WHEREやSELECTに影響）

Slide 33

Slide 33 text

ू߹ͷΠϝʔδΛࢥ͍ग़ͯ͠

Slide 34

Slide 34 text

දࣔཤྺ clicked = TRUE COUNT(*) >= 2 Buttons 1.WHEREで絞る 2.GROUP BYでまとめる今回はButtonごとに 4.JOINで関連づける 3.HAVINGで絞る 0.FROMを決める 5.結果をSELECT

Slide 35

Slide 35 text

ؾΛ͚ͭΔ͜ͱ

Slide 36

Slide 36 text

巨大なデータを巨大なまま扱わない集合の母数を考えて、絞り込みをしたうえで、集計なり加工なりをしよう。サブクエリは多用しすぎない必要なデータはまとめてSELECTした方が効率的なこともある。母数を意識して適材適所で使おう。 JOINのモチベーションは色々ある今回は説明の都合上、最後の結果に＋αするケースを主にしたけれど、WHEREやGROUP BYなど使うシーンは色々ある。FROMと同等に考えよう。（WHEREやGROUPを考える過程で足りないものを足していく感じ。）

Slide 37

Slide 37 text

ͱ͸͍͑ɺ࠷ॳ͸

Slide 38

Slide 38 text

いきなり難しい取り方をしようとしない取りたい集合をイメージ・分解して、最後にサブクエリなどを活用して結合する。そこから、まとめられそうなところをまとめたり、リファクタリングする。

Slide 39

Slide 39 text

·ͱΊ まずは、何を見たい・知りたいのかを決める。集合をイメージして、データがどこにありそうか考えよう。集合を小さくしたり、関連付けたり、新しい集合をつくったりして、欲しい形にしていこう。

Slide 40

Slide 40 text

ࢀߟ ͳͥ"ؔ܎"Ϟσϧͱ͍͏໊લͳͷʁ GROUP BY ͱ PARTITION BY 3஋࿦ཧ ―― ਆͷ͍ͳ͍࿦ཧ ॻ੶ʮୡਓʹֶͿSQLపఈࢦೆॻʯʢᠳӭࣾʣ