如何在 SQL 中计算比率？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5114401/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I calculate ratios in SQL?
提问by deltanovember
Suppose I have a GAMEtable with the following fields
假设我有一个GAME包含以下字段的表
user_id, result
I wish to calculate win percentage defined as total number of records where
我希望计算定义为记录总数的获胜百分比，其中
result = 'Win'
Compared to total number of records where
与其中的记录总数相比
result <> ''
How can I do this in SQL and have the result returned in order with highest win percentage first?
如何在 SQL 中执行此操作并首先按最高获胜百分比的顺序返回结果？
回答by Thomas
Most database systems should be able to handle this with something akin to:
大多数数据库系统应该能够使用类似于以下内容的方法处理此问题：
Select user_id
, Sum( Case When Result = 'Win' Then 1 Else 0 End ) / Count(*) * 1.0 As WinPercent
From Game
Group By user_id
Order By WinPercent Desc
Btw, I'm assuming you meant to say the win percentage is the total number of wins out of the total number of games. If you really did mean where result <> '', then you would need to modify the query like so:
顺便说一句，我假设您的意思是说获胜百分比是游戏总数中的获胜总数。如果你真的是 mean where result <> ''，那么你需要像这样修改查询：
Select user_id
, Sum( Case When Result = 'Win' Then 1 Else 0 End )
/ Sum( Case When Result <> '' Then 1 Else 0 End ) * 1.0 As WinPercent
From Game
Group By user_id
Order By WinPercent Desc
Addition
添加
It was suggested in comments that this is SQL Server specific. It is not. Everything in the above solutions is part of the SQL standard. Whether a given database system implements all of the features required is obviously another matter. So, if we are switching gears to finding a solution that would actually work on most database systems, the one feature that has the most variance in support would likely be the use of the column alias in the Order By. You can achieve the equivalent in a couple of ways but one way that would work on most database systems would be to use a derived table:
在评论中建议这是特定于 SQL Server 的。它不是。上述解决方案中的所有内容都是 SQL 标准的一部分。一个给定的数据库系统是否实现了所有所需的功能显然是另一回事。因此，如果我们正在转向寻找一种实际上适用于大多数数据库系统的解决方案，那么支持差异最大的一个功能可能是在 Order By 中使用列别名。您可以通过多种方式实现等效的效果，但一种适用于大多数数据库系统的方法是使用派生表：
Select Z.user_id, Z.WinPercent
From (
Select user_id
, Sum( Case When Result = 'Win' Then 1 Else 0 End )
/ Sum( Case When Result <> '' Then 1 Else 0 End ) * 1.0 As WinPercent
From Game
Group By user_id
) As Z
Order By Z.WinPercent Desc
回答by iggy
In MySQL, you can use this shortcut:
在 MySQL 中，您可以使用此快捷方式：
SELECT user_id, avg(Result = 'Win') AS WinPercent
FROM Game
GROUP BY user_id
ORDER BY WinPercent DESC
Assuming Result is never NULL (empty string is ok). Otherwise you have to use Result IS NOT NULL AND Result = 'Win'
假设 Result 永远不会为 NULL（空字符串也可以）。否则你必须使用 Result IS NOT NULL AND Result = 'Win'
