データ分析のためのSQL勉強会 〜実践編〜

Transcript

1. σʔλ෼ੳͷͨΊͷ42-ษڧձ
   ʙ࣮ફฤ
   ϋϯζΦϯʙ
2. © 
   )JLBSV
   5BLBIBTIJ  w 42-ษڧձ
   ʙ࣮ફฤʙ
   ֓ཁઆ໌
    ର৅ऀ
    ΰʔϧ
    ਐΊํ ΞδΣϯμ "HFOEB w 42-໰୊ूͷ࣮ફ
   
   ղઆ
3. © 
   )JLBSV
   5BLBIBTIJ  w 42-ษڧձ
   ʙ࣮ફฤʙ
   ֓ཁઆ໌
    ର৅ऀ
    ΰʔϧ
    ਐΊํ ΞδΣϯμ "HFOEB w 42-໰୊ूͷ࣮ફ
   
   ղઆ
4. © 
   )JLBSV
   5BLBIBTIJ  w42-ͷجૅΛཧղ͍ͯ͠Δਓ
   w ʰ42-ษڧձʙॳڃฤʙʱΛडߨ͍ͯ͠Δਓ
   w ʰσʔλ෼ੳྗΛߴΊΔϏδωεύʔιϯͷͨΊͷ42-ೖ໳ॻʱΛಡΜͩਓ
   w࣮຿Ͱσʔλ෼ੳΛ࢖͑ΔΑ͏ʹͳΓ͍ͨਓ ˞ࠓճ͸ʮσʔλ෼ੳͷͨΊͷʯ42-ษڧձͰ͢
   ˞ΞϓϦέʔγϣϯ։ൃͳͲͰ࢖͏42-͸૝ఆ͍ͯ͠ͳ͍ͷͰσʔλऔ ಘ 4&-&$5จ ͕த৺ʹͳ͍ͬͯ·͢ 
   ର৅ऀ 42-ษڧձ
   ʙ࣮ફฤʙ
   ֓ཁઆ໌
5. © 
   )JLBSV
   5BLBIBTIJ  wσʔλ෼ੳͷ࣮຿Ͱ࢖͏42-Λཧղ͢Δ
   wσʔλ෼ੳͷ࣮຿Ͱ࢖͏42-Λࣗ෼Ͱॻ͚ΔΑ͏ʹ͢Δ w ௐ΂͔͚ͯΔ༷ʹ͢Δ
   w ଞͷਓ͕ॻ͍ͨ42- ࣗ෼͕աڈʹॻ͍ͨ42- Λࢀߟʹ42-ͷಡΈղ ͖͕Ͱ͖ΔΑ͏ʹ͢Δ ˞θϩϕʔεͰ42-Λ͔͚Δ༷ʹͳΔඞཁ͸ͳ͍ 
   ΰʔϧ 42-ษڧձ
   ʙ࣮ફฤʙ
   ֓ཁઆ໌
6. © 
   )JLBSV
   5BLBIBTIJ  w ॳڃฤͱಉ༷ʹϋϯζΦϯܗࣜͰ࣮ࢪ
   w ˞ॳڃฤͰ༻ҙͨ͠؀ڥͰ42-ͷ࣮ߦ؀ڥ͕੔͍ͬͯΔ͜ͱ
   w ࣮ࡍͷσʔλ෼ੳͰ࢖͏ूܭΛ΋ͱʹܭ໰ͷ࣮ફ໰୊Λ࣮ࢪ
   w ࣮ફ໰୊Λϕʔεʹجૅ஌ࣝΛઆ໌ޙࣗ෼Ͱߟ͑ͯ42-Λॻ͍͍ͯ͘ελ Πϧ
   w ໰୊ͷղ౴ྫ΋Ұॹʹࡌ͍ͤͯ·͕͢ɺղ౴ΛΈΔલʹ·ͣ͸ࣗ෼Ͱߟ͑ ͯΈ·͠ΐ͏ 
   ਐΊํ 42-ษڧձ
   ʙ࣮ફฤʙ
   ֓ཁઆ໌
7. © 
   )JLBSV
   5BLBIBTIJ  w 42-ษڧձ
   ʙ࣮ફฤʙ
   ֓ཁઆ໌
    ର৅ऀ
    ΰʔϧ
    ਐΊํ ΞδΣϯμ "HFOEB w 42-໰୊ूͷ࣮ફ
   
   ղઆ
8. © 
   )JLBSV
   5BLBIBTIJ  ࣮ફ42-໰୊ूɹબ 42-໰୊ूͷ࣮ફ
   
   ղઆ /P ໰୊ ೉қ౓  ੑผ͝ͱͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒ˑˑˑˑ  ೥୅ผͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ೔ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ݄ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  Ϣʔβʔ͝ͱͷ࠷ऴߪೖ೔ߪೖස౓/ߪೖֹۚΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶃ ˒˒˒˒ˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶄ ˒˒˒˒˒  ʮ৯඼ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒ˑ  ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒˒
9. © 
   )JLBSV
   5BLBIBTIJ  ΧϥϜ໊ ҙຯ ܕ උߟ VTFS@JE Ϣʔβʔ*% 5&95 HFOEFS ੑผ 5&95 ঁੑ
   
   உੑ CJSUI ஀ੜ೔ */5&(&3 ஀ੜ೥͕਺ࣈͰೖΓ·͢ JT@EFMFUFE ࡟আϑϥά */5&(&3 ɿ௨ৗϢʔβʔ
   ɿ࡟আϢʔβʔ ΧϥϜ໊ ҙຯ ܕ උߟ QSPEVDU@JE ঎඼*% 5&95 OBNF ঎඼໊ 5&95 QSJDF ֹۚ */5&(&3 MBSHF@DBUFHPSZ େΧςΰϦ 5&95 NFEJVN@DBUFHPSZ தΧςΰϦ 5&95 TNBMM@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ςʔϒϧ PSEFSTςʔϒϧ αϯϓϧσʔλ 42-໰୊ूͷ࣮ફ
   
   ղઆ ࠓճ͸Սۭͷ&$αΠτͷߪങσʔλΛࢀߟʹσʔ λநग़Λߦ͍·͢ IUUQTHJUIVCDPNIJLBSVU42-4BNQMF%BUB αϯϓϧσʔλɿ
10. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ूɹબ 42-໰୊ूͷ࣮ફ
    
    ղઆ /P ໰୊ ೉қ౓  ੑผ͝ͱͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒ˑˑˑˑ  ೥୅ผͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ೔ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ݄ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  Ϣʔβʔ͝ͱͷ࠷ऴߪೖ೔ߪೖස౓/ߪೖֹۚΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶃ ˒˒˒˒ˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶄ ˒˒˒˒˒  ʮ৯඼ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒ˑ  ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒˒
11. © 
    )JLBSV
    5BLBIBTIJ ੑผ͝ͱͷਓ਺Λूܭ͍ͯͩ͘͠͞ɻͨͩ͠࡟আࡁϢʔβʔ͸আ֎͍ͯͩ͘͠͞ɻ  ໰୊ ೉қ౓ ˒ˑˑˑˑ ࣮ફ42-໰୊ᶃ ࣮ફ42-໰୊ूɹબ ॴཁ࣌ؒ ෼
12. © 
    )JLBSV
    5BLBIBTIJ ੑผ͝ͱͷਓ਺Λूܭ͍ͯͩ͘͠͞ɻͨͩ͠࡟আࡁϢʔβʔ͸আ֎͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶃ ࣮ફ42-໰୊ूɹબ ώϯτᶃ VTFSTςʔϒϧʹੑผ͕෼͔ΔΧϥϜ͕ೖͬͯ·͢ ೉қ౓ ˒ˑˑˑˑ ॴཁ࣌ؒ ෼
13. © 
    )JLBSV
    5BLBIBTIJ ੑผ͝ͱͷਓ਺Λूܭ͍ͯͩ͘͠͞ɻͨͩ͠࡟আࡁϢʔβʔ͸আ֎͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶃ ࣮ફ42-໰୊ूɹબ ώϯτᶃ VTFSTςʔϒϧʹੑผ͕෼͔ΔΧϥϜ͕ೖͬͯ·͢ ώϯτᶄ ಛఆͷଐੑ͝ͱʹΧ΢ϯτ͍ͨ͠৔߹͸(3061
    #:Λ࢖͍·͠ΐ͏ ೉қ౓ ˒ˑˑˑˑ ॴཁ࣌ؒ ෼
14. © 
    )JLBSV
    5BLBIBTIJ ੑผ͝ͱͷਓ਺Λूܭ͍ͯͩ͘͠͞ɻͨͩ͠࡟আࡁϢʔβʔ͸আ֎͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶃ ࣮ફ42-໰୊ूɹબ ώϯτᶃ VTFSTςʔϒϧʹੑผ͕෼͔ΔΧϥϜ͕ೖͬͯ·͢ ώϯτᶄ ಛఆͷଐੑ͝ͱʹΧ΢ϯτ͍ͨ͠৔߹͸(3061
    #:Λ࢖͍·͠ΐ͏ ώϯτᶅ ʰ࡟আࡁϢʔβʔʱ͸VTFSTςʔϒϧͷJT@EFMFUFEϑϥάͰ൑ఆͰ͖·͢ ೉қ౓ ˒ˑˑˑˑ ॴཁ࣌ؒ ෼
15. © 
    )JLBSV
    5BLBIBTIJ ੑผ͝ͱͷਓ਺Λूܭ͍ͯͩ͘͠͞ɻͨͩ͠࡟আࡁϢʔβʔ͸আ֎͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶃɹղ౴ྫ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒ˑˑˑˑ ॴཁ࣌ؒ ෼ SELECT gender, COUNT(*) FROM users WHERE is_deleted = 0 GROUP BY gender
16. © 
    )JLBSV
    5BLBIBTIJ ੑผ͝ͱͷਓ਺Λूܭ͍ͯͩ͘͠͞ɻͨͩ͠࡟আࡁϢʔβʔ͸আ֎͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶃɹղ౴ྫ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒ˑˑˑˑ ॴཁ࣌ؒ ෼ SELECT gender, COUNT(*) FROM users WHERE is_deleted = 0 GROUP BY gender w HSPVQ
    CZΛ࢖ͬͯੑผ͝ͱʹάϧʔϐ ϯά
    w $06/5 Ͱਓ਺Λूܭ $06/5 %*45*/$5
    VTFS@JE Ͱ΋0,
    w 8)&3&ͷ৚݅Ͱ࡟আࡁϢʔβʔΛআ֎
17. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ूɹબ 42-໰୊ूͷ࣮ફ
    
    ղઆ /P ໰୊ ೉қ౓  ੑผ͝ͱͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒ˑˑˑˑ  ೥୅ผͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ೔ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ݄ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  Ϣʔβʔ͝ͱͷ࠷ऴߪೖ೔ߪೖස౓/ߪೖֹۚΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶃ ˒˒˒˒ˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶄ ˒˒˒˒˒  ʮ৯඼ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒ˑ  ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒˒
