对于我一直在努力编写的 SQL 查询,我非常感谢帮助。
背景:
每次用户玩游戏时,都会在表 game_runs
中创建一条记录,以及他们的 user_id
和 run_date
(MySQL 时间戳).
- 当用户成功玩游戏时,他们会获得大于 0 的分数。
- 如果游戏运行失败(例如可能崩溃),则得分为
0
表格看起来像这样:
id | run_date | user_id | score
-------------------------------------------------------
1 | 2020-02-02 00:20:00 | 10 | 0 |
2 | 2020-02-02 01:50:10 | 10 | 40 |
3 | 2020-02-02 03:40:20 | 11 | 80 |
4 | 2020-02-03 03:20:14 | 20 | 80 |
5 | 2020-02-03 12:20:14 | 21 | 0 |
6 | 2020-02-04 06:20:42 | 50 | 0 |
7 | 2020-02-04 11:15:00 | 50 | 0 |
8 | 2020-02-04 12:10:46 | 51 | 70 |
9 | 2020-02-05 00:15:00 | 60 | 0 |
10 | 2020-02-05 01:10:40 | 61 | 0 |
我想了解每天有多少用户无法运行游戏。
在上面的示例中,这是我希望生成的内容:
date | percent_users_who_failed_to_run_the_game
-------------------------------------------------------------
2020-02-02 | 0
2020-02-03 | 0.5
2020-02-04 | 0.5
2020-02-05 | 1
注意在 2020-02-02
上,未能运行游戏的用户百分比为 0%(即每个人至少成功一次)。这是因为在 2020-02-02
上,有 3 次运行:
- id=1: user_id
10
最初运行失败 (score=0) - id=2: user_id
10
第二次成功 (score=40) - id=3: user_id
11
成功
由于当天两位用户都成功了,所以失败的用户百分比为 0%。
我希望就如何开始提供任何意见。我使用的是 mySQL v8+,所以如果有必要可以访问窗口函数(我的研究告诉我它们可能有帮助,但无法编写执行此操作的查询)。
我认为正确的逻辑应该是找出具有 MAX(score) = 0
但不确定如何编写查询的用户百分比。
我希望这不是太不清楚 - 非常感谢您到目前为止的阅读,任何指示都会很有帮助。
谢谢!
请您参考如下方法:
我认为您需要分两步完成此操作。第一步是获取每个用户每天的最高分数:
SELECT CAST(Run_Date AS DATE) AS RunDate,
User_ID,
MAX(Score) AS Score
FROM YourTable
GROUP BY CAST(Run_Date AS DATE), User_ID;
然后你可以把它放在一个子查询中并计算你的百分比:
SELECT RunDate,
COUNT(CASE WHEN Score = 0 THEN 1 END) / SUM(1.0) AS Failed_Percent
FROM ( SELECT CAST(Run_Date AS DATE) AS RunDate,
User_ID,
MAX(Score) AS Score
FROM YourTable
GROUP BY CAST(Run_Date AS DATE), User_ID
) AS t
GROUP BY RunDate;
您也可以在没有子查询的情况下使用 COUNT(DISTINCT)
实现此目的:
SELECT CAST(Run_Date AS DATE) AS RunDate,
1 - (1.0 * COUNT(DISTINCT CASE WHEN Score > 0 THEN User_ID END)
/ COUNT(DISTINCT User_id)) AS Failed_Percent
FROM YourTable
GROUP BY CAST(Run_Date AS DATE);
这确实是在做反逻辑,但是结果是一样的。相关部分是:
COUNT(DISTINCT CASE WHEN Score > 0 THEN User_ID END)
这会得到在任何给定日期成功运行游戏的不同用户总数,然后
COUNT(DISTINCT User_id)
给出在该日期记录的用户总数。前者除以后者得到成功用户的百分比,因此我们需要从 1 中减去它以获得失败的百分比。我将其中一个计数乘以 1.0 以将其隐式转换为小数以避免 integer division
我希望第一个查询更有效率,但我可能错了。