PostgreSQL按时间分组统计行数：年分组行数不足时自动按月分组的实现

嘿，这个需求挺实用的——既要按年份聚合数据，又要在年分组的行数达不到指定阈值时自动降级到月份粒度，PostgreSQL里有几种优雅的写法可以实现，我来给你拆解一下：

方法一：先预计算年份统计量，再关联分组 #

这种思路先通过CTE算出每个年份的总条数，再关联原表动态选择分组粒度，逻辑清晰易懂：

-- 替换your_table为你的表名，3为指定阈值
WITH yearly_summary AS (
    SELECT
        EXTRACT(YEAR FROM time) AS year_num,
        COUNT(*) AS yearly_row_count
    FROM your_table
    GROUP BY year_num
)
SELECT
    -- 根据年份总条数选择分组键：够阈值用年，不够用月
    CASE
        WHEN ys.yearly_row_count >= 3 THEN DATE_TRUNC('year', t.time)::date
        ELSE DATE_TRUNC('month', t.time)::date
    END AS group_key,
    COUNT(*) AS row_count
FROM your_table t
JOIN yearly_summary ys ON EXTRACT(YEAR FROM t.time) = ys.year_num
GROUP BY group_key
ORDER BY group_key;

逻辑解释： #

1. yearly_summary CTE先统计每个年份的总行数，拿到每个年分组是否满足阈值的判断依据；
2. 关联原表后，用CASE语句动态决定分组键：如果年份总条数≥3，就把时间截断到年份（用当年1月1日作为分组标识），否则截断到月份；
3. 最后按生成的分组键聚合统计行数，排序后得到结果。

方法二：用窗口函数直接标记，更紧凑 #

如果不想用CTE和JOIN，可以用窗口函数给每一行标记它所在年份的总条数，然后直接外层分组，写法更简洁：

-- 替换your_table为你的表名，3为指定阈值
SELECT
    CASE
        WHEN sub.yearly_total >= 3 THEN TO_CHAR(time, 'YYYY')
        ELSE TO_CHAR(time, 'YYYY-MM')
    END AS group_key,
    COUNT(*) AS row_count
FROM (
    SELECT
        time,
        -- 窗口函数：统计当前行所在年份的总行数
        COUNT(*) OVER (PARTITION BY EXTRACT(YEAR FROM time)) AS yearly_total
    FROM your_table
) sub
GROUP BY group_key
ORDER BY group_key;

逻辑解释： #

1. 子查询sub里，用COUNT(*) OVER (PARTITION BY 年)给每一行添加上它所属年份的总条数；
2. 外层根据这个yearly_total判断分组粒度：够阈值就展示年份字符串（比如"2005"），不够就展示年月字符串（比如"2005-02"）；
3. 最后按分组键聚合统计，结果更直观易读。

额外优化点 #

• 如果阈值需要灵活调整，可以把它定义成变量：

  SET threshold = 3;
  -- 之后用current_setting('threshold')::int替代代码里的3即可
  

• 分组键的展示形式可以自定义：比如用DATE_TRUNC得到的日期，或者用TO_CHAR格式化的字符串，根据你的需求选择就行。

举个实际例子：假设你的表中有这些数据：

2005-02-18、2005-03-20、2006-01-05、2006-04-10、2006-07-15、2006-09-22

阈值设为3的话，最终结果会是：

因为2005年只有2条数据（小于阈值3），所以按月分组；2006年有4条（≥3），按年分组。

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