データ分析のためのSQL勉強会 〜初級編 ハンズオン〜

Transcript

1. 
   σʔλ෼ੳͷͨΊͷ42-ษڧձ
   ʙॳڃฤϋϯζΦϯʙ
   

   View Slide

2. 
   w 42-ษڧձͷᶃର৅ऀᶄΰʔϧᶅਐΊํ
   w ͳͥ42-ΛֶͿͷ͔ʁ
   w 42-ͱ͸ʁ
   w 42-࣮ߦ؀ڥߏங
   w 42-ߨ࠲
   "HFOEB
   

   View Slide

3. 
   w 42-ษڧձͷᶃର৅ऀᶄΰʔϧᶅਐΊํ
   w ͳͥ42-ΛֶͿͷ͔ʁ
   w 42-ͱ͸ʁ
   w 42-࣮ߦ؀ڥߏங
   w 42-ߨ࠲
   "HFOEB
   

   View Slide

4. 
   ᶃ42-ษڧձͷର৅ऀ
   w42-Λ͜Ε͔Βֶͼ͍ͨਓ
   w࢓ࣄͰ42-Λ৮Γ࢝Ί͚ͨͲΠϚΠνΑ͘Θ͔Βͳ͍ਓ
   wσʔλूܭ΍σʔλ෼ੳΛΤΫηϧ࢖ͬͯ΍ͬͯΔਓ
   ˞ࠓճ͸ʮσʔλ෼ੳͷͨΊͷʯ42-ษڧձͰ͢
   ˞ΞϓϦέʔγϣϯ։ൃͳͲͰ࢖͏42-͸૝ఆ͍ͯ͠ͳ͍ͷͰσʔλऔ
   ಘ 4&-&$5จ
   ͕த৺ʹͳ͍ͬͯ·͢
   

   View Slide

5. 
   ᶄ42-ษڧձͷΰʔϧ
   w42-Λ࣮຿Ͱ࢖͑Δ༷ʹͳΔ
   w42-Λ࢖ͬͯཉ͍͠σʔλΛࣗ༝ʹऔΕΔ༷ʹͳΔࣄ
   w ௐ΂͔͚ͯΔ༷ʹ͢Δ
   w ଞͷਓ͕ॻ͍ͨ42- ࣗ෼͕աڈʹॻ͍ͨ42-
   Λࢀߟʹ42-ͷಡΈղ
   ͖͕Ͱ͖Δ༷ʹ͢Δ
   ˞θϩϕʔεͰ42-Λ͔͚Δ༷ʹͳΔඞཁ͸ͳ͍
   

   View Slide

6. 
   ᶅ42-ษڧձͷਐΊํ
   w ϋϯζΦϯܗࣜͰ࣮ࢪ
   w ͨͩ࿩Λฉ͚ͩ͘Ͱ͸ͳ͘ඞͣࣗ෼Ͱ42-Λॻ͘ࣄ
   w ͦͷͨΊʹࣗ෼ͷ1$Ͱ42-Λ࣮ߦͰ͖Δ؀ڥΛ༻ҙ͢Δ
   w ࢿྉͷதͰʮ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ʯͱॻ͔Ε͍ͯΔ෦෼͸ࣗ෼
   Ͱ42-Λ࣮ߦͯ݁͠Ռ͕Ͳ͏ͳΔ͔֬ೝ͠·͠ΐ͏
   w ·ͣ͸جຊతͳ42-ͷઆ໌͠ɺͦͷޙࣗ෼Ͱ42-Λॻ͍͍ͯ͘ελΠϧ
   w ໰୊ͷղ౴ྫ΋Ұॹʹࡌ͍ͤͯ·͕͢ɺղ౴ΛΈΔલʹ·ͣ͸ࣗ෼Ͱߟ͑
   ͯΈ·͠ΐ͏
   

   View Slide

7. 
   w 42-ษڧձͷᶃର৅ऀᶄΰʔϧᶅਐΊํ
   w ͳͥ42-ΛֶͿͷ͔ʁ
   w 42-ͱ͸ʁ
   w 42-࣮ߦ؀ڥߏங
   w 42-ߨ࠲
   "HFOEB
   

   View Slide

8. ͳͥ42-ΛֶͿͷ͔ʁ
   
   w σʔλ׆༻͕Ϗδωε੒௕ʹ͓͍ͯඞਢͰ͋Δ
   w 42-͕࢖͑ΔσʔλΛࣗ༝ʹѻ͑Δ
   w σʔλ෼ੳʹ͓͍ͯ42-ͷ׆༻ͷػձ͕૿͍͑ͯΔ
   w #JH2VFSZ 5SFBTVSF%BUB SFEBTI 3FETIJGUͳͲ
   w ΤΫηϧͰѻ͑Δσʔλʹݶք͕͋Δ
   w ΤΫηϧͷݶք͸ສߦ͘Β͍ ΤΫηϧͰ෼ੳ͢Δͱॏ͍
   
   42-͸σʔλ෼ੳͷ൚༻తͳεΩϧͰ͋Γɺ
   42-͕࢖͑ΔͱେྔͷσʔλΛߴ଎ʹ෼ੳͰ͖Δ
   

   View Slide

9. 
   w 42-ษڧձͷᶃର৅ऀᶄΰʔϧᶅਐΊํ
   w ͳͥ42-ΛֶͿͷ͔ʁ
   w 42-ͱ͸ʁ
   w 42-࣮ߦ؀ڥߏங
   w 42-ߨ࠲
   "HFOEB
   

   View Slide

10. 
    w42-ͱ͸4USVDUVSFE2VFSZ-BOHVBHFͷུ
    wσʔλϕʔε্ͷςʔϒϧʹ֨ೲ͞ΕͨσʔλΛऔಘ͢Δ࣌
    ʹ࢖͏ݴޠ
    42-ͱ͸ʁ
    

    View Slide

11. 
    wσʔλϕʔεͱ͸σʔλΛ֨ೲ͢Δେ͖ͳശ
    wςʔϒϧͱ͸σʔλϕʔε্ʹଘࡏ͢Δσʔλͷ͔ͨ·Γ
    wσʔλϕʔεʹ͋Δςʔϒϧ͔Β৘ใΛऔಘ͢Δํ๏͕42-
    Ϣʔβʔ
    αʔόʔ σʔλϕʔε
    %#
    
    42-
    VTFS@JE TFY CJSUI
    " உੑ
    " ঁੑ
    " உੑ
    ɾɾɾ ɾɾɾ ɾɾɾ
    ςʔϒϧ
    QSPEVDU@JE QSPEVDU@OBNF QSJDF
    # ͓͍͍͠ਫ
    # γϟʔϓϖϯ
    # ࿹࣌ܭ
    ɾɾɾ ɾɾɾ ɾɾɾ
    8αΠτ
    42-ͱ͸ʁ
    

    View Slide

12. 
    w σʔλϕʔεʹ͸.Z42-ɺ0SBDMFɺ1PTUHSF42-ɺ42-JUFɺ5FSBEBUBɺ
    )BEPPQ )%'4
    ͳͲ༷ʑͳछྨ͕͋Δ
    w ͦΕͧΕͷ%#ʹରͯ͠σʔλΛऔಘ͢ΔͨΊͷํ๏͕42-
    1SFTUP )JWFͳͲ΋͋Δ
    
    42-ͷछྨ
    

    View Slide

13. 
    w 42-ษڧձͷᶃର৅ऀᶄΰʔϧᶅਐΊํ
    w ͳͥ42-ΛֶͿͷ͔ʁ
    w 42-ͱ͸ʁ
    w 42-࣮ߦ؀ڥߏங
    w 42-ߨ࠲
    "HFOEB
    

    View Slide

14. 
    wࠓճͷߨٛͰ͸ʮ42-JUFʯΛ࢖͍·͢
    w42-JUFͷಛ௃
    wܰͯ͘଎͍
    wαʔόʔ͕ͳͯ͘΋%#ΛߏஙͰ͖Δ ΞϓϦέʔγϣϯͱ
    ͯ͠૊ΈࠐΊΔ
    
    wࣗ෼ͷ1$ʹ%#Λཱͯͯɺ42-Λ࣮ߦ͢Δ؀ڥ͕࡞ΕΔ
    w42-JUFͷ࣮ߦ؀ڥͱͯ͠ʮ%##SPXTFSGPS42-JUFʯΛ
    ࢖͍·͢
    42-࣮ߦ؀ڥߏங
    

    View Slide

15. 
    IUUQTTRMJUFCSPXTFSPSHEM
    IUUQTXXXLLBOFLPKQEBUBTRMJUFECCSPXTFSIUNM
    IUUQTRJJUBDPNIJFTJFBJUFNTGDGDEF
    ʮ%##SPXTFSGPS42-JUFʯͷΠϯετʔϧ
    ࢀߟ
    8JOEPXT
    .BD
    

    View Slide

