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

Data Engineering Study #20 "Introduction to Data Analytics with SQL" Book

Data Engineering Study #20 "Introduction to Data Analytics with SQL" Book

Minero Aoki

July 03, 2023
Tweet

More Decks by Minero Aoki

Other Decks in Technology

Transcript

  1. ࣗݾ঺հ ‣ ੨໦ๆ࿠ʢ͓͖͋ΈͶΖ͏ʣ ‣ ݩ3VCZίϛολʔɺஶॻଟ਺ˠ ‣ લʑ৬ฒྻ3%#.4ϕϯμʔ ‣ ฒྻσʔλϕʔεͱग़ձ͏ ‣

    લ৬ΫοΫύου ‣ ෼ੳαʔϏεʮͨ΂ΈΔʯͷ։ൃ ‣ %8)ͷߏங ‣ ݱ৬5SFBTVSF%BUBʢڈ೥͔Βʣ ‣ "QQMJDBUJPOTνʔϜʹͯ$%1ͷ։ൃ
  2. ʰ೥ઓ͑Δσʔλ෼ੳೖ໳ʱ ιϑτόϯΫΫϦΤΠςΟϒɺ w લ৬ΫοΫύουͷಉ྅ʹ42-ΛֶΜ Ͱ΋Β͏ͨΊͷςΩετͱͯ͠ॻ͍ͨ w ΤϯδχΞͩͱ0-51ͷ42-͔͠஌Βͳ ͍ਓ͕େ൒Ͱɺલఏ͕߹Θͣۤ࿑ͨ͠ ͷͰɺͦͷ͋ͨΓʹॏ఺Λஔ͍ͨ w

    ͍ͭͰʹ෼ੳσʔλϕʔεʹ͍ͭͯ ೔ʑࢥ͍ͬͯͨ͜ͱ΋ॻ͍ͨ w ΤϯλʔϓϥΠζ޲͚اۀʹ͍ͨ਎͔ Β͢Δͱ%8)͸͋ͨΓ·͑ͷ஌ࣝͩ ͕ɺ౰࣌͸·ͩ΢Σϒք۾ʹ͸޿·ͬ ͓ͯΒͣɺ஌ࣝͷஅઈ͕͋ͬͨ
  3. ʙ೥͘Β͍ͷঢ়گ ͍ΘΏΔ΢Σϒܥ ৽͍͠اۀ ͍ΘΏΔ ΤϯλʔϓϥΠζاۀ ฏۉతͳ΢ΣϒΤϯδχΞ ϓϥϯφʔɺσΟϨΫλʔɺʜ %8)ʁͳʹͦΕʁ %8)ͬͯΦϯϓϨͰ͠ΐ ͡Ό͋"DUJWF3FDPSEͰʜ

    )BEPPQ࢖͑͹͍͍ΑͶ )BEPPQΑ͘Θ͔ΒΜ ϏοάσʔλΑ͘Θ͔ΒΜ Α͘Θ͔ΒΜ͚Ͳ Ϗοάσʔλ͍ͬͯ͏ౕͰ %8)͸Φϫίϯͳͷʁ ͳʹ΋Θ͔Βͳ͍ ဃ཭
  4. ୈ෦42-ʹΑΔσʔλ෼ੳ w ୈষ೥ઓ͑Δσʔλ෼ੳͷٕज़ w ୈষ͞ΘͬͯΈΑ͏3%#.4 w ୈষ؆୯ʂTFMFDUจͰσʔλ୳ࡧ w ୈষ͢΂ͯͷ෼ੳ͸ूܭ͔Β࢝·Δ w

    ୈষؔ਺Ͱࣗ༝ࣗࡏʹ৽͍͠ΧϥϜΛ࡞Γग़͢ w ୈষδϣΠϯΛ੍͢Δ΋ͷ͸3%#.4Λ੍͢ʕʕجૅฤ w ୈষδϣΠϯΛ੍͢Δ΋ͷ͸3%#.4Λ੍͢ʕʕԠ༻ฤ w ୈষ஗Εͯདྷͨ෼ੳ42-࠷ڧͷ෢ثʕʕ΢Οϯυ΢ؔ਺ w ୈষॎͱԣ͸೉͍͠ w ୈষΞΫηεϩάͷηογϣϯ෼ੳΛ͢Δ ୈ෦෼ੳγεςϜͷߏங w ୈষ೥ઓ͑Δσʔλ෼ੳγεςϜ w ୈষϏοάσʔλʹཱͪ޲͔͏ w ୈষ42-όονͷٕ๏ w ୈষຊॻΛಡΈऴ͑ͨޙʹ ʰ೥ઓ͑Δσʔλ෼ੳೖ໳ʱ֓ཁ લઆ 42-ͷ࿩ 42-த৺ ΞʔΩςΫνϟ
  5. w 42- w ࠷ॳͷ42-ඪ४ w 42-ʢ42-ʣ w ͍͍ͩͨͲͷํݴͰ΋࣮૷͞Ε͍ͯΔൣғ w 42-ʢ42-ʣ

    w ਖ਼نදݱϚονɺ࠶ؼΫΤϦʔɺ഑ྻɺߏ଄ମɺετΞυϓϩ γʔδϟ w 42- w ΢Οϯυ΢ؔ਺ɺγʔέϯεɺ*EFOUJUZΧϥϜ ීٴͨ͠42-ඪ४
  6. ৽͠Ίͷ42-ඪ४ ඪ४ ओͳ௥Ճػೳ 42- 42-9.- 42- 5SVODBUFTUBUFNFOU 42- 5FNQPSBM 42-

    3PXQBUUFSONBUDIJOH +40/PQFSBUJPOT  1PMZNPSQIJDUBCMFGVODUJPO MJTUBHH 42- ଟ࣍ݩ഑ྻ 42- +40/UZQF 6OEFSTDPSFTJOOVNFSJDMJUFSBMT 
 1SPQFSUZHSBQIRVFSJFT
  7. w ߦͷฒͼʹରͯ͠ਖ਼نදݱϚ ονΛ͢Δ͜ͱ͕Ͱ͖Δ w Ϛονͨ͠෦෼ΛऔΓग़ͨ͠ Γूܭͨ͠ΓͰ͖Δ w 1SFTUP 4OPX fl

    BLF 0SBDMF ͳͲ͕࣮૷͍ͯ͠Δ ߦͷฒͼʹର͢ΔύλʔϯϚον NBUDI@SFDPHOJ[F TFMFDU GSPNXFC@BDDFTT NBUDI@SFDPHOJ[F  QBSUJUJPOCZVTFS@JE PSEFSCZFWFOU@UJNF NFBTVSFT TVN BNPVOU BTUPUBM@BNPVOU QBUUFSO 7 $#  EF fi OF 7BTFWFOUWJFX $BTFWFOUDBSU #BTFWFOUCVZ 
  8. w ͓ͦΒ͘NBUDI@SFDPHOJ[Fͷ ݩωλ w Ϋϥελʔ಺ʹ)BEPPQ͕ಈ͍ ͍ͯͯ.BQ3FEVDFͰॲཧ w "TUFS%BUB͸ޙʹ5FSBEBUBʹ ങऩ͞Εͨ "TUFS%BUBͷO1BUI

    TFMFDU GSPNOQBUI  POXFC@BDDFTT 
 QBSUJUJPOCZVTFS@JE 
 PSEFSCZFWFOU@UJNF VTJOHNPEF OPOPWFSMBQQJOH  QBUUFSO 7 $#  TZNCPMT  FWFOUWJFXBT7  FWFOUDBSUBT$  FWFOUCVZBT#   SFTVMU  TVN BNPVOU PG#   BTU
  9. w +40/PQFSBUJPOT 42-  w +40/Ͱ͋ΔจࣈྻΛύʔεͯ͠ॲཧ͢Δؔ਺܈ɻ w +40/1BUIͰ஋ΛऔΓग़ͨ͠ΓͰ͖Δɻ w +40/5ZQF

    42-  w ʢύʔεࡁΈͷʣ+40/஋Λσʔλϕʔεʹอଘͯ͠ɺؔ਺Ͱ͸ͳ͘ߏจͰΞΫ ηεͰ͖Δɻ w จࣈྻॲཧͰ͸ͳ͘ɺ42-ͷߏจͰ+40/Λߏஙͯ͠ฦ͢͜ͱ΋Ͱ͖Δɻ +40/UZQFͱ+40/PQFSBUJPO
  10. w 1SPQFSUZ(SBQI2VFSJFT 42-  w ςʔϒϧʹอଘ͞Εͨάϥϑʢσʔλߏ଄ʣʹର͢ΔΫΤϦʔ͕ॻ͚Δɻ 1SPQFSUZ(SBQI2VFSJFT $3&"5&1301&35:(3"1) fi OBODJBM@USBOTBDUJPOT

    7&35&95"#-&4  1FSTPOT-"#&-1FSTPO1301&35*&4 OBNF  $PNQBOJFT-"#&-$PNQBOZ1301&35*&4 OBNF  "DDPVOUT-"#&-"DDPVOU1301&35*&4 OVNCFS    &%(&5"#-&4  5SBOTBDUJPOT 4063$&,&: GSPN@BDDPVOU 3&'&3&/$&4"DDPVOUT OVNCFS  %&45*/"5*0/,&: UP@BDDPVOU 3&'&3&/$&4"DDPVOUT OVNCFS  -"#&-USBOTBDUJPO1301&35*&4 BNPVOU  "DDPVOUT"41FSTPO0XOFS 4063$&,&: OVNCFS 3&'&3&/$&4"DDPVOUT OVNCFS  %&45*/"5*0/1FSTPOT -"#&-PXOFS/01301&35*&4  "DDPVOUT"4$PNQBOZ0XOFS 4063$&,&: OVNCFS 3&'&3&/$&4"DDPVOUT OVNCFS  %&45*/"5*0/$PNQBOJFT -"#&-PXOFS/01301&35*&4  1FSTPOT"4XPSLT'PS 4063$&,&: JE 3&'&3&/$&41FSTPOT JE  %&45*/"5*0/$PNQBOJFT /01301&35*&4  4&-&$5 PXOFSOBNF"4BDDPVOU@IPMEFS  46. UBNPVOU "4UPUBM@USBOTBDUFE@XJUI@/JLJUB '30. ."5$) Q1FSTPO <PXOFS> BDDPVOU"DDPVOU   ."5$) BDDPVOU <UUSBOTBDUJPO> BDDPVOU   ."5$) BDDPVOU"DDPVOU <PXOFS> PXOFS1FSTPOc$PNQBOZ  8)&3& QOBNF/JLJUB (3061#: PXOFS 'SPNIUUQTQHRMMBOHPSH
  11. w RVBMJGZ۟ w ΢Οϯυ΢ؔ਺͕࣮ߦ͞Εͨ͋ͱʹධՁ͞ΕΔ8)&3&۟ɻ w ΢Οϯυ΢ؔ਺Λ࢖͏ͱߴ֬཰Ͱൃੜ͢ΔαϒΫΤϦʔΛ๾໓Ͱ͖Δɻ w ݩ͸5FSBEBUBͷಠ֦ࣗு͕ͩɺ͍·͸#JH2VFSZɺ4OPX fl BLF΋αϙʔτͯ͠

    ͍Δɻ w ϥςϥϧࢀর w BTͰ෇͚ͨผ໊ΛɺXIFSF۟΍HSPVQCZ͔۟ΒࢀরͰ͖Δɻ w ྫʹΑͬͯ5FSBEBUB͕࣮૷͍͕ͯͨ͠3FETIJGUɺ)JWFʹ΋͍ͭͷ·ʹ͔ೖͬ ͨɻ w ϥϜμࣜ w ֤छϓϩάϥϛϯάݴޠʹ͋ΔΞϨɻແ໊ͷؔ਺Λॻ͘͜ͱ͕Ͱ͖Δɻ w 5SJOP͕࣮૷͍ͯ͠Δɻ 42-ඪ४Ͱ͸ͳ͍͕ศརͳߏจ
  12. w ओཁ3%#.4͕͢΂ͯผͷݴޠΛαϙʔτͨ͠Βࢮ͵ʁ w 0SBDMF 42-4FSWFS .Z42- 1PTUHSF42- #JH2VFSZ 4OPX fl

    BLF  5FSBEBUB %#͕͢΂ͯ42-Ͱ͸ͳ͍ಉ͡ݴޠΛαϙʔτ͢Δ͜ͱ͸͋Γ͏Δ ͔ʁ w 3%#.4͕ࢮΜͩΒ42-΋ࢮ͵ʁ w ೥୅ͷ,74ϒʔϜ͸3%#.4Λࡴ͢࠷େͷνϟϯεͩͬͨ͸͕ͣͩɺ݁ Ռͱͯ͠ࡴ͔ͤͨʁ w ਓؒͱओཁ#*πʔϧ͕ἧͬͯҧ͏ݴޠΛ࢖͍࢝ΊͨΒࢮ͵ʁ w ਓؒ͸$IBU(15͕ॻ͍ͯ͘ΕΔ͔΋͠Εͳ͍ w #*πʔϧͷݴޠΛม͑Δಈػ͕ͦ͜·Ͱ͋Δ͔ʁ Ͳ͏ͳͬͨΒ42-͸ࢮ͵͔
  13. աڈ೥ͷ෼ੳ%#ͷྲྀΕ %8)ઐ༻ػ )BEPPQͱ/P42- ৽͍͠ฒྻ3%#.4 ʙ 5FSBEBUB૑ۀ    (SFFOQMVN૑ۀ

    ('4࿦จ    )BEPPQ  5FSBEBUB  0SBDMF&YBEBUB $BTTBOESBϦϦʔε   )JWFϦϦʔε  #JH2VFSZ("  "NB[PO3FETIJGU )BEPPQ  1SFTUP044Խ  5%1MB[NB4OPX fl BLF    )BEPPQ    3FETIJGU3"
  14. w %8)ઐ༻ػͷ࣌୅ w ΦϯϓϨTIBSFEOPUIJOH w 42-ϕʔεͰτϥϯβΫγϣϯΛαϙʔτ͢Δ w )BEPPQͱ/P42-ͷ࣌୅ w ΦϯϓϨ·ͨ͸Ϋϥ΢υͷTIBSFEOPUIJOHͰϩʔΧϧ)%%ʹ

    σʔλΛอଘ w 42-EPFTOUTDBMF w OPX৽͍͠ฒྻ3%#.4ͷ࣌୅ w Ϋϥ΢υલఏͰDPNQVUFͱTUPSBHFΛ෼཭ w ࠶ͼ42-ϕʔεͰτϥϯβΫγϣϯΛαϙʔτ͢Δ աڈ೥ͷ෼ੳ%#ͷྲྀΕ
  15. 3FETIJGUͷྫ %4OPEFUZQF &UIFSOFU MFBEFS OPEF DPNQVUF DPNQVUF DPNQVUF DPNQVUF MPDBM

    EJTL MPDBM EJTL MPDBM EJTL MPDBM EJTL w γϯϓϧͳTIBSFEOPUIJOHΞʔΩςΫνϟ w σʔλ͸͢΂ͯFQIFNFSBMEJTLʹ഑ஔ w ϊʔυΛ௥Ճ͢Δͱશσʔλͷ࠶෼ࢄ͕ඞཁ Ͱ೔୯Ґͷ͕͔͔࣌ؒΔ
  16. 3FETIJGUͷྫ 3"OPEFUZQF &UIFSOFU MFBEFS OPEF DPNQVUF DPNQVUF DPNQVUF DPNQVUF 4

    w σʔλ͸͢΂ͯ4ʹ഑ஔ w σʔλͱ͸·ͬͨؔ͘܎ͳ͘DPNQVUFϊʔυ Λ૿ݮͰ͖Δ w ݁Ռͱͯ͠EZOBNJDͳ૿ݮ΍ɺΫϥελʔؒ Ͱͷσʔλڞ༗ͳͲ͕ՄೳʹͳΔ
  17. 5SFBTVSF%BUB1MB[NB 4 1MB[NB NFUBEBUB 1SFTUP $PPSEJOBUPS 1SFTUP 8PSLFS 1SFTUP 8PSLFS

    1SFTUP 8PSLFS 1SFTUP 8PSLFS &UIFSOFU w DPNQVUFTUPSBHF෼཭ΞʔΩςΫνϟ w σʔλ͸͢΂ͯ4ʹ഑ஔ w ϝλσʔλ͸1PTUHSF42-ʹஔ͍͍ͯΔͷͰ τϥϯβΫγϣϯ͕࢖͑Δ
  18. 5FSBEBUB7 #:/&5JOUFSDPOOFDU /PEF /PEF /PEF /PEF /PEF 4UPSBHF"SSBZ 1& ".1

    1& ".1 1& ".1 1& ".1 1& ".1 'JCSF$IBOOFM w ϘτϧωοΫʹͳΓ΍͍͢ϊʔυؒ௨৴ʹಠࣗϋʔ υ΢ΣΞ#:/&5Λ࢖͏͜ͱͰϦχΞʹεέʔϧ w MFBEFS૬౰͕֤ϊʔυʹ͍ΔϚϧνϚελʔ w ͓ߴ͍ετϨʔδΞϨΠΛෳ਺ϊʔυؒͰڞ༗͢Δ ͜ͱͰϨϓϦ΍OPEFGBJM࣌ͷ࠶෼ࢄΛճආ
  19. w ͜Ε·Ͱ͸ࢀরॏࢹͩͬͨ෼ੳγεςϜʹߋ৽ܥͷ೾͕དྷ͍ͯΔ w τϥϯβΫγϣϯͷαϙʔτ͸ඞવ w ετϨʔδ͕ެ։͞Ε͍ͯΔ͔Ͳ͏͔Ͱ೉қ౓͕มΘΔ w "QBDIF*DFCFSHͳͲ͸جຊతʹ͸ެ։ετϨʔδΛ૝ఆͨ͠ϑΥʔϚοτ ʢ༷ʑͳγεςϜ͕ΞΫηεͰ͖Δʣ w

    3FETIJGU΍1MB[NBͷετϨʔδ͸4Ͱ͋ͬͯ΋ઐ༻όέοτΛ࢖͏ͷͰτϥ ϯβΫγϣϯͷ࣮૷͕γεςϜʹด͡Δ w ύϑΥʔϚϯε͔Βߟ͑Δͱઐ༻ετϨʔδΛ࣋ͭ΄͏͕༗ར w σʔλϨΠΫͰ͢΂ͯΛ·͔ͳ͏ํ޲ʹ͸ߦ͔ͳ͍ͱ༧૝ w ઐ༻ετϨʔδ 0CKFDU4UPSBHF"DDFTTʢޙड़ʣʹͳΔͷͰ͸ʁ τϥϯβΫγϣϯͷαϙʔτ
  20. w 'FEFSBUFE2VFSZ͸αΠζͰ࢖͍෼͚Δͷ͕ݱ࣮త w খ͍͞ςʔϒϧ͸&-5ͰऔΓࠐΉΑΓ'FEFSBUFE2VFSZ w ڊେͳςʔϒϧ͸Ҿ͖ଓ͖&-5 w 0CKFDU4UPSBHF"DDFTT͸τϥϯβΫγϣϯ༗ແʹΑΔ w ΞΫηεϩάͳͲͷڊେ͕ͩ௥ՃΦϯϦʔͷςʔϒϧ͸0CKFDU4UPSBHF

    "DDFTT w Ϛελʔ΍αϚϦʔςʔϒϧ͸಺෦ςʔϒϧ w %BUB4IBSJOH͸Ϋϥελʔ෼ׂΛ໨తʹ͢ΔͱΑ͍ w ྫ͑͹ࢠձࣾͷσʔλΛผΫϥελʔʹ෼͚ͭͭάϧʔϓͰ౷߹͢Δέʔεͳ Ͳ σʔλ࿈ܞػೳͷ࢖͍Ͳ͜Ζ
  21. w 3FETIJGUͷ"VSPSB;FSP&5- w "VSPSBͷςʔϒϧ͕ඵ͍ͯͲͰ3FETIJGUʹϨϓϦ͞ΕɺΞΫηεՄೳʹͳ Δ w 4OPX fl BLFͷ6OJTUPSF w

    0-51ΞΫηεʹ޲͍ͨ)ZCSJE5BCMFΛ࡞Δ͜ͱ͕Ͱ͖Δ w ෼ੳΫΤϦʔ͔Β΋ී௨ͷςʔϒϧͷΑ͏ʹΞΫηεͰ͖Δ 0-51ͱͷ౷߹ ʢ௥Ճʣ;FSP&5-ͱ6OJTUPSF
  22.  &5-ΑΓ&-5 w ୈ૚ΛʮιʔεσʔλΛͦͷ··ϩʔυ͢ΔεΩʔϚʯͱఆٛ͢Δ͜ͱͰɺ ඞવతʹ&-5ʹͳΔ  ෺ཧσʔλϚʔτΑΓԾ૝σʔλϚʔτ w ୈ૚Λ഑ஔͨ͜͠ͱͰɺඞવతʹԾ૝σʔλϚʔτʹͳΔ w

    ˡˠ0-51%#ͰσʔλϚʔτΛ࡞Δ  σʔλͷྲྀΕ͸Ұํ޲ w ୈ૚͔Β૚ɺ૚΁ͷํ޲Ͱσʔλ͸ྲྀΕΔ΂͖  ૊৫ͷ੾Ε໨ʹରԠ͍ͯ͠Δʢ͜ͱ΋͋Δʣ w σʔλऔΓࠐΈͱ%8)ߏங͸σʔλϚʔτߏஙͱ͸ผͷνʔϜʹͳΔ͜ͱ͕ ଟ͍ͷͰɺͦ͜Ͱ੾͓ͬͯ͘ ૚ߏ੒ͷഎܠ
  23. w ෼ੳσʔλϕʔεʹ͸σʔλͷྲྀΕ͕͋Δ w 0-51σʔλϕʔεʹ͸͜Μͳʹ͸͖ͬΓͨ͠ྲྀΕ͸ͳ͍ w ෼ੳσʔλετϦʔϜʹ͸ग़ޱ͕͋Δ w ͭ·Γ׆༻ઌ͕͋Δ w ׆༻͕σʔλసૹͷ৔߹͸BDUJWBUJPOɺSFWFSTF&5-ͳͲͱݺ͹ΕΔ

    w ͢΂ͯͷσʔλॲཧ͸BDUJWBUJPOͷͨΊʹ͋Δ w ͳͥͳΒՁ஋ΛੜΉͷ͸BDUJWBUJPO͔ͩΒ w ෼ੳγεςϜશମΛޙΖ͔Β࡞Δ΂͖ w ׆༻ࢪࡦˠBDUJWBUJPOˠʢσʔλϚʔτʣˠιʔεσʔλ ΑΓڧௐ͢΂͖ͩͬͨ͜ͱ