SQLのCOUNTカラムに順位をつけるには

Question

表題のとおり、投票結果を集計したものに順位を付けたいです。
どのようにすべきでしょうか。
調べてみると、遅い早いで複数のアプローチがあるようですが、おすすめの方法をご教示いただければ幸いです。

// vote table

id  good 
---------
1	4
2	4
3	7
4	1
5	6
6	1
7	4
8	8
9	8

下記のSQL実行

        return DB::table('vote')
            ->select('good', DB::raw('count(good) as COUNT'))
            ->groupBy('good')
            ->orderBy('COUNT', 'desc')
            ->get();

結果

good COUNT
4	3
1	2
8	2
7	1
6	1

COUNTの横にRANKカラムを追加して、かつ同順位を表示できればBestです。

よろしくお願いいたします。

Answer 1

MySQL 5.7までは分析関数が使えないので、この手の処理はかなり面倒です。

まずはこちらのユーザ変数を使った方法を参考にしてみました。

Dense Rank function In Mysql SQL Query (Create / Implement)

SET @priv = NULL ;
SET @nbr = 0 ;
SET @rank = 0 ;

SELECT
    good
    ,`COUNT`
    ,@nbr := @nbr + 1 AS nbr
    ,@rank := IF(`COUNT` = @priv, @rank, @nbr) AS rank
    ,@priv := `COUNT`
FROM (
    SELECT
        good
        ,COUNT(good) AS `COUNT`
    FROM
        vote
    GROUP BY
        good
    ORDER BY
        `COUNT` DESC
) AS tmp
;

これでとりあえず結果は得られていますが、どうやらMySQLではSELECT句にユーザ変数を指定した順序とそれらの評価順序が同じであることを保証しないとのことです。（日本語訳がおかしいので英語版から）

MySQL 5.7 9.4 User-Defined Variables

For other statements, such as SELECT, you might get the results you expect, but this is not guaranteed. In the following statement, you might think that MySQL will evaluate @a first and then do an assignment second:

SELECT @a, @a:=@a+1, ...;

However, the order of evaluation for expressions involving user variables is undefined.

つまり、以下の部分が意図した結果になるかどうか保証されないようです。

    ,@nbr := @nbr + 1 AS nbr
    ,@rank := IF(`COUNT` = @priv, @rank, @nbr) AS rank
    ,@priv := `COUNT`

ユーザ変数の評価順に依存しない書き方や、ユーザ変数を使わない書き方を考えてみましたが、いずれも複雑です。

-- 集計結果を自身と1行ずらして結合
-- COUNTに変化がない場合ひとつ前のRANKを用いる

SET @nbr1 = 0 ;
SET @nbr2 = 0 ;
SET @rnk = 1 ;

SELECT
    tmp1.good
    ,tmp1.`COUNT`
--    ,tmp2.`COUNT`
    ,CASE WHEN tmp1.`COUNT` <> tmp2.`COUNT` 
        THEN @rnk := tmp1.nbr
        ELSE @rnk
        END AS `RANK`
FROM (
    SELECT
        good ,`COUNT`
        ,@nbr1 := @nbr1 + 1 AS nbr
    FROM (
        SELECT good ,COUNT(good) AS `COUNT`
        FROM vote
        GROUP BY good
        ORDER BY `COUNT` DESC
    ) AS tmpA
) AS tmp1
LEFT OUTER JOIN (
    SELECT
        good ,`COUNT`
        ,@nbr2 := @nbr2 + 1 AS nbr
    FROM (
        SELECT good ,COUNT(good) AS `COUNT`
        FROM vote
        GROUP BY good
        ORDER BY `COUNT` DESC
        ) AS tmpB
) AS tmp2
        ON tmp1.nbr - 1 = tmp2.nbr
ORDER BY
    tmp1.nbr
;

-- COUNTの値を集計結果内の他のレコードと比較して
-- RANKを求める

SELECT
    good
    ,`COUNT`
    ,(
        SELECT COUNT(*) + 1
        FROM (
            SELECT good ,COUNT(good) AS `COUNT`
            FROM vote
            GROUP BY good
        ) AS tmp2
        WHERE tmp1.`COUNT` < tmp2.`COUNT`
    ) AS `RANK`
FROM (
    SELECT good ,COUNT(good) AS `COUNT`
    FROM vote
    GROUP BY good
) AS tmp1
ORDER BY
    `COUNT` DESC
;

PHP側でランクを振った方がシンプルでしょうし、PHPでやった方がサブクエリが必要ない分速度についても優位かと思います。

Comments

[supermaruetsu]

PHPのフレームワーク内でのユーザ変数の使い方が分からず、またご指摘いただいた速度面でのデメリットもあった為PHP側でランクを振る手法にしました。すごく丁寧にアドバイス頂き大大大感謝です。ありがとうございました。

Answer 2

適切なインデックスが貼ってあるならご提示のgroup byの集計がベストです。
ランクは集計後のCOUNTで行うのでしょうか？
同じCOUNT数の場合同順とし、その後のデータが後ろたおしすればいいのでしょうか？
例示のものでいうとcount=3が1位、count=2が2位、count=1が4位？

追記

集計後のデータを最終形すると処理自体は冗長になります
とりあえずこうしてみてください

select good,count,
(select count(*)+1 from 
(select good,count(*) as count from vote group by good) as t2 
where t1.count<t2.count) as rank
from (select good,count(*) as count from vote group by good) as t1
order by rank asc,good asc

Comments

[supermaruetsu]

はい、おっしゃる通りCOUNTの数が多い順に1位２位...と付けたいです。３位もおっしゃる通り存在せず、４位がきます。初心者のため、お恥ずかしながらインデックスという言葉にもピンと来ません。。。。