Re: ASOF join

On 16.06.2017 19:07, David Fetter wrote:
> On Fri, Jun 16, 2017 at 11:51:34AM +1200, Thomas Munro wrote:
>> On Fri, Jun 16, 2017 at 4:20 AM, Konstantin Knizhnik
>> <k(dot)knizhnik(at)postgrespro(dot)ru> wrote:
>>> I wonder if there were some discussion/attempts to add ASOF join to Postgres
>>> (sorry, may be there is better term for it, I am refereeing KDB definition:
>>> http://code.kx.com/wiki/Reference/aj ).
>> Interesting idea. Also in Pandas:
>>
>> http://pandas.pydata.org/pandas-docs/version/0.19.0/generated/pandas.merge_asof.html#pandas.merge_asof

I attached simple patch adding ASOF join to Postgres. Right now it
support only outer join and requires USING clause (consequently it is
not possible to join two tables which joi keys has different names. May
be it is also possible to support ON clause with condition written like
o.k1 = i.k2 AND o.k2 = i.k2 AND ... AND o.kN >= i.kN
But such notation can be confusing, because join result includes only
one matching inner record with kN smaller or equal than kN of outer
record and not all such records.
As alternative we can add specia

If people fin such construction really useful, I will continue work on it.

>>
>> I suppose you could write a function that pulls tuples out of a bunch
>> of cursors and zips them together like this, as a kind of hand-coded
>> special merge join "except that we match on nearest key rather than
>> equal keys" (as they put it).
>>
>> I've written code like this before in a trading context, where we
>> called that 'previous tick interpolation', and in a scientific context
>> where other kinds of interpolation were called for (so not really
>> matching a tuple but synthesising one if no exact match). If you view
>> the former case as a kind of degenerate case of interpolation then it
>> doesn't feel like a "join" as we know it, but clearly it is. I had
>> never considered before that such things might belong inside the
>> database as a kind of join operator.
> 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.

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company