150225_sql_for_everyone.pdf

Slide 1

Slide 1 text

How Livesense Works #1 Ӧۀ͞Μ·Ͱࣾһશһ͕SQLΛ࢖͏ ʮӽڥܕ૊৫ʯ͕Ͱ͖Δ·Ͱͷ3+1ͷϙΠϯτ Yukihiko Kawarazuka, Engineer of Livesense, inc [email protected]

Slide 2

Slide 2 text

͋Δ೔ɺӦۀ͞Μ͔Βདྷͨґཔ

Slide 3

Slide 3 text

No content

Slide 4

Slide 4 text

No content

Slide 5

Slide 5 text

No content

Slide 6

Slide 6 text

͋Δ͋Δ

Slide 7

Slide 7 text

ΘΓͱͬ͘͟Γ࢓༷ɻ ※൓ڹ=͓໰͍߹Θͤͷ͜ͱ

Slide 8

Slide 8 text

Ͱ΋

Slide 9

Slide 9 text

No content

Slide 10

Slide 10 text

͋Δ͋Δ

Slide 11

Slide 11 text

ͦͷ8ϲ݄ޙ

Slide 12

Slide 12 text

No content

Slide 13

Slide 13 text

No content

Slide 14

Slide 14 text

No content

Slide 15

Slide 15 text

No content

Slide 16

Slide 16 text

͜Ε͸΄ΜͷҰྫ

Slide 17

Slide 17 text

ͳͥʁ ͳͥɺӦۀ͞Μ΋SQLΛॻ͘ͷ͔ ͳͥɺӦۀ͞Μʹ·ͰσʔλϕʔεΞΫηεݖΛ։์͍ͯ͠Δͷ͔ ͳͥɺSQLΛֶश͠Α͏ͱࢥͬͨͷ͔ ͳͥɺֶशΛ׬਱Ͱ͖ͨͷ͔ ͳͥɺֶशʹͱͲ·Β࣮ͣӡ༻ʹ଱͑ΔεΩϧʹঢ՚͞Εͨͷ͔ ͳͥɺ͜ͷΑ͏ͳڠۀจԽ͕Ͱ͖ɺࠜ෇͍ͨͷ͔

Slide 18

Slide 18 text

ࢲ͸લ৬ͰʮଵଦͰԣฑͰ௚ײཔΓͳӦۀʯʹ͠ͼΕΛ੾Β͠ɺ*1 ։ൃΛ΍Γͭͭࣗ෼ͰӦۀ΋͍ͯͨ͠ͷͰ ʮͳͥʁʯΛӽ͑ͯʮγϣοΫʯͩͬͨɻ *1 ੾Βͨ͠ͷ͸͠ͼΕ͚ͩͰ͸ͳ͔͕ͬͨ

Slide 19

Slide 19 text

ӦۀɹɹʮσʔλϕʔεʁͦΜͳΑ͘Θ͔ΒΜ΋ͷӦۀʹ͸ؔ܎ͳ͍ʯ γε؅ɹʮDBʹΞΫηεͰ͖Δਓ਺͸ɺ࠷খݶʹ͢΂͖ɻ ɹɹɹɹɹٕज़ίϯλϛΛ๷͙ͨΊʹɺ෦ॺ֎ͷਓؒ͸ΞΫηεېࢭʯ Ϛʔέɹʮੲ͍ͨʓʓ͞Μ͸SQLͰ΍ͬͯͨΈ͍͚ͨͩͲɺɺ͸ͯͲ͏΍ΔΜ͚ͩͬʯ ϓϩϚωʮ͍͍΋ͷΛ։ൃ͠Α͏ɻ͍͍΋ͷΛ࡞Ε͹Ӧۀ͕ച͖ͬͯͯ͘ΕΔɻ ɹɹɹɹɹͲ͏ചΔ͔ʁ஌Βͳ͍͚ͲͦΕ͸Ӧۀ͕ɻʯ ։ൃɹɹʮ͑ͬ֎஫ͯ͠࡞ͬͯ΋ΒͬͨͷʁͦΕ͏ͪͷ෦ॺͰπʔϧԽͯͨ͠ͷʹɻʯ ࢲ͕લ৬ͰΑ͘ฉ͍ͨ੠

Slide 20

Slide 20 text

ຊ࣭తͳ՝୊ͱ͸ ແؔ৺ ੹೚ൣғͷ ݻࣥ ҙਤ͠ͳ͍ ൿີओٛ

Slide 21

Slide 21 text

੹೚ൣғ΁ͷݻࣥʢηΫγϣφϦζϜʣ ݪҼ: ࣗ෼͕༩͑ΒΕͨ੹೚Λશ͏͠Α͏ͱ͢Δɺ੹೚ײʹىҼɻ ੹೚Λશ͏͢Δͷ͸ɺ౰વॏཁɻ ͔͠͠෦෼࠷దʹͳ͍ͬͯͳ͍͔ʁ ʮηΩϡϦςΟͷͨΊɺDBʹΞΫηεͰ͖Δਓ਺͸࠷খݶʹ͢΂͖ʯ ʮٕज़ίϯλϛΛ๷͙ͨΊʹɺ෦ॺ֎ͷਓؒ͸ΞΫηεېࢭʯ

Slide 22

Slide 22 text

ແؔ৺ ʮσʔλϕʔεʁͦΜͳΑ͘Θ͔ΒΜ΋ͷӦۀʹ͸ؔ܎ͳ͍ʯ ʮ͍͍΋ͷΛ։ൃ͠Α͏ɻ ͍͍΋ͷΛ࡞Ε͹Ӧۀ͕ച͖ͬͯͯ͘ΕΔɻ Ͳ͏ചΔ͔ʁ஌Βͳ͍͚ͲͦΕ͸Ӧۀ͕ɻʯ ݪҼ: (1)ࣗ෼ͷ࣋ͭڧΈʢٕज़ɾϊ΢ϋ΢ʣͷΈͰ࢓ࣄΛଊ͑ɺͦΕΛҰൠԽͯ͠͠·͏ɻ (2)ࣄۀ΍αʔϏεશମΛ၆ᛌͨ͠ࢹ໺͕͓࣋ͯͯΒͣɺ ࣗ෼ͷۀ຿֎ͷ෦෼Λٵऩ͠Α͏ͱ͠ͳ͍ɻ ”੹೚ൣғ΁ͷݻࣥ”͔Βੜ·Ε͍ͯΔ͜ͱ΋ଟ͍ɻ

Slide 23

Slide 23 text

ҙਤ͠ͳ͍ൿີओٛ ݪҼ: (1)ຊ౰͸ͪΌΜͱօʹڞ༗͍͕ͨ͠ɺஔ͖৔͕ͳ͍ͨΊޱ఻ͷੈքʹ (2)ଞʹඞཁͳਓɺ͋Δͱخ͍͠ਓ͕͍Δ͜ͱΛ஌Βͳ͍ ͦͷ݁Ռɺਓ͕໨ʹಧ͔ͳ͍ͱ͜Ζʹ੒Ռ෺͕͓͔ΕɺҙਤͤͣʹൿີओٛԽ͢Δɻ “ແؔ৺”͔ΒҾ͖ى͜͞ΕΔ͜ͱ΋ଟ͍ɻ ʮੲ͍ͨʓʓ͞Μ͸ͦ͏΍ͬͯͨΈ͍͚ͨͩͲɺɺ͸ͯͲ͏΍ΔΜ͚ͩͬʯ ʮ͑ͬ֎஫ͯ͠࡞ͬͯ΋ΒͬͨͷʁͦΕ͏ͪͷ෦ॺͰπʔϧԽͯͨ͠ͷʹɻʯ

Slide 24

Slide 24 text