16. 
    ࠓճͷߨٛͰ͸Սۭͷ&$αΠτͷߪങσʔλΛࢀߟʹσʔλநग़Λߦ͍·͢
    ΧϥϜ໊ ҙຯ ܕ උߟ
    VTFS@JE Ϣʔβʔ*% 5&95
    TFY ੑผ 5&95 ঁੑஉੑ
    CJSUI ஀ੜ೔ */5&(&3 ஀ੜ೥͕਺ࣈͰೖΓ·͢
    JT@EFMFUFE ࡟আϑϥά */5&(&3
    ɿ௨ৗϢʔβʔ
    ɿ࡟আϢʔβʔ
    ΧϥϜ໊ ҙຯ ܕ උߟ
    QSPEVDU@JE ঎඼*% 5&95
    OBNF ঎඼໊ 5&95
    QSJDF ֹۚ */5&(&3
    DBUFHPSZ ΧςΰϦ 5&95
    DBUFHPSZ ΧςΰϦ 5&95
    ΧϥϜ໊ ҙຯ ܕ උߟ
    PSEFS@JE ஫จ*% 5&95
    VTFS@JE Ϣʔβʔ*% 5&95
    PSEFS@QSPEVDU@JE ঎඼*% 5&95
    PSEFS@EBUF ஫จ೔࣌ */5&(&3
    JT@EJTDPVOUFE ׂҾϑϥά */5&(&3
    ɿׂҾͳ͠
    ɿׂҾ͋Γ
    JT@DBODFMFE Ωϟϯηϧϑϥά */5&(&3
    ɿΩϟϯηϧͳ͠
    ɿΩϟϯηϧ͋Γ
    VTFSTςʔϒϧ
    QSPEVDUTςʔϒϧ
    PSEFS@EBUBςʔϒϧ
    

    View Slide

17. 
    ʮ%##SPXTFSGPS42-JUFʯΛىಈͯ͠σʔλϕʔεΛ࡞੒ᶃ
    

    View Slide

18. 
    ʮ%##SPXTFSGPS42-JUFʯΛىಈͯ͠σʔλϕʔεΛ࡞੒ᶄ
    ˞σʔλϕʔε໊͸ԿͰ΋0,Ͱ͢
    

    View Slide

19. 
    ʮ%##SPXTFSGPS42-JUFʯΛىಈͯ͠σʔλϕʔεΛ࡞੒ᶅ
    ˞ςʔϒϧ࡞੒͸Ωϟϯηϧ
    

    View Slide

20. 
    DTWϑΝΠϧ͔ΒҎԼͭͷςʔϒϧ৘ใΛΞοϓϩʔυᶃ
    VTFSTςʔϒϧ QSPEVDUTςʔϒϧ PSEFS@EBUBςʔϒϧ
    

    View Slide

21. 
    VTFSTςʔϒϧ QSPEVDUTςʔϒϧ PSEFS@EBUBςʔϒϧ
    DTWϑΝΠϧ͔ΒҎԼͭͷςʔϒϧ৘ใΛΞοϓϩʔυᶄ
    w VTFSTDTW
    w QSPEVDUTDTW
    w PSEFS@EBUBDTW
    ͷͭͷDTWϑΝΠϧΛΠϯϙʔτͯ͠
    ςʔϒϧΛ࡞੒
    

    View Slide

22. 
    42-ߨ࠲
    4&-&$5
    ू߹ؔ਺
    (3061#:
    %*45*/$5
    8)&3&
    #&58&&/
    -*,&
    */
    -*.*5
    03%&3#:
    +0*/
    )"7*/(
    $"4&
    αϒΫΤϦ
    8*5)
    6/*0/ 6/*0/"--
    
    ͦͷଞ5JQT
    

    View Slide

23. 
    w ͲͷߦͷσʔλΛऔΔͷ͔
    w ͲͷྻͷσʔλΛऔΔͷ͔
    w Ͳ͏σʔλΛ·ͱΊΔͷ͔
    w Ͳ͏σʔλΛ૊Έ߹ΘͤΔͷ͔
    طଘͷςʔϒϧ
    42-ͷجຊతͳߟ͑ํ
    ৽͍͠ςʔϒϧ
    OBNF DPVOU QSJDF
    ͓͍͍͠ਫ
    γϟʔϓϖϯ
    ɾɾɾ ɾɾɾ
    PSEFS@JE VTFS@JE PSEFS@QSPEVDU@JE
    $ " #
    $ " #
    ɾɾɾ ɾɾɾ ɾɾɾ
    QSPEVDU@JE OBNF QSJDF DBUFHPSZ
    # ͓͍͍͠ਫ ৯඼
    # γϟʔϓϖϯ จ๪۩
    ɾɾɾ ɾɾɾ ɾɾɾ ɾɾɾ
    ߦ
    ྻ
    ͔Β Λ࡞੒͢Δ࡞ۀ
    Λߟ͑ͯ
    

    View Slide

24. 
    42-ߨ࠲
    4&-&$5
    ू߹ؔ਺
    (3061#:
    %*45*/$5
    8)&3&
    #&58&&/
    -*,&
    */
    -*.*5
    03%&3#:
    +0*/
    )"7*/(
    $"4&
    αϒΫΤϦ
    8*5)
    6/*0/ 6/*0/"--
    
    ͦͷଞ5JQT
    

    View Slide

25. 
    جຊతͳ42-ߏจ
    SELECT
    ΧϥϜ໊
    FROM ςʔϒϧ໊
    WHERE ৚݅
    GROUP BY άϧʔϓ৚݅ΧϥϜ
    ORDER BY ฒͼॱࢦఆΧϥϜ
    جຊߏจ
    SELECT
    category1,
    COUNT(*)
    FROM products
    WHERE category1 = '৯඼'
    GROUP BY category1
    ORDER BY category1
    ۩ମྫ
    

    View Slide

26. 
    جຊతͳ42-ߏจ
    جຊߏจ
    SELECT
    category1,
    COUNT(*)
    FROM products
    WHERE category1 = '৯඼'
    GROUP BY category1
    ORDER BY category1
    ˞ࡉ͔͍෦෼͸ޙ΄Ͳৄࡉʹઆ໌͢ΔͷͰ·ͣ͸4&-&$5
    ෦෼ͷΈ஫໨
    4&-&$5
    '30.
    (3061#:
    03%&3#:
    8)&3&
    ग़ྗ͢ΔσʔλΛࢦఆ
    Ͳͷςʔϒϧ͔ΒσʔλΛऔ
    ಘ͢Δ͔
    σʔλΛநग़͢Δ৚݅
    ूܭͷࡍͷάϧʔϓ৚݅
    ग़ྗ݁Ռͷฒͼॱ
    

    View Slide

27. 
    4&-&$5จɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃ
    SELECT
    product_id,
    name
    FROM products
    

    View Slide

28. 
    4&-&$5จɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃɹղઆ
    SELECT
    product_id,
    name
    FROM products
    ςʔϒϧʹͲ͏Ώ͏σʔλ͕ೖ͍ͬͯΔ͔֬ೝͯ͠ɺཉ͍͠σʔλ
    ΧϥϜ
    Λࢦఆͯ͠42-Λ࣮ߦ
    ΧϥϜ໊ ҙຯ ܕ උߟ
    QSPEVDU@JE ঎඼*% 5&95
    OBNF ঎඼໊ 5&95
    QSJDF ֹۚ */5&(&3
    DBUFHPSZ ΧςΰϦ 5&95
    DBUFHPSZ ΧςΰϦ 5&95
    DBUFHPSZ ΧςΰϦ 5&95
    QSPEVDUTςʔϒϧ
    

    View Slide

29. 
    42-JUFͷσʔλͷܕ
    42-JUFͰ͸ͭͷܕ͕ଘࡏ͠·͢
    ΧϥϜ໊ ҙຯ ܕ උߟ
    QSPEVDU@JE ঎඼*% 5&95
    OBNF ঎඼໊ 5&95
    QSJDF ֹۚ */5&(&3
    DBUFHPSZ ΧςΰϦ 5&95
    DBUFHPSZ ΧςΰϦ 5&95
    DBUFHPSZ ΧςΰϦ 5&95
    QSPEVDUTςʔϒϧ
    /6--
    */5&(&3
    5&95
    #-0#
    3&"-
    /6--஋
    ੔਺
    খ਺఺
    '-0"5 %06#-&
    
    จࣈྻ
    $)"3 7"3$)"3
    
    #JOBSZ-BSHF
    0CKFDU
    ˞ͦͷଞͷ%#Ͱ͸ʮ%"5&ʯ΍ʮ5*.&45".1ʯͳ
    Ͳ೔෇Λ֨ೲ͢Δܕ͕࢖ΘΕΔͷ͕ҰൠతͰ͕͢ɺ
    42-JUFʹ͸ଘࡏ͠·ͤΜ
    #-0#ͱ͸จࣈྻ΍਺ࣈͰ͸ͳ͍೚ҙͷσʔλ
    ΛอଘͰ͖Δσʔλܕ ը૾ɺಈըɺ࣮ߦϑΝ
    ΠϧͳͲ
    
    

    View Slide

30. 
    4&-&$5จɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄ
    SELECT
    *
    FROM products
    

    View Slide

31. 
    4&-&$5จɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄɹղઆ
    SELECT
    *
    FROM products
    w શͯͷσʔλΛநग़͢Δ৔߹͸ʮʯ ΞελϦεΫ
    Λ࢖͍·͢
    w ͲΜͳσʔλ͕ೖͬͯΔ͔֬ೝ͢Δ࣌ʹศར
    w ग़ྗ͢Δ߲໨͕ଟ͘ͳΔ෼42-͕ॏ͘ͳΓෛՙ͕͔͔Γ΍͍͢ͷͰ஫ҙ
    w ʮʯΛ࢖ͬͯσʔλΛ֬ೝ͢Δ৔߹͸-*.*5ͱҰॹʹ࢖͏ͷ͕͓קΊ ޙड़
    
    ΧϥϜ໊ ҙຯ ܕ උߟ
    QSPEVDU@JE ঎඼*% 5&95
    OBNF ঎඼໊ 5&95
    QSJDF ֹۚ */5&(&3
    DBUFHPSZ ΧςΰϦ 5&95
    DBUFHPSZ ΧςΰϦ 5&95
    DBUFHPSZ ΧςΰϦ 5&95
    

    View Slide

32. 
    4&-&$5จͷ"4ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶅ
    SELECT
    product_id AS '঎඼ID',
    name AS '঎඼໊'
    FROM products
    SELECT
    product_id AS id,
    name AS na
    FROM products
    

    View Slide

33. 
    SELECT
    product_id AS '঎඼ID',
    name AS '঎඼໊'
    FROM products
    w "4ͷޙʹผ໊͕෇͚ΒΕΔ
    w ೔ຊޠͰ΋ྑ͍͠ΞϧϑΝϕοτͰ΋Α͍
    w σʔλͷղऍ͕͠΍͍͢Α͏ʹ42-࣮ߦऀ͕޷͖ʹ໊લΛ͚ͭΒΕΔ
    ʮ"4ʯΛ࢖͏ࣄͰ4&-&$5݁Ռʹผ໊Λ͚ͭΔࣄ͕Ͱ͖Δ
    SELECT
    product_id AS id,
    name AS na
    FROM products
    4&-&$5จͷ"4ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶅɹղઆ
    

    View Slide

34. 
    4&-&$5จɹ໰୊ᶃ
    ঎඼ςʔϒϧ QSPEVDUTςʔϒϧ
    ͔Βnameͱcategory1ͷσʔλΛऔಘ
    ͍ͯͩ͘͠͞ɻ·ͨname͸ʮ঎඼໊ʯɺcategory1͸ʮΧςΰϦ1ʯͱผ໊
    Λ͚ͭͯग़ྗ͍ͯͩ͘͠͞ɻ
    ΧϥϜ໊ ҙຯ ܕ උߟ
    QSPEVDU@JE ঎඼*% 5&95
    OBNF ঎඼໊ 5&95
    QSJDF ֹۚ */5&(&3
    DBUFHPSZ ΧςΰϦ 5&95
    DBUFHPSZ ΧςΰϦ 5&95
    DBUFHPSZ ΧςΰϦ 5&95
    QSPEVDUTςʔϒϧ
    

    View Slide

35. 
    4&-&$5จɹղ౴ྫᶃ
    ঎඼ςʔϒϧ QSPEVDUTςʔϒϧ
    ͔Βnameͱcategory1ͷσʔλΛऔಘ
    ͍ͯͩ͘͠͞ɻ·ͨname͸ʮ঎඼໊ʯɺcategory1͸ʮΧςΰϦ1ʯͱผ໊
    Λ͚ͭͯग़ྗ͍ͯͩ͘͠͞ɻ
    SELECT
    name AS '঎඼໊',
    category1 AS 'ΧςΰϦ1'
    FROM products
    

    View Slide

36. 
    4&-&$5จɹղ౴ྫᶃɹղઆ
    ঎඼ςʔϒϧ QSPEVDUTςʔϒϧ
    ͔Βnameͱcategory1ͷσʔλΛऔಘ
    ͍ͯͩ͘͠͞ɻ·ͨname͸ʮ঎඼໊ʯɺcategory1͸ʮΧςΰϦ1ʯͱผ໊
    Λ͚ͭͯग़ྗ͍ͯͩ͘͠͞ɻ
    SELECT
    name AS '঎඼໊',
    category1 AS 'ΧςΰϦ1'
    FROM products
    w ඞཁͳΧϥϜΛ4&-&$5จͰࢦఆ
    w "4Λ࢖ͬͯผ໊Ͱग़ྗ
    

    View Slide

37. 
    4&-&$5จͷΠϝʔδ
    QSPEVDU@JE OBNF QSJDF DBUFHPSZ
    # ͓͍͍͠ਫ ৯඼
    # γϟʔϓϖϯ จ๪۩
    # ࿹࣌ܭ ϑΝογϣϯ
    ɾɾɾ ɾɾɾ ɾɾɾ ɾɾɾ
    4&-&$5จ͸ςʔϒϧ͔ΒྻΛࢦఆͯ͠σʔλΛऔಘ͢ΔΠϝʔδ
    QSPEVDU@JE OBNF
    # ͓͍͍͠ਫ
    # γϟʔϓϖϯ
    # ࿹࣌ܭ
    ɾɾɾ ɾɾɾ
    

    View Slide

38. 
    42-ߨ࠲
    4&-&$5
    ू߹ؔ਺
    (3061#:
    %*45*/$5
    8)&3&
    #&58&&/
    -*,&
    */
    -*.*5
    03%&3#:
    +0*/
    )"7*/(
    $"4&
    αϒΫΤϦ
    8*5)
    6/*0/ 6/*0/"--
    
    ͦͷଞ5JQT
    

    View Slide

39. 
    ू߹ؔ਺ɾूܭؔ਺
    wσʔλͷ݅਺ɺ߹ܭɺฏۉͳͲΛूܭ͢Δࡍʹ࢖͏ؔ਺
    wෳ਺͋ΔσʔλΛ୅ද஋ʹ·ͱΊΔํ๏
    46. ૯࿨ΛٻΊΔɻ/6--ͷ৔߹͸ूܭର৅֎
    ."9 ࠷େ஋ΛٻΊΔ
    .*/ ࠷খ஋ΛٻΊΔ
    "7( ฏۉ஋ΛٻΊΔɻ/6--ͷ৔߹͸ूܭର৅֎
    $06/5 ૯਺ΛٻΊΔɻ/6--ͷ৔߹͸ूܭର৅֎
    

    View Slide

40. 
    ू߹ؔ਺ɾूܭؔ਺ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃ
    SELECT
    COUNT(order_id)
    FROM order_data
    

    View Slide

41. 
    ू߹ؔ਺ɾूܭؔ਺ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃɹղઆ
    SELECT
    COUNT(order_id)
    FROM order_data
    w ʮ$06/5ʯ͸Ϩίʔυ਺ σʔλͷ૯݅਺σʔλͷߦ਺
    ΛΧ΢ϯτ͢Δ
    w
    ͷதʹ͸ Ҿ਺ʹ͸
    ूܭ͍ͨ͠ΧϥϜ ྻ
    Λࢦఆ͠·͢
    ΧϥϜ໊ ҙຯ ܕ උߟ
    PSEFS@JE ஫จ*% 5&95
    VTFS@JE Ϣʔβʔ*% 5&95
    PSEFS@QSPEVDU@JE ঎඼*% 5&95
    PSEFS@EBUF ஫จ೔࣌ */5&(&3
    JT@EJTDPVOUFE ׂҾϑϥά */5&(&3
    ɿׂҾͳ͠
    ɿׂҾ͋Γ
    JT@DBODFMFE Ωϟϯηϧϑϥά */5&(&3
    ɿΩϟϯηϧͳ͠
    ɿΩϟϯηϧ͋Γ
    PSEFS@EBUBςʔϒϧ
    

    View Slide

42. 
    ू߹ؔ਺ɾूܭؔ਺ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄ
    SELECT
    COUNT(order_id) AS count_1,
    COUNT(*) AS count_2,
    COUNT(1) AS count_3
    FROM order_data
    

    View Slide

43. 
    ू߹ؔ਺ɾूܭؔ਺ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄɹղઆ
    SELECT
    COUNT(order_id) AS count_1,
    COUNT(*) AS count_2,
    COUNT(1) AS count_3
    FROM order_data ߦ਺ΛΧ΢ϯτ͍ͨ͠৔߹͸ͲͷྻΛࢦఆ
    ͯ͠΋ಉ͡ͳͷͰʮʯ΍ʮʯͱೖΕΔ৔
    ߹͕ଟ͍Ͱ͢
    "4Λ͚ͭͯू߹ؔ਺ͷ݁Ռʹ໊લΛ͚ͭΔ
    ͜ͱ΋Ͱ͖·͢ɻ
    ू߹ؔ਺͚ͩ࢖͏ͱ݁Ռͷղऍ͕ͮ͠Β͍
    ͷͰɺ"4Λ࢖໊ͬͯલΛ͚ͭΔࣄ͕ଟ͍Ͱ
    ͢
    

    View Slide

44. 
    ू߹ؔ਺ɾूܭؔ਺ɹ໰୊ᶃ
    usersςʔϒϧ͔Β஀ੜ೔ͷ࠷େ஋ɺ࠷খ஋ɺฏۉ஋Λग़ྗ͍ͯͩ͘͠͞
    ΧϥϜ໊ ҙຯ ܕ උߟ
    VTFS@JE Ϣʔβʔ*% 5&95
    TFY ੑผ 5&95 ঁੑஉੑ
    CJSUI ஀ੜ೔ */5&(&3 ஀ੜ೥͕਺ࣈͰೖΓ·͢
    JT@EFMFUFE ࡟আϑϥά */5&(&3
    ɿ௨ৗϢʔβʔ
    ɿ࡟আϢʔβʔ
    VTFSTςʔϒϧ
    

    View Slide

45. 
    ू߹ؔ਺ɾूܭؔ਺ɹղ౴ྫᶃ
    usersςʔϒϧ͔Β஀ੜ೔ͷ࠷େ஋ɺ࠷খ஋ɺฏۉ஋Λग़ྗ͍ͯͩ͘͠͞
    SELECT
    MAX(birth) AS max_birth,
    MIN(birth) AS min_birth,
    AVG(birth) AS avg_birth
    FROM users
    

    View Slide

46. 
    ू߹ؔ਺ɾूܭؔ਺ɹղ౴ྫᶃɹղઆ
    usersςʔϒϧ͔Β஀ੜ೔ͷ࠷େ஋ɺ࠷খ஋ɺฏۉ஋Λग़ྗ͍ͯͩ͘͠͞
    SELECT
    MAX(birth) AS max_birth,
    MIN(birth) AS min_birth,
    AVG(birth) AS avg_birth
    FROM users
    w ࠷େ஋͸."9ɺ࠷খ஋͸.*/ɺฏ
    ۉ஋͸"7(Λ࢖ͬͯग़ྗ
    w "4Λ࢖ͬͯผ໊Ͱग़ྗ ೚ҙ
    
    

    View Slide

47. 
    ू߹ؔ਺ɾूܭؔ਺ɹิ଍
    ू߹ؔ਺ͷ݁Ռ΍೔෇΍਺ࣈ͸଍͠ࢉҾ͖ࢉͳͲͷܭࢉ͕Ͱ͖Δ
    SELECT
    MAX(birth) AS max_birth,
    MIN(birth) AS min_birth,
    MAX(birth) - MIN(birth) AS diff
    FROM users
    ஀ੜ೔ͷ࠷େ஋ͱ࠷খ஋ΛҾ͖ࢉ͕
    Ͱ͖Δ
    ˠ࠷େͷ೥ྸ͕ࠩΘ͔Δ
    

    View Slide

48. 
    ू߹ؔ਺ɾूܭؔ਺ͷΠϝʔδ
    PSEFS@JE VTFS@JE PSEFS@QSPEVDU@JE PSEFS@EBUF JT@EJTDPVOUFE JT@DBODFMFE
    $ " #
    $ " #
    $ " #
    ɾɾɾ ɾɾɾ ɾɾɾ ɾɾɾ
    ू߹ؔ਺͸ςʔϒϧ͔ΒߦΛ·ͱΊͯͭͷϨίʔυʹ͢ΔΠϝʔδ
    DPVOU@ DPVOU@ DPVOU@
    
    

    View Slide

49. 
    42-ߨ࠲
    4&-&$5
    ू߹ؔ਺
    (3061#:
    %*45*/$5
    8)&3&
    #&58&&/
    -*,&
    */
    -*.*5
    03%&3#:
    +0*/
    )"7*/(
    $"4&
    αϒΫΤϦ
    8*5)
    6/*0/ 6/*0/"--
    
    ͦͷଞ5JQT
    

    View Slide

50. 
    (3061#:
    ɾෳ਺ͷσʔλΛಛఆͷ੾ΓޱͰ·ͱΊΔ࣌ʹ࢖͏ߏจ
    ɾෳ਺ͷσʔλΛͭʹ·ͱΊΔࣄ͔Βू߹ؔ਺
    $06/5 46.ͳͲ
    ͱҰॹʹ࢖͏͜ͱ͕ଟ͍
    

    View Slide

51. 
    (3061#:ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃ
    SELECT
    COUNT(*)
    FROM products
    SELECT
    category1,
    COUNT(*)
    FROM products
    GROUP BY category1
    ςʔϒϧશମͷ݅਺Λग़ྗ͢Δ৔߹ ΧςΰϦ͝ͱͷ݅਺Λग़ྗ͢Δ৔߹
    

    View Slide

52. 
    (3061#:ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃɹղઆ
    SELECT
    COUNT(*)
    FROM products
    SELECT
    category1,
    COUNT(*)
    FROM products
    GROUP BY category1
    ςʔϒϧશମͷ݅਺Λग़ྗ͢Δ৔߹ ΧςΰϦ͝ͱͷ݅਺Λग़ྗ͢Δ৔߹
    (3061#:Ͱࢦఆͨ͠ΧϥϜΛ4&-&$5۟Ͱ΋ࢦఆ͢Δ
    ˞4&-&$5۟Ͱࢦఆ͠ͳͯ͘΋ߏจͱͯ͠͸੒Γཱ͕ͭղऍ͕͠ʹ͘͘ͳΔ
    

    View Slide

53. 
    (3061#:ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄ
    SELECT
    category1,
    category2,
    COUNT(*)
    FROM products
    GROUP BY
    category1,
    category2
    

    View Slide

54. 
    (3061#:ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄɹղઆ
    SELECT
    category1,
    category2,
    COUNT(*)
    FROM products
    GROUP BY
    category1,
    category2
    (3061#:͸ΧϯϚ۠੾ΓͰෳ਺ͷΧ
    ϥϜΛࢦఆ͢Δ͜ͱ΋Ͱ͖·͢
    DBUFHPSZͱDBUFHPSZͰ·ͱΊͨͱ͖
    ͷ݅਺Λग़ྗ͢Δ
    

    View Slide

55. 
    (3061#:ɹ໰୊ᶃ
    ঎඼ςʔϒϧ QSPEVDUTςʔϒϧ
    ͔ΒΧςΰϦ1͝ͱͷฏۉՁ֨Λऔಘ͠
    ͍ͯͩ͘͞
    ΧϥϜ໊ ҙຯ ܕ උߟ
    QSPEVDU@JE ঎඼*% 5&95
    OBNF ঎඼໊ 5&95
    QSJDF ֹۚ */5&(&3
    DBUFHPSZ ΧςΰϦ 5&95
    DBUFHPSZ ΧςΰϦ 5&95
    DBUFHPSZ ΧςΰϦ 5&95
    QSPEVDUTςʔϒϧ
    

    View Slide

56. 
    (3061#:ɹղ౴ྫᶃ
    ঎඼ςʔϒϧ QSPEVDUTςʔϒϧ
    ͔ΒΧςΰϦ1͝ͱͷฏۉՁ֨Λऔಘ͠
    ͍ͯͩ͘͞
    SELECT
    category1,
    AVG(price)
    FROM products
    GROUP BY category1
    SELECT
    category1,
    AVG(price),
    COUNT(*)
    FROM products
    GROUP BY category1
    

    View Slide

57. 
    (3061#:ɹղ౴ྫᶃɹղઆ
    ঎඼ςʔϒϧ QSPEVDUTςʔϒϧ
    ͔ΒΧςΰϦ1͝ͱͷฏۉՁ֨Λऔಘ͠
    ͍ͯͩ͘͞
    SELECT
    category1,
    AVG(price)
    FROM products
    GROUP BY category1
    SELECT
    category1,
    AVG(price),
    COUNT(*)
    FROM products
    GROUP BY category1
    w ฏۉʹ͸"7(Λ࢖͍·͢
    w ू߹ؔ਺͸(3061#:ͱ߹ΘͤͯͭҎ্ग़ྗ͢Δࣄ΋Ͱ͖·͢
    

    View Slide

58. 
    (3061#:ͷΠϝʔδ
    (3061#:͸ࢦఆͨ͠ྻ͝ͱʹςʔϒϧ͔Βෳ਺͋ΔߦΛ·ͱΊͯ
    ͭͷϨίʔυʹ͢ΔΠϝʔδ
    DBUFHPSZ DPVOU@
    ϑΝογϣϯ
    ৯඼
    ೔༻඼
    QSPEVDU@JE OBNF QSJDF DBUFHPSZ DBUFHPSZ
    # ͓͍͍͠ਫ ৯඼ ໺ࡊ
    # γϟʔϓϖϯ ೔༻඼ จ๪۩
    # ࿹࣌ܭ ϑΝογϣϯ ΞΫηαϦʔ
    # τʔτόοΫ ϑΝογϣϯ Χόϯ
    ɾɾɾ ɾɾɾ ɾɾɾ ɾɾɾ ɾɾɾ
    

    View Slide

59. 
    42-ߨ࠲
    4&-&$5
    ू߹ؔ਺
    (3061#:
    %*45*/$5
    8)&3&
    #&58&&/
    -*,&
    */
    -*.*5
    03%&3#:
    +0*/
    )"7*/(
    $"4&
    αϒΫΤϦ
    8*5)
    6/*0/ 6/*0/"--
    
    ͦͷଞ5JQT
    

    View Slide

60. 
    %*45*/$5
    wॏෳΛഉআ͍ͨ࣌͠ʹ࢖͏
    wॏෳΛഉআͯ͠ෳ਺ϨίʔυΛϨίʔυʹ͢Δ
    

    View Slide

61. 
    %*45*/$5ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃ
    SELECT
    category1
    FROM products
    SELECT
    DISTINCT category1
    FROM products
    ΧςΰϦΛग़ྗ ॏෳഉআͯ͠ΧςΰϦΛग़ྗ
    

    View Slide

62. 
    %*45*/$5ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃɹղઆ
    SELECT
    category1
    FROM products
    SELECT
    DISTINCT category1
    FROM products
    ΧςΰϦΛग़ྗ ॏෳഉআͯ͠ΧςΰϦΛग़ྗ
    4&-&$5จͷதͰॏෳഉআ͍߲ͨ͠໨ͷલʹ%*45*/$5Λهड़͢Δ
    

    View Slide

63. 
    %*45*/$5ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄ
    SELECT
    COUNT(*),
    COUNT(category1),
    COUNT(DISTINCT category1)
    FROM products
    

    View Slide

64. 
    %*45*/$5ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄɹղઆ
    SELECT
    COUNT(*),
    COUNT(category1),
    COUNT(DISTINCT category1)
    FROM products
    ू߹ؔ਺ͷதͰॏෳഉআ͍ͨ࣌͠ʹ
    Α͘࢖ΘΕΔ
    

    View Slide

65. 
    42-ߨ࠲
    4&-&$5
    ू߹ؔ਺
    (3061#:
    %*45*/$5
    8)&3&
    #&58&&/
    -*,&
    */
    -*.*5
    03%&3#:
    +0*/
    )"7*/(
    $"4&
    αϒΫΤϦ
    8*5)
    6/*0/ 6/*0/"--
    
    ͦͷଞ5JQT
    

    View Slide

66. 
    8)&3&
    wಛఆͷ৚݅Λ͚ͭͯσʔλΛग़ྗ͍ͨ࣌͠ʹ࢖͏ߏจ
    ౳͍͠
    େ͖͍
    খ͍͞
    Ҏ্
    ҎԼ
    ౳͘͠ͳ͍
    "/% ͭҎ্ͷ৚݅Λ݁߹ͯ͠ɺ྆ํͷ৚͕݅Ұக͢Δ
    03 ͭҎ্ͷ৚݅Λ݁߹ͯ͠ɺ͍ͣΕ͔ͷ৚݅ʹҰக͢Δ
    /05 ৚݅ͷ൓ର
    #&58&&/""/%# "ͱ#ͷؒʹ֘౰͢Δ
    */ ର৅ͷΧϥϜ͕Ұཡͷ͍ͣΕ͔ʹҰக͢Δ
    -*,& ର৅ͷΧϥϜ͕৚݅ͷจࣈྻʹϚον͢Δ
    

    View Slide

67. 
    8)&3&ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃ
    SELECT
    COUNT(*)
    FROM order_data
    SELECT
    COUNT(*)
    FROM order_data
    WHERE order_date = '2020-01-01'
    શϨίʔυ਺ΛΧ΢ϯτ ৚݅ʹ֘౰͢ΔϨίʔυ਺ΛΧ΢ϯτ
    

    View Slide

68. 
    8)&3&ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃɹղઆ
    SELECT
    COUNT(*)
    FROM order_data
    SELECT
    COUNT(*)
    FROM order_data
    WHERE order_date = '2020-01-01'
    શϨίʔυ਺ΛΧ΢ϯτ ৚݅ʹ֘౰͢ΔϨίʔυ਺ΛΧ΢ϯτ
    w '30.ͷޙʹ8)&3&Ͱ৚݅Λࢦఆ
    w 8)&3&Ͱࢦఆͨ͠৚݅ͷϨίʔυΛग़ྗͰ͖Δ
    

    View Slide

69. 
    8)&3&ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄ
    SELECT
    COUNT(*)
    FROM order_data
    WHERE order_date <= '2020-01-05'
    SELECT
    COUNT(*)
    FROM order_data
    WHERE order_date <> '2020-01-01'
    

    View Slide

70. 
    8)&3&ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄɹղઆ
    SELECT
    COUNT(*)
    FROM order_data
    WHERE order_date <= '2020-01-05'
    SELECT
    COUNT(*)
    FROM order_data
    WHERE order_date <> '2020-01-01'
    ೥݄೔ΑΓ΋খ͍͞೔෇ͷϨίʔ
    υΛऔಘ
    ೥݄೔Ҏ֎ͷϨίʔυΛऔಘ
    

    View Slide

71. 
    8)&3&ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶅ
    SELECT
    COUNT(*)
    FROM order_data
    WHERE order_date IS NULL
    SELECT
    COUNT(*)
    FROM order_data
    WHERE order_date IS NOT NULL
    

    View Slide

72. 
    8)&3&ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶅɹղઆ
    SELECT
    COUNT(*)
    FROM order_data
    WHERE order_date IS NULL
    SELECT
    COUNT(*)
    FROM order_data
    WHERE order_date IS NOT NULL
    PSEFS@EBUB͕/6-- σʔλ͕ۭ
    Ϩίʔ
    υΛऔಘ
    PSEFS@EBUB͕/6--Ͱͳ͍ σʔλ͕ۭͰ
    ͳ͍
    ϨίʔυΛऔಘ
    

    View Slide

