ランキング作成時に使ったSQL手法 - EatSmartシステム部ブログ

先日、弊社サービスのもぐナビで、食品ランキングのリニューアルを行いました。

今回は、ランキング構築等で使用したいくつかのSQL手法を共有したいと思います。ちなみに、弊社で使用しているRDBMSはPostgreSQLです。

カテゴリ毎のランクの振り方

これはなんてことのない内容なのですが、今回はカテゴリごとにランキングを作っているので、ランクもカテゴリごとに振っています。一括でまとめてランクを出すために、ランク関数(RANK)と集約関数(OVER)を使って出しました。

例えば、カテゴリ(大カテゴリ:LARGE_CATEGORY、中カテゴリ:MIDDLE_CATEGORY)ごとに、スコア(SCORE)の高い順に並べてランキングを作る場合は、

SELECT
    LARGE_CATEGORY,
    MIDDLE_CATEGORY,
    NAME,
    RANK() OVER (
        PARTITION BY LARGE_CATEGORY,MIDDLE_CATEGORY
        ORDER BY SCORE DESC
    ) AS RANKING
FROM SCORE_TABLE

というSQLを組んで、

INSERT INTO RANKING_TABLE
SELECT ・・・ FROM SCORE_TABLE

みたいな感じで、順位を振って一気にランキングテーブルにデータを作成しています。

上位3位までの対照表を作る

もぐナビのランキングでは、「食べたいランキング」と「オススメランキング」の2種類があり、それぞれ食べたい気持ちの期待度と食べた感想の評価をランキングしています。

今回は、カテゴリ毎に上位3位まで抽出し、左に食べたいランキング、右にオススメランキングを並べて対比してみようという事になりました。

大カテ	中カテ	順位	食べたい	おすすめ
お菓子	チョコ	1位	ダース	ポッキー
お菓子	チョコ	2位	パイの実	レーズンクランチ
お菓子	チョコ	3位	チェリーブランデー	ガーナ
お菓子	ガム	1位	Fit's デカビタC	キシリトール
お菓子	ガム	2位	Fit's Crispop	キシリッシュハイパークール
お菓子	ガム	3位	マイニチケア	キシリッシュライムクール

普通に考えると、カテゴリのマスタ表を主表にして、食べたいランキング表とオススメランキング表をJOINすれば良いという事になります。

ただし、食べたいランキングもおすすめランキングも、カテゴリが全て揃っているとは限らないので、LEFT OUTER JOINにする必要があります。また、順位(1位〜3位)も全て揃っているとは限らないので、順位の軸となる表も必要となります。

今回は、順位の軸となる表をGENERATE_SERIESを使用して

SELECT
    CAT.LARGE_CATEGORY_NAME,
    CAT.MIDDLE_CATEGORY_NAME,
    RANKING.NO,
    TABETAI.NAME,
    OSUSUME.NAME
FROM CATEGORY_TABLE CAT
INNER JOIN GENERATE_SERIES(1,3) AS RANKING(NO)
    -- 1から3までの順位をRANKING表のNO列として作る
ON 1=1    -- 結合しないのでダミー条件を入れる
LEFT OUTER JOIN (
    SELECT * 
    FROM TABETAI_RANKING_TABLE
    WHERE RANKING <= 3
) TABETAI
ON CAT.LARGE_CATEGORY = TABETAI.LARGE_CATEGORY
AND CAT.MIDDLE_CATEGORY = TABETAI.MIDDLE_CATEGORY
LEFT OUTER JOIN (
    SELECT * 
    FROM OSUSUME_RANKING_TABLE
    WHERE RANKING <= 3
) OSUSUME
ON CAT.LARGE_CATEGORY = OSUSUME.LARGE_CATEGORY
AND CAT.MIDDLE_CATEGORY = OSUSUME.MIDDLE_CATEGORY
;

という感じで実現しました。

その他、小ネタ

ランキングのスコア算出時などに、偏差値やパーセンタイル値を使用しています。このあたりは、最新のPostgreSQLであれば標準の関数で算出できるのですが、弊社で使用しているバージョンでは関数が使えなかったので、SQLを使って算出しました。

偏差値の算出

偏差値は平均値と標準偏差を使用して算出します。具体的には、得点(POINT)と平均値の差を標準偏差で割ったものを10倍し、中心を50にするために50を足すことで算出できます。

先ほどのテーブルでSQL実装をすると

SELECT
    LARGE_CATEGORY,
    MIDDLE_CATEGORY,
    NAME,
    (((POINT-POINT_AVG)/POINT_STDDEV * 10) + 50) AS DEV_VALUE
FROM (
    SELECT
        LARGE_CATEGORY,
        MIDDLE_CATEGORY,
        NAME,
        POINT,
        STDDEV(POINT) OVER (
            PARTITION BY LARGE_CATEGORY,MIDDLE_CATEGORY
        ) AS POINT_STDDEV,
        AVG(POINT) OVER (
            PARTITION BY LARGE_CATEGORY,MIDDLE_CATEGORY
        ) AS POINT_AVG
    FROM SCORE_TABLE
) S

という感じです。

パーセンタイル値の算出

パーセンタイル値とは、データを小さい順に並べた時に、全体のN%目に該当する値のことです。全体の90%が入る境目の値を90パーセンタイル値と呼びます。

ランキング算出時の特異値などを平準化するための足切りなどに使います。

算出するためには、値の小さい順に順位をつけて全体の件数で%を算出すれば良いので、

SELECT
    LARGE_CATEGORY,
    MIDDLE_CATEGORY,
    POINT,
    PERCENTILE
FROM (
    SELECT
        LARGE_CATEGORY,
        MIDDLE_CATEGORY,
        POINT,
        RANK() OVER (
            PARTITION BY LARGE_CATEGORY,MIDDLE_CATEGORY
            ORDER BY POINT
        )*100 / COUNT(*) OVER (
            PARTITION BY LARGE_CATEGORY,MIDDLE_CATEGORY
        ) AS PERCENTILE
    FROM SCORE_TABLE
) S

のようになります。

SQLって工夫すると色々できるので、本当に楽しいですね！

今週は以上です。