ແؔ৺ ੹೚ൣғͷ ݻࣥ ҙਤ͠ͳ͍ ൿີओٛ ͦΜͳΑ͘Θ͔ΒΜ΋ͷӦۀʹ͸ؔ܎ͳ͍ ͓٬༷ͷ৘ใ͕ೖͬͨσʔλΛ ͩΕͰ΋৮ΒͤΔΘ͚ʹ͸͍͔Μ ੲ͍ͨʓʓ͞Μ͸ ͦ͏΍ͬͯͨΈ͍͚ͨͩͲ

Slide 25

Slide 25 text

֤ʑ͕֤ʑΛҾ͖ى͜͢ҼՌؔ܎ʹ͋Γɺ ૊Έ߹Θ͞ΔͱɺͻͲ͍ѱ॥؀ʹɻ

Slide 26

Slide 26 text

͜Μͳѱ॥؀ʹ͠ͳ͍ͨΊʹɺ ͳʹ͕Ͱ͖Δ͔ɻ

Slide 27

Slide 27 text

#1 ʮҙਤ͠ͳ͍ൿີओٛʯ͔Βʮڞ༗Χϧνϟʔͷৢ੒ʯ΁ ɹɾ৘ใڞ༗͢ΔͨΊͷͷΠϯϑϥΛ༻ҙ͢Δ ɹɾ৘ใڞ༗ϚΠϯυΛৢ੒͢Δ #2 ʮແؔ৺ʯ͔ΒʮֶशΛ௨ͨ͡૬ޓཧղʯ΁ ɹɾؔ৺ΛҾ͘ࢿྉΛڞ༗͠ɺͦΕΛ࣮ݱ͢ΔͨΊͷٕज़ཁૉͷఏࣔ ɹɾ਎ۙͳ୊ࡐΛ༻͍ͨڭࡐͷ༻ҙ ɹɾֶशͷݟ͑ΔԽʹΑΔ૬ޓ࡞༻ #3 ʮ੹೚ൣғ΁ͷݻࣥ(ηΫγϣφϦζϜ)ʯ͔Βʮӽڥ͢Δ૊৫ʯ΁ ɹɾ৬छΛӽ͑ΔͨΊͷ؀ڥͷ੔උ ɹɾӽڥऀʹ͸൐૸Λ ɹɾӽڥͨ͠Β఻ಓΛ

Slide 28

Slide 28 text

ແؔ৺ ੹೚ൣғͷ ݻࣥ ҙਤ͠ͳ͍ ൿີओٛ

Slide 29

Slide 29 text

ֶश ӽڥ ڞ༗

Slide 30

Slide 30 text

૊৫΍৬छͱ͍͏ൣᙝΛӽڥͯ͠ڠۀΛߦ͍ɺ ͦͷࣄྫΛڞ༗͠ɺ ͦͷڞ༗ࣄྫ͕͞ΒͳΔֶशҙཉΛੜΉ͜ͱͰɺ ΑΓ૊৫΍৬छΛ௒͑ͨίϥϘϨʔγϣϯ͕ੜ·ΕΔαΠΫϧ͕ཧ૝త

Slide 31

Slide 31 text

Ӧۀ͞Μ΋SQLΛॻ͘Α͏ʹͳΔ·Ͱͷࣄྫ

Slide 32

Slide 32 text

#1 ʮҙਤ͠ͳ͍ൿີओٛʯ͔Β ʮڞ༗Χϧνϟʔͷৢ੒ʯ΁ ֶश ӽڥ ڞ༗

Slide 33

Slide 33 text

ಋೖ͍ͯ͠Δ৘ใڞ༗Πϯϑϥ Redmine(ݱࡏ͸JIRAʹҠߦத) Conﬂuence Hipchat Cybouzu GoogleApps جຊ͸ࣾһʹରͯ͠ΦʔϓϯΞΫηε ඞཁʹԠ੍ͯ͡ݶ͢ΔϙϦγʔ

Slide 34

Slide 34 text

