Tweet
Tweet

Slide 1

Slide 1 text

σʔλ෼ੳͷͨΊͷ42-ษڧձ ʙ࣮ફฤϋϯζΦϯʙ

Slide 2

Slide 2 text

© )JLBSV5BLBIBTIJ  w 42-ษڧձʙ࣮ફฤʙ֓ཁઆ໌  ର৅ऀ  ΰʔϧ  ਐΊํ ΞδΣϯμ "HFOEB w 42-໰୊ूͷ࣮ફ ղઆ

Slide 3

Slide 3 text

© )JLBSV5BLBIBTIJ  w 42-ษڧձʙ࣮ફฤʙ֓ཁઆ໌  ର৅ऀ  ΰʔϧ  ਐΊํ ΞδΣϯμ "HFOEB w 42-໰୊ूͷ࣮ફ ղઆ

Slide 4

Slide 4 text

© )JLBSV5BLBIBTIJ  w42-ͷجૅΛཧղ͍ͯ͠Δਓ w ʰ42-ษڧձʙॳڃฤʙʱΛडߨ͍ͯ͠Δਓ w ʰσʔλ෼ੳྗΛߴΊΔϏδωεύʔιϯͷͨΊͷ42-ೖ໳ॻʱΛಡΜͩਓ w࣮຿Ͱσʔλ෼ੳΛ࢖͑ΔΑ͏ʹͳΓ͍ͨਓ ˞ࠓճ͸ʮσʔλ෼ੳͷͨΊͷʯ42-ษڧձͰ͢ ˞ΞϓϦέʔγϣϯ։ൃͳͲͰ࢖͏42-͸૝ఆ͍ͯ͠ͳ͍ͷͰσʔλऔ ಘ 4&-&$5จ ͕த৺ʹͳ͍ͬͯ·͢ ର৅ऀ 42-ษڧձʙ࣮ફฤʙ֓ཁઆ໌

Slide 5

Slide 5 text

© )JLBSV5BLBIBTIJ  wσʔλ෼ੳͷ࣮຿Ͱ࢖͏42-Λཧղ͢Δ wσʔλ෼ੳͷ࣮຿Ͱ࢖͏42-Λࣗ෼Ͱॻ͚ΔΑ͏ʹ͢Δ w ௐ΂͔͚ͯΔ༷ʹ͢Δ w ଞͷਓ͕ॻ͍ͨ42- ࣗ෼͕աڈʹॻ͍ͨ42- Λࢀߟʹ42-ͷಡΈղ ͖͕Ͱ͖ΔΑ͏ʹ͢Δ ˞θϩϕʔεͰ42-Λ͔͚Δ༷ʹͳΔඞཁ͸ͳ͍ ΰʔϧ 42-ษڧձʙ࣮ફฤʙ֓ཁઆ໌

Slide 6

Slide 6 text

© )JLBSV5BLBIBTIJ  w ॳڃฤͱಉ༷ʹϋϯζΦϯܗࣜͰ࣮ࢪ w ˞ॳڃฤͰ༻ҙͨ͠؀ڥͰ42-ͷ࣮ߦ؀ڥ͕੔͍ͬͯΔ͜ͱ w ࣮ࡍͷσʔλ෼ੳͰ࢖͏ूܭΛ΋ͱʹܭ໰ͷ࣮ફ໰୊Λ࣮ࢪ w ࣮ફ໰୊Λϕʔεʹجૅ஌ࣝΛઆ໌ޙࣗ෼Ͱߟ͑ͯ42-Λॻ͍͍ͯ͘ελ Πϧ w ໰୊ͷղ౴ྫ΋Ұॹʹࡌ͍ͤͯ·͕͢ɺղ౴ΛΈΔલʹ·ͣ͸ࣗ෼Ͱߟ͑ ͯΈ·͠ΐ͏ ਐΊํ 42-ษڧձʙ࣮ફฤʙ֓ཁઆ໌

Slide 7

Slide 7 text

© )JLBSV5BLBIBTIJ  w 42-ษڧձʙ࣮ફฤʙ֓ཁઆ໌  ର৅ऀ  ΰʔϧ  ਐΊํ ΞδΣϯμ "HFOEB w 42-໰୊ूͷ࣮ફ ղઆ

Slide 8

Slide 8 text

© )JLBSV5BLBIBTIJ  ࣮ફ42-໰୊ूɹબ 42-໰୊ूͷ࣮ફ ղઆ /P ໰୊ ೉қ౓  ੑผ͝ͱͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒ˑˑˑˑ  ೥୅ผͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ೔ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ݄ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  Ϣʔβʔ͝ͱͷ࠷ऴߪೖ೔ߪೖස౓/ߪೖֹۚΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶃ ˒˒˒˒ˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶄ ˒˒˒˒˒  ʮ৯඼ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒ˑ  ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒˒

Slide 9

Slide 9 text

© )JLBSV5BLBIBTIJ  ΧϥϜ໊ ҙຯ ܕ උߟ 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 αϯϓϧσʔλɿ

Slide 10

Slide 10 text

© )JLBSV5BLBIBTIJ  ࣮ફ42-໰୊ूɹબ 42-໰୊ूͷ࣮ફ ղઆ /P ໰୊ ೉қ౓  ੑผ͝ͱͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒ˑˑˑˑ  ೥୅ผͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ೔ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ݄ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  Ϣʔβʔ͝ͱͷ࠷ऴߪೖ೔ߪೖස౓/ߪೖֹۚΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶃ ˒˒˒˒ˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶄ ˒˒˒˒˒  ʮ৯඼ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒ˑ  ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒˒

Slide 11

Slide 11 text

© )JLBSV5BLBIBTIJ ੑผ͝ͱͷਓ਺Λूܭ͍ͯͩ͘͠͞ɻͨͩ͠࡟আࡁϢʔβʔ͸আ֎͍ͯͩ͘͠͞ɻ  ໰୊ ೉қ౓ ˒ˑˑˑˑ ࣮ફ42-໰୊ᶃ ࣮ફ42-໰୊ूɹબ ॴཁ࣌ؒ ෼

Slide 12

Slide 12 text

© )JLBSV5BLBIBTIJ ੑผ͝ͱͷਓ਺Λूܭ͍ͯͩ͘͠͞ɻͨͩ͠࡟আࡁϢʔβʔ͸আ֎͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶃ ࣮ફ42-໰୊ूɹબ ώϯτᶃ VTFSTςʔϒϧʹੑผ͕෼͔ΔΧϥϜ͕ೖͬͯ·͢ ೉қ౓ ˒ˑˑˑˑ ॴཁ࣌ؒ ෼

