mandala-scripts/bigQuerySQL_for_CF.rb at master · manga-data-library/mandala-scripts · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
require 'shangrila'

#bundle exe ruby bigQuerySQL_for_CF.rb 2015 3 2015_C3a.2015_C7a

year = ARGV[0]
cours = ARGV[1]

#2015_C3a.2015_C7a
table_name = ARGV[2]

masters = Shangrila::Sora.new().get_master_data(year, cours)

count_limit = 10

val_title = ''

masters.each do |master|
 val_title += sprintf('        WHEN tweet_text CONTAINS \'%s\' OR tweet_text CONTAINS \'%s\'  OR tweet_text CONTAINS \'%s\' OR tweet_text CONTAINS \'%s\' OR tweet_text CONTAINS \'%s\' THEN \'%s\'' + "\n",
  master['title'],
  master['title_short1'] != '' ? master['title_short1'] : master['title'],
  master['title_short2'] != '' ? master['title_short2'] : master['title'],
  master['title_short3'] != '' ? master['title_short3'] : master['title'],
  master['twitter_hash_tag'],
  master['title'])
end

sql = <<"EOS"
SELECT
  name,
  title,
  IF(sum(score) > #{count_limit}, #{count_limit},  sum(score)) as score
FROM (
  SELECT
    name,
    title,
    1 AS score,
  FROM (
    SELECT
      name,
      CASE
#{val_title}
      ELSE NULL END AS title
    FROM [#{table_name}] ) )
WHERE
  title IS NOT NULL group by name, title order by name
EOS


puts sql