Redmine(νέοττϥοΩϯάγεςϜ) ʢ˞ݱࡏ͸Atlassian JIRAʹҠߦதʣ ͢΂ͯͷۀ຿ϑϩʔΛҰͭͷγεςϜʹू໿ɻ Ӧۀ͞Μ͔ΒͷSQLൃߦґཔ͔Β  ϓϩμΫτͷ࢓༷ܾఆ·Ͱɺ ޙ͔Βݟฦ͠ܦҢΛ஌Δ͜ͱ͕Ͱ͖Δɻ →৽͘͠ೖࣾͯ͠΋ɺ૑ۀॳظ͔ΒͷܦҢΛ ஌Δ͜ͱ͕Ͱ͖Δ

Slide 35

Slide 35 text

Atlassian Conﬂuence (ࣾ಺WIKI) τοϓϖʔδ͸ਓؾॱɻ اըɾΤϯδχΞϦϯά͔Β ʮຊ౰ʹ೉͍͠γϣʔτέʔΩʯ·Ͱɺ ϑϥοτʹฒͿɻ

Slide 36

Slide 36 text

Atlassian HipChat (νϟοτ) ࣄۀผROOM(ඞਢࢀՃ)ͱɺ  ཁૉผROOM(೚ҙࢀՃ)Λ༻ҙɻ

Slide 37

Slide 37 text

৘ใڞ༗Πϯϑϥͷಋೖ ≠ ɹ ৘ใڞ༗Χϧνϟʔͷৢ੒

Slide 38

Slide 38 text

ɹ৘ใڞ༗Χϧνϟʔͷৢ੒ɹʼɹ৘ใڞ༗Πϯϑϥͷಋೖ

Slide 39

Slide 39 text

No content

Slide 40

Slide 40 text

ɹ৘ใڞ༗Χϧνϟʔͷৢ੒ɹʼɹ৘ใڞ༗Πϯϑϥͷಋೖ

Slide 41

Slide 41 text

#2 ʮແؔ৺ʯ͔Β ʮֶशΛ௨ͨ͡૬ޓཧղʯ΁ ֶश ӽڥ ڞ༗

Slide 42

Slide 42 text

ؔ৺ΛҾ͘Ϩϙʔτͱɺͦͷڞ༗

Slide 43

Slide 43 text

No content

Slide 44

Slide 44 text

ௐࠪϨϙʔτʹSQLΫΤϦ͕ซه͞ΕΔ͜ͱͰɺ ؔ৺ࣄͱཁૉٕज़͕ͭͳ͕Δ ࠶ݱํ๏͕͋Δ͜ͱͰ௥ࢼΛߦ͑ΔɺਅࣅͰ͖Δ

Slide 45

Slide 45 text

਎ۙͳ୊ࡐΛ༻͍ͨڭࡐ: SQL100ຊϊοΫ

Slide 46

Slide 46 text

No content

Slide 47

Slide 47 text

ࣗशͷݟ͑ΔԽͱ૬ޓ࡞༻

Slide 48

Slide 48 text

No content

Slide 49

Slide 49 text

#3 ʮ੹೚ൣғ΁ͷݻࣥ(ηΫγϣφϦζϜ)ʯ͔Β ʮӽڥ͢Δ૊৫ʯ΁ ֶश ӽڥ ڞ༗

Slide 50

Slide 50 text

ʮ৬छΛӽ͑ΔͨΊͷ؀ڥͷ੔උʯ ඇΤϯδχΞ΁ͷ෼ੳ༻DBެ։

Slide 51

Slide 51 text

ʮηΩϡϦςΟͷͨΊɺDBʹΞΫηεͰ͖Δਓ਺͸࠷খݶʹ͢΂͖ʯ ٕज़ʹΑΔ੍໿ͷճආ

Slide 52

Slide 52 text

No content

Slide 53

Slide 53 text

ݸਓ৘ใϚεΩϯάϓϩάϥϜ ຊ൪ܥDBʹ֨ೲ͞Εͨݸਓ৘ใΛอޢ(ϚεΫ)͠ɺ ࣾ಺ελοϑ͕৮ΕΔΑ͏͢ΔʹͨΊͷϓϩάϥϜɻ DBϚεΩϯάॲཧΛ؆୯ʹهड़Ͱ͖ΔΑ͏ɺ Α͘࢖ΘΕΔϚεΩϯάύλʔϯΛجຊϓϩάϥϜଆͰ༻ҙ͍ͯ͠Δɻ ςʔϒϧ໊ͱΧϥϜ໊Λࢦఆ͢Ε͹ଈϚεΩϯάରԠՄೳɻ

