データ分析力を高めるビジネスパーソンのためのSQL入門

Transcript

1. ೖ໳
   
   42-ษڧձ
   σʔλ෼ੳྗΛߴΊΔ
   ϏδωεύʔιϯͷͨΊͷ
   42-
   

   View Slide

2. © )JLBSV5BLBIBTIJ
   ߴڮޫ
   )JLBSV5BLBIBTIJ
   ೥ʹ৽ଔͰϠϑʔגࣜձࣾʹೖࣾ͠ɺ8FCΤϯδχΞͱͯ͠αʔϏεͷ։ൃɺӡ༻ɺا
   ըɺఏҊɺͳͲ෯޿͘αʔϏεʹߩݙɻ೥͔Β͸גࣜձࣾΠʔϒοΫΠχγΞςΟϒδϟύ
   ϯͰσʔλαΠΤϯεάϧʔϓΛ্ཱͪ͛෦௕ͱͯࣾ͠಺ͷσʔλ׆༻Λଅਐɻ೥͔Β֎ࢿ
   ܥίϯαϧςΟϯάاۀʹೖࣾ͠ɺ42-Λ࢖ͬͨσʔλ෼ੳͳͲσʔλΛ׆༻ͨ͠ϚʔέςΟϯ
   άࢧԉΛ࣮ࢪɻ
   ࣗݾ঺հ
   1SPpMF
   ෳۀͱͯ͠#-&"(6&ͷΫϥϒνʔϜͰɺϚʔέςΟϯάετϥς
   δετͱͯ͠σʔλ෼ੳͷαϙʔτΛߦͬͨΓɺͦͷଞෳ਺ࣾͰ
   42-Λ׆༻ͨ͠σʔλ෼ੳͷαϙʔτܦݧ͋Γɻʰσʔλ෼ੳྗΛ
   ߴΊΔϏδωεύʔιϯͷͨΊͷ42-ೖ໳ʱͷஶॻɻ
   MJHIU
   

   View Slide

3. <13>σʔλ෼ੳྗΛߴΊΔϏδωεύʔιϯͷͨΊͷ42-ೖ໳
   ઈࢍൃചத
   w42-͕શ͘Θ͔Βͳ͍ਓ͕ಡΜͰ΋σʔλ෼ੳͰ
   ࢖͏جຊతͳ42-͕͜ͷ࡭Ͱֶ΂Δ
   wϋϯζΦϯܗࣜͰ࣮ફΛҙࣝͨ͠42-ֶ͕΂Δ
   wϏδωεॻͱͯ͠ಡΈ΍͍͢ϖʔδʹऩ·Δ಺༰
   IUUQTXXXBNB[PODPKQEQ
   ೥݄ൃച
   

   View Slide

4. wࠓ42-ΛֶͿ΂͖
   ͭͷཧ༝
   ΞδΣϯμ
   "HFOEB
   wຊॻͷ
   ͭͷັྗ
   wσʔλ෼ੳͰ࢖͏42-ͷ
   ͭͷಛ௃
   

   View Slide

5. wࠓ42-ΛֶͿ΂͖
   ͭͷཧ༝
   ΞδΣϯμ
   "HFOEB
   wຊॻͷ
   ͭͷັྗ
   wσʔλ෼ੳͰ࢖͏42-ͷ
   ͭͷಛ௃
   

   View Slide

6. © )JLBSV5BLBIBTIJ
   
   42-ͱ͸ʰ4USVDUVSFE2VFSZ-BOHVBHFʱͷུͰʰΤε
   ΩϡʔΤϧʱͱಡΉ
   
   42-͸σʔλϕʔε্ͷςʔϒϧʹ֨ೲ͞ΕͨσʔλΛऔ
   ಘ͢Δͱ͖ʹ࢖͏ݴޠ
   
   Ϗδωεύʔιϯ͕࢖͏42-ͱΤϯδχΞ͕࢖͏42-͸ҧ͏
   
   σʔλ෼ੳͰ࢖͏42-ͷͭͷಛ௃
   8IBU42-
   

   View Slide