Slide 13

Slide 13 text

© )JLBSV5BLBIBTIJ ੑผ͝ͱͷਓ਺Λूܭ͍ͯͩ͘͠͞ɻͨͩ͠࡟আࡁϢʔβʔ͸আ֎͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶃ ࣮ફ42-໰୊ूɹબ ώϯτᶃ VTFSTςʔϒϧʹੑผ͕෼͔ΔΧϥϜ͕ೖͬͯ·͢ ώϯτᶄ ಛఆͷଐੑ͝ͱʹΧ΢ϯτ͍ͨ͠৔߹͸(3061#:Λ࢖͍·͠ΐ͏ ೉қ౓ ˒ˑˑˑˑ ॴཁ࣌ؒ ෼

Slide 14

Slide 14 text

© )JLBSV5BLBIBTIJ ੑผ͝ͱͷਓ਺Λूܭ͍ͯͩ͘͠͞ɻͨͩ͠࡟আࡁϢʔβʔ͸আ֎͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶃ ࣮ફ42-໰୊ूɹબ ώϯτᶃ VTFSTςʔϒϧʹੑผ͕෼͔ΔΧϥϜ͕ೖͬͯ·͢ ώϯτᶄ ಛఆͷଐੑ͝ͱʹΧ΢ϯτ͍ͨ͠৔߹͸(3061#:Λ࢖͍·͠ΐ͏ ώϯτᶅ ʰ࡟আࡁϢʔβʔʱ͸VTFSTςʔϒϧͷJT@EFMFUFEϑϥάͰ൑ఆͰ͖·͢ ೉қ౓ ˒ˑˑˑˑ ॴཁ࣌ؒ ෼

Slide 15

Slide 15 text

© )JLBSV5BLBIBTIJ ੑผ͝ͱͷਓ਺Λूܭ͍ͯͩ͘͠͞ɻͨͩ͠࡟আࡁϢʔβʔ͸আ֎͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶃɹղ౴ྫ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒ˑˑˑˑ ॴཁ࣌ؒ ෼ SELECT gender, COUNT(*) FROM users WHERE is_deleted = 0 GROUP BY gender

Slide 16

Slide 16 text

© )JLBSV5BLBIBTIJ ੑผ͝ͱͷਓ਺Λूܭ͍ͯͩ͘͠͞ɻͨͩ͠࡟আࡁϢʔβʔ͸আ֎͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶃɹղ౴ྫ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒ˑˑˑˑ ॴཁ࣌ؒ ෼ SELECT gender, COUNT(*) FROM users WHERE is_deleted = 0 GROUP BY gender w HSPVQCZΛ࢖ͬͯੑผ͝ͱʹάϧʔϐ ϯά w $06/5 Ͱਓ਺Λूܭ $06/5 %*45*/$5VTFS@JE Ͱ΋0, w 8)&3&ͷ৚݅Ͱ࡟আࡁϢʔβʔΛআ֎

Slide 17

Slide 17 text

© )JLBSV5BLBIBTIJ  ࣮ફ42-໰୊ूɹબ 42-໰୊ूͷ࣮ફ ղઆ /P ໰୊ ೉қ౓  ੑผ͝ͱͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒ˑˑˑˑ  ೥୅ผͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ೔ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ݄ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  Ϣʔβʔ͝ͱͷ࠷ऴߪೖ೔ߪೖස౓/ߪೖֹۚΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶃ ˒˒˒˒ˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶄ ˒˒˒˒˒  ʮ৯඼ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒ˑ  ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒˒

Slide 18

Slide 18 text

© )JLBSV5BLBIBTIJ ҎԼͷ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 ݱࡏͷ੢ྐྵ͔Β஀ੜ೥ͷ੢ྐྵΛҾ͖ࢉ͢Δˠࠓ೥ ͷ೥ྸΛऔಘ ؔ਺આ໌

Slide 19

Slide 19 text

© )JLBSV5BLBIBTIJ ҎԼͷ42-Λࢀߟʹ೥୅ผͷਓ਺Λूܭ͍ͯͩ͘͠͞ɻ࡟আࡁϢʔβʔ͸আ֎ͯ͠ɺ೥୅͸10 ୅ɺ20୅ɺ30୅ɺɺɺ80୅ͱ10۠੾ΓͰܭࢉ͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶄ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒ˑˑ ॴཁ࣌ؒ ෼ ώϯτᶃ ·ͣ͸DBTFจΛ࢖ͬͯʮ೥୅ʯͷΧϥϜΛ࡞ͬͯΈ·͠ΐ͏

Slide 20

Slide 20 text

© )JLBSV5BLBIBTIJ ҎԼͷ42-Λࢀߟʹ೥୅ผͷਓ਺Λूܭ͍ͯͩ͘͠͞ɻ࡟আࡁϢʔβʔ͸আ֎ͯ͠ɺ೥୅͸10 ୅ɺ20୅ɺ30୅ɺɺɺ80୅ͱ10۠੾ΓͰܭࢉ͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶄ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒ˑˑ ॴཁ࣌ؒ ෼ ώϯτᶃ ·ͣ͸DBTFจΛ࢖ͬͯʮ೥୅ʯͷΧϥϜΛ࡞ͬͯΈ·͠ΐ͏ ώϯτᶄ 8*5)۟ αϒΫΤϦ Λ࢖ͬͯʮ೥୅ʯΛ௥Ճͨ͠Ұ࣌ςʔϒϧΛ࡞੒ͯ͠ Έ·͠ΐ͏

Slide 21

Slide 21 text

© )JLBSV5BLBIBTIJ ҎԼͷ42-Λࢀߟʹ೥୅ผͷਓ਺Λूܭ͍ͯͩ͘͠͞ɻ࡟আࡁϢʔβʔ͸আ֎ͯ͠ɺ೥୅͸10 ୅ɺ20୅ɺ30୅ɺɺɺ80୅ͱ10۠੾ΓͰܭࢉ͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶄ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒ˑˑ ॴཁ࣌ؒ ෼ ώϯτᶃ ·ͣ͸DBTFจΛ࢖ͬͯʮ೥୅ʯͷΧϥϜΛ࡞ͬͯΈ·͠ΐ͏ ώϯτᶄ 8*5)۟ αϒΫΤϦ Λ࢖ͬͯʮ೥୅ʯΛ௥Ճͨ͠Ұ࣌ςʔϒϧΛ࡞੒ͯ͠ Έ·͠ΐ͏ ώϯτᶅ ʮ೥୅ʯͰ(3061#:ͯ͠Χ΢ϯτͯ͠Έ·͠ΐ͏

Slide 22

Slide 22 text

© )JLBSV5BLBIBTIJ  ࣮ફ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