73. 
    8)&3&ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶆ
    SELECT
    COUNT(*)
    FROM order_data
    WHERE order_date >= '2020-01-01'
    AND order_date <= '2020-01-02'
    

    View Slide

74. 
    8)&3&ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶆɹղઆ
    SELECT
    COUNT(*)
    FROM order_data
    WHERE order_date >= '2020-01-01'
    AND order_date <= '2020-01-02'
    ೥݄೔ΑΓେ͖͘ɺ
    ೥݄೔ΑΓখ͍͞೔෇ͷ
    ϨίʔυΛऔಘ
    ෳ਺ͷ৚݅Λ͚ͭΔ৔߹ɺͭ໨Ҏ߱ͷ৚݅͸ʮ"/%ʯͰܨ͛Δ
    

    View Slide

75. 
    8)&3&ɹ໰୊ᶃ
    ঎඼ςʔϒϧ QSPEVDUTςʔϒϧ
    ͔ΒՁ͕֨1000ԁҎԼͷ঎඼਺Λग़ྗ͠
    ͍ͯͩ͘͞
    ΧϥϜ໊ ҙຯ ܕ උߟ
    QSPEVDU@JE ঎඼*% 5&95
    OBNF ঎඼໊ 5&95
    QSJDF ֹۚ */5&(&3
    DBUFHPSZ ΧςΰϦ 5&95
    DBUFHPSZ ΧςΰϦ 5&95
    DBUFHPSZ ΧςΰϦ 5&95
    QSPEVDUTςʔϒϧ
    

    View Slide

76. 
    8)&3&ɹղ౴ྫᶃ
    ঎඼ςʔϒϧ QSPEVDUTςʔϒϧ
    ͔ΒՁ͕֨1000ԁҎԼͷ঎඼਺Λग़ྗ͠
    ͍ͯͩ͘͞
    SELECT
    COUNT(*)
    FROM products
    WHERE price <= 1000
    

    View Slide

77. 
    8)&3&ɹղ౴ྫᶃɹղઆ
    ঎඼ςʔϒϧ QSPEVDUTςʔϒϧ
    ͔ΒՁ͕֨1000ԁҎԼͷ঎඼਺Λग़ྗ͠
    ͍ͯͩ͘͞
    SELECT
    COUNT(*)
    FROM products
    WHERE price <= 1000
    ঎඼਺ͷΧ΢ϯτʹ͸ू߹ؔ਺ͷ
    ʮ$06/5ʯΛ࢖͏
    ʮԁҎԼʯͷ৚݅ʹ8)&3&Ͱ
    ʮʯΛ࢖͏
    

    View Slide

78. 
    8)&3&ɹิ଍
    ຊ౰ʹσʔλ͕͋ͬͯΔ͔֬ೝ͢Δ৔߹ʹ$06/5Λ࢖ΘͣʹσʔλΛશ
    ͯநग़ͯ֬͠ೝ͢Δࣄ͕͋Γ·͢ ࣮ࡍͷσʔλ෼ੳͰ΋Α͘࢖͍·͢
    
    SELECT
    *
    FROM products
    WHERE price <= 1000
    σʔλͷ֬ೝͰʮʯΛ࢖ͬͯूܭલͷ
    σʔλΛ֬ೝ͢Δ
    

    View Slide

79. 
    8)&3&ͷΠϝʔδ
    8)&3&͸৚݅ʹҰகͨ͠ϨίʔυΛநग़͢ΔΠϝʔδ
    PSEFS@JE VTFS@JE PSEFS@QSPEVDU@JE PSEFS@EBUF
    $ " #
    $ " #
    $ " #
    ɾɾɾ ɾɾɾ ɾɾɾ ɾɾɾ
    PSEFS@JE VTFS@JE PSEFS@QSPEVDU@JE PSEFS@EBUF
    $ " #
    $ " #
    

    View Slide

80. 
    42-ߨ࠲
    4&-&$5
    ू߹ؔ਺
    (3061#:
    %*45*/$5
    8)&3&
    #&58&&/
    -*,&
    */
    -*.*5
    03%&3#:
    +0*/
    )"7*/(
    $"4&
    αϒΫΤϦ
    8*5)
    6/*0/ 6/*0/"--
    
    ͦͷଞ5JQT
    

    View Slide

81. 
    8)&3&Λ࢖ͬͨ৚݅ɹ#&58&&/
    ɾ8)&3&ͷதͰ࢖͑Δ৚݅ࢦఆͷ΍Γํ
    ɾ೔෇΍਺ࣈͷಛఆͷൣғΛநग़͢Δ৔߹ʹ࢖͏
    

    View Slide

82. 
    #&58&&/ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃ
    SELECT
    *
    FROM products
    WHERE price <= 1000
    AND price >= 500
    SELECT
    *
    FROM products
    WHERE price BETWEEN 500 AND 1000
    "/%Ͱ৚݅Λͭͳ͛ͨ৔߹ #&58&&/Λ࢖ͬͯൣғࢦఆΛͨ͠৔߹
    

    View Slide

83. 
    #&58&&/ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃɹղઆ
    SELECT
    *
    FROM products
    WHERE price <= 1000
    AND price >= 500
    SELECT
    *
    FROM products
    WHERE price BETWEEN 500 AND 1000
    "/%Ͱ৚݅Λͭͳ͛ͨ৔߹ #&58&&/Λ࢖ͬͯൣғࢦఆΛͨ͠৔߹
    ͲͪΒ΋݁Ռ͸ಉ͡ͳͷͰͲͬͪΛ࢖ͬͯ΋ྑ͍Ͱ͕͢ɺ
    ൣғࢦఆͷ৔߹͸#&58&&/Λ࢖ͬͨํ͕௚ײతʹ෼͔Γ΍͍͢
    

    View Slide

84. 
    42-ߨ࠲
    4&-&$5
    ू߹ؔ਺
    (3061#:
    %*45*/$5
    8)&3&
    #&58&&/
    -*,&
    */
    -*.*5
    03%&3#:
    +0*/
    )"7*/(
    $"4&
    αϒΫΤϦ
    8*5)
    6/*0/ 6/*0/"--
    
    ͦͷଞ5JQT
    

    View Slide

85. 
    8)&3&Λ࢖ͬͨ৚݅ɹ-*,&
    w 8)&3&ͷதͰ࢖͑Δ৚݅ࢦఆͷ΍Γํ
    w จࣈྻΛݕࡧ͢Δ࣌ʹ࢖͏
    w ݕࡧͷࡍʹ͸ϫΠϧυΧʔυจࣈΛ࢖ͬͯจࣈྻϚο
    νΛߦ͏
    จࣈҎ্ͷ೚ҙͷจࣈྻ
    @ ೚ҙͷจࣈ
    -*,&Ͱ࢖͑ΔϫΠϧυΧʔυจࣈ
    ΞϯμʔείΞ
    

    View Slide

86. 
    -*,&ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃ
    SELECT
    *
    FROM products
    WHERE name LIKE '%ηοτ'
    

    View Slide

87. 
    -*,&ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃɹղઆ
    SELECT
    *
    FROM products
    WHERE name LIKE '%ηοτ'
    ʮ8)&3&ྻ໊-*,&bݕࡧ৚݅`ʯͱͯ͠࢖͏
    ໊લ͕ʮʓʓηοτʯʹͳ͍ͬͯΔσʔλ
    Λऔಘ(ʓʓ͸Կจࣈೖͬͯ΋ྑ͍)
    

    View Slide

88. 
    -*,&ͷύλʔϯϚονྫ
    WHERE name LIKE '%ηοτ'
    ࣮ફͰ͸ʮʯΛ࢖͏ࣄ͕ଟ͍ͷͰʮʯͷ࢖͍ํ͚֮ͩ͑Ε͹0,
    WHERE name LIKE 'ηοτ%'
    WHERE name LIKE '%ηοτ%'
    WHERE name LIKE '_ηοτ'
    ʮʓʓηοτʯ
    ʮʓʓηοτʓʓʯ
    ʮʓηοτʯ
    ʮηοτʓʓʯ
    ˞ʓ͸จࣈ
    ˞ʓ͸ԿจࣈͰ΋0,
    ˞ʓ͸ԿจࣈͰ΋0,
    ˞ʓ͸ԿจࣈͰ΋0,
    

    View Slide

89. 
    42-ߨ࠲
    4&-&$5
    ू߹ؔ਺
    (3061#:
    %*45*/$5
    8)&3&
    #&58&&/
    -*,&
    */
    -*.*5
    03%&3#:
    +0*/
    )"7*/(
    $"4&
    αϒΫΤϦ
    8*5)
    6/*0/ 6/*0/"--
    
    ͦͷଞ5JQT
    

    View Slide

90. 
    8)&3&Λ࢖ͬͨ৚݅ɹ*/
    w 8)&3&ͷதͰ࢖͑Δ৚݅ࢦఆͷ΍Γํ
    w ෳ਺ͷࢦఆͨ͠஋ʹ֘౰͢Δ͔Λ൑ผ͢Δ࣌ʹ࢖͏
    

    View Slide

