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

社内勉強会用資料です。

Transcript

1. ࣮຿ՈͷͨΊͷSQLೖ໳ @yoskhdia
2. About me • ԞాՂڗʢYoshitaka Okudaʣ • גࣜձࣾSocketʢKDDI Syn.ϗʔϧσΟϯάεάϧʔϓʣ
 ΞʔΩςΫτ • Twitter @yoskhdia • interested in DDD & Reactive System & Team Building & more !
3. ஫ҙࣄ߲ このスライドは、開発者でない営業や広報や運用 の人間がSQLをカジュアルに書けるようになること を目的としています。 まず使えるようになることを第一義とするため、 厳密性よりハードルを下げることに注力します。 標準SQLに準拠し各製品固有拡張には触れません。 （そうと知らず使っていたらご指摘ください…）
4. ͓͠ͳ͕͖ データベース、集合論と群論 SQL、まずはこれだけ SQL、書き方のコツ
5. ͜͜Ͱѻ͏αϯϓϧσʔλ
6. 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
7. σʔλϕʔεɺ ू߹࿦ͱ܈࿦ IT'S "ؔ܎"Ϟσϧ NOT "ද"Ϟσϧ
8. ਤͰ֮͑Α͏
9. "ςʔϒϧ" ද ͱ ू߹ id name division 1 ೾ฏ Ӧۀ 2 ւฏ ։ൃ 3 αβΤ ӡ༻ 4 ϚεΦ Ӧۀ Users ͜ͷ1ͭͷ఺͕σʔλ ྫ͑͹ɺ೾ฏ Users
10. "ؔ܎" 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ͱؔ܎͍ͯ͠Δ
11. ද͸ू߹͔ΒσʔλΛऔΓग़ͨ݁͠Ռ 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
12. খ·ͱΊ 集合としてイメージする （表、テーブルと言葉では言うもののSQLを書 く過程では集合としてイメージする） 集合の関係を活用したり、集合からどんなデータ を抽出するのかを記述するものがSQL
13. SQLɺ ·ͣ͸͜Ε͚ͩ جૅߏจ ࣮ફͷҰา໨
14. جૅߏจ ·ͣ͸͜Ε͚ͩ 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
15. جૅߏจ + α 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
16. 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ճҎ্ΫϦοΫͯ͠ Δ΋ͷʹߋʹߜͬͨ
17. ू߹ͰΠϝʔδ͢Δͱ
18. දࣔཤྺ clicked = TRUE COUNT(*) >= 2 Buttons 1.WHEREで絞る 2.GROUP BYでまとめる 今回はButtonごとに 4.JOINで関連づける 3.HAVINGで絞る
19. Ճ޻Ͱ͸ͳ͘ߜΓࠐΈʂ
20. ౓ʑग़ͯ͘Δ *(ΞελϦεΫ) ͱ͸Կ͔ とりあえず「ぜんぶ」の意で覚えよう SELECT * FROMに指定したテーブルの全ての列を対象と する A.*とすると「Aテーブルの全ての列」 SELECT COUNT(*) 特定の列に依らず行のまとまりを対象とする
21. JOINͷछྨ ·ͣ͸͜Ε͚ͩ INNER JOIN <A> INNER JOIN <B> ON <条件>としたとき、条 件を満たさないデータは結果から除外される LEFT OUTER JOIN <A> LEFT OUTER JOIN <B> ON <条件>としたと き、条件を満たしていなくてもAのデータは全 て結果に含まれ、B側はNULLとなる
22. ͞Βͬͱग़͖ͯͨNULLͱ͸Կ͔ 魔物 虚無 捉え方は色々あれど、「データが無い」くらいで、 まずは覚える
23. ৚݅ࣜΛॻ͘ಓ۩ ·ͣ͸͜Ε͚ͩ =, !=, >, >=, <, <=, AND, OR, NOT != はExcelでいうと <> IS NULL, IS NOT NULL a = NULLはマッチしない。NULLは無。 IN (<値やサブクエリ>), EXISTS (<サブクエリ>) カッコ"()"の中を満たせばマッチ
24. ྫ 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)
25. ߴ౓ͳSQL΁ͷҰา CASE式 場合分けを書ける。どこにでも！ サブクエリ SELECTをネストして書ける。 COUNT関数, SUM関数, MAX関数, MIN関数 Excelにもありますよね。
26. 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式で場合分けをして、 一度の問い合わせで複数の 集計を行っている
27. 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は続けて書くこと もできる
28. αϒΫΤϦ 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 この例は同じテーブルで比較 して重複データの抽出をして いる（自己相関）
29. αϒΫΤϦ͸ͱͯ΋໘ന͍ ࣗ෼Ͱू߹Λ࡞ΕΔ ৽ੈք Buttons Users දࣔཤྺ
30. খ·ͱΊ 集合としてイメージする 大きな集合を小さく絞り込む データをグルーピングする 集合同士を関連付ける CASE式とサブクエリは表現の幅をぐっと広げる 覚えるとやりたいことの8割はカバーできる
31. SQLɺ ॻ͖ํͷίπ ू߹ΛΠϝʔδ͠ͳ͕Βॻ͘ʹ͸
32. ͜ͷॱʹॻ͘ 1. FROM まずはどのデータを使うかを決める（主軸） 2. WHERE その中の何が欲しいかを決める 3. SELECT どんな結果として欲しいかを決める 4. （必要に応じて）JOIN マスタなど関係したデータを決める（WHEREやSELECTに影響）
33. ू߹ͷΠϝʔδΛࢥ͍ग़ͯ͠
34. දࣔཤྺ clicked = TRUE COUNT(*) >= 2 Buttons 1.WHEREで絞る 2.GROUP BYでまとめる 今回はButtonごとに 4.JOINで関連づける 3.HAVINGで絞る 0.FROMを決める 5.結果をSELECT
35. ؾΛ͚ͭΔ͜ͱ
36. 巨大なデータを巨大なまま扱わない 集合の母数を考えて、絞り込みをしたうえで、集計な り加工なりをしよう。 サブクエリは多用しすぎない 必要なデータはまとめてSELECTした方が効率的なこと もある。母数を意識して適材適所で使おう。 JOINのモチベーションは色々ある 今回は説明の都合上、最後の結果に＋αするケースを 主にしたけれど、WHEREやGROUP BYなど使うシーンは 色々ある。FROMと同等に考えよう。（WHEREやGROUPを 考える過程で足りないものを足していく感じ。）
37. ͱ͸͍͑ɺ࠷ॳ͸
38. いきなり難しい取り方をしようとしない 取りたい集合をイメージ・分解して、最後にサ ブクエリなどを活用して結合する。 そこから、まとめられそうなところをまとめた り、リファクタリングする。
39. ·ͱΊ まずは、何を見たい・知りたいのかを決める。 集合をイメージして、データがどこにありそうか 考えよう。 集合を小さくしたり、関連付けたり、新しい集合 をつくったりして、欲しい形にしていこう。
40. ࢀߟ ͳͥ"ؔ܎"Ϟσϧͱ͍͏໊લͳͷʁ GROUP BY ͱ PARTITION BY 3஋࿦ཧ ―― ਆͷ͍ͳ͍࿦ཧ ॻ੶ʮୡਓʹֶͿSQLపఈࢦೆॻʯʢᠳӭࣾʣ