Slide 23

Slide 23 text

© )JLBSV5BLBIBTIJ  ࣮ફ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&ࣜΛ࢖ͬͯ೥୅ʹม׵

Slide 24

Slide 24 text

© )JLBSV5BLBIBTIJ  ࣮ફ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 ࡟আࡁϢʔβʔ͸আ֎͢Δ

Slide 25

Slide 25 text

© )JLBSV5BLBIBTIJ  ࣮ફ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#:Ͱ೥୅ॱʹฒ΂ସ͑

Slide 26

Slide 26 text

© )JLBSV5BLBIBTIJ  ࣮ફ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  ݱঢ়ͷςʔϒϧͷ֬ೝ ूܭΑ͏ʹ৽͍͠ΧϥϜ ʢ೥୅ʣΛ௥Ճ ௥Ճͨ͠ΧϥϜͰάϧʔϐϯά ͯ͠ूܭ

Slide 27

Slide 27 text

© )JLBSV5BLBIBTIJ  ࣮ફ42-໰୊ूɹબ 42-໰୊ूͷ࣮ફ ղઆ /P ໰୊ ೉қ౓  ੑผ͝ͱͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒ˑˑˑˑ  ೥୅ผͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ೔ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ݄ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  Ϣʔβʔ͝ͱͷ࠷ऴߪೖ೔ߪೖස౓/ߪೖֹۚΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶃ ˒˒˒˒ˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶄ ˒˒˒˒˒  ʮ৯඼ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒ˑ  ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒˒

Slide 28

Slide 28 text

© )JLBSV5BLBIBTIJ ೔ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ɻͨͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎ͯͩ͘͠͞ ͍ɻ  ໰୊ ࣮ફ42-໰୊ᶅ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒ˑˑˑ ॴཁ࣌ؒ ෼

Slide 29

Slide 29 text

© )JLBSV5BLBIBTIJ ೔ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ɻͨͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎ͯͩ͘͠͞ ͍ɻ  ໰୊ ࣮ફ42-໰୊ᶅ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒ˑˑˑ ॴཁ࣌ؒ ෼ ώϯτᶃ ച্Λूܭ͢ΔͨΊʹ͸஫จ৘ใͱ঎඼৘ใΛ݁߹ +0*/ ͤ͞Δඞཁ͕͋ Γ·͢

Slide 30

Slide 30 text

© )JLBSV5BLBIBTIJ ೔ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ɻͨͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎ͯͩ͘͠͞ ͍ɻ  ໰୊ ࣮ફ42-໰୊ᶅ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒ˑˑˑ ॴཁ࣌ؒ ෼ ώϯτᶃ ച্Λूܭ͢ΔͨΊʹ͸஫จ৘ใͱ঎඼৘ใΛ݁߹ +0*/ ͤ͞Δඞཁ͕͋ Γ·͢ ώϯτᶄ ஫จ৘ใ PSEFST ͱ঎඼৘ใ QSPEVDUT ͸঎඼*% QSPEVDU@JE Λ࢖ͬͯ ݁߹Ͱ͖·͢

Slide 31

Slide 31 text

© )JLBSV5BLBIBTIJ ೔ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ɻͨͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎ͯͩ͘͠͞ ͍ɻ  ໰୊ ࣮ફ42-໰୊ᶅ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒ˑˑˑ ॴཁ࣌ؒ ෼ ώϯτᶃ ച্Λूܭ͢ΔͨΊʹ͸஫จ৘ใͱ঎඼৘ใΛ݁߹ +0*/ ͤ͞Δඞཁ͕͋ Γ·͢ ώϯτᶄ ஫จ৘ใ PSEFST ͱ঎඼৘ใ QSPEVDUT ͸঎඼*% QSPEVDU@JE Λ࢖ͬͯ ݁߹Ͱ͖·͢ ώϯτᶅ ೔෇Ͱ(3061#:ͯ͠ച্ͱߪೖ݅਺Λूܭ͠·͠ΐ͏

Slide 32

Slide 32 text

© )JLBSV5BLBIBTIJ  ࣮ફ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

Slide 33

Slide 33 text

© )JLBSV5BLBIBTIJ  ࣮ફ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*/Ͱ΋݁Ռ͸ ಉ͡

Slide 34

Slide 34 text

© )JLBSV5BLBIBTIJ  ࣮ફ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Ͱ஫จΩϟϯηϧͷσʔλΛআ֎

Slide 35

Slide 35 text

© )JLBSV5BLBIBTIJ  ࣮ફ42-໰୊ूɹબ 42-໰୊ूͷ࣮ફ ղઆ /P ໰୊ ೉қ౓  ੑผ͝ͱͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒ˑˑˑˑ  ೥୅ผͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ೔ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ݄ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  Ϣʔβʔ͝ͱͷ࠷ऴߪೖ೔ߪೖස౓/ߪೖֹۚΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶃ ˒˒˒˒ˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶄ ˒˒˒˒˒  ʮ৯඼ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒ˑ  ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒˒

Slide 36

Slide 36 text

© )JLBSV5BLBIBTIJ ݄ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ɻͨͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎ͯͩ͘͠͞ ͍ɻ  ໰୊ ࣮ફ42-໰୊ᶆ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒ˑˑˑ ॴཁ࣌ؒ ෼

Slide 37

Slide 37 text

© )JLBSV5BLBIBTIJ ݄ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ɻͨͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎ͯͩ͘͠͞ ͍ɻ  ໰୊ ࣮ફ42-໰୊ᶆ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒ˑˑˑ ॴཁ࣌ؒ ෼ ώϯτᶃ ʮ݄ʯ৘ใͷऔಘ͸TVCTUSΛ࢖ͬͯ೔෇͔Βʮ݄ʯʹม׵͠·͠ΐ͏

Slide 38

Slide 38 text

© )JLBSV5BLBIBTIJ ݄ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ɻͨͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎ͯͩ͘͠͞ ͍ɻ  ໰୊ ࣮ફ42-໰୊ᶆ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒ˑˑˑ ॴཁ࣌ؒ ෼ ώϯτᶃ ʮ݄ʯ৘ใͷऔಘ͸TVCTUSΛ࢖ͬͯ೔෇͔Βʮ݄ʯʹม׵͠·͠ΐ͏ ώϯτᶄ ʮ݄ʯ৘ใ͸ʮTVCTUS PSEFS@EBUF   ʯͰม׵Ͱ͖·͢

Slide 39

Slide 39 text