7. © )JLBSV5BLBIBTIJ
   σʔλ෼ੳͰ࢖͏42-ͷಛ௃
   8IBU42-
   VTFS@JE HFOEFS CJSUI
   " உੑ
   " ঁੑ
   ɾɾɾ ɾɾɾ ɾɾɾ
   QSPEVDU@JE QSPEVDU@OBNF QSJDF
   # ͓͍͍͠ਫ
   # γϟʔϓϖϯ
   ɾɾɾ ɾɾɾ ɾɾɾ
   42-
   VTFS@JE HFOEFS CJSUI
   " உੑ
   ࣮ߦ݁Ռ
   4&-&$5'30.VTFST
   8)&3&VTFS@JEb"`
   σʔλϕʔε ςʔϒϧ
   42-ͱ͸ʰ4USVDUVSFE2VFSZ-BOHVBHFʱͷུͰʰΤεΩϡʔΤϧʱͱಡΉ
   42-͸σʔλϕʔε্ͷςʔϒϧʹ֨ೲ͞ΕͨσʔλΛऔಘ͢Δͱ͖ʹ࢖͏ݴޠ
   

   View Slide

8. © )JLBSV5BLBIBTIJ
   σʔλ෼ੳͰ࢖͏42-ͷಛ௃
   8IBU42-
   Ϗδωεύʔιϯ͕࢖͏42-ͱΤϯδχΞ͕࢖͏42-͸ҧ͏
   wσʔλ෼ੳͰ͸جຊతʹ͸σʔλͷऔಘʢ4&-&$5ʣΛ
   ֮͑Ε͹Α͍
   w42-ͷॲཧ଎౓ʹ͍ͭͯҙࣝ͢Δඞཁੑ͕௿͍
   wجຊతͳߏจΛ֮͑Ε͹ͣͬͱ࢖͍ଓ͚Δ͜ͱ͕Ͱ͖Δ
   wΞϓϦέʔγϣϯ։ൃʹඞཁͳ42-ʢ4&-&$5ɹ
   */4&35%&-&5&61%"5&$3&"5&ʣΛ֮͑Δ
   ඞཁ͕͋Δ
   wϛϦඵ୯ҐͰॲཧ଎౓Λҙࣝ͢Δඞཁ͕͋ΔʢͦͷͨΊ
   ΠϯσοΫεͳͲͷσʔλϕʔεͷ஌ࣝ΋ඞཁʹͳΔʣ
   w৽͍͠42-ͷߏจΛ֮͑ͯΑΓޮ཰తͳ42-Λॻ͘ඞཁ
   ͕͋Δ
   Ϗδωεύʔιϯ͕࢖͏42- ΤϯδχΞ͕࢖͏42-
   

   View Slide

9. wࠓ42-ΛֶͿ΂͖
   ͭͷཧ༝
   ΞδΣϯμ
   "HFOEB
   wຊॻͷ
   ͭͷັྗ
   wσʔλ෼ੳͰ࢖͏42-ͷ
   ͭͷಛ௃
   

   View Slide

10. © )JLBSV5BLBIBTIJ
    ᶃσʔλ෼ੳʹ͓͚Δ
    42-͸ίεύ͕Α͍
    ᶄ࣮຿Ͱ෯޿͘࢖͑Δ
    ᶅֻ͚ࢉͷεΩϧͱͯ͠
    ໾ཱͭ
    σʔλ෼ੳͰֶͿ42-͸ֶशίετ
    ͕௿͘ɺҰ౓ֶ΂͹ීวతʹ׆Ͱ͖
    ΔεΩϧ
    42-͸৬छΛ໰Θͣ͋ΒΏΔ৬छͰ
    σʔλ෼ੳͰ׆༻Ͱ͖ΔͨΊ൚༻ੑ
    ͕ߴ͍εΩϧ
    σʔλநग़ΛΤϯδχΞʹཔΒͣɺ
    42-Λ࢖͏͜ͱͰࣗ෼ͷཉ͍͠σʔ
    λΛૉૣ͘ूܭ͢Δ͜ͱ͕Ͱ͖Δ
    ࠓ42-ΛֶͿ΂͖ͭͷཧ༝
    8IZ42-
    

    View Slide

