Upgrade to Pro — share decks privately, control downloads, hide ads and more …

150225_sql_for_everyone.pdf

Livesense Inc.
March 11, 2015
69

 150225_sql_for_everyone.pdf

Livesense Inc.

March 11, 2015
Tweet

More Decks by Livesense Inc.

Transcript

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

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

    Combination (࿈݁Խ) ଟ͘ͷ “஌ࣝ૑଄اۀ” Ͱ
 ݟΒΕΔ “SECIϞσϧ”
  3. ςʔϒϧઆ໌::entries(Ԡื) ΧϥϜ໊ આ໌ ྫ JE *%  OBNF Ԡืऀࢯ໊ ໦ଜଠ࿠

    CJSUIEBZ ੜ೥݄೔  NBJM ϝʔϧΞυϨε GPP!CBSOFU EBUF Ԡื೔  BEPQU@TUBUVT ࠾༻֬ఆ࣌ʹɺ ෆ࠾༻֬ఆ࣌ʹɺ ͦ͏Ͱͳ͚Ε͹  BEPQU@EBUF ࠾༻ɾෆ࠾༻֬ఆ೔ ະ֬ఆͷͱ͖͸  DMJFOU@JE ళฮ*%  EFWJDF@UZQF Ԡื࣌ʹར༻ͨ͠
 σόΠε 4."351)0/&1$
  4. 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' ;
  5. 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' ;
  6. 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 ;
  7. 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 ;
  8. 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 ;