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

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

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

社内勉強会用資料です。

Yoshitaka Okuda

August 01, 2016
Tweet

More Decks by Yoshitaka Okuda

Other Decks in Programming

Transcript

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

    View Slide

  2. About me
    • ԞాՂڗʢYoshitaka Okudaʣ
    • גࣜձࣾSocketʢKDDI Syn.ϗʔϧσΟϯάεάϧʔϓʣ

    ΞʔΩςΫτ
    • Twitter @yoskhdia
    • interested in DDD & Reactive System & Team
    Building & more !

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  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

    View Slide

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

    View Slide

  8. ਤͰ֮͑Α͏

    View Slide

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

    View Slide

  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ͱؔ܎͍ͯ͠Δ

    View Slide

  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

    View Slide

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

    View Slide

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

    View Slide

  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

    View Slide

  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

    View Slide

  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ճҎ্ΫϦοΫͯ͠
    Δ΋ͷʹߋʹߜͬͨ

    View Slide

  17. ू߹ͰΠϝʔδ͢Δͱ

    View Slide

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

    View Slide

  19. Ճ޻Ͱ͸ͳ͘ߜΓࠐΈʂ

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide


  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)

    View Slide

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

    View Slide

  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式で場合分けをして、
    一度の問い合わせで複数の
    集計を行っている

    View Slide

  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は続けて書くこと
    もできる

    View Slide

  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
    この例は同じテーブルで比較
    して重複データの抽出をして
    いる(自己相関)

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  33. ू߹ͷΠϝʔδΛࢥ͍ग़ͯ͠

    View Slide

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

    View Slide

  35. ؾΛ͚ͭΔ͜ͱ

    View Slide

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

    View Slide

  37. ͱ͸͍͑ɺ࠷ॳ͸

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide