はじめに（導入）
こんな課題、ありませんか？
- BigQueryで全アイテムの類似度を出そうとCROSS JOINを試したら、クエリが何時間も終わらず、料金が怖くなって慌ててキャンセルした…
- 毎晩の関連アイテム更新バッチ、データ量の増加とともに実行時間が延び続け、朝までに終わるかヒヤヒヤしている…
- まずは関連アイテム候補を手早く出したいが、実装のハードルが高い
この記事では、そんな悩みを解決する方法をご紹介します。
アイテム同士の「似ている度合い」を計算するのに、全部の組み合わせを計算する必要はありません。共通のキーワードを持つアイテム同士だけに絞って計算することで、BigQueryだけで効率よく類似度を求められます。
どのくらい改善するか： 従来の全組み合わせ計算と比べて、計算量を10分の1〜100分の1に削減。クエリ実行時間も数時間から数十分に短縮できます。
自己紹介
株式会社ケンサク代表の堀田と申します。「良いものを、見つけやすく」をミッションに、機械学習を活用した検索・レコメンデーション改善の技術開発に取り組んでいます。特にECサイトやメディアサイトでの商品・コンテンツ発見体験の向上を専門としており、SQLベースの軽量な手法から最新のEmbedding技術まで幅広くカバーしています。
「似ている」はビジネスの武器になる：具体的な活用シーン4選
- 商品詳細ページの「この商品に似た商品」: 「iPhone 15 ケース 透明」を見ている人に「iPhone 15 ケース シリコン」や「iPhone 15 ケース 手帳型」を提案
- 在庫切れ時の代替商品提案: 人気の「ワイヤレスイヤホン A社製」が売り切れた時、説明文が似ている「ワイヤレスイヤホン B社製」を自動で代替候補として表示
- 新商品のレコメンド: 発売したばかりの商品は購買履歴がないため、商品説明から既存商品との類似度を計算して関連商品として表示
- 検索結果の補強: 「防水スピーカー」で検索した時、検索結果が少なければ類似する「Bluetoothスピーカー 防水」なども一緒に表示
アイテム×アイテムの類似度は、ユーザー特徴が薄い場面でも効く「候補生成の土台」になり、パーソナライズの前段やフォールバックとしても有用です。
特に、レコメンドのコールドスタート問題に対する定番の解決策として広く用いられます。
TF-IDFって何？（なぜその語が重要なのか）
- TF（Term Frequency）：語 t が文書 d に何回出たか（同じ文書内の重要度）
- DF（Document Frequency）：語 t を含む文書の数（一般的すぎる語は価値が下がる）
- IDF：$\mathrm{idf}(t) = \log \frac{N}{df(t)}$（N は文書総数）
- TF-IDF：$\mathrm{tfidf}(t,d) = \mathrm{tf}(t,d) \times \mathrm{idf}(t)$
- 正規化：ベクトル $v_d$ を $|v_d|_2$ で割って単位長にすることで、後段のコサイン計算が内積に一致します。
直感的には、「その文書にとって相対的に重要」かつ「コーパス全体では希少」な語ほど重みが高くなります。
ざっくりイメージ
- 文書＝商品ページ、語＝その商品の特徴やキーワード。
- よく出る一般的な語（「人気」「新作」など）は点数が下がり、固有の語（「防水」「シリコン」「Lightning対応」など）は点数が上がる。
- TF は「その商品の中で何回出たか」、DF は「全体でどれだけ珍しいか」。TF-IDF はその掛け合わせの重要度。
例:
- A商品の説明: 「防水 シリコン ケース」
- B商品の説明: 「防水 レザー ケース」
- 「防水」「ケース」はどの商品にもありがち→重みは低め。「シリコン」「レザー」は差がつく特徴→重みが高い。
コサイン類似度って何？（似ているってどういうこと？）
- 定義：ベクトル間のなす角の余弦。単位長に正規化済みなら単なる内積。
- 範囲：0〜1（負値は出にくい設計）。共通語の重み合計として解釈できます。
- 疎ベクトルの利点：共有している語だけ見ればよい→語で結合しスコアを足し上げる実装が可能。
このため、本記事のSQLでは「正規化TF-IDF」を共有語で自己結合し、重みの積を合計することでコサイン類似度を得ます。
ざっくりイメージ
- 2つの商品を「特徴ベクトル」という矢印で表す。矢印の向きが近いほど似ている。
- 正規化済みなら、共通する特徴の重みの合計がスコア。1に近いほどとても似ている、0に近いとほぼ関係ない。
- 例: AとBが「防水」を共有し、かつ「シリコン/レザー」で違う → 一部は似ている中くらいのスコア。共有語がない場合は0に近づく。
どうやって計算するの？（手法の全体像）
- TF-IDFで各アイテム（文書）を疎ベクトル化し、正規化後に共有語彙で内積を取る＝コサイン類似度
- BigQueryのCTEで前処理から集計までを完結
- 計算ステップの俯瞰（0〜10のCTE）に沿って、一貫したSQLで実装
図解（ざっくり）
必要なデータは何？（データの準備）
- アイテムを識別するID: 商品ID、記事ID、ユーザーIDなど、各アイテムを一意に識別できるもの
- アイテムの説明テキスト: 商品説明文、記事本文、プロフィール文など、類似度計算に使いたいテキストデータ
つまり、どんなテーブル名・カラム名でも、「アイテムID」と「そのアイテムを表すテキスト」の2つが取得できれば、このSQLで類似度を計算できます。
トークナイズ（テキストを単語などの単位に分割すること）について: テキストを単語や文字に分割する方法は、文字2-gram（日本語向け）またはスペース区切り（英語向け）から選択します。SQLの中で設定できます。
実際のSQL（詳しい実装方法）
考え方だけ知りたい方は、このSQLセクションは読み飛ばしてOKです。
- コンテンツ抽出
- トークン化（2-gramまたは単語分割）
- TF（Term Frequency）
- DF（Document Frequency）
- 文書総数の取得
- TF-IDFの算出
- ノルム計算
- 正規化TF-IDF
- 高DFフィルタリング（計算量抑制の要）
- 自己結合でペア生成とスコア合算（a.id < b.id で重複回避）
WITH
-- 0. コンテンツデータの抜き出し
contents_table AS (
SELECT id, contents FROM `YOUR_PROJECT_ID`.`YOUR_DATASET_ID`.`YOUR_TABLE_ID`
),
-- 1. テキストデータの分解
terms AS (
SELECT
id,
term
FROM
contents_table,
UNNEST(ML.NGRAMS(SPLIT(contents, ''), [2], '')) AS term -- 文字2gramの場合
-- UNNEST(SPLIT(contents, ' ')) AS term -- スペース区切りで分割
),
-- 2. TF（Term Frequency）の計算
tf AS (
SELECT
id,
term,
COUNT(*) AS tf
FROM
terms
GROUP BY
id, term
),
-- 3. DF（Document Frequency）の計算
df AS (
SELECT
term,
COUNT(DISTINCT id) AS df
FROM
terms
GROUP BY
term
),
-- 4. 全体の文書数を取得
total_docs AS (
SELECT
COUNT(DISTINCT id) AS total_docs
FROM
contents_table
),
-- 5. TF-IDFの計算
tfidf AS (
SELECT
tf.id,
tf.term,
tf.tf * LOG(td.total_docs / df.df) AS tfidf
FROM
tf
JOIN
df ON tf.term = df.term,
total_docs td
),
-- 6. ノルムの計算
norm AS (
SELECT
id,
SQRT(SUM(tfidf.tfidf * tfidf.tfidf)) AS norm
FROM
tfidf
GROUP BY
id
),
-- 7. 正規化されたTF-IDFの計算
normalized_tfidf AS (
SELECT
tfidf.id,
tfidf.term,
tfidf.tfidf / norm.norm AS n_tfidf
FROM
tfidf
JOIN
norm ON tfidf.id = norm.id
),
-- 8. 高DFのフィルタリング
filtered_tfidf AS (
SELECT
normalized_tfidf.id,
normalized_tfidf.term,
normalized_tfidf.n_tfidf
FROM
normalized_tfidf
JOIN
df ON df.term = normalized_tfidf.term
WHERE
df.df <= 100 -- ハイパーパラメータ
),
-- 9. 自己結合によるスコア計算
term_similarity AS (
SELECT
a.term,
a.id AS item1,
b.id AS item2,
a.n_tfidf * b.n_tfidf AS score
FROM
filtered_tfidf a
JOIN
filtered_tfidf b
ON
a.term = b.term
WHERE
a.id < b.id -- 同じペアを2度計算しない
)
-- 10. アイテムペアでスコアを集計し、コサイン類似度を算出
SELECT
item1,
item2,
SUM(score) AS cosine
FROM
term_similarity
GROUP BY
item1, item2;
実践で差がつく！コストと精度を両立するパラメータ調整のコツ
このSQLには調整可能なハイパーパラメータ（SQLの中で変更できる設定値のこと）がいくつかあります。
-
DFしきい値（
df.df <= 100）の決め方：- 全アイテム数の1%〜5%を初期値として試す
-
dfの多い順に上位語を確認し、汎用語（「商品」「新作」「送料無料」など）が消える境目を探す - Dry runで計算量とコストを見ながら調整
-
TF/TF-IDFの最小値設定：
-
min_tf（最低出現回数）やmin_tfidf（最低重要度）でノイズ語を除外 - 記号・数字のみの語を前処理で除外
-
-
アイテムあたりの語数制限：
- 各アイテムでTF-IDF上位N語に絞る（N=20, 50, 100で比較）
- 精度とコストのバランスを確認しながら決定
コラム：なぜEmbeddingではなくTF-IDFなのか？
最近はWord2VecなどのEmbeddingも一般的です。Embeddingは文脈を捉えやすい一方で、
- モデルの学習・推論環境や追加コストが要る、
- 全ペア類似を正面から解くと組み合わせ爆発に向き合う、
といった課題があります。
本稿の手法は、SQLだけで完結し、共通語を持つペアだけを対象に計算できるスケーラビリティが強みです。まずはこの方法で高速な候補生成の基盤を作り、必要に応じてEmbeddingで再ランクするのが、現実的で費用対効果の高い構成です。
改めて、この手法で解決できる課題をまとめると：
- 全組み合わせ計算による計算量爆発とコスト増大の回避
- SQLのみでの実装による導入ハードルの低さ
- レコメンドのコールドスタート問題への対応
- 既存のDWH運用への組み込みやすさ
プロはここも見る：精度と運用性を高める応用テクニック
基本のSQLを超えて、より高度な改善を目指す場合の方向性です。
-
テキスト前処理の高度化：
- 業界特有のストップワードリスト整備（アパレルなら「新作」「入荷」「送料無料」など、類似度に寄与しない汎用語を除外）
- 同義語・表記揺れの統一（「iPhoneケース」「アイフォン ケース」を事前辞書で正規化し、より正確な類似度を計算）
- 日本語なら2-gramから始めて、精度が必要な場合は形態素解析への移行を検討
-
ビジネスロジックとの組み合わせ：
- カテゴリ内類似のみ許可、価格帯や在庫状況でフィルタリング（実際の購買につながりやすい商品を優先表示）
- 表示面ごとに件数調整（商品詳細ページなら5件、検索補強なら20件など、ユーザー体験を考慮した設定）
-
運用・パフォーマンス最適化：
- 中間テーブル（
filtered_tfidfなど）の永続化とCLUSTER BY termでIO削減 - 差分更新の仕組み構築（新規・更新アイテムのみ再計算し、日次バッチの実行時間を短縮）
- データ更新頻度とビジネス要件に応じたスケジュール実行の最適化
- 中間テーブル（
-
ハイブリッド構成への発展：
- TF-IDFで候補生成→ユーザー行動ログ（協調フィルタ）で再ランキングの2段階構成
- 候補生成は安価・高速に、最終順位付けは精度重視で行う効率的なアプローチ
まとめ
本記事では、BigQueryのSQLだけでアイテム間のコサイン類似度を計算する実装を提示しました。TF-IDFで疎ベクトル化し、L2正規化後に共有語をキーに自己結合して重み積を集計することで、密ベクトルの全組み合わせを回避しつつ、実用的な類似度を安価に得られます。高DF語のフィルタとa.id < b.idの制約が計算量抑制の肝です。
- 使いどころ：商品詳細/カート/在庫切れ代替/検索補助など、広範に活用可能。コールドスタート対策の定番。
- 導入のしやすさ：SQLのみで完結し、既存のDWH運用に乗せやすい。
- 伸びしろ：dfしきい値、上位N語、前処理辞書の整備、ビジネス制約の反映、必要に応じた再ランクで精度・コストの最適点を探れます。
さいごに：もっと本格的なレコメンド改善をお考えの方へ
改めまして、株式会社ケンサク代表の堀田です。我々は「良いものを、見つけやすく」をミッションに、機械学習を用いた検索・レコメンデーション改善の受託開発や技術コンサルティングを行っています。
本記事で紹介した手法は、SQLだけで実装できる非常に強力なベースラインとなります。しかし、実際にビジネスで大きな成果を出すためには、
- データ特性に合わせた最適な前処理（同義語対応、ストップワード選定など）
- ビジネスロジック（在庫、価格帯、カテゴリなど）の適切な組み込み
- ユーザーの行動ログと組み合わせた、より高度なパーソナライズ
など、多くの実践的なノウハウが求められます。
もし、皆様のビジネスにおいて、
「この記事の手法を自社データで試したいが、リソースがない」
「レコメンドの精度が頭打ちになっており、次の打ち手を探している」
「データは溜まっているが、何から手をつければ良いか分からない」
といったお悩みをお持ちでしたら、ぜひ一度お気軽にご相談ください。
貴社の課題やデータ状況をヒアリングさせていただき、最適な改善アプローチをご提案します。
PoCレベルで小さく試したい方、技術的な質問だけでも大歓迎です。