© )JLBSV5BLBIBTIJ ݄ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ɻͨͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎ͯͩ͘͠͞ ͍ɻ  ໰୊ ࣮ફ42-໰୊ᶆ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒ˑˑˑ ॴཁ࣌ؒ ෼ ώϯτᶃ ʮ݄ʯ৘ใͷऔಘ͸TVCTUSΛ࢖ͬͯ೔෇͔Βʮ݄ʯʹม׵͠·͠ΐ͏ ώϯτᶄ ʮ݄ʯ৘ใ͸ʮTVCTUS PSEFS@EBUF   ʯͰม׵Ͱ͖·͢ ώϯτᶅ ݄Ͱ(3061#:ͯ͠ച্ͱߪೖ݅਺Λूܭ͠·͠ΐ͏

Slide 40

Slide 40 text

© )JLBSV5BLBIBTIJ  ࣮ફ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

Slide 41

Slide 41 text

© )JLBSV5BLBIBTIJ  ࣮ફ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  

Slide 42

Slide 42 text

© )JLBSV5BLBIBTIJ  ࣮ફ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-ͷ࣮ߦॱংˠ

Slide 43

Slide 43 text

© )JLBSV5BLBIBTIJ  ࣮ફ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#:ͷूܭ͕Մೳ

Slide 44

Slide 44 text

© )JLBSV5BLBIBTIJ  ࣮ફ42-໰୊ूɹબ 42-໰୊ूͷ࣮ફ ղઆ /P ໰୊ ೉қ౓  ੑผ͝ͱͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒ˑˑˑˑ  ೥୅ผͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ೔ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ݄ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  Ϣʔβʔ͝ͱͷ࠷ऴߪೖ೔ߪೖස౓/ߪೖֹۚΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶃ ˒˒˒˒ˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶄ ˒˒˒˒˒  ʮ৯඼ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒ˑ  ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒˒

Slide 45

Slide 45 text

© )JLBSV5BLBIBTIJ Ϣʔβʔ͝ͱͷ࠷ऴߪೖ೔ɺߪೖස౓ ஫จ೔਺ ɺߪೖֹۚΛूܭ͍ͯͩ͘͠͞ 3'.෼ੳ ɻͨ ͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎ͯ͠࡟আࡁϢʔβʔɺະߪೖϢʔβʔ΋আ֎͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶇ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒ˑˑ ॴཁ࣌ؒ ෼

Slide 46

Slide 46 text

© )JLBSV5BLBIBTIJ Ϣʔβʔ͝ͱͷ࠷ऴߪೖ೔ɺߪೖස౓ ஫จ೔਺ ɺߪೖֹۚΛूܭ͍ͯͩ͘͠͞ 3'.෼ੳ ɻͨ ͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎ͯ͠࡟আࡁϢʔβʔɺະߪೖϢʔβʔ΋আ֎͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶇ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒ˑˑ ॴཁ࣌ؒ ෼ ώϯτᶃ VTFSTςʔϒϧͱQSPEVDUTςʔϒϧͱ+0*/ͯͭ͠ςʔϒϧΛ݁߹ͯ͠ Ϣʔβʔ͝ͱͷߪೖσʔλΛ֬ೝ͠·͠ΐ͏

Slide 47

Slide 47 text

© )JLBSV5BLBIBTIJ Ϣʔβʔ͝ͱͷ࠷ऴߪೖ೔ɺߪೖස౓ ஫จ೔਺ ɺߪೖֹۚΛूܭ͍ͯͩ͘͠͞ 3'.෼ੳ ɻͨ ͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎ͯ͠࡟আࡁϢʔβʔɺະߪೖϢʔβʔ΋আ֎͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶇ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒ˑˑ ॴཁ࣌ؒ ෼ ώϯτᶃ VTFSTςʔϒϧͱQSPEVDUTςʔϒϧͱ+0*/ͯͭ͠ςʔϒϧΛ݁߹ͯ͠ Ϣʔβʔ͝ͱͷߪೖσʔλΛ֬ೝ͠·͠ΐ͏ ώϯτᶄ Ϣʔβʔ͝ͱͷूܭΛ͢Δ৔߹͸VTFS@JEͰ(3061#:Λ࢖͍·͠ΐ͏

Slide 48

Slide 48 text

© )JLBSV5BLBIBTIJ Ϣʔβʔ͝ͱͷ࠷ऴߪೖ೔ɺߪೖස౓ ஫จ೔਺ ɺߪೖֹۚΛूܭ͍ͯͩ͘͠͞ 3'.෼ੳ ɻͨ ͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎ͯ͠࡟আࡁϢʔβʔɺະߪೖϢʔβʔ΋আ֎͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶇ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒ˑˑ ॴཁ࣌ؒ ෼ ώϯτᶃ VTFSTςʔϒϧͱQSPEVDUTςʔϒϧͱ+0*/ͯͭ͠ςʔϒϧΛ݁߹ͯ͠ Ϣʔβʔ͝ͱͷߪೖσʔλΛ֬ೝ͠·͠ΐ͏ ώϯτᶄ Ϣʔβʔ͝ͱͷूܭΛ͢Δ৔߹͸VTFS@JEͰ(3061#:Λ࢖͍·͠ΐ͏ ώϯτᶅ ɾ࠷ऴߪೖ೔ˠߪೖ೔ͷ࠷େ஋ ɾߪೖස౓ ஫จ೔਺ ˠߪೖ೔਺ͷΧ΢ϯτ ɾߪೖֹۚˠֹۚͷ߹ܭɹ ͱͯ͠ܭࢉͯ͠Έ·͠ΐ͏

Slide 49

Slide 49 text

© )JLBSV5BLBIBTIJ  ࣮ફ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

Slide 50

Slide 50 text

© )JLBSV5BLBIBTIJ  ࣮ફ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 ࡟আࡁΈϢʔβʔΛআ֎͢ΔͨΊ

Slide 51

Slide 51 text

© )JLBSV5BLBIBTIJ  ࣮ફ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*/$5PPSEFS@EBUF  w .ɿߪೖֹۚˠ46. QQSJDF ˞3'.෼ੳͱ͸3FDFODZʢ࠷ऴߪೖ೔ʣ'SFRVFODZʢߪೖස౓ʣ .POFUBSZʢߪೖֹۚʣͷͭͷࢦඪͰސ٬Λ෼ੳ͢Δख๏

Slide 52

Slide 52 text

© )JLBSV5BLBIBTIJ  ࣮ફ42-໰୊ूɹબ 42-໰୊ूͷ࣮ફ ղઆ /P ໰୊ ೉қ౓  ੑผ͝ͱͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒ˑˑˑˑ  ೥୅ผͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ೔ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ݄ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  Ϣʔβʔ͝ͱͷ࠷ऴߪೖ೔ߪೖස౓/ߪೖֹۚΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶃ ˒˒˒˒ˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶄ ˒˒˒˒˒  ʮ৯඼ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒ˑ  ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒˒

Slide 53

Slide 53 text

© )JLBSV5BLBIBTIJ ೥ؒͷߪೖͰԿ೔ؒߪೖ͕͔͋ͬͨߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ɻ ͨͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎ͯ͠࡟আࡁϢʔβʔͱະߪೖϢʔβʔ΋আ֎ͯͩ͘͠͞ ͍ɻ  ໰୊ ࣮ફ42-໰୊ᶈ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒ˑ ॴཁ࣌ؒ ෼ ˠ ೥ؒͷߪೖ೔਺͕೔ͷϢʔβʔ͕ਓ ˠ ೥ؒͷߪೖ೔਺͕೔ͷϢʔβʔ͕ਓ Ξ΢τϓοτΠϝʔδ ߪೖ೔਺ ߪೖऀ਺    

Slide 54

Slide 54 text

© )JLBSV5BLBIBTIJ ೥ؒͷߪೖͰԿ೔ؒߪೖ͕͔͋ͬͨߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ɻ ͨͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎ͯ͠࡟আࡁϢʔβʔͱະߪೖϢʔβʔ΋আ֎ͯͩ͘͠͞ ͍ɻ  ໰୊ ࣮ફ42-໰୊ᶈ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒ˑ ॴཁ࣌ؒ ෼ ώϯτᶃ Ϣʔβʔ͝ͱʹߪೖස౓ ߪೖ೔਺ ΛΧ΢ϯτ͠·͠ΐ͏

Slide 55

Slide 55 text

© )JLBSV5BLBIBTIJ ೥ؒͷߪೖͰԿ೔ؒߪೖ͕͔͋ͬͨߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ɻ ͨͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎ͯ͠࡟আࡁϢʔβʔͱະߪೖϢʔβʔ΋আ֎ͯͩ͘͠͞ ͍ɻ  ໰୊ ࣮ફ42-໰୊ᶈ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒ˑ ॴཁ࣌ؒ ෼ ώϯτᶃ Ϣʔβʔ͝ͱʹߪೖස౓ ߪೖ೔਺ ΛΧ΢ϯτ͠·͠ΐ͏ ώϯτᶄ 8*5)۟Λ࢖ͬͯҰ࣌ςʔϒϧΛ࡞Δͱ੔ཧ͠΍͘͢ͳΓ·͢ ͦͷࡍ͸ 4&-&$5ͨ݁͠Ռʹ"4ͰΧϥϜ໊Λ͚ͭ·͠ΐ͏

Slide 56

Slide 56 text

© )JLBSV5BLBIBTIJ ೥ؒͷߪೖͰԿ೔ؒߪೖ͕͔͋ͬͨߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ɻ ͨͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎ͯ͠࡟আࡁϢʔβʔͱະߪೖϢʔβʔ΋আ֎ͯͩ͘͠͞ ͍ɻ  ໰୊ ࣮ફ42-໰୊ᶈ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒ˑ ॴཁ࣌ؒ ෼ ώϯτᶃ Ϣʔβʔ͝ͱʹߪೖස౓ ߪೖ೔਺ ΛΧ΢ϯτ͠·͠ΐ͏ ώϯτᶄ 8*5)۟Λ࢖ͬͯҰ࣌ςʔϒϧΛ࡞Δͱ੔ཧ͠΍͘͢ͳΓ·͢ ͦͷࡍ͸ 4&-&$5ͨ݁͠Ռʹ"4ͰΧϥϜ໊Λ͚ͭ·͠ΐ͏ ώϯτᶅ ߪೖස౓ ஫จ೔਺ ͝ͱʹϢʔβʔ਺ΛΧ΢ϯτ͢Δ৔߹͸ߪೖස౓ ஫จ ೔਺ Ͱ(3061#:Λ࢖͍·͠ΐ͏

Slide 57

Slide 57 text

© )JLBSV5BLBIBTIJ  ࣮ફ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

Slide 58

Slide 58 text

© )JLBSV5BLBIBTIJ  ࣮ફ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

Slide 59

Slide 59 text

© )JLBSV5BLBIBTIJ  ࣮ફ42-໰୊ᶈɹղ౴ྫ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒ˑ ॴཁ࣌ؒ ෼ w VTFS@JEͰ(3061#:͢Δ͜ͱͰϢʔβʔຖͷߪೖ೔਺ Λूܭ w ߪೖ೔਺ͷूܭ͸$06/5 %*45*/$5PSEFS@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

Slide 60

Slide 60 text

© )JLBSV5BLBIBTIJ  ࣮ફ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

Slide 61

Slide 61 text

© )JLBSV5BLBIBTIJ  ࣮ફ42-໰୊ᶈɹղઆ ࣮ફ42-໰୊ूɹબ VTFS@JE DPVOU@PSEFS@EBUF "  "  "  "  45&1 ·ͣ͸Ϣʔβʔຖͷߪೖ೔਺Λूܭ͠ɺͦͷޙߪೖ೔਺ͰϢʔβʔ਺Λूܭ͢Δ  45&1  45&1  VTFSTςʔϒϧ ݱঢ়ͷςʔϒϧͷ֬ೝ Ϣʔβʔຖͷߪೖ೔਺Λूܭ ߪೖ೔਺ͰϢʔβʔ਺Λूܭ PSEFSTςʔϒϧ DPVOU@PSEFS@EBUF VTFS@DPVOU      

Slide 62

Slide 62 text

© )JLBSV5BLBIBTIJ  ࣮ફ42-໰୊ूɹબ 42-໰୊ूͷ࣮ફ ղઆ /P ໰୊ ೉қ౓  ੑผ͝ͱͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒ˑˑˑˑ  ೥୅ผͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ೔ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ݄ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  Ϣʔβʔ͝ͱͷ࠷ऴߪೖ೔ߪೖස౓/ߪೖֹۚΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶃ ˒˒˒˒ˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶄ ˒˒˒˒˒  ʮ৯඼ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒ˑ  ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒˒

Slide 63

Slide 63 text

