sql - 按组起点的差异分组

Question

我在 Postgres 数据库表中有很多测量值，当某些值离当前组的“起始”点太远时，我需要将这个集合分成几组(超过一些阈值).排序顺序由 id 列决定。

示例:使用 threshold = 1 拆分:

id measurements
---------------
1  1.5
2  1.4
3  1.8
4  2.6
5  3.7
6  3.5
7  3.0
8  2.6
9  2.5
10 2.8

应按如下方式分组:

id measurements group
---------------------
1  1.5            0     --- start new group 
2  1.4            0
3  1.8            0

4  2.6            1     --- start new group because it too far from 1.5

5  3.7            2     --- start new group because it too far from 2.6
6  3.5            2
7  3.0            2

8  2.6            3     --- start new group because it too far from 3.7
9  2.5            3
10 2.8            3

我可以通过使用 LOOP 编写一个函数来做到这一点，但我正在寻找一种更有效的方法。性能非常重要，因为实际表包含数百万行。

是否可以通过使用PARTITION OVER、CTE 或任何其他类型的SELECT 来实现目标？

Answer 1

Is it possible to achieve the goal by using PARTITION OVER, CTE or any other kind of 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, grp
FROM   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

db<> fiddle here

我的钱在程序功能上。
通常，基于集合的解决方案更快。但在解决固有的程序性问题时则不然。

相关:

• GROUP BY and aggregate sequential numeric values