150225_sql_for_everyone.pdf

Transcript

1. How Livesense Works #1 Ӧۀ͞Μ·Ͱࣾһશһ͕SQLΛ࢖͏ ʮӽڥܕ૊৫ʯ͕Ͱ͖Δ·Ͱͷ3+1ͷϙΠϯτ Yukihiko Kawarazuka, Engineer of Livesense, inc kawarazuka@livesense.co.jp
2. ͋Δ೔ɺӦۀ͞Μ͔Βདྷͨґཔ
6. ͋Δ͋Δ
7. ΘΓͱͬ͘͟Γ࢓༷ɻ ※൓ڹ=͓໰͍߹Θͤͷ͜ͱ
8. Ͱ΋
10. ͋Δ͋Δ
11. ͦͷ8ϲ݄ޙ
16. ͜Ε͸΄ΜͷҰྫ
17. ͳͥʁ ͳͥɺӦۀ͞Μ΋SQLΛॻ͘ͷ͔ ͳͥɺӦۀ͞Μʹ·ͰσʔλϕʔεΞΫηεݖΛ։์͍ͯ͠Δͷ͔ ͳͥɺSQLΛֶश͠Α͏ͱࢥͬͨͷ͔ ͳͥɺֶशΛ׬਱Ͱ͖ͨͷ͔ ͳͥɺֶशʹͱͲ·Β࣮ͣӡ༻ʹ଱͑ΔεΩϧʹঢ՚͞Εͨͷ͔ ͳͥɺ͜ͷΑ͏ͳڠۀจԽ͕Ͱ͖ɺࠜ෇͍ͨͷ͔
18. ࢲ͸લ৬ͰʮଵଦͰԣฑͰ௚ײཔΓͳӦۀʯʹ͠ͼΕΛ੾Β͠ɺ*1 ։ൃΛ΍Γͭͭࣗ෼ͰӦۀ΋͍ͯͨ͠ͷͰ ʮͳͥʁʯΛӽ͑ͯʮγϣοΫʯͩͬͨɻ *1 ੾Βͨ͠ͷ͸͠ͼΕ͚ͩͰ͸ͳ͔͕ͬͨ
19. ӦۀɹɹʮσʔλϕʔεʁͦΜͳΑ͘Θ͔ΒΜ΋ͷӦۀʹ͸ؔ܎ͳ͍ʯ γε؅ɹʮDBʹΞΫηεͰ͖Δਓ਺͸ɺ࠷খݶʹ͢΂͖ɻ ɹɹɹɹɹٕज़ίϯλϛΛ๷͙ͨΊʹɺ෦ॺ֎ͷਓؒ͸ΞΫηεېࢭʯ Ϛʔέɹʮੲ͍ͨʓʓ͞Μ͸SQLͰ΍ͬͯͨΈ͍͚ͨͩͲɺɺ͸ͯͲ͏΍ΔΜ͚ͩͬʯ ϓϩϚωʮ͍͍΋ͷΛ։ൃ͠Α͏ɻ͍͍΋ͷΛ࡞Ε͹Ӧۀ͕ച͖ͬͯͯ͘ΕΔɻ ɹɹɹɹɹͲ͏ചΔ͔ʁ஌Βͳ͍͚ͲͦΕ͸Ӧۀ͕ɻʯ ։ൃɹɹʮ͑ͬ֎஫ͯ͠࡞ͬͯ΋ΒͬͨͷʁͦΕ͏ͪͷ෦ॺͰπʔϧԽͯͨ͠ͷʹɻʯ ࢲ͕લ৬ͰΑ͘ฉ͍ͨ੠
20. ຊ࣭తͳ՝୊ͱ͸ ແؔ৺ ੹೚ൣғͷ ݻࣥ ҙਤ͠ͳ͍ ൿີओٛ
21. ੹೚ൣғ΁ͷݻࣥʢηΫγϣφϦζϜʣ ݪҼ: ࣗ෼͕༩͑ΒΕͨ੹೚Λશ͏͠Α͏ͱ͢Δɺ੹೚ײʹىҼɻ ੹೚Λશ͏͢Δͷ͸ɺ౰વॏཁɻ ͔͠͠෦෼࠷దʹͳ͍ͬͯͳ͍͔ʁ ʮηΩϡϦςΟͷͨΊɺDBʹΞΫηεͰ͖Δਓ਺͸࠷খݶʹ͢΂͖ʯ ʮٕज़ίϯλϛΛ๷͙ͨΊʹɺ෦ॺ֎ͷਓؒ͸ΞΫηεېࢭʯ
22. ແؔ৺ ʮσʔλϕʔεʁͦΜͳΑ͘Θ͔ΒΜ΋ͷӦۀʹ͸ؔ܎ͳ͍ʯ ʮ͍͍΋ͷΛ։ൃ͠Α͏ɻ ͍͍΋ͷΛ࡞Ε͹Ӧۀ͕ച͖ͬͯͯ͘ΕΔɻ Ͳ͏ചΔ͔ʁ஌Βͳ͍͚ͲͦΕ͸Ӧۀ͕ɻʯ ݪҼ: (1)ࣗ෼ͷ࣋ͭڧΈʢٕज़ɾϊ΢ϋ΢ʣͷΈͰ࢓ࣄΛଊ͑ɺͦΕΛҰൠԽͯ͠͠·͏ɻ (2)ࣄۀ΍αʔϏεશମΛ၆ᛌͨ͠ࢹ໺͕͓࣋ͯͯΒͣɺ ࣗ෼ͷۀ຿֎ͷ෦෼Λٵऩ͠Α͏ͱ͠ͳ͍ɻ ”੹೚ൣғ΁ͷݻࣥ”͔Βੜ·Ε͍ͯΔ͜ͱ΋ଟ͍ɻ
23. ҙਤ͠ͳ͍ൿີओٛ ݪҼ: (1)ຊ౰͸ͪΌΜͱօʹڞ༗͍͕ͨ͠ɺஔ͖৔͕ͳ͍ͨΊޱ఻ͷੈքʹ (2)ଞʹඞཁͳਓɺ͋Δͱخ͍͠ਓ͕͍Δ͜ͱΛ஌Βͳ͍ ͦͷ݁Ռɺਓ͕໨ʹಧ͔ͳ͍ͱ͜Ζʹ੒Ռ෺͕͓͔ΕɺҙਤͤͣʹൿີओٛԽ͢Δɻ “ແؔ৺”͔ΒҾ͖ى͜͞ΕΔ͜ͱ΋ଟ͍ɻ ʮੲ͍ͨʓʓ͞Μ͸ͦ͏΍ͬͯͨΈ͍͚ͨͩͲɺɺ͸ͯͲ͏΍ΔΜ͚ͩͬʯ ʮ͑ͬ֎஫ͯ͠࡞ͬͯ΋ΒͬͨͷʁͦΕ͏ͪͷ෦ॺͰπʔϧԽͯͨ͠ͷʹɻʯ
24. ແؔ৺ ੹೚ൣғͷ ݻࣥ ҙਤ͠ͳ͍ ൿີओٛ ͦΜͳΑ͘Θ͔ΒΜ΋ͷӦۀʹ͸ؔ܎ͳ͍ ͓٬༷ͷ৘ใ͕ೖͬͨσʔλΛ ͩΕͰ΋৮ΒͤΔΘ͚ʹ͸͍͔Μ ੲ͍ͨʓʓ͞Μ͸ ͦ͏΍ͬͯͨΈ͍͚ͨͩͲ
25. ֤ʑ͕֤ʑΛҾ͖ى͜͢ҼՌؔ܎ʹ͋Γɺ ૊Έ߹Θ͞ΔͱɺͻͲ͍ѱ॥؀ʹɻ
26. ͜Μͳѱ॥؀ʹ͠ͳ͍ͨΊʹɺ ͳʹ͕Ͱ͖Δ͔ɻ
27. #1 ʮҙਤ͠ͳ͍ൿີओٛʯ͔Βʮڞ༗Χϧνϟʔͷৢ੒ʯ΁ ɹɾ৘ใڞ༗͢ΔͨΊͷͷΠϯϑϥΛ༻ҙ͢Δ ɹɾ৘ใڞ༗ϚΠϯυΛৢ੒͢Δ #2 ʮແؔ৺ʯ͔ΒʮֶशΛ௨ͨ͡૬ޓཧղʯ΁ ɹɾؔ৺ΛҾ͘ࢿྉΛڞ༗͠ɺͦΕΛ࣮ݱ͢ΔͨΊͷٕज़ཁૉͷఏࣔ ɹɾ਎ۙͳ୊ࡐΛ༻͍ͨڭࡐͷ༻ҙ ɹɾֶशͷݟ͑ΔԽʹΑΔ૬ޓ࡞༻ #3 ʮ੹೚ൣғ΁ͷݻࣥ(ηΫγϣφϦζϜ)ʯ͔Βʮӽڥ͢Δ૊৫ʯ΁ ɹɾ৬छΛӽ͑ΔͨΊͷ؀ڥͷ੔උ ɹɾӽڥऀʹ͸൐૸Λ ɹɾӽڥͨ͠Β఻ಓΛ
28. ແؔ৺ ੹೚ൣғͷ ݻࣥ ҙਤ͠ͳ͍ ൿີओٛ
29. ֶश ӽڥ ڞ༗
30. ૊৫΍৬छͱ͍͏ൣᙝΛӽڥͯ͠ڠۀΛߦ͍ɺ ͦͷࣄྫΛڞ༗͠ɺ ͦͷڞ༗ࣄྫ͕͞ΒͳΔֶशҙཉΛੜΉ͜ͱͰɺ ΑΓ૊৫΍৬छΛ௒͑ͨίϥϘϨʔγϣϯ͕ੜ·ΕΔαΠΫϧ͕ཧ૝త
31. Ӧۀ͞Μ΋SQLΛॻ͘Α͏ʹͳΔ·Ͱͷࣄྫ
32. #1 ʮҙਤ͠ͳ͍ൿີओٛʯ͔Β ʮڞ༗Χϧνϟʔͷৢ੒ʯ΁ ֶश ӽڥ ڞ༗
33. ಋೖ͍ͯ͠Δ৘ใڞ༗Πϯϑϥ Redmine(ݱࡏ͸JIRAʹҠߦத) Confluence Hipchat Cybouzu GoogleApps جຊ͸ࣾһʹରͯ͠ΦʔϓϯΞΫηε ඞཁʹԠ੍ͯ͡ݶ͢ΔϙϦγʔ
34. Redmine(νέοττϥοΩϯάγεςϜ) ʢ˞ݱࡏ͸Atlassian JIRAʹҠߦதʣ ͢΂ͯͷۀ຿ϑϩʔΛҰͭͷγεςϜʹू໿ɻ Ӧۀ͞Μ͔ΒͷSQLൃߦґཔ͔Β
 ϓϩμΫτͷ࢓༷ܾఆ·Ͱɺ ޙ͔Βݟฦ͠ܦҢΛ஌Δ͜ͱ͕Ͱ͖Δɻ →৽͘͠ೖࣾͯ͠΋ɺ૑ۀॳظ͔ΒͷܦҢΛ ஌Δ͜ͱ͕Ͱ͖Δ