91. 
    */ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃ
    SELECT
    DISTINCT category1
    FROM products
    SELECT
    *
    FROM products
    WHERE category1 = '৯඼'
    ΧςΰϦͷೖ͍ͬͯΔ஋Λ֬ೝ ΧςΰϦΛࢦఆͯ͠ϨίʔυΛऔಘ
    ΧςΰϦ৚݅Λࢦఆͯ͠ςʔϒϧ͔ΒϨίʔυΛऔಘ͢Δ৔߹
    

    View Slide

92. 
    */ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄ
    SELECT
    *
    FROM products
    WHERE category1 = '৯඼'
    OR category1 = '೔༻඼'
    ෳ਺ͷΧςΰϦ৚݅Λࢦఆͯ͠ςʔϒϧ͔ΒϨίʔυΛऔಘ͢Δ৔߹
    

    View Slide

93. 
    */ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄɹղઆ
    SELECT
    *
    FROM products
    WHERE category1 = '৯඼'
    OR category1 = '೔༻඼'
    ʮ03ʯ͸ANDͷ൓ରͰɺ௚લͷ৚͕݅௚
    ޙͷ৚݅ͷ͍ͣΕ͔ʹ֘౰͢ΔσʔλΛந
    ग़͢Δ
    ෳ਺ͷΧςΰϦ৚݅Λࢦఆͯ͠ςʔϒϧ͔ΒϨίʔυΛऔಘ͢Δ৔߹
    

    View Slide

94. 
    */ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶅ
    SELECT
    *
    FROM products
    WHERE category1 IN ('৯඼', '೔༻඼')
    ෳ਺ͷΧςΰϦ৚݅Λࢦఆͯ͠ςʔϒϧ͔ΒϨίʔυΛऔಘ͢Δ৔߹
    

    View Slide

95. 
    */ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶅɹղઆ
    SELECT
    *
    FROM products
    WHERE category1 IN ('৯඼', '೔༻඼')
    ʮ*/ʯΛ࢖ͬͯෳ਺৚
    ݅ʹϚον͢Δ͔Λ
    ߦͰهड़Ͱ͖Δ
    ෳ਺ͷΧςΰϦ৚݅Λࢦఆͯ͠ςʔϒϧ͔ΒϨίʔυΛऔಘ͢Δ৔߹
    03Λ࢖ͬͨ৚݅ͱ݁Ռ͸ಉ͡ͳͷͰͲͬͪΛ࢖ͬͯ΋ྑ͍Ͱ͕͢ɺ
    ෳ਺ͷ஋Λࢦఆ͢Δ৔߹͸*/Λ࢖ͬͨํ͕௚ײతʹ෼͔Γ΍͍͢
    

    View Slide

96. 
    42-ߨ࠲
    4&-&$5
    ू߹ؔ਺
    (3061#:
    %*45*/$5
    8)&3&
    #&58&&/
    -*,&
    */
    -*.*5
    03%&3#:
    +0*/
    )"7*/(
    $"4&
    αϒΫΤϦ
    8*5)
    6/*0/ 6/*0/"--
    
    ͦͷଞ5JQT
    

    View Slide

97. 
    -*.*5
    w ݁Ռͷग़ྗ݅਺Λࢦఆ͢Δ৚݅
    

    View Slide

98. 
    -*.*5ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃ
    SELECT
    *
    FROM products
    SELECT
    *
    FROM products
    LIMIT 10
    શ݅औಘ ͚݅ͩऔಘ
    

    View Slide

99. 
    -*.*5ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃɹղઆ
    SELECT
    *
    FROM products
    SELECT
    *
    FROM products
    LIMIT 10
    શ݅औಘ ͚݅ͩऔಘ
    w -*.*5Λ࢖͏ͱ42-ͷ࣮ߦ଎౓͕଎͘ͳΔ શ݅औಘͰ͸ͳ݅͘਺Λߜͬͯ
    औಘ͕ՄೳͳͷͰ
    
    w Ͳ͏Ώ͏σʔλ͕ೖ͍ͬͯΔͷ͔֬ೝ͍ͨ࣌͠ʹΑ͘࢖͏(શ݅औಘͩͱ
    SQLͷෛՙ͕͔͔Δ৔߹͕͋ΔͷͰ)
    

    View Slide

100. 
     42-ߨ࠲
     4&-&$5
     ू߹ؔ਺
     (3061#:
     %*45*/$5
     8)&3&
     #&58&&/
     -*,&
     */
     -*.*5
     03%&3#:
     +0*/
     )"7*/(
     $"4&
     αϒΫΤϦ
     8*5)
     6/*0/ 6/*0/"--
     
     ͦͷଞ5JQT
     

     View Slide

101. 
     03%&3#:
     w ݁ՌΛಛఆͷ৚݅Ͱฒ΂ସ͑Δ
     

     View Slide

102. 
     03%&3#:ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃ
     SELECT
     *
     FROM products
     SELECT
     *
     FROM products
     ORDER BY price
     શ݅औಘ ஋ஈ͕͍҆ॱʹฒͼସ͑
     

     View Slide

103. 
     03%&3#:ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃɹղઆ
     SELECT
     *
     FROM products
     SELECT
     *
     FROM products
     ORDER BY price ASC
     શ݅औಘ ஋ஈ͕͍҆ॱʹฒͼସ͑
     ͲͷΧϥϜΛԿॱʹฒ΂ସ͑Δͷ
     ͔Λࢦఆ
     

     View Slide

104. 
     03%&3#:ɹฒͼॱͷղઆ
     ORDER BY price
     ORDER BY price ASC
     ORDER BY price DESC
     ঢॱ খ͍͞ॱ
     Ͱฒͼସ͑
     ߱ॱ େ͖͍ॱ
     Ͱฒͼସ͑
     ঢॱ খ͍͞ॱ
     Ͱฒͼସ͑
     

     View Slide

105. 
     03%&3#:ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄ
     SELECT
     *
     FROM products
     ORDER BY price DESC, category1 ASC
     

     View Slide

106. 
     03%&3#:ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄɹղઆ
     SELECT
     *
     FROM products
     ORDER BY price DESC, category1 ASC
     ฒͼସ͑৚݅͸ෳ਺ࢦఆͰ
     ͖Δ
     w ୈιʔτج४͕஋ஈͷ߱ॱ
     w ୈιʔτج४͕ΧςΰϦͷঢॱ
     

     View Slide

107. 
     42-ߨ࠲
     4&-&$5
     ू߹ؔ਺
     (3061#:
     %*45*/$5
     8)&3&
     #&58&&/
     -*,&
     */
     -*.*5
     03%&3#:
     +0*/
     )"7*/(
     $"4&
     αϒΫΤϦ
     8*5)
     6/*0/ 6/*0/"--
     
     ͦͷଞ5JQT
     

     View Slide

108. 
     +0*/
     w ͭҎ্ͷςʔϒϧΛ݁߹ͤ͞Δ࣌ʹ࢖͏
     w ݁߹ͤ͞ΔͨΊʹ͸ԿΛΩʔʹͯ݁͠߹ͤ͞Δ͔ࢦఆ͢Δඞཁ͕͋Δ
     w ݁߹ʹ͸େ͖͘෼͚ͯछྨ͋Δ ˞ݫີʹ͸छྨҎ্͋Δ
     
     ᶃ಺෦݁߹
     *//&3+0*/
     ֎෦݁߹
     ᶄࠨ֎෦݁߹
     -&'5
     065&3+0*/
     ᶅӈ֎෦݁߹
     3*()5
     065&3+0*/
     ݁߹
     

     View Slide

109. 
     ᶃ಺෦݁߹
     *//&3+0*/
     ᶄࠨ֎෦݁߹
     -&'5
     065&3+0*/
     ᶅӈ֎෦݁߹
     3*()5
     065&3+0*/
     ྆ํͷςʔϒϧͷڞ௨෦෼ͷ
     ΈΛ݁߹ͯ͠औಘ͢Δ
     ࠨଆͷςʔϒϧΛશͯऔಘ͠
     ͯ݁߹͢Δ
     ӈଆͷςʔϒϧΛશͯऔಘ͠
     ͯ݁߹͢Δ
     छྨͷ+0*/ɹ֓ཁ
     

     View Slide

110. 
     +0*/ͷجຊߏจ
     FROM ςʔϒϧ1 AS ςʔϒϧ1ผ໊
     JOIN ςʔϒϧ2 AS ςʔϒϧ2ผ໊ ON ςʔϒϧ1ผ໊.Ωʔ = ςʔϒϧ2ผ໊.Ωʔ
     FROM order_data AS o
     INNER JOIN users AS u ON o.user_id = u.user_id
     جຊߏจ
     ۩ମྫ
     w ݁߹ͷࡍͷςʔϒϧ໊Λผ໊Ͱॻ͘͜ͱ͕Ͱ͖Δɻ4&-&$5ͷ"4ͱಉ͡ "4͸লུՄೳ
     
     w ςʔϒϧ໊ͷ಄จࣈʹͨ͠ΓɺA,B΍1,2ͳͲͷ࿈൪Ͱผ໊Λ͚ͭΔ৔߹΋͋Δ͕ɺՄಡੑ্͕
     ͕ΔΑ͏ʹ͚ͭͨํ͕ྑ͍
     ݁߹৚݅
     

     View Slide

111. 
     +0*/ ಺෦݁߹
     ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃ
     SELECT
     o.order_id,
     o.user_id,
     u.sex,
     u.birth
     FROM order_data o
     INNER JOIN users u ON o.user_id = u.user_id
     LIMIT 10
     

     View Slide

112. 
     +0*/ ಺෦݁߹
     ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃɹղઆ
     SELECT
     o.order_id,
     o.user_id,
     u.sex,
     u.birth
     FROM order_data o
     INNER JOIN users u ON o.user_id = u.user_id
     LIMIT 10
     w PSEFS@EBUBͱVTFSTͷ৘ใ͕ͭͷςʔϒϧʹͳͬͯग़ྗͰ͖Δ
     w ςʔϒϧ໊Λผ໊Ͱॻ͘͜ͱ͕Ͱ͖Δʮ"4ʯ͸লུՄೳ
     w 4&-&$5ͰྻΛࢦఆ͢Δࡍ͸Ͳͷςʔϒϧͷྻͳͷ͔Λ໌ࣔ͢Δඞཁ͕͋Δ PVTFS@JE
     
     

     View Slide

113. 
     +0*/ ಺෦݁߹
     ɹઆ໌
     SELECT
     COUNT(*)
     FROM order_data o
     INNER JOIN users u ON o.user_id = u.user_id
     SELECT
     COUNT(*)
     FROM order_data o
     JOIN users u ON o.user_id = u.user_id
     ʮ*//&3+0*/ʯ͸
     ʮ+0*/ʯͱলུͰ͖Δ
     

     View Slide

114. 
     +0*/ ಺෦݁߹
     ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄ
     SELECT
     COUNT(*)
     FROM order_data o
     INNER JOIN users u ON o.user_id = u.user_id
     

     View Slide

115. 
     +0*/ ಺෦݁߹
     ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄɹղઆ
     SELECT
     COUNT(*)
     FROM order_data o
     INNER JOIN users u ON o.user_id = u.user_id
     w +0*/ͨ͠ޙͷςʔϒϧͷϨίʔυ਺Λ֬ೝ
     w ݩʑ͋ͬͨPSEFS@EBUBͷϨίʔυ਺ΑΓগͳ͘ͳ͍ͬͯΔ
     w ͜Ε͸಺෦݁߹ʹΑͬͯuser_idΛΩʔʹͨ࣌͠ʹorder_dataͱusersͷ2ͭ
     ͷςʔϒϧʹ྆ํೖ͍ͬͯΔσʔλΛऔಘ͍ͯ͠Δ͔Β
     

     View Slide

116. 
     +0*/ ಺෦݁߹
     ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄɹղઆ
     SELECT
     COUNT(*)
     FROM order_data o
     INNER JOIN users u ON o.user_id = u.user_id
     w PSEFS@EBUBͷதʹೖ͍ͬͯΔVTFS@JEΛ֬ೝ͢Δͱʮ/6--ʯʹͳ͍ͬͯ
     ΔϨίʔυ͕͋Δ ϩάΠϯͤͣʹߪೖͨ͠৔߹ΛΠϝʔδ
     
     w ʮNULLʯ͸usersςʔϒϧʹ৘ใ͕ೖ͍ͬͯͳ͍ͷͰɺͦͷ෼ͷσʔλ͕
     ݁߹͞Εͣʹ݁Ռͱͯ͠*//&3+0*/ͨ݁͠ՌͷϨίʔυ਺΋ݮ͍ͬͯΔ
     PSEFS@JE VTFS@JE PSEFS@QSPEVDU@JE
     $ " #
     $ " #
     $ /6-- #
     ɾɾɾ ɾɾɾ ɾɾɾ
     VTFS@JE TFY CJSUI
     " உੑ
     " ঁੑ
     " உੑ
     ɾɾɾ ɾɾɾ ɾɾɾ
     º
     ˓
     ˓
     VTFSTςʔϒϧ
     PSEFS@EBUBςʔϒϧ
     

     View Slide

117. 
     +0*/ ֎෦݁߹
     ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶅ
     SELECT
     *
     FROM order_data o
     LEFT OUTER JOIN users u ON o.user_id = u.user_id
     LIMIT 10
     SELECT
     *
     FROM order_data o
     INNER JOIN users u ON o.user_id = u.user_id
     LIMIT 10
     

     View Slide