© )JLBSV5BLBIBTIJ ೥ؒͷߪೖͰԿ೔ؒߪೖ͕͔͋ͬͨߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ɻ ͨͩ͠ະߪೖϢʔβʔ΋ؚΊͨߪೖ೔਺ΛΧ΢ϯτͯ͠ɺ஫จΩϟϯηϧͷσʔλ͸আ֎ɺ࡟আ ࡁΈϢʔβʔ͸আ֎ͯ͠Լ͍͞ɻ  ໰୊ ࣮ફ42-໰୊ᶉ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒˒ ॴཁ࣌ؒ ෼ ˠ ೥ؒͷߪೖ೔਺͕೔ͷϢʔβʔ͕ਓ ˠ ೥ؒͷߪೖ೔਺͕೔ͷϢʔβʔ͕ਓ Ξ΢τϓοτΠϝʔδ ߪೖ೔਺ ߪೖऀ਺     ʢະߪೖϢʔβʔʣ

Slide 64

Slide 64 text

© )JLBSV5BLBIBTIJ ೥ؒͷߪೖͰԿ೔ؒߪೖ͕͔͋ͬͨߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ɻ ͨͩ͠ະߪೖϢʔβʔ΋ؚΊͨߪೖ೔਺ΛΧ΢ϯτͯ͠ɺ஫จΩϟϯηϧͷσʔλ͸আ֎ɺ࡟আ ࡁΈϢʔβʔ͸আ֎ͯ͠Լ͍͞ɻ  ໰୊ ࣮ફ42-໰୊ᶉ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒˒ ॴཁ࣌ؒ ෼ ώϯτᶃ ߟ͑ํ͸໰୊ᶈͱಉ͡Ͱ·ͣ͸Ϣʔβʔ͝ͱͷߪೖ೔਺ΛΧ΢ϯτ͠· ͠ΐ͏

Slide 65

Slide 65 text

© )JLBSV5BLBIBTIJ ೥ؒͷߪೖͰԿ೔ؒߪೖ͕͔͋ͬͨߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ɻ ͨͩ͠ະߪೖϢʔβʔ΋ؚΊͨߪೖ೔਺ΛΧ΢ϯτͯ͠ɺ஫จΩϟϯηϧͷσʔλ͸আ֎ɺ࡟আ ࡁΈϢʔβʔ͸আ֎ͯ͠Լ͍͞ɻ  ໰୊ ࣮ફ42-໰୊ᶉ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒˒ ॴཁ࣌ؒ ෼ ώϯτᶃ ߟ͑ํ͸໰୊ᶈͱಉ͡Ͱ·ͣ͸Ϣʔβʔ͝ͱͷߪೖ೔਺ΛΧ΢ϯτ͠· ͠ΐ͏ ώϯτᶄ ߪೖ͕ͳ͔ͬͨϢʔβʔ΋Χ΢ϯτ͢ΔͷͰɺVTFSTΛϕʔεʹσʔλΛऔ ಘ͠·͠ΐ͏

Slide 66

Slide 66 text

© )JLBSV5BLBIBTIJ ೥ؒͷߪೖͰԿ೔ؒߪೖ͕͔͋ͬͨߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ɻ ͨͩ͠ະߪೖϢʔβʔ΋ؚΊͨߪೖ೔਺ΛΧ΢ϯτͯ͠ɺ஫จΩϟϯηϧͷσʔλ͸আ֎ɺ࡟আ ࡁΈϢʔβʔ͸আ֎ͯ͠Լ͍͞ɻ  ໰୊ ࣮ફ42-໰୊ᶉ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒˒ ॴཁ࣌ؒ ෼ ώϯτᶃ ߟ͑ํ͸໰୊ᶈͱಉ͡Ͱ·ͣ͸Ϣʔβʔ͝ͱͷߪೖ೔਺ΛΧ΢ϯτ͠· ͠ΐ͏ ώϯτᶄ ߪೖ͕ͳ͔ͬͨϢʔβʔ΋Χ΢ϯτ͢ΔͷͰɺVTFSTΛϕʔεʹσʔλΛऔ ಘ͠·͠ΐ͏ ώϯτᶅ ߪೖස౓ ஫จ೔਺ ͝ͱʹϢʔβʔ਺ΛΧ΢ϯτ͢Δ৔߹͸ߪೖස౓ ஫จ ೔਺ Ͱ(3061#:Λ࢖͍·͠ΐ͏

Slide 67

Slide 67 text

© )JLBSV5BLBIBTIJ  ࣮ફ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

Slide 68

Slide 68 text

© )JLBSV5BLBIBTIJ  ࣮ફ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ͷ৔߹΋৚݅ʹؚΊΔ

Slide 69

Slide 69 text

© )JLBSV5BLBIBTIJ  ࣮ફ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*/

Slide 70

Slide 70 text

© )JLBSV5BLBIBTIJ  ࣮ફ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

Slide 71

Slide 71 text

© )JLBSV5BLBIBTIJ  ࣮ફ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#:Λͯ͠Ϣʔ βʔ਺ΛΧ΢ϯτ͢Δ

Slide 72

Slide 72 text

© )JLBSV5BLBIBTIJ  ࣮ફ42-໰୊ूɹબ 42-໰୊ूͷ࣮ફ ղઆ /P ໰୊ ೉қ౓  ੑผ͝ͱͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒ˑˑˑˑ  ೥୅ผͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ೔ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ݄ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  Ϣʔβʔ͝ͱͷ࠷ऴߪೖ೔ߪೖස౓/ߪೖֹۚΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶃ ˒˒˒˒ˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶄ ˒˒˒˒˒  ʮ৯඼ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒ˑ  ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒˒

Slide 73

Slide 73 text

© )JLBSV5BLBIBTIJ ೥݄೔ʹʮ৯඼ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ɻͨ ͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶊ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒ˑˑˑ ॴཁ࣌ؒ ෼

Slide 74

Slide 74 text

© )JLBSV5BLBIBTIJ ೥݄೔ʹʮ৯඼ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ɻͨ ͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶊ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒ˑˑˑ ॴཁ࣌ؒ ෼ ώϯτᶃ ஫จ৘ใͱ঎඼৘ใΛ݁߹͠·͠ΐ͏

Slide 75

Slide 75 text

© )JLBSV5BLBIBTIJ ೥݄೔ʹʮ৯඼ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ɻͨ ͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶊ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒ˑˑˑ ॴཁ࣌ؒ ෼ ώϯτᶃ ஫จ৘ใͱ঎඼৘ใΛ݁߹͠·͠ΐ͏ ώϯτᶄ ෳ਺ͷ৚݅Λ͚ͭΔ৔߹͸8)&3&ʙ"/%Λ࢖͍·͠ΐ͏

Slide 76

Slide 76 text