35. Atlassian Confluence (ࣾ಺WIKI) τοϓϖʔδ͸ਓؾॱɻ اըɾΤϯδχΞϦϯά͔Β ʮຊ౰ʹ೉͍͠γϣʔτέʔΩʯ·Ͱɺ ϑϥοτʹฒͿɻ
36. Atlassian HipChat (νϟοτ) ࣄۀผROOM(ඞਢࢀՃ)ͱɺ
 ཁૉผROOM(೚ҙࢀՃ)Λ༻ҙɻ
37. ৘ใڞ༗Πϯϑϥͷಋೖ ≠ ɹ ৘ใڞ༗Χϧνϟʔͷৢ੒
38. ɹ৘ใڞ༗Χϧνϟʔͷৢ੒ɹʼɹ৘ใڞ༗Πϯϑϥͷಋೖ
40. ɹ৘ใڞ༗Χϧνϟʔͷৢ੒ɹʼɹ৘ใڞ༗Πϯϑϥͷಋೖ
41. #2 ʮແؔ৺ʯ͔Β ʮֶशΛ௨ͨ͡૬ޓཧղʯ΁ ֶश ӽڥ ڞ༗
42. ؔ৺ΛҾ͘Ϩϙʔτͱɺͦͷڞ༗
44. ௐࠪϨϙʔτʹSQLΫΤϦ͕ซه͞ΕΔ͜ͱͰɺ ؔ৺ࣄͱཁૉٕज़͕ͭͳ͕Δ ࠶ݱํ๏͕͋Δ͜ͱͰ௥ࢼΛߦ͑ΔɺਅࣅͰ͖Δ
45. ਎ۙͳ୊ࡐΛ༻͍ͨڭࡐ: SQL100ຊϊοΫ
47. ࣗशͷݟ͑ΔԽͱ૬ޓ࡞༻
49. #3 ʮ੹೚ൣғ΁ͷݻࣥ(ηΫγϣφϦζϜ)ʯ͔Β ʮӽڥ͢Δ૊৫ʯ΁ ֶश ӽڥ ڞ༗
50. ʮ৬छΛӽ͑ΔͨΊͷ؀ڥͷ੔උʯ ඇΤϯδχΞ΁ͷ෼ੳ༻DBެ։
51. ʮηΩϡϦςΟͷͨΊɺDBʹΞΫηεͰ͖Δਓ਺͸࠷খݶʹ͢΂͖ʯ ٕज़ʹΑΔ੍໿ͷճආ
53. ݸਓ৘ใϚεΩϯάϓϩάϥϜ ຊ൪ܥDBʹ֨ೲ͞Εͨݸਓ৘ใΛอޢ(ϚεΫ)͠ɺ ࣾ಺ελοϑ͕৮ΕΔΑ͏͢ΔʹͨΊͷϓϩάϥϜɻ DBϚεΩϯάॲཧΛ؆୯ʹهड़Ͱ͖ΔΑ͏ɺ Α͘࢖ΘΕΔϚεΩϯάύλʔϯΛجຊϓϩάϥϜଆͰ༻ҙ͍ͯ͠Δɻ ςʔϒϧ໊ͱΧϥϜ໊Λࢦఆ͢Ε͹ଈϚεΩϯάରԠՄೳɻ
54. ύλʔϯ マスキング処理内容 ࢯ໊ 「山田 太郎」「ヤマダ タロウ」「ﾔﾏﾀﾞﾀﾛｳ」「やまだ たろう」のどれかに変更 ॅॴ ʮ౦ژ౎ौ୩۠ೆฏ୆ொ16-28ʯʹมߋ Ϗϧ໊ 「グラスシティ渋谷 2F」に変更 ϝʔϧΞυϨε “@”とトップレベルドメイン以外をハッシュ化。ただしデバッグのため自社メ ールアドレス(*.livesense.co.jp)はハッシュ化しない ޱ࠲൪߸ 半角7文字の乱数で埋める ۜߦίʔυ 半角4文字の乱数で埋める ۜߦࢧళ൪߸ 半角3文字の乱数で埋める ͦͷଞ 「このカラムはマスキングされています。」に変更 اۀ໊ 「株式会社マスキング」「カブシキガイシャマスキング」のどれかに変更 ి࿩൪߸ 半角12文字のユニークな乱数で埋める جຊϓϩάϥϜͰ༻ҙ͍ͯ͠ΔϚεΩϯάύλʔϯ
55. ͦͷ্Ͱ
56. ·ͣ͸࠶ར༻͔Β
58. ΤϯδχΞओಋͷτϨʔχϯά ʮӽڥऀʹ͸൐૸Λʯ
59. एख(ೖࣾ1~2೥໨૬౰)2໊ ڭࡐ࡞੒(SQL100ຊϊοΫ) &࣮஍ͰͷτϨʔχϯά ڭ͑Δ͔ΘΓʹɺଞͷਓ΁΋ڭ͑Δ໿ଋ
60. ࣮஍ͰͷτϨʔχϯά
64. ɿ ɿ ࢦఠ͞Εͨͱ͜ΖΛ΋͏Ұ౓
66. ɿ ɿ
67. ೉͍͠ͱ͜Ζ͸ExcelͰ৐Γ੾Δ
68. ͦͷޙɺ׳Ε͖ͯͨࠒʹ
69. 400ສ݅ͷϑϧεΩϟϯ
70. ॏ͍ΫΤϦͰDBαʔόࢭ·Δ
75. “ ʔ “ະདྷاۀ ϨδϦΤϯεͷܦӦͱϦʔμʔγοϓ”ɹ94ϖʔδ ڠಇΛ࠷େݶʹଅͨ͢Ίʹ
    ɹ੹຿ͷಁ໌ੑΛߴΊΔ
    ɹαΠόʔۭؒͰ৴པΛߴΊͳ͕Β਌ກΛਂΊΔ
    ɹίϛϡχέʔγϣϯΛ͔ܽ͞ͳ͍
    ɹࢥ͍΍ΓͷॏཁੑΛཧղ͢Δ