18. © 
    )JLBSV
    5BLBIBTIJ ҎԼͷ42-Λࢀߟʹ೥୅ผͷਓ਺Λूܭ͍ͯͩ͘͠͞ɻ࡟আࡁϢʔβʔ͸আ֎ͯ͠ɺ೥୅͸10 ୅ɺ20୅ɺ30୅ɺɺɺ80୅ͱ10۠੾ΓͰܭࢉ͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶄ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒ˑˑ ॴཁ࣌ؒ ෼ SELECT user_id, gender, birth, date(), substr(date(), 1, 4) as year, substr(date(), 1, 4) - birth FROM users EBUF ݱࡏ೔෇Λऔಘ͢Δؔ਺ TVCTUS จࣈྻΛ੾ΓऔΔؔ਺
    ࢖͍ํɿTVCTUS ੾ΓऔΔจࣈྻ
    ։࢝Ґஔ
    จࣈ ਺ TVCTUS EBUF
    
     ݱࡏ೔෇ͷจࣈྻ͔Βจࣈ໨͔Βจࣈ੾Γൈ͘
    ྫɿˠ TVCTUS EBUF
    
    
    
    CJSUI ݱࡏͷ੢ྐྵ͔Β஀ੜ೥ͷ੢ྐྵΛҾ͖ࢉ͢Δˠࠓ೥ ͷ೥ྸΛऔಘ ؔ਺આ໌
19. © 
    )JLBSV
    5BLBIBTIJ ҎԼͷ42-Λࢀߟʹ೥୅ผͷਓ਺Λूܭ͍ͯͩ͘͠͞ɻ࡟আࡁϢʔβʔ͸আ֎ͯ͠ɺ೥୅͸10 ୅ɺ20୅ɺ30୅ɺɺɺ80୅ͱ10۠੾ΓͰܭࢉ͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶄ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒ˑˑ ॴཁ࣌ؒ ෼ ώϯτᶃ ·ͣ͸DBTFจΛ࢖ͬͯʮ೥୅ʯͷΧϥϜΛ࡞ͬͯΈ·͠ΐ͏
20. © 
    )JLBSV
    5BLBIBTIJ ҎԼͷ42-Λࢀߟʹ೥୅ผͷਓ਺Λूܭ͍ͯͩ͘͠͞ɻ࡟আࡁϢʔβʔ͸আ֎ͯ͠ɺ೥୅͸10 ୅ɺ20୅ɺ30୅ɺɺɺ80୅ͱ10۠੾ΓͰܭࢉ͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶄ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒ˑˑ ॴཁ࣌ؒ ෼ ώϯτᶃ ·ͣ͸DBTFจΛ࢖ͬͯʮ೥୅ʯͷΧϥϜΛ࡞ͬͯΈ·͠ΐ͏ ώϯτᶄ 8*5)۟ αϒΫΤϦ Λ࢖ͬͯʮ೥୅ʯΛ௥Ճͨ͠Ұ࣌ςʔϒϧΛ࡞੒ͯ͠ Έ·͠ΐ͏
21. © 
    )JLBSV
    5BLBIBTIJ ҎԼͷ42-Λࢀߟʹ೥୅ผͷਓ਺Λूܭ͍ͯͩ͘͠͞ɻ࡟আࡁϢʔβʔ͸আ֎ͯ͠ɺ೥୅͸10 ୅ɺ20୅ɺ30୅ɺɺɺ80୅ͱ10۠੾ΓͰܭࢉ͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶄ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒ˑˑ ॴཁ࣌ؒ ෼ ώϯτᶃ ·ͣ͸DBTFจΛ࢖ͬͯʮ೥୅ʯͷΧϥϜΛ࡞ͬͯΈ·͠ΐ͏ ώϯτᶄ 8*5)۟ αϒΫΤϦ Λ࢖ͬͯʮ೥୅ʯΛ௥Ճͨ͠Ұ࣌ςʔϒϧΛ࡞੒ͯ͠ Έ·͠ΐ͏ ώϯτᶅ ʮ೥୅ʯͰ(3061
    #:ͯ͠Χ΢ϯτͯ͠Έ·͠ΐ͏
22. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶄɹղ౴ྫ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒ˑˑ ॴཁ࣌ؒ ෼ WITH user_add_age AS( SELECT user_id, gender, birth, substr(date(), 1, 4) - birth AS age, CASE WHEN substr(date(), 1, 4) - birth >= 80 THEN '80୅' WHEN substr(date(), 1, 4) - birth >= 70 THEN '70୅' WHEN substr(date(), 1, 4) - birth >= 60 THEN '60୅' WHEN substr(date(), 1, 4) - birth >= 50 THEN '50୅' WHEN substr(date(), 1, 4) - birth >= 40 THEN '40୅' WHEN substr(date(), 1, 4) - birth >= 30 THEN '30୅' WHEN substr(date(), 1, 4) - birth >= 20 THEN '20୅' WHEN substr(date(), 1, 4) - birth < 20 THEN '10୅' ELSE 'ͦͷଞ' END AS age_range FROM users WHERE is_deleted = 0 ) SELECT age_range, COUNT(DISTINCT user_id) as uu FROM user_add_age GROUP BY age_range ORDER BY age_range
23. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶄɹղ౴ྫ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒ˑˑ ॴཁ࣌ؒ ෼ WITH user_add_age AS( SELECT user_id, gender, birth, substr(date(), 1, 4) - birth AS age, CASE WHEN substr(date(), 1, 4) - birth >= 80 THEN '80୅' WHEN substr(date(), 1, 4) - birth >= 70 THEN '70୅' WHEN substr(date(), 1, 4) - birth >= 60 THEN '60୅' WHEN substr(date(), 1, 4) - birth >= 50 THEN '50୅' WHEN substr(date(), 1, 4) - birth >= 40 THEN '40୅' WHEN substr(date(), 1, 4) - birth >= 30 THEN '30୅' WHEN substr(date(), 1, 4) - birth >= 20 THEN '20୅' WHEN substr(date(), 1, 4) - birth < 20 THEN '10୅' ELSE 'ͦͷଞ' END AS age_range FROM users WHERE is_deleted = 0 ) SELECT age_range, COUNT(DISTINCT user_id) as uu FROM user_add_age GROUP BY age_range ORDER BY age_range w ೥ྸ͸ʮTVCTUS EBUF
    
    
    
    CJSUIʯͰܭࢉ
    w ˞ʮBHFʯ͸ࠓճ͸௚઀࢖Θͳ͍ͷͰΧϥϜͱ͠ ͯͳͯ͘΋໰୊ͳ͠
    w ೥ྸΛ$"4&ࣜΛ࢖ͬͯ೥୅ʹม׵
24. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶄɹղ౴ྫ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒ˑˑ ॴཁ࣌ؒ ෼ WITH user_add_age AS( SELECT user_id, gender, birth, substr(date(), 1, 4) - birth AS age, CASE WHEN substr(date(), 1, 4) - birth >= 80 THEN '80୅' WHEN substr(date(), 1, 4) - birth >= 70 THEN '70୅' WHEN substr(date(), 1, 4) - birth >= 60 THEN '60୅' WHEN substr(date(), 1, 4) - birth >= 50 THEN '50୅' WHEN substr(date(), 1, 4) - birth >= 40 THEN '40୅' WHEN substr(date(), 1, 4) - birth >= 30 THEN '30୅' WHEN substr(date(), 1, 4) - birth >= 20 THEN '20୅' WHEN substr(date(), 1, 4) - birth < 20 THEN '10୅' ELSE 'ͦͷଞ' END AS age_range FROM users WHERE is_deleted = 0 ) SELECT age_range, COUNT(DISTINCT user_id) as uu FROM user_add_age GROUP BY age_range ORDER BY age_range w ೥୅ΛೖΕͨ৘ใͰҰ࣌ςʔϒϧΛ࡞੒
    w ࡟আࡁϢʔβʔ͸আ֎͢Δ
25. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶄɹղ౴ྫ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒ˑˑ ॴཁ࣌ؒ ෼ WITH user_add_age AS( SELECT user_id, gender, birth, substr(date(), 1, 4) - birth AS age, CASE WHEN substr(date(), 1, 4) - birth >= 80 THEN '80୅' WHEN substr(date(), 1, 4) - birth >= 70 THEN '70୅' WHEN substr(date(), 1, 4) - birth >= 60 THEN '60୅' WHEN substr(date(), 1, 4) - birth >= 50 THEN '50୅' WHEN substr(date(), 1, 4) - birth >= 40 THEN '40୅' WHEN substr(date(), 1, 4) - birth >= 30 THEN '30୅' WHEN substr(date(), 1, 4) - birth >= 20 THEN '20୅' WHEN substr(date(), 1, 4) - birth < 20 THEN '10୅' ELSE 'ͦͷଞ' END AS age_range FROM users WHERE is_deleted = 0 ) SELECT age_range, COUNT(DISTINCT user_id) as uu FROM user_add_age GROUP BY age_range ORDER BY age_range w ೥୅ BHF@SBOHF Ͱ(3061
    #:ͯ͠ਓ਺ΛΧ΢ϯτ
    w ਓ਺ͷΧ΢ϯτ͸VTFS@JEΛϢχʔΫʹͯ͠Χ΢ϯτ ͢Δ
    w ඞཁʹԠͯ͡03%&3
    #:Ͱ೥୅ॱʹฒ΂ସ͑
26. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶄɹղઆ ࣮ફ42-໰୊ूɹબ ΧϥϜ໊ ҙຯ VTFS@JE Ϣʔβʔ*% HFOEFS ੑผ CJSUI ஀ੜ೔ JT@EFMFUFE ࡟আϑϥά ΧϥϜ໊ ҙຯ VTFS@JE Ϣʔβʔ*% HFOEFS ੑผ CJSUI ஀ੜ೔ JT@EFMFUFE ࡟আϑϥά BHF@SBOHF ೥୅ 45&1 ΧϥϜ໊ ҙຯ BHF@SBOHF ೥୅ $06/5 VTFS@JE Ϣʔβʔ*%ͷ਺ ݩͷςʔϒϧʹͳ͍৘ใͰूܭ͍ͨ͠৔߹͸8*5)۟Λ࢖ͬͯҰ࣌ςʔϒϧΛ࡞੒͢Δ͜ͱͰॱ൪ ʹσʔλΛ੔ཧͯ͠࠷ऴతʹूܭ͍ͨ͠σʔλΛ·ͱΊΔ͜ͱ͕Ͱ͖Δ  45&1  45&1  ݱঢ়ͷςʔϒϧͷ֬ೝ ूܭΑ͏ʹ৽͍͠ΧϥϜ ʢ೥୅ʣΛ௥Ճ ௥Ճͨ͠ΧϥϜͰάϧʔϐϯά ͯ͠ूܭ
27. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ूɹબ 42-໰୊ूͷ࣮ફ
    
    ղઆ /P ໰୊ ೉қ౓  ੑผ͝ͱͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒ˑˑˑˑ  ೥୅ผͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ೔ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ݄ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  Ϣʔβʔ͝ͱͷ࠷ऴߪೖ೔ߪೖස౓/ߪೖֹۚΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶃ ˒˒˒˒ˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶄ ˒˒˒˒˒  ʮ৯඼ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒ˑ  ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒˒
28. © 
    )JLBSV
    5BLBIBTIJ ೔ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ɻͨͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎ͯͩ͘͠͞ ͍ɻ  ໰୊ ࣮ફ42-໰୊ᶅ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒ˑˑˑ ॴཁ࣌ؒ ෼