118. 
     +0*/ ֎෦݁߹
     ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶅɹղઆ
     SELECT
     *
     FROM order_data o
     LEFT OUTER JOIN users u ON o.user_id = u.user_id
     LIMIT 10
     SELECT
     *
     FROM order_data o
     INNER JOIN users u ON o.user_id = u.user_id
     LIMIT 10
     ಺
     ෦
     ݁
     ߹
     ֎
     ෦
     ݁
     ߹
     

     View Slide

119. 
     +0*/ ֎෦݁߹
     ɹઆ໌
     SELECT
     *
     FROM order_data o LEFT OUTER JOIN users u ON o.user_id = u.user_id
     LIMIT 10
     w جຊߏจ͸಺෦݁߹ *//&3+0*/
     ͱಉ͡
     w -&'5065&3+0*/͸֎෦݁߹ͷͭ
     w 065&3͸লུͯ͠-&'5+0*/ͱॻ͘ࣄ͕Ͱ͖Δ
     w -&'5'30.ͷ௚ޙʹॻ͍ͨςʔϒϧɺ3*()5+0*/ͷޙʹॻ͍
     ͨςʔϒϧ
     -&'5 3*()5
     ˞42-JUFͷ৔߹͸3*()5065&3+0*/͸αϙʔτ͞Ε͍ͯͳ͍ͷͰΤϥʔʹͳΔ
     ˞࣮ࡍ3*()5065&3+0*/Λ࢖͏έʔε͸΄ͱΜͲͳ͍ͷͰɺ֎෦݁߹-&'5065&3+0*/ -&'5+0*/
     ͱ֮͑ͯྑ͍
     

     View Slide

120. 
     +0*/ ֎෦݁߹
     ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶆ
     SELECT
     COUNT(*)
     FROM order_data o
     LEFT JOIN users u ON o.user_id = u.user_id
     

     View Slide

121. 
     +0*/ ֎෦݁߹
     ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶆɹղઆ
     SELECT
     COUNT(*)
     FROM order_data o
     LEFT JOIN users u ON o.user_id = u.user_id
     w -&'5+0*/ͨ͠ޙͷςʔϒϧͷϨίʔυ਺Λ֬ೝ
     w ݩʑ͋ͬͨPSEFS@EBUBͷϨίʔυ਺ͱಉ݅͡਺ʹͳ͍ͬͯΔ
     

     View Slide

122. 
     +0*/ ֎෦݁߹
     ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶆɹղઆ
     SELECT
     COUNT(*)
     FROM order_data o
     LEFT JOIN users u ON o.user_id = u.user_id
     ʮNULLʯ͸usersςʔϒϧʹ৘ใ͕ೖ͍ͬͯͳ͍͕ɺݩͱͳΔPSEFS@EBUBʹ
     ͸ೖ͍ͬͯΔͷͰɺVTFSTςʔϒϧͷ৘ใ͕શͯ/6--ͷঢ়ଶͰ݁߹͞ΕΔ
     PSEFS@JE VTFS@JE PSEFS@QSPEVDU@JE
     $ " #
     $ " #
     $ /6-- #
     ɾɾɾ ɾɾɾ ɾɾɾ
     VTFS@JE TFY CJSUI
     " உੑ
     " ঁੑ
     /6-- /6-- /6--
     ɾɾɾ ɾɾɾ ɾɾɾ
     ˓
     ˓
     VTFSTςʔϒϧ
     PSEFS@EBUBςʔϒϧ
     ˓
     

     View Slide

123. 
     +0*/ͷ಺෦݁߹ͱ֎෦݁߹ͷҧ͍Λ֬ೝɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶇɹ
     SELECT
     *
     FROM order_data o
     INNER JOIN users u ON o.user_id = u.user_id
     WHERE o.user_id IS NULL
     SELECT
     *
     FROM order_data o
     LEFT JOIN users u ON o.user_id = u.user_id
     WHERE o.user_id IS NULL
     ֎
     ෦
     ݁
     ߹
     ಺
     ෦
     ݁
     ߹
     

     View Slide

124. 
     +0*/ͷ಺෦݁߹ͱ֎෦݁߹ͷҧ͍Λ֬ೝɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶇɹղઆɹ
     SELECT
     *
     FROM order_data o
     INNER JOIN users u ON o.user_id = u.user_id
     WHERE o.user_id IS NULL
     SELECT
     *
     FROM order_data o
     LEFT JOIN users u ON o.user_id = u.user_id
     WHERE o.user_id IS NULL
     ֎
     ෦
     ݁
     ߹
     ಺
     ෦
     ݁
     ߹
     VTFSTςʔϒϧͷ
     VTFS@JEʹ/6--ͷ৘ใ
     ͕ͳ͍ͷͰ݁߹͞Εͳ͍
     VTFSTςʔϒϧͷ
     VTFS@JEʹ/6--ͷ৘ใ
     ͕ͳ͍ͷ͕/6--ͱͯ͠
     ݁߹͞ΕΔ
     

     View Slide

125. 
     +0*/ͷॱ൪Λม͑ΔͱͲ͏ͳΔ͔ ಺෦݁߹ͷ৔߹
     
     SELECT
     COUNT(*)
     FROM order_data o
     INNER JOIN users u ON o.user_id = u.user_id
     SELECT
     COUNT(*)
     FROM users u
     INNER JOIN order_data o ON o.user_id = u.user_id
     ಺෦݁߹ JOOFSKPJO
     ͷ৔
     ߹͸ͲͪΒͷςʔϒϧʹ
     ΋ڞ௨͢ΔσʔλΛऔಘ
     ͢ΔͷͰ݁Ռͷ݅਺͸ม
     ΘΒͳ͍
     

     View Slide

126. 
     +0*/ͷॱ൪Λม͑ΔͱͲ͏ͳΔ͔ ֎෦݁߹ͷ৔߹
     
     SELECT
     COUNT(*)
     FROM order_data o
     LEFT JOIN users u ON o.user_id = u.user_id
     SELECT
     COUNT(*)
     FROM users u
     LEFT JOIN order_data o ON o.user_id = u.user_id
     ֎෦݁߹ MFGUKPJO
     ͷ৔
     ߹͸ͲͪΒͷςʔϒϧΛ
     ϕʔεʹ͢Δ͔Ͱ݅਺͕
     มΘΔ ৔߹͕͋Δ
     
     

     View Slide

127. 
     ͳͥ֎෦݁߹ͷ৔߹͸+0*/ͷॱ൪Λม͑Δͱ݁Ռ͕มΘΔͷ͔
     SELECT
     COUNT(*)
     FROM order_data o
     LEFT JOIN users u ON o.user_id = u.user_id
     SELECT
     COUNT(*)
     FROM users u
     LEFT JOIN order_data o ON o.user_id = u.user_id
     ߪೖ৘ใͷग़ྗ
     ߪೖ͍ͯ͠ͳ͍Ϣʔβʔ৘
     ใ͸Ͱͳ͍
     
     Ϣʔβʔ৘ใͷग़ྗ
     Ϣʔβʔ৘ใ͕ͳ͍ߪೖ
     σʔλ͸ग़ͳ͍ߪೖ৘ใ
     ͷͳ͍Ϣʔβʔ৘ใ͕ग़Δ
     
     

     View Slide

128. 
     ֎෦݁߹ͷࡍͷ஫ҙ఺
     w ֎෦݁߹ͷ৔߹͸+0*/ͷॱ൪Ͱ݁Ռ͕มΘΔՄೳੑ͕͋ΔͷͰ஫ҙ͕
     ඞཁ
     w ·ͣ͸ԿͷσʔλΛऔΓ͍͔ͨ֬ೝ͠ɺͦͷςʔϒϧΛઌʹऔಘ͢Δ
     w ߪೖσʔλ͕ཉ͚͠Ε͹ߪೖσʔλΛFROMͰઌʹࢦఆɺϢʔβʔσʔ
     λ͕ཉ͚͠Ε͹ϢʔβʔσʔλΛFROMͰઌʹࢦఆɺJOINʹ͸ͦΕҎ
     ֎ͷσʔλΛ෇ਵͤ͞Δ
     w ֎෦݁߹-&'5+0*/ͱ֮͑ͯྑ͍ 3*()5+0*/͸΄΅࢖Θͳ͍
     
     

     View Slide

129. 
     +0*/ɹ໰୊ᶃ
     ҎԼςʔϒϧΛࢀߟʹʮ2020೥1݄1೔ʯͷߪೖσʔλͷ૯ച্ͱ݅਺Λग़
     ͍ͯͩ͘͠͞
     ΧϥϜ໊ ҙຯ ܕ
     QSPEVDU@JE ঎඼*% 5&95
     OBNF ঎඼໊ 5&95
     QSJDF ֹۚ */5&(&3
     DBUFHPSZ ΧςΰϦ 5&95
     DBUFHPSZ ΧςΰϦ 5&95
     ΧϥϜ໊ ҙຯ ܕ
     PSEFS@JE ஫จ*% 5&95
     VTFS@JE Ϣʔβʔ*% 5&95
     PSEFS@QSPEVDU@JE ঎඼*% 5&95
     PSEFS@EBUF ஫จ೔࣌ */5&(&3
     JT@EJTDPVOUFE ׂҾϑϥά */5&(&3
     JT@DBODFMFE Ωϟϯηϧϑϥά */5&(&3
     QSPEVDUTςʔϒϧ PSEFS@EBUBςʔϒϧ
     

     View Slide

130. 
     +0*/ɹղ౴ྫᶃ
     ҎԼςʔϒϧΛࢀߟʹʮ2020೥1݄1೔ʯͷߪೖσʔλͷߪೖֹۚͱ݅਺Λ
     ग़͍ͯͩ͘͠͞
     SELECT
     COUNT(*),
     SUM(p.price)
     FROM order_data o
     LEFT JOIN products p ON o.order_product_id = p.product_id
     WHERE o.order_date = '2020-01-01'
     

     View Slide

131. 
     +0*/ɹղ౴ྫᶃɹղઆ
     ҎԼςʔϒϧΛࢀߟʹʮ2020೥1݄1೔ʯͷߪೖσʔλͷߪೖֹۚͱ݅਺Λ
     ग़͍ͯͩ͘͠͞
     SELECT
     COUNT(*),
     SUM(p.price)
     FROM order_data o
     LEFT JOIN products p ON o.order_product_id = p.product_id
     WHERE o.order_date = '2020-01-01'
     QSPEVDUTͱPSEFS@EBUF
     Λ঎඼*%ΛΩʔʹ݁߹
     ೔෇Λ৚݅ࢦఆ
     ू߹ؔ਺Λ࢖ͬͯച্ͱ݅਺Λऔಘ
     

     View Slide

132. 
     +0*/ɹղ౴ྫᶃɹิ଍
     w ࠓճ͸-&'5+0*/ ֎෦݁߹
     Ͱ΋*//&3+0*/ ಺෦݁߹
     Ͱ΋݁Ռ͸ಉ͡Ͱ͢
     w PSEFS@EBUBʹೖ͍ͬͯΔPSEFS@QSPEVDU@JE΋QSPEVDUTʹೖ͍ͬͯΔQSPEVDU@JE΋Ͳ
     ͪΒ΋ಉ͡΋ͷ͕ೖ͍ͬͯΔͷͰ Ͳ͔ͬͪʹ͔͠ೖͬͯͳ͍*%͕ͳ͍ͷͰ
     ݁Ռ͸ಉ͡
     w ྫ͑͹ɺorder_dataʹ͸͋ͬͯɺproductsʹͳ͍঎඼ID͕ଘࡏͨ࣌͠(ͨ·ͨ·঎඼؅ཧΛ
     ͍ͯ͠ͳ͍σʔλ͕͋ͬͨ࣌)ɺINNER JOINͩͱ঎඼؅ཧ͞Ε͍ͯͳ͍σʔλ͕͸͔͡Εͯ
     ૯ച্΋ͦͷ෼ݮͬͯ͠·͏ͷͰ஫ҙ͕ඞཁ
     w ͜Ε΋঎඼؅ཧ͞Ε͍ͯͳ͍͔Βച্ͱͯ͠΋Χ΢ϯτ͠ͳ͍΄͏͕ྑ͍ͷ͔ɺച্ͱͯ͠
     Χ΢ϯτͨ͠΄͏͕ྑ͍ͷ͔͸࣌ͱ৔߹ʹΑΔͷͰҰ֓ʹͲ͕ͬͪྑ͍ͱ͸ݴ͑ͳ͍
     

     View Slide

133. 
     +0*/ɹ໰୊ᶄ ͪΐͬͱ೉͍͠
     
     ҎԼςʔϒϧΛࢀߟʹuser_id͝ͱͷߪೖ݅਺ͱߪೖֹۚΛग़ͯͩ͘͠͞
     ͍ɻ·ͨ݁Ռ͸ߪೖֹ͕ۚଟ͍ॱʹιʔτͯ͠ग़ྗ͍ͤͯͩ͘͞͞
     ΧϥϜ໊ ҙຯ ܕ
     QSPEVDU@JE ঎඼*% 5&95
     OBNF ঎඼໊ 5&95
     QSJDF ֹۚ */5&(&3
     DBUFHPSZ ΧςΰϦ 5&95
     DBUFHPSZ ΧςΰϦ 5&95
     ΧϥϜ໊ ҙຯ ܕ
     PSEFS@JE ஫จ*% 5&95
     VTFS@JE Ϣʔβʔ*% 5&95
     PSEFS@QSPEVDU@JE ঎඼*% 5&95
     PSEFS@EBUF ஫จ೔࣌ */5&(&3
     JT@EJTDPVOUFE ׂҾϑϥά */5&(&3
     JT@DBODFMFE Ωϟϯηϧϑϥά */5&(&3
     QSPEVDUTςʔϒϧ PSEFS@EBUBςʔϒϧ
     

     View Slide

134. 
     +0*/ɹղ౴ྫᶄ
     ҎԼςʔϒϧΛࢀߟʹuser_id͝ͱͷߪೖ݅਺ͱߪೖֹۚΛग़ͯͩ͘͠͞
     ͍ɻ·ͨ݁Ռ͸ߪೖֹ͕ۚଟ͍ॱʹιʔτͯ͠ग़ྗ͍ͤͯͩ͘͞͞
     SELECT
     o.user_id,
     COUNT(*),
     SUM(p.price) AS total_price
     FROM order_data o
     LEFT JOIN products p ON o.order_product_id = p.product_id
     GROUP BY o.user_id
     ORDER BY total_price DESC
     

     View Slide

135. 
     +0*/ɹղ౴ྫᶄɹղઆ
     ҎԼςʔϒϧΛࢀߟʹuser_id͝ͱͷߪೖ݅਺ͱߪೖֹۚΛग़ͯͩ͘͠͞
     ͍ɻ·ͨ݁Ռ͸ߪೖֹ͕ۚଟ͍ॱʹιʔτͯ͠ग़ྗ͍ͤͯͩ͘͞͞
     SELECT
     o.user_id,
     COUNT(*),
     SUM(p.price) AS total_price
     FROM order_data o
     LEFT JOIN products p ON o.order_product_id = p.product_id
     GROUP BY o.user_id
     ORDER BY total_price DESC
     (3061#:ͰϢʔβʔ͝ͱʹά
     ϧʔϐϯά
     03%&3#:Ͱߪೖֹ͕ۚଟ͍ॱ
     ʹιʔτ
     ू߹ؔ਺Λ࢖ͬͯϢʔβʔ͝ͱʹֹۚͱ݅਺Λऔಘ
     ˞03%&3#:ʹ͸$06/5΍46.ͨ݁͠Ռ΋هೖͰ͖Δ
     

     View Slide

136. 
     +0*/ͷΠϝʔδ
     +0*/͸݁߹ͷΩʔΛܾΊͯςʔϒϧΛ݁߹͢Δ ྻΛ૿΍͢
     Πϝʔδ
     PSEFS@JE VTFS@JE PSEFS@QSPEVDU@JE PSEFS@EBUF
     $ " #
     $ " #
     $ " #
     ɾɾɾ ɾɾɾ ɾɾɾ ɾɾɾ
     PSEFS@JE VTFS@JE PSEFS@QSPEVDU@JE PSEFS@EBUF VTFS@JE TFY CJSUI
     $ " # " உੑ
     $ " # " ঁੑ
     $ " # /6-- /6-- /6--
     VTFS@JE TFY CJSUI
     " உੑ
     " ঁੑ
     /6-- /6-- /6--
     ɾɾɾ ɾɾɾ ɾɾɾ
     

     View Slide

137. 
     42-ߨ࠲
     4&-&$5
     ू߹ؔ਺
     (3061#:
     %*45*/$5
     8)&3&
     #&58&&/
     -*,&
     */
     -*.*5
     03%&3#:
     +0*/
     )"7*/(
     $"4&
     αϒΫΤϦ
     8*5)
     6/*0/ 6/*0/"--
     
     ͦͷଞ5JQT
     

     View Slide

138. 
     )"7*/(
     w ಛఆͷ৚݅Λ͚ͭͯσʔλΛग़ྗ͍ͨ࣌͠ʹ࢖͏ߏจ
     w 8)&3&ͱಉ͡Α͏ͳ࢖͍ํΛ͢Δ
     w 8)&3&ͱͷҧ͍͸࣮ߦ͞ΕΔॱ൪ͷҧ͍
     

     View Slide

139. 
     )"7*/(ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃ ෮श
     
     SELECT
     category1,
     COUNT(*)
     FROM products
     GROUP BY category1
     DBUFHPSZ͝ͱͷ঎඼݅਺Λऔಘ
     ݅਺͕݅Ҏ্ͷΧςΰϦΛந
     ग़͍ͨ͠৔߹͸ʁ
     

     View Slide

140. 
     )"7*/(ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄ
     SELECT
     category1,
     COUNT(*)
     FROM products
     WHERE COUNT(*) >= 5
     GROUP BY category1
     ݅਺͕݅Ҏ্ͷDBUFHPSZΛऔಘ
     SELECT
     category1,
     COUNT(*)
     FROM products
     GROUP BY category1
     DBUFHPSZ͝ͱͷ঎඼݅਺Λऔಘ
     

     View Slide

141. 
     )"7*/(ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄɹղઆ
     SELECT
     category1,
     COUNT(*)
     FROM products
     WHERE COUNT(*) >= 5
     GROUP BY category1
     ݅਺͕݅Ҏ্ͷDBUFHPSZΛऔಘ
     w 8)&3&Ͱू߹ؔ਺ͷ৚݅Λࢦఆ͢ΔͱΤϥʔʹͳΔ
     w 42-ʹ͸࣮ߦ͞ΕΔॱ൪͕͋Δ
     º
     SELECT
     category1,
     COUNT(*)
     FROM products
     GROUP BY category1
     DBUFHPSZ͝ͱͷ঎඼݅਺Λऔಘ
     

     View Slide

142. 
     42-ͷ࣮ߦ͞ΕΔॱ൪
     '30.
     +0*/
     8)&3&
     (3061#:
     )"7*/(
     4&-&$5
     03%&3#:
     -*.*5
     ᶃ
     ᶄ
     ᶅ
     ᶆ
     ᶇ
     ᶈ
     ᶉ
     ᶊ
     ɾ8)&3&۟Ͱ(3061#:ͨ݁͠ՌΛऔಘ͠Α͏ͱ͍ͯ͠Δ͕ɺ
     (3061#:ΑΓઌʹ8)&3&ͷ৚͕࣮݅ߦ͞Εͯ͠·͏
     ɾͭ·Γ(3061#:ͷ݁ՌΛ8)&3&Ͱࢦఆ͢Δ͜ͱ͸Ͱ͖ͳ͍
     ɾGROUP BYͷޙʹ࣮ߦ͞ΕΔHAVINGΛ࢖͏͜ͱͰ࣮ݱՄೳ
     SELECT
     category1,
     COUNT(*)
     FROM products
     WHERE COUNT(*) >= 5
     GROUP BY category1
     º
     

     View Slide

143. SELECT
     category1,
     COUNT(*)
     FROM products
     GROUP BY category1
     HAVING COUNT(*) >= 5
     
     )"7*/(ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶅ
     '30.
     +0*/
     8)&3&
     (3061#:
     )"7*/(
     4&-&$5
     03%&3#:
     -*.*5
     ᶃ
     ᶄ
     ᶅ
     ᶆ
     ᶇ
     ᶈ
     ᶉ
     ᶊ
     

     View Slide

144. 
     )"7*/(ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶅɹղઆ
     '30.
     +0*/
     8)&3&
     (3061#:
     )"7*/(
     4&-&$5
     03%&3#:
     -*.*5
     ᶃ
     ᶄ
     ᶅ
     ᶆ
     ᶇ
     ᶈ
     ᶉ
     ᶊ
     w 8)&3&$06/5
     Ͱ͸ͳ͘)"7*/($06/5
     ʹ
     ͢Δ
     w )"7*/(͸(3061#:ͷޙʹ࣮ߦ͞ΕΔͷͰ(3061#:ͷޙʹ
     ॻ͘ ˞8)&3&͸(3061#:ͷલʹॻ͘
     
     w 03%&3#:ʹ$06/5
     ͕࢖͑Δͷ΋42-ͷ࣮ߦॱ൪తʹ໰୊ͳ
     ͍͔Β
     SELECT
     category1,
     COUNT(*)
     FROM products
     GROUP BY category1
     HAVING COUNT(*) >= 5
     

     View Slide

145. 
     )"7*/(
     w 42-ʹ͸࣮ߦ͞ΕΔॱ൪͕͋Δ
     w (3061#:ͨ݁͠Ռʹରͯ͠৚݅Λ௥Ճ͍ͨ͠ͱ͖
     w ˠ)"7*/(
     w ͦΕҎ֎Ͱ৚݅Λࢦఆ͍ͨ͠ͱ͖(௨ৗΑ͘࢖͏৚݅ࢦఆ
     
     w ˠ8)&3&
     

     View Slide

