postgresql GROUP BY聚合不精确

k4aesqcs 于 2023-08-04 发布在 PostgreSQL

关注(0)|答案(1)|浏览(163)

我在想一种方法来推理一个相当简单的问题：
表foo有两列，id有唯一标识符，value有随机的numeric值。给定一个常数threshold值，找出value在该阈值内的值组-该组的计数和该组的平均值。
下面是foo表（在PostgreSQL中）：

CREATE TABLE foo (
    id serial PRIMARY KEY,
    value numeric NOT NULL
);

字符串
查询应该返回计数和平均值-条目 * 不能 * 被重复计数，所以我希望行基于一些阈值“接近度”值被“切割成组”-你可以把threshold值看作半径：

SELECT AVG(foo.value), COUNT(foo.id)
  FROM foo
  GROUP BY foo.value; -- where I'm stuck

型
上面的查询只会将聚合函数应用到foo.value有exact重复的行-我想要的是类似于以下伪SQL的东西：

SELECT AVG(foo.value), COUNT(foo.id)
  FROM foo
  GROUP BY
   (row_being_grouped.value <= foo.value + threshold)
   AND
   (row_being_grouped.value >= foo.value - threshold);

型
我不确定这是否有意义。我想知道我是否可以在不做子查询的情况下摆脱这种情况-也许在找到桶内的平均值之前以某种方式“桶”行？

postgresql

来源：https://stackoverflow.com/questions/76829648/inexact-group-by-aggregation

1条答案

按热度按时间

zwghvu4y1#

如果“阈值”可以理解为同一组的值之间允许的最大间隙，那么这是一个定义明确的gaps-and-islands问题。

下面是一个解决方案：

SELECT grp, count(*) AS grp_count, round(avg(value), 2) AS grp_avg
FROM  (
   SELECT count(gap) OVER (ORDER BY value) AS grp, *
   FROM  (
      SELECT value
           , value - lag(value) OVER (ORDER BY value) > 150 OR null AS gap
      FROM   foo
      ) sub1
   ) sub2
GROUP  BY grp
ORDER  BY grp;

字符串
fiddle（带分步演示）
解释和链接到更多：

如何将时间戳分组为岛（基于任意间隔）？

为了方便和简短的代码，我使用布尔逻辑（true OR null → true，false OR null → null），以及count()忽略null值的事实。请参阅：

For absolute performance, is SUM faster or COUNT?的
如果这更多的是一个光栅/集群/颗粒/网格问题，你需要准确地定义哪些行选择为“焦点”，或者，独立光栅/网格的确切性质。

赞(0）回复(0）举报 2023-08-04

我来回答

postgresql GROUP BY聚合不精确

1条答案

相关问题

热门标签

最新问答