検索エンジンのランキングアルゴリズムをMySQLで真似てみる

今回はMySQLを使って、GoogleやYahooなどがやっている「ランキング」を出す方法を書いてみたいと思います。

別に検索エンジンを作ろうなどと大それたページではありません。笑

単に「あっちこっちにあるデータを指標として得点化し、合計値でランキングを出すにはどうしたら良いか」を簡単に調べるための方法について書いているものです。　誤解しないでね！

今回のテーマ：関連キーワードと被リンクからランキング

今回は、検索エンジンのSEOにおける評価の王道である「キーワード数」と「被リンク数」にスポットを当て、ページ各々が持っている数からランキングを出してみようと思います。

テーブル情報

テーブル名：pages

pid	title	url
1	Macバンザイ！	http://apple.example.com
2	りんごを使ったレシピ集	http://ringo.example.com

テーブル名：keywords

kid	pid	num	word
1	1	4	apple
2	2	2	apple

テーブル名：links

lid	pid	num
1	1	2
2	2	3

実際のページでキーワード抽出や被リンク数を調べるのは面倒なので、ここでは上記のように最初から格納されているものとします。笑　ページ情報についてはもちろん架空の情報ですよ。

で、こんな感じで関連性がもたせてあります。

「apple」で検索したときに出る順番を考えてみよう！

キーワードの多さで見ると「Macバンザイ！」

keywordsテーブルを見ると分かるように、「apple」が4つ入っているので、pid=1の「Macバンザイ！」が明らかに有利です。

kid	pid	num	word
1	1	4	apple
2	2	2	apple

被リンクの多さで見ると「りんごを使ったレシピ集」

linksテーブルを見ると分かるように、pid=2の「りんごを使ったレシピ集」が3つの被リンクを獲得しているので明らかに有利です。

lid	pid	num
1	1	2
2	2	3

「じゃー、どっちなのよ？」ということになるんですが、「キーワードの多さ」と「被リンクの多さ」の両方を統一の「ポイント」として合計していくと、総合得点が出せますので、順位が出せることになります。

さっそくやってみましょう。

MySQLで異なる列の値を合計して比較するには？

異なるテーブルで、しかも異なる列の値を加算して統一した指標にするには、「SUM」「AS」キーワードを使うと便利です。

SELECT SUM(keywords.num) ... AS points

次にリンクポイントの計算ですが、今回は「LEFT JOIN」を使うので、最初の「keywords」テーブルに複数行の一致があった場合、「links」テーブルの一致する行が重複して加算されてしまいます。

これを防ぐには、links.numをSUMで合計を出した後に、keywordsの一致件数で割ることで正確な合計値を出すことができます。　他にもキーがあった場合はそれらの件数も併せて割れば良いですね。

SELECT SUM(links.num) / COUNT(DISTINCT keywords.kid) AS points ...

というわけで、この2つを組み合わせると以下のようなSQL文になります。

SELECT SUM(keywords.num) + SUM(links.num) / COUNT(DISTINCT keywords.kid) AS points...

これで「points」というカラムに双方の合計値が入るようになるので、ORDER BY points DESC を使って多い順に並べることができるんですね。

さらにもうちょっとリアルにするために、「被リンクの1ポイントは、キーワードのポイントの3倍の効果がある」というふうに変更してみましょう。

SELECT SUM(keywords.num) + SUM(links.num) / COUNT(DISTINCT keywords.kid) * 3 AS points ...

ちょっとリアルになりましたかね？（笑）

というわけで、これらをまとめてSQL文にすると、以下のようになります。

SELECT 
pages.*,
SUM(keywords.num) + SUM(links.num) / COUNT(DISTINCT keywords.kid) * 3 AS points 
FROM pages 
LEFT JOIN keywords ON (pages.pid=keywords.pid) 
LEFT JOIN links ON (pages.pid=links.pid) 
WHERE 
keywords.word = 'apple' 
GROUP BY pages.pid 
ORDER BY points DESC

実行結果はこれ。

pid	title	url	points
2	りんごを使ったレシピ集	http://ringo.example.com	11
1	Macバンザイ！	http://apple.example.com	10

というわけで、「りんごを使ったレシピ集」が上位表示されることになりました。

まとめ

いやー、意外とやってみるといけるもんですね。　Googleほど複雑にはできませんが、異なるデータを統一の指標として合算するというやり方は結構ニーズがあるのではないかと思って書いてみました。

お役に立てば幸いでございます。　それではまた！