146. 
     )"7*/(ɹ໰୊ᶃ
     ҎԼςʔϒϧΛࢀߟʹ೔͝ͱͷച্͕ ԁҎ্ͷσʔλΛநग़͠
     ͍ͯͩ͘͞
     ΧϥϜ໊ ҙຯ ܕ
     QSPEVDU@JE ঎඼*% 5&95
     OBNF ঎඼໊ 5&95
     QSJDF ֹۚ */5&(&3
     DBUFHPSZ ΧςΰϦ 5&95
     DBUFHPSZ ΧςΰϦ 5&95
     ΧϥϜ໊ ҙຯ ܕ
     PSEFS@JE ஫จ*% 5&95
     VTFS@JE Ϣʔβʔ*% 5&95
     PSEFS@QSPEVDU@JE ঎඼*% 5&95
     PSEFS@EBUF ஫จ೔࣌ */5&(&3
     JT@EJTDPVOUFE ׂҾϑϥά */5&(&3
     JT@DBODFMFE Ωϟϯηϧϑϥά */5&(&3
     QSPEVDUTςʔϒϧ PSEFS@EBUBςʔϒϧ
     

     View Slide

147. 
     )"7*/(ɹղ౴ྫᶃ
     ҎԼςʔϒϧΛࢀߟʹ೔͝ͱͷച্͕ ԁҎ্ͷσʔλΛநग़͠
     ͍ͯͩ͘͞
     SELECT
     o.order_date,
     COUNT(*),
     SUM(p.price)
     FROM order_data o
     LEFT JOIN products p ON o.order_product_id = p.product_id
     GROUP BY o.order_date
     HAVING SUM(p.price) > 3000000
     

     View Slide

148. 
     )"7*/(ɹղ౴ྫᶃɹղઆ
     ҎԼςʔϒϧΛࢀߟʹ೔͝ͱͷച্͕ ԁҎ্ͷσʔλΛநग़͠
     ͍ͯͩ͘͞
     SELECT
     o.order_date,
     COUNT(*),
     SUM(p.price)
     FROM order_data o
     LEFT JOIN products p ON o.order_product_id = p.product_id
     GROUP BY o.order_date
     HAVING SUM(p.price) > 3000000
     ू߹ؔ਺Λ࢖ͬͯ೔͝ͱʹֹۚͱ݅਺Λऔಘ
     )"7*/(Ͱച্ͷ৚݅Λࢦఆ ঎඼৘ใͱߪೖ৘ใΛ+0*/
     

     View Slide

149. 
     )"7*/(ͷΠϝʔδ
     )"7*/(͸৚݅ʹҰகͨ͠ϨίʔυΛநग़͢ΔΠϝʔδ
     8)&3&ͱಉ͡Πϝʔδ
     
     PSEFS@JE VTFS@JE PSEFS@QSPEVDU@JE PSEFS@EBUF
     $ " #
     $ " #
     $ " #
     ɾɾɾ ɾɾɾ ɾɾɾ ɾɾɾ
     PSEFS@JE VTFS@JE PSEFS@QSPEVDU@JE PSEFS@EBUF
     $ " #
     $ " #
     

     View Slide

150. 
     42-ߨ࠲
     4&-&$5
     ू߹ؔ਺
     (3061#:
     %*45*/$5
     8)&3&
     #&58&&/
     -*,&
     */
     -*.*5
     03%&3#:
     +0*/
     )"7*/(
     $"4&
     αϒΫΤϦ
     8*5)
     6/*0/ 6/*0/"--
     
     ͦͷଞ5JQT
     

     View Slide

151. 
     $"4&
     w SQLͰ৚݅෼ذΛॻ͘͜ͱ͕Ͱ͖Δߏจ
     w ʓʓͩͬͨΒ˚˚ɺ××ͩͬͨΒ□□(ϓϩάϥϛϯάͰ͍͏if
     จΈ͍ͨͳ΋ͷ)
     CASE
     WHEN ৚݅1 THEN ݁Ռ1
     WHEN ৚݅2 THEN ݁Ռ2
     (ELSE ্هҎ֎ͷ৚݅ʹ֘౰ͨ͠৔߹ͷ݁Ռ)
     END
     $"4&ࣜͷߏจ
     

     View Slide

152. 
     $"4&ࣜͷ۩ମྫ
     ߪೖֹۚͰϔϏʔɺϛυϧɺϥΠτͷଐੑʹ෼ྨ͍ͨ͠
     ߪೖֹۚ
     =
     =
     =
     ϔϏʔ
     ϛυϧ
     ϥΠτ
     ೥ؒߪೖֹ͕ۚ= Ҏ্
     ೥ؒߪೖֹ͕ۚ= ʙ= Ҏ্
     ೥ؒߪೖֹ͕ۚ= ҎԼ
     

     View Slide

153. 
     $"4&ࣜͷ۩ମྫɹ42-Λ࣮ߦͯ͠ΈΑ͏ᶃ ෮श
     
     SELECT
     o.user_id,
     COUNT(*) AS total_count,
     SUM(p.price) AS total_price
     FROM order_data o
     LEFT JOIN products p ON o.order_product_id = p.product_id
     WHERE o.user_id IS NOT NULL
     GROUP BY o.user_id
     ·ͣ͸Ϣʔβʔ͝ͱͷߪೖֹۚΛग़͢
     

     View Slide

154. 
     $"4&ࣜͷ۩ମྫɹ42-Λ࣮ߦͯ͠ΈΑ͏ᶃ ෮श
     ɹղઆ
     SELECT
     o.user_id,
     COUNT(*) AS total_count,
     SUM(p.price) AS total_price
     FROM order_data o
     LEFT JOIN products p ON o.order_product_id = p.product_id
     WHERE o.user_id IS NOT NULL
     GROUP BY o.user_id
     ·ͣ͸Ϣʔβʔ͝ͱͷߪೖֹۚΛग़͢
     ू߹ؔ਺Λ࢖ͬͯϢʔβʔ͝ͱʹֹۚͱ݅਺Λऔಘ
     ঎඼৘ใͱߪೖ৘ใΛ+0*/
     Ϣʔβʔ*%͕ۭͷσʔλ͸আ֎
     

     View Slide

155. SELECT
     o.user_id,
     COUNT(*) AS total_count,
     SUM(p.price) AS total_price,
     CASE
     WHEN SUM(p.price) >= 1000000 THEN 'ϔϏʔ'
     WHEN SUM(p.price) < 1000000 AND SUM(p.price) >= 200000 THEN 'ϛυϧ'
     WHEN SUM(p.price) < 200000 THEN 'ϥΠτ'
     ELSE 'ͦͷଞ'
     END AS hml
     FROM order_data o
     LEFT JOIN products p ON o.order_product_id = p.product_id
     WHERE o.user_id <> 'NULL'
     GROUP BY o.user_id
     
     $"4&ࣜͷ۩ମྫɹ42-Λ࣮ߦͯ͠ΈΑ͏ᶄ
     

     View Slide

156. SELECT
     o.user_id,
     COUNT(*) AS total_count,
     SUM(p.price) AS total_price,
     CASE
     WHEN SUM(p.price) >= 1000000 THEN 'ϔϏʔ'
     WHEN SUM(p.price) < 1000000 AND SUM(p.price) >= 200000 THEN 'ϛυϧ'
     WHEN SUM(p.price) < 200000 THEN 'ϥΠτ'
     ELSE 'ͦͷଞ'
     END AS hml
     FROM order_data o
     LEFT JOIN products p ON o.order_product_id = p.product_id
     WHERE o.user_id <> 'NULL'
     GROUP BY o.user_id
     
     $"4&ࣜͷ۩ମྫɹ42-Λ࣮ߦͯ͠ΈΑ͏ᶄɹղઆ
     $"4&ࣜΛ࢖ͬͯߪೖֹۚ͝ͱʹϑϥά͚ͭ
     

     View Slide

157. 
     SELECT
     o.user_id,
     COUNT(*) AS total_count,
     SUM(p.price) AS total_price,
     CASE
     WHEN SUM(p.price) >= 1000000 THEN 'ϔϏʔ'
     WHEN SUM(p.price) < 1000000 AND SUM(p.price) >= 200000 THEN 'ϛυϧ'
     WHEN SUM(p.price) < 200000 THEN 'ϥΠτ'
     ELSE 'ͦͷଞ'
     END AS hml
     FROM order_data o
     LEFT JOIN products p ON o.order_product_id = p.product_id
     WHERE o.user_id <> 'NULL'
     GROUP BY o.user_id
     ߪೖֹۚ 46. QQSJDF
     
     ͕ Ҏ্ͳΒϔϏʔ
     8)&/5)&/Ͱ৚݅Λෳ਺هड़Մೳ
     ৚݅ʹ֘౰͠ͳ͍৔߹͸ʮͦͷଞʯ
     ϑϥά෇͚ͨ͠΋ͷΛ"4Ͱผ໊Λ͚ͭΔ
     $"4&ࣜͷ۩ମྫɹ42-Λ࣮ߦͯ͠ΈΑ͏ᶄɹղઆ
     

     View Slide

158. 
     $"4&ࣜͷ۩ମྫɹղઆ
     CASE
     WHEN SUM(p.price) >= 1000000 THEN 'ϔϏʔ'
     WHEN SUM(p.price) < 1000000 AND SUM(p.price) >= 200000 THEN 'ϛυϧ'
     WHEN SUM(p.price) < 200000 THEN 'ϥΠτ'
     ELSE 'ͦͷଞ'
     END AS hml
     ෳ਺৚݅Λॻ͘৔߹ɺ৚݅͸ॻ͍ͨॱ͔Β࣮ߦ͞Εͯɺ৚݅ʹҰகͨ͠
     ৔߹͸ͦͷޙͷ৚݅͸εΩοϓ͞ΕΔ
     CASE
     WHEN SUM(p.price) >= 1000000 THEN 'ϔϏʔ'
     WHEN SUM(p.price) >= 200000 THEN 'ϛυϧ'
     WHEN SUM(p.price) < 200000 THEN 'ϥΠτ'
     ELSE 'ͦͷଞ'
     END AS hml
     ্͔Βॱ൪ʹධՁ͞ΕΔͷͰ
     46. QQSJDF
     ͸ෆཁ
     

     View Slide

