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
                       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

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to