29. © 
    )JLBSV
    5BLBIBTIJ ೔ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ɻͨͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎ͯͩ͘͠͞ ͍ɻ  ໰୊ ࣮ફ42-໰୊ᶅ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒ˑˑˑ ॴཁ࣌ؒ ෼ ώϯτᶃ ച্Λूܭ͢ΔͨΊʹ͸஫จ৘ใͱ঎඼৘ใΛ݁߹ +0*/ ͤ͞Δඞཁ͕͋ Γ·͢
30. © 
    )JLBSV
    5BLBIBTIJ ೔ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ɻͨͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎ͯͩ͘͠͞ ͍ɻ  ໰୊ ࣮ફ42-໰୊ᶅ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒ˑˑˑ ॴཁ࣌ؒ ෼ ώϯτᶃ ച্Λूܭ͢ΔͨΊʹ͸஫จ৘ใͱ঎඼৘ใΛ݁߹ +0*/ ͤ͞Δඞཁ͕͋ Γ·͢ ώϯτᶄ ஫จ৘ใ PSEFST ͱ঎඼৘ใ QSPEVDUT ͸঎඼*% QSPEVDU@JE Λ࢖ͬͯ ݁߹Ͱ͖·͢
31. © 
    )JLBSV
    5BLBIBTIJ ೔ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ɻͨͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎ͯͩ͘͠͞ ͍ɻ  ໰୊ ࣮ફ42-໰୊ᶅ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒ˑˑˑ ॴཁ࣌ؒ ෼ ώϯτᶃ ച্Λूܭ͢ΔͨΊʹ͸஫จ৘ใͱ঎඼৘ใΛ݁߹ +0*/ ͤ͞Δඞཁ͕͋ Γ·͢ ώϯτᶄ ஫จ৘ใ PSEFST ͱ঎඼৘ใ QSPEVDUT ͸঎඼*% QSPEVDU@JE Λ࢖ͬͯ ݁߹Ͱ͖·͢ ώϯτᶅ ೔෇Ͱ(3061
    #:ͯ͠ച্ͱߪೖ݅਺Λूܭ͠·͠ΐ͏
32. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶅɹղ౴ྫ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒ˑˑˑ ॴཁ࣌ؒ ෼ SELECT o.order_date, COUNT(*), SUM(p.price) FROM orders AS o LEFT JOIN products AS p ON o.order_product_id = p.product_id WHERE o.is_canceled = 0 GROUP BY o.order_date ORDER BY o.order_date
33. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶅɹղ౴ྫ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒ˑˑˑ ॴཁ࣌ؒ ෼ SELECT o.order_date, COUNT(*), SUM(p.price) FROM orders AS o LEFT JOIN products AS p ON o.order_product_id = p.product_id WHERE o.is_canceled = 0 GROUP BY o.order_date ORDER BY o.order_date w QSPEVDUTͷQSPEVDU@JEͱPSEFSTͷ PSEFS@QSPEVDU@JEΛ࢖ͬͯ঎඼৘ใͱ஫จ৘ใΛ݁ ߹ͤ͞Δ
    w ݁߹ͷࡍ͸*//&3
    +0*/͔-&'5
    +0*/Λ࢖͏
    w ˞ࠓճ͸*//&3
    +0*/Ͱ΋-&'5
    +0*/Ͱ΋݁Ռ͸ ಉ͡
34. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶅɹղ౴ྫ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒ˑˑˑ ॴཁ࣌ؒ ෼ SELECT o.order_date, COUNT(*), SUM(p.price) FROM orders AS o LEFT JOIN products AS p ON o.order_product_id = p.product_id WHERE o.is_canceled = 0 GROUP BY o.order_date ORDER BY o.order_date w ೔෇৘ใͰ(3061
    #:Λͯ͠ച্ͱߪೖ݅਺Λूܭ
    w ച্͸QSJDFͷ߹ܭɺߪೖ݅਺͸Ϩίʔυ਺ ஫จ৘ใ ͷߦ਺ ΛΧ΢ϯτ͢Δ
    w JT@DBODFMFE
    
    Ͱ஫จΩϟϯηϧͷσʔλΛআ֎
35. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ूɹબ 42-໰୊ूͷ࣮ફ
    
    ղઆ /P ໰୊ ೉қ౓  ੑผ͝ͱͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒ˑˑˑˑ  ೥୅ผͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ೔ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ݄ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  Ϣʔβʔ͝ͱͷ࠷ऴߪೖ೔ߪೖස౓/ߪೖֹۚΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶃ ˒˒˒˒ˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶄ ˒˒˒˒˒  ʮ৯඼ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒ˑ  ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒˒
36. © 
    )JLBSV
    5BLBIBTIJ ݄ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ɻͨͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎ͯͩ͘͠͞ ͍ɻ  ໰୊ ࣮ફ42-໰୊ᶆ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒ˑˑˑ ॴཁ࣌ؒ ෼
37. © 
    )JLBSV
    5BLBIBTIJ ݄ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ɻͨͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎ͯͩ͘͠͞ ͍ɻ  ໰୊ ࣮ફ42-໰୊ᶆ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒ˑˑˑ ॴཁ࣌ؒ ෼ ώϯτᶃ ʮ݄ʯ৘ใͷऔಘ͸TVCTUSΛ࢖ͬͯ೔෇͔Βʮ݄ʯʹม׵͠·͠ΐ͏
38. © 
    )JLBSV
    5BLBIBTIJ ݄ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ɻͨͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎ͯͩ͘͠͞ ͍ɻ  ໰୊ ࣮ફ42-໰୊ᶆ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒ˑˑˑ ॴཁ࣌ؒ ෼ ώϯτᶃ ʮ݄ʯ৘ใͷऔಘ͸TVCTUSΛ࢖ͬͯ೔෇͔Βʮ݄ʯʹม׵͠·͠ΐ͏ ώϯτᶄ ʮ݄ʯ৘ใ͸ʮTVCTUS PSEFS@EBUF
    
     ʯͰม׵Ͱ͖·͢
39. © 
    )JLBSV
    5BLBIBTIJ ݄ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ɻͨͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎ͯͩ͘͠͞ ͍ɻ  ໰୊ ࣮ફ42-໰୊ᶆ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒ˑˑˑ ॴཁ࣌ؒ ෼ ώϯτᶃ ʮ݄ʯ৘ใͷऔಘ͸TVCTUSΛ࢖ͬͯ೔෇͔Βʮ݄ʯʹม׵͠·͠ΐ͏ ώϯτᶄ ʮ݄ʯ৘ใ͸ʮTVCTUS PSEFS@EBUF
    
     ʯͰม׵Ͱ͖·͢ ώϯτᶅ ݄Ͱ(3061
    #:ͯ͠ച্ͱߪೖ݅਺Λूܭ͠·͠ΐ͏
40. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶆɹղ౴ྫ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒ˑˑˑ ॴཁ࣌ؒ ෼ SELECT substr(o.order_date, 1, 7) AS order_month, COUNT(*), SUM(p.price) FROM orders AS o LEFT JOIN products AS p ON o.order_product_id = p.product_id WHERE o.is_canceled = 0 GROUP BY order_month ORDER BY order_month
41. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶆɹղ౴ྫ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒ˑˑˑ ॴཁ࣌ؒ ෼ SELECT substr(o.order_date, 1, 7) AS order_month, COUNT(*), SUM(p.price) FROM orders AS o LEFT JOIN products AS p ON o.order_product_id = p.product_id WHERE o.is_canceled = 0 GROUP BY order_month ORDER BY order_month w ʮ݄ʯ͸TVCTUS PSEFS@EBUF
    
     Λ࢖ͬͯऔಘ  ೔෇ͷจࣈ໨͔ΒจࣈΛ੾Γऔͬͯʮ݄ʯͷ৘ใΛऔಘ PSEFS@EBUF  PSEFS@NPOUI TVCTUS PSEFS@EBUF
    
    
42. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶆɹղ౴ྫ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒ˑˑˑ ॴཁ࣌ؒ ෼ SELECT substr(o.order_date, 1, 7) AS order_month, COUNT(*), SUM(p.price) FROM orders AS o LEFT JOIN products AS p ON o.order_product_id = p.product_id WHERE o.is_canceled = 0 GROUP BY order_month ORDER BY order_month w 4&-&$5಺ͰTVCTUSͰม׵ͨ͠PSEFS@NPOUIΛ࢖ͬͯ (3061
    #:ͱ03%&3
    #:Ͱूܭͱฒͼସ͑
    w ຊདྷͰ͋Ε͹42-ͷ࣮ߦॱ͔Β03%&3
    #:Ͱ͸4&-&$5 ͷผ໊͕࢖͑Δ͕ɺ(3061
    #:Ͱ͸࢖͑ͳ͍
    w %#ʹΑͬͯ(3061
    #:Ͱ΋4&-&$5ͷผ໊͕࢖͑Δ৔ ߹͕͋ΔͷͰ஫ҙ͕ඞཁ '30. +0*/ 8)&3& (3061
    #: )"7*/( 4&-&$5 03%&3
    #: -*.*5 ᶃ ᶄ ᶅ ᶆ ᶇ ᶈ ᶉ ᶊ 42-ͷ࣮ߦॱং
    ˠ
43. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶆɹղ౴ྫʢผʣ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒ˑˑˑ ॴཁ࣌ؒ ෼ WITH order_data_month AS( SELECT *, substr(order_date, 1, 7) AS order_month FROM orders WHERE is_canceled = 0 ) SELECT order_month, COUNT(*), SUM(p.price) FROM order_data_month AS o LEFT JOIN products AS p ON o.order_product_id = p.product_id GROUP BY order_month ORDER BY order_month w 8*5)۟Λ࢖ͬͯɺʮ݄ʯ৘ใΛ௥Ճͨ͠Ұ࣌ςʔϒϧ Λ࡞੒
    w ݄৘ใͰ(3061
    #:Λ࢖ͬͯूܭ
    w ͜ͷ৔߹͸8*5)۟ͷதͰఆٛͨ͠PSEFS@NPOUIΛ ࢖ͬͯ(3061
    #:ͷूܭ͕Մೳ
44. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ूɹબ 42-໰୊ूͷ࣮ફ
    
    ղઆ /P ໰୊ ೉қ౓  ੑผ͝ͱͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒ˑˑˑˑ  ೥୅ผͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ೔ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ݄ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  Ϣʔβʔ͝ͱͷ࠷ऴߪೖ೔ߪೖස౓/ߪೖֹۚΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶃ ˒˒˒˒ˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶄ ˒˒˒˒˒  ʮ৯඼ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒ˑ  ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒˒
45. © 
    )JLBSV
    5BLBIBTIJ Ϣʔβʔ͝ͱͷ࠷ऴߪೖ೔ɺߪೖස౓ ஫จ೔਺ ɺߪೖֹۚΛूܭ͍ͯͩ͘͠͞ 3'.෼ੳ ɻͨ ͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎ͯ͠࡟আࡁϢʔβʔɺະߪೖϢʔβʔ΋আ֎͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶇ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒ˑˑ ॴཁ࣌ؒ ෼
46. © 
    )JLBSV
    5BLBIBTIJ Ϣʔβʔ͝ͱͷ࠷ऴߪೖ೔ɺߪೖස౓ ஫จ೔਺ ɺߪೖֹۚΛूܭ͍ͯͩ͘͠͞ 3'.෼ੳ ɻͨ ͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎ͯ͠࡟আࡁϢʔβʔɺະߪೖϢʔβʔ΋আ֎͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶇ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒ˑˑ ॴཁ࣌ؒ ෼ ώϯτᶃ VTFSTςʔϒϧͱQSPEVDUTςʔϒϧͱ+0*/ͯͭ͠ςʔϒϧΛ݁߹ͯ͠ Ϣʔβʔ͝ͱͷߪೖσʔλΛ֬ೝ͠·͠ΐ͏
