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

by hikarut

Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

Slide 3

Slide 3 text

Slide 4

Slide 4 text

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

Slide 5

Slide 5 text

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

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

Slide 11

Slide 11 text

Slide 12

Slide 12 text

Slide 13

Slide 13 text

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

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

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

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

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

Slide 24

Slide 24 text

Slide 25

Slide 25 text

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

Slide 28

Slide 28 text

Slide 29

Slide 29 text

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

Slide 32

Slide 32 text

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

Slide 36

Slide 36 text

Slide 37

Slide 37 text

Slide 38

Slide 38 text

Slide 39

Slide 39 text

Slide 40

Slide 40 text

Slide 41

Slide 41 text

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

Slide 45

Slide 45 text

Slide 46

Slide 46 text

Slide 47

Slide 47 text

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

Slide 48

Slide 48 text

Slide 49

Slide 49 text

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

Slide 53

Slide 53 text

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

Slide 54

Slide 54 text

Slide 55

Slide 55 text

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

Slide 56

Slide 56 text

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

Slide 63

Slide 63 text

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

Slide 64

Slide 64 text

Slide 65

Slide 65 text

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

Slide 66

Slide 66 text

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

Slide 67

Slide 67 text

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

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

Slide 72

Slide 72 text

Slide 73

Slide 73 text

Slide 74

Slide 74 text

Slide 75

Slide 75 text

Slide 76

Slide 76 text

Slide 77

Slide 77 text

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

Slide 81

Slide 81 text

Slide 82

Slide 82 text

Slide 83

Slide 83 text

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

Slide 84

Slide 84 text

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

Slide 87

Slide 87 text

Slide 88

Slide 88 text

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

Slide 92

Slide 92 text

Slide 93

Slide 93 text

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

Slide 94

Slide 94 text

Slide 95

Slide 95 text

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

Slide 98

Slide 98 text

Slide 99

Slide 99 text

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

&/%