MySQLでROW_NUMBERの機能がほしい

Question

###実現したいこと
スレッドのコメントランキングをUPDATEしたいです。

コメントへの評価を上げる|下げるというアクションがありまして、スレッドのグループごとに、この合計点でランキングをつけて、rankingカラムをUPDATEする処理になります。

ただ合計点が同値の場合、ランキングは同位にはせず連番にしたいです。

###問題点
ランキングのUPDATEはできたのですが、合計点が同値の場合、ランキングも同位になってしまいます。

たとえば次のp_commentsというテーブルにrankingを入れたいわけですが、
super_ID=3(39行目)にコメントがあるように
#　合計点は 3-4=-1 であり、ranking は 2 にしたいが 1 となる
というのが問題です。

このsuper_ID=3はsuper_ID=8と同じ-1という合計点なので確かに同位ではあるのですが、今回はsuper_IDが大きいものを上位とし、
super_ID=8(42行目)のrankingを1位にして
super_ID=3(39行目)のrankingを2位にしたいのです。

###前提のテーブル構成

テーブルは次の５つで構成されます。
-- 投稿 (スーパータイプ)
-- 投稿 (サブタイプ/スレッド)
-- 投稿 (サブタイプ/コメント)
-- 評価を上げる
-- 評価を下げる

-- 投稿 (スーパータイプ)
CREATE TABLE p_supers
    (`ID` int, `kind_id` int, `sentence` varchar(1000), primary key(`ID`))
;   
INSERT INTO p_supers
    (`ID`, `kind_id`, `sentence`)
VALUES
     (1,  1,  '猫について語りましょう')
    ,(2,  1,  '犬について語りましょう')
    ,(3,  2,  '猫はいいです')
    ,(4,  2,  'たしかに猫は最高です')
    ,(5,  1,  '鰐について語りましょう')
    ,(6,  2,  '鰐は怖いです')
    ,(7,  2,  '鰐はやわらかいです')
    ,(8,  2,  '猫は液体です')
    ,(9,  2,  '猫は液体ではないです')
    ,(10, 2,  '鰐は怖くないです')
;

-- 投稿 (サブタイプ/スレッド)
CREATE TABLE p_threads
    (`super_ID` int, `title` varchar(100), primary key(`super_ID`))
;   
INSERT INTO p_threads
    (`super_ID`, `title`)
VALUES
     (1, '猫スレ')
    ,(2, '犬スレ')
    ,(5, '鰐スレ')
;

-- 投稿 (サブタイプ/コメント)
CREATE TABLE p_comments
    (`super_ID` int, `thread_ID` int, `up_count` int, `down_count` int, `ranking` int, primary key(`super_ID`))
;   
INSERT INTO p_comments
    (`super_ID`, `thread_ID`, `up_count`, `down_count`, `ranking`)
VALUES
    # ranking カラムは該当のソースコードを実行すると、次のコメントのようになる
     (3, 1, 3, 4, 0) #　合計点は 3-4=-1 であり、ranking は 2 にしたいが 1 となる
    ,(6, 5, 1, 1, 0) #　合計点は 1-1=0  であり、ranking は 2 となる
    ,(7, 5, 2, 0, 0) #　合計点は 2-0=2  であり、ranking は 1 となる
    ,(8, 1, 1, 2, 0) #　合計点は 1-2=-1 であり、ranking は 1 となる
;

-- 評価を上げる
CREATE TABLE a_up_comments
    (`users_ID` int, `comment_ID` int, `created_at` datetime
     , foreign key (`comment_ID`) references p_comments(`super_ID`))
;   
INSERT INTO a_up_comments
    (`users_ID`, `comment_ID`, `created_at`)
VALUES
     (3, 3, '2020-12-01 12:00:00.000001')
    ,(4, 3, '2020-12-01 12:00:00.000002')
    ,(1, 7, '2020-12-01 12:00:00.000003')
    ,(1, 3, '2020-12-01 12:00:00.000004')
    ,(2, 6, '2020-12-01 12:00:00.000005')
    ,(1, 7, '2020-12-01 12:00:00.000006')
    ,(2, 8, '2020-12-01 12:00:00.000007')
;

-- 評価を下げる
CREATE TABLE a_down_comments
    (`users_ID` int, `comment_ID` int, `created_at` datetime
     , foreign key (`comment_ID`) references p_comments(`super_ID`))     
;   
INSERT INTO a_down_comments
    (`users_ID`, `comment_ID`, `created_at`)
VALUES
     (1, 3, '2020-12-02 12:00:00.000001')
    ,(4, 3, '2020-12-02 12:00:00.000002')
    ,(5, 8, '2020-12-02 12:00:00.000003')
    ,(1, 3, '2020-12-02 12:00:00.000004')
    ,(3, 3, '2020-12-02 12:00:00.000005')
    ,(1, 8, '2020-12-02 12:00:00.000006')
    ,(2, 6, '2020-12-02 12:00:00.000007')
