Cardinality of a multirange? How many ranges or gaps are there in a multirange?
1
vote
1
answer
107
views
How do I obtain the cardinality of a multirange?
I'm using
range_agg()
to aggregate ranges, which merges ranges if they are continuous, or returns what looks like an array of ranges if the ranges are discontinuous.
I want to detect if there are any gaps in the multirange and wanted to use cardinality()
for this. A cardinality of 1 would mean no gaps, a cardinality of 2 means 1 gap, etc.
However cardinality is not defined on a multirange, because a multirange is not technically an array:
with x(a) as (
values
(daterange(date '2024-01-30', date '2024-03-31', '[)')),
(daterange(date '2024-04-01', date '2024-04-30', '[)'))
)
select cardinality(range_agg(a)) from x;
returns
ERROR: function cardinality(datemultirange) does not exist
LINE 6: select cardinality(range_agg(a)) from x;
I can probably implement the function myself with unnest()
and counting the number of resulting rows, but I'm curious if there are any other good solutions.
Asked by Colin 't Hart
(9455 rep)
Sep 26, 2024, 07:42 AM
Last activity: Sep 26, 2024, 11:28 AM
Last activity: Sep 26, 2024, 11:28 AM