11. © )JLBSV5BLBIBTIJ
    ᶃσʔλ෼ੳͷ42-͸ίεύ͕Α͍
    ׆
    ༻
    ͷ
    ෯
    ͕
    ڱ
    ͍
    ׆
    ༻
    ͷ
    ෯
    ͕
    ޿
    ͍
    ֶशίετ͕௿͍
    ֶशίετ͕ߴ͍
    42-
    Ұൠతͳϓϩάϥϛϯ
    άݴޠʹൺ΂ֶͯशί
    ετ͕௿͘ɺҰ౓ֶश
    ͢Ε͹௕ظؒ࢖͑Δε
    Ωϧͱͯ͠໾ཱͭ
    शಘͰ͖Ε͹σʔλ෼
    ੳ΍ιϑτ΢ΣΞ։ൃ
    ͳͲ෯޿͘࢖͑ΔϝϦ
    οτ͸͋Δֶ͕शίε
    τ͕ඇৗʹߴ͍
    Ұൠతͳ
    ϓϩάϥϛϯάݴޠ
    ୭Ͱ΋ѻ͍΍͘͢σʔ
    λ෼ੳͰ΋׆༻Ͱ͖Δ
    ͕ɺѻ͑Δσʔλͷྔ
    ʹݶք͕͋Γෳࡶͳ෼
    ੳ͕͠ʹ͍͘
    ΤΫηϧ
    ˞ॾઆ͋Γ
    ࠓ42-ΛֶͿ΂͖ͭͷཧ༝
    

    View Slide

12. © )JLBSV5BLBIBTIJ
    ୹࣌ؒͰجຊతͳཧղ͸Ͱ͖ΔͷͰ࣮຿Ͱ࢖͍ଓ͚Δ͜ͱͰεΩϧΛशಘͰ͖Δ
    ᶃσʔλ෼ੳͷ42-͸ίεύ͕Α͍
    ࠓ42-ΛֶͿ΂͖ͭͷཧ༝
    
    ϖʔδͷຊΛ࡭ಡ
    Ί͹جຊతͳ42-ΛֶͿ
    ͜ͱ͕Ͱ͖Δ
    IºճͷΦϯϥΠϯ
    ߨ࠲Ͱجຊతͳ42-ߏจ
    ΛֶͿ͜ͱ͕Ͱ͖Δ
    ۀ຿Ͱ42-Λ࢖͍ଓ͚Ε
    ͹໿൒೥ͰσʔλΞφϦ
    ετͱͯ͠42-͕࢖͑Δ
    Α͏ʹͳΔ
    Ԡ༻
    جૅ
    

    View Slide

13. © )JLBSV5BLBIBTIJ
    ᶄ࣮຿Ͱ෯޿͘࢖͑Δ
    ࠓ42-ΛֶͿ΂͖ͭͷཧ༝
    େاۀͳͲͰΑ͋͘ΔҰൠతͳσʔλ෼ੳ؀ڥ
    42-
    42-
    ෼ੳ݁Ռ
    σʔλ
    42-Λ࢖͏͜ͱͰͦͷ࣌ʑʹԠͯ͡ඞཁͳσʔλ
    Λૉૣ͘ूܭ͢Δ͜ͱ͕Ͱ͖Δ
    w ΤϯδχΞʹґཔͤͣʹࣗ෼͕ཉ͍͠σʔλΛૉૣ͘ूܭ
    w 1%$"Λߴ଎Ͱճ͢͜ͱ͕Ͱ͖Δ
    جװγεςϜ %8)ʢσʔλ΢ΣΞϋ΢εʣ #*πʔϧ
    

    View Slide