;

# ranking を更新
update p_comments as t0
inner join (
  # thread_ID のグループで ranking の順位を取得
  select super_ID, up_count, down_count, (
    select count(*) +1
    from p_comments as t2
    where 1
	and ( t2.up_count - t2.down_count ) > ( t1.up_count - t1.down_count )
	and t2.thread_ID = t1.thread_ID
  ) as ranking
  from p_comments AS t1
) as t3 on t0.super_ID = t3.super_ID
set t0.ranking = t3.ranking;

###該当のソースコード
こちらが連番でなく同位になるという問題のUPDATEになります。
実行する → http://sqlfiddle.com/#!9/b0b213/1

# ranking を更新
update p_comments as t0
inner join (
  # thread_ID のグループで ranking の順位を取得
  select super_ID, up_count, down_count, (
    select count(*) +1
    from p_comments as t2
    where 1
	and ( t2.up_count - t2.down_count ) > ( t1.up_count - t1.down_count )
	and t2.thread_ID = t1.thread_ID
  ) as ranking
  from p_comments AS t1
) as t3 on t0.super_ID = t3.super_ID
set t0.ranking = t3.ranking;

###試したこと
該当のソースコードを使い同位のrankingを入れるのはひとまずよしとして、それが終わった後で改めて別の処理で連番にする。という風に下記の流れでならできますが、

select 
  (select count(*)+1
   from p_comments
   where thread_ID = x.thread_ID and super_ID > x.super_ID
  ) as 'No.'
  , super_ID, thread_ID
from p_comments as x
order by x.thread_ID, x.ranking desc

こうして別の処理にせず、該当のソースコードのUPDATEのクエリ１回で済ませたく思って質問させて頂きました。

もしそれができましたらご教示いただけますと幸いです。
宜しくお願いいたします。

Comments

[yambejp]

IDはprimaryにしていないのですか？（uniqueなnot nullなintというだけでもいいですが）
あとはMySQLのバージョンを明記ください

[ikatako]

すみません、仰る通りIDはprimaryでした。（該当のソースコードを編集いたしました。）

MySQLは現状では5.5なので、5.5でできるような方法ですとありがたいです。
しかし頑張れば5.7にできますので、5.7でしか難しければ頑張ります。

[yambejp]

5.5だと環境的にちょっと厳しいですね、せめて5.7.6以降なら生成列という仕組みが使えるんですが・・・
わりきってビューでやるという考え方もありますね

Answer 1

※ランクカラムを設定する

CREATE TABLE p_comments
(super_ID int unique,
thread_ID int,
up_count int,
down_count int,
rank int null);

INSERT INTO p_comments
(super_ID, thread_ID, up_count, down_count)
VALUES
(3, 1, 3, 4),
(6, 5, 1, 1),
(7, 5, 2, 0),
(8, 1, 1, 2);


insert into p_comments(super_ID,rank)  select super_ID,(
select count(*)+1 from p_comments
where up_count-down_count-t1.up_count+t1.down_count<0 or
(up_count-down_count-t1.up_count+t1.down_count=0 and super_ID<t1.super_ID) ) as rank
from p_comments as t1
on duplicate key update rank=values(rank)

Comments

[yambejp]

select *,(
select count(*)+1 from p_comments
where up_count-down_count-t1.up_count+t1.down_count<0 or
(up_count-down_count-t1.up_count+t1.down_count=0 and super_ID<t1.super_ID) ) as rank
from p_comments as t1

[ikatako]

UPDATEするならこうするという意味ですよね？

update p_comments as t0
inner join (

# ここにご回答を書く

) as t3 on t0.super_ID = t3.super_ID
set t0.ranking = t3.rank;

実行しますとrankingカラムに値は入ったものの、up_countとdown_countの計算結果に応じてのランキングにならず、super_IDと同じ並びの数字が入りました。

そして入った数字も連番でなくとびとびで、例えば
supers_IDの値に 1, 7, 12 と入っていたら、
rankingの値は 11, 17, 22 ととびとびに入るという不思議な結果でした。

[yambejp]

回答を修正版に書き換えました

[ikatako]

ご修正どうもありがとうございます。あと「>スレッドのグループごとに」という条件の方だけいかがでしょうか。

ご回答の結果↓はすべてでランク付けされますが、
super_ID, thread_ID, rank
3, 1, 1
6, 5, 3
7, 5, 4
8, 1, 2

目的↓はthread_IDが同じグループでランクの付けしたかったという点です。
super_ID, thread_ID, rank
3, 1, 2
6, 5, 2
7, 5, 1
8, 1, 1

[ikatako]

おかげ様でなんとかできたようです。
http://sqlfiddle.com/#!9/ae54a5/1
どうもありがとうございました。