Postgresql 在一个查询中对不同的组进行计数

Question

我需要计算每个城市、州和国家的计数。例如，如果我的表 T1 中有以下数据:

name      city  state   country
------------------------------
name1      c1     s1      C1
name2      c1     s1      C1
name3      c2     s1      C1
name4      c3     s1      C1
name5      c4     s2      C1
name6      c5     s2      C1
name7      c5     s3      C1
name8      c12    s12     C2

查询结果应为:

city    state    country   citycount, statecount, countrycount
-------------------------------------------------------------------
c1       s1        C1        2            4             7
c2       s1        C1        1            4             7
c3       s1        C1        1            4             7
c4       s2        C1        1            2             7
c5       s2        C1        1            2             7
c5       s3        C1        1            1             7
c12      s12       C2        1            1             1

如果我进行分组和计数，那么我需要编写 3 个不同的查询。但我想在一个查询中完成它。请帮忙。

Answer 1

您可以使用window functions ，例如，一种解决方案可能是这样的:

 SELECT DISTINCT city
    ,STATE
    ,country
    ,count(*) OVER (PARTITION BY city,STATE,country) AS citycount
    ,count(*) OVER (PARTITION BY STATE,country) AS statecount
    ,count(*) OVER (PARTITION BY country) AS countrycount
FROM T1
ORDER BY city
    ,STATE
    ,country

请看 fiddle here .