BigQueryでキーワード分析などをする場合、文字列をまず正規化したい。
String.prototype.normalizeが使えるので使う。
半角全角はchromeのように考慮してないので自前でマッピングを作る。

CREATE TEMPORARY FUNCTION normalize(str STRING)
RETURNS STRING
LANGUAGE js AS """
if (str === null)
  return;

var kanaMap = {
  'ガ': 'ガ', 'ギ': 'ギ', 'グ': 'グ', 'ゲ': 'ゲ', 'ゴ': 'ゴ',
  'ザ': 'ザ', 'ジ': 'ジ', 'ズ': 'ズ', 'ゼ': 'ゼ', 'ゾ': 'ゾ',
  'ダ': 'ダ', 'ヂ': 'ヂ', 'ヅ': 'ヅ', 'デ': 'デ', 'ド': 'ド',
  'バ': 'バ', 'ビ': 'ビ', 'ブ': 'ブ', 'ベ': 'ベ', 'ボ': 'ボ',
  'パ': 'パ', 'ピ': 'ピ', 'プ': 'プ', 'ペ': 'ペ', 'ポ': 'ポ',
  'ヴ': 'ヴ', 'ヷ': 'ヷ', 'ヺ': 'ヺ',
  'ア': 'ア', 'イ': 'イ', 'ウ': 'ウ', 'エ': 'エ', 'オ': 'オ',
  'カ': 'カ', 'キ': 'キ', 'ク': 'ク', 'ケ': 'ケ', 'コ': 'コ',
  'サ': 'サ', 'シ': 'シ', 'ス': 'ス', 'セ': 'セ', 'ソ': 'ソ',
  'タ': 'タ', 'チ': 'チ', 'ツ': 'ツ', 'テ': 'テ', 'ト': 'ト',
  'ナ': 'ナ', 'ニ': 'ニ', 'ヌ': 'ヌ', 'ネ': 'ネ', 'ノ': 'ノ',
  'ハ': 'ハ', 'ヒ': 'ヒ', 'フ': 'フ', 'ヘ': 'ヘ', 'ホ': 'ホ',
  'マ': 'マ', 'ミ': 'ミ', 'ム': 'ム', 'メ': 'メ', 'モ': 'モ',
  'ヤ': 'ヤ', 'ユ': 'ユ', 'ヨ': 'ヨ',
  'ラ': 'ラ', 'リ': 'リ', 'ル': 'ル', 'レ': 'レ', 'ロ': 'ロ',
  'ワ': 'ワ', 'ヲ': 'ヲ', 'ン': 'ン',
  'ァ': 'ァ', 'ィ': 'ィ', 'ゥ': 'ゥ', 'ェ': 'ェ', 'ォ': 'ォ',
  'ッ': 'ッ', 'ャ': 'ャ', 'ュ': 'ュ', 'ョ': 'ョ',
  '。': '。', '、': '、', 'ー': 'ー', '「': '「', '」': '」', '・': '・'
};

var reg = new RegExp('[' + Object.keys(kanaMap).join('') + ']', 'g');

return str.normalize('NFKC').toLowerCase()
  .replace(/[a-z0-9]/g, function(s) {
    return String.fromCharCode(s.charCodeAt(0) - 0xFEE0);
  })
  .replace(reg, function (s) {
    return kanaMap[s];
  })
  .replace(/[\\s\\u3000]+/g, ' ')
  .trim();
"""
;


SELECT normalize('  A   IU EO12345アイ') -- "a iu eo12345アイ"
;

この関数を使って、

normalize -> split -> unnest -> aggregate

で単語ごとの集計をする。

SELECT
  term
  , SUM(impressions)
FROM
  `hogehoge.keyword_log`
  JOIN UNNEST(SPLIT(normalize(`keyword`), ' ')) as term -- 配列に分割してexplodeする
GROUP BY term
;
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account log in.