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

by hikarut

Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

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

Slide 4

Slide 4 text

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

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

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

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

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

Slide 37

Slide 37 text

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

Slide 38

Slide 38 text

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

Slide 39

Slide 39 text

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

Slide 40

Slide 40 text

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

Slide 41

Slide 41 text

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

Slide 42

Slide 42 text

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

Slide 43

Slide 43 text

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

Slide 44

Slide 44 text

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

Slide 45

Slide 45 text

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

Slide 46

Slide 46 text

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

Slide 47

Slide 47 text

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

Slide 48

Slide 48 text

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

Slide 49

Slide 49 text

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

Slide 50

Slide 50 text

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

Slide 51

Slide 51 text

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

Slide 52

Slide 52 text

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

Slide 53

Slide 53 text

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

Slide 54

Slide 54 text

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

Slide 55

Slide 55 text

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

Slide 56

Slide 56 text

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

Slide 57

Slide 57 text

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

Slide 58

Slide 58 text

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

Slide 59

Slide 59 text

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

Slide 60

Slide 60 text

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

Slide 61

Slide 61 text

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

Slide 62

Slide 62 text

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

Slide 63

Slide 63 text

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

Slide 64

Slide 64 text

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

Slide 65

Slide 65 text

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

Slide 66

Slide 66 text

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

Slide 67

Slide 67 text

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

Slide 68

Slide 68 text

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

Slide 69

Slide 69 text

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

Slide 70

Slide 70 text

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

Slide 71

Slide 71 text

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

Slide 72

Slide 72 text

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

Slide 73

Slide 73 text

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

Slide 74

Slide 74 text

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

Slide 75

Slide 75 text

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

Slide 76

Slide 76 text

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

Slide 77

Slide 77 text

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

Slide 78

Slide 78 text

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

Slide 79

Slide 79 text

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

Slide 80

Slide 80 text

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

Slide 81

Slide 81 text

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

Slide 82

Slide 82 text

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

Slide 83

Slide 83 text

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

Slide 84

Slide 84 text

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

Slide 85

Slide 85 text

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

Slide 86

Slide 86 text

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

Slide 87

Slide 87 text

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

Slide 88

Slide 88 text

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

Slide 89

Slide 89 text

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

Slide 90

Slide 90 text

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

Slide 91

Slide 91 text

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

Slide 92

Slide 92 text

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

Slide 93

Slide 93 text

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

Slide 94

Slide 94 text

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

Slide 95

Slide 95 text

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

Slide 96

Slide 96 text

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

Slide 97

Slide 97 text

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

Slide 98

Slide 98 text

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

Slide 99

Slide 99 text

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

Slide 100

Slide 100 text

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

Slide 101

Slide 101 text

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

Slide 102

Slide 102 text

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

Slide 103

Slide 103 text

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

Slide 104

Slide 104 text

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

Slide 105

Slide 105 text

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

Slide 106

Slide 106 text

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

Slide 107

Slide 107 text

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

Slide 108

Slide 108 text

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

Slide 109

Slide 109 text

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

Slide 110

Slide 110 text

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

Slide 111

Slide 111 text

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

Slide 112

Slide 112 text

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

Slide 113

Slide 113 text

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

Slide 114

Slide 114 text

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

Slide 115

Slide 115 text

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

Slide 116

Slide 116 text

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

Slide 117

Slide 117 text

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

Slide 118

Slide 118 text

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

Slide 119

Slide 119 text

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

Slide 120

Slide 120 text

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

Slide 121

Slide 121 text

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

Slide 122

Slide 122 text

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

Slide 123

Slide 123 text

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

Slide 124

Slide 124 text

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

Slide 125

Slide 125 text

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

Slide 126

Slide 126 text

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

Slide 127

Slide 127 text

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

Slide 128

Slide 128 text

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

Slide 129

Slide 129 text

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

Slide 130

Slide 130 text

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

Slide 131

Slide 131 text

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

Slide 132

Slide 132 text

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

Slide 133

Slide 133 text

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

Slide 134

Slide 134 text

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

Slide 135

Slide 135 text

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

Slide 136

Slide 136 text

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

Slide 137

Slide 137 text

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

Slide 138

Slide 138 text

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

Slide 139

Slide 139 text

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

Slide 140

Slide 140 text

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

Slide 141

Slide 141 text

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

Slide 142

Slide 142 text

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

Slide 143

Slide 143 text

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

Slide 144

Slide 144 text

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

Slide 145

Slide 145 text

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

Slide 146

Slide 146 text

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

Slide 147

Slide 147 text

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

Slide 148

Slide 148 text

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

Slide 149

Slide 149 text

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

Slide 150

Slide 150 text

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

Slide 151

Slide 151 text

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

Slide 152

Slide 152 text

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

Slide 153

Slide 153 text

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

Slide 154

Slide 154 text

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

Slide 155

Slide 155 text

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

Slide 156

Slide 156 text

Slide 157

Slide 157 text

Slide 158

Slide 158 text

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

Slide 159

Slide 159 text

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

Slide 160

Slide 160 text

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

Slide 161

Slide 161 text

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

Slide 162

Slide 162 text

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

Slide 163

Slide 163 text

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

Slide 164

Slide 164 text

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

Slide 165

Slide 165 text

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

Slide 166

Slide 166 text

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

Slide 167

Slide 167 text

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

Slide 168

Slide 168 text

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

Slide 169

Slide 169 text

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

Slide 170

Slide 170 text

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

Slide 171

Slide 171 text

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

Slide 172

Slide 172 text

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

Slide 173

Slide 173 text

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

Slide 174

Slide 174 text

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

Slide 175

Slide 175 text

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

Slide 176

Slide 176 text

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

Slide 177

Slide 177 text

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

Slide 178

Slide 178 text

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

Slide 179

Slide 179 text

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

Slide 180

Slide 180 text

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

Slide 181

Slide 181 text

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

Slide 182

Slide 182 text

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

Slide 183

Slide 183 text

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

Slide 184

Slide 184 text

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

Slide 185

Slide 185 text

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

Slide 186

Slide 186 text

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

Slide 187

Slide 187 text

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

Slide 188

Slide 188 text

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

Slide 189

Slide 189 text

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

Slide 190

Slide 190 text

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

Slide 191

Slide 191 text

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

Slide 192

Slide 192 text

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

Slide 193

Slide 193 text

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

Slide 194

Slide 194 text

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

Slide 195

Slide 195 text

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

Slide 196

Slide 196 text

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

Slide 197

Slide 197 text

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

Slide 198

Slide 198 text

&/%