150225_sql_for_everyone.pdf

E60aa4f80303f3f386898546ddb3686a?s=47 Livesense Inc.
March 11, 2015
42

 150225_sql_for_everyone.pdf

E60aa4f80303f3f386898546ddb3686a?s=128

Livesense Inc.

March 11, 2015
Tweet

Transcript

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

    Livesense, inc kawarazuka@livesense.co.jp
  2. ͋Δ೔ɺӦۀ͞Μ͔Βདྷͨґཔ

  3. None
  4. None
  5. None
  6. ͋Δ͋Δ

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

  8. Ͱ΋

  9. None
  10. ͋Δ͋Δ

  11. ͦͷ8ϲ݄ޙ

  12. None
  13. None
  14. None
  15. None
  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. ɹ৘ใڞ༗Χϧνϟʔͷৢ੒ɹʼɹ৘ใڞ༗Πϯϑϥͷಋೖ

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

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

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

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

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

  46. None
  47. ࣗशͷݟ͑ΔԽͱ૬ޓ࡞༻

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

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

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

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

  54. ύλʔϯ マスキング処理内容 ࢯ໊ 「山田 太郎」「ヤマダ タロウ」「ヤマダタロウ」「やまだ たろう」のどれかに変更 ॅॴ ʮ౦ژ౎ौ୩۠ೆฏ୆ொ16-28ʯʹมߋ Ϗϧ໊

    「グラスシティ渋谷 2F」に変更 ϝʔϧΞυϨε “@”とトップレベルドメイン以外をハッシュ化。ただしデバッグのため自社メ ールアドレス(*.livesense.co.jp)はハッシュ化しない ޱ࠲൪߸ 半角7文字の乱数で埋める ۜߦίʔυ 半角4文字の乱数で埋める ۜߦࢧళ൪߸ 半角3文字の乱数で埋める ͦͷଞ 「このカラムはマスキングされています。」に変更 اۀ໊ 「株式会社マスキング」「カブシキガイシャマスキング」のどれかに変更 ి࿩൪߸ 半角12文字のユニークな乱数で埋める جຊϓϩάϥϜͰ༻ҙ͍ͯ͠ΔϚεΩϯάύλʔϯ
  55. ͦͷ্Ͱ

  56. ·ͣ͸࠶ར༻͔Β

  57. None
  58. ΤϯδχΞओಋͷτϨʔχϯά ʮӽڥऀʹ͸൐૸Λʯ

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

  60. ࣮஍ͰͷτϨʔχϯά

  61. None
  62. None
  63. None
  64. ɿ ɿ ࢦఠ͞Εͨͱ͜ΖΛ΋͏Ұ౓

  65. None
  66. ɿ ɿ

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

  68. ͦͷޙɺ׳Ε͖ͯͨࠒʹ

  69. 400ສ݅ͷϑϧεΩϟϯ

  70. ॏ͍ΫΤϦͰDBαʔόࢭ·Δ

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

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

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

  78. ఻ಓࢣత׆ಈ

  79. ΞϧόΠτελοϑʹ΋

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

  81. None
  82. શһ͕SQLΛॻ͘Α͏ʹͳΔ·Ͱͷࣄྫ͸͜͜·Ͱɻ

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

  84. One more thing…

  85. ֶश ӽڥ ڞ༗

  86. ֶश ӽڥ ڞ༗ ஌ࣝ૑଄

  87. ֶश Internalization (಺໘Խ) ӽڥ Socialization (ڞಉԽ) ڞ༗ Externalization (දग़Խ) ஌ࣝ૑଄

    Combination (࿈݁Խ) ଟ͘ͷ “஌ࣝ૑଄اۀ” Ͱ
 ݟΒΕΔ “SECIϞσϧ”
  88. “૊৫తͳ஌ࣝ૑଄͸ ݸਓϨϕϧ͔Β࢝·Γɺ ϝϯόʔؒͷ૬ޓ࡞༻͕ɺ՝ɺ෦ɺࣄۀ෦໳ɺ ͦͯ͠૊৫ͱ͍͏ڞಉମͷ࿮Λ௒͑ͯ ্ঢɺ֦େ͍ͯ͘͠εύΠϥϧɾϓϩηε ʔ “஌ࣝ૑଄اۀ”ɹ108ϖʔδ

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

  90. Ӧۀ͞Μ΋SQLΛॻ͘Α͏ʹͳͬͨࠒʹ

  91. None
  92. None
  93. None
  94. None
  95. SQLΫΤϦΛొ࿥͓͖ͯ͠

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

  97. ֤छ෼ੳΛExcelϕʔεͰߦ͑ΔΑ͏ʹ

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

  99. ·ͱΊ

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

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

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

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

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

  105. None
  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. ղ͍ͨࣄྫ͸ڞ༗ͯ͠΋Β·͠ΐ͏ʂ