76. ͦͷޙɺ֤νʔϜͰͷ఻ಓࢣతͳ׆ಈ ʮӽڥͨ͠Β఻ಓΛʯ
77. ֤ϝσΟΞ͝ͱͷ100ຊϊοΫͷ࡞੒
78. ఻ಓࢣత׆ಈ
79. ΞϧόΠτελοϑʹ΋
80. ΞϧόΠτελοϑ޲͚ษڧձࢿྉ
82. શһ͕SQLΛॻ͘Α͏ʹͳΔ·Ͱͷࣄྫ͸͜͜·Ͱɻ
83. ʔ “ઓུαϑΝϦ”ɹ258ϖʔδ ૊৫ֶशͷجຊݪଇ
    ɹࣦഊ͔ΒֶͿ
    ɹઈ͑ؒͳ͍࠶ݕূ
    ɹ௚઀ମݧʹΑΔֶश
    ɹ஌ࣝͷྲྀಈੑΛอͭ
    ɹ֎քʹ໨Λ޲͚ɺ஌ࣝΛٵऩ͢Δ “
84. One more thing…
85. ֶश ӽڥ ڞ༗
86. ֶश ӽڥ ڞ༗ ஌ࣝ૑଄
87. ֶश Internalization (಺໘Խ) ӽڥ Socialization (ڞಉԽ) ڞ༗ Externalization (දग़Խ) ஌ࣝ૑଄ Combination (࿈݁Խ) ଟ͘ͷ “஌ࣝ૑଄اۀ” Ͱ
 ݟΒΕΔ “SECIϞσϧ”