Slide 54

Slide 54 text

ύλʔϯ マスキング処理内容 ࢯ໊ 「山田太郎」「ヤマダタロウ」「ﾔﾏﾀﾞﾀﾛｳ」「やまだたろう」のどれかに変更 ॅॴ ʮ౦ژ౎ौ୩۠ೆฏ୆ொ16-28ʯʹมߋ Ϗϧ໊ 「グラスシティ渋谷 2F」に変更 ϝʔϧΞυϨε “@”とトップレベルドメイン以外をハッシュ化。ただしデバッグのため自社メールアドレス(*.livesense.co.jp)はハッシュ化しない ޱ࠲൪߸ 半角7文字の乱数で埋める ۜߦίʔυ 半角4文字の乱数で埋める ۜߦࢧళ൪߸ 半角3文字の乱数で埋める ͦͷଞ 「このカラムはマスキングされています。」に変更 اۀ໊ 「株式会社マスキング」「カブシキガイシャマスキング」のどれかに変更 ి࿩൪߸ 半角12文字のユニークな乱数で埋める جຊϓϩάϥϜͰ༻ҙ͍ͯ͠ΔϚεΩϯάύλʔϯ

Slide 55

Slide 55 text

ͦͷ্Ͱ

Slide 56

Slide 56 text

·ͣ͸࠶ར༻͔Β

Slide 57

Slide 57 text

No content

Slide 58

Slide 58 text

ΤϯδχΞओಋͷτϨʔχϯά ʮӽڥऀʹ͸൐૸Λʯ

Slide 59

Slide 59 text

एख(ೖࣾ1~2೥໨૬౰)2໊ ڭࡐ࡞੒(SQL100ຊϊοΫ) &࣮஍ͰͷτϨʔχϯά ڭ͑Δ͔ΘΓʹɺଞͷਓ΁΋ڭ͑Δ໿ଋ

Slide 60

Slide 60 text

࣮஍ͰͷτϨʔχϯά

Slide 61

Slide 61 text

No content

Slide 62

Slide 62 text

No content

Slide 63

Slide 63 text

No content

Slide 64

Slide 64 text

ɿ ɿ ࢦఠ͞Εͨͱ͜ΖΛ΋͏Ұ౓

Slide 65

Slide 65 text

No content

Slide 66

Slide 66 text

ɿ ɿ

Slide 67

Slide 67 text

೉͍͠ͱ͜Ζ͸ExcelͰ৐Γ੾Δ

Slide 68

Slide 68 text

ͦͷޙɺ׳Ε͖ͯͨࠒʹ

Slide 69

Slide 69 text

400ສ݅ͷϑϧεΩϟϯ

Slide 70

Slide 70 text

ॏ͍ΫΤϦͰDBαʔόࢭ·Δ

Slide 71

Slide 71 text

No content

Slide 72

Slide 72 text

No content

Slide 73

Slide 73 text

No content

Slide 74

Slide 74 text

No content

Slide 75

Slide 75 text

“ ʔ “ະདྷاۀ ϨδϦΤϯεͷܦӦͱϦʔμʔγοϓ”ɹ94ϖʔδ ڠಇΛ࠷େݶʹଅͨ͢Ίʹ ɹ੹຿ͷಁ໌ੑΛߴΊΔ ɹαΠόʔۭؒͰ৴པΛߴΊͳ͕Β਌ກΛਂΊΔ ɹίϛϡχέʔγϣϯΛ͔ܽ͞ͳ͍ ɹࢥ͍΍ΓͷॏཁੑΛཧղ͢Δ

Slide 76

Slide 76 text

ͦͷޙɺ֤νʔϜͰͷ఻ಓࢣతͳ׆ಈ ʮӽڥͨ͠Β఻ಓΛʯ

