Re: ASOF join

On Mon, Jun 19, 2017 at 11:57 PM, Konstantin Knizhnik
<k(dot)knizhnik(at)postgrespro(dot)ru> wrote:
> On 16.06.2017 19:07, David Fetter wrote:
>> If you turn your head sideways, it's very similar to the range merge
>> join Jeff Davis proposed. https://commitfest.postgresql.org/14/1106/
>
> May be, but I do not understand how to limit result to contain exactly one
> (last) inner tuple for each outer tuple.

Yeah, it's somehow related but it's not the same thing. I guess you
can think of the keys in the ASOF case as modelling range starts, with
range ends implied by the record with next higher/lower key.
Concretely, if every 'tick' row from my example in a nearby message
had a time but also an end time to be set when a new tick is inserted
so that each tick row had the complete effective time range for that
tick, then you could rewrite the query as "get me the tick whose
effective time range overlaps with each value of times.time" and get a
nice range merge join with Jeff's patch. That sort of thing might be
very useful for SQL:2011 temporal query-style stuff, but it's not what
Konstantin wants to do: he wants to merge time series where the
effective range is not explicitly stored in every row.

--
Thomas Munro
http://www.enterprisedb.com