在PostgreSQL中计算百分比而不使用子查询

Question

我有一个用户表格，每个用户都有一个国家。我想要得到所有国家的用户数量列表，并且显示百分比/总数。目前我所拥有的是：

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数据库。非常感谢您的帮助。

- fanjabi

Answer 1

这段内容很古老，但是上面的两个选择示例要么不起作用，要么过于复杂。

SELECT
    country_id,
    COUNT(*),
    (COUNT(*) / (SUM(COUNT(*)) OVER() )) * 100
FROM
    users
WHERE
    cond1 = true AND cond2 = true AND cond3 = true
GROUP BY 
    country_id

第二个计数不是必要的，它只是为了调试，以确保您获得正确的结果。诀窍在于记录集上计数的总和。

希望这能帮助有需要的人。

另外，如果有人想在Django中做到这一点，只需修改聚合函数即可。

class PercentageOverRecordCount(Aggregate):
    function = 'OVER'
    template = '(COUNT(*) / (SUM(COUNT(*)) OVER() )) * 100'

    def __init__(self, expression, **extra):
        super().__init__(
            expression,
            output_field=DecimalField(),
            **extra
        )

现在它可以用于注释。

- Trent

---

你的代码很棒，我在OVER()部分添加了PARTITION BY来决定要计算哪个百分比。 - AbdulRahman Awad

Answer 2

我猜你想消除子查询的原因是为了避免两次扫描用户表。请记住，总数是每个国家计数的总和。

WITH c AS (
  SELECT
    country_id,
    count(*) AS cnt
  FROM users
  WHERE cond1=...
  GROUP BY country_id
) 
SELECT
  *,
  100.0 * cnt / (SELECT sum(cnt) FROM c) AS percent
FROM c;

这个查询使用一个小的CTE获取每个国家的统计信息。它只扫描一次用户表，并生成一个小的结果集（每个国家仅有一行）。

总和（SELECT sum(cnt) FROM c）仅在该小型结果集上计算一次，因此它所需的时间可以忽略不计。

您也可以使用窗口函数：

SELECT
  country_id,
  cnt,
  100.0 * cnt / (sum(cnt) OVER ()) AS percent 
FROM (
  SELECT country_id, count(*) as cnt from users group by country_id
) foo;

这与Nightwolf的查询相同，只是删除了错误 lol。

这两个查询所需时间大致相同。

- bobflux

---

嗯...我遇到了一个错误：`ERROR: 语法错误，附近出现 "WITH c" LINE 1: WITH c AS (SELECT ^********** 错误 **********ERROR: 语法错误，附近出现 "WITH c" SQL 状态: 42601 字符: 1` - fanjabi

您需要版本8.4以使用CTE（常用表达式）和窗口函数... - bobflux

我没有意识到外部查询没有进行分组。 运行得很好！ - François Beausoleil

Answer 3

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

请阅读http://developer.postgresql.org/pgdocs/postgres/tutorial-window.html了解如何使用它。

我能用来描述它的最好解释是：基本上它允许你在没有group by子句的情况下对一个字段进行分组。

我相信这可能会起作用：

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

- Nightwolf

---

实际上，这个查询将为用户表中的每一行产生1个输出行，因此您确实需要使用GROUP BY。请参见我的答案。 - bobflux

@peufeu：我以前从未编写过窗口函数，也没有测试过。看起来我需要更多地阅读语法方面的资料。 - Nightwolf

是的，想象一下像RANK() OVER(PARTITION BY something)这样的东西，它为每行提供1个值（分区中的排名，例如每场比赛中运动员的排名）；窗口函数允许您做非常强大的事情，比如访问按顺序排列的前/后行，但它们不进行任何分组。在这种情况下，count(*) over ()将简单地在所有行中重复。 - bobflux

Answer 4

使用最新的PostgreSQL版本，查询可以如下所示：

CREATE TABLE users (
    id serial,
    country_id int
);

INSERT INTO users (country_id) VALUES (1),(1),(1),(2),(2),(3);

select distinct
    country_id,
    round(
        ((COUNT(*) OVER (partition by country_id )) * 100)::numeric 
        / COUNT(*) OVER () 
    , 2) as percent
from users 
order by country_id
;

在 SQLize.online 上的结果

+============+=========+
| country_id | percent |
+============+=========+
| 1          | 50.00   |
+------------+---------+
| 2          | 33.33   |
+------------+---------+
| 3          | 16.67   |
+------------+---------+

- Slava Rozhnev