Slide 77

Slide 77 text

֤ϝσΟΞ͝ͱͷ100ຊϊοΫͷ࡞੒

Slide 78

Slide 78 text

఻ಓࢣత׆ಈ

Slide 79

Slide 79 text

ΞϧόΠτελοϑʹ΋

Slide 80

Slide 80 text

ΞϧόΠτελοϑ޲͚ษڧձࢿྉ

Slide 81

Slide 81 text

No content

Slide 82

Slide 82 text

શһ͕SQLΛॻ͘Α͏ʹͳΔ·Ͱͷࣄྫ͸͜͜·Ͱɻ

Slide 83

Slide 83 text

ʔ “ઓུαϑΝϦ”ɹ258ϖʔδ ૊৫ֶशͷجຊݪଇ ɹࣦഊ͔ΒֶͿ ɹઈ͑ؒͳ͍࠶ݕূ ɹ௚઀ମݧʹΑΔֶश ɹ஌ࣝͷྲྀಈੑΛอͭ ɹ֎քʹ໨Λ޲͚ɺ஌ࣝΛٵऩ͢Δ “

Slide 84

Slide 84 text

One more thing…

Slide 85

Slide 85 text

ֶश ӽڥ ڞ༗

Slide 86

Slide 86 text

ֶश ӽڥ ڞ༗ ஌ࣝ૑଄

Slide 87

Slide 87 text

ֶश Internalization (಺໘Խ) ӽڥ Socialization (ڞಉԽ) ڞ༗ Externalization (දग़Խ) ஌ࣝ૑଄ Combination (࿈݁Խ) ଟ͘ͷ “஌ࣝ૑଄اۀ” Ͱ  ݟΒΕΔ “SECIϞσϧ”

Slide 88

Slide 88 text

“૊৫తͳ஌ࣝ૑଄͸ ݸਓϨϕϧ͔Β࢝·Γɺ ϝϯόʔؒͷ૬ޓ࡞༻͕ɺ՝ɺ෦ɺࣄۀ෦໳ɺ ͦͯ͠૊৫ͱ͍͏ڞಉମͷ࿮Λ௒͑ͯ ্ঢɺ֦େ͍ͯ͘͠εύΠϥϧɾϓϩηε ʔ “஌ࣝ૑଄اۀ”ɹ108ϖʔδ

Slide 89

Slide 89 text

͜ͷεύΠϥϧ͕ճΓग़͢͜ͱͰɺ ΑΓ૊৫΍৬छΛ௒͑ͨίϥϘϨʔγϣϯ͕ੜ·ΕΔ

Slide 90

Slide 90 text

Ӧۀ͞Μ΋SQLΛॻ͘Α͏ʹͳͬͨࠒʹ

Slide 91

Slide 91 text

No content

Slide 92

Slide 92 text

No content

Slide 93

Slide 93 text

No content

Slide 94

Slide 94 text

No content

Slide 95

Slide 95 text

SQLΫΤϦΛొ࿥͓͖ͯ͠

Slide 96

Slide 96 text

ExcelͷWebΫΤϦػೳͰɺσʔλΛऔಘɾߋ৽

Slide 97

Slide 97 text

֤छ෼ੳΛExcelϕʔεͰߦ͑ΔΑ͏ʹ

Slide 98

Slide 98 text

ͦͯͦ͠ͷ݁Ռ͸ɺσΠϦʔνϟʔτͱͯ͠νʔϜʹڞ༗

Slide 99

Slide 99 text

·ͱΊ

Slide 100

Slide 100 text

ֶशɾڞ༗ͷ؀ڥΛ੔্͑ͨͰɺ ʮӽڥʯΛଅ͢͜ͱͰɺ ৬छΛ௒͑ͨίϥϘϨʔγϣϯΛߦ͏͜ͱ͕Ͱ͖Δɻ SQL͸ͦͷҰྫɻ

Slide 101

Slide 101 text