88. “૊৫తͳ஌ࣝ૑଄͸
    ݸਓϨϕϧ͔Β࢝·Γɺ
    ϝϯόʔؒͷ૬ޓ࡞༻͕ɺ՝ɺ෦ɺࣄۀ෦໳ɺ
    ͦͯ͠૊৫ͱ͍͏ڞಉମͷ࿮Λ௒͑ͯ
    ্ঢɺ֦େ͍ͯ͘͠εύΠϥϧɾϓϩηε ʔ “஌ࣝ૑଄اۀ”ɹ108ϖʔδ
89. ͜ͷεύΠϥϧ͕ճΓग़͢͜ͱͰɺ ΑΓ૊৫΍৬छΛ௒͑ͨίϥϘϨʔγϣϯ͕ੜ·ΕΔ
90. Ӧۀ͞Μ΋SQLΛॻ͘Α͏ʹͳͬͨࠒʹ
95. SQLΫΤϦΛొ࿥͓͖ͯ͠
96. ExcelͷWebΫΤϦػೳͰɺσʔλΛऔಘɾߋ৽
97. ֤छ෼ੳΛExcelϕʔεͰߦ͑ΔΑ͏ʹ
98. ͦͯͦ͠ͷ݁Ռ͸ɺσΠϦʔνϟʔτͱͯ͠νʔϜʹڞ༗
99. ·ͱΊ
100. ֶशɾڞ༗ͷ؀ڥΛ੔্͑ͨͰɺ ʮӽڥʯΛଅ͢͜ͱͰɺ ৬छΛ௒͑ͨίϥϘϨʔγϣϯΛߦ͏͜ͱ͕Ͱ͖Δɻ SQL͸ͦͷҰྫɻ
101. ࣗ෼ͷ࢓ࣄͷྖҬΛܾΊֶ͗ͣ͢Ϳ͜ͱ ૬खͷ࢓ࣄͷྖҬΛܟҙΛ΋ͬͯ৵͢͜ͱ
 ͏·͍ͬͨ͘͜ͱΛɺ۩ମతʹڞ༗͢Δ͜ͱ ͏·͍ͬͨ͘εύΠϥϧͷ఻ಓࢣͱͳΔ͜ͱ