© )JLBSV5BLBIBTIJ ೥݄೔ʹʮ৯඼ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ɻͨ ͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶊ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒ˑˑˑ ॴཁ࣌ؒ ෼ ώϯτᶃ ஫จ৘ใͱ঎඼৘ใΛ݁߹͠·͠ΐ͏ ώϯτᶄ ෳ਺ͷ৚݅Λ͚ͭΔ৔߹͸8)&3&ʙ"/%Λ࢖͍·͠ΐ͏ ώϯτᶅ ʮ৯඼ʯ͸MBSHF@DBUFHPSZͰ൑ఆ͠·͠ΐ͏

Slide 77

Slide 77 text

© )JLBSV5BLBIBTIJ  ࣮ફ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 = '৯඼'

Slide 78

Slide 78 text

© )JLBSV5BLBIBTIJ  ࣮ફ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@DBUFHPSZb৯඼`Ͱ൑ఆ͠·͢

Slide 79

Slide 79 text

© )JLBSV5BLBIBTIJ  ࣮ફ42-໰୊ᶊɹղ౴ྫɹ5*14 ࣮ફ42-໰୊ूɹબ SELECT DISTINCT large_category FROM products w MBSHF@DBUFHPSZʹͲΜͳ஋͕ೖ͍ͬͯΔͷ͔֬ೝͨ͠ ͍৔߹ɺʮ%*45*/$5ʯΛ࢖ͬͯMBSHF@DBUFHPSZͷϢ χʔΫͳ஋ΛҰཡͰ֬ೝ͢Δࣄ͕Ͱ͖·͢ w ͜ΕͰMBSHF@DBUFHPSZʹʮ৯඼ʯͷσʔλ͕ೖ͍ͬͯ Δࣄ͕֬ೝͰ͖·͢ σʔλ෼ੳͷࡍʹΑ֬͘ೝ͠·͢ MBSHF@DBUFHPSZ ৯඼ ೔༻඼ ΠϯςϦΞ ϑΝογϣϯ ిԽ੡඼ ग़ྗ݁Ռ

Slide 80

Slide 80 text

© )JLBSV5BLBIBTIJ  ࣮ફ42-໰୊ूɹબ 42-໰୊ूͷ࣮ફ ղઆ /P ໰୊ ೉қ౓  ੑผ͝ͱͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒ˑˑˑˑ  ೥୅ผͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ೔ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ݄ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  Ϣʔβʔ͝ͱͷ࠷ऴߪೖ೔ߪೖස౓/ߪೖֹۚΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶃ ˒˒˒˒ˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶄ ˒˒˒˒˒  ʮ৯඼ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒ˑ  ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒˒

Slide 81

Slide 81 text

© )JLBSV5BLBIBTIJ  ࣮ફ42-໰୊ᶋ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒ˑ ॴཁ࣌ؒ ෼ ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔ɺΧςΰϦ͝ͱʹߪೖऀ਺Λநग़ͯ͠Լ͍͞ɻ ͨͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎͍ͯͩ͘͠͞ɻ·ͨதΧςΰϦ NFEJVN@DBUFHPSZ ͝ ͱͷߪೖऀ͕ଟ͍ॱʹ݁ՌΛग़ྗ͍ͯͩ͘͠͞ɻ ໰୊

Slide 82

Slide 82 text

© )JLBSV5BLBIBTIJ  ࣮ફ42-໰୊ᶋ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒ˑ ॴཁ࣌ؒ ෼ ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔ɺΧςΰϦ͝ͱʹߪೖऀ਺Λநग़ͯ͠Լ͍͞ɻ ͨͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎͍ͯͩ͘͠͞ɻ·ͨதΧςΰϦ NFEJVN@DBUFHPSZ ͝ ͱͷߪೖऀ͕ଟ͍ॱʹ݁ՌΛग़ྗ͍ͯͩ͘͠͞ɻ ໰୊ ώϯτᶃ 8*5)۟Λ࢖ͬͯʮඒ༰ΛങͬͨਓʯͷҰ࣌ςʔϒϧΛ࡞੒͠·͠ΐ͏

Slide 83

Slide 83 text

© )JLBSV5BLBIBTIJ  ࣮ફ42-໰୊ᶋ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒ˑ ॴཁ࣌ؒ ෼ ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔ɺΧςΰϦ͝ͱʹߪೖऀ਺Λநग़ͯ͠Լ͍͞ɻ ͨͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎͍ͯͩ͘͠͞ɻ·ͨதΧςΰϦ NFEJVN@DBUFHPSZ ͝ ͱͷߪೖऀ͕ଟ͍ॱʹ݁ՌΛग़ྗ͍ͯͩ͘͠͞ɻ ໰୊ ώϯτᶃ 8*5)۟Λ࢖ͬͯʮඒ༰ΛങͬͨਓʯͷҰ࣌ςʔϒϧΛ࡞੒͠·͠ΐ͏ ώϯτᶄ ʮඒ༰Λങͬͨਓʯ"/%ʮඒ༰Ҏ֎ͷ঎඼ʯͱͭͷ৚݅Λ෇͚ͯσʔλ Λऔಘ͠·͠ΐ͏

Slide 84

Slide 84 text

© )JLBSV5BLBIBTIJ  ࣮ફ42-໰୊ᶋ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒ˑ ॴཁ࣌ؒ ෼ ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔ɺΧςΰϦ͝ͱʹߪೖऀ਺Λநग़ͯ͠Լ͍͞ɻ ͨͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎͍ͯͩ͘͠͞ɻ·ͨதΧςΰϦ NFEJVN@DBUFHPSZ ͝ ͱͷߪೖऀ͕ଟ͍ॱʹ݁ՌΛग़ྗ͍ͯͩ͘͠͞ɻ ໰୊ ώϯτᶃ 8*5)۟Λ࢖ͬͯʮඒ༰ΛങͬͨਓʯͷҰ࣌ςʔϒϧΛ࡞੒͠·͠ΐ͏ ώϯτᶄ ʮඒ༰Λങͬͨਓʯ"/%ʮඒ༰Ҏ֎ͷ঎඼ʯͱͭͷ৚݅Λ෇͚ͯσʔλ Λऔಘ͠·͠ΐ͏ ώϯτᶅ ࠷ऴతʹ͸NFEJVN@DBUFHPSZͰ(3061#:ͯ͠ߪೖऀ VTFS@JE ΛΧ΢ ϯτ͠·͠ΐ͏

Slide 85

Slide 85 text

© )JLBSV5BLBIBTIJ  ࣮ફ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

Slide 86

Slide 86 text

© )JLBSV5BLBIBTIJ  ࣮ફ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-ͱ΄΅ಉ͡

Slide 87

Slide 87 text

© )JLBSV5BLBIBTIJ  ࣮ફ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 ඒ༰Ҏ֎ͷ঎඼Λநग़͢Δ৔߹͸ʮʯΛ࢖ͬͯಛఆ ͷΧςΰϦҎ֎ͷσʔλΛநग़͢Δ

Slide 88

Slide 88 text

© )JLBSV5BLBIBTIJ  ࣮ફ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ΛϢ χʔΫΧ΢ϯτ͢Δ

Slide 89

Slide 89 text

© )JLBSV5BLBIBTIJ  ࣮ફ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 จ๪۩    ҿྉਫ    ໺ࡊ   

Slide 90

Slide 90 text

© )JLBSV5BLBIBTIJ  ࣮ફ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 จ๪۩    ҿྉਫ    ໺ࡊ   

Slide 91

Slide 91 text

© )JLBSV5BLBIBTIJ  ࣮ફ42-໰୊ूɹબ 42-໰୊ूͷ࣮ફ ղઆ /P ໰୊ ೉қ౓  ੑผ͝ͱͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒ˑˑˑˑ  ೥୅ผͷਓ਺Λूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ೔ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ݄ผͷച্ͱߪೖ݅਺Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ  Ϣʔβʔ͝ͱͷ࠷ऴߪೖ೔ߪೖස౓/ߪೖֹۚΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶃ ˒˒˒˒ˑ  ߪೖස౓(஫จ೔਺)͝ͱʹϢʔβʔ਺Λूܭ͍ͯͩ͘͠͞ᶄ ˒˒˒˒˒  ʮ৯඼ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ ˒˒ˑˑˑ  ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒ˑ  ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒˒

Slide 92

Slide 92 text

© )JLBSV5BLBIBTIJ ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔঎඼ผͷϥϯΩϯά ߪೖ݅਺͕ଟ͍ॱ Λநग़ͯ͠Լ͞ ͍ɻͨͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎ͯ͠ɺVTFS@JE͕ۭͷσʔλ΋আ֎͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶌ ࣮ફ42-໰୊ूɹબ ೉қ౓ ˒˒˒˒˒ ॴཁ࣌ؒ ෼

Slide 93

Slide 93 text

© )JLBSV5BLBIBTIJ ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔঎඼ผͷϥϯΩϯά ߪೖ݅਺͕ଟ͍ॱ Λநग़ͯ͠Լ͞ ͍ɻͨͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎ͯ͠ɺVTFS@JE͕ۭͷσʔλ΋আ֎͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶌ ࣮ફ42-໰୊ूɹબ ೉қ౓ ॴཁ࣌ؒ ෼ ώϯτᶃ Ϣʔβʔ͝ͱͷॳճ஫จ*%Λநग़ͯ͠ɺͦͷ஫จ*%ͷσʔλΛநग़͢Ε͹ ॳճߪೖͷߪೖσʔλ͕நग़Ͱ͖·͢ ˒˒˒˒˒

Slide 94

Slide 94 text

© )JLBSV5BLBIBTIJ ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔঎඼ผͷϥϯΩϯά ߪೖ݅਺͕ଟ͍ॱ Λநग़ͯ͠Լ͞ ͍ɻͨͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎ͯ͠ɺVTFS@JE͕ۭͷσʔλ΋আ֎͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶌ ࣮ફ42-໰୊ूɹબ ೉қ౓ ॴཁ࣌ؒ ෼ ώϯτᶃ Ϣʔβʔ͝ͱͷॳճ஫จ*%Λநग़ͯ͠ɺͦͷ஫จ*%ͷσʔλΛநग़͢Ε͹ ॳճߪೖͷߪೖσʔλ͕நग़Ͱ͖·͢ ώϯτᶄ ʮॳճߪೖʯʮϢʔβʔ୯ҐͰ஫จ൪߸͕Ұ൪খ͍͞஫จʯ ˒˒˒˒˒

Slide 95

Slide 95 text

© )JLBSV5BLBIBTIJ ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔঎඼ผͷϥϯΩϯά ߪೖ݅਺͕ଟ͍ॱ Λநग़ͯ͠Լ͞ ͍ɻͨͩ͠஫จΩϟϯηϧͷσʔλ͸আ֎ͯ͠ɺVTFS@JE͕ۭͷσʔλ΋আ֎͍ͯͩ͘͠͞ɻ  ໰୊ ࣮ફ42-໰୊ᶌ ࣮ફ42-໰୊ूɹબ ೉қ౓ ॴཁ࣌ؒ ෼ ώϯτᶃ Ϣʔβʔ͝ͱͷॳճ஫จ*%Λநग़ͯ͠ɺͦͷ஫จ*%ͷσʔλΛநग़͢Ε͹ ॳճߪೖͷߪೖσʔλ͕நग़Ͱ͖·͢ ώϯτᶄ ʮॳճߪೖʯʮϢʔβʔ୯ҐͰ஫จ൪߸͕Ұ൪খ͍͞஫จʯ ώϯτᶅ ʮ஫จ൪߸͕Ұ൪খ͍͞ʯΛ൑ఆ͢ΔͨΊʹ.*/ؔ਺Λ࢖͍·͠ΐ͏ ˒˒˒˒˒

Slide 96

Slide 96 text

© )JLBSV5BLBIBTIJ  ࣮ફ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 ˒˒˒˒˒

Slide 97

Slide 97 text

© )JLBSV5BLBIBTIJ  ࣮ફ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 ʯʹΑͬͯϢʔβʔ͝ͱʹҰ൪খ͞ ͍஫จ*%ॳճͷ஫จ*%Λऔಘ͢Δ

Slide 98

Slide 98 text

© )JLBSV5BLBIBTIJ  ࣮ફ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 ʮ*/ʯΛ࢖ͬͯॳճߪೖͷ஫จ*%ͷΈΛநग़

Slide 99

Slide 99 text

© )JLBSV5BLBIBTIJ  ࣮ફ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 ʯͰ΋ಉ͡

Slide 100

Slide 100 text

© )JLBSV5BLBIBTIJ  ࣮ફ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Ґɾɾɾʣ

Slide 101

Slide 101 text

© )JLBSV5BLBIBTIJ  ࣮ફ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. " $    " $    " $    " $    ݩͷςʔϒϧͷߦΛҡ࣋ͨ͠··ಛఆͷΧϥϜͰάϧʔϐϯ άͨ݁͠ՌΛ෇༩͢Δ͜ͱ͕Ͱ͖Δ ݩςʔϒϧ Πϝʔδ

Slide 102

Slide 102 text

© )JLBSV5BLBIBTIJ  ࣮ફ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#: ۩ମྫ

Slide 103

Slide 103 text

&/%