如何在Postgres中通过单个组获取多个列

Question

我有一个表，看起来像下面的

Table "public.test_systems"
Column            |          Type          | Modifiers 
-----------------------------+------------------------+-----------
rid                         | integer                | not null
r_osname                    | character varying(255) | 
r_health                    | integer                | 
r_patch                     | bigint                 | 
r_loc                       | character varying(255) | 

表中的每一行都描述了一个系统。现在，如果我想通过唯一的操作系统名称来了解有多少系统，我会进行下面的查询

select r_osname, count(*) as total_systems from test_systems group by r_osname;

所以我得到的结果如下

r_osname                   | total_systems 
-----------------------------------------------+--------------
Ubuntu 18.04.4 LTS                            |           18
Windows 10 Pro                                |            2
CentOS Linux                                  |            1
Windows Server 2019                           |            3
Mac OS X - High Sierra                        |            2

现在，我想对多个列运行相同的查询。换句话说，我想用一个groupby得到多个列。但是Postgres迫使我也提到groupby中的附加列。

我在下面的中尝试了distinct on

select distinct on (r_osname) test_systems.* from test_systems order by os_name;

我得到了相同数量的行(部分成功(，但无法获得作为附加列的count(*)。

最终结果可能如下所示(包括r_health和r_loc等附加列(

r_osname                   |           r_health                |   r_loc            | total_systems 
-----------------------------------------------+-----------------------------------+--------------------+--------------
Ubuntu 18.04.4 LTS                            | 1012                              | NYC                |            18
Windows 10 Pro                                | 1121                              | LON                |             2
CentOS Linux                                  | 1255                              | DEL                |             1
Windows Server 2019                           | 1451                              | HYD                |             3
Mac OS X - High Sierra                        | 1120                              | LA                 |             2

如何获得预期结果？

Answer 1

您需要一个窗口函数来实现这一点：

SELECT DISTINCT 
r_osname, r_health, r_loc,
count(*) OVER (PARTITION BY r_osname, r_health, r_loc)
FROM test_systems

根据要包含在结果中的列中的值的组合，可以使用DISTINCT ON (...)子句。如果没有DISTINCT子句，您将获得与表中的行数一样多的行(在您的示例中为26(，但如果每个操作系统只需要一行，则应该使用DISTINCT ON (r_osname)。将返回的行取决于ORDER BY子句-如果没有给定，则将为具有相同r_osname的每组行返回第一行，但无法预测将是哪一行。