Share
Embed
データ分析のためのSQL勉強会 〜実践編〜
hikarut
March 04, 2024
Transcript
103 slides
-
© )JLBSV5BLBIBTIJ w 42-ษڧձʙ࣮ફฤʙ֓ཁઆ໌ ରऀ ΰʔϧ
ਐΊํ ΞδΣϯμ "HFOEB w 42-ूͷ࣮ફ ղઆ -
© )JLBSV5BLBIBTIJ w 42-ษڧձʙ࣮ફฤʙ֓ཁઆ໌ ରऀ ΰʔϧ
ਐΊํ ΞδΣϯμ "HFOEB w 42-ूͷ࣮ફ ղઆ -
© )JLBSV5BLBIBTIJ w42-ͷجૅΛཧղ͍ͯ͠Δਓ w ʰ42-ษڧձʙॳڃฤʙʱΛडߨ͍ͯ͠Δਓ w ʰσʔλੳྗΛߴΊΔϏδωεύʔιϯͷͨΊͷ42-ೖॻʱΛಡΜͩਓ w࣮ͰσʔλੳΛ͑ΔΑ͏ʹͳΓ͍ͨਓ ˞ࠓճʮσʔλੳͷͨΊͷʯ42-ษڧձͰ͢
˞ΞϓϦέʔγϣϯ։ൃͳͲͰ͏42-ఆ͍ͯ͠ͳ͍ͷͰσʔλऔ ಘ 4&-&$5จ ͕த৺ʹͳ͍ͬͯ·͢ ରऀ 42-ษڧձʙ࣮ફฤʙ֓ཁઆ໌ -
© )JLBSV5BLBIBTIJ wσʔλੳͷ࣮Ͱ͏42-Λཧղ͢Δ wσʔλੳͷ࣮Ͱ͏42-ΛࣗͰॻ͚ΔΑ͏ʹ͢Δ w ௐ͔͚ͯΔ༷ʹ͢Δ w ଞͷਓ͕ॻ͍ͨ42- ͕ࣗաڈʹॻ͍ͨ42-
Λࢀߟʹ42-ͷಡΈղ ͖͕Ͱ͖ΔΑ͏ʹ͢Δ ˞θϩϕʔεͰ42-Λ͔͚Δ༷ʹͳΔඞཁͳ͍ ΰʔϧ 42-ษڧձʙ࣮ફฤʙ֓ཁઆ໌ -
© )JLBSV5BLBIBTIJ w ॳڃฤͱಉ༷ʹϋϯζΦϯܗࣜͰ࣮ࢪ w ˞ॳڃฤͰ༻ҙͨ͠ڥͰ42-ͷ࣮ߦڥ͕͍ͬͯΔ͜ͱ w ࣮ࡍͷσʔλੳͰ͏ूܭΛͱʹܭͷ࣮ફΛ࣮ࢪ w
࣮ફΛϕʔεʹجૅࣝΛઆ໌ޙࣗͰߟ͑ͯ42-Λॻ͍͍ͯ͘ελ Πϧ w ͷղྫҰॹʹࡌ͍ͤͯ·͕͢ɺղΛΈΔલʹ·ͣࣗͰߟ͑ ͯΈ·͠ΐ͏ ਐΊํ 42-ษڧձʙ࣮ફฤʙ֓ཁઆ໌ -
© )JLBSV5BLBIBTIJ w 42-ษڧձʙ࣮ફฤʙ֓ཁઆ໌ ରऀ ΰʔϧ
ਐΊํ ΞδΣϯμ "HFOEB w 42-ूͷ࣮ફ ղઆ -
© )JLBSV5BLBIBTIJ ࣮ફ42-ूɹબ 42-ूͷ࣮ફ ղઆ /P қ
ੑผ͝ͱͷਓΛूܭ͍ͯͩ͘͠͞ ˒ˑˑˑˑ ผͷਓΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ ผͷച্ͱߪೖ݅Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ ݄ผͷച্ͱߪೖ݅Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ Ϣʔβʔ͝ͱͷ࠷ऴߪೖߪೖස/ߪೖֹۚΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ ߪೖස(จ)͝ͱʹϢʔβʔΛूܭ͍ͯͩ͘͠͞ᶃ ˒˒˒˒ˑ ߪೖස(จ)͝ͱʹϢʔβʔΛूܭ͍ͯͩ͘͠͞ᶄ ˒˒˒˒˒ ʮ৯ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ ˒˒ˑˑˑ ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒ˑ ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒˒ -
© )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 αϯϓϧσʔλɿ -
© )JLBSV5BLBIBTIJ ࣮ફ42-ूɹબ 42-ूͷ࣮ફ ղઆ /P қ
ੑผ͝ͱͷਓΛूܭ͍ͯͩ͘͠͞ ˒ˑˑˑˑ ผͷਓΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ ผͷച্ͱߪೖ݅Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ ݄ผͷച্ͱߪೖ݅Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ Ϣʔβʔ͝ͱͷ࠷ऴߪೖߪೖස/ߪೖֹۚΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ ߪೖස(จ)͝ͱʹϢʔβʔΛूܭ͍ͯͩ͘͠͞ᶃ ˒˒˒˒ˑ ߪೖස(จ)͝ͱʹϢʔβʔΛूܭ͍ͯͩ͘͠͞ᶄ ˒˒˒˒˒ ʮ৯ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ ˒˒ˑˑˑ ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒ˑ ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒˒ -
© )JLBSV5BLBIBTIJ ੑผ͝ͱͷਓΛूܭ͍ͯͩ͘͠͞ɻͨͩ͠আࡁϢʔβʔআ֎͍ͯͩ͘͠͞ɻ ࣮ફ42-ᶃ ࣮ફ42-ूɹબ ώϯτᶃ VTFSTςʔϒϧʹੑผ͕͔ΔΧϥϜ͕ೖͬͯ·͢ ώϯτᶄ
ಛఆͷଐੑ͝ͱʹΧϯτ͍ͨ͠߹(3061#:Λ͍·͠ΐ͏ қ ˒ˑˑˑˑ ॴཁ࣌ؒ -
© )JLBSV5BLBIBTIJ ੑผ͝ͱͷਓΛूܭ͍ͯͩ͘͠͞ɻͨͩ͠আࡁϢʔβʔআ֎͍ͯͩ͘͠͞ɻ ࣮ફ42-ᶃ ࣮ફ42-ूɹબ ώϯτᶃ VTFSTςʔϒϧʹੑผ͕͔ΔΧϥϜ͕ೖͬͯ·͢ ώϯτᶄ
ಛఆͷଐੑ͝ͱʹΧϯτ͍ͨ͠߹(3061#:Λ͍·͠ΐ͏ ώϯτᶅ ʰআࡁϢʔβʔʱVTFSTςʔϒϧͷJT@EFMFUFEϑϥάͰఆͰ͖·͢ қ ˒ˑˑˑˑ ॴཁ࣌ؒ -
© )JLBSV5BLBIBTIJ ੑผ͝ͱͷਓΛूܭ͍ͯͩ͘͠͞ɻͨͩ͠আࡁϢʔβʔআ֎͍ͯͩ͘͠͞ɻ ࣮ફ42-ᶃɹղྫ ࣮ફ42-ूɹબ қ ˒ˑˑˑˑ ॴཁ࣌ؒ
SELECT gender, COUNT(*) FROM users WHERE is_deleted = 0 GROUP BY gender -
© )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&ͷ݅ͰআࡁϢʔβʔΛআ֎ -
© )JLBSV5BLBIBTIJ ࣮ફ42-ूɹબ 42-ूͷ࣮ફ ղઆ /P қ
ੑผ͝ͱͷਓΛूܭ͍ͯͩ͘͠͞ ˒ˑˑˑˑ ผͷਓΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ ผͷച্ͱߪೖ݅Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ ݄ผͷച্ͱߪೖ݅Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ Ϣʔβʔ͝ͱͷ࠷ऴߪೖߪೖස/ߪೖֹۚΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ ߪೖස(จ)͝ͱʹϢʔβʔΛूܭ͍ͯͩ͘͠͞ᶃ ˒˒˒˒ˑ ߪೖස(จ)͝ͱʹϢʔβʔΛूܭ͍ͯͩ͘͠͞ᶄ ˒˒˒˒˒ ʮ৯ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ ˒˒ˑˑˑ ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒ˑ ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒˒ -
© )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 ݱࡏͷྐྵ͔ΒੜͷྐྵΛҾ͖ࢉ͢Δˠࠓ ͷྸΛऔಘ ؔઆ໌ -
© )JLBSV5BLBIBTIJ ҎԼͷ42-ΛࢀߟʹผͷਓΛूܭ͍ͯͩ͘͠͞ɻআࡁϢʔβʔআ֎ͯ͠ɺ10 ɺ20ɺ30ɺɺɺ80ͱ10۠ΓͰܭࢉ͍ͯͩ͘͠͞ɻ ࣮ફ42-ᶄ ࣮ફ42-ूɹબ қ ˒˒˒ˑˑ
ॴཁ࣌ؒ ώϯτᶃ ·ͣDBTFจΛͬͯʮʯͷΧϥϜΛ࡞ͬͯΈ·͠ΐ͏ -
© )JLBSV5BLBIBTIJ ҎԼͷ42-ΛࢀߟʹผͷਓΛूܭ͍ͯͩ͘͠͞ɻআࡁϢʔβʔআ֎ͯ͠ɺ10 ɺ20ɺ30ɺɺɺ80ͱ10۠ΓͰܭࢉ͍ͯͩ͘͠͞ɻ ࣮ફ42-ᶄ ࣮ફ42-ूɹબ қ ˒˒˒ˑˑ
ॴཁ࣌ؒ ώϯτᶃ ·ͣDBTFจΛͬͯʮʯͷΧϥϜΛ࡞ͬͯΈ·͠ΐ͏ ώϯτᶄ 8*5)۟ αϒΫΤϦ ΛͬͯʮʯΛՃͨ͠Ұ࣌ςʔϒϧΛ࡞ͯ͠ Έ·͠ΐ͏ -
© )JLBSV5BLBIBTIJ ҎԼͷ42-ΛࢀߟʹผͷਓΛूܭ͍ͯͩ͘͠͞ɻআࡁϢʔβʔআ֎ͯ͠ɺ10 ɺ20ɺ30ɺɺɺ80ͱ10۠ΓͰܭࢉ͍ͯͩ͘͠͞ɻ ࣮ફ42-ᶄ ࣮ફ42-ूɹબ қ ˒˒˒ˑˑ
ॴཁ࣌ؒ ώϯτᶃ ·ͣDBTFจΛͬͯʮʯͷΧϥϜΛ࡞ͬͯΈ·͠ΐ͏ ώϯτᶄ 8*5)۟ αϒΫΤϦ ΛͬͯʮʯΛՃͨ͠Ұ࣌ςʔϒϧΛ࡞ͯ͠ Έ·͠ΐ͏ ώϯτᶅ ʮʯͰ(3061#:ͯ͠Χϯτͯ͠Έ·͠ΐ͏ -
© )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 -
© )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&ࣜΛͬͯʹม -
© )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 আࡁϢʔβʔআ֎͢Δ -
© )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#:Ͱॱʹฒସ͑ -
© )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 ݱঢ়ͷςʔϒϧͷ֬ೝ ूܭΑ͏ʹ৽͍͠ΧϥϜ ʢʣΛՃ Ճͨ͠ΧϥϜͰάϧʔϐϯά ͯ͠ूܭ -
© )JLBSV5BLBIBTIJ ࣮ફ42-ूɹબ 42-ूͷ࣮ફ ղઆ /P қ
ੑผ͝ͱͷਓΛूܭ͍ͯͩ͘͠͞ ˒ˑˑˑˑ ผͷਓΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ ผͷച্ͱߪೖ݅Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ ݄ผͷച্ͱߪೖ݅Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ Ϣʔβʔ͝ͱͷ࠷ऴߪೖߪೖස/ߪೖֹۚΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ ߪೖස(จ)͝ͱʹϢʔβʔΛूܭ͍ͯͩ͘͠͞ᶃ ˒˒˒˒ˑ ߪೖස(จ)͝ͱʹϢʔβʔΛूܭ͍ͯͩ͘͠͞ᶄ ˒˒˒˒˒ ʮ৯ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ ˒˒ˑˑˑ ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒ˑ ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒˒ -
© )JLBSV5BLBIBTIJ ผͷച্ͱߪೖ݅Λूܭ͍ͯͩ͘͠͞ɻͨͩ͠จΩϟϯηϧͷσʔλআ֎ͯͩ͘͠͞ ͍ɻ ࣮ફ42-ᶅ ࣮ફ42-ूɹબ қ ˒˒ˑˑˑ
ॴཁ࣌ؒ ώϯτᶃ ച্Λूܭ͢ΔͨΊʹจใͱใΛ݁߹ +0*/ ͤ͞Δඞཁ͕͋ Γ·͢ -
© )JLBSV5BLBIBTIJ ผͷച্ͱߪೖ݅Λूܭ͍ͯͩ͘͠͞ɻͨͩ͠จΩϟϯηϧͷσʔλআ֎ͯͩ͘͠͞ ͍ɻ ࣮ફ42-ᶅ ࣮ફ42-ूɹબ қ ˒˒ˑˑˑ
ॴཁ࣌ؒ ώϯτᶃ ച্Λूܭ͢ΔͨΊʹจใͱใΛ݁߹ +0*/ ͤ͞Δඞཁ͕͋ Γ·͢ ώϯτᶄ จใ PSEFST ͱใ QSPEVDUT *% QSPEVDU@JE Λͬͯ ݁߹Ͱ͖·͢ -
© )JLBSV5BLBIBTIJ ผͷച্ͱߪೖ݅Λूܭ͍ͯͩ͘͠͞ɻͨͩ͠จΩϟϯηϧͷσʔλআ֎ͯͩ͘͠͞ ͍ɻ ࣮ફ42-ᶅ ࣮ફ42-ूɹબ қ ˒˒ˑˑˑ
ॴཁ࣌ؒ ώϯτᶃ ച্Λूܭ͢ΔͨΊʹจใͱใΛ݁߹ +0*/ ͤ͞Δඞཁ͕͋ Γ·͢ ώϯτᶄ จใ PSEFST ͱใ QSPEVDUT *% QSPEVDU@JE Λͬͯ ݁߹Ͱ͖·͢ ώϯτᶅ Ͱ(3061#:ͯ͠ച্ͱߪೖ݅Λूܭ͠·͠ΐ͏ -
© )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 -
© )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*/Ͱ݁Ռ ಉ͡ -
© )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ͰจΩϟϯηϧͷσʔλΛআ֎ -
© )JLBSV5BLBIBTIJ ࣮ફ42-ूɹબ 42-ूͷ࣮ફ ղઆ /P қ
ੑผ͝ͱͷਓΛूܭ͍ͯͩ͘͠͞ ˒ˑˑˑˑ ผͷਓΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ ผͷച্ͱߪೖ݅Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ ݄ผͷച্ͱߪೖ݅Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ Ϣʔβʔ͝ͱͷ࠷ऴߪೖߪೖස/ߪೖֹۚΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ ߪೖස(จ)͝ͱʹϢʔβʔΛूܭ͍ͯͩ͘͠͞ᶃ ˒˒˒˒ˑ ߪೖස(จ)͝ͱʹϢʔβʔΛूܭ͍ͯͩ͘͠͞ᶄ ˒˒˒˒˒ ʮ৯ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ ˒˒ˑˑˑ ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒ˑ ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒˒ -
© )JLBSV5BLBIBTIJ ݄ผͷച্ͱߪೖ݅Λूܭ͍ͯͩ͘͠͞ɻͨͩ͠จΩϟϯηϧͷσʔλআ֎ͯͩ͘͠͞ ͍ɻ ࣮ફ42-ᶆ ࣮ફ42-ूɹબ қ ˒˒ˑˑˑ
ॴཁ࣌ؒ ώϯτᶃ ʮ݄ʯใͷऔಘTVCTUSΛ͔ͬͯΒʮ݄ʯʹม͠·͠ΐ͏ -
© )JLBSV5BLBIBTIJ ݄ผͷച্ͱߪೖ݅Λूܭ͍ͯͩ͘͠͞ɻͨͩ͠จΩϟϯηϧͷσʔλআ֎ͯͩ͘͠͞ ͍ɻ ࣮ફ42-ᶆ ࣮ફ42-ूɹબ қ ˒˒ˑˑˑ
ॴཁ࣌ؒ ώϯτᶃ ʮ݄ʯใͷऔಘTVCTUSΛ͔ͬͯΒʮ݄ʯʹม͠·͠ΐ͏ ώϯτᶄ ʮ݄ʯใʮTVCTUS PSEFS@EBUF ʯͰมͰ͖·͢ -
© )JLBSV5BLBIBTIJ ݄ผͷച্ͱߪೖ݅Λूܭ͍ͯͩ͘͠͞ɻͨͩ͠จΩϟϯηϧͷσʔλআ֎ͯͩ͘͠͞ ͍ɻ ࣮ફ42-ᶆ ࣮ફ42-ूɹબ қ ˒˒ˑˑˑ
ॴཁ࣌ؒ ώϯτᶃ ʮ݄ʯใͷऔಘTVCTUSΛ͔ͬͯΒʮ݄ʯʹม͠·͠ΐ͏ ώϯτᶄ ʮ݄ʯใʮTVCTUS PSEFS@EBUF ʯͰมͰ͖·͢ ώϯτᶅ ݄Ͱ(3061#:ͯ͠ച্ͱߪೖ݅Λूܭ͠·͠ΐ͏ -
© )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 -
© )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 -
© )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-ͷ࣮ߦॱংˠ -
© )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#:ͷूܭ͕Մೳ -
© )JLBSV5BLBIBTIJ ࣮ફ42-ूɹબ 42-ूͷ࣮ફ ղઆ /P қ
ੑผ͝ͱͷਓΛूܭ͍ͯͩ͘͠͞ ˒ˑˑˑˑ ผͷਓΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ ผͷച্ͱߪೖ݅Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ ݄ผͷച্ͱߪೖ݅Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ Ϣʔβʔ͝ͱͷ࠷ऴߪೖߪೖස/ߪೖֹۚΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ ߪೖස(จ)͝ͱʹϢʔβʔΛूܭ͍ͯͩ͘͠͞ᶃ ˒˒˒˒ˑ ߪೖස(จ)͝ͱʹϢʔβʔΛूܭ͍ͯͩ͘͠͞ᶄ ˒˒˒˒˒ ʮ৯ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ ˒˒ˑˑˑ ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒ˑ ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒˒ -
© )JLBSV5BLBIBTIJ Ϣʔβʔ͝ͱͷ࠷ऴߪೖɺߪೖස จ ɺߪೖֹۚΛूܭ͍ͯͩ͘͠͞ 3'.ੳ ɻͨ ͩ͠จΩϟϯηϧͷσʔλআ֎ͯ͠আࡁϢʔβʔɺະߪೖϢʔβʔআ֎͍ͯͩ͘͠͞ɻ
࣮ફ42-ᶇ ࣮ફ42-ूɹબ қ ˒˒˒ˑˑ ॴཁ࣌ؒ -
© )JLBSV5BLBIBTIJ Ϣʔβʔ͝ͱͷ࠷ऴߪೖɺߪೖස จ ɺߪೖֹۚΛूܭ͍ͯͩ͘͠͞ 3'.ੳ ɻͨ ͩ͠จΩϟϯηϧͷσʔλআ֎ͯ͠আࡁϢʔβʔɺະߪೖϢʔβʔআ֎͍ͯͩ͘͠͞ɻ
࣮ફ42-ᶇ ࣮ફ42-ूɹબ қ ˒˒˒ˑˑ ॴཁ࣌ؒ ώϯτᶃ VTFSTςʔϒϧͱQSPEVDUTςʔϒϧͱ+0*/ͯͭ͠ςʔϒϧΛ݁߹ͯ͠ Ϣʔβʔ͝ͱͷߪೖσʔλΛ֬ೝ͠·͠ΐ͏ -
© )JLBSV5BLBIBTIJ Ϣʔβʔ͝ͱͷ࠷ऴߪೖɺߪೖස จ ɺߪೖֹۚΛूܭ͍ͯͩ͘͠͞ 3'.ੳ ɻͨ ͩ͠จΩϟϯηϧͷσʔλআ֎ͯ͠আࡁϢʔβʔɺະߪೖϢʔβʔআ֎͍ͯͩ͘͠͞ɻ
࣮ફ42-ᶇ ࣮ફ42-ूɹબ қ ˒˒˒ˑˑ ॴཁ࣌ؒ ώϯτᶃ VTFSTςʔϒϧͱQSPEVDUTςʔϒϧͱ+0*/ͯͭ͠ςʔϒϧΛ݁߹ͯ͠ Ϣʔβʔ͝ͱͷߪೖσʔλΛ֬ೝ͠·͠ΐ͏ ώϯτᶄ Ϣʔβʔ͝ͱͷूܭΛ͢Δ߹VTFS@JEͰ(3061#:Λ͍·͠ΐ͏ -
© )JLBSV5BLBIBTIJ Ϣʔβʔ͝ͱͷ࠷ऴߪೖɺߪೖස จ ɺߪೖֹۚΛूܭ͍ͯͩ͘͠͞ 3'.ੳ ɻͨ ͩ͠จΩϟϯηϧͷσʔλআ֎ͯ͠আࡁϢʔβʔɺະߪೖϢʔβʔআ֎͍ͯͩ͘͠͞ɻ
࣮ફ42-ᶇ ࣮ફ42-ूɹબ қ ˒˒˒ˑˑ ॴཁ࣌ؒ ώϯτᶃ VTFSTςʔϒϧͱQSPEVDUTςʔϒϧͱ+0*/ͯͭ͠ςʔϒϧΛ݁߹ͯ͠ Ϣʔβʔ͝ͱͷߪೖσʔλΛ֬ೝ͠·͠ΐ͏ ώϯτᶄ Ϣʔβʔ͝ͱͷूܭΛ͢Δ߹VTFS@JEͰ(3061#:Λ͍·͠ΐ͏ ώϯτᶅ ɾ࠷ऴߪೖˠߪೖͷ࠷େ ɾߪೖස จ ˠߪೖͷΧϯτ ɾߪೖֹۚˠֹۚͷ߹ܭɹ ͱͯ͠ܭࢉͯ͠Έ·͠ΐ͏ -
© )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 -
© )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 আࡁΈϢʔβʔΛআ֎͢ΔͨΊ -
© )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ʢߪೖֹۚʣͷͭͷࢦඪͰސ٬Λੳ͢Δख๏ -
© )JLBSV5BLBIBTIJ ࣮ફ42-ूɹબ 42-ूͷ࣮ફ ղઆ /P қ
ੑผ͝ͱͷਓΛूܭ͍ͯͩ͘͠͞ ˒ˑˑˑˑ ผͷਓΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ ผͷച্ͱߪೖ݅Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ ݄ผͷച্ͱߪೖ݅Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ Ϣʔβʔ͝ͱͷ࠷ऴߪೖߪೖස/ߪೖֹۚΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ ߪೖස(จ)͝ͱʹϢʔβʔΛूܭ͍ͯͩ͘͠͞ᶃ ˒˒˒˒ˑ ߪೖස(จ)͝ͱʹϢʔβʔΛूܭ͍ͯͩ͘͠͞ᶄ ˒˒˒˒˒ ʮ৯ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ ˒˒ˑˑˑ ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒ˑ ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒˒ -
© )JLBSV5BLBIBTIJ ؒͷߪೖͰԿؒߪೖ͕͔͋ͬͨߪೖස(จ)͝ͱʹϢʔβʔΛूܭ͍ͯͩ͘͠͞ɻ ͨͩ͠จΩϟϯηϧͷσʔλআ֎ͯ͠আࡁϢʔβʔͱະߪೖϢʔβʔআ֎ͯͩ͘͠͞ ͍ɻ ࣮ફ42-ᶈ ࣮ફ42-ूɹબ қ
˒˒˒˒ˑ ॴཁ࣌ؒ ˠ ؒͷߪೖ͕ͷϢʔβʔ͕ਓ ˠ ؒͷߪೖ͕ͷϢʔβʔ͕ਓ ΞτϓοτΠϝʔδ ߪೖ ߪೖऀ -
© )JLBSV5BLBIBTIJ ؒͷߪೖͰԿؒߪೖ͕͔͋ͬͨߪೖස(จ)͝ͱʹϢʔβʔΛूܭ͍ͯͩ͘͠͞ɻ ͨͩ͠จΩϟϯηϧͷσʔλআ֎ͯ͠আࡁϢʔβʔͱະߪೖϢʔβʔআ֎ͯͩ͘͠͞ ͍ɻ ࣮ફ42-ᶈ ࣮ફ42-ूɹબ қ
˒˒˒˒ˑ ॴཁ࣌ؒ ώϯτᶃ Ϣʔβʔ͝ͱʹߪೖස ߪೖ ΛΧϯτ͠·͠ΐ͏ -
© )JLBSV5BLBIBTIJ ؒͷߪೖͰԿؒߪೖ͕͔͋ͬͨߪೖස(จ)͝ͱʹϢʔβʔΛूܭ͍ͯͩ͘͠͞ɻ ͨͩ͠จΩϟϯηϧͷσʔλআ֎ͯ͠আࡁϢʔβʔͱະߪೖϢʔβʔআ֎ͯͩ͘͠͞ ͍ɻ ࣮ફ42-ᶈ ࣮ફ42-ूɹબ қ
˒˒˒˒ˑ ॴཁ࣌ؒ ώϯτᶃ Ϣʔβʔ͝ͱʹߪೖස ߪೖ ΛΧϯτ͠·͠ΐ͏ ώϯτᶄ 8*5)۟ΛͬͯҰ࣌ςʔϒϧΛ࡞Δͱཧ͘͢͠ͳΓ·͢ ͦͷࡍ 4&-&$5ͨ݁͠Ռʹ"4ͰΧϥϜ໊Λ͚ͭ·͠ΐ͏ -
© )JLBSV5BLBIBTIJ ؒͷߪೖͰԿؒߪೖ͕͔͋ͬͨߪೖස(จ)͝ͱʹϢʔβʔΛूܭ͍ͯͩ͘͠͞ɻ ͨͩ͠จΩϟϯηϧͷσʔλআ֎ͯ͠আࡁϢʔβʔͱະߪೖϢʔβʔআ֎ͯͩ͘͠͞ ͍ɻ ࣮ફ42-ᶈ ࣮ફ42-ूɹબ қ
˒˒˒˒ˑ ॴཁ࣌ؒ ώϯτᶃ Ϣʔβʔ͝ͱʹߪೖස ߪೖ ΛΧϯτ͠·͠ΐ͏ ώϯτᶄ 8*5)۟ΛͬͯҰ࣌ςʔϒϧΛ࡞Δͱཧ͘͢͠ͳΓ·͢ ͦͷࡍ 4&-&$5ͨ݁͠Ռʹ"4ͰΧϥϜ໊Λ͚ͭ·͠ΐ͏ ώϯτᶅ ߪೖස จ ͝ͱʹϢʔβʔΛΧϯτ͢Δ߹ߪೖස จ Ͱ(3061#:Λ͍·͠ΐ͏ -
© )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 -
© )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 -
© )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 -
© )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 -
© )JLBSV5BLBIBTIJ ࣮ફ42-ᶈɹղઆ ࣮ફ42-ूɹબ VTFS@JE DPVOU@PSEFS@EBUF " "
" " 45&1 ·ͣϢʔβʔຖͷߪೖΛूܭ͠ɺͦͷޙߪೖͰϢʔβʔΛूܭ͢Δ 45&1 45&1 VTFSTςʔϒϧ ݱঢ়ͷςʔϒϧͷ֬ೝ ϢʔβʔຖͷߪೖΛूܭ ߪೖͰϢʔβʔΛूܭ PSEFSTςʔϒϧ DPVOU@PSEFS@EBUF VTFS@DPVOU -
© )JLBSV5BLBIBTIJ ࣮ફ42-ूɹબ 42-ूͷ࣮ફ ղઆ /P қ
ੑผ͝ͱͷਓΛूܭ͍ͯͩ͘͠͞ ˒ˑˑˑˑ ผͷਓΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ ผͷച্ͱߪೖ݅Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ ݄ผͷച্ͱߪೖ݅Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ Ϣʔβʔ͝ͱͷ࠷ऴߪೖߪೖස/ߪೖֹۚΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ ߪೖස(จ)͝ͱʹϢʔβʔΛूܭ͍ͯͩ͘͠͞ᶃ ˒˒˒˒ˑ ߪೖස(จ)͝ͱʹϢʔβʔΛूܭ͍ͯͩ͘͠͞ᶄ ˒˒˒˒˒ ʮ৯ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ ˒˒ˑˑˑ ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒ˑ ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒˒ -
© )JLBSV5BLBIBTIJ ؒͷߪೖͰԿؒߪೖ͕͔͋ͬͨߪೖස(จ)͝ͱʹϢʔβʔΛूܭ͍ͯͩ͘͠͞ɻ ͨͩ͠ະߪೖϢʔβʔؚΊͨߪೖΛΧϯτͯ͠ɺจΩϟϯηϧͷσʔλআ֎ɺআ ࡁΈϢʔβʔআ֎ͯ͠Լ͍͞ɻ ࣮ફ42-ᶉ ࣮ફ42-ूɹબ қ
˒˒˒˒˒ ॴཁ࣌ؒ ˠ ؒͷߪೖ͕ͷϢʔβʔ͕ਓ ˠ ؒͷߪೖ͕ͷϢʔβʔ͕ਓ ΞτϓοτΠϝʔδ ߪೖ ߪೖऀ ʢະߪೖϢʔβʔʣ -
© )JLBSV5BLBIBTIJ ؒͷߪೖͰԿؒߪೖ͕͔͋ͬͨߪೖස(จ)͝ͱʹϢʔβʔΛूܭ͍ͯͩ͘͠͞ɻ ͨͩ͠ະߪೖϢʔβʔؚΊͨߪೖΛΧϯτͯ͠ɺจΩϟϯηϧͷσʔλআ֎ɺআ ࡁΈϢʔβʔআ֎ͯ͠Լ͍͞ɻ ࣮ફ42-ᶉ ࣮ફ42-ूɹબ қ
˒˒˒˒˒ ॴཁ࣌ؒ ώϯτᶃ ߟ͑ํᶈͱಉ͡Ͱ·ͣϢʔβʔ͝ͱͷߪೖΛΧϯτ͠· ͠ΐ͏ -
© )JLBSV5BLBIBTIJ ؒͷߪೖͰԿؒߪೖ͕͔͋ͬͨߪೖස(จ)͝ͱʹϢʔβʔΛूܭ͍ͯͩ͘͠͞ɻ ͨͩ͠ະߪೖϢʔβʔؚΊͨߪೖΛΧϯτͯ͠ɺจΩϟϯηϧͷσʔλআ֎ɺআ ࡁΈϢʔβʔআ֎ͯ͠Լ͍͞ɻ ࣮ફ42-ᶉ ࣮ફ42-ूɹબ қ
˒˒˒˒˒ ॴཁ࣌ؒ ώϯτᶃ ߟ͑ํᶈͱಉ͡Ͱ·ͣϢʔβʔ͝ͱͷߪೖΛΧϯτ͠· ͠ΐ͏ ώϯτᶄ ߪೖ͕ͳ͔ͬͨϢʔβʔΧϯτ͢ΔͷͰɺVTFSTΛϕʔεʹσʔλΛऔ ಘ͠·͠ΐ͏ -
© )JLBSV5BLBIBTIJ ؒͷߪೖͰԿؒߪೖ͕͔͋ͬͨߪೖස(จ)͝ͱʹϢʔβʔΛूܭ͍ͯͩ͘͠͞ɻ ͨͩ͠ະߪೖϢʔβʔؚΊͨߪೖΛΧϯτͯ͠ɺจΩϟϯηϧͷσʔλআ֎ɺআ ࡁΈϢʔβʔআ֎ͯ͠Լ͍͞ɻ ࣮ફ42-ᶉ ࣮ફ42-ूɹબ қ
˒˒˒˒˒ ॴཁ࣌ؒ ώϯτᶃ ߟ͑ํᶈͱಉ͡Ͱ·ͣϢʔβʔ͝ͱͷߪೖΛΧϯτ͠· ͠ΐ͏ ώϯτᶄ ߪೖ͕ͳ͔ͬͨϢʔβʔΧϯτ͢ΔͷͰɺVTFSTΛϕʔεʹσʔλΛऔ ಘ͠·͠ΐ͏ ώϯτᶅ ߪೖස จ ͝ͱʹϢʔβʔΛΧϯτ͢Δ߹ߪೖස จ Ͱ(3061#:Λ͍·͠ΐ͏ -
© )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 -
© )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ͷ߹݅ʹؚΊΔ -
© )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*/ -
© )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 -
© )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#:Λͯ͠Ϣʔ βʔΛΧϯτ͢Δ -
© )JLBSV5BLBIBTIJ ࣮ફ42-ूɹબ 42-ूͷ࣮ફ ղઆ /P қ
ੑผ͝ͱͷਓΛूܭ͍ͯͩ͘͠͞ ˒ˑˑˑˑ ผͷਓΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ ผͷച্ͱߪೖ݅Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ ݄ผͷച্ͱߪೖ݅Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ Ϣʔβʔ͝ͱͷ࠷ऴߪೖߪೖස/ߪೖֹۚΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ ߪೖස(จ)͝ͱʹϢʔβʔΛूܭ͍ͯͩ͘͠͞ᶃ ˒˒˒˒ˑ ߪೖස(จ)͝ͱʹϢʔβʔΛूܭ͍ͯͩ͘͠͞ᶄ ˒˒˒˒˒ ʮ৯ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ ˒˒ˑˑˑ ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒ˑ ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒˒ -
© )JLBSV5BLBIBTIJ ݄ʹʮ৯ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ɻͨ ͩ͠จΩϟϯηϧͷσʔλআ֎͍ͯͩ͘͠͞ɻ ࣮ફ42-ᶊ ࣮ફ42-ूɹબ қ ˒˒ˑˑˑ
ॴཁ࣌ؒ ώϯτᶃ จใͱใΛ݁߹͠·͠ΐ͏ -
© )JLBSV5BLBIBTIJ ݄ʹʮ৯ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ɻͨ ͩ͠จΩϟϯηϧͷσʔλআ֎͍ͯͩ͘͠͞ɻ ࣮ફ42-ᶊ ࣮ફ42-ूɹબ қ ˒˒ˑˑˑ
ॴཁ࣌ؒ ώϯτᶃ จใͱใΛ݁߹͠·͠ΐ͏ ώϯτᶄ ෳͷ݅Λ͚ͭΔ߹8)&3&ʙ"/%Λ͍·͠ΐ͏ -
© )JLBSV5BLBIBTIJ ݄ʹʮ৯ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ɻͨ ͩ͠จΩϟϯηϧͷσʔλআ֎͍ͯͩ͘͠͞ɻ ࣮ફ42-ᶊ ࣮ફ42-ूɹબ қ ˒˒ˑˑˑ
ॴཁ࣌ؒ ώϯτᶃ จใͱใΛ݁߹͠·͠ΐ͏ ώϯτᶄ ෳͷ݅Λ͚ͭΔ߹8)&3&ʙ"/%Λ͍·͠ΐ͏ ώϯτᶅ ʮ৯ʯMBSHF@DBUFHPSZͰఆ͠·͠ΐ͏ -
© )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 = '৯' -
© )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৯`Ͱఆ͠·͢ -
© )JLBSV5BLBIBTIJ ࣮ફ42-ᶊɹղྫɹ5*14 ࣮ફ42-ूɹબ SELECT DISTINCT large_category FROM products
w MBSHF@DBUFHPSZʹͲΜͳ͕ೖ͍ͬͯΔͷ͔֬ೝͨ͠ ͍߹ɺʮ%*45*/$5ʯΛͬͯMBSHF@DBUFHPSZͷϢ χʔΫͳΛҰཡͰ֬ೝ͢Δࣄ͕Ͱ͖·͢ w ͜ΕͰMBSHF@DBUFHPSZʹʮ৯ʯͷσʔλ͕ೖ͍ͬͯ Δࣄ͕֬ೝͰ͖·͢ σʔλੳͷࡍʹΑ֬͘ೝ͠·͢ MBSHF@DBUFHPSZ ৯ ༻ ΠϯςϦΞ ϑΝογϣϯ ిԽ ग़ྗ݁Ռ -
© )JLBSV5BLBIBTIJ ࣮ફ42-ूɹબ 42-ूͷ࣮ફ ղઆ /P қ
ੑผ͝ͱͷਓΛूܭ͍ͯͩ͘͠͞ ˒ˑˑˑˑ ผͷਓΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ ผͷച্ͱߪೖ݅Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ ݄ผͷച্ͱߪೖ݅Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ Ϣʔβʔ͝ͱͷ࠷ऴߪೖߪೖස/ߪೖֹۚΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ ߪೖස(จ)͝ͱʹϢʔβʔΛूܭ͍ͯͩ͘͠͞ᶃ ˒˒˒˒ˑ ߪೖස(จ)͝ͱʹϢʔβʔΛूܭ͍ͯͩ͘͠͞ᶄ ˒˒˒˒˒ ʮ৯ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ ˒˒ˑˑˑ ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒ˑ ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒˒ -
© )JLBSV5BLBIBTIJ ࣮ફ42-ᶋ ࣮ફ42-ूɹબ қ ˒˒˒˒ˑ ॴཁ࣌ؒ ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔ɺΧςΰϦ͝ͱʹߪೖऀΛநग़ͯ͠Լ͍͞ɻ
ͨͩ͠จΩϟϯηϧͷσʔλআ֎͍ͯͩ͘͠͞ɻ·ͨதΧςΰϦ NFEJVN@DBUFHPSZ ͝ ͱͷߪೖऀ͕ଟ͍ॱʹ݁ՌΛग़ྗ͍ͯͩ͘͠͞ɻ -
© )JLBSV5BLBIBTIJ ࣮ફ42-ᶋ ࣮ફ42-ूɹબ қ ˒˒˒˒ˑ ॴཁ࣌ؒ ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔ɺΧςΰϦ͝ͱʹߪೖऀΛநग़ͯ͠Լ͍͞ɻ
ͨͩ͠จΩϟϯηϧͷσʔλআ֎͍ͯͩ͘͠͞ɻ·ͨதΧςΰϦ NFEJVN@DBUFHPSZ ͝ ͱͷߪೖऀ͕ଟ͍ॱʹ݁ՌΛग़ྗ͍ͯͩ͘͠͞ɻ ώϯτᶃ 8*5)۟Λͬͯʮඒ༰ΛങͬͨਓʯͷҰ࣌ςʔϒϧΛ࡞͠·͠ΐ͏ -
© )JLBSV5BLBIBTIJ ࣮ફ42-ᶋ ࣮ફ42-ूɹબ қ ˒˒˒˒ˑ ॴཁ࣌ؒ ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔ɺΧςΰϦ͝ͱʹߪೖऀΛநग़ͯ͠Լ͍͞ɻ
ͨͩ͠จΩϟϯηϧͷσʔλআ֎͍ͯͩ͘͠͞ɻ·ͨதΧςΰϦ NFEJVN@DBUFHPSZ ͝ ͱͷߪೖऀ͕ଟ͍ॱʹ݁ՌΛग़ྗ͍ͯͩ͘͠͞ɻ ώϯτᶃ 8*5)۟Λͬͯʮඒ༰ΛങͬͨਓʯͷҰ࣌ςʔϒϧΛ࡞͠·͠ΐ͏ ώϯτᶄ ʮඒ༰Λങͬͨਓʯ"/%ʮඒ༰Ҏ֎ͷʯͱͭͷ݅Λ͚ͯσʔλ Λऔಘ͠·͠ΐ͏ -
© )JLBSV5BLBIBTIJ ࣮ફ42-ᶋ ࣮ફ42-ूɹબ қ ˒˒˒˒ˑ ॴཁ࣌ؒ ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔ɺΧςΰϦ͝ͱʹߪೖऀΛநग़ͯ͠Լ͍͞ɻ
ͨͩ͠จΩϟϯηϧͷσʔλআ֎͍ͯͩ͘͠͞ɻ·ͨதΧςΰϦ NFEJVN@DBUFHPSZ ͝ ͱͷߪೖऀ͕ଟ͍ॱʹ݁ՌΛग़ྗ͍ͯͩ͘͠͞ɻ ώϯτᶃ 8*5)۟Λͬͯʮඒ༰ΛങͬͨਓʯͷҰ࣌ςʔϒϧΛ࡞͠·͠ΐ͏ ώϯτᶄ ʮඒ༰Λങͬͨਓʯ"/%ʮඒ༰Ҏ֎ͷʯͱͭͷ݅Λ͚ͯσʔλ Λऔಘ͠·͠ΐ͏ ώϯτᶅ ࠷ऴతʹNFEJVN@DBUFHPSZͰ(3061#:ͯ͠ߪೖऀ VTFS@JE ΛΧ ϯτ͠·͠ΐ͏ -
© )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 -
© )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-ͱ΄΅ಉ͡ -
© )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 ඒ༰Ҏ֎ͷΛநग़͢Δ߹ʮʯΛͬͯಛఆ ͷΧςΰϦҎ֎ͷσʔλΛநग़͢Δ -
© )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ΛϢ χʔΫΧϯτ͢Δ -
© )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 จ۩ ҿྉਫ ࡊ -
© )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 จ۩ ҿྉਫ ࡊ -
© )JLBSV5BLBIBTIJ ࣮ફ42-ूɹબ 42-ूͷ࣮ફ ղઆ /P қ
ੑผ͝ͱͷਓΛूܭ͍ͯͩ͘͠͞ ˒ˑˑˑˑ ผͷਓΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ ผͷച্ͱߪೖ݅Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ ݄ผͷച্ͱߪೖ݅Λूܭ͍ͯͩ͘͠͞ ˒˒ˑˑˑ Ϣʔβʔ͝ͱͷ࠷ऴߪೖߪೖස/ߪೖֹۚΛूܭ͍ͯͩ͘͠͞ ˒˒˒ˑˑ ߪೖස(จ)͝ͱʹϢʔβʔΛूܭ͍ͯͩ͘͠͞ᶃ ˒˒˒˒ˑ ߪೖස(จ)͝ͱʹϢʔβʔΛूܭ͍ͯͩ͘͠͞ᶄ ˒˒˒˒˒ ʮ৯ʯΛߪೖͨ͠ϢʔβʔIDΛϢχʔΫ(ॏෳͳ͠)Ͱநग़͍ͯͩ͘͠͞ ˒˒ˑˑˑ ʮඒ༰ʯΛങ͍ͬͯΔਓ͕ଞʹԿΛങ͍ͬͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒ˑ ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔நग़ͯ͠Լ͍͞ ˒˒˒˒˒ -
© )JLBSV5BLBIBTIJ ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔ผͷϥϯΩϯά ߪೖ͕݅ଟ͍ॱ Λநग़ͯ͠Լ͞ ͍ɻͨͩ͠จΩϟϯηϧͷσʔλআ֎ͯ͠ɺVTFS@JE͕ۭͷσʔλআ֎͍ͯͩ͘͠͞ɻ ࣮ફ42-ᶌ ࣮ફ42-ूɹબ
қ ॴཁ࣌ؒ ώϯτᶃ Ϣʔβʔ͝ͱͷॳճจ*%Λநग़ͯ͠ɺͦͷจ*%ͷσʔλΛநग़͢Ε ॳճߪೖͷߪೖσʔλ͕நग़Ͱ͖·͢ ˒˒˒˒˒ -
© )JLBSV5BLBIBTIJ ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔ผͷϥϯΩϯά ߪೖ͕݅ଟ͍ॱ Λநग़ͯ͠Լ͞ ͍ɻͨͩ͠จΩϟϯηϧͷσʔλআ֎ͯ͠ɺVTFS@JE͕ۭͷσʔλআ֎͍ͯͩ͘͠͞ɻ ࣮ફ42-ᶌ ࣮ફ42-ूɹબ
қ ॴཁ࣌ؒ ώϯτᶃ Ϣʔβʔ͝ͱͷॳճจ*%Λநग़ͯ͠ɺͦͷจ*%ͷσʔλΛநग़͢Ε ॳճߪೖͷߪೖσʔλ͕நग़Ͱ͖·͢ ώϯτᶄ ʮॳճߪೖʯʮϢʔβʔ୯ҐͰจ൪߸͕Ұ൪খ͍͞จʯ ˒˒˒˒˒ -
© )JLBSV5BLBIBTIJ ॳճߪೖͰԿ͕Ұ൪ߪೖ͞Ε͍ͯΔ͔ผͷϥϯΩϯά ߪೖ͕݅ଟ͍ॱ Λநग़ͯ͠Լ͞ ͍ɻͨͩ͠จΩϟϯηϧͷσʔλআ֎ͯ͠ɺVTFS@JE͕ۭͷσʔλআ֎͍ͯͩ͘͠͞ɻ ࣮ફ42-ᶌ ࣮ફ42-ूɹબ
қ ॴཁ࣌ؒ ώϯτᶃ Ϣʔβʔ͝ͱͷॳճจ*%Λநग़ͯ͠ɺͦͷจ*%ͷσʔλΛநग़͢Ε ॳճߪೖͷߪೖσʔλ͕நग़Ͱ͖·͢ ώϯτᶄ ʮॳճߪೖʯʮϢʔβʔ୯ҐͰจ൪߸͕Ұ൪খ͍͞จʯ ώϯτᶅ ʮจ൪߸͕Ұ൪খ͍͞ʯΛఆ͢ΔͨΊʹ.*/ؔΛ͍·͠ΐ͏ ˒˒˒˒˒ -
© )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 ˒˒˒˒˒ -
© )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 ʯʹΑͬͯϢʔβʔ͝ͱʹҰ൪খ͞ ͍จ*%ॳճͷจ*%Λऔಘ͢Δ -
© )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 ʮ*/ʯΛͬͯॳճߪೖͷจ*%ͷΈΛநग़ -
© )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 ʯͰಉ͡ -
© )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Ґɾɾɾʣ -
© )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. " $ " $ " $ " $ ݩͷςʔϒϧͷߦΛҡ࣋ͨ͠··ಛఆͷΧϥϜͰάϧʔϐϯ άͨ݁͠ՌΛ༩͢Δ͜ͱ͕Ͱ͖Δ ݩςʔϒϧ Πϝʔδ -
© )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.#&346. ͳͲ͕͑Δɻ %&/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#: ۩ମྫ