159. 
     $"4&ࣜͷ஫ҙ఺
     w $"4&ࣜͷ৚݅͸্͔Βॱ൪ʹධՁ͞ΕΔͷ
     w ৚݅ͷॱ൪Λม͑Δͱҙਤ͠ͳ͍݁ՌʹͳΔՄೳੑ͕͋ΔͷͰ஫ҙ
     w ΏΔ͍৚݅Λઌʹॻ͍ͯ͠·͏ͱޙ͔Β͖͍ͭ৚݅ʹ֘౰͠ͳ͘ͳͬ
     ͯ͠·͏ͷͰ஫ҙ
     CASE
     WHEN SUM(p.price) >= 200000 THEN 'ϛυϧ'
     WHEN SUM(p.price) >= 1000000 THEN 'ϔϏʔ'
     WHEN SUM(p.price) < 200000 THEN 'ϥΠτ'
     ELSE 'ͦͷଞ'
     END AS hml
     º
     ˞৚݅ͷॱ൪Λม͑ΔͱϔϏʔ͕֘౰ͳ͠ʹͳͬͯ͠·͏
     

     View Slide

160. 
     $"4&ɹ໰୊ᶃ
     ҎԼ42-ΛࢀߟʹϢʔβʔ͝ͱͷߪೖ݅਺͕݅Ҏ্ΛϩΠϠϧɺ
     ݅Ҏ্݅ະຬΛϔϏʔɺ݅Ҏ্݅ະຬΛϛυϧɺ݅ະຬ
     ΛϥΠτɺͦΕҎ֎Λͦͷଞͱ෼ྨ͍ͯͩ͘͠͞
     SELECT
     o.user_id,
     COUNT(*) AS total_count,
     SUM(p.price) AS total_price,
     CASE
     WHEN SUM(p.price) >= 1000000 THEN 'ϔϏʔ'
     WHEN SUM(p.price) < 1000000 AND SUM(p.price) >= 200000 THEN 'ϛυϧ'
     WHEN SUM(p.price) < 200000 THEN 'ϥΠτ'
     ELSE 'ͦͷଞ'
     END AS hml
     FROM order_data o
     LEFT JOIN products p ON o.order_product_id = p.product_id
     WHERE o.user_id <> 'NULL'
     GROUP BY o.user_id
     

     View Slide

161. 
     $"4&ɹղ౴ྫᶃ
     ҎԼ42-ΛࢀߟʹϢʔβʔ͝ͱͷߪೖ݅਺͕݅Ҏ্ΛϩΠϠϧɺ
     ݅Ҏ্݅ະຬΛϔϏʔɺ݅Ҏ্݅ະຬΛϛυϧɺ݅ະຬ
     ΛϥΠτɺͦΕҎ֎Λͦͷଞͱ෼ྨ͍ͯͩ͘͠͞
     SELECT
     o.user_id,
     COUNT(*) AS total_count,
     SUM(p.price) AS total_price,
     CASE
     WHEN COUNT(*) >= 500 THEN 'ϩΠϠϧ'
     WHEN COUNT(*) >= 300 THEN 'ϔϏʔ'
     WHEN COUNT(*) >= 100 THEN 'ϛυϧ'
     WHEN COUNT(*) < 100 THEN 'ϥΠτ'
     ELSE 'ͦͷଞ'
     END AS hml
     FROM order_data o
     LEFT JOIN products p ON o.order_product_id = p.product_id
     WHERE o.user_id <> 'NULL'
     GROUP BY o.user_id
     

     View Slide

162. 
     $"4&ɹղ౴ྫᶃɹղઆ
     ҎԼ42-ΛࢀߟʹϢʔβʔ͝ͱͷߪೖ݅਺͕݅Ҏ্ΛϩΠϠϧɺ
     ݅Ҏ্݅ະຬΛϔϏʔɺ݅Ҏ্݅ະຬΛϛυϧɺ݅ະຬ
     ΛϥΠτɺͦΕҎ֎Λͦͷଞͱ෼ྨ͍ͯͩ͘͠͞
     SELECT
     o.user_id,
     COUNT(*) AS total_count,
     SUM(p.price) AS total_price,
     CASE
     WHEN COUNT(*) >= 500 THEN 'ϩΠϠϧ'
     WHEN COUNT(*) >= 300 THEN 'ϔϏʔ'
     WHEN COUNT(*) >= 100 THEN 'ϛυϧ'
     WHEN COUNT(*) < 100 THEN 'ϥΠτ'
     ELSE 'ͦͷଞ'
     END AS hml
     FROM order_data o
     LEFT JOIN products p ON o.order_product_id = p.product_id
     WHERE o.user_id <> 'NULL'
     GROUP BY o.user_id
     ৚݅Λߪೖ݅਺ $06/5
     
     ʹͯ͠ɺ
     ύλʔϯʹ෼ྨ
     

     View Slide

163. 
     $"4&ͷΠϝʔδ
     $"4&͸৽͘͠ྻΛ௥Ճ͢Δͱ͖ʹ࢖͏Πϝʔδ
     ˞4&-&$5ͷதͰ$"4&ࣜΛ࢖͏৔߹
     
     PSEFS@JE VTFS@JE PSEFS@QSPEVDU@JE PSEFS@EBUF
     $ " #
     $ " #
     $ " #
     ɾɾɾ ɾɾɾ ɾɾɾ ɾɾɾ
     PSEFS@JE VTFS@JE PSEFS@QSPEVDU@JE PSEFS@EBUF INM
     $ " # ϔϏʔ
     $ " # ϛυϧ
     $ " # ϥΠτ
     ɾɾɾ ɾɾɾ ɾɾɾ ɾɾɾ ɾɾɾ
     

     View Slide

164. 
     42-ߨ࠲
     4&-&$5
     ू߹ؔ਺
     (3061#:
     %*45*/$5
     8)&3&
     #&58&&/
     -*,&
     */
     -*.*5
     03%&3#:
     +0*/
     )"7*/(
     $"4&
     αϒΫΤϦ
     8*5)
     6/*0/ 6/*0/"--
     
     ͦͷଞ5JQT
     

     View Slide

165. 
     αϒΫΤϦͱ͸
     w 42-ͷதʹ͞Βʹ42-Λॻ͘͜ͱ͕Ͱ͖Δػೳ
     

     View Slide

166. 
     αϒΫΤϦɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃ ෮श
     
     Ϣʔβʔͷੑผ͕ʮஉੑʯ͚ͩͷ஫จ*%Λऔಘ͍ͨ͠
     SELECT
     o.order_id,
     u.sex
     FROM order_data o
     LEFT JOIN users u ON o.user_id = u.user_id
     WHERE u.sex = 'உੑ'
     

     View Slide

167. 
     Ϣʔβʔͷੑผ͕ʮஉੑʯ͚ͩͷ஫จ*%Λऔಘ͍ͨ͠
     SELECT
     o.order_id,
     u.sex
     FROM order_data o
     LEFT JOIN users u ON o.user_id = u.user_id
     WHERE u.sex = 'உੑ'
     +0*/Λ࢖ͬͯVTFSTͱPSEFS@EBUBΛ݁߹ͤͯ͞ɺVTFSTͷੑผΛ8)&3&
     ͷ৚݅Ͱࢦఆ
     αϒΫΤϦɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃ ෮श
     ɹղઆ
     

     View Slide

168. 
     Ϣʔβʔͷੑผ͕ʮஉੑʯ͚ͩͷ஫จ*%Λऔಘ͍ͨ͠
     SELECT
     order_id
     FROM order_data
     WHERE user_id IN (SELECT DISTINCT user_id FROM users WHERE sex = 'உੑ')
     αϒΫΤϦɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄ
     

     View Slide

169. 
     Ϣʔβʔͷੑผ͕ʮஉੑʯ͚ͩͷ஫จ*%Λऔಘ͍ͨ͠
     SELECT
     order_id
     FROM order_data
     WHERE user_id IN (SELECT DISTINCT user_id FROM users WHERE sex = 'உੑ')
     8)&3&ͷ৚݅ʹ4&-&$5จΛ࢖͏ࣄ͕Ͱ͖Δ
     ˞+0*/Λ࢖͏ͱݩͷςʔϒϧͷϨίʔυ਺͕૿ݮ͢ΔՄೳੑ͕͋ΔͷͰɺݩͷςʔϒϧͷϨίʔυ਺Λ୲อͭͭ͠৚݅ࢦఆ
     ͍ͨ͠৔߹ʹ͸ศར
     αϒΫΤϦɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄɹղઆ
     

     View Slide

170. 
     ߪೖֹۚͰϔϏʔɺϛυϧɺϥΠτΛ෼͚ͨͱ͖ʹɺϔϏʔɺϛυϧɺϥΠ
     τͷϢʔβʔ͕ͦΕͧΕԿਓ͍Δͷ͔ूܭ͍ͨ͠
     SELECT
     o.user_id,
     COUNT(*) AS total_count,
     SUM(p.price) AS total_price,
     CASE
     WHEN SUM(p.price) >= 1000000 THEN 'ϔϏʔ'
     WHEN SUM(p.price) >= 200000 THEN 'ϛυϧ'
     WHEN SUM(p.price) < 200000 THEN 'ϥΠτ'
     ELSE 'ͦͷଞ'
     END AS hml
     FROM order_data o
     LEFT JOIN products p ON o.order_product_id = p.product_id
     WHERE o.user_id <> 'NULL'
     GROUP BY o.user_id
     $"4&ࣜΛ࢖ͬͯϔ
     ϏʔɺϛυϧɺϥΠ
     τͷϑϥά෇͚
     ͜ͷ݁Ռʹରͯ͠͞
     Βʹ42-Λॻ͘ࣄ͕
     Ͱ͖Δ
     αϒΫΤϦɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶅ ෮श
     
     

     View Slide

171. 
     SELECT
     hml,
     COUNT(user_id)
     FROM
     (
     SELECT
     o.user_id,
     COUNT(*) AS total_count,
     SUM(p.price) AS total_price,
     CASE
     WHEN SUM(p.price) >= 1000000 THEN 'ϔϏʔ'
     WHEN SUM(p.price) >= 200000 THEN 'ϛυϧ'
     WHEN SUM(p.price) < 200000 THEN 'ϥΠτ'
     ELSE 'ͦͷଞ'
     END AS hml
     FROM order_data o
     LEFT JOIN products p ON o.order_product_id = p.product_id
     WHERE o.user_id <> 'NULL'
     GROUP BY o.user_id
     )
     GROUP BY hml
     ߪೖֹۚͰϔϏʔɺϛυϧɺϥΠτΛ෼͚ͨͱ͖ʹɺϔϏʔɺϛυϧɺϥΠ
     τͷϢʔβʔ͕ͦΕͧΕԿਓ͍Δͷ͔ूܭ͍ͨ͠
     αϒΫΤϦɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶆ
     

     View Slide

172. 
     SELECT
     hml,
     COUNT(user_id)
     FROM
     (
     SELECT
     o.user_id,
     COUNT(*) AS total_count,
     SUM(p.price) AS total_price,
     CASE
     WHEN SUM(p.price) >= 1000000 THEN 'ϔϏʔ'
     WHEN SUM(p.price) >= 200000 THEN 'ϛυϧ'
     WHEN SUM(p.price) < 200000 THEN 'ϥΠτ'
     ELSE 'ͦͷଞ'
     END AS hml
     FROM order_data o
     LEFT JOIN products p ON o.order_product_id = p.product_id
     WHERE o.user_id <> 'NULL'
     GROUP BY o.user_id
     )
     GROUP BY hml
     4&-&$5จͷ݁Ռ
     Λ'30.Ͱࢦఆ͠
     ͯɺ4&-&$5ͨ͠
     ݁Ռʹରͯ͠42-
     Λ͔͚Δ
     ߪೖֹۚͰϔϏʔɺϛυϧɺϥΠτΛ෼͚ͨͱ͖ʹɺϔϏʔɺϛυϧɺϥΠ
     τͷϢʔβʔ͕ͦΕͧΕԿਓ͍Δͷ͔ूܭ͍ͨ͠
     αϒΫΤϦɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶆɹղઆ
     

     View Slide

173. 
     ͜ͷͭͷ42-Λ߹ମͤͨ͞Πϝʔδ
     SELECT
     o.user_id,
     COUNT(*) AS total_count,
     SUM(p.price) AS total_price,
     CASE
     WHEN SUM(p.price) >= 1000000 THEN 'ϔϏʔ'
     WHEN SUM(p.price) >= 200000 THEN 'ϛυϧ'
     WHEN SUM(p.price) < 200000 THEN 'ϥΠτ'
     ELSE 'ͦͷଞ'
     END AS hml
     FROM order_data o
     LEFT JOIN products p ON o.order_product_id = p.product_id
     WHERE o.user_id <> 'NULL'
     GROUP BY o.user_id
     $"4&ࣜΛ࢖ͬͯϑϥά෇͚
     +
     SELECT
     hml,
     COUNT(user_id)
     FROM αϒΫΤϦ
     GROUP BY hml
     αϒΫΤϦΛ࢖ͬͯूܭ
     αϒΫΤϦɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶆɹղઆ
     

     View Slide

174. 
     αϒΫΤϦͱ͸
     w 42-ͷதʹ͞Βʹ42-Λॻ͘͜ͱ͕Ͱ͖Δػೳ
     w '30.ͷத΍8)&3&ͷ৚݅ͱͯ͠࢖͏ࣄ͕ଟ͍
     

     View Slide

175. 
     αϒΫΤϦɹ໰୊ᶃ
     ҎԼSQLΛࢀߟʹߪೖ݅਺ͰHML෼ྨͨ݁͠ՌͰɺHML͝ͱͷϢʔβʔ
     ਺ɺߪೖ݅਺ɺߪೖֹۚΛग़͍ͯͩ͘͠͞
     SELECT
     o.user_id,
     COUNT(*) AS total_count,
     SUM(p.price) AS total_price,
     CASE
     WHEN SUM(p.price) >= 1000000 THEN 'ϔϏʔ'
     WHEN SUM(p.price) >= 200000 THEN 'ϛυϧ'
     WHEN SUM(p.price) < 200000 THEN 'ϥΠτ'
     ELSE 'ͦͷଞ'
     END AS hml
     FROM order_data o
     LEFT JOIN products p ON o.order_product_id = p.product_id
     WHERE o.user_id <> 'NULL'
     GROUP BY o.user_id
     $"4&ࣜΛ࢖ͬͯ).-ʹ෼ྨ
     

     View Slide

176. 
     αϒΫΤϦɹղ౴ྫᶃ
     ҎԼSQLΛࢀߟʹߪೖ݅਺ͰHML෼ྨͨ݁͠ՌͰɺHML͝ͱͷϢʔβʔ
     ਺ɺߪೖ݅਺ɺߪೖֹۚΛग़͍ͯͩ͘͠͞
     SELECT
     hml,
     COUNT(user_id),
     SUM(total_count),
     SUM(total_price)
     FROM
     (
     SELECT
     o.user_id,
     COUNT(*) AS total_count,
     SUM(p.price) AS total_price,
     CASE
     WHEN SUM(p.price) >= 1000000 THEN 'ϔϏʔ'
     WHEN SUM(p.price) >= 200000 THEN 'ϛυϧ'
     WHEN SUM(p.price) < 200000 THEN 'ϥΠτ'
     ELSE 'ͦͷଞ'
     END AS hml
     FROM order_data o
     LEFT JOIN products p ON o.order_product_id = p.product_id
     WHERE o.user_id <> 'NULL'
     GROUP BY o.user_id
     )
     GROUP BY hml
     

     View Slide

