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

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

社内勉強会用資料です。

65cc101435ac36057f2a2c80244c4c6e?s=128

Yoshitaka Okuda

August 01, 2016
Tweet

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పఈࢦೆॻʯʢᠳӭࣾʣ