PostgreSQL非精确GROUP BY聚合：按阈值分组求均值与计数

一、窗口函数实现方案（子查询/CTE方式） #

这是处理这类传递性聚类分组的常规方案，通过窗口函数标记新组起始、生成组ID后完成聚合：

WITH grouped_rows AS (
  SELECT
    value,
    -- 标记当前行是否为新组起始：与前一行value差超过阈值则触发新组
    SUM(CASE WHEN value - LAG(value, 1, value) OVER (ORDER BY value) > 1.5 THEN 1 ELSE 0 END) 
      OVER (ORDER BY value) AS group_id
  FROM foo
)
SELECT
  ROUND(AVG(value), 2) AS avg_value,
  COUNT(*) AS row_count
FROM grouped_rows
GROUP BY group_id
ORDER BY avg_value;

逻辑说明： #

• LAG(value, 1, value)：获取当前行的前一行value，第一行用自身值避免NULL
• 若当前行与前一行的value差超过阈值1.5，标记为1（新组开始），否则为0
• SUM(...) OVER (ORDER BY value)：累加标记值生成唯一组ID，同一聚类组的行ID一致
• 按组ID聚合后得到每组的均值和行数

执行结果：

avg_value | row_count
-----------+-----------
       2.0 |         4
       7.0 |         4

二、近似无分子查询的分桶方案（仅适用于特定场景） #

严格来说，传递性聚类无法完全脱离子查询/CTE，因为组划分依赖相邻行的比较。但如果数据的组间间隔明显大于阈值、组内数值范围稳定，可以用固定分桶表达式直接GROUP BY：

SELECT
  ROUND(AVG(value), 2) AS avg_value,
  COUNT(*) AS row_count
FROM foo
GROUP BY FLOOR((value - 0.5) / 3) -- 3为阈值的2倍，0.5为偏移量，适配当前示例数据
ORDER BY avg_value;

注意： #

该方案是硬编码分桶规则，仅适配当前示例数据。若后续出现4这类处于组边缘的值，会错误划分分组，不具备通用性。

三、递归CTE实现方案（可选） #

如果需要更灵活处理复杂的连通分量分组，可使用递归CTE：

WITH RECURSIVE clusters AS (
  -- 初始化：每行作为独立初始簇
  SELECT id, value, id AS cluster_id FROM foo
  UNION ALL
  -- 合并簇：将与当前簇内value差<=1.5的行合并到同一簇
  SELECT c.id, c.value, cl.cluster_id
  FROM clusters c
  JOIN foo cl ON ABS(c.value - cl.value) <= 1.5 AND c.cluster_id != cl.id
)
SELECT
  ROUND(AVG(value), 2) AS avg_value,
  COUNT(DISTINCT id) AS row_count -- 去重避免递归导致的重复统计
FROM clusters
GROUP BY cluster_id
ORDER BY avg_value;

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