为每组数据聚合一组结果

Question

我有一份国家名单。每个简短的缩写(简短)对应于一个国家，但是除了以下面列表结尾的国名外，人们还喜欢输入其他变量。

输出

short        nation     Students
   A           A           604
   A        Austria       6707
   A       Österreich     3400
   AFG        Afg           18
   AFG    Afghanistan     1991
   AGL       Angola         16
   AGN      Guinea           2
   AL         Al             5
   AL       Albanien        61
   ARM        Arm            6
   ARM      Armenien        87

因此，正如你所看到的，计算每个国家的学生总数可以得到2到3个结果。因此，很显然，我想到了一个问题，如果可以计算出每一个短短线的和，如下所示

预期产出

short        nation     Students
   A           A           604
   A        Austria       6707
   A       Österreich     3400
   A                     10711 
   AFG        Afg           18
   AFG    Afghanistan     1991
   AFG                    2009
   AGL       Angola         16
   AGN      Guinea           2
   AL         Al             5
   AL       Albanien        61
   AL                       66
   ARM        Arm            6
   ARM      Armenien        87
   ARM                      93

我的代码如下

第1部分

with natctf as (
SELECT  short, 
        nation, 
        cast(Studentcount as varchar(6)) as Studentcount
        FROM (
                SELECT  ctf.shorttext as short, ctf.longtext as nation,
                                count(distinct s.studentid) as Studentcount
                from students s
                    join pupil p on p.id = s.pupilid
                    join pupilnation pn on pn.pupilid = p.id
                    join country ctf on ctf.id = pn.coutnryid

                Group by ctf.shorttext,ctf.longtext
                Order by ctf.shorttext
) t )

第2部分

SELECT short, initcap(nation), Studentcount
FROM natctf

UNION ALL
SELECT null as short, 
       cast(count(nation) as varchar(3)) ||' Nations', 
       cast(SUM(cast(Studentcount as bigint)) as varchar(10)) ||' Students'
FROM natctf

Answer 1

免责声明：这是PostgreSQL版本9.0-9.4的解决方案。对于Postgres 9.5或更高版本，我将使用@LaurenzAlbe的GROUPING SETS解决方案

demo: db<>fiddle

WITH count_nations AS (                                               -- A
    SELECT 
        *, 
        sum(students) OVER (PARTITION BY short) as total              -- B
    FROM nations
)
SELECT short, name, students FROM count_nations                       -- C            
UNION                                                                 -- E
SELECT short, NULL, total FROM count_nations                          -- D

ORDER BY 
    short, 
    name NULLS LAST,                                                  -- F
    students

答：WITH子句使查询更具可读性，因为您不需要编写相同的子查询两次。

B:窗口函数(https://www.postgresql.org/docs/current/static/tutorial-window.html) SUM汇总给定帧中的所有值(这里是short列)。因此，你得到的国家总数作为单独的一栏。

子查询的结果：

short     name          students   total
A         A             604        10711
A         Austria       6707       10711
A         Österreich    3400       10711
AFG       Afg           18         2009
AFG       Afghanistan   1991       2009
AGL       Angola        16         16
AGN       Guinea        2          2
AL        Al            5          66
AL        Albanien      61         66
ARM       Arm           6          93
ARM       Armenien      87         93

C:选择原始列..。

选择没有名字的新列..。

E：UNION两种结果。UNION使结果变得明显，所以每个国家只能得到一行。(UNION ALL不会做不同的事情)

F:排序结果。对于国家行，NULL值应该是最后一个。

结果：

short   name          students
A       A             604
A       Austria       6707
A       Österreich    3400
A                     10711
AFG     Afg           18
AFG     Afghanistan   1991
AFG                   2009
AGL     Angola        16
AGL                   16
AGN     Guinea        2
AGN                   2
AL      Al            5
AL      Albanien      61
AL                    66
ARM     Arm           6
ARM     Armenien      87
ARM                   93

在您的示例中，您只为多个行的国家添加额外的行。例如，对于AGN，您不添加行。如果这是您的意图，那么上面链接的db<>fiddle向您展示了解决方案：

1. 在WITH子句中添加每个窗口帧的行计数器
2. 使用UNION筛选所有国家的row_count > 1子查询

Answer 2

最好的解决方案是使用分组集，这是一种SQL标准特性，对您的用例来说正是正确的：

SELECT ctf.shorttext as short,
       ctf.longtext as nation,
       count(...)
FROM country AS ctf JOIN ...
GROUP BY GROUPING SETS ((ctf.shorttext, ctf.longtext), (ctf.shorttext))
ORDER BY ctf.shorttext, ctf.longtext

Answer 3

UNION ALL是一个查询，其中GROUP BY是短名称和长名称，另一个是只按短名称分组的查询。

SELECT x.short,
       x.nation,
       x.studentcount
       FROM (SELECT ctf.shorttext short,
                    ctf.longtext nation,
                    count(DISTINCT s.studentid) studentcount
                    FROM students s
                         INNER JOIN pupil p
                                    ON p.id = s.pupilid
                         INNER JOIN pupilnation pn
                                    ON pn.pupilid = p.id
                         INNER JOIN country ctf
                                    ON ctf.id = pn.coutnryid
                    GROUP BY ctf.shorttext,
                             ctf.longtext
             UNION ALL
             SELECT ctf.shorttext short,
                    NULL nation,
                    count(DISTINCT s.studentid) studentcount
                    FROM students s
                         INNER JOIN pupil p
                                    ON p.id = s.pupilid
                         INNER JOIN pupilnation pn
                                    ON pn.pupilid = p.id
                         INNER JOIN country ctf
                                    ON ctf.id = pn.coutnryid
                    GROUP BY ctf.shorttext) x
      ORDER BY x.short,
               x.nation NULLS LAST;

注意，按短名称分组的查询的计数不能是计数的总和，其他查询将返回。那是因为不同的计数。如果对一个学生来说，有几个不同的长名字，有一个短名，他们会被计算在长名的每一组中，但在组中只有一次作为短名。