MySQL ウィンドウ関数を使用してリストのランキングを達成する方法

まず、テスト テーブルを構築します

create table praise_record(
    id bigint primary key auto_increment,
    name varchar(10),
    praise_num int
) ENGINE=InnoDB;

次に、chatGpt にいくつかのテスト データを生成させます

INSERT INTO praise_record (name, praise_num) VALUES ('John', 5);
INSERT INTO praise_record (name, praise_num) VALUES ('Jane', 3);
INSERT INTO praise_record (name, praise_num) VALUES ('Bob', 10);
INSERT INTO praise_record (name, praise_num) VALUES ('Alice', 3);
INSERT INTO praise_record (name, praise_num) VALUES ('David', 7);
INSERT INTO praise_record (name, praise_num) VALUES ('oct', 7);

その後、ニーズを認識し始めることができます。「いいね!」のリストを返します。ランキングを返す

rank()

rank() 関数を使用していいねのリストを返します。rank() over()

## 注意这里返回的rank字段要用反引号包起来
select name, praise_num, rank() over (order by praise_num desc) as `rank` from praise_record;
+-------+------------+------+
| name  | praise_num | rank |
+-------+------------+------+
| Bob   |         10 |    1 |
| David |          7 |    2 |
| oct   |          7 |    2 |
| John  |          5 |    4 |
| Jane  |          3 |    5 |
| Alice |          3 |    5 |
+-------+------------+------+

Rank()関数を使用すると、同じいいね数でも同じ順位となり、順位が急上昇する場合があり、最終的な順位は連続しません

dense_rank()

dense_rank() 関数を使用していいねのリストを返します。dense_rank() over()

 select name, praise_num, dense_rank() over (order by praise_num desc) as `rank` from praise_record;
 
 +-------+------------+------+
| name  | praise_num | rank |
+-------+------------+------+
| Bob   |         10 |    1 |
| David |          7 |    2 |
| oct   |          7 |    2 |
| John  |          5 |    3 |
| Jane  |          3 |    4 |
| Alice |          3 |    4 |
+-------+------------+------+

rank() 関数と同じで、同じ数のいいねをすると同じランキングが返されます。ただし、dense_rank() によって返される最終的なランキングは連続ランキングです

row_number()

row_number() 関数はいいねのリストを返し、row_number() over()

 select name, praise_num, row_number() over (order by praise_num desc) as `rank` from praise_record;
 +-------+------------+------+
| name  | praise_num | rank |
+-------+------------+------+
| Bob   |         10 |    1 |
| David |          7 |    2 |
| oct   |          7 |    3 |
| John  |          5 |    4 |
| Jane  |          3 |    5 |
| Alice |          3 |    6 |
+-------+------------+------+

row_number() 関数は、返されたリストにシリアル番号のみが必要な場合の使用に適しています

上記の 3 つの関数はすべて MySQL8.0 に新しく追加されたものであるため、次のような古いバージョンではMySQL5.7 をシミュレーションして、これら 3 つのウィンドウ関数の実装原理を学ぶことができます。

rank() 関数のシミュレーション実装

select p1.name, p1.praise_num, count(p2.praise_num) + 1 as `rank` from praise_record p1
left join praise_record p2 on p1.praise_num < p2.praise_num
group by p1.name, p1.praise_num
order by `rank`;
+-------+------------+------+
| name  | praise_num | rank |
+-------+------------+------+
| Bob   |         10 |    1 |
| David |          7 |    2 |
| oct   |          7 |    2 |
| John  |          5 |    4 |
| Jane  |          3 |    5 |
| Alice |          3 |    5 |
+-------+------------+------+

自己結合方式を使用して各スコアを低くすることができます行スコアの現在のレコード数よりも大きくなり、最後に現在の行のランキングとしてカウント値に 1 を追加して、rank()

dense_rank()

select p1.name, p1.praise_num, count(distinct p2.praise_num) + 1 as `dense_rank` from praise_record p1
left join praise_record p2 on p1.praise_num < p2.praise_num
group by p1.name, p1.praise_num
order by `dense_rank`;
+-------+------------+------------+
| name  | praise_num | dense_rank |
+-------+------------+------------+
| Bob   |         10 |          1 |
| oct   |          7 |          2 |
| David |          7 |          2 |
| John  |          5 |          3 |
| Jane  |          3 |          4 |
| Alice |          3 |          4 |
+-------+------------+------------+

のシミュレーション実装をシミュレートします。 Density_rank の実装は、rank と似ています。唯一の違いは、いいね数の重複を排除するために、distinct が追加され、異なるいいね数に対して返されるランキングが連続的になることです。

row_number

##使用自定义变量得先初始化
set @rowNum = 0;
select name, praise_num, @rowNum := @rowNum +1 as `row_number`  from praise_record order by praise_num desc ;
+-------+------------+------------+
| name  | praise_num | row_number |
+-------+------------+------------+
| Bob   |         10 |          1 |
| David |          7 |          2 |
| oct   |          7 |          3 |
| John  |          5 |          4 |
| Jane  |          3 |          5 |
| Alice |          3 |          6 |
+-------+------------+------------+

# のシミュレーション実装##rowNum 変数を使用して行番号を記録します。各行のデータ rowNUm は 1 なので、必要なシーケンス番号を取得できます

以上がMySQL ウィンドウ関数を使用してリストのランキングを達成する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。