102. ͦͯ͠ɺ ͜ͷαΠΫϧΛ௨ͯ͡օ͕Ұॹʹɺ ྑ͍࢓ࣄɾྑ͍αʔϏεΛ࡞͍ͬͯ͜͏ͱ͢Δ͜ͱ
103. ͦͷઌʹ͋Δɺʮ૑଄ܕ૊৫ʯΛ໨ࢦ͠ଓ͚Δ͜ͱ
104. “ ʔ “ΠϊϕʔγϣϯͷDNA”ɹ53ϖʔδ ΠϊϕʔςΟϒͳΞΠσΞ͸ɺ͞·͟·ͳਓͨͪ ͷଟ༷ͳܦݧ͕ަΘΔ৔ॴͰՖ։͘ɻྺ࢙Λ௨͡ ͯɺҒେͳΞΠσΞ͸จԽ΍ܦݧͷަΘΔͱ͜Ζ Ͱੜ·Ε͍ͯΔɻ
106. ෇࿥ ࠓ೔͔Βࣾ಺Ͱ࢖͑ΔSQL10ຊϊοΫ
107. Ϧϒηϯεࣾ಺Ͱ࢖ΘΕ͍ͯΔ࣮ࡍͷʮSQL100ຊϊοΫʯͷ͏ͪ 10ຊΛݫબ͠ɺ ڭҭ༻్ͱͯ͠ѻ͍΍͍͢Α͏ʹεΩʔϚΛΧελϚΠζͨ͠΋ͷͰ͢ɻ DB؀ڥ͕ͳ͘ͱ΋ɺϒϥ΢β͕͋Ε͹࣮ࢪͰ͖ΔΑ͏ʹ४උ͠·ͨ͠ɻ
108. ୊ࡐ͸ʮΞϧόΠτٻਓ৘ใαΠτʯ (※وࣾʹೃછΈͷ͋Δ୊ࡐʹมߋ͓ͯ͠࢖͍͍ͩ͘͞)
109. Ԡืςʔϒϧɺళฮςʔϒϧɺ౎ಓ෎ݝςʔϒϧͷߏ੒
110. ςʔϒϧઆ໌::entries(Ԡื) ΧϥϜ໊ આ໌ ྫ JE
     *%  OBNF Ԡืऀࢯ໊ ໦ଜଠ࿠ CJSUIEBZ ੜ೥݄೔  NBJM ϝʔϧΞυϨε GPP!CBSOFU EBUF Ԡื೔  BEPQU@TUBUVT ࠾༻֬ఆ࣌ʹɺ
     ෆ࠾༻֬ఆ࣌ʹɺ
     ͦ͏Ͱͳ͚Ε͹  BEPQU@EBUF ࠾༻ɾෆ࠾༻֬ఆ೔
     ະ֬ఆͷͱ͖͸  DMJFOU@JE ళฮ*%  EFWJDF@UZQF Ԡื࣌ʹར༻ͨ͠
 σόΠε 4."351)0/&1$
