不含子查询的PostgreSQL中总计的百分比 [英] Percent to total in PostgreSQL without subquery

问题描述

我与用户有一张桌子。每个用户都有一个国家。我想要的是获取所有国家/地区的列表，其中包括用户数量和百分比/总数。到目前为止，我所拥有的是：

I have a table with users. Each user has a country. What I want is to get the list of all countries with the numbers of users and the percent/total. What I have so far is:

SELECT
country_id,
COUNT(*) AS total,
((COUNT(*) * 100) / (SELECT COUNT(*) FROM users WHERE cond1 = true AND cond2 = true AND cond3 = true)::decimal) AS percent
FROM users
WHERE cond1 = true AND cond2 = true AND cond3 = true
GROUP BY contry_id

两个查询都相同。我尝试在没有子查询的情况下执行此操作，但无法获得用户总数，但无法获得每个国家/地区的总数。有没有子查询的方法吗？我正在使用PostgreSQL。非常感谢您的帮助。
预先感谢

Conditions in both of queries are the same. I tried to do this without a subquery but then I can't get the total number of users but total per country. Is there a way to do this without a subquery? I'm using PostgreSQL. Any help is highly appreciated. Thanks in advance

推荐答案

我不是PostgreSQL用户，但是一般的解决方案是使用窗口函数。

I am not a PostgreSQL user but, the general solution would be to use window functions.

在 http://developer.postgresql.org/pgdocs/postgres/tutorial-window.html

我可以用来描述它的最佳解释是：基本上，它允许您在一个字段上执行分组依据，而无需使用group by子句。

Best explanation i could use to describe it is: basically it allows you to do a group by on one field without the group by clause.

我相信这可以解决问题：

I believe this might do the trick:

SELECT 
    country_id, 
    COUNT(*) OVER (country_id) 
    ((COUNT(*) OVER (country_id)) * 100) / COUNT(*) OVER () )::decimal) as percent
FROM 
    users
WHERE
    cond1 = true AND cond2 = true AND cond3 = true

这篇关于不含子查询的PostgreSQL中总计的百分比的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！