Re: Given a set of daterange, finding the continuous range thatincludes a particular date (aggregates)

Поиск

Список

Период

Сортировка

От	Ken Tanzer
Тема	Re: Given a set of daterange, finding the continuous range thatincludes a particular date (aggregates)
Дата	28 февраля 2018 г. 06:15:14
Msg-id	CAD3a31Ucyvdy=quqeZockSuYF209UfiRPz9hjzX-2wuhFebDdQ@mail.gmail.com обсуждение исходный текст
Ответ на	Re: Given a set of daterange, finding the continuous range thatincludes a particular date (aggregates) (mariusz <marius@mtvk.pl>)
Ответы	Re: Given a set of daterange, finding the continuous range thatincludes a particular date (aggregates) (mariusz <marius@mtvk.pl>)
Список	pgsql-general

Дерево обсуждения

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:

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
>

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.

Thanks!

Ken