时间戳

首页 > 解决方案 > 如何使用 MYSQL 获取每个用户最近 n 天的分数变化以及多列的排名？

mysql - 如何使用 MYSQL 获取每个用户最近 n 天的分数变化以及多列的排名？

问题描述

我有一个 MYSQL 数据库，可以跟踪所有用户的每日总分（以及其他一些类似的分数/计数类型指标，如“badgesEarned”，我只包括我需要跟踪的 5 个字段中的 2 个）。它仅包含用户活跃的日期（获得分数或徽章）的数据。所以数据库不会有每个日期的数据。

这是一个玩具示例： 示例数据库表：“用户”

现在我的目标是获得每个用户最近 7 天的分数变化（我还需要做最后 30 天和 365 天，但在这个例子中我们只坚持 7 天）。由于 db 表存储了每个用户所有活动天数的总分数快照，因此我编写了一个 SQL 查询来查找两个适当的行/快照并获取它们之间的分数/徽章的差异。这 2 行将是当前日期行（或者如果不存在，则使用它之前的行）与第 (current_date - 7) 行（或者如果不存在，则使用它之前的行）。

更糟糕的是，我还必须通过 dense_rank() SQL 方法跟踪每个玩家的“排名”，并将其作为列添加到最终结果表中。

到目前为止，我有两种方法可以使用 2 个不同的 SQL 查询来实现这一点。

我的主要问题是- 在性能/良好实践/效率方面，其中一个是否比另一个“更好”？还是它们都很可怕，我一开始就完全走错了路，完全错过了更有效的方法？我对 SQL 的东西不是很好，所以如果问题和代码示例令人恐惧，请提前道歉：

第一种方法： 仅使用多个嵌套子查询（无连接）。

SELECT *, dense_rank() OVER (ORDER BY t3.score DESC) AS ranking
FROM
(
  SELECT t1.userId, 
                
                                (SELECT t2.score
                                FROM User t2 
                                WHERE t2.date <= CURDATE() AND t2.userId=t1.userId
                                ORDER BY t2.date DESC LIMIT 1)
                                - 
                                (SELECT t2.score
                                FROM User t2 
                                WHERE t2.date <= DATE_ADD(CURDATE(), INTERVAL - 7 DAY) AND t2.userId=t1.userId
                                ORDER BY t2.date DESC LIMIT 1) as score,
                
                                (SELECT t2.badgesEarned
                                FROM User t2 
                                WHERE t2.date <= CURDATE() AND t2.userId=t1.userId
                                ORDER BY t2.date DESC LIMIT 1)
                                - 
                                (SELECT t2.badgesEarned
                                FROM User t2 
                                WHERE t2.date <= DATE_ADD(CURDATE(), INTERVAL - 7 DAY) AND t2.userId=t1.userId
                                ORDER BY t2.date DESC LIMIT 1) as badgesEarned
                
    FROM User t1
    GROUP BY t1.userId) t3

第二种方法： 为每个日期点获取 2 个单独的表，然后进行内部联接以减去相关列。

SELECT *, dense_rank() OVER (ORDER BY T0.score_delta DESC) AS ranking
FROM
(SELECT T1.userId,
        (T1.score - T2.score),
        (T1.badgesEarned - T2.badgesEarned)

FROM 

(select *
from (
   select *, row_number() over (partition by userId order by date desc) as ranking
   from User
   where date<=date_add(CURDATE(),interval -7 day)
) t
where t.ranking = 1) as T2

INNER JOIN

(select *
from (
   select *, row_number() over (partition by userId order by date desc) as ranking
   from User
   where date<=CURDATE()
) t
where t.ranking = 1) as T1

on T1.userId= T2.userId ) T0

附带问题：我的一位同事建议我在代码本身中处理列减法 - 比如，我会调用数据库两次，获取两个表（一个用于 CURDATE()，另一个用于 CURDATE-7），然后遍历所有用户对象并减去相关字段以构建我的最终结果列表。我不确定这是否是更好的方法，所以我应该这样做而不是通过 SQL 方式处理它吗？

