对于我一直在努力编写的 SQL 查询,我非常感谢帮助。

背景:

每次用户玩游戏时,都会在表 game_runs 中创建一条记录,以及他们的 user_idrun_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; 

Example on SQL Fiddle

您也可以在没有子查询的情况下使用 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); 

Example on SQL Fiddle

这确实是在做反逻辑,但是结果是一样的。相关部分是:

COUNT(DISTINCT CASE WHEN Score > 0 THEN User_ID END) 

这会得到在任何给定日期成功运行游戏的不同用户总数,然后

COUNT(DISTINCT User_id)  

给出在该日期记录的用户总数。前者除以后者得到成功用户的百分比,因此我们需要从 1 中减去它以获得失败的百分比。我将其中一个计数乘以 1.0 以将其隐式转换为小数以避免 integer division

我希望第一个查询更有效率,但我可能错了。


评论关闭
IT序号网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!