14. © )JLBSV5BLBIBTIJ
    ᶄ࣮຿Ͱ෯޿͘࢖͑Δ
    ࠓ42-ΛֶͿ΂͖ͭͷཧ༝
    খن໛ɾݸਓͰߦ͏σʔλ෼ੳ؀ڥ
    42-
    ϑΝΠϧΛ
    μ΢ϯϩʔυ
    ϑΝΠϧ͔Βࣗ
    ෼ͷ1$ʹσʔλ
    Λొ࿥
    ෼ੳ݁Ռ
    w 1ZUIPOͳͲͷϓϩάϥϛϯάݴޠͰσʔλΛѻ͏ʹ͸೉қ౓͕ߴ͍
    w ΤΫηϧΛ࢖͏ͱѻ͑Δσʔλͷྔ͕ݶΒΕΔσʔλͷॲཧʹ͕͔͔࣌ؒΔ
    σʔλ෼ੳ؀ڥ͕੔͍ͬͯͳͯ͘΋ɺݩσʔλΛࣗ෼ͷ1$ʹೖΕΔ
    ͜ͱͰखݩͰ42-Λ࢖ͬͯσʔλ෼ੳ͕Ͱ͖Δ
    جװγεςϜ ݩσʔλʢDTWϑΝΠϧͳͲʣ ࣗ෼ͷ1$
    

    View Slide

15. © )JLBSV5BLBIBTIJ
    ᶅֻ͚ࢉͷεΩϧͱͯ͠໾ཱͭ
    ࠓ42-ΛֶͿ΂͖ͭͷཧ༝
    42-
    ϚʔέςΟϯά σʔλΞφϦετ
    ܦӦاը Ӧۀ
    w &$αΠτͷߦಈ෼ੳ
    w ސ٬ͷߪങ෼ੳ
    w ޿ࠂ΍ΩϟϯϖʔϯͷޮՌݕূ
    w ೥౓ผച্
    w ࢛൒ظ͝ͱͷച্
    w ސ٬ηάϝϯτ෼ੳ
    w %8)͔ΒσʔλΛநग़
    w #*πʔϧ͔Βσʔλ઀ଓ
    w σʔλͷલॲཧ
    w ചΕے঎඼ͷ෼ੳ
    w ੒໿݅਺෼ੳ
    w ஍Ҭผ෼ੳ
    ༷ʑͳ৬छͰ࣮຿ͱ
    ͯ͠׆༻͠΍͍͢
    42-͸୭Ͱ΋࣮຿Ͱ࢖͍΍͍͢εΩϧͳͷͰͲΜͳ৬छͰ΋෯޿͘׆༻Ͱ͖Δ
    

    View Slide

16. wࠓ42-ΛֶͿ΂͖
    ͭͷཧ༝
    ΞδΣϯμ
    "HFOEB
    wຊॻͷ
    ͭͷັྗ
    wσʔλ෼ੳͰ࢖͏42-ͷ
    ͭͷಛ௃
    

    View Slide

17. © )JLBSV5BLBIBTIJ
    ຊॻͷͭͷັྗ
    ʰσʔλ෼ੳྗΛߴΊΔϏδωεύʔιϯͷͨΊͷ42-ೖ໳ʱͷղઆ
    
    ແྉͰެ։ͨ͠42-ษڧձࢿྉͷ্Ґޓ׵
    
    42-Λશ͘஌Βͳ͍ਓͰ΋ϋϯζΦϯΛத৺ͱ࣮ͨ͠ફͰ
    ࢖͑Δ42-ֶ͕΂Δ
    
    ࣮຿ͷσʔλ෼ੳʹඞཁͳ಺༰ʹಛԽ͢Δ͜ͱͰޮ཰Αֶ͘΂Δ
    
    

    View Slide

18. © )JLBSV5BLBIBTIJ
    ग़൛ͷഎܠ
    ຊॻͷಛ௃ɹᶃແྉͰެ։ͨ͠42-ษڧձࢿྉͷ্Ґޓ׵
    IUUQTOPUFDPNIJLBSVUOOBBB
    OPUFͰʮσʔλ෼ੳͷͨΊͷ42-ษڧ
    ձʙॳڃฤϋϯζΦϯʙʯΛແྉͰެ։
    ΑΓ࣮຿Ͱ࢖͑ΔεΩϧͱ͠
    ͯମܥతʹ·ͱΊΔͨΊʹຊ
    ͱͯ͠ग़൛
    

    View Slide

