如何在mysql中跟踪分数增益 [英] how to track score gains in mysql
问题描述
我想显示玩家当前的得分以及他们在选定时间范围内获得的分数.
I would like to display a players current score as well as how many points they have gained within a selected time frame.
我有两张桌子
技能表
+----+---------+---------------------+
| id | name | created_at |
+----+---------+---------------------+
| 1 | skill 1 | 2020-06-05 00:00:00 |
| 2 | skill 2 | 2020-06-05 00:00:00 |
| 3 | skill 3 | 2020-06-05 00:00:00 |
+----+---------+---------------------+
成绩表
+----+-----------+----------+-------+---------------------+
| id | player_id | skill_id | score | created_at |
+----+-----------+----------+-------+---------------------+
| 1 | 1 | 1 | 5 | 2020-06-06 00:00:00 |
| 2 | 1 | 1 | 10 | 2020-07-06 00:00:00 |
| 3 | 1 | 2 | 1 | 2020-07-06 00:00:00 |
| 4 | 2 | 1 | 11 | 2020-07-06 00:00:00 |
| 5 | 1 | 1 | 13 | 2020-07-07 00:00:00 |
| 6 | 1 | 2 | 10 | 2020-07-07 00:00:00 |
| 7 | 2 | 1 | 12 | 2020-07-07 00:00:00 |
| 8 | 1 | 1 | 20 | 2020-07-08 00:00:00 |
| 9 | 1 | 2 | 15 | 2020-07-08 00:00:00 |
| 10 | 2 | 1 | 17 | 2020-07-08 00:00:00 |
+----+-----------+----------+-------+---------------------+
我的预期结果是:-
24 小时查询
+-----------+---------+-------+------+
| player_id | name | score | gain |
+-----------+---------+-------+------+
| 1 | skill 1 | 20 | 7 |
| 1 | skill 2 | 15 | 5 |
+-----------+---------+-------+------+
7 天查询
+-----------+---------+-------+------+
| player_id | name | score | gain |
+-----------+---------+-------+------+
| 1 | skill 1 | 20 | 10 |
| 1 | skill 2 | 15 | 14 |
+-----------+---------+-------+------+
31 天查询
+-----------+---------+-------+------+
| player_id | name | score | gain |
+-----------+---------+-------+------+
| 1 | skill 1 | 20 | 15 |
| 1 | skill 2 | 15 | 14 |
+-----------+---------+-------+------+
到目前为止我有以下内容，但这只是返回每个技能的最后 2 条记录，我正在努力计算收益和不同的时间范围
so far I have the following, but all this does is return the last 2 records for each skill, I am struggling to calculate the gains and the different time frames
SELECT player_id, skill_id, name, score
FROM (SELECT player_id, skill_id, name, score,
@skill_count := IF(@current_skill = skill_id, @skill_count + 1, 1) AS skill_count,
@current_skill := skill_id
FROM skill_scores
INNER JOIN skills
ON skill_id = skills.id
WHERE player_id = 1
ORDER BY skill_id, score DESC
) counted
WHERE skill_count <= 2
我需要一些帮助来确定我需要构建的查询以获得所需的结果，还是最好使用 php 而不是在 db 中执行此操作?
I would like some help figuring out the query I need to build to get the desired results, or is it best to do this with php instead of in the db?
-
MYSQL 8.0.20 虚拟数据 ID 是 primary_key 自动增量，但为了简单起见，我没有广告:-
MYSQL 8.0.20 dummy data id's are primary_key auto increment but I didnt ad that for simplicity:-
CREATE TABLE skills
(
id bigint,
name VARCHAR(80)
);
CREATE TABLE skill_scores
(
id bigint,
player_id bigint,
skill_id bigint,
score bigint,
created_at timestamp
);
INSERT INTO skills VALUES (1, 'skill 1');
INSERT INTO skills VALUES (2, 'skill 2');
INSERT INTO skills VALUES (3, 'skill 3');
INSERT INTO skill_scores VALUES (1, 1, 1 , 5, '2020-06-06 00:00:00');
INSERT INTO skill_scores VALUES (2, 1, 1 , 10, '2020-07-06 00:00:00');
INSERT INTO skill_scores VALUES (3, 1, 2 , 1, '2020-07-06 00:00:00');
INSERT INTO skill_scores VALUES (4, 2, 1 , 11, '2020-07-06 00:00:00');
INSERT INTO skill_scores VALUES (5, 1, 1 , 13, '2020-07-07 00:00:00');
INSERT INTO skill_scores VALUES (6, 1, 2 , 10, '2020-07-07 00:00:00');
INSERT INTO skill_scores VALUES (7, 2, 1 , 12, '2020-07-07 00:00:00');
INSERT INTO skill_scores VALUES (8, 1, 1 , 20, '2020-07-08 00:00:00');
INSERT INTO skill_scores VALUES (9, 1, 2 , 15, '2020-07-08 00:00:00');
INSERT INTO skill_scores VALUES (10, 2, 1 , 17, '2020-07-08 00:00:00');
推荐答案
WITH cte AS (
SELECT id, player_id, skill_id,
FIRST_VALUE(score) OVER (PARTITION BY player_id, skill_id ORDER BY created_at DESC) score,
FIRST_VALUE(score) OVER (PARTITION BY player_id, skill_id ORDER BY created_at DESC) - FIRST_VALUE(score) OVER (PARTITION BY player_id, skill_id ORDER BY created_at ASC) gain,
ROW_NUMBER() OVER (PARTITION BY player_id, skill_id ORDER BY created_at DESC) rn
FROM skill_scores
WHERE created_at BETWEEN @current_date - INTERVAL @interval DAY AND @current_date
)
SELECT cte.player_id, skills.name, cte.score, cte.gain
FROM cte
JOIN skills ON skills.id = cte.skill_id
WHERE rn = 1
ORDER BY player_id, name;
附言.我不明白 gain=15 的 31 天期限 - '2020-07-08 00:00:00' 和 之间的区别'2020-06-06 00:00:00' 是 32 天.
Ps. I don't understand where gain=15 is taken for 31-day period - the difference between '2020-07-08 00:00:00' and '2020-06-06 00:00:00' is 32 days.
这篇关于如何在mysql中跟踪分数增益的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
