PostgreSQL 11分组统计flag计数并获取每组首行time值

实现说明 #

PostgreSQL 11 环境下，要同时完成分组条件计数、总计数、取分组首行指定字段的需求，核心是不能直接在GROUP BY子句后裸写非分组字段time，必须通过聚合函数明确定义分组内time的取值规则，且必须指定排序逻辑——数据库物理存储顺序不可靠，不指定排序取到的"第一条"结果完全随机，不具备业务意义。
以下提供两种可直接落地的实现，默认"第一条记录"定义为分组内time值最小（最早生成）的记录，可根据业务需要调整ORDER BY后的排序字段和顺序。 #

方案1：单表扫描实现（性能最优，推荐数据量较大场景使用） #

通过条件聚合完成计数，用array_agg按指定规则排序后直接取第一个元素作为分组首行time，仅需扫描一次业务表，执行效率最高。

SELECT
    filters,
    -- 统计flag=0的记录数
    COUNT(CASE WHEN flag = 0 THEN 1 END) AS total_0,
    -- 统计flag=1的记录数
    COUNT(CASE WHEN flag = 1 THEN 1 END) AS total_1,
    -- 统计分组总记录数
    COUNT(*) AS total_0_and_1,
    -- 按time升序排序后取第一个值，即分组最早的time
    (array_agg(time ORDER BY time ASC))[1] AS time
FROM your_business_table
-- 如需指定统计的用户范围，可在此加过滤条件，例如 WHERE username = 'target_user'
GROUP BY filters;

方案2：窗口函数标记实现（逻辑灵活，推荐需取首行多字段场景使用） #

通过CTE+ROW_NUMBER()窗口函数先给每个分组内的记录按排序规则打行号，再聚合时取行号为1的记录对应的time值，逻辑直观，扩展方便，如果后续需要同时取分组首行的username等其他字段，不需要修改排序逻辑直接加字段即可。

WITH record_with_rank AS (
    SELECT
        filters,
        flag,
        time,
        -- 按filters分区，按指定规则排序，行号1即为分组第一条
        ROW_NUMBER() OVER (PARTITION BY filters ORDER BY time ASC) AS rn
    FROM your_business_table
    -- 可在此加全局过滤条件，缩小统计范围
)
SELECT
    filters,
    COUNT(CASE WHEN flag = 0 THEN 1 END) AS total_0,
    COUNT(CASE WHEN flag = 1 THEN 1 END) AS total_1,
    COUNT(*) AS total_0_and_1,
    -- 取行号为1的记录对应的time
    MAX(CASE WHEN rn = 1 THEN time END) AS time
FROM record_with_rank
GROUP BY filters;

注意事项 #

• 所有ORDER BY time ASC的部分可根据业务对"第一条记录"的定义调整：如果要取分组内最新生成的记录，改成ORDER BY time DESC即可；如果需要多字段排序优先级，直接在后面追加字段，例如ORDER BY time ASC, username ASC。
• 条件计数逻辑中不需要额外写ELSE NULL，CASE语句不满足条件时默认返回NULL，COUNT会自动忽略NULL值，仅统计符合条件的记录。

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