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

Thomas Munro

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

Reply via email to