Instantly share code, notes, and snippets.

Embed
What would you like to do?
BigQueryで2015年4期(秋)のアニメ作品のつぶやきを協調フィルタリング用に整形する
SELECT
name,
title,
IF(sum(score) > 10, 10, sum(score)) as score
FROM (
SELECT
name,
title,
1 AS score,
FROM (
SELECT
name,
CASE
WHEN tweet_text CONTAINS 'DIABOLIK LOVERS MORE,BLOOD' OR tweet_text CONTAINS 'DIABOLIK LOVERS' OR tweet_text CONTAINS 'DIABOLIK LOVERS MORE,BLOOD' OR tweet_text CONTAINS 'DIABOLIK LOVERS MORE,BLOOD' OR tweet_text CONTAINS 'アニラヴァ' THEN 238
WHEN tweet_text CONTAINS 'ヤング ブラック・ジャック' OR tweet_text CONTAINS 'ブラック・ジャック' OR tweet_text CONTAINS 'ブラックジャック' OR tweet_text CONTAINS 'ヤング ブラック・ジャック' OR tweet_text CONTAINS 'anime_ybj' THEN 239
WHEN tweet_text CONTAINS 'ルパン三世' OR tweet_text CONTAINS 'ルパン三世' OR tweet_text CONTAINS 'ルパン三世' OR tweet_text CONTAINS 'ルパン三世' OR tweet_text CONTAINS 'lupinIII_4th' THEN 240
WHEN tweet_text CONTAINS 'ランス・アンド・マスクス' OR tweet_text CONTAINS 'ランスアンドマスクス' OR tweet_text CONTAINS 'ランス・アンド・マスクス' OR tweet_text CONTAINS 'ランス・アンド・マスクス' OR tweet_text CONTAINS 'lanmas ' THEN 241
WHEN tweet_text CONTAINS 'ハッカドール THE あにめ~しょん' OR tweet_text CONTAINS 'ハッカドール' OR tweet_text CONTAINS 'ハッカドール THE あにめ~しょん' OR tweet_text CONTAINS 'ハッカドール THE あにめ~しょん' OR tweet_text CONTAINS 'ハッカアニメ' THEN 242
WHEN tweet_text CONTAINS '影鰐 KAGEWANI' OR tweet_text CONTAINS '影鰐' OR tweet_text CONTAINS 'KAGEWANI' OR tweet_text CONTAINS '影鰐 KAGEWANI' OR tweet_text CONTAINS 'KAGEWANI' THEN 243
WHEN tweet_text CONTAINS 'ヘヴィーオブジェクト' OR tweet_text CONTAINS 'ヘヴィーオブジェクト' OR tweet_text CONTAINS 'ヘヴィーオブジェクト' OR tweet_text CONTAINS 'ヘヴィーオブジェクト' OR tweet_text CONTAINS 'heavyobject' THEN 244
WHEN tweet_text CONTAINS 'ノラガミ ARAGOTO' OR tweet_text CONTAINS 'ノラガミ' OR tweet_text CONTAINS 'ノラガミ ARAGOTO' OR tweet_text CONTAINS 'ノラガミ ARAGOTO' OR tweet_text CONTAINS 'ノラガミ' THEN 245
WHEN tweet_text CONTAINS 'K RETURN OF KINGS' OR tweet_text CONTAINS 'K RETURN OF KINGS' OR tweet_text CONTAINS 'K RETURN OF KINGS' OR tweet_text CONTAINS 'K RETURN OF KINGS' OR tweet_text CONTAINS 'anime_k' THEN 246
WHEN tweet_text CONTAINS '蒼穹のファフナー EXODUS(2期)' OR tweet_text CONTAINS '蒼穹のファフナー EXODUS' OR tweet_text CONTAINS '蒼穹のファフナー' OR tweet_text CONTAINS '蒼穹のファフナー EXODUS(2期)' OR tweet_text CONTAINS 'fafner' THEN 247
WHEN tweet_text CONTAINS '金田一少年の事件簿R(2期)' OR tweet_text CONTAINS '金田一少年の事件簿R' OR tweet_text CONTAINS '金田一少年の事件簿' OR tweet_text CONTAINS '金田一' OR tweet_text CONTAINS 'kindaichi' THEN 248
WHEN tweet_text CONTAINS '学戦都市アスタリスク' OR tweet_text CONTAINS 'アスタリスク' OR tweet_text CONTAINS '学戦都市アスタリスク' OR tweet_text CONTAINS '学戦都市アスタリスク' OR tweet_text CONTAINS 'asteriskwar' THEN 249
WHEN tweet_text CONTAINS '落第騎士の英雄譚' OR tweet_text CONTAINS '落第騎士' OR tweet_text CONTAINS '落第騎士の英雄譚' OR tweet_text CONTAINS '落第騎士の英雄譚' OR tweet_text CONTAINS 'cav_anime' THEN 250
WHEN tweet_text CONTAINS '終物語' OR tweet_text CONTAINS 'madogatari' OR tweet_text CONTAINS '終物語' OR tweet_text CONTAINS '終物語' OR tweet_text CONTAINS 'madogatari' THEN 251
WHEN tweet_text CONTAINS 'うたわれるもの 偽りの仮面' OR tweet_text CONTAINS 'うたわれるもの' OR tweet_text CONTAINS 'うたわれるもの 偽りの仮面' OR tweet_text CONTAINS 'うたわれるもの 偽りの仮面' OR tweet_text CONTAINS 'うたわれ' THEN 252
WHEN tweet_text CONTAINS '進撃!巨人中学校' OR tweet_text CONTAINS '巨人中学校' OR tweet_text CONTAINS '進撃!巨人中学校' OR tweet_text CONTAINS '進撃!巨人中学校' OR tweet_text CONTAINS 'kyojinchu' THEN 253
WHEN tweet_text CONTAINS 'ハイキュー!! セカンドシーズン' OR tweet_text CONTAINS 'ハイキュー!!' OR tweet_text CONTAINS 'ハイキュー' OR tweet_text CONTAINS 'ハイキュー!! セカンドシーズン' OR tweet_text CONTAINS 'hq_anime' THEN 254
WHEN tweet_text CONTAINS '機動戦士ガンダム 鉄血のオルフェンズ' OR tweet_text CONTAINS '鉄血のオルフェンズ' OR tweet_text CONTAINS 'オルフェンズ' OR tweet_text CONTAINS '機動戦士ガンダム 鉄血のオルフェンズ' OR tweet_text CONTAINS 'g_tekketsu' THEN 255
WHEN tweet_text CONTAINS '温泉幼精ハコネちゃん' OR tweet_text CONTAINS 'ハコネちゃん' OR tweet_text CONTAINS '温泉幼精ハコネちゃん' OR tweet_text CONTAINS '温泉幼精ハコネちゃん' OR tweet_text CONTAINS 'ハコネちゃん' THEN 256
WHEN tweet_text CONTAINS '雨色ココア Rainy Colorへようこそ!(第2期)' OR tweet_text CONTAINS '雨色ココア' OR tweet_text CONTAINS '雨色ココア Rainy Colorへようこそ!(第2期)' OR tweet_text CONTAINS '雨色ココア Rainy Colorへようこそ!(第2期)' OR tweet_text CONTAINS '雨色ココア' THEN 257
WHEN tweet_text CONTAINS '小森さんは断れない!' OR tweet_text CONTAINS '小森さんは断れない' OR tweet_text CONTAINS '小森さんは断れない!' OR tweet_text CONTAINS '小森さんは断れない!' OR tweet_text CONTAINS 'komori_anime' THEN 258
WHEN tweet_text CONTAINS 'コメット・ルシファー' OR tweet_text CONTAINS 'コメットルシファー' OR tweet_text CONTAINS 'コメット・ルシファー' OR tweet_text CONTAINS 'コメット・ルシファー' OR tweet_text CONTAINS 'cometlucifer' THEN 259
WHEN tweet_text CONTAINS 'コンクリート・レボルティオ~超人幻想~' OR tweet_text CONTAINS 'コンクリート・レボルティオ' OR tweet_text CONTAINS 'レボルティオ' OR tweet_text CONTAINS 'コンクリート・レボルティオ~超人幻想~' OR tweet_text CONTAINS 'コンレボ' THEN 260
WHEN tweet_text CONTAINS 'ワンパンマン' OR tweet_text CONTAINS 'ワンパンマン' OR tweet_text CONTAINS 'ワンパンマン' OR tweet_text CONTAINS 'ワンパンマン' OR tweet_text CONTAINS 'onepunchman' THEN 261
WHEN tweet_text CONTAINS 'スタミュ' OR tweet_text CONTAINS 'スタミュ' OR tweet_text CONTAINS 'スタミュ' OR tweet_text CONTAINS 'スタミュ' OR tweet_text CONTAINS 'スタミュ' THEN 262
WHEN tweet_text CONTAINS 'てーきゅう 6期' OR tweet_text CONTAINS 'てーきゅう' OR tweet_text CONTAINS 'てーきゅう 6期' OR tweet_text CONTAINS 'てーきゅう 6期' OR tweet_text CONTAINS 'てーきゅう' THEN 263
WHEN tweet_text CONTAINS 'おそ松さん' OR tweet_text CONTAINS 'おそ松' OR tweet_text CONTAINS 'おそ松さん' OR tweet_text CONTAINS 'おそ松さん' OR tweet_text CONTAINS 'おそ松さん' THEN 264
WHEN tweet_text CONTAINS 'ゆるゆり さん☆ハイ!' OR tweet_text CONTAINS 'ゆるゆり' OR tweet_text CONTAINS 'ゆるゆり さん☆ハイ!' OR tweet_text CONTAINS 'ゆるゆり さん☆ハイ!' OR tweet_text CONTAINS 'yuruyuri' THEN 265
WHEN tweet_text CONTAINS '緋弾のアリアAA' OR tweet_text CONTAINS '緋弾のアリア' OR tweet_text CONTAINS '緋弾のアリアAA' OR tweet_text CONTAINS '緋弾のアリアAA' OR tweet_text CONTAINS 'ariaaa' THEN 266
WHEN tweet_text CONTAINS 'DD北斗の拳2' OR tweet_text CONTAINS 'DD北斗の拳' OR tweet_text CONTAINS 'DD北斗の拳2' OR tweet_text CONTAINS 'DD北斗の拳2' OR tweet_text CONTAINS 'DD北斗の拳' THEN 267
WHEN tweet_text CONTAINS '北斗の拳イチゴ味' OR tweet_text CONTAINS '北斗の拳イチゴ味' OR tweet_text CONTAINS '北斗の拳イチゴ味' OR tweet_text CONTAINS '北斗の拳イチゴ味' OR tweet_text CONTAINS '北斗の拳イチゴ味' THEN 268
WHEN tweet_text CONTAINS '俺がお嬢様学校に「庶民サンプル」としてゲッツ♥された件' OR tweet_text CONTAINS '庶民サンプル' OR tweet_text CONTAINS '俺がお嬢様学校に「庶民サンプル」としてゲッツ♥された件' OR tweet_text CONTAINS '俺がお嬢様学校に「庶民サンプル」としてゲッツ♥された件' OR tweet_text CONTAINS 'syomin_anime' THEN 269
WHEN tweet_text CONTAINS 'Dance with Devils' OR tweet_text CONTAINS 'ダンデビ' OR tweet_text CONTAINS 'Dance with Devils' OR tweet_text CONTAINS 'Dance with Devils' OR tweet_text CONTAINS 'ダンデビ' THEN 270
WHEN tweet_text CONTAINS '不思議なソメラちゃん' OR tweet_text CONTAINS 'ソメラちゃん' OR tweet_text CONTAINS '不思議なソメラちゃん' OR tweet_text CONTAINS '不思議なソメラちゃん' OR tweet_text CONTAINS 'ソメラ' THEN 271
WHEN tweet_text CONTAINS '櫻子さんの足下には死体が埋まっている' OR tweet_text CONTAINS '櫻子さん' OR tweet_text CONTAINS '櫻子さんの足下には死体が埋まっている' OR tweet_text CONTAINS '櫻子さんの足下には死体が埋まっている' OR tweet_text CONTAINS '櫻子さん' THEN 272
WHEN tweet_text CONTAINS '対魔導学園35試験小隊' OR tweet_text CONTAINS '35小隊' OR tweet_text CONTAINS '対魔導学園35試験小隊' OR tweet_text CONTAINS '対魔導学園35試験小隊' OR tweet_text CONTAINS '35小隊' THEN 273
WHEN tweet_text CONTAINS 'すべてがFになる THE PERFECT INSIDER' OR tweet_text CONTAINS 'すべてがFになる' OR tweet_text CONTAINS 'すべてがFになる THE PERFECT INSIDER' OR tweet_text CONTAINS 'すべてがFになる THE PERFECT INSIDER' OR tweet_text CONTAINS 'すべてがFになる' THEN 274
WHEN tweet_text CONTAINS '新妹魔王の契約者 BURST' OR tweet_text CONTAINS '新妹魔王の契約者' OR tweet_text CONTAINS '新妹魔王' OR tweet_text CONTAINS '新妹魔王の契約者 BURST' OR tweet_text CONTAINS 'shinmaimaou' THEN 275
WHEN tweet_text CONTAINS 'ご注文はうさぎですか??' OR tweet_text CONTAINS 'ご注文はうさぎですか' OR tweet_text CONTAINS 'ごちうさ' OR tweet_text CONTAINS 'ご注文はうさぎですか??' OR tweet_text CONTAINS 'gochiusa' THEN 276
WHEN tweet_text CONTAINS '終わりのセラフ 名古屋決戦編' OR tweet_text CONTAINS '終わりのセラフ' OR tweet_text CONTAINS '終わりのセラフ 名古屋決戦編' OR tweet_text CONTAINS '終わりのセラフ 名古屋決戦編' OR tweet_text CONTAINS '終わりのセラフ' THEN 277
WHEN tweet_text CONTAINS 'ヴァルキリードライヴ マーメイド' OR tweet_text CONTAINS 'ヴァルキリードライヴ' OR tweet_text CONTAINS 'ヴァルキリードライヴ マーメイド' OR tweet_text CONTAINS 'ヴァルキリードライヴ マーメイド' OR tweet_text CONTAINS 'vdproject' THEN 278
WHEN tweet_text CONTAINS 'ワールドトリガー 逃亡者編' OR tweet_text CONTAINS 'ワールドトリガー' OR tweet_text CONTAINS 'ワールドトリガー 逃亡者編' OR tweet_text CONTAINS 'ワールドトリガー 逃亡者編' OR tweet_text CONTAINS 'ワールドトリガー' THEN 279
WHEN tweet_text CONTAINS '血液型くん!3' OR tweet_text CONTAINS '血液型くん!' OR tweet_text CONTAINS '血液型くん!3' OR tweet_text CONTAINS '血液型くん!3' OR tweet_text CONTAINS '血液型くん' THEN 280
ELSE NULL END AS title
FROM [2015_AnimeQ4Set1.anime2015_q4a] ) )
WHERE
title IS NOT NULL group by name, title order by name
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment