MySQL每日得分随全局排名变化

Question

我正在构建一个系统，该系统在每天结束时记录用户的总分(“XP”)，以便玩家可以随着时间的推移跟踪他们的进度。目前，我正在尝试编写一个查询，该查询可以根据前 X 天的 XP 变化返回排行榜，并带有排名。我不想构建专用的排行榜表，因为间隔 X 可以更改。

编辑

我在这里添加了一个 SQL Fiddle:

http://sqlfiddle.com/#!9/a7c1c/9 - 这是一个没有排名的工作版本 http://sqlfiddle.com/#!9/a7c1c/11 - 这是我能得到的最接近的排名合并(这不起作用，但希望能清楚我正在尝试的内容)

问题:

• 我有一个双层嵌套子查询。我不喜欢这样，但是如果没有它，我找不到计算 countsub 子查询中的 HAVING BY 子句的方法；
• 由于双重嵌套子查询，xp_change 列在 countsub 查询中不可用，因此我实际上无法比较更改

在我看来，我要么没有正确编写查询，要么遗漏了什么。我一直在尝试找出一种方法来删除 COUNT 子查询，但到目前为止还没有成功。如果有人能指出我正确的方向，那就太好了!

/编辑

这是我的模式:

账户

CREATE TABLE `accounts` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `display_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `slug` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `scanned_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `accounts_display_name_index` (`display_name`),
  KEY `accounts_last_tracked_index` (`scanned_at`),
  KEY `accounts_slug_index` (`slug`)
)

account_instances

有问题的游戏有多种游戏类型，每种都有不同的排行榜，因此一个帐户可以有多个“实例”:

CREATE TABLE `account_instances` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `account_id` int(10) unsigned NOT NULL,
  `game_type_id` int(10) unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `account_instances_account_id_game_type_id_unique` (`account_id`,`game_type_id`),
  KEY `account_instances_game_type_id_foreign` (`game_type_id`),
  CONSTRAINT `account_instances_game_type_id_foreign` FOREIGN KEY (`game_type_id`) REFERENCES `game_types` (`id`)
)

统计数据

这些是用户可以获得 XP 分数的统计数据:

CREATE TABLE `stats` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `display_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `stats_name_unique` (`name`),
  UNIQUE KEY `stats_display_name_unique` (`display_name`),
  KEY `stats_name_index` (`name`)
)

account_instance_stats

将帐户实例和给定日期的统计数据与分数 (xp) 相关联:

CREATE TABLE `account_instance_stats` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `account_instance_id` int(10) unsigned NOT NULL,
  `stat_id` int(10) unsigned NOT NULL,
  `xp` bigint(20) DEFAULT NULL,
  `date` date NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `account_instance_stats_account_instance_id_stat_id_date_unique` (`account_instance_id`,`stat_id`,`date`),
  KEY `account_instance_stats_stat_id_foreign` (`stat_id`),
  KEY `account_instance_stats_xp_index` (`xp`),
  KEY `account_instance_stats_date_index` (`date`),
  CONSTRAINT `account_instance_stats_account_instance_id_foreign` FOREIGN KEY (`account_instance_id`) REFERENCES `account_instances` (`id`),
  CONSTRAINT `account_instance_stats_stat_id_foreign` FOREIGN KEY (`stat_id`) REFERENCES `stats` (`id`)
)

这是我到目前为止编写的查询。它不会运行，但希望您能看到我在这里尝试的内容:

SELECT 
    a.*, 
    SUM(ais.xp - ais2.xp) AS xp_change,
    (
        select count(*) FROM
        (
            SELECT COUNT(sub.id)
                FROM account_instance_stats AS sub 
                LEFT JOIN account_instance_stats sub2 
                    ON sub.account_instance_id = sub2.account_instance_id 
                    AND sub.stat_id = sub2.stat_id 
                    AND sub2.date = date_sub(sub.date, INTERVAL 1 day)  
                JOIN account_instances AS ai ON sub.account_instance_id = ai.id 
                WHERE ai.game_type_id = 1
                AND sub.date = curdate()
                AND sub.stat_id = 1
                GROUP BY sub.id
                HAVING SUM(sub.xp - sub2.xp) > xp_change
        ) AS countsub
    ) AS rank

    FROM account_instance_stats AS ais 
    LEFT JOIN account_instance_stats ais2 
        ON ais.account_instance_id = ais2.account_instance_id 
        AND ais.stat_id = ais2.stat_id 
        AND ais2.date = date_sub(ais.date, INTERVAL 1 day)  
    JOIN account_instances AS ai ON ais.account_instance_id = ai.id 
    JOIN accounts AS a ON ai.account_id = a.id
    WHERE ai.game_type_id = 1
    AND ais.date = curdate()
    AND ais.stat_id = 1
    GROUP BY a.id
    ORDER BY rank DESC
    LIMIT 10;

父查询的大部分是 account_instance_stats 上的left join，基于间隔，所以我可以比较 xp 列这两个日期。这一点按预期工作。我正在努力解决的部分是 rank 子查询。这几乎执行相同的查询，但通过计算有多少帐户具有更高的 xp_change 来计算排名。

谢谢!

Answer 1

Here is a solution that uses SQL variables

显示此代码。

SELECT *, @rank := COALESCE(@rank + 1, 1) AS ranking
FROM (
  SELECT a.ID AS ID, a.display_name AS display_name
   , SUM(ais.xp - ais2.xp) AS xp_change
  FROM accounts AS a 
  JOIN account_instances AS ai 
    ON ai.account_id = a.id
  JOIN account_instance_stats AS ais 
    ON ais.account_instance_id = ai.id 
  LEFT JOIN account_instance_stats ais2 
    ON ais.account_instance_id = ais2.account_instance_id 
       AND ais.stat_id = ais2.stat_id 
       AND ais2.date = date_sub(ais.date, INTERVAL 1 day) 
  WHERE ai.game_type_id = 1
       AND ais.date = '2016-06-02'
       AND ais.stat_id = 1
  GROUP BY a.id
  ORDER BY xp_change DESC) AS t1

我将您的表 JOIN 顺序重新排列为 (IMO) 更合乎逻辑的顺序。

鉴于此解决方案:如果您想要单个帐户(假设 ID=2)，我会将代码更改为:

SELECT * 
FROM (
  SELECT *, @rank := COALESCE(@rank + 1, 1) AS ranking
  FROM (
    SELECT a.ID AS ID, a.display_name AS display_name
     , SUM(ais.xp - ais2.xp) AS xp_change
    FROM accounts AS a 
    JOIN account_instances AS ai 
      ON ai.account_id = a.id
    JOIN account_instance_stats AS ais 
      ON ais.account_instance_id = ai.id 
    LEFT JOIN account_instance_stats ais2 
      ON ais.account_instance_id = ais2.account_instance_id 
         AND ais.stat_id = ais2.stat_id 
         AND ais2.date = date_sub(ais.date, INTERVAL 1 day) 
    WHERE ai.game_type_id = 1
         AND ais.date = '2016-06-02'
         AND ais.stat_id = 1
    GROUP BY a.id
    ORDER BY xp_change DESC) AS t1
) AS foo
WHERE id = 2;