Posted at

BigQueryで string.normalize

More than 1 year has passed since last update.

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
;