How do I break a range into homogeneous sub-ranges in PostgreSQL?

user_5399734 0
Viewed 130

Say I have a table like this

WITH conds(cond) AS (
    SELECT '[3, 5)'::int4range
        UNION
    SELECT '[6, 8)'::int4range
        UNION
    SELECT '[9, 20)'::int4range
)
SELECT cond FROM conds;

For a given input range, I want to break it into homogeneous sub-ranges which either are entirely contained in some row in conds, or do not overlap with any row in conds. There should be an additional column indicating whether each sub-range is covered by conds.

More concretely, for an input period of '[1, 11)'::int4range, the expected output is

  ?column? | ?column?
-----------+----------
 [1,3)     |        f
 [3,5)     |        t
 [5,6)     |        f
 [6,8)     |        t
 [8,9)     |        f
 [9,11)    |        t
(6 rows)

Every two rows in conds are guaranteed to be disjoint, but conds may also be empty (in which case the output is just the input range and f), and each cond may overlap with the bound of the input range (as shown in the example above).

Which query can achieve this? This answer tells me how to handle the case where cond only has one row, but it may contain multiple rows for me.

range postgresql sql
2 Answers

You can use a brute force approach -- expand the desired range into individual elements. Check each of those, and then aggregate back down to ranges:

WITH conds(cond) AS (
    SELECT '[3, 5)'::int4range
        UNION ALL
    SELECT '[6, 8)'::int4range
        UNION ALL
    SELECT '[9, 20)'::int4range
)
SELECT int4range(min(r.val), max(r.val) + 1), flag
FROM (SELECT gs.val, (c.cond IS NULL) as flag,
             ROW_NUMBER() OVER (PARTITION BY c.cond IS NULL ORDER BY gs.val) as seqnum
      FROM (VALUES ('[1, 11)'::int4range)) v(range) CROSS JOIN
           generate_series(lower(v.range), upper(v.range), 1) gs(val) LEFT JOIN
           conds c
           ON gs.val <@ c.cond
     ) r
GROUP BY flag, r.val - seqnum
ORDER BY min(r.val);

Here is a db<>fiddle.

user_11440350

You can also generate the covered and uncovered subranges separately, fusion them together with UNION, and give them the correct order with ORDER BY

WITH conds(cond) AS (
    SELECT '[3, 5)'::int4range
    UNION
    SELECT '[6, 8)'::int4range
    UNION
    SELECT '[9, 20)'::int4range
),
     intersections(subrange) AS (
         SELECT cond * '[1, 11)'::int4range
         FROM conds
         WHERE cond && '[1, 11)'::int4range
     ),
     fusion(s, covered) AS (
         SELECT int4range(LAG(UPPER(subrange), 1, LOWER('[1, 11)'::int4range)) OVER (ORDER BY LOWER(subrange)),
                          LOWER(subrange)),
                false
         FROM intersections
         UNION
         SELECT subrange,
                true
         FROM intersections
     )
SELECT s, covered
FROM fusion
ORDER BY LOWER(s)
user_53997340
Related