177. 
     αϒΫΤϦɹղ౴ྫᶃɹղઆ
     ҎԼSQLΛࢀߟʹߪೖ݅਺ͰHML෼ྨͨ݁͠ՌͰɺHML͝ͱͷϢʔβʔ
     ਺ɺߪೖ݅਺ɺߪೖֹۚΛग़͍ͯͩ͘͠͞
     SELECT
     hml,
     COUNT(user_id),
     SUM(total_count),
     SUM(total_price)
     FROM
     (
     SELECT
     o.user_id,
     COUNT(*) AS total_count,
     SUM(p.price) AS total_price,
     CASE
     WHEN SUM(p.price) >= 1000000 THEN 'ϔϏʔ'
     WHEN SUM(p.price) >= 200000 THEN 'ϛυϧ'
     WHEN SUM(p.price) < 200000 THEN 'ϥΠτ'
     ELSE 'ͦͷଞ'
     END AS hml
     FROM order_data o
     LEFT JOIN products p ON o.order_product_id = p.product_id
     WHERE o.user_id <> 'NULL'
     GROUP BY o.user_id
     )
     GROUP BY hml
     UPUBM@DPVOU ߪೖ݅਺
     ͱ
     UPUBM@QSJDF ߪೖֹۚ
     Λ46.Ͱ߹ܭ
     ※FROMͷதͰαϒΫΤϦΛ࢖͏৔߹
     ͸SELECTͨ݁͠Ռʹରͯ͠ASͰ໊લ
     Λ͚ͭΔඞཁ͕͋Γ·͢
     

     View Slide

178. 
     42-ߨ࠲
     4&-&$5
     ू߹ؔ਺
     (3061#:
     %*45*/$5
     8)&3&
     #&58&&/
     -*,&
     */
     -*.*5
     03%&3#:
     +0*/
     )"7*/(
     $"4&
     αϒΫΤϦ
     8*5)
     6/*0/ 6/*0/"--
     
     ͦͷଞ5JQT
     

     View Slide

179. 
     8*5)۟ͱ͸
     w 42-্ͰҰ࣍ςʔϒϧΛ࡞੒Ͱ͖Δॻ͖ํ
     w '30.ͷதͰαϒΫΤϦΛ࢖͏৔߹ʹ8*5)۟Λ࢖͏ͱՄ
     ಡੑ্͕͕Δ
     

     View Slide

180. 
     8*5)۟ͷ׆༻ྫ
     SELECT
     hml,
     COUNT(user_id)
     FROM
     (
     SELECT
     o.user_id,
     COUNT(*) AS total_count,
     SUM(p.price) AS total_price,
     CASE
     WHEN SUM(p.price) >= 1000000 THEN 'ϔϏʔ'
     WHEN SUM(p.price) >= 200000 THEN 'ϛυϧ'
     WHEN SUM(p.price) < 200000 THEN 'ϥΠτ'
     ELSE 'ͦͷଞ'
     END AS hml
     FROM order_data o
     LEFT JOIN products p ON o.order_product_id = p.product_id
     WHERE o.user_id <> 'NULL'
     GROUP BY o.user_id
     )
     GROUP BY hml
     ϔϏʔɺϛυϧɺϥΠτͷϢʔβʔ਺ूܭ
     ؒҧ͍Ͱ͸ͳ͍͚
     Ͳ42-͕ݟͮΒ͍
     

     View Slide

181. 
     8*5)۟ͷ׆༻ྫɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃ
     WITH hml_data AS(
     SELECT
     o.user_id,
     COUNT(*) AS total_count,
     SUM(p.price) AS total_price,
     CASE
     WHEN SUM(p.price) >= 1000000 THEN 'ϔϏʔ'
     WHEN SUM(p.price) >= 200000 THEN 'ϛυϧ'
     WHEN SUM(p.price) < 200000 THEN 'ϥΠτ'
     ELSE 'ͦͷଞ'
     END AS hml
     FROM order_data o
     LEFT JOIN products p ON o.order_product_id = p.product_id
     WHERE o.user_id <> 'NULL'
     GROUP BY o.user_id
     )
     SELECT
     hml,
     COUNT(user_id)
     FROM hml_data
     GROUP BY hml
     ϔϏʔɺϛυϧɺϥΠτͷϢʔβʔ਺ूܭ
     

     View Slide

182. 
     WITH hml_data AS(
     SELECT
     o.user_id,
     COUNT(*) AS total_count,
     SUM(p.price) AS total_price,
     CASE
     WHEN SUM(p.price) >= 1000000 THEN 'ϔϏʔ'
     WHEN SUM(p.price) >= 200000 THEN 'ϛυϧ'
     WHEN SUM(p.price) < 200000 THEN 'ϥΠτ'
     ELSE 'ͦͷଞ'
     END AS hml
     FROM order_data o
     LEFT JOIN products p ON o.order_product_id = p.product_id
     WHERE o.user_id <> 'NULL'
     GROUP BY o.user_id
     )
     SELECT
     hml,
     COUNT(user_id)
     FROM hml_data
     GROUP BY hml
     ϔϏʔɺϛυϧɺϥΠτͷϢʔβʔ਺ूܭ
     8*5)۟Λ࢖ͬͯҰ࣍
     ςʔϒϧΛ࡞੒
     ࡞੒ͨ͠ςʔϒϧΛ
     42-ͷதͰ࢖͑Δ
     8*5)۟ͷ׆༻ྫɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃɹղઆ
     

     View Slide

183. 
     8*5)۟ͷߏจ
     WITH ςʔϒϧ໊1 AS(
     ),
     ςʔϒϧ໊2 AS(
     )
     SELECT
     *
     FROM ςʔϒϧ໊1
     w ΧϯϚ۠੾ΓͰෳ਺ͷҰ࣍ςʔϒϧ
     Λ࡞Δࣄ͕Ͱ͖Δ
     w 42-্ͰҰ࣍ςʔϒϧΛ࡞Δࣄ͕Ͱ
     ͖Δ ࣮ࡍʹςʔϒϧ͕࡞੒͞ΕΔ
     Θ͚Ͱ͸ͳ͍ͷͰ42-ͷதͰ͔͠࢖
     ͑ͳ͍
     
     w 8*5)۟Λ࢖͏ͱෳࡶͳΫΤϦ΋Մ
     ಡੑΛߴΊΔࣄ͕Ͱ͖Δ
     αϒΫΤϦΛ࢖͏৔߹ʹ͸8*5)۟Λ࢖ͬͨํ͕Մಡੑ͕ߴ·
     ΔͷͰੵۃతʹ8*5)۟Λ࢖͏͜ͱΛ͓קΊ͠·͢
     

     View Slide

184. 
     8*5)۟ɹ໰୊ᶃ
     ҎԼSQLΛ8*5)۟Λ࢖ͬͯॻ͖௚ͯ͠Έ͍ͯͩ͘͞ ϦϑΝΫλϦϯά
     
     SELECT
     order_id
     FROM order_data
     WHERE user_id IN (SELECT DISTINCT user_id FROM users WHERE sex = 'உੑ')
     ͜͜Λ8*5)۟Λ࢖ͬͯॻ͖௚͠
     

     View Slide

185. 
     8*5)۟ɹղ౴ྫᶃ
     ҎԼSQLΛ8*5)۟Λ࢖ͬͯॻ͖௚ͯ͠Έ͍ͯͩ͘͞ ϦϑΝΫλϦϯά
     
     WITH user_man AS(
     SELECT
     DISTINCT user_id
     FROM users
     WHERE sex = 'உੑ'
     )
     SELECT
     order_id
     FROM order_data
     WHERE user_id IN (SELECT user_id FROM user_man)
     

     View Slide

186. 
     8*5)۟ɹղ౴ྫᶃɹղઆ
     ҎԼSQLΛ8*5)۟Λ࢖ͬͯॻ͖௚ͯ͠Έ͍ͯͩ͘͞ ϦϑΝΫλϦϯά
     
     WITH user_man AS(
     SELECT
     DISTINCT user_id
     FROM users
     WHERE sex = 'உੑ'
     )
     SELECT
     order_id
     FROM order_data
     WHERE user_id IN (SELECT user_id FROM user_man)
     8*5)۟Λ࢖ͬͯҰ࣍ςʔϒϧΛ࡞੒
     8*5)۟ͷςʔϒϧΛ
     ৚݅Ͱࢦఆ
     ˞͜Ε͘Β͍ͩͱ8*5)۟Λ࢖ͬͯ΋͋·ΓมԽͳ͍Ͱ͕͢ɺ৚͕݅ෳࡶʹͳͬͯ͘Δͱ8*5)۟Λ࢖ͬͨํ͕Մಡੑ͕ߴ·
     Δ ྫ͑͹உੑͰɺࡀҎ্Ͱɺ࡟আ͞Εͯͳ͍*%ͳͲ৚͕݅૿͑ͨ৔߹
     
     

     View Slide

187. 
     42-ߨ࠲
     4&-&$5
     ू߹ؔ਺
     (3061#:
     %*45*/$5
     8)&3&
     #&58&&/
     -*,&
     */
     -*.*5
     03%&3#:
     +0*/
     )"7*/(
     $"4&
     αϒΫΤϦ
     8*5)
     6/*0/ 6/*0/"--
     
     ͦͷଞ5JQT
     

     View Slide

188. 
     6/*0/ 6/*0/"--
     
     w ෳ਺ͷςʔϒϧΛ݁߹͢Δͱ͖ʹ࢖͏هड़
     w σʔλΛॎ͔࣋ͪΒԣ࣋ͪʹมߋ͍ͨ͠ͱ͖ʹ࢖͑Δ
     w +0*/ʹΑΔ݁߹͸ςʔϒϧͷʮྻ ॎ
     ʯΛ݁߹͢Δ΍ΓํͰɺ
     6/*0/ 6/*0/"--
     ʹΑΔ݁߹͸ςʔϒϧͷʮߦ ԣ
     ʯΛ݁߹͢Δ
     ΍Γํ
     +0*/ 6/*0/ 6/*0/"--
     
     

     View Slide

189. 
     6/*0/ 6/*0/"--
     ͷ׆༻ྫɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃ
     SELECT
     MAX(birth) AS max_birth,
     MIN(birth) AS min_birth
     FROM users
     SELECT
     'max_birth' as data_key,
     MAX(birth) AS data_value
     FROM users
     UNION ALL
     SELECT
     'max_birth' as data_key,
     MIN(birth) AS data_value
     FROM users
     

     View Slide

190. 
     6/*0/ 6/*0/"--
     ͷ׆༻ྫɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃɹղઆ
     SELECT
     MAX(birth) AS max_birth,
     MIN(birth) AS min_birth
     FROM users
     SELECT
     'max_birth' as data_key,
     MAX(birth) AS data_value
     FROM users
     UNION ALL
     SELECT
     'max_birth' as data_key,
     MIN(birth) AS data_value
     FROM users
     σʔλΛॎʹ࣋ͭ৔߹
     NBY@CJSUI NJO@CJSUI NBY@CJSUI
     NJO@CJSUI
     σʔλΛԣʹ࣋ͭ৔߹
     9999 9999
     9999
     9999
     

     View Slide

191. 
     SELECT
     'max_birth' as data_key,
     MAX(birth) AS data_value
     FROM users
     UNION ALL
     SELECT
     'max_birth' as data_key,
     MIN(birth) AS data_value
     FROM users
     σʔλΛԣʹ࣋ͭ৔߹
     w 6/*0/ 6/*0/"--
     Ͱ݁߹ͤ͞
     Δςʔϒϧ͸ΧϥϜ͕શͯҰகͯ͠
     ͍Δඞཁ͕͋Δ ࠨͷྫͰ͸
     EBUB@LFZͱEBUB@WBMVF͕ͲͪΒ
     ʹ΋ଘࡏ͢Δඞཁ͕͋Δ
     
     6/*0/
     6/*0/"--
     ॏෳߦΛഉআͯ݁͠߹
     ॏෳߦΛഉআͤͣ݁߹
     6/*0/ 6/*0/"--
     ͷ׆༻ྫɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃɹղઆ
     

     View Slide

192. 
     6/*0/ 6/*0/"--
     ͷΠϝʔδ
     6/*0/ 6/*0/"--
     ͸৽͘͠ߦΛ௥Ճ͢Δ࢖͏Πϝʔδ
     PSEFS@JE VTFS@JE PSEFS@QSPEVDU@JE
     $ " #
     $ " #
     ɾɾɾ ɾɾɾ ɾɾɾ
     PSEFS@JE VTFS@JE PSEFS@QSPEVDU@JE
     $ " #
     $ " #
     ɾɾɾ ɾɾɾ ɾɾɾ
     PSEFS@JE VTFS@JE PSEFS@QSPEVDU@JE
     $ " #
     $ " #
     $ " #
     $ " #
     ɾɾɾ ɾɾɾ ɾɾɾ
     

     View Slide

193. 
     42-ߨ࠲
     4&-&$5
     ू߹ؔ਺
     (3061#:
     %*45*/$5
     8)&3&
     #&58&&/
     -*,&
     */
     -*.*5
     03%&3#:
     +0*/
     )"7*/(
     $"4&
     αϒΫΤϦ
     8*5)
     6/*0/ 6/*0/"--
     
     ͦͷଞ5JQT
     

     View Slide

194. 
     5JQTᶃ
     42-Λॻ࣌͘͸ՄಡੑΛߴΊΔͨΊʹΠϯσϯτʹؾΛ͚ͭ·͠ΐ͏
     SELECT
     product_id,
     name
     FROM products
     SELECT
     product_id,
     name
     FROM products
     ˓ ˚
     

     View Slide

195. 
     5JQTᶄ
     ίϝϯτΛ༗ޮʹ࢖͍·͠ΐ͏
     -- ঎඼σʔλͷऔಘ
     SELECT
     product_id,
     name
     FROM products
     /*
     ঎඼σʔλͷऔಘ
     (঎඼IDͱ໊લ)
     */
     SELECT
     product_id,
     name
     FROM products
     ʮʯΛ࢖ͬͯߦίϝϯτΞ΢τ͕Ͱ͖·͢ ʮʯΛ࢖͏ͱෳ਺ߦίϝϯτΞ΢τ͕Ͱ͖·͢
     ෳࡶͳ42-ʹίϝϯτΛ͚ͭͯՄಡੑΛ͋͛ͨΓɺτϥΠΞϯυΤϥʔͰ
     Ұ࣌తʹίϝϯτΞ΢τͨ͠Γ͢ΔͱศརͰ͢
     

     View Slide

196. 
     5JQTᶅ
     42-ͷʮ༧໿ޠʯ͸େจࣈͰॻ͘श׳͕͋Γ·͢
     ࣮ࡍʹࣗ෼Ͱॻ࣌͘͸͋·Γҙࣝ͠ͳͯ͘΋ྑ͍ͱࢥ͍·͢ɻ
     ˞༧໿ޠ͸"4ͳͲͷผ໊Ͱ࢖͑ͳ͍ͷͰ஫ҙ͕ඞཁͰ͢
     SELECT
     product_id,
     name
     FROM products
     select
     product_id,
     name
     from products
     ˓ ˓
     SELECT
     product_id,
     name as select
     FROM products
     º
     

     View Slide

197. 
     5JQTᶆ
     42-ͷू߹ؔ਺Ҏ֎ʹ΋࢖͑Δؔ਺͸͍ͬͺ͍͋Γ·͢
     %#ͷछྨʹΑͬͯ΋࢖͑Δؔ਺͸ҟͳΔͷͰɺશ֮ͯ͑Δඞཁ͸ͳ͘౎౓
     ௐ΂ͯ࢖͑Ε͹ྑ͍ͱࢥ͍·͢
     SELECT
     birth,
     cast(birth as TEXT) as birth_text,
     date(),
     substr(date(), 1, 4) as year,
     substr(date(), 6, 2) as month
     FROM users
     DBTU ܕΛม׵͢Δ
     EBUF ݱࡏ೔෇Λऔಘ͢Δ
     TVCTUS จࣈྻΛ੾ΓऔΔ
     ɾ
     ɾ
     ɾ
     ˞ࠓճ͸৭ʑͳؔ਺΋͋Δͱ͍͏ཧղ͚ͩͰ͖Ε͹ྑ͍Ͱ͢
     

     View Slide

198. 
     &/%
     

     View Slide