Re: Given a set of daterange, finding the continuous range thatincludes a particular date (aggregates) - Mailing list pgsql-general

On Fri, Feb 23, 2018 at 6:10 AM, mariusz <marius@mtvk.pl> wrote: 

i guess, you can easily get max continuous range for each row with
something like this:

CREATE OR REPLACE FUNCTION
        append_daterange(d1 daterange, d2 daterange)
        RETURNS daterange
        LANGUAGE sql
        AS
$$
        SELECT CASE WHEN d1 && d2 OR d1 -|- d2 THEN d1 + d2 ELSE d2 END;
$$;

CREATE AGGREGATE agg_daterange (daterange) (
        sfunc = append_daterange,
        stype = daterange
);

SELECT dr,
       lower(agg_daterange(dr) OVER (ORDER BY dr ASC)),
       upper(agg_daterange(dr) OVER (ORDER BY dr DESC))
  FROM ...

above example is simplified to selecting only daterange column "dr" for
readability, which in your case should be something like

daterange(staff_assign_date,staff_assign_date_end,'[)')

please note that daterange would be normalized to [) format so upper()
above returns exactly your max "staff_assign_date_end" for each
continuous range when dateranges are created with '[)' format.

the key point is ... ELSE d2 in append_daterange() which starts with new
value each time that new value is discontinuous with agg's state value
and order in which rows are processed (ASC for lower of daterange, DESC
for upper of daterange).

unfortunately this involves reading all rows for "client_id" and
additional sorting for each window.
i recall reading that you already pay the price of reading all rows for
client_id anyway, so the only question is the cost of two additional
sorts (maybe extracting dateranges to subset on which to do windows and
rejoining result of continuous ranges to original set would help to
lower the cost).