> > On Fri, Feb 23, 2018 at 6:10 AM, mariusz <mar...@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). > > Thank you, and I wanted to follow up on this. I couldn't quite get your example working as described, but I also ended up trying something very similar that got me very close but not quite there. Basically, I can see that it is correctly calculating the ranges (in the notices), but it is only returning the last range for each client. (Because I said PARTITION BY client_id). So I'm not sure if I should be calling this differently, or if the function needs to work differently, or if this just isn't possible. Do I need to partition by something else, and if so what? I don't see what I could specify that would indicate a new episode. Also, it's not clear to me how an aggregate might define/return different values within a partition. Although this must be possible, since functions like rank() and row_number() seem to do it. Hoping there is something easy that can be tweaked here. See below for copy/pastable test stuff. It includes output from both functions. Both look to be returning the same results, which makes me wonder if my passing in a start date was a waste of time, though it seems to me it would be necessary. Cheers, Ken BEGIN; CREATE TEMP TABLE sample_data ( client_id INTEGER, start_date DATE, end_date DATE, episode INTEGER -- Just a label, for clarity ); INSERT INTO sample_data VALUES (1,'1990-01-01','1990-12-31',1), (1,'1991-01-01','1991-12-31',1), (1,'2000-01-01','2000-12-31',2), (1,'2001-01-01','2001-12-31',2), (1,'2002-01-01','2002-12-31',2), (1,'2003-01-01','2003-12-31',2), (1,'2004-01-01','2004-12-31',2), (1,'2005-01-01','2005-12-31',2), (1,'2006-01-01','2006-12-31',2), (1,'2014-01-01','2014-12-31',3), (1,'2015-01-01','2015-12-31',3), (1,'2017-06-30','2017-12-31',4), (1,'2018-01-01',NULL,4), (2,'2014-02-01','2015-01-31',1), (2,'2015-02-01','2015-12-31',1), (2,'2017-09-30','2018-01-31',2), (2,'2018-02-01','2018-02-14',2) ; 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 ); CREATE OR REPLACE FUNCTION range_continuous_merge( daterange, daterange, date ) RETURNS daterange AS $$ DECLARE res daterange; BEGIN res:= CASE WHEN $1 IS NULL AND NOT $2 @> $3 THEN NULL WHEN $1 IS NULL AND $2 @> $3 THEN $2 WHEN ($1 && $2) OR ($1 -|- $2) THEN $1 + $2 WHEN NOT $1 @> $3 THEN $2 ELSE $1 END; RAISE NOTICE 'Inputs: %,%,%. Returning %',$1::text,$2::text,$3::text,res; RETURN res; END; $$ LANGUAGE plpgsql STABLE; CREATE AGGREGATE range_continuous( daterange, date ) ( sfunc = range_continuous_merge, stype = daterange -- initcond = '{0,0,0}' ); SELECT client_id,episode,start_date,end_date,range_continuous(daterange(start_date,end_date,'[]'),start_date) OVER (PARTITION by client_id) FROM sample_data ; SELECT client_id,episode,start_date,end_date,agg_daterange(daterange(start_date,end_date,'[]')) OVER (PARTITION by client_id) FROM sample_data ; -- RANGE_CONTINUOUS_MERGE NOTICE: Inputs: <NULL>,[1990-01-01,1991-01-01),1990-01-01. Returning [1990-01-01,1991-01-01) NOTICE: Inputs: [1990-01-01,1991-01-01),[1991-01-01,1992-01-01),1991-01-01. Returning [1990-01-01,1992-01-01) NOTICE: Inputs: [1990-01-01,1992-01-01),[2000-01-01,2001-01-01),2000-01-01. Returning [2000-01-01,2001-01-01) NOTICE: Inputs: [2000-01-01,2001-01-01),[2001-01-01,2002-01-01),2001-01-01. Returning [2000-01-01,2002-01-01) NOTICE: Inputs: [2000-01-01,2002-01-01),[2002-01-01,2003-01-01),2002-01-01. Returning [2000-01-01,2003-01-01) NOTICE: Inputs: [2000-01-01,2003-01-01),[2003-01-01,2004-01-01),2003-01-01. Returning [2000-01-01,2004-01-01) NOTICE: Inputs: [2000-01-01,2004-01-01),[2004-01-01,2005-01-01),2004-01-01. Returning [2000-01-01,2005-01-01) NOTICE: Inputs: [2000-01-01,2005-01-01),[2005-01-01,2006-01-01),2005-01-01. Returning [2000-01-01,2006-01-01) NOTICE: Inputs: [2000-01-01,2006-01-01),[2006-01-01,2007-01-01),2006-01-01. Returning [2000-01-01,2007-01-01) NOTICE: Inputs: [2000-01-01,2007-01-01),[2014-01-01,2015-01-01),2014-01-01. Returning [2014-01-01,2015-01-01) NOTICE: Inputs: [2014-01-01,2015-01-01),[2015-01-01,2016-01-01),2015-01-01. Returning [2014-01-01,2016-01-01) NOTICE: Inputs: [2014-01-01,2016-01-01),[2017-06-30,2018-01-01),2017-06-30. Returning [2017-06-30,2018-01-01) NOTICE: Inputs: [2017-06-30,2018-01-01),[2018-01-01,),2018-01-01. Returning [2017-06-30,) NOTICE: Inputs: <NULL>,[2014-02-01,2015-02-01),2014-02-01. Returning [2014-02-01,2015-02-01) NOTICE: Inputs: [2014-02-01,2015-02-01),[2015-02-01,2016-01-01),2015-02-01. Returning [2014-02-01,2016-01-01) NOTICE: Inputs: [2014-02-01,2016-01-01),[2017-09-30,2018-02-01),2017-09-30. Returning [2017-09-30,2018-02-01) NOTICE: Inputs: [2017-09-30,2018-02-01),[2018-02-01,2018-02-15),2018-02-01. Returning [2017-09-30,2018-02-15) client_id | episode | start_date | end_date | range_continuous -----------+---------+------------+------------+------------------------- 1 | 1 | 1990-01-01 | 1990-12-31 | [2017-06-30,) 1 | 1 | 1991-01-01 | 1991-12-31 | [2017-06-30,) 1 | 2 | 2000-01-01 | 2000-12-31 | [2017-06-30,) 1 | 2 | 2001-01-01 | 2001-12-31 | [2017-06-30,) 1 | 2 | 2002-01-01 | 2002-12-31 | [2017-06-30,) 1 | 2 | 2003-01-01 | 2003-12-31 | [2017-06-30,) 1 | 2 | 2004-01-01 | 2004-12-31 | [2017-06-30,) 1 | 2 | 2005-01-01 | 2005-12-31 | [2017-06-30,) 1 | 2 | 2006-01-01 | 2006-12-31 | [2017-06-30,) 1 | 3 | 2014-01-01 | 2014-12-31 | [2017-06-30,) 1 | 3 | 2015-01-01 | 2015-12-31 | [2017-06-30,) 1 | 4 | 2017-06-30 | 2017-12-31 | [2017-06-30,) 1 | 4 | 2018-01-01 | | [2017-06-30,) 2 | 1 | 2014-02-01 | 2015-01-31 | [2017-09-30,2018-02-15) 2 | 1 | 2015-02-01 | 2015-12-31 | [2017-09-30,2018-02-15) 2 | 2 | 2017-09-30 | 2018-01-31 | [2017-09-30,2018-02-15) 2 | 2 | 2018-02-01 | 2018-02-14 | [2017-09-30,2018-02-15) (17 rows) -- AGG_DATERANGE client_id | episode | start_date | end_date | agg_daterange -----------+---------+------------+------------+------------------------- 1 | 1 | 1990-01-01 | 1990-12-31 | [2017-06-30,) 1 | 1 | 1991-01-01 | 1991-12-31 | [2017-06-30,) 1 | 2 | 2000-01-01 | 2000-12-31 | [2017-06-30,) 1 | 2 | 2001-01-01 | 2001-12-31 | [2017-06-30,) 1 | 2 | 2002-01-01 | 2002-12-31 | [2017-06-30,) 1 | 2 | 2003-01-01 | 2003-12-31 | [2017-06-30,) 1 | 2 | 2004-01-01 | 2004-12-31 | [2017-06-30,) 1 | 2 | 2005-01-01 | 2005-12-31 | [2017-06-30,) 1 | 2 | 2006-01-01 | 2006-12-31 | [2017-06-30,) 1 | 3 | 2014-01-01 | 2014-12-31 | [2017-06-30,) 1 | 3 | 2015-01-01 | 2015-12-31 | [2017-06-30,) 1 | 4 | 2017-06-30 | 2017-12-31 | [2017-06-30,) 1 | 4 | 2018-01-01 | | [2017-06-30,) 2 | 1 | 2014-02-01 | 2015-01-31 | [2017-09-30,2018-02-15) 2 | 1 | 2015-02-01 | 2015-12-31 | [2017-09-30,2018-02-15) 2 | 2 | 2017-09-30 | 2018-01-31 | [2017-09-30,2018-02-15) 2 | 2 | 2018-02-01 | 2018-02-14 | [2017-09-30,2018-02-15) (17 rows) -- AGENCY Software A Free Software data system By and for non-profits *http://agency-software.org/ <http://agency-software.org/>* *https://demo.agency-software.org/client <https://demo.agency-software.org/client>* ken.tan...@agency-software.org (253) 245-3801 Subscribe to the mailing list <agency-general-requ...@lists.sourceforge.net?body=subscribe> to learn more about AGENCY or follow the discussion.