ࣗ෼ͷ࢓ࣄͷྖҬΛܾΊֶ͗ͣ͢Ϳ͜ͱ ૬खͷ࢓ࣄͷྖҬΛܟҙΛ΋ͬͯ৵͢͜ͱ  ͏·͍ͬͨ͘͜ͱΛɺ۩ମతʹڞ༗͢Δ͜ͱ ͏·͍ͬͨ͘εύΠϥϧͷ఻ಓࢣͱͳΔ͜ͱ

Slide 102

Slide 102 text

ͦͯ͠ɺ ͜ͷαΠΫϧΛ௨ͯ͡օ͕Ұॹʹɺ ྑ͍࢓ࣄɾྑ͍αʔϏεΛ࡞͍ͬͯ͜͏ͱ͢Δ͜ͱ

Slide 103

Slide 103 text

ͦͷઌʹ͋Δɺʮ૑଄ܕ૊৫ʯΛ໨ࢦ͠ଓ͚Δ͜ͱ

Slide 104

Slide 104 text

“ ʔ “ΠϊϕʔγϣϯͷDNA”ɹ53ϖʔδ ΠϊϕʔςΟϒͳΞΠσΞ͸ɺ͞·͟·ͳਓͨͪ ͷଟ༷ͳܦݧ͕ަΘΔ৔ॴͰՖ։͘ɻྺ࢙Λ௨͡ ͯɺҒେͳΞΠσΞ͸จԽ΍ܦݧͷަΘΔͱ͜Ζ Ͱੜ·Ε͍ͯΔɻ

Slide 105

Slide 105 text

No content

Slide 106

Slide 106 text

෇࿥ ࠓ೔͔Βࣾ಺Ͱ࢖͑ΔSQL10ຊϊοΫ

Slide 107

Slide 107 text

Ϧϒηϯεࣾ಺Ͱ࢖ΘΕ͍ͯΔ࣮ࡍͷʮSQL100ຊϊοΫʯͷ͏ͪ 10ຊΛݫબ͠ɺ ڭҭ༻్ͱͯ͠ѻ͍΍͍͢Α͏ʹεΩʔϚΛΧελϚΠζͨ͠΋ͷͰ͢ɻ DB؀ڥ͕ͳ͘ͱ΋ɺϒϥ΢β͕͋Ε͹࣮ࢪͰ͖ΔΑ͏ʹ४උ͠·ͨ͠ɻ

Slide 108

Slide 108 text

୊ࡐ͸ʮΞϧόΠτٻਓ৘ใαΠτʯ (※وࣾʹೃછΈͷ͋Δ୊ࡐʹมߋ͓ͯ͠࢖͍͍ͩ͘͞)

Slide 109

Slide 109 text

Ԡืςʔϒϧɺళฮςʔϒϧɺ౎ಓ෎ݝςʔϒϧͷߏ੒

Slide 110

Slide 110 text

ςʔϒϧઆ໌::entries(Ԡื) ΧϥϜ໊ આ໌ ྫ JE *% OBNF Ԡืऀࢯ໊ ໦ଜଠ࿠ CJSUIEBZ ੜ೥݄೔ NBJM ϝʔϧΞυϨε GPP!CBSOFU EBUF Ԡื೔ BEPQU@TUBUVT ࠾༻֬ఆ࣌ʹɺ ෆ࠾༻֬ఆ࣌ʹɺ ͦ͏Ͱͳ͚Ε͹ BEPQU@EBUF ࠾༻ɾෆ࠾༻֬ఆ೔ ະ֬ఆͷͱ͖͸ DMJFOU@JE ళฮ*% EFWJDF@UZQF Ԡื࣌ʹར༻ͨ͠  σόΠε 4."351)0/&1$

Slide 111

Slide 111 text

ςʔϒϧઆ໌::client(ళฮ) ΧϥϜ໊ આ໌ ྫ JE *% OBNF ళฮ໊ Ҫଜ঎ళ QSFG@JE ౎ಓ෎ݝ*%

Slide 112

Slide 112 text

ςʔϒϧઆ໌::pref(౎ಓ෎ݝ) ΧϥϜ໊ આ໌ ྫ JE *% OBNF ౎ಓ෎ݝ໊ ౦ژ౎