47. © 
    )JLBSV
    5BLBIBTIJ Ϣʔβʔ͝ͱͷ࠷ऴߪೖ೔ɺߪೖස౓ ஫จ೔਺ ɺߪೖֹۚΛूܭ͍ͯͩ͘͠͞ 3'.෼ੳ ɻͨ ͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎ͯ͠࡟আࡁϢʔβʔɺະߪೖϢʔβʔ΋আ֎͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶇ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒ˑˑ ॴཁ࣌ؒ ෼ ώϯτᶃ VTFSTςʔϒϧͱQSPEVDUTςʔϒϧͱ+0*/ͯͭ͠ςʔϒϧΛ݁߹ͯ͠ Ϣʔβʔ͝ͱͷߪೖσʔλΛ֬ೝ͠·͠ΐ͏ ώϯτᶄ Ϣʔβʔ͝ͱͷूܭΛ͢Δ৔߹͸VTFS@JEͰ(3061
    #:Λ࢖͍·͠ΐ͏
48. © 
    )JLBSV
    5BLBIBTIJ Ϣʔβʔ͝ͱͷ࠷ऴߪೖ೔ɺߪೖස౓ ஫จ೔਺ ɺߪೖֹۚΛूܭ͍ͯͩ͘͠͞ 3'.෼ੳ ɻͨ ͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎ͯ͠࡟আࡁϢʔβʔɺະߪೖϢʔβʔ΋আ֎͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶇ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒ˑˑ ॴཁ࣌ؒ ෼ ώϯτᶃ VTFSTςʔϒϧͱQSPEVDUTςʔϒϧͱ+0*/ͯͭ͠ςʔϒϧΛ݁߹ͯ͠ Ϣʔβʔ͝ͱͷߪೖσʔλΛ֬ೝ͠·͠ΐ͏ ώϯτᶄ Ϣʔβʔ͝ͱͷूܭΛ͢Δ৔߹͸VTFS@JEͰ(3061
    #:Λ࢖͍·͠ΐ͏ ώϯτᶅ ɾ࠷ऴߪೖ೔
    ˠ
    ߪೖ೔ͷ࠷େ஋
    ɾߪೖස౓ ஫จ೔਺
    ˠ
    ߪೖ೔਺ͷΧ΢ϯτ
    ɾߪೖֹۚ
    ˠ
    ֹۚͷ߹ܭɹ ͱͯ͠ܭࢉͯ͠Έ·͠ΐ͏
49. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶇɹղ౴ྫ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒ˑˑ ॴཁ࣌ؒ ෼ SELECT o.user_id, MAX(o.order_date), COUNT(DISTINCT o.order_date), SUM(p.price) FROM orders o LEFT JOIN products p ON o.order_product_id = p.product_id LEFT JOIN users u ON o.user_id = u.user_id WHERE o.is_canceled = 0 AND u.is_deleted = 0 GROUP BY o.user_id
50. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶇɹղ౴ྫ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒ˑˑ ॴཁ࣌ؒ ෼ SELECT o.user_id, MAX(o.order_date), COUNT(DISTINCT o.order_date), SUM(p.price) FROM orders o LEFT JOIN products p ON o.order_product_id = p.product_id LEFT JOIN users u ON o.user_id = u.user_id WHERE o.is_canceled = 0 AND u.is_deleted = 0 GROUP BY o.user_id w PSEFSTͱQSPEVDUTςʔϒϧΛ+0*/
    w ঎඼ͷֹۚʢQSJDFʣΛऔಘ͢ΔͨΊ
    w PSEFSTͱVTFSTςʔϒϧΛ+0*/
    w ࡟আࡁΈϢʔβʔΛআ֎͢ΔͨΊ
51. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶇɹղ౴ྫ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒ˑˑ ॴཁ࣌ؒ ෼ SELECT o.user_id, MAX(o.order_date), COUNT(DISTINCT o.order_date), SUM(p.price) FROM orders o LEFT JOIN products p ON o.order_product_id = p.product_id LEFT JOIN users u ON o.user_id = u.user_id WHERE o.is_canceled = 0 AND u.is_deleted = 0 GROUP BY o.user_id w VTFS@JEͰ(3061
    #:ͯ͠ɺҎԼͰ3'.Λநग़
    w 3ɿ࠷ऴߪೖ೔ˠ."9 PPSEFS@EBUF
    w 'ɿߪೖ೔਺ˠ$06/5 %*45*/$5
    PPSEFS@EBUF
    w .ɿߪೖֹۚˠ46. QQSJDF ˞3'.෼ੳͱ͸3FDFODZʢ࠷ऴߪೖ೔ʣ'SFRVFODZʢߪೖස౓ʣ .POFUBSZʢߪೖֹۚʣͷͭͷࢦඪͰސ٬Λ෼ੳ͢Δख๏
52. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ूɹબ 42-໰୊ूͷ࣮ફ
    
    ղઆ /P ໰୊ ೉қ౓  ੑผ͝ͱͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒ˑˑˑˑ  ೥୅ผͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ೔ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ݄ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  Ϣʔβʔ͝ͱͷ࠷ऴߪೖ೔ߪೖස౓/ߪೖֹۚΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶃ ˒˒˒˒ˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶄ ˒˒˒˒˒  ʮ৯඼ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒ˑ  ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒˒
53. © 
    )JLBSV
    5BLBIBTIJ ೥ؒͷߪೖͰԿ೔ؒߪೖ͕͔͋ͬͨߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ɻ ͨͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎ͯ͠࡟আࡁϢʔβʔͱະߪೖϢʔβʔ΋আ֎ͯͩ͘͠͞ ͍ɻ  ໰୊ ࣮ફ42-໰୊ᶈ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒ˑ ॴཁ࣌ؒ ෼ ˠ ೥ؒͷߪೖ೔਺͕೔ͷϢʔβʔ͕ਓ ˠ ೥ؒͷߪೖ೔਺͕೔ͷϢʔβʔ͕ਓ Ξ΢τϓοτΠϝʔδ ߪೖ೔਺ ߪೖऀ਺    
54. © 
    )JLBSV
    5BLBIBTIJ ೥ؒͷߪೖͰԿ೔ؒߪೖ͕͔͋ͬͨߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ɻ ͨͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎ͯ͠࡟আࡁϢʔβʔͱະߪೖϢʔβʔ΋আ֎ͯͩ͘͠͞ ͍ɻ  ໰୊ ࣮ફ42-໰୊ᶈ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒ˑ ॴཁ࣌ؒ ෼ ώϯτᶃ Ϣʔβʔ͝ͱʹߪೖස౓ ߪೖ೔਺ ΛΧ΢ϯτ͠·͠ΐ͏
55. © 
    )JLBSV
    5BLBIBTIJ ೥ؒͷߪೖͰԿ೔ؒߪೖ͕͔͋ͬͨߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ɻ ͨͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎ͯ͠࡟আࡁϢʔβʔͱະߪೖϢʔβʔ΋আ֎ͯͩ͘͠͞ ͍ɻ  ໰୊ ࣮ફ42-໰୊ᶈ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒ˑ ॴཁ࣌ؒ ෼ ώϯτᶃ Ϣʔβʔ͝ͱʹߪೖස౓ ߪೖ೔਺ ΛΧ΢ϯτ͠·͠ΐ͏ ώϯτᶄ 8*5)۟Λ࢖ͬͯҰ࣌ςʔϒϧΛ࡞Δͱ੔ཧ͠΍͘͢ͳΓ·͢ ͦͷࡍ͸ 4&-&$5ͨ݁͠Ռʹ"4ͰΧϥϜ໊Λ͚ͭ·͠ΐ͏
56. © 
    )JLBSV
    5BLBIBTIJ ೥ؒͷߪೖͰԿ೔ؒߪೖ͕͔͋ͬͨߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ɻ ͨͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎ͯ͠࡟আࡁϢʔβʔͱະߪೖϢʔβʔ΋আ֎ͯͩ͘͠͞ ͍ɻ  ໰୊ ࣮ફ42-໰୊ᶈ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒ˑ ॴཁ࣌ؒ ෼ ώϯτᶃ Ϣʔβʔ͝ͱʹߪೖස౓ ߪೖ೔਺ ΛΧ΢ϯτ͠·͠ΐ͏ ώϯτᶄ 8*5)۟Λ࢖ͬͯҰ࣌ςʔϒϧΛ࡞Δͱ੔ཧ͠΍͘͢ͳΓ·͢ ͦͷࡍ͸ 4&-&$5ͨ݁͠Ռʹ"4ͰΧϥϜ໊Λ͚ͭ·͠ΐ͏ ώϯτᶅ ߪೖස౓ ஫จ೔਺ ͝ͱʹϢʔβʔ਺ΛΧ΢ϯτ͢Δ৔߹͸ߪೖස౓ ஫จ ೔਺ Ͱ(3061
    #:Λ࢖͍·͠ΐ͏
57. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶈɹղ౴ྫ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒ˑ ॴཁ࣌ؒ ෼ WITH order_data_users AS( SELECT o.user_id, COUNT(DISTINCT order_date) AS count_order_date FROM orders o LEFT JOIN products p ON o.order_product_id = p.product_id LEFT JOIN users u ON o.user_id = u.user_id WHERE is_canceled = 0 AND u.is_deleted = 0 AND order_date BETWEEN '2022-01-01' and '2022-12-31' GROUP BY o.user_id ) SELECT count_order_date, COUNT(DISTINCT user_id) as user_count FROM order_data_users GROUP BY count_order_date ORDER BY count_order_date
58. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶈɹղ౴ྫ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒ˑ ॴཁ࣌ؒ ෼ w 8*5)۟Λ࢖ͬͯ·ͣ͸Ϣʔβʔ͝ͱͷߪೖ೔਺ΛΧ΢ϯ τ͢Δ
    w PSEFSTͱVTFSTςʔϒϧΛ+0*/ͯ͠஫จσʔλΛநग़
    w ৚݅ͰΩϟϯηϧΛআ֎ɺ࡟আࡁϢʔβʔͷআ֎Λ͢Δ
    w PSEFST͔ΒσʔλΛநग़͍ͯ͠ΔͷͰɺߪೖ͕͋ͬͨ ϢʔβʔͷΈσʔλΛநग़ WITH order_data_users AS( SELECT o.user_id, COUNT(DISTINCT order_date) AS count_order_date FROM orders o LEFT JOIN products p ON o.order_product_id = p.product_id LEFT JOIN users u ON o.user_id = u.user_id WHERE is_canceled = 0 AND u.is_deleted = 0 AND order_date BETWEEN '2022-01-01' and '2022-12-31' GROUP BY o.user_id ) SELECT count_order_date, COUNT(DISTINCT user_id) as user_count FROM order_data_users GROUP BY count_order_date ORDER BY count_order_date
59. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶈɹղ౴ྫ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒ˑ ॴཁ࣌ؒ ෼ w VTFS@JEͰ(3061
    #:͢Δ͜ͱͰϢʔβʔຖͷߪೖ೔਺ Λूܭ
    w ߪೖ೔਺ͷूܭ͸$06/5 %*45*/$5
    PSEFS@EBUFʣˠ ߪೖ೔਺ΛϢχʔΫΧ΢ϯτ͢Δ WITH order_data_users AS( SELECT o.user_id, COUNT(DISTINCT order_date) AS count_order_date FROM orders o LEFT JOIN products p ON o.order_product_id = p.product_id LEFT JOIN users u ON o.user_id = u.user_id WHERE is_canceled = 0 AND u.is_deleted = 0 AND order_date BETWEEN '2022-01-01' and '2022-12-31' GROUP BY o.user_id ) SELECT count_order_date, COUNT(DISTINCT user_id) as user_count FROM order_data_users GROUP BY count_order_date ORDER BY count_order_date
60. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶈɹղ౴ྫ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒ˑ ॴཁ࣌ؒ ෼ w Ϣʔβʔ͝ͱͷߪೖ೔਺ΛΧ΢ϯτͨ͠Ұ࣌ςʔϒϧ͔ Βߪೖ೔਺Λϕʔεʹͯ͠(3061
    #:Ͱਓ਺ΛΧ΢ϯτ WITH order_data_users AS( SELECT o.user_id, COUNT(DISTINCT order_date) AS count_order_date FROM orders o LEFT JOIN products p ON o.order_product_id = p.product_id LEFT JOIN users u ON o.user_id = u.user_id WHERE is_canceled = 0 AND u.is_deleted = 0 AND order_date BETWEEN '2022-01-01' and '2022-12-31' GROUP BY o.user_id ) SELECT count_order_date, COUNT(DISTINCT user_id) as user_count FROM order_data_users GROUP BY count_order_date ORDER BY count_order_date
61. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶈɹղઆ ࣮ફ42-໰୊ूɹબ VTFS@JE DPVOU@PSEFS@EBUF "  "  "  "  45&1 ·ͣ͸Ϣʔβʔຖͷߪೖ೔਺Λूܭ͠ɺͦͷޙߪೖ೔਺ͰϢʔβʔ਺Λूܭ͢Δ  45&1  45&1  VTFSTςʔϒϧ ݱঢ়ͷςʔϒϧͷ֬ೝ Ϣʔβʔຖͷߪೖ೔਺Λूܭ ߪೖ೔਺ͰϢʔβʔ਺Λूܭ PSEFSTςʔϒϧ DPVOU@PSEFS@EBUF VTFS@DPVOU      
62. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ूɹબ 42-໰୊ूͷ࣮ફ
    
    ղઆ /P ໰୊ ೉қ౓  ੑผ͝ͱͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒ˑˑˑˑ  ೥୅ผͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ೔ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ݄ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  Ϣʔβʔ͝ͱͷ࠷ऴߪೖ೔ߪೖස౓/ߪೖֹۚΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶃ ˒˒˒˒ˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶄ ˒˒˒˒˒  ʮ৯඼ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒ˑ  ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒˒
63. © 
    )JLBSV
    5BLBIBTIJ ೥ؒͷߪೖͰԿ೔ؒߪೖ͕͔͋ͬͨߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ɻ ͨͩ͠ະߪೖϢʔβʔ΋ؚΊͨߪೖ೔਺ΛΧ΢ϯτͯ͠ɺ஫จΩϟϯηϧͷσʔλ͸আ֎ɺ࡟আ ࡁΈϢʔβʔ͸আ֎ͯ͠Լ͍͞ɻ  ໰୊ ࣮ફ42-໰୊ᶉ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒˒ ॴཁ࣌ؒ ෼ ˠ ೥ؒͷߪೖ೔਺͕೔ͷϢʔβʔ͕ਓ ˠ ೥ؒͷߪೖ೔਺͕೔ͷϢʔβʔ͕ਓ Ξ΢τϓοτΠϝʔδ ߪೖ೔਺ ߪೖऀ਺     ʢະߪೖϢʔβʔʣ
64. © 
    )JLBSV
    5BLBIBTIJ ೥ؒͷߪೖͰԿ೔ؒߪೖ͕͔͋ͬͨߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ɻ ͨͩ͠ະߪೖϢʔβʔ΋ؚΊͨߪೖ೔਺ΛΧ΢ϯτͯ͠ɺ஫จΩϟϯηϧͷσʔλ͸আ֎ɺ࡟আ ࡁΈϢʔβʔ͸আ֎ͯ͠Լ͍͞ɻ  ໰୊ ࣮ફ42-໰୊ᶉ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒˒ ॴཁ࣌ؒ ෼ ώϯτᶃ ߟ͑ํ͸໰୊ᶈͱಉ͡Ͱ·ͣ͸Ϣʔβʔ͝ͱͷߪೖ೔਺ΛΧ΢ϯτ͠· ͠ΐ͏
65. © 
    )JLBSV
    5BLBIBTIJ ೥ؒͷߪೖͰԿ೔ؒߪೖ͕͔͋ͬͨߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ɻ ͨͩ͠ະߪೖϢʔβʔ΋ؚΊͨߪೖ೔਺ΛΧ΢ϯτͯ͠ɺ஫จΩϟϯηϧͷσʔλ͸আ֎ɺ࡟আ ࡁΈϢʔβʔ͸আ֎ͯ͠Լ͍͞ɻ  ໰୊ ࣮ફ42-໰୊ᶉ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒˒ ॴཁ࣌ؒ ෼ ώϯτᶃ ߟ͑ํ͸໰୊ᶈͱಉ͡Ͱ·ͣ͸Ϣʔβʔ͝ͱͷߪೖ೔਺ΛΧ΢ϯτ͠· ͠ΐ͏ ώϯτᶄ ߪೖ͕ͳ͔ͬͨϢʔβʔ΋Χ΢ϯτ͢ΔͷͰɺVTFSTΛϕʔεʹσʔλΛऔ ಘ͠·͠ΐ͏
66. © 
    )JLBSV
    5BLBIBTIJ ೥ؒͷߪೖͰԿ೔ؒߪೖ͕͔͋ͬͨߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ɻ ͨͩ͠ະߪೖϢʔβʔ΋ؚΊͨߪೖ೔਺ΛΧ΢ϯτͯ͠ɺ஫จΩϟϯηϧͷσʔλ͸আ֎ɺ࡟আ ࡁΈϢʔβʔ͸আ֎ͯ͠Լ͍͞ɻ  ໰୊ ࣮ફ42-໰୊ᶉ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒˒ ॴཁ࣌ؒ ෼ ώϯτᶃ ߟ͑ํ͸໰୊ᶈͱಉ͡Ͱ·ͣ͸Ϣʔβʔ͝ͱͷߪೖ೔਺ΛΧ΢ϯτ͠· ͠ΐ͏ ώϯτᶄ ߪೖ͕ͳ͔ͬͨϢʔβʔ΋Χ΢ϯτ͢ΔͷͰɺVTFSTΛϕʔεʹσʔλΛऔ ಘ͠·͠ΐ͏ ώϯτᶅ ߪೖස౓ ஫จ೔਺ ͝ͱʹϢʔβʔ਺ΛΧ΢ϯτ͢Δ৔߹͸ߪೖස౓ ஫จ ೔਺ Ͱ(3061
    #:Λ࢖͍·͠ΐ͏
67. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶉɹղ౴ྫ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒˒ ॴཁ࣌ؒ ෼ WITH order_data_users AS( SELECT u.user_id, COUNT(DISTINCT order_date) AS count_order_date FROM users u LEFT JOIN orders o ON u.user_id = o.user_id WHERE (o.is_canceled = 0 OR o.is_canceled IS NULL) AND u.is_deleted = 0 GROUP BY u.user_id ) SELECT count_order_date, COUNT(DISTINCT user_id) AS user_count FROM order_data_users GROUP BY count_order_date ORDER BY count_order_date
68. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶉɹղ౴ྫ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒˒ ॴཁ࣌ؒ ෼ WITH order_data_users AS( SELECT u.user_id, COUNT(DISTINCT order_date) AS count_order_date FROM users u LEFT JOIN orders o ON u.user_id = o.user_id WHERE (o.is_canceled = 0 OR o.is_canceled IS NULL) AND u.is_deleted = 0 GROUP BY u.user_id ) SELECT count_order_date, COUNT(DISTINCT user_id) AS user_count FROM order_data_users GROUP BY count_order_date ORDER BY count_order_date w 8*5)۟Λ࢖ͬͯ·ͣ͸Ϣʔβʔ͝ͱͷߪೖ೔਺ΛΧ΢ϯ τ͢Δ
    w ߪೖ͕ͳ͔ͬͨϢʔβʔ΋Χ΢ϯτ͢ΔͷͰɺϕʔε͸ VTFSTςʔϒϧ͔Βऔಘ͢ΔɻVTFSTςʔϒϧʹରͯ͠ PSEFSTΛ+0*/ͤ͞Δ
    w ΩϟϯηϧΛআ֎͢ΔࡍʹJT@DBODFMFE
    
    ͚ͩʹ͢Δ ͱɺͦ΋ͦ΋ߪೖ͕ͳ͔ͬͨσʔλ͕શͯআ֎͞Εͯ͠· ͏ ߪೖ͕ͳ͍৔߹͸JT@DBODFMFE͕OVMM ͳͷͰɺ JT@DBODFMFE͕OVMMͷ৔߹΋৚݅ʹؚΊΔ
69. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶉɹղ౴ྫ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒˒ ॴཁ࣌ؒ ෼ WITH order_data_users AS( SELECT u.user_id, COUNT(DISTINCT order_date) AS count_order_date FROM users u LEFT JOIN orders o ON u.user_id = o.user_id WHERE (o.is_canceled = 0 OR o.is_canceled IS NULL) AND u.is_deleted = 0 GROUP BY u.user_id ) SELECT count_order_date, COUNT(DISTINCT user_id) AS user_count FROM order_data_users GROUP BY count_order_date ORDER BY count_order_date w ᶈͱͷҧ͍͸+0*/ͷॱ൪
    w ᶈ͸PSEFSTΛϕʔεʹͯ͠VTFSTͱ+0*/ɻࠓճ͸VTFST Λϕʔεʹͯ͠PSEFSTͱ+0*/
70. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶉɹղ౴ྫ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒˒ ॴཁ࣌ؒ ෼ WITH order_data_users AS( SELECT u.user_id, COUNT(DISTINCT order_date) AS count_order_date FROM users u LEFT JOIN orders o ON u.user_id = o.user_id WHERE (o.is_canceled = 0 OR o.is_canceled IS NULL) AND u.is_deleted = 0 GROUP BY u.user_id ) SELECT count_order_date, COUNT(DISTINCT user_id) AS user_count FROM order_data_users GROUP BY count_order_date ORDER BY count_order_date w ᶈͱͷҧ͍͸+0*/ͷॱ൪
    w ᶈ͸PSEFSTΛϕʔεʹͯ͠VTFSTͱ+0*/ɻࠓճ͸VTFST Λϕʔεʹͯ͠PSEFSTͱ+0*/ ໰୊ᶈ
    PSEFSTΛϕʔεʹVTFSTͱ+0*/ ໰୊ᶉ
    VTFSTΛϕʔεʹPSEFSTͱ+0*/ ɾPSEFSTͷσʔλ͕શͯ݁߹͞ΕΔ
    ˠVTFSTʹ৘ใ͕ͳ͍ߪೖσʔλ ʢະϩάΠϯͰߪೖͨ͠σʔλʣ΋ औಘ͞ΕΔ
    ɾߪೖ͕ͳ͍σʔλ͸औಘ͞Εͳ͍ ɾVTFSTͷσʔλ͕શͯ݁߹͞ΕΔ
    ˠPSEFSTʹ৘ใ͕ͳ͍ސ٬৘ใʢະ ߪೖͷϢʔβʔʣ΋औಘ͞ΕΔ
    ɾVTFSTʹ৘ใ͕ͳ͍ߪೖσʔλ ʢະϩάΠϯͰߪೖͨ͠σʔλʣ͸ ͸औಘ͞Εͳ͍ VTFST PSEFST PSEFST VTFST
71. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶉɹղ౴ྫ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒˒ ॴཁ࣌ؒ ෼ WITH order_data_users AS( SELECT u.user_id, COUNT(DISTINCT order_date) AS count_order_date FROM users u LEFT JOIN orders o ON u.user_id = o.user_id WHERE (o.is_canceled = 0 OR o.is_canceled IS NULL) AND u.is_deleted = 0 GROUP BY u.user_id ) SELECT count_order_date, COUNT(DISTINCT user_id) AS user_count FROM order_data_users GROUP BY count_order_date ORDER BY count_order_date w ͋ͱ͸໰୊ᶈಉ༷ʹߪೖස౓Ͱ(3061
    #:Λͯ͠Ϣʔ βʔ਺ΛΧ΢ϯτ͢Δ
72. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ूɹબ 42-໰୊ूͷ࣮ફ
    
    ղઆ /P ໰୊ ೉қ౓  ੑผ͝ͱͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒ˑˑˑˑ  ೥୅ผͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ೔ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ݄ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  Ϣʔβʔ͝ͱͷ࠷ऴߪೖ೔ߪೖස౓/ߪೖֹۚΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶃ ˒˒˒˒ˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶄ ˒˒˒˒˒  ʮ৯඼ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒ˑ  ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒˒
73. © 
    )JLBSV
    5BLBIBTIJ ೥݄೔ʹʮ৯඼ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ɻͨ ͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶊ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒ˑˑˑ ॴཁ࣌ؒ ෼
74. © 
    )JLBSV
    5BLBIBTIJ ೥݄೔ʹʮ৯඼ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ɻͨ ͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶊ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒ˑˑˑ ॴཁ࣌ؒ ෼ ώϯτᶃ ஫จ৘ใͱ঎඼৘ใΛ݁߹͠·͠ΐ͏
75. © 
    )JLBSV
    5BLBIBTIJ ೥݄೔ʹʮ৯඼ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ɻͨ ͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶊ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒ˑˑˑ ॴཁ࣌ؒ ෼ ώϯτᶃ ஫จ৘ใͱ঎඼৘ใΛ݁߹͠·͠ΐ͏ ώϯτᶄ ෳ਺ͷ৚݅Λ͚ͭΔ৔߹͸8)&3&ʙ"/%Λ࢖͍·͠ΐ͏
76. © 
    )JLBSV
    5BLBIBTIJ ೥݄೔ʹʮ৯඼ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ɻͨ ͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶊ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒ˑˑˑ ॴཁ࣌ؒ ෼ ώϯτᶃ ஫จ৘ใͱ঎඼৘ใΛ݁߹͠·͠ΐ͏ ώϯτᶄ ෳ਺ͷ৚݅Λ͚ͭΔ৔߹͸8)&3&ʙ"/%Λ࢖͍·͠ΐ͏ ώϯτᶅ ʮ৯඼ʯ͸MBSHF@DBUFHPSZͰ൑ఆ͠·͠ΐ͏
77. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶊɹղ౴ྫ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒ˑˑˑ ॴཁ࣌ؒ ෼ SELECT DISTINCT o.user_id FROM orders o LEFT JOIN products p ON o.order_product_id = p.product_id WHERE is_canceled = 0 AND o.order_date = '2022-01-01' AND p.large_category = '৯඼'
78. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶊɹղ౴ྫ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒ˑˑˑ ॴཁ࣌ؒ ෼ SELECT DISTINCT o.user_id FROM orders o LEFT JOIN products p ON o.order_product_id = p.product_id WHERE is_canceled = 0 AND o.order_date = '2022-01-01' AND p.large_category = '৯඼' w PSEFSTͱQSPEVDUTΛQSPEVDU@JEΛΩʔʹ݁߹͠·͢
    w ΦʔμʔΩϟϯηϧͷ৘ใ͸আ֎͠·͢
    w ʮ৯඼ʯ͸MBSHF@DBUFHPSZ
    
    b৯඼`Ͱ൑ఆ͠·͢
79. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶊɹղ౴ྫɹ5*14 ࣮ફ42-໰୊ूɹબ SELECT DISTINCT large_category FROM products w MBSHF@DBUFHPSZʹͲΜͳ஋͕ೖ͍ͬͯΔͷ͔֬ೝͨ͠ ͍৔߹ɺʮ%*45*/$5ʯΛ࢖ͬͯMBSHF@DBUFHPSZͷϢ χʔΫͳ஋ΛҰཡͰ֬ೝ͢Δࣄ͕Ͱ͖·͢
    w ͜ΕͰMBSHF@DBUFHPSZʹʮ৯඼ʯͷσʔλ͕ೖ͍ͬͯ Δࣄ͕֬ೝͰ͖·͢ σʔλ෼ੳͷࡍʹΑ֬͘ೝ͠·͢ MBSHF@DBUFHPSZ ৯඼ ೔༻඼ ΠϯςϦΞ ϑΝογϣϯ ిԽ੡඼ ग़ྗ݁Ռ
80. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ूɹબ 42-໰୊ूͷ࣮ફ
    
    ղઆ /P ໰୊ ೉қ౓  ੑผ͝ͱͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒ˑˑˑˑ  ೥୅ผͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ೔ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ݄ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  Ϣʔβʔ͝ͱͷ࠷ऴߪೖ೔ߪೖස౓/ߪೖֹۚΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶃ ˒˒˒˒ˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶄ ˒˒˒˒˒  ʮ৯඼ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒ˑ  ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒˒
81. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶋ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒ˑ ॴཁ࣌ؒ ෼ ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔ɺΧςΰϦ͝ͱʹߪೖऀ਺Λநग़ͯ͠Լ͍͞ɻ ͨͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎͍ͯͩ͘͠͞ɻ·ͨதΧςΰϦ NFEJVN@DBUFHPSZ ͝ ͱͷߪೖऀ͕ଟ͍ॱʹ݁ՌΛग़ྗ͍ͯͩ͘͠͞ɻ ໰୊
82. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶋ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒ˑ ॴཁ࣌ؒ ෼ ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔ɺΧςΰϦ͝ͱʹߪೖऀ਺Λநग़ͯ͠Լ͍͞ɻ ͨͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎͍ͯͩ͘͠͞ɻ·ͨதΧςΰϦ NFEJVN@DBUFHPSZ ͝ ͱͷߪೖऀ͕ଟ͍ॱʹ݁ՌΛग़ྗ͍ͯͩ͘͠͞ɻ ໰୊ ώϯτᶃ 8*5)۟Λ࢖ͬͯʮඒ༰ΛങͬͨਓʯͷҰ࣌ςʔϒϧΛ࡞੒͠·͠ΐ͏
83. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶋ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒ˑ ॴཁ࣌ؒ ෼ ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔ɺΧςΰϦ͝ͱʹߪೖऀ਺Λநग़ͯ͠Լ͍͞ɻ ͨͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎͍ͯͩ͘͠͞ɻ·ͨதΧςΰϦ NFEJVN@DBUFHPSZ ͝ ͱͷߪೖऀ͕ଟ͍ॱʹ݁ՌΛग़ྗ͍ͯͩ͘͠͞ɻ ໰୊ ώϯτᶃ 8*5)۟Λ࢖ͬͯʮඒ༰ΛങͬͨਓʯͷҰ࣌ςʔϒϧΛ࡞੒͠·͠ΐ͏ ώϯτᶄ ʮඒ༰Λങͬͨਓʯ"/%ʮඒ༰Ҏ֎ͷ঎඼ʯͱͭͷ৚݅Λ෇͚ͯσʔλ Λऔಘ͠·͠ΐ͏
84. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶋ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒ˑ ॴཁ࣌ؒ ෼ ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔ɺΧςΰϦ͝ͱʹߪೖऀ਺Λநग़ͯ͠Լ͍͞ɻ ͨͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎͍ͯͩ͘͠͞ɻ·ͨதΧςΰϦ NFEJVN@DBUFHPSZ ͝ ͱͷߪೖऀ͕ଟ͍ॱʹ݁ՌΛग़ྗ͍ͯͩ͘͠͞ɻ ໰୊ ώϯτᶃ 8*5)۟Λ࢖ͬͯʮඒ༰ΛങͬͨਓʯͷҰ࣌ςʔϒϧΛ࡞੒͠·͠ΐ͏ ώϯτᶄ ʮඒ༰Λങͬͨਓʯ"/%ʮඒ༰Ҏ֎ͷ঎඼ʯͱͭͷ৚݅Λ෇͚ͯσʔλ Λऔಘ͠·͠ΐ͏ ώϯτᶅ ࠷ऴతʹ͸NFEJVN@DBUFHPSZͰ(3061
    #:ͯ͠ߪೖऀ VTFS@JE ΛΧ΢ ϯτ͠·͠ΐ͏
85. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶋɹղ౴ྫ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒ˑ ॴཁ࣌ؒ ෼ WITH beauty_order_user_id AS ( SELECT DISTINCT user_id FROM orders o LEFT JOIN products p ON o.order_product_id = p.product_id WHERE is_canceled = 0 AND p.medium_category = 'ඒ༰' ) SELECT p.medium_category, COUNT(DISTINCT user_id) AS uu FROM orders o LEFT JOIN products p ON o.order_product_id = p.product_id WHERE is_canceled = 0 -- ඒ༰Λങͬͨਓ AND user_id IN (SELECT user_id FROM beauty_order_user_id) -- ඒ༰Ҏ֎ͷ঎඼ AND p.medium_category <> 'ඒ༰' GROUP BY p.medium_category ORDER BY uu DESC
86. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶋɹղ౴ྫ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒ˑ ॴཁ࣌ؒ ෼ WITH beauty_order_user_id AS ( SELECT DISTINCT user_id FROM orders o LEFT JOIN products p ON o.order_product_id = p.product_id WHERE is_canceled = 0 AND p.medium_category = 'ඒ༰' ) SELECT p.medium_category, COUNT(DISTINCT user_id) AS uu FROM orders o LEFT JOIN products p ON o.order_product_id = p.product_id WHERE is_canceled = 0 -- ඒ༰Λങͬͨਓ AND user_id IN (SELECT user_id FROM beauty_order_user_id) -- ඒ༰Ҏ֎ͷ঎඼ AND p.medium_category <> 'ඒ༰' GROUP BY p.medium_category ORDER BY uu DESC w 8*5)۟Λ࢖ͬͯඒ༰ΛങͬͨVTFS@JEΛϢχʔΫʹந ग़ ໰୊ᶊͷ42-ͱ΄΅ಉ͡
87. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶋɹղ౴ྫ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒ˑ ॴཁ࣌ؒ ෼ WITH beauty_order_user_id AS ( SELECT DISTINCT user_id FROM orders o LEFT JOIN products p ON o.order_product_id = p.product_id WHERE is_canceled = 0 AND p.medium_category = 'ඒ༰' ) SELECT p.medium_category, COUNT(DISTINCT user_id) AS uu FROM orders o LEFT JOIN products p ON o.order_product_id = p.product_id WHERE is_canceled = 0 -- ඒ༰Λങͬͨਓ AND user_id IN (SELECT user_id FROM beauty_order_user_id) -- ඒ༰Ҏ֎ͷ঎඼ AND p.medium_category <> 'ඒ༰' GROUP BY p.medium_category ORDER BY uu DESC w ৚݅ʹʮඒ༰ΛങͬͨਓʯΛ͚ͭΔ
    w VTFS@JE͕ඒ༰ΛങͬͨϢʔβʔ*%ʹؚ·ΕΔ ʢ*/ʣͱ͍͏৚݅Λ͚ͭΔ
    w ඒ༰Ҏ֎ͷ঎඼Λநग़͢Δ৔߹͸ʮʯΛ࢖ͬͯಛఆ ͷΧςΰϦҎ֎ͷσʔλΛநग़͢Δ
88. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶋɹղ౴ྫ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒ˑ ॴཁ࣌ؒ ෼ WITH beauty_order_user_id AS ( SELECT DISTINCT user_id FROM orders o LEFT JOIN products p ON o.order_product_id = p.product_id WHERE is_canceled = 0 AND p.medium_category = 'ඒ༰' ) SELECT p.medium_category, COUNT(DISTINCT user_id) AS uu FROM orders o LEFT JOIN products p ON o.order_product_id = p.product_id WHERE is_canceled = 0 -- ඒ༰Λങͬͨਓ AND user_id IN (SELECT user_id FROM beauty_order_user_id) -- ඒ༰Ҏ֎ͷ঎඼ AND p.medium_category <> 'ඒ༰' GROUP BY p.medium_category ORDER BY uu DESC w NFEJVN@DBUFHPSZͰ(3061
    #:ͯ͠VTFS@JEΛϢ χʔΫΧ΢ϯτ͢Δ
89. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶋɹղ౴ྫʴЋ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒ˑ ॴཁ࣌ؒ ෼ WITH beauty_order_user_id AS ( SELECT DISTINCT user_id FROM orders o LEFT JOIN products p ON o.order_product_id = p.product_id WHERE is_canceled = 0 AND p.medium_category = 'ඒ༰' ), category_order_data AS ( SELECT p.medium_category, COUNT(DISTINCT user_id) AS uu, (SELECT COUNT(DISTINCT user_id) FROM beauty_order_user_id) AS total_uu FROM orders o LEFT JOIN products p ON o.order_product_id = p.product_id WHERE is_canceled = 0 -- ඒ༰Λങͬͨਓ AND user_id IN (SELECT user_id FROM beauty_order_user_id) -- ඒ༰Ҏ֎ͷ঎඼ AND p.medium_category <> 'ඒ༰' GROUP BY p.medium_category ) SELECT medium_category, uu, total_uu, round(100.00 * uu / total_uu, 2) AS percent_uu FROM category_order_data ORDER BY uu DESC w αϒΫΤϦΛ࢖ͬͯɺඒ༰Λങͬͨਓͷ߹ܭਓ਺Λྻ ʹ௥Ճ ଞͷΧςΰϦങͬͯΔਓ͕Կ%͍Δͷׂ͔߹΋Ұॹʹग़͢৔߹ NFEJVN@DBUFHPSZ VV UPUBM@VV QFSDFOU@VV จ๪۩    ҿྉਫ    ໺ࡊ   
90. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶋɹղ౴ྫʴЋ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒ˑ ॴཁ࣌ؒ ෼ WITH beauty_order_user_id AS ( SELECT DISTINCT user_id FROM orders o LEFT JOIN products p ON o.order_product_id = p.product_id WHERE is_canceled = 0 AND p.medium_category = 'ඒ༰' ), category_order_data AS ( SELECT p.medium_category, COUNT(DISTINCT user_id) AS uu, (SELECT COUNT(DISTINCT user_id) FROM beauty_order_user_id) AS total_uu FROM orders o LEFT JOIN products p ON o.order_product_id = p.product_id WHERE is_canceled = 0 -- ඒ༰Λങͬͨਓ AND user_id IN (SELECT user_id FROM beauty_order_user_id) -- ඒ༰Ҏ֎ͷ঎඼ AND p.medium_category <> 'ඒ༰' GROUP BY p.medium_category ) SELECT medium_category, uu, total_uu, round(100.00 * uu / total_uu, 2) AS percent_uu FROM category_order_data ORDER BY uu DESC w ߹ܭਓ਺ͱΧςΰϦ͝ͱͷߪೖਓ਺ΛׂΓࢉׂͯ͠߹ ΛٻΊΔ
    w 306/%͸ܻ਺Λࢦఆ࢛ࣺͯ͠ޒೖ͢Δؔ਺
    w ʮʯΛ͔͚Δ͜ͱͰ݁Ռ΋খ਺఺ୈೋҐ·Ͱ දࣔͤ͞Δ ଞͷΧςΰϦങͬͯΔਓ͕Կ%͍Δͷׂ͔߹΋Ұॹʹग़͢৔߹ NFEJVN@DBUFHPSZ VV UPUBM@VV QFSDFOU@VV จ๪۩    ҿྉਫ    ໺ࡊ   
91. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ूɹબ 42-໰୊ूͷ࣮ફ
    
    ղઆ /P ໰୊ ೉қ౓  ੑผ͝ͱͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒ˑˑˑˑ  ೥୅ผͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ೔ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ݄ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  Ϣʔβʔ͝ͱͷ࠷ऴߪೖ೔ߪೖස౓/ߪೖֹۚΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶃ ˒˒˒˒ˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶄ ˒˒˒˒˒  ʮ৯඼ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒ˑ  ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒˒
92. © 
    )JLBSV
    5BLBIBTIJ ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔঎඼ผͷϥϯΩϯά ߪೖ݅਺͕ଟ͍ॱ Λநग़ͯ͠Լ͞ ͍ɻͨͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎ͯ͠ɺVTFS@JE͕ۭͷσʔλ΋আ֎͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶌ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒˒ ॴཁ࣌ؒ ෼
93. © 
    )JLBSV
    5BLBIBTIJ ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔঎඼ผͷϥϯΩϯά ߪೖ݅਺͕ଟ͍ॱ Λநग़ͯ͠Լ͞ ͍ɻͨͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎ͯ͠ɺVTFS@JE͕ۭͷσʔλ΋আ֎͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶌ ࣮ફ42-໰୊ूɹબ ೉қ౓ ॴཁ࣌ؒ ෼ ώϯτᶃ Ϣʔβʔ͝ͱͷॳճ஫จ*%Λநग़ͯ͠ɺͦͷ஫จ*%ͷσʔλΛநग़͢Ε͹ ॳճߪೖͷߪೖσʔλ͕நग़Ͱ͖·͢ ˒˒˒˒˒
94. © 
    )JLBSV
    5BLBIBTIJ ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔঎඼ผͷϥϯΩϯά ߪೖ݅਺͕ଟ͍ॱ Λநग़ͯ͠Լ͞ ͍ɻͨͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎ͯ͠ɺVTFS@JE͕ۭͷσʔλ΋আ֎͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶌ ࣮ફ42-໰୊ूɹબ ೉қ౓ ॴཁ࣌ؒ ෼ ώϯτᶃ Ϣʔβʔ͝ͱͷॳճ஫จ*%Λநग़ͯ͠ɺͦͷ஫จ*%ͷσʔλΛநग़͢Ε͹ ॳճߪೖͷߪೖσʔλ͕நग़Ͱ͖·͢ ώϯτᶄ ʮॳճߪೖʯʮϢʔβʔ୯ҐͰ஫จ൪߸͕Ұ൪খ͍͞஫จʯ ˒˒˒˒˒
95. © 
    )JLBSV
    5BLBIBTIJ ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔঎඼ผͷϥϯΩϯά ߪೖ݅਺͕ଟ͍ॱ Λநग़ͯ͠Լ͞ ͍ɻͨͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎ͯ͠ɺVTFS@JE͕ۭͷσʔλ΋আ֎͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶌ ࣮ફ42-໰୊ूɹબ ೉қ౓ ॴཁ࣌ؒ ෼ ώϯτᶃ Ϣʔβʔ͝ͱͷॳճ஫จ*%Λநग़ͯ͠ɺͦͷ஫จ*%ͷσʔλΛநग़͢Ε͹ ॳճߪೖͷߪೖσʔλ͕நग़Ͱ͖·͢ ώϯτᶄ ʮॳճߪೖʯʮϢʔβʔ୯ҐͰ஫จ൪߸͕Ұ൪খ͍͞஫จʯ ώϯτᶅ ʮ஫จ൪߸͕Ұ൪খ͍͞ʯΛ൑ఆ͢ΔͨΊʹ.*/ؔ਺Λ࢖͍·͠ΐ͏ ˒˒˒˒˒
96. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶌɹղ౴ྫ ࣮ફ42-໰୊ूɹબ ೉қ౓ ॴཁ࣌ؒ ෼ -- Ϣʔβʔ͝ͱͷॳճ஫จIDΛநग़ WITH user_order AS ( SELECT user_id, MIN(order_id) AS min_order_id FROM orders WHERE user_id IS NOT NULL AND is_canceled = 0 GROUP BY user_id ) SELECT name, COUNT(*) AS order_count, SUM(price) AS order_price FROM orders AS o LEFT JOIN products p ON o.order_product_id = p.product_id -- ॳճߪೖͷ஫จIDʹݶఆ WHERE order_id IN (SELECT min_order_id FROM user_order) GROUP BY name ORDER BY order_count DESC ˒˒˒˒˒
97. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶌɹղ౴ྫ ࣮ફ42-໰୊ूɹબ ೉қ౓ ॴཁ࣌ؒ ෼ -- Ϣʔβʔ͝ͱͷॳճ஫จIDΛநग़ WITH user_order AS ( SELECT user_id, MIN(order_id) AS min_order_id FROM orders WHERE user_id IS NOT NULL AND is_canceled = 0 GROUP BY user_id ) SELECT name, COUNT(*) AS order_count, SUM(price) AS order_price FROM orders AS o LEFT JOIN products p ON o.order_product_id = p.product_id -- ॳճߪೖͷ஫จIDʹݶఆ WHERE order_id IN (SELECT min_order_id FROM user_order) GROUP BY name ORDER BY order_count DESC ˒˒˒˒˒ w VTFS@JEͰάϧʔϐϯάͯ͠஫จ൪߸͕Ұ൪খ͍͞ σʔλΛநग़͢ΔҰ࣌ςʔϒϧΛ࡞੒͢Δ
    w ʮ.*/ PSEFS@JE ʯʹΑͬͯϢʔβʔ͝ͱʹҰ൪খ͞ ͍஫จ*%ॳճͷ஫จ*%Λऔಘ͢Δ
98. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶌɹղ౴ྫ ࣮ફ42-໰୊ूɹબ ೉қ౓ ॴཁ࣌ؒ ෼ -- Ϣʔβʔ͝ͱͷॳճ஫จIDΛநग़ WITH user_order AS ( SELECT user_id, MIN(order_id) AS min_order_id FROM orders WHERE user_id IS NOT NULL AND is_canceled = 0 GROUP BY user_id ) SELECT name, COUNT(*) AS order_count, SUM(price) AS order_price FROM orders AS o LEFT JOIN products p ON o.order_product_id = p.product_id -- ॳճߪೖͷ஫จIDʹݶఆ WHERE order_id IN (SELECT min_order_id FROM user_order) GROUP BY name ORDER BY order_count DESC ˒˒˒˒˒ w 8*5)۟Ͱ࡞੒ͨ͠Ұ࣌ςʔϒϧʹॳճߪೖͷ஫จ*% ؚ͕·Ε͍ͯΔͷͰɺͦΕΛ৚݅ʹ௥Ճͯ͠ॳճߪೖ ͷσʔλΛநग़
    w ʮ*/ʯΛ࢖ͬͯॳճߪೖͷ஫จ*%ͷΈΛநग़
99. © 
    )JLBSV
    5BLBIBTIJ  ࣮ફ42-໰୊ᶌɹղ౴ྫ ࣮ફ42-໰୊ूɹબ ೉қ౓ ॴཁ࣌ؒ ෼ -- Ϣʔβʔ͝ͱͷॳճ஫จIDΛநग़ WITH user_order AS ( SELECT user_id, MIN(order_id) AS min_order_id FROM orders WHERE user_id IS NOT NULL AND is_canceled = 0 GROUP BY user_id ) SELECT name, COUNT(*) AS order_count, SUM(price) AS order_price FROM orders AS o LEFT JOIN products p ON o.order_product_id = p.product_id -- ॳճߪೖͷ஫จIDʹݶఆ WHERE order_id IN (SELECT min_order_id FROM user_order) GROUP BY name ORDER BY order_count DESC ˒˒˒˒˒ w ঎඼໊ʢOBNFʣͰάϧʔϐϯάͯ͠ߪೖ݅਺Λநग़ ඞཁʹԠֹͯۚ͡΋ग़ͤΔ
    w ߪೖ݅਺͸ʮ$06/5 ʯͰ΋ʮ$06/5 %*45*/$5
    PSEFS@JE ʯͰ΋ಉ͡
100. © 
     )JLBSV
     5BLBIBTIJ  ࣮ફ42-໰୊ᶌɹղ౴ྫʢผղʣ ࣮ફ42-໰୊ूɹબ ೉қ౓ ॴཁ࣌ؒ ෼ -- Ϣʔβʔ͝ͱͷ஫จॱ൪Λ௥Ճ WITH add_order_number AS( SELECT *, DENSE_RANK() OVER(PARTITION BY o.user_id ORDER BY o.order_id ASC) AS order_number FROM orders AS o LEFT JOIN products p ON o.order_product_id = p.product_id WHERE o.user_id IS NOT NULL AND o.is_canceled = 0 ) SELECT name, COUNT(*) AS order_count, SUM(price) AS order_price FROM add_order_number WHERE order_number = 1 GROUP BY name ORDER BY order_count DESC ˒˒˒˒˒ w 8*/%08ؔ਺Λ࢖ͬͯϢʔβʔ͝ͱͷ஫จʹ൪߸Λ෇༩͢ΔࣄͰநग़͕Մೳˠ%&/4&@3"/,
     w ॳճߪೖͷσʔλΛநग़͢Δ৔߹͸ʮPSEFS@OVNCFSʯͷ৚݅Λ͚ͭΔ 3"/, ಉ཰͕͋ͬͨ৔߹ॱҐ͸ಉ ͡ʹͳΓɺͦͷ࣍͸ॱҐΛ ඈ͹͠·͢ɻ
     ʢ1Ґɺ1Ґɺ3Ґɾɾɾʣ %&/4&@3" /, ಉ཰͕͋ͬͨ৔߹ॱҐ͸ಉ ͡ʹͳΓɺͦͷ࣍͸ॱҐΛ ඈ͹͠·ͤΜɻ
     ʢ1Ґɺ1Ґɺ2Ґɾɾɾʣ 308@/6. .#&3
     ಉ཰͕͋ͬͯ΋ಉ͡ॱҐʹ ͸ͳΒͣɺॱҐΛΧ΢ϯτ ͠·͢ɻ
     ಉ཰͕͋ͬͨ৔߹ͷॱҐ͸ ৗʹಉ͡ͱ͸ݶΓ·ͤΜɻ ʢ1Ґɺ2Ґɺ3Ґɾɾɾʣ
101. © 
     )JLBSV
     5BLBIBTIJ  ࣮ફ42-໰୊ᶌɹ8*/%08ؔ਺ͷղઆ ࣮ફ42-໰୊ूɹબ 8*/%08ؔ਺͸ݩͷςʔϒϧͷߦΛҡ࣋ͨ͠··(3061
     #:ͰಘΒΕΔ݁Ռʢू໿ؔ਺ͷ݁ ՌʣΛςʔϒϧʹ෇Ճ͢ΔΠϝʔδ VTFS@JE PSEFS@JE QSJDF " $  " $  " $  " $  VTFS@JE .*/ PSEFS@JE 46. QSJDF " $  " $  (3061
     #:Λ࢖͏ͱVTFS@JEͰάϧʔϐϯάͰ͖Δ͕ߦ͕ VTFS@JE͝ͱʹ·ͱΊΒΕΔ (3061
     #: 8*/%08ؔ਺ VTFS@JE PSEFS@JE QSJDF %&/4&@3"/, 46. " $    " $    " $    " $    ݩͷςʔϒϧͷߦΛҡ࣋ͨ͠··ಛఆͷΧϥϜͰάϧʔϐϯ άͨ݁͠ՌΛ෇༩͢Δ͜ͱ͕Ͱ͖Δ ݩςʔϒϧ Πϝʔδ
102. © 
     )JLBSV
     5BLBIBTIJ  ࣮ફ42-໰୊ᶌɹ8*/%08ؔ਺ͷղઆ ࣮ફ42-໰୊ूɹબ DENSE_RANK() OVER(PARTITION BY o.user_id ORDER BY o.order_id ASC) AS order_number 8*/%08ؔ਺͸ݩͷςʔϒϧͷߦΛҡ࣋ͨ͠··(3061
     #:ͰಘΒΕΔ݁Ռʢू໿ؔ਺ͷ݁ ՌʣΛςʔϒϧʹ෇Ճ͢ΔΠϝʔδ ʰ%&/4&@3"/,ʱ͸ॱ ൪෇Λ͢Δؔ਺ɻͲͷΑ͏ ͳॱ൪ʹ͢Δ͔͸07&3Ҏ Լͷهࡌ಺༰ʹै͏ɻ %&/4&@3"/,Ҏ֎ʹ΋ 308@/6.#&3΍46. ͳͲ͕࢖͑Δɻ %&/4&@3"/, ʰ07&3ʱ͸8*/%08ؔ ਺Λ࢖͏ͱ͍͏એݴɻৄࡉ ͸07&3Ҏ߱ʹهࡌɻ 07&3 ʰ1"35*5*0/
     #:ʱ͸ά ϧʔϓ৚݅ΛࢦఆͰ͖Δɻ (3061
     #:ͱಉ͡Πϝʔ δɻ 1"35*5*0/
     #: ʰ03%&3
     #:ʱ͸ฒͼସ ͑Λࢦఆʢ௨ৗͷ 03%&3
     #:ͱಉ͡ʣ 03%&3
     #: ۩ମྫ
103. &/%