Re: [HACKERS] ASOF join

On 21.06.2017 11:00, Thomas Munro wrote:
> 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-20 12: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 true
>   ORDER BY times.time;
>
>            time          | price
> ------------------------+--------
>   2017-07-19 12:00:00+12 |
>   2017-07-20 12:00:00+12 | 100.00
>   2017-07-21 12:00:00+12 | 150.00
>   2017-07-22 12: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.
Thank you for this idea. I agree that it is the best way of implementing 
ASOF join - just as optimization of standard SQL query.
But do you think that still it will be good idea to extend SQL syntax 
with ASOF JOIN ... USING ... clause? It will significantly simplify 
writing queries like above
and IMHO doesn't introduce some confusions with standard SQL syntax. My 
primary idea of suggesting ASOF join for Postgres was not  just building 
more efficient plan (using merge join instead of nested loop) but also 
simplifying writing of such queries. Or do you think that nobody will be 
interested in non-standard SQL extensions?

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