如果您想使用虚拟数据，这里是数据库的 SQLfiddle：http ://sqlfiddle.com/#!9/86c58f0/1

此外，以上两个代码段在我的 MySQL 8.0 工作台上运行良好，没有错误。

标签： mysqlsqldateinner-joindense-rank

解决方案

我没有得到你预期的结果。但是你能不能只使用窗口函数和 RANGE 子句？

我只是在创建中央主干表，然后由你来减去你需要从彼此减去的任何东西，最后到dense_rank（）你需要到dense_rank（）。基本上，我认为您需要DENSE_RANK()从我with_a_week_before的内联表中选择包含 的最终选择。

WITH                                                                                                 
-- your input
usr(userid,dt,score,badgesearned) AS (
          SELECT 1234,DATE '2020-08-06', 100, 10
UNION ALL SELECT 1234,DATE '2020-08-07', 120, 12
UNION ALL SELECT 1234,DATE '2020-08-08', 130, 13
UNION ALL SELECT 1234,DATE '2020-08-12', 140, 14
UNION ALL SELECT 1234,DATE '2020-08-14', 150, 15
UNION ALL SELECT  100,DATE '2020-08-05', 100, 10
UNION ALL SELECT  100,DATE '2020-08-10', 100, 10
UNION ALL SELECT  100,DATE '2020-08-14', 200, 10
UNION ALL SELECT    1,DATE '2020-08-05', 140, 14
UNION ALL SELECT    1,DATE '2020-08-08', 145, 14
UNION ALL SELECT    1,DATE '2020-08-12', 150, 15
)
,
with_a_week_before AS (
  SELECT 
    *
  , FIRST_VALUE(score) OVER(
      PARTITION BY userid ORDER BY dt
      RANGE BETWEEN INTERVAL '7 DAYS' PRECEDING AND CURRENT ROW
    ) AS score_a_week
  , FIRST_VALUE(badgesearned) OVER(
      PARTITION BY userid ORDER BY dt
      RANGE BETWEEN INTERVAL '7 DAYS' PRECEDING AND CURRENT ROW
    ) AS badgesearned_a_week
  , FIRST_VALUE(dt) OVER( -- check the date of the previous row
      PARTITION BY userid ORDER BY dt
      RANGE BETWEEN INTERVAL '7 DAYS' PRECEDING AND CURRENT ROW
    ) AS dt_a_week
  FROM usr
)
SELECT * FROM with_a_week_before ORDER BY userid
-- out  userid |     dt     | score | badgesearned | score_a_week | badgesearned_a_week | dt_a_week  
-- out --------+------------+-------+--------------+--------------+---------------------+------------
-- out       1 | 2020-08-05 |   140 |           14 |          140 |                  14 | 2020-08-05
-- out       1 | 2020-08-08 |   145 |           14 |          140 |                  14 | 2020-08-05
-- out       1 | 2020-08-12 |   150 |           15 |          140 |                  14 | 2020-08-05
-- out     100 | 2020-08-05 |   100 |           10 |          100 |                  10 | 2020-08-05
-- out     100 | 2020-08-10 |   100 |           10 |          100 |                  10 | 2020-08-05
-- out     100 | 2020-08-14 |   200 |           10 |          100 |                  10 | 2020-08-10
-- out    1234 | 2020-08-06 |   100 |           10 |          100 |                  10 | 2020-08-06
-- out    1234 | 2020-08-07 |   120 |           12 |          100 |                  10 | 2020-08-06
-- out    1234 | 2020-08-08 |   130 |           13 |          100 |                  10 | 2020-08-06
-- out    1234 | 2020-08-12 |   140 |           14 |          100 |                  10 | 2020-08-06
-- out    1234 | 2020-08-14 |   150 |           15 |          120 |                  12 | 2020-08-07

推荐阅读