使用 SQL 聚合和计算统计数据

Question

我有一款射击游戏，用户在一周的时间内相互竞争以积累最多的分数。我想编写一个查询来聚合镜头表中的统计数据。这里关注的表和关系是：

• user has much opportunity_competition_period
• 属于用户
• co​​mpetition_period有很多镜头
• shot属于competition_period

在shots表中，我有以下字段可以使用：

• result -->;字符串值：WON、LOST 或 TIED
• amount_won -->整数值：例如-100、0、2000等。

对于每个用户，我想返回具有以下聚合统计信息的结果集：

• won_countlost_counttied_counttotal_shots_count
• total_amount_won
• （ amount_won
• （won_count+lost_count+tied_count）
• 的总和）
• avg_amount_won_per_shot（ Total_amount_won /total_shots_count)

我已经工作了这个查询已经有几个小时了，但还没有取得多大进展。统计功能让我很困惑。一位朋友建议我尝试将结果返回到一个名为 shot_records 的新虚拟表中。

Answer 1

这是基本的解决方案，计算给定球员的所有击球的统计数据（您没有指定是否希望在每个比赛周期的基础上进行统计）：

 SELECT user, SUM(IF(result = 'WON', 1, 0))  AS won_count, 
              SUM(IF(result = 'LOST', 1, 0)) AS lost_count, 
              SUM(IF(result = 'TIED', 1, 0)) AS tied_count, 
              COUNT(*)                       AS total_shots_count, 
              SUM(amount_won)                AS total_amount_won, 
              (SUM(amount_won) / COUNT(*))   AS avg_amount_won_per_shot 
 FROM user U INNER JOIN competition_periods C ON U.user_id = C.user_id
 INNER JOIN shots S ON C.competition_period_id = S.competition_period_id
 GROUP BY user

请注意，这包括计算“总胜利”时的负数数字（即总数减去损失）。如果这不是您游戏的正确算法，您可以将两个地方的 SUM(Amount) 更改为 SUM(IF(Amount > 0, Amount, 0))出现在查询中。