19. © )JLBSV5BLBIBTIJ
    ແྉ൛ͱຊॻͷҧ͍
    ຊॻͷಛ௃ɹᶃແྉͰެ։ͨ͠42-ษڧձࢿྉͷ্Ґޓ׵
    
    جຊతʹ͸ຊॻ͸OPUFͰແྉެ։ͨ͠42-ษڧձࢿྉͷ্Ґޓ׵
    ຊॻ ແྉެ։൛
    42-ͷ֓ཁઆ໌ ˕ ˚
    42-ͷ࣮ߦ؀ڥͷߏங ˕ ˚
    42-ͷߏจͷઆ໌ ˕ ˓
    ࣮຿Ͱ࢖͏5JQT ˕ ˓
    42-࣮ߦ݁Ռͷදࣔ ˕ º
    

    View Slide

20. © )JLBSV5BLBIBTIJ
    w42-Λ͜Ε͔Βֶ΅͏ͱࢥ͍ͬͯΔਓ
    w࢓ࣄͰ42-Λ৮Γ࢝Ί͚ͨͲΠϚΠνΑ͘Θ͔Βͳ͍ਓ
    w&YDFMΛ࢖ͬͯσʔλूܭ͍ͯ͠Δਓ
    w΋ͬͱޮ཰తͳ؀ڥͰσʔλ෼ੳ͍ͨ͠Ϗδωεύʔιϯ
    ຊॻͷର৅ऀ
    ຊॻͷಛ௃ɹᶄ42-Λશ͘஌Βͳ͍ਓͰ΋ϋϯζΦϯΛத৺ͱ࣮ͨ͠ફͰ࢖͑Δ42-ֶ͕΂Δ
    

    View Slide

21. © )JLBSV5BLBIBTIJ
    w42-͕શ͘Θ͔Βͳ͍ਓ͕ಡΜͰ΋σʔλ෼ੳͰ࢖͏جຊత
    ͳ42-͕͜ͷ࡭Ͱֶ΂Δ
    wΤϯδχΞͰ͸ͳ͘σʔλ෼ੳΛߦ͏Ϗδωεύʔιϯ޲͚
    wϏδωεॻͱͯ͠ಡΈ΍͍͢ϖʔδʹऩ·Δ಺༰
    wϋϯζΦϯܗࣜͰ࣮ફΛҙࣝͨ͠42-ֶ͕΂Δ
    ຊॻͷಛ௃
    ຊॻͷಛ௃ɹᶄ42-Λશ͘஌Βͳ͍ਓͰ΋ϋϯζΦϯΛத৺ͱ࣮ͨ͠ફͰ࢖͑Δ42-ֶ͕΂Δ
    

    View Slide

22. © )JLBSV5BLBIBTIJ
    ຊॻͷಛ௃ɹଞͷຊͱͷൺֱ
    ຊॻͷಛ௃ɹᶄ42-Λશ͘஌Βͳ͍ਓͰ΋ϋϯζΦϯΛத৺ͱ࣮ͨ͠ફͰ࢖͑Δ42-ֶ͕΂Δ
    ϋϯζΦϯܗࣜͰޮ཰Α͘σʔλ෼ੳͰ࢖͏42-Λֶ΂Δͷ͕ຊॻͷಛ௃
    ຊॻ Ұൠతͳ42-ೖ໳ॻ σʔλ෼ੳ༻ͷ42-ຊ
    ର৅ऀ Ϗδωεύʔιϯ ΤϯδχΞ Ϗδωεύʔιϯ
    ຊͷ಺༰ σʔλ෼ੳ ΞϓϦέʔγϣϯ։ൃ σʔλ෼ੳ
    ຊͷಛ௃
    ϋϯζΦϯܗࣜͰ࣮຿ͷσʔλ෼
    ੳͰΑ͘࢖͏ߏจʹಛԽ
    ໢ཏతʹ42-ͷߏจΛઆ໌
    ϋϯζΦϯܗࣜʹͳ͍ͬͯͳ͍
    ໢ཏతʹ42-ͷߏจΛઆ໌
    ϖʔδ਺ ʙ ʙ
    

    View Slide

