问题:如何从 postgres 分组集查询中删除空值

我想在单个查询中执行多个独立的分组,以便我可以在单个查询中获得不同列的分组计数。我使用以下示例查询实现了这一点:

SELECT  model, count(model), os_version,count(os_version), timezone, count(timezone)
FROM device
GROUP BY
GROUPING SETS(
(model),(os_version),(timezone)
);

查询示例结果如下:

"model" : "model A", "count" : 14, "os_version" : null, "count" : 14, "timezone" : null, "count" : 14
"model" : "model B", "count" : 6, "os_version" : null, "count" : 6, "timezone" : null, "count" : 6
"model" : null, "count" : 12, "os_version" : "Win 10", "count" : 12, "timezone" : null, "count" : 12
"model" : null, "count" : 8, "os_version" : "Mac OS", "count" : 8, "timezone" : null, "count" : 8
"model" : null, "count" : 5, "os_version" : null, "count" : 5, "timezone" : "GMT", "count" : 5
"model" : null, "count" : 15, "os_version" : null, "count" : 15, "timezone" : "EST", "count" : 15

从结果中,您可以看到它首先在model列上分组,然后是os_version,然后是timezone。但是在这个过程中,它还为当前没有发生分组的列引入了空值。

我想知道是否有任何方法可以在不编写任何自定义解析器的情况下获得没有任何空值的结果。例如,我期望的目标结果是:

"model" : "model A","count" : 14,
"model" : "model B", "count" : 6,
"os_version" : "Win 10", "count" : 12,
"os_version" : "Mac OS", "count" : 8,
"timezone" : "GMT", "count" : 5,
"timezone" : "EST", "count" : 15

解答

您可以使用外部查询包装查询:

SELECT CASE WHEN model      IS NOT NULL THEN 'model'
            WHEN os_version IS NOT NULL THEN 'os_version'
            WHEN timezone   IS NOT NULL THEN 'timezone'
       END AS category,
       coalesce(model, os_version, timezone) AS value,
       count
FROM (SELECT model, os_version, timezone, count(*)
      FROM device
      GROUP BY GROUPING SETS ((model),(os_version),(timezone))
     ) AS q;