Re: [HACKERS] ASOF join

On Mon, Jun 19, 2017 at 11:57 PM, Konstantin Knizhnik
<k.knizhnik@postgrespro.ru> wrote:
> 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

Hmm.  Yeah, I see the notational problem.  It's hard to come up with a
new syntax that has SQL nature.  What if... we didn't use a new syntax
at all, but recognised existing queries that are executable with this
strategy?  Queries like this:

WITH ticks(time, price) AS      (VALUES ('2017-07-20 12:00:00'::timestamptz, 100.00),              ('2017-07-21
11:00:00'::timestamptz,150.00)),    times(time) AS      (VALUES ('2017-07-19 12:00:00'::timestamptz),
('2017-07-2012:00:00'::timestamptz),              ('2017-07-21 12:00:00'::timestamptz),              ('2017-07-22
12:00:00'::timestamptz))

SELECT times.time, previous_tick.price FROM times LEFT JOIN LATERAL (SELECT * FROM ticks                     WHERE
ticks.time<= times.time                     ORDER BY ticks.time DESC LIMIT 1) previous_tick ON trueORDER BY
times.time;
         time          | price
------------------------+--------2017-07-19 12:00:00+12 |2017-07-20 12:00:00+12 | 100.002017-07-21 12:00:00+12 |
150.002017-07-2212:00:00+12 | 150.00
 
(4 rows)

I haven't used LATERAL much myself but I've noticed that it's often
used to express this type of thing.  "Get me the latest ... as of time
...".

It'd a bit like the way we recognise EXISTS (...) as a semi-join and
execute it with a join operator instead of having a SEMI JOIN syntax.
On the other hand it's a bit more long winded, extreme and probably
quite niche.

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