23. © )JLBSV5BLBIBTIJ
    ຊॻͷಛ௃ɹϋϯζΦϯͰಡΈਐΊΔ
    ຊॻͷಛ௃ɹᶄ42-Λશ͘஌Βͳ͍ਓͰ΋ϋϯζΦϯΛத৺ͱ࣮ͨ͠ફͰ࢖͑Δ42-ֶ͕΂Δ
    w ࣗ෼ͷ1$Ͱ࣮ࡍʹ42-Λ࣮ߦͯ݁͠
    ՌΛ֬ೝ͠ͳ͕ΒಡΈਐΊΔ
    w ͲΜͳ42-Λ࣮ߦ͢ΔͱͲΜͳ݁Ռ͕
    ฦͬͯ͘Δͷ͔Λ֬ೝ͠ͳ͕ΒಡΈਐ
    ΊΔ͜ͱͰσʔλ෼ੳΛ࣮຿Ͱ࢖͍΍
    ͍͢εΩϧͱͯ͠਎ʹ͚ͭΔ͜ͱ͕Ͱ
    ͖Δ
    

    View Slide

24. © )JLBSV5BLBIBTIJ
    ຊॻͷߏ੒
    ຊॻͷಛ௃ɹᶅ࣮຿ͷσʔλ෼ੳʹඞཁͳ಺༰ʹಛԽ͢Δ͜ͱͰޮ཰Αֶ͘΂Δ
    ͳͥ42-͕ඞཁ͔ʁ
    42-ͱ͸Կ͔ʁ
    42-࣮ߦ؀ڥͷߏங
    ϋϯζΦϯͷ४උ
    42-ͷߏจ
    ཧղ
    w σʔλ෼ੳ͕ॏཁͳཧ༝
    w σʔλ෼ੳʹ͓͚Δ42-ͱ͸
    w 42-ͷجຊతͳ֓೦
    ୈষ ୈষ ୈʙষ
    w 42-JUFΛ࢖ͬͨσʔλ෼ੳ؀ڥ
    w 42-࣮ߦͷͨΊͷ؀ڥߏங
    w 42-࣮ߦπʔϧͷ࢖͍ํ
    4&-&$5-*.*503%&3#:ू
    ໿ؔ਺(3061#:%*45*/$5
    8)&3&)"7*/(+0*/6/*0/
    $"4&αϒΫΤϦ8*5)
    

    View Slide

25. © )JLBSV5BLBIBTIJ
    wΤϯδχΞͱͯ͠Ͱ͸ͳ͘Ϗδωεύʔιϯͱ͠
    ͯσʔλ෼ੳʹඞཁͳ42-ͷ஌ࣝ
    w42-ͷಡΈղ͖͕Ͱ͖ΔೳྗΛ਎ʹ͚ͭΔ
    wΤϯδχΞͱͯ͠ඞཁͳ42-ͷ஌ࣝ
    ʢ*/4&35%&-&5&61%"5&$3&"5&ʣ
    wσʔλ෼ੳͰར༻ස౓ͷগͳ͍׬શ֎෦݁߹ͱΫ
    ϩε݁߹
    wॳֶऀʹ͸೉қ౓ͷߴ͍8*/%08ؔ਺
    ຊॻͰ৮ΕΔ಺༰ɾ৮Εͳ͍಺༰
    ຊॻͷಛ௃ɹᶅ࣮຿ͷσʔλ෼ੳʹඞཁͳ಺༰ʹಛԽ͢Δ͜ͱͰޮ཰Αֶ͘΂Δ
    ৮ΕΔ಺༰ ৮Εͳ͍಺༰
    

    View Slide

26. ຊॻͷັྗ
    4VNNBSZ
    42-Λֶͼ͍ͨͱࢥͬͨਓ͕࠷ॳʹखʹऔΔ࡭
    σʔλ෼ੳͰ࢖͏
    

    View Slide

27. &/%
    

    View Slide