sql - 分组与分组起点之间的差异

我在Postgres数据库表中有很多度量值，当某个值离当前组的“起点”太远时（超过某个阈值），我需要将这个集合分组。排序顺序由id列决定。示例：使用threshold = 1拆分：id measurements---------------1 1.52 1.43 1.84 2.65 3.76 3.57 3.08 2.69 2.510 2.8应分组如下：id measurements group---------------------1 1.5 0 --- start new group2 1.4 03 1.8 04 2.6 1 --- start new group because it too far from 1.55 3.7 2 --- start new group because it too far from 2.66 3.5 27 3.0 28 2.6 3 --- start new group because it too far from 3.79 2.5 310 2.8 3我可以通过使用LOOP编写函数来实现这一点，但我正在寻找一种更有效的方法。性能非常重要，因为实际的表包含数百万行。是否可以通过使用PARTITION OVER、CTE或任何其他类型的SELECT来实现目标？ 最佳答案 是否可以通过使用PARTITION OVER、CTE或任何其他类型的SELECT来实现目标？这是一个固有的程序性问题。根据您的起始位置，后面的所有行都可以以不同的组和/或不同的组值结束。Window functions（使用PARTITION子句）对此没有好处。您可以使用recursive CTE：WITH RECURSIVE rcte AS ( ( SELECT id , measurement , measurement - 1 AS grp_min , measurement + 1 AS grp_max , 1 AS grp FROM tbl ORDER BY id LIMIT 1 ) UNION ALL ( SELECT t.id , t.measurement , CASE WHEN t.same_grp THEN r.grp_min ELSE t.measurement - 1 END -- AS grp_min , CASE WHEN t.same_grp THEN r.grp_max ELSE t.measurement + 1 END -- AS grp_max , CASE WHEN t.same_grp THEN r.grp ELSE r.grp + 1 END -- AS grp FROM rcte r CROSS JOIN LATERAL ( SELECT *, t.measurement BETWEEN r.grp_min AND r.grp_max AS same_grp FROM tbl t WHERE t.id > r.id ORDER BY t.id LIMIT 1 ) t ) )SELECT id, measurement, grpFROM rcte;很优雅。而且速度很快。但只有在有效实现时，它的速度才能与在集合上有一个循环的过程语言函数一样快，甚至比它慢：CREATE OR REPLACE FUNCTION f_measurement_groups(_threshold numeric = 1) RETURNS TABLE (id int, grp int, measurement numeric) AS$func$DECLARE _grp_min numeric; _grp_max numeric;BEGIN grp := 0; -- init FOR id, measurement IN SELECT * FROM tbl t ORDER BY t.id LOOP IF measurement BETWEEN _grp_min AND _grp_max THEN RETURN NEXT; ELSE SELECT INTO grp , _grp_min , _grp_max grp + 1, measurement - _threshold, measurement + _threshold; RETURN NEXT; END IF; END LOOP;END$func$ LANGUAGE plpgsql;呼叫：SELECT * FROM f_measurement_groups(); -- optionally supply different threshold分贝小提琴here我的钱在程序上。通常，基于集合的解决方案更快。但在解决固有的程序性问题时就不行了。相关：GROUP BY and aggregate sequential numeric values关于sql - 分组与分组起点之间的差异，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/58561845/