PostgreSQL分组统计状态数量并获取组内任意child_id

现有如下数据表：

type | child_id  | status
-----------------------------------
1    | a         | completed
1    | b         | failed
1    | a         | completed
1    | c         | completed
-----------------------------------
2    | a         | failed
2    | b         | failed

需求是按type字段分组，统计不同status的数量，同时获取该组内任意一个child_id，预期结果如下：

type | child_id  | completed   | failed
-----------------------------------------
1    | b         | 3           | 1
2    | a         | 0           | 2

• 对于type 1，child_id为a、b、c均可；type 2则a或b均可。

目前已写出如下SQL语句，可获取除child_id外的所有字段：

SELECT type, 
   sum(case when status  = 'completed' then 1 else 0 end) as completed,
   sum(case when  status  = 'failed' then 1 else 0 end) as failed
 from mytable
 group by type;

想请教是否可以不用连接查询，就能在结果中加入child_id字段。

当然可以不用连接查询，直接在现有聚合查询中添加聚合函数获取组内任意child_id即可，以下是几种常用方法：

方法1：用MIN()/MAX()取极值 #

这两个函数会返回组内child_id的最小或最大值，完全符合“任意一个”的要求：

SELECT type,
       MIN(child_id) AS child_id, -- 换成MAX(child_id)也可以
       SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed,
       SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) AS failed
FROM mytable
GROUP BY type;

方法2：用ANY_VALUE()（MySQL专属） #

MySQL专门提供了ANY_VALUE()函数，用于分组查询中直接提取组内任意一个非聚合字段的值，完美匹配需求：

SELECT type,
       ANY_VALUE(child_id) AS child_id,
       SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed,
       SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) AS failed
FROM mytable
GROUP BY type;

方法3：用窗口函数（适用于MySQL 8+、PostgreSQL、SQL Server等） #

如果想指定取组内某一行的child_id（比如第一行），可以用窗口函数实现，同样不需要连接：

SELECT DISTINCT
       type,
       FIRST_VALUE(child_id) OVER (PARTITION BY type) AS child_id,
       SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) OVER (PARTITION BY type) AS completed,
       SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) OVER (PARTITION BY type) AS failed
FROM mytable;

以上方法都无需额外连接操作，直接在原查询基础上扩展就能得到目标结果。

内容的提问来源于stack exchange，提问作者fractal5