将聚合除以列上的和

hgc7kmma 于 2021-07-29 发布在 Java

关注(0)|答案(2)|浏览(300)

以下假设为博士后。
假设我有下表：

CREATE TABLE object (
  object_id       SERIAL  PRIMARY KEY
  object_category integer NOT NULL CHECK(value BETWEEN 1 AND 5)
);

我可以使用以下公式计算每个类别中的对象数：

SELECT object_category,
       count(object_category) number_of_objects,
  FROM object
 GROUP BY object_category;

，但如何计算每个类别中的对象数与对象总数的比率？例如，如果上述查询返回以下结果集：

object_category | number_of_objects
-----------------------------------
              1 |                 5
              2 |                 3
              3 |                 7
              4 |                 2
              5 |                10

，如何返回：

object_category | object_ratio
------------------------------
              1 |        0.185
              2 |        0.111
              3 |        0.259
              4 |        0.074
              5 |        0.370

我已经尝试将上面的和作为子查询来计算，但无法访问 number_of_objects 因为postgres抱怨缺少group by子句，我不太明白。有什么帮助吗？

sql postgresql

来源：https://stackoverflow.com/questions/62308192/divide-aggregate-over-sum-over-columns

2条答案

按热度按时间

x0fgdtte1#

只需使用窗口功能：

SELECT object_category, COUNT(*), COUNT(*) * 1.0 / SUM(COUNT(*)) OVER ()
FROM object
GROUP BY object_category;

赞(0）回复(0）举报 2021-07-29

bf1o4zei2#

可以使用窗口函数。

WITH OBJECTT
     AS (SELECT
               1 OBJECT_CATEGORY 
         UNION ALL
         SELECT
               1 OBJECT_CATEGORY 
         UNION ALL
         SELECT
               1 OBJECT_CATEGORY 
         UNION ALL
         SELECT
               1 OBJECT_CATEGORY 
         UNION ALL
         SELECT
               1 OBJECT_CATEGORY 
         UNION ALL
         SELECT
               1 OBJECT_CATEGORY 
         UNION ALL
         SELECT
               5 OBJECT_CATEGORY 
         UNION ALL
         SELECT
               5 OBJECT_CATEGORY 
         UNION ALL
         SELECT
               4 OBJECT_CATEGORY 
         UNION ALL
         SELECT
               4 OBJECT_CATEGORY 
         UNION ALL
         SELECT
               4 OBJECT_CATEGORY 
         UNION ALL
         SELECT
               2 OBJECT_CATEGORY 
         UNION ALL
         SELECT
               3 OBJECT_CATEGORY 
         UNION ALL
         SELECT
               2 OBJECT_CATEGORY 
         UNION ALL
         SELECT
               6 OBJECT_CATEGORY )
SELECT DISTINCT
       OBJECT_CATEGORY,
       COUNT (*) OVER (PARTITION BY OBJECT_CATEGORY) / COUNT (*) OVER () AS RATIO
  FROM OBJECTT

赞(0）回复(0）举报 2021-07-29

我来回答

将聚合除以列上的和

2条答案

相关问题

热门标签

最新问答