SQL 计算组内的百分比
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1823599/
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 percentage within a group
提问by Jon
given a table that for the following commands:
给出一个表，用于以下命令：
select sex, count(*) from my_table group by sex;
select sex, employed, count(*) from my_table group by sex, employed;
gives:
给出：
sex | count
-------+------
male | 1960
female | 1801
and:
和：
sex | employed | count
---------+----------+-------
male | f | 1523
male | t | 437
female | f | 1491
female | t | 310
I'm having a difficulty writing a query that will calculate percentage of employed within each sex group. So the output should look like this:
我在编写一个查询来计算每个性别组内的就业百分比时遇到了困难。所以输出应该是这样的：
sex | employed | count | percent
---------+----------+--------+-----------
male | f | 1523 | 77.7% (1523/1960)
male | t | 437 | 22.3% (437/1960)
female | f | 1491 | 82.8% (1491/1801)
female | t | 310 | 17.2% (310/1801)
采纳答案by outis
You can do it with a sub-select and a join:
您可以使用子选择和连接来完成：
SELECT t1.sex, employed, count(*) AS `count`, count(*) / t2.total AS percent
FROM my_table AS t1
JOIN (
SELECT sex, count(*) AS total
FROM my_table
GROUP BY sex
) AS t2
ON t1.sex = t2.sex
GROUP BY t1.sex, employed;
I can't think of other approaches off the top of my head.
我想不出其他方法。
回答by Jan Pravda
May be too late, but for upcoming searchers, possible solution could be:
可能为时已晚，但对于即将到来的搜索者，可能的解决方案可能是：
select sex, employed, COUNT(*) / CAST( SUM(count(*)) over (partition by sex) as float)
from my_table
group by sex, employed
By IO Statistics this seems to be most effective solution - may be dependant on number of rows to be queried - tested on numbers above ...
通过 IO Statistics 这似乎是最有效的解决方案 - 可能取决于要查询的行数 - 在上面的数字上进行测试......
The same attitude could be used for getting male / female percentage:
相同的态度可用于获得男性/女性百分比：
select sex, COUNT(*) / CAST( SUM(count(*)) over () as float)
from my_table
group by sex
Regards, Jan
问候， 简
