mysql - 使用重复的数据库记录创建排名和统计

Question

今天，我想获得在数据库中为每个用户创建分数的帮助。我有这样的疑问:

SELECT
    r1.id,
    r1.nickname,
    r1.fecha,
    r1.bestia1,
    r1.bestia2,
    r1.bestia3,
    r1.bestia4
    r1.bestia5
FROM
    reporte AS r1
INNER JOIN
    ( SELECT
          nickname, MAX(fecha) AS max_date
      FROM
          reporte
      GROUP BY
          nickname ) AS latests_reports 
ON latests_reports.nickname = r1.nickname
AND latests_reports.max_date = r1.fecha
ORDER BY
    r1.fecha DESC

这是来自该网站的一位 friend ，他帮助我获得了“每天每个用户的最后一条记录”，基于此我正在寻找如何计算每日、每周或每月的排名结果，以便使用统计图表或谷歌数据工作室，我尝试过下一个:

select id, nickname, sum(bestia1), sum(bestia2), etc...

但它没有给出我想要的完整结果。这就是我寻求帮助的原因。此外，我知道 datastudio 过滤器，我可以在其中显示许多图表，但我仍然可以完全计数。

例如，一名玩家在过去 30 天内报告杀死了 265 个怪物，但是当我在 datastudio 中使用我的查询时，它只计算最新值(可以是 12)。所以我想正确计数以便与图表一起使用

使用我的查询过滤的 SQL 记录:

Answer 1

获取每个用户在最近 X 天杀死的怪物总数并进行分数计算的一种通用方法(如您在评论中建议的那样)可以是这样的:

SET @daysOnHistory = X; -- Where X should be an integer positive number (like 10).

SELECT
    nickname,
    SUM(bestia1) AS total_bestia1_killed,
    SUM(bestia2) AS total_bestia2_killed,
    SUM(bestia3) AS total_bestia3_killed,
    SUM(bestia4) AS total_bestia4_killed,
    SUM(bestia5) AS total_bestia5_killed,
    SUM(bestia1 + bestia2 + bestia3 + bestia4 + bestia5) AS total_monsters_killed,
    SUM(bestia1 +  2 * bestia2 + 3 * bestia3 + 4 * bestia4 + 5 * bestia5) AS total_score
FROM
    reporte
WHERE
    fecha >= DATE_ADD(DATE(NOW()), INTERVAL -@daysOnHistory DAY)
GROUP BY
    nickname
ORDER BY
    total_score DESC

现在，如果您想要进行相同的计算，但只考虑本周的天数(假设一周从星期一开始)，则需要替换之前的 WHERE 下一个子句:

WHERE
    fecha >= DATE_ADD(DATE(NOW()), INTERVAL -WEEKDAY(NOW()) DAY)

更重要的是，如果您想要全部相同，但只考虑当月的天数，则需要将 WHERE 子句替换为:

WHERE
    MONTH(fecha) = MONTH(NOW())

要评估当年天的统计信息，您需要将 WHERE 子句替换为:

WHERE
    YEAR(fecha) = YEAR(NOW())

最后，对于特定天数范围的评估，您可以使用，例如:

WHERE
    DATE(fecha) BETWEEN CAST("2018-10-15" AS DATE) AND CAST('2018-11-10' AS DATE)

我希望本指南能够帮助您并阐明您的观点。