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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
