Hi. Thanks so much for your assistance. This is definitely getting the results I was looking for. It is still syntacticallly more cumbersome than I might have hoped, but I can work with it. So I've got two follow questions/issues:
which is what is it exactly that is triggering Postgresql to know there is a discontinuity and to start with a new range? And is it based on the input or the output values? Based on PARTITION BY client_id ORDER BY d, I might have thought it was d. But that doesn't seem to be right. So is it something about what agg_daterange is returning, and if so what? Again, sorry for being dense about this one.
1) I can see there are many, more complex, options for aggregates, which I am trying to wrap my mind around. I'm wondering if any of these (esp. partial aggregates/combine functions, final functions or moving aggregates) could be used to streamline this into a single function call, or if that is barking up a dead tree.
2) I'm sure at this point I must be being dense, but after re-reading docs multiple times, I am still confused about one piece of this:
> first window (within lower() func) extends its initial (earliest) range
> to right for every next row continuous with current range (and jumps to
> new start on discontinuity), thus over(order by d ASC)
> second window (within upper() func) extends its initial (latest) range
> to left for every next row continuous with current range (and jumps to
> new end on discontinuity), thus over(order by d DESC)
> partition by client_id within each over() is to not mix client_ids'
> dateranges
>
Thanks!
Ken
--
![]()
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