Slide 113

Slide 113 text

http://sqlﬁddle.com/ ؀ڥ ϕʔεSQLʢDBεΩʔϚʣ http://made.livesense.co.jp/blogs/311

Slide 114

Slide 114 text

http://sqlﬁddle.com/ɹʹͯɺϕʔεSQLΛ౤ೖ

Slide 115

Slide 115 text

·ͣɺςʔϒϧ಺ͷ݅਺Λग़ͯ͠Έ·͠ΐ͏ ಈ࡞֬ೝ Run SQLΛԡͯ͠ɺ݅਺͕ग़Ε͹੒ޭͰ͢ɻ

Slide 116

Slide 116 text

ͦΕͰ͸10ຊϊοΫɺߦͬͯΈ·͠ΐ͏

Slide 117

Slide 117 text

2013೥2݄ͷԠื਺

Slide 118

Slide 118 text

SELECT COUNT(*) FROM entries WHERE entries.date BETWEEN '2013-02-01 00:00:00' AND '2013-02-31 23:59:59' ;

Slide 119

Slide 119 text

2013೥2݄ͷ࠾༻਺

Slide 120

Slide 120 text

SELECT COUNT(*) FROM entries WHERE entries.adopt_status = 1 AND entries.date BETWEEN '2013-02-01 00:00:00' AND '2013-02-31 23:59:59' ;

Slide 121

Slide 121 text

2013೥2݄ͷෆ࠾༻਺

Slide 122

Slide 122 text

SELECT COUNT(*) FROM entries WHERE entries.adopt_status = 2 AND entries.date BETWEEN '2013-02-01 00:00:00' AND '2013-02-31 23:59:59' ;

Slide 123

Slide 123 text

2013೥2݄ʹԠื͕͋ͬͨళฮ਺

Slide 124

Slide 124 text

SELECT COUNT(DISTINCT client_id) FROM entries WHERE entries.date BETWEEN '2013-02-01 00:00:00' AND '2013-02-31 23:59:59' ;

Slide 125

Slide 125 text

2013೥2݄ͷԠื਺લ೥ൺ

Slide 126

Slide 126 text

SELECT SUM(entries.date between '2013-02-01' and '2013-02-31 23:59:59') / SUM(entries.date between '2012-02-01' and '2012-02-31 23:59:59') AS ratio FROM entries WHERE YEAR(entries.date) IN (2012,2013) AND MONTH(entries.date) = 2 ;

Slide 127

Slide 127 text

2013೥2݄ͷ౦ژ౎ͷԠื਺

Slide 128

Slide 128 text

SELECT prefs.id, prefs.name, COUNT(*) FROM clients INNER JOIN prefs ON clients.pref_id = prefs.id INNER JOIN entries ON clients.id = entries.client_id WHERE entries.date BETWEEN '2013-02-01 00:00:00' AND '2013-02-31 23:59:59' AND prefs.name = '౦ژ౎' GROUP BY prefs.id, prefs.name ORDER BY prefs.id, prefs.name ;

Slide 129

Slide 129 text

2013೥2݄ͷ౎ಓ෎ݝผԠื਺ϥϯΩϯά্Ґ̏Ґ

Slide 130

Slide 130 text

SELECT prefs.id, prefs.name, COUNT(*) FROM clients INNER JOIN prefs ON clients.pref_id = prefs.id INNER JOIN entries ON clients.id = entries.client_id WHERE entries.date BETWEEN '2013-02-01 00:00:00' AND '2013-02-31 23:59:59' GROUP BY prefs.id, prefs.name ORDER BY COUNT(*) DESC LIMIT 3 ;

Slide 131

Slide 131 text

2013೥2݄ͷσόΠεผԠื਺

Slide 132

Slide 132 text

SELECT device_type, COUNT(*) FROM entries WHERE entries.date BETWEEN '2013-02-01 00:00:00' AND '2013-02-31 23:59:59' GROUP BY device_type ;