如何使用MYSQL获得每个用户最后n天的分数变化以及多列的排名

Question

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

下面是一个玩具示例：示例数据库表："；用户"；

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

更糟糕的是，我还必须记录"；排名；通过dense_rank((SQL方法对每个玩家进行排序，并将其作为列添加到最终结果表中。

到目前为止，我有两种方法可以使用两个不同的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

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

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(，然后循环遍历所有User对象并减去相关字段，以构建我的最终结果列表。我不确定这是否是更好的方法，所以我应该这样做，而不是通过SQL的方式来处理它吗？

如果你想处理伪数据，下面是数据库的SQLfiddle：http://sqlfiddle.com/#！9/86c58f0/1

此外，上面两个代码段在我的MySQL 8.0工作台上运行得很好，没有任何错误。

Answer 1

我没有完全得到你预期的结果。但是，你能不能不把窗口函数和RANGE子句结合起来使用呢？

我只是在创建中心主干表，然后由您从彼此中减去需要减去的内容，最后再到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