php - 根据总分、最高分和全球、国家、省份优化 MySQL 聚合排名和排行榜查询
问题描述
我在 MySQL 中的排名和排行榜方面遇到了一个相当复杂和困难的问题。
首先,这是game_instances
表结构(条带化):
+----+---------+---------------------------+--------+-------+-------+---------------------+
| id | user_id | country | region | score | moves | finished_at |
+----+---------+---------------------------+--------+-------+-------+---------------------+
| 1 | 1 | Iran, Islamic Republic Of | Qom | 404 | 71 | 2021-05-14 02:56:10 |
| 2 | 1 | Iran, Islamic Republic Of | Qom | 686 | 138 | 2021-05-14 02:58:13 |
| 3 | 1 | Iran, Islamic Republic Of | Qom | NULL | NULL | NULL |
| 4 | 2 | Iran, Islamic Republic Of | Yazd | 1162 | 194 | 2021-05-14 03:03:00 |
| 5 | 2 | Iran, Islamic Republic Of | Yazd | 220 | 56 | 2021-05-14 03:04:19 |
| 6 | 2 | Iran, Islamic Republic Of | Yazd | 8 | 5 | 2021-05-14 03:05:13 |
| 7 | 2 | Iran, Islamic Republic Of | Qom | 280 | 70 | 2021-05-14 03:06:11 |
| 8 | 2 | Iran, Islamic Republic Of | Qom | NULL | NULL | NULL |
| 9 | 3 | Iran, Islamic Republic Of | Qom | 570 | 107 | 2021-05-14 03:10:26 |
| 10 | 3 | Iran, Islamic Republic Of | Qom | 0 | 0 | 2021-05-14 03:32:40 |
+----+---------+---------------------------+--------+-------+-------+---------------------+
我需要特定用户的排名编号:
- 总分:全球
- 总分:国家
- 总分:地区
- 高分:全球
- 高分:国家
- 高分:地区
以及前 10 名用户的这 6 个细分市场的排行榜。总共剩下 12 个数据集。
我在 StackOverflow 和其他网站上搜索并尝试了很多小时,但没有成功。目前,我正在使用 12 个单独的查询来获取这些数据,这根本不是一个好习惯,我正在寻找一种方法来尽可能地组合和优化这些查询。
编辑:我正在使用MySQL 5.7。
这是我当前的实现:
public function leaderboard(Request $request)
{
$user = $request->input('_user');
if (!$user) {
return apiSend(null, 401);
}
$data = [
'total' => [
'player' => [
'score' => 0,
'global_rank' => 0,
'country_rank' => 0,
'region_rank' => 0,
],
'global' => [], 'country' => [], 'region' => [],
],
'high' => [
'player' => [
'score' => 0,
'global_rank' => 0,
'country_rank' => 0,
'region_rank' => 0,
],
'global' => [], 'country' => [], 'region' => [],
],
];
$countryWhereClause = $user->country ? "WHERE country = '{$user->country}'" : 'WHERE country IS NULL';
$regionWhereClause = $user->region ? "AND region = '{$user->region}'" : 'AND region IS NULL';
$totalGlobalRank = DB::select("SELECT t1.user_id, t1.rank, t1.total_score FROM (SELECT p.user_id, p.total_score, @curRank := @curRank + 1 AS rank FROM (SELECT user_id, SUM(score) AS total_score FROM game_instances GROUP BY user_id) p, (SELECT @curRank := 0) r ORDER BY p.total_score DESC) t1 WHERE t1.user_id = {$user->id}");
$totalCountryRank = DB::select("SELECT t1.user_id, t1.rank, t1.total_score FROM (SELECT p.user_id, p.total_score, @curRank := @curRank + 1 AS rank FROM (SELECT user_id, SUM(score) AS total_score FROM game_instances {$countryWhereClause} GROUP BY user_id) p, (SELECT @curRank := 0) r ORDER BY p.total_score DESC) t1 WHERE t1.user_id = {$user->id}");
$totalRegionRank = DB::select("SELECT t1.user_id, t1.rank, t1.total_score FROM (SELECT p.user_id, p.total_score, @curRank := @curRank + 1 AS rank FROM (SELECT user_id, SUM(score) AS total_score FROM game_instances {$countryWhereClause} {$regionWhereClause} GROUP BY user_id) p, (SELECT @curRank := 0) r ORDER BY p.total_score DESC) t1 WHERE t1.user_id = {$user->id}");
$highGlobalRank = DB::select("SELECT t1.user_id, t1.rank, t1.max_score FROM (SELECT p.user_id, p.max_score, @curRank := @curRank + 1 AS rank FROM (SELECT user_id, MAX(score) AS max_score FROM game_instances GROUP BY user_id) p, (SELECT @curRank := 0) r ORDER BY p.max_score DESC) t1 WHERE t1.user_id = {$user->id}");
$highCountryRank = DB::select("SELECT t1.user_id, t1.rank, t1.max_score FROM (SELECT p.user_id, p.max_score, @curRank := @curRank + 1 AS rank FROM (SELECT user_id, MAX(score) AS max_score FROM game_instances {$countryWhereClause} GROUP BY user_id) p, (SELECT @curRank := 0) r ORDER BY p.max_score DESC) t1 WHERE t1.user_id = {$user->id}");
$highRegionRank = DB::select("SELECT t1.user_id, t1.rank, t1.max_score FROM (SELECT p.user_id, p.max_score, @curRank := @curRank + 1 AS rank FROM (SELECT user_id, MAX(score) AS max_score FROM game_instances {$countryWhereClause} {$regionWhereClause} GROUP BY user_id) p, (SELECT @curRank := 0) r ORDER BY p.max_score DESC) t1 WHERE t1.user_id = {$user->id}");
if (count($totalGlobalRank)) {
$data['total']['player']['score'] = $totalGlobalRank[0]->total_score;
$data['total']['player']['global_rank'] = $totalGlobalRank[0]->rank;
}
if (count($totalCountryRank)) {
$data['total']['player']['country_rank'] = $totalCountryRank[0]->rank;
}
if (count($totalRegionRank)) {
$data['total']['player']['region_rank'] = $totalRegionRank[0]->rank;
}
if (count($highGlobalRank)) {
$data['high']['player']['score'] = $highGlobalRank[0]->max_score;
$data['high']['player']['global_rank'] = $highGlobalRank[0]->rank;
}
if (count($highCountryRank)) {
$data['high']['player']['country_rank'] = $highCountryRank[0]->rank;
}
if (count($highRegionRank)) {
$data['high']['player']['region_rank'] = $highRegionRank[0]->rank;
}
$countryWhereClause = $user->country ? "WHERE g.country = '{$user->country}'" : 'WHERE g.country IS NULL';
$regionWhereClause = $user->region ? "AND g.region = '{$user->region}'" : 'AND g.region IS NULL';
$totalGlobalLeaderboards = $this->totalGlobalLeaderboards($user);
$totalCountryLeaderboards = $this->totalCountryLeaderboards($user);
$totalRegionLeaderboards = $this->totalRegionLeaderboards($user);
$highGlobalLeaderboards = $this->highGlobalLeaderboards($user);
$highCountryLeaderboards = $this->highCountryLeaderboards($user);
$highRegionLeaderboards = $this->highRegionLeaderboards($user);
foreach ($totalGlobalLeaderboards as $r) {
$data['total']['global'][] = ['name' => $r->name, 'score' => $r->total_score, 'rank' => $r->rank, 'is_user' => $user->id == $r->user_id];
}
foreach ($totalCountryLeaderboards as $r) {
$data['total']['country'][] = ['name' => $r->name, 'score' => (int) $r->total_score, 'rank' => $r->rank, 'is_user' => $user->id == $r->user_id];
}
foreach ($totalRegionLeaderboards as $r) {
$data['total']['region'][] = ['name' => $r->name, 'score' => (int) $r->total_score, 'rank' => $r->rank, 'is_user' => $user->id == $r->user_id];
}
foreach ($highGlobalLeaderboards as $r) {
$data['high']['global'][] = ['name' => $r->name, 'score' => (int) $r->max_score, 'rank' => $r->rank, 'is_user' => $user->id == $r->user_id];
}
foreach ($highCountryLeaderboards as $r) {
$data['high']['country'][] = ['name' => $r->name, 'score' => (int) $r->max_score, 'rank' => $r->rank, 'is_user' => $user->id == $r->user_id];
}
foreach ($highRegionLeaderboards as $r) {
$data['high']['region'][] = ['name' => $r->name, 'score' => (int) $r->max_score, 'rank' => $r->rank, 'is_user' => $user->id == $r->user_id];
}
return apiSend($data);
}
// Returns leaderboards with ranks near user rank, if user is not in the top 10.
public function userLeaderboards(Request $request)
{
$user = $request->input('_user');
if (!$user) {
return apiSend(null, 401);
}
$rank = (int) $request->query('rank');
$type = $request->query('type');
$scope = $request->query('scope');
if ($rank <= 10) {
return apiSend(null, 400);
}
$data = [];
$rows = [];
switch (true) {
case $type == 'total' && $scope == 'global':
$rows = $this->totalGlobalLeaderboards($user, $rank);
break;
case $type == 'total' && $scope == 'country':
$rows = $this->totalCountryLeaderboards($user, $rank);
break;
case $type == 'total' && $scope == 'region':
$rows = $this->totalRegionLeaderboards($user, $rank);
break;
case $type == 'high' && $scope == 'global':
$rows = $this->highGlobalLeaderboards($user, $rank);
break;
case $type == 'high' && $scope == 'country':
$rows = $this->highCountryLeaderboards($user, $rank);
break;
case $type == 'high' && $scope == 'region':
$rows = $this->highRegionLeaderboards($user, $rank);
break;
default:
return apiSend(null, 400);
}
foreach ($rows as $r) {
$data[] = ['name' => $r->name, 'score' => (int) ($type == 'total' ? $r->total_score : $r->max_score), 'rank' => (int) $r->rank, 'is_user' => $user->id == $r->user_id];
}
return apiSend($data);
}
private function totalGlobalLeaderboards($user, $rank = 0)
{
if (!$user) {
return [];
}
$offset = $rank > 10 ? sprintf('OFFSET %d', $rank - 6) : '';
$rowNumInit = $rank > 10 ? $rank - 6 : 0;
return DB::select("SELECT t.*, @rownum := @rownum + 1 AS rank FROM (SELECT u.name, g.user_id, SUM(g.score) AS total_score FROM game_instances g INNER JOIN users u ON u.id = g.user_id GROUP BY user_id ORDER BY total_score DESC LIMIT 10 {$offset}) t, (SELECT @rownum := {$rowNumInit}) r");
}
private function totalCountryLeaderboards($user, $rank = 0)
{
if (!$user) {
return [];
}
$offset = $rank > 10 ? sprintf('OFFSET %d', $rank - 6) : '';
$rowNumInit = $rank > 10 ? $rank - 6 : 0;
$countryWhereClause = $user->country ? "WHERE g.country = '{$user->country}'" : 'WHERE g.country IS NULL';
return DB::select("SELECT t.*, @rownum := @rownum + 1 AS rank FROM (SELECT u.name, g.user_id, SUM(g.score) AS total_score FROM game_instances g INNER JOIN users u ON u.id = g.user_id {$countryWhereClause} GROUP BY user_id ORDER BY total_score DESC LIMIT 10 {$offset}) t, (SELECT @rownum := {$rowNumInit}) r");
}
private function totalRegionLeaderboards($user, $rank = 0)
{
if (!$user) {
return [];
}
$offset = $rank > 10 ? sprintf('OFFSET %d', $rank - 6) : '';
$rowNumInit = $rank > 10 ? $rank - 6 : 0;
$countryWhereClause = $user->country ? "WHERE g.country = '{$user->country}'" : 'WHERE g.country IS NULL';
$regionWhereClause = $user->region ? "AND g.region = '{$user->region}'" : 'AND g.region IS NULL';
return DB::select("SELECT t.*, @rownum := @rownum + 1 AS rank FROM (SELECT u.name, g.user_id, SUM(g.score) AS total_score FROM game_instances g INNER JOIN users u ON u.id = g.user_id {$countryWhereClause} {$regionWhereClause} GROUP BY user_id ORDER BY total_score DESC LIMIT 10 {$offset}) t, (SELECT @rownum := {$rowNumInit}) r");
}
private function highGlobalLeaderboards($user, $rank = 0)
{
if (!$user) {
return [];
}
$offset = $rank > 10 ? sprintf('OFFSET %d', $rank - 6) : '';
$rowNumInit = $rank > 10 ? $rank - 6 : 0;
return DB::select("SELECT t.*, @rownum := @rownum + 1 AS rank FROM (SELECT u.name, g.user_id, MAX(g.score) AS max_score FROM game_instances g INNER JOIN users u ON u.id = g.user_id GROUP BY user_id ORDER BY max_score DESC LIMIT 10 {$offset}) t, (SELECT @rownum := {$rowNumInit}) r");
}
private function highCountryLeaderboards($user, $rank = 0)
{
if (!$user) {
return [];
}
$offset = $rank > 10 ? sprintf('OFFSET %d', $rank - 6) : '';
$rowNumInit = $rank > 10 ? $rank - 6 : 0;
$countryWhereClause = $user->country ? "WHERE g.country = '{$user->country}'" : 'WHERE g.country IS NULL';
return DB::select("SELECT t.*, @rownum := @rownum + 1 AS rank FROM (SELECT u.name, g.user_id, MAX(g.score) AS max_score FROM game_instances g INNER JOIN users u ON u.id = g.user_id {$countryWhereClause} GROUP BY user_id ORDER BY max_score DESC LIMIT 10 {$offset}) t, (SELECT @rownum := {$rowNumInit}) r");
}
private function highRegionLeaderboards($user, $rank = 0)
{
if (!$user) {
return [];
}
$offset = $rank > 10 ? sprintf('OFFSET %d', $rank - 6) : '';
$rowNumInit = $rank > 10 ? $rank - 6 : 0;
$countryWhereClause = $user->country ? "WHERE g.country = '{$user->country}'" : 'WHERE g.country IS NULL';
$regionWhereClause = $user->region ? "AND g.region = '{$user->region}'" : 'AND g.region IS NULL';
return DB::select("SELECT t.*, @rownum := @rownum + 1 AS rank FROM (SELECT u.name, g.user_id, MAX(g.score) AS max_score FROM game_instances g INNER JOIN users u ON u.id = g.user_id {$countryWhereClause} {$regionWhereClause} GROUP BY user_id ORDER BY max_score DESC LIMIT 10 {$offset}) t, (SELECT @rownum := {$rowNumInit}) r");
}
如您所见,我不精通 SQL。
谢谢你。
编辑2:我不知道是否可以将排行榜查询组合在一起,因为它们每个都返回自己的结果集而不是单行,但如果您认为它们可以组合,我会很高兴也很高兴得知这一点——也许会返回一个多维数组。
有 6 个排行榜:(total_score, max_score) x (global, country, region)。
这些排行榜中的每一个最多包含 10 条具有以下结构的记录:
[
{
"name": "Mina", // Joined from users table.
"user_id": 1,
"toal_score": 7400, // Or max_score for Max Score Leaderboards.
"rank": 1,
},
{...}
]
但我认为可以将所有(特定用户的)排名查询组合成一个查询,因为它们只返回一行,我们可以将多列连接在一起。像这样的东西:
// SELECT ... WHERE user_id = 1;
{
"total_score_global": 3540,
"total_score_global_rank": 13,
"total_score_country": 2830,
"total_score_country_rank": 6,
"total_score_region": 2600,
"total_score_region_rank": 2,
"max_score_global": 1084,
"max_score_global_rank": 19,
"max_score_country": 0, // No data for this user with given country.
"max_score_country_rank": 0, // So his rank will be 0.
"max_score_region": 950,
"max_score_region_rank": 1,
}
我想要的只是优化这些查询的执行,可能通过减少查询计数——目前为 12。
我真的试图实现这一点,但我能得到的只是空的结果集或语法错误。
我的许多尝试之一是:
SELECT
r1.total_score AS total_score_global, r1.rank AS total_score_global_rank,
r2.total_score AS total_score_country, r2.rank AS total_score_country_rank,
r3.total_score AS total_score_region, r3.rank AS total_score_region_rank,
r4.max_score AS max_score_global, r4.rank AS max_score_global_rank,
r5.max_score AS max_score_country, r5.rank AS max_score_country_rank,
r6.max_score AS max_score_region, r6.rank AS max_score_region_rank,
FROM
(SELECT t1.user_id, t1.rank, t1.total_score FROM (SELECT p.user_id, p.total_score, @curRank := @curRank + 1 AS rank FROM (SELECT user_id, SUM(score) AS total_score FROM game_instances GROUP BY user_id) p, (SELECT @curRank := 0) r ORDER BY p.total_score DESC) t1 WHERE t1.user_id = 1) r1,
(SELECT t1.user_id, t1.rank, t1.total_score FROM (SELECT p.user_id, p.total_score, @curRank := @curRank + 1 AS rank FROM (SELECT user_id, SUM(score) AS total_score FROM game_instances WHERE country = 'cname' GROUP BY user_id) p, (SELECT @curRank := 0) r ORDER BY p.total_score DESC) t1 WHERE t1.user_id = 1) r2,
(SELECT t1.user_id, t1.rank, t1.total_score FROM (SELECT p.user_id, p.total_score, @curRank := @curRank + 1 AS rank FROM (SELECT user_id, SUM(score) AS total_score FROM game_instances WHERE country = 'cname' AND region = 'rname' GROUP BY user_id) p, (SELECT @curRank := 0) r ORDER BY p.total_score DESC) t1 WHERE t1.user_id = 1) r3,
(SELECT t1.user_id, t1.rank, t1.max_score FROM (SELECT p.user_id, p.max_score, @curRank := @curRank + 1 AS rank FROM (SELECT user_id, MAX(score) AS max_score FROM game_instances GROUP BY user_id) p, (SELECT @curRank := 0) r ORDER BY p.max_score DESC) t1 WHERE t1.user_id = 1) r4,
(SELECT t1.user_id, t1.rank, t1.max_score FROM (SELECT p.user_id, p.max_score, @curRank := @curRank + 1 AS rank FROM (SELECT user_id, MAX(score) AS max_score FROM game_instances WHERE country = 'cname' GROUP BY user_id) p, (SELECT @curRank := 0) r ORDER BY p.max_score DESC) t1 WHERE t1.user_id = 1) r5,
(SELECT t1.user_id, t1.rank, t1.max_score FROM (SELECT p.user_id, p.max_score, @curRank := @curRank + 1 AS rank FROM (SELECT user_id, MAX(score) AS max_score FROM game_instances WHERE country = 'cname' AND region = 'rname' GROUP BY user_id) p, (SELECT @curRank := 0) r ORDER BY p.max_score DESC) t1 WHERE t1.user_id = 1) r6
解决方案
这不是答案
首先,您必须在字段user_id上添加索引
温度样本
SELECT
SUM(sum_table.sum_score) AS total_score_global
, SUM(IF( sum_user.sum_score > sum_table.sum_score, 1,0)) AS total_score_global_rank
, SUM(IF( sum_user.country = sum_table.country AND sum_user.sum_score > sum_table.sum_score, sum_table.sum_score,0)) AS total_score_country
, SUM(IF( sum_user.country = sum_table.country AND sum_user.sum_score > sum_table.sum_score, 1,0)) AS total_score_country
, SUM(IF( sum_user.region = sum_table.region AND sum_user.sum_score > sum_table.sum_score, sum_table.sum_score,0)) AS total_score_region
, SUM(IF( sum_user.region = sum_table.region AND sum_user.sum_score > sum_table.sum_score, 1,0)) AS total_score_region_rank
FROM ( SELECT MAX(score) as sum_score, g.* FROM game_instances AS g GROUP BY user_id ORDER BY sum_score) AS sum_table
JOIN ( SELECT MAX(score) as sum_score, x.* FROM game_instances AS x WHERE user_id = 5 GROUP BY user_id) AS sum_user;
样本
MariaDB [(none)]> use ranking;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [ranking]>
MariaDB [ranking]>
MariaDB [ranking]> SELECT
-> SUM(sum_table.sum_score) AS total_score_global
-> , SUM(IF( sum_user.sum_score > sum_table.sum_score, 1,0)) AS total_score_global_rank
->
-> , SUM(IF( sum_user.country = sum_table.country AND sum_user.sum_score > sum_table.sum_score, sum_table.sum_score,0)) AS total_score_country
-> , SUM(IF( sum_user.country = sum_table.country AND sum_user.sum_score > sum_table.sum_score, 1,0)) AS total_score_country
->
-> , SUM(IF( sum_user.region = sum_table.region AND sum_user.sum_score > sum_table.sum_score, sum_table.sum_score,0)) AS total_score_region
-> , SUM(IF( sum_user.region = sum_table.region AND sum_user.sum_score > sum_table.sum_score, 1,0)) AS total_score_region_rank
->
->
->
-> FROM ( SELECT MAX(score) as sum_score, g.* FROM game_instances AS g GROUP BY user_id ORDER BY sum_score) AS sum_table
-> JOIN ( SELECT MAX(score) as sum_score, x.* FROM game_instances AS x WHERE user_id = 5 GROUP BY user_id) AS sum_user;
+--------------------+-------------------------+---------------------+---------------------+--------------------+-------------------------+
| total_score_global | total_score_global_rank | total_score_country | total_score_country | total_score_region | total_score_region_rank |
+--------------------+-------------------------+---------------------+---------------------+--------------------+-------------------------+
| 33440 | 12 | 9794 | 9 | 2038 | 2 |
+--------------------+-------------------------+---------------------+---------------------+--------------------+-------------------------+
1 row in set (0.00 sec)
MariaDB [ranking]>
推荐阅读
- java - 如何使用 Maven 为每个操作系统下载 javafx 库?
- python-3.x - 内部回收视图未显示在框布局中 - Kivy
- google-colaboratory - CUB_200_2011 数据集下载链接错误 COLAB
- php - 如何在 Laravel 中编写给定的 SQL 查询
- c++ - gsoap-2.8.41 或更高版本与 openssl-1 兼容。1.1k?
- c++ - 不再使用安装 ITK4.10.0 作为 rusage 的困难
- cookies - SameSite:松散的 cookie 不适用于 Safari
- azureservicebus - Azure 服务总线,错误:无法在启用自动转发的实体上创建消息接收器
- ios - 在 OnOpenUrl 视图修饰符中调用 api 后刷新视图
- node.js - 逐行从aws s3读取一组lage txt文件