postgresql – 将单独的范围组合成最大可能的连续范围

>无需区分无穷大和开放上限(上限(范围)IS NULL). (你可以用任何一种方式,但这种方式更简单.)

> NULL vs. infinity in PostgreSQL range types

>由于date是离散类型,因此所有范围都具有default [)边界.
Per documentation:

The built-in range types int4range,int8range,and daterange all use a
canonical form that includes the lower bound and excludes the upper
bound; that is,[).

对于其他类型(如tsrange！),如果可能,我会强制执行相同的操作：

> Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL

纯SQL解决方案

为了清楚起见,CTE：

WITH a AS (
   SELECT range,COALESCE(lower(range),'-infinity') AS startdate,max(COALESCE(upper(range),'infinity')) OVER (ORDER BY range) AS enddate
   FROM   test
   ),b AS (
   SELECT *,lag(enddate) OVER (ORDER BY range) < startdate OR NULL AS step
   FROM   a
   ),c AS (
   SELECT *,count(step) OVER (ORDER BY range) AS grp
   FROM   b
   )
SELECT daterange(min(startdate),max(enddate)) AS range
FROM   c
GROUP  BY grp
ORDER  BY 1;

或者,与子查询相同,更快但不太容易阅读：

SELECT daterange(min(startdate),max(enddate)) AS range
FROM  (
   SELECT *,count(step) OVER (ORDER BY range) AS grp
   FROM  (
      SELECT *,lag(enddate) OVER (ORDER BY range) < startdate OR NULL AS step
      FROM  (
         SELECT range,'infinity')) OVER (ORDER BY range) AS enddate
         FROM   test
         ) a
      ) b
   ) c
GROUP  BY grp
ORDER  BY 1;

或者使用少一个子查询级别,但翻转排序顺序：

SELECT daterange(min(COALESCE(lower(range),'-infinity')),count(nextstart > enddate OR NULL) OVER (ORDER BY range DESC NULLS LAST) AS grp
   FROM  (
      SELECT range,'infinity')) OVER (ORDER BY range) AS enddate,lead(lower(range)) OVER (ORDER BY range) As nextstart
      FROM   test
      ) a
   ) b
GROUP  BY grp
ORDER  BY 1;

>使用ORDER BY范围DESC NULLS LAST(使用NULLS LAST)在第二步中对窗口进行排序,以获得完全颠倒的排序顺序.这应该更便宜(更容易生成,完全匹配建议索引的排序顺序),并且对于具有等级IS NULL的角落情况是准确的.

> PostgreSQL sort by datetime asc,null first?

说明

a：按范围排序时,使用窗口函数计算上限(enddate)的运行最大值.
用/ – 无穷大替换NULL边界(无界)只是为了简化(没有特殊的NULL情况).

b：在相同的排序顺序中,如果前一个enddate早于startdate,我们有一个间隙并开始一个新的范围(步骤).
请记住,上限始终被排除在外.

c：通过使用另一个窗口函数计算步骤来形成组(grp).

在外部SELECT构建范围从每个组的下限到上限.瞧.
关于SO的密切相关答案有更多解释：

> Compare multiple date ranges

用plpgsql编写程序解决方案

适用于任何表/列名称,但仅适用于类型daterange.
带循环的程序解决方案通常较慢,但在这种特殊情况下,我希望功能大大加快,因为它只需要一次顺序扫描：

CREATE OR REPLACE FUNCTION f_range_agg(_tbl text,_col text)
  RETURNS SETOF daterange AS
$func$
DECLARE
   _lower     date;
   _upper     date;
   _enddate   date;
   _startdate date;
BEGIN
   FOR _lower,_upper IN EXECUTE
      format($$SELECT COALESCE(lower(t.%2$I),'-infinity')  -- replace NULL with ...,COALESCE(upper(t.%2$I),'infinity')  -- ... +/- infinity
               FROM   %1$I t
               ORDER  BY t.%2$I$$,_tbl,_col)
   LOOP
      IF _lower > _enddate THEN     -- return previous range
         RETURN NEXT daterange(_startdate,_enddate);
         SELECT _lower,_upper  INTO _startdate,_enddate;

      ELSIF _upper > _enddate THEN  -- expand range
         _enddate := _upper;

      -- do nothing if _upper <= _enddate (range already included) ...

      ELSIF _enddate IS NULL THEN   -- init 1st round
         SELECT _lower,_enddate;
      END IF;
   END LOOP;

   IF FOUND THEN                    -- return last row
      RETURN NEXT daterange(_startdate,_enddate);
   END IF;
END
$func$ LANGUAGE plpgsql;

呼叫：

SELECT * FROM f_range_agg('test','range');  -- table and column name

逻辑类似于SQL解决方案,但我们可以通过单次传递来完成.

SQL Fiddle.

有关：

> GROUP BY and aggregate sequential numeric values

在动态SQL中处理用户输入的常用练习：

> SQL injection in Postgres functions vs prepared queries

指数

对于这些解决方案中的每一个,范围上的普通(默认)btree索引将有助于大表中的性能：

CREATE INDEX foo on test (range);

A btree index is of limited use for range types,但我们可以获得预先排序的数据,甚至可以进行仅索引扫描.