SQL 使用 GROUP BY 查询计算百分比
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6207224/
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
Calculating percentages with GROUP BY query
提问by DeadMonkey
I have a table with 3 columns which looks like this:
我有一个包含 3 列的表，如下所示：
File User Rating (1-5)
------------------------------
00001 1 3
00002 1 4
00003 2 2
00004 3 5
00005 4 3
00005 3 2
00006 2 3
Etc.
I want to generate a query that outputs the following (for each user and rating, display the number of files as well as percentage of files):
我想生成一个输出以下内容的查询（对于每个用户和评级，显示文件数量以及文件百分比）：
User Rating Count Percentage
-----------------------------------
1 1 3 .18
1 2 6 .35
1 3 8 .47
2 5 12 .75
2 3 4 .25
With Postgresql, I know how to create a query that includes the first 3 columns using the following query, but I can't figure out how to calculate percentage within the GROUP BY:
使用 Postgresql，我知道如何使用以下查询创建包含前 3 列的查询，但我不知道如何计算 GROUP BY 中的百分比：
SELECT
User,
Rating,
Count(*)
FROM
Results
GROUP BY
User, Rating
ORDER BY
User, Rating
Here I want the percentage calculation to apply to each user/rating group.
在这里，我希望百分比计算适用于每个用户/评级组。
采纳答案by Andrew Lazarus
WITH t1 AS
(SELECT User, Rating, Count(*) AS n
FROM your_table
GROUP BY User, Rating)
SELECT User, Rating, n,
(0.0+n)/(COUNT(*) OVER (PARTITION BY User)) -- no integer divide!
FROM t1;
Or
或者
SELECT User, Rating, Count(*) OVER w_user_rating AS n,
(0.0+Count(*) OVER w_user_rating)/(Count(*) OVER (PARTITION BY User)) AS pct
FROM your_table
WINDOW w_user_rating AS (PARTITION BY User, Rating);
I would see if one of these or the other yields a better query plan with the appropriate tool for your RDBMS.
我会看看这些或其他中的一个是否会使用适合您的 RDBMS 的工具产生更好的查询计划。
回答by Nicholas Carey
Alternatively, you can do the old-school way — arguably easier to grok:
或者，您可以采用老式的方式——可以说更容易理解：
select usr.User as User ,
usr.Rating as Rating ,
usr.N as N ,
(100.0 * item.N) / total.N as Pct
from ( select User, Rating , count(*) as N
from Results
group by User , Rating
) usr
join ( select User , count(*) as N
from Results
group by User
) total on total.User = usr.User
order by usr.User, usr.Rating
Cheers!
干杯!
回答by mikerobi
The best way to do this would be with window functions.
最好的方法是使用窗口函数。
回答by user3060544
WITH data AS
(SELECT User, Rating, Count(*) AS Count
FROM Results
GROUP BY User, Rating)
SELECT User, Rating, Count,
(0.0+n)/(SUM(Count) OVER (PARTITION BY User))
FROM data;
回答by James Holland
In TSQL this should work
在 TSQL 这应该工作
SELECT
User,
Rating,
Count(*), SUM(COUNT(*)) OVER (PARTITION BY User, Rating ORDER BY User, Rating) AS Total,
Count(*)/(SUM(COUNT(*)) OVER (PARTITION BY User, Rating ORDER BY User, Rating)) AS Percentage
FROM
Results
GROUP BY
User, Rating
ORDER BY
User, Rating
