協調フィルタリングデータ生成

cf.sql

SELECT
  name,
  title,
  sum(score) as score
FROM (
  SELECT
    name,
    title,
    1 AS score,
  FROM (
    SELECT
      name,
      CASE WHEN tweet_text CONTAINS 'Charlotte_AB_'
      OR tweet_text CONTAINS 'シャーロット'
      OR tweet_text CONTAINS 'Charlotte(シャーロット)' THEN 'シャーロット' WHEN tweet_text CONTAINS 'gakkou_gurashi'
      OR tweet_text CONTAINS 'がっこうぐらし'
      OR tweet_text CONTAINS 'がっこうぐらし' THEN 'がっこうぐらし' ELSE NULL END AS title
    FROM
      [2015_C3a.2015_C7a] ) )
WHERE
  title IS NOT NULL group by name, title order by name