111. ςʔϒϧઆ໌::client(ళฮ) ΧϥϜ໊ આ໌ ྫ JE *%  OBNF ళฮ໊ Ҫଜ঎ళ QSFG@JE ౎ಓ෎ݝ*% 
112. ςʔϒϧઆ໌::pref(౎ಓ෎ݝ) ΧϥϜ໊ આ໌ ྫ JE *%  OBNF ౎ಓ෎ݝ໊ ౦ژ౎
113. http://sqlfiddle.com/ ؀ڥ ϕʔεSQLʢDBεΩʔϚʣ http://made.livesense.co.jp/blogs/311
114. http://sqlfiddle.com/ɹʹͯɺϕʔεSQLΛ౤ೖ
115. ·ͣɺςʔϒϧ಺ͷ݅਺Λग़ͯ͠Έ·͠ΐ͏ ಈ࡞֬ೝ Run SQLΛԡͯ͠ɺ݅਺͕ग़Ε͹੒ޭͰ͢ɻ
116. ͦΕͰ͸10ຊϊοΫɺߦͬͯΈ·͠ΐ͏
117. 2013೥2݄ͷԠื਺
118. SELECT COUNT(*) FROM entries WHERE entries.date BETWEEN '2013-02-01 00:00:00' AND '2013-02-31 23:59:59' ;
119. 2013೥2݄ͷ࠾༻਺
120. 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' ;
121. 2013೥2݄ͷෆ࠾༻਺
122. 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' ;
123. 2013೥2݄ʹԠื͕͋ͬͨళฮ਺
124. SELECT COUNT(DISTINCT client_id) FROM entries WHERE entries.date BETWEEN '2013-02-01 00:00:00' AND '2013-02-31 23:59:59' ;
125. 2013೥2݄ͷԠื਺લ೥ൺ
126. 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 ;
127. 2013೥2݄ͷ౦ژ౎ͷԠื਺
128. 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 ;
129. 2013೥2݄ͷ౎ಓ෎ݝผԠื਺ϥϯΩϯά্Ґ̏Ґ
130. 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 ;
131. 2013೥2݄ͷσόΠεผԠื਺
132. 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 ;
133. Ҏ্Ͱ໰୊ऴྃͰ͢ɻ
134. ղ͍ͨࣄྫ͸ڞ༗ͯ͠΋Β·͠ΐ͏ʂ