Re: [I] ASOF join support / Specialize Range Joins [datafusion]

["ozankabak (via GitHub)" <gi...@apache.org>]

ozankabak commented on issue #318:
URL: https://github.com/apache/datafusion/issues/318#issuecomment-2194718023

   @jirislav I'm not sure if you chose your tone intentionally to be this way and I have to say I don't really appreciate it. 
   
   Nevertheless I will try to answer as best as I can. My concern about the `ASOF` join is not specific to a particular implementation -- it is a conceptual one. The pain point is of course real, but one doesn't really need a new, special syntax to solve it.
   
   Consider a scenario where we are computing the most recent values of each position in a stock portfolio (akin to @alamb's example). Let's create some sample data via:
   
   ```sql
   CREATE TABLE holdings (
       ts TIMESTAMP,
       ticker VARCHAR(10),
       amount INT
   );
   
   INSERT INTO holdings (ts, ticker, amount) VALUES
     ('2024-06-27 09:00:00', 'AAPL', '50'),
     ('2024-06-27 09:00:00', 'GOOGL', '30'),
     ('2024-06-27 09:00:00', 'MSFT', '20'),
     ('2024-06-28 09:00:00', 'AAPL', '60'),
     ('2024-06-28 09:00:00', 'GOOGL', '35'),
     ('2024-06-28 09:00:00', 'MSFT', '25');
   
   CREATE TABLE prices (
       ts TIMESTAMP,
       ticker VARCHAR(10),
       price DECIMAL(10, 2)
   );
   
   INSERT INTO prices (ts, ticker, price) VALUES
     ('2024-06-27 09:00:00', 'AAPL', '145.50'),
     ('2024-06-27 09:00:00', 'GOOGL', '2754.36'),
     ('2024-06-27 09:00:00', 'MSFT', '301.22'),
     ('2024-06-28 09:00:00', 'AAPL', '146.20'),
     ('2024-06-28 09:00:00', 'GOOGL', '2770.01'),
     ('2024-06-28 09:00:00', 'MSFT', '305.50');
   ```
   
   Consider the following standard query:
   ```sql
   SELECT h.*, h.amount * LAST_VALUE(p.price ORDER BY p.ts)
   FROM holdings as h, prices as p
   WHERE h.ts >= p.ts AND h.ticker = p.ticker
   GROUP BY h.ts, h.ticker, h.amount
   ORDER BY h.ts, h.ticker
   ```
   
   We get the following (expected) result:
   ```
   +---------------------+--------+--------+----------+
   | ts                  | ticker | amount | value    |
   +---------------------+--------+--------+----------+
   | 2024-06-27T09:00:00 | AAPL   | 50     | 7275.00  |
   | 2024-06-27T09:00:00 | GOOGL  | 30     | 82630.80 |
   | 2024-06-27T09:00:00 | MSFT   | 20     | 6024.40  |
   | 2024-06-28T09:00:00 | AAPL   | 60     | 8772.00  |
   | 2024-06-28T09:00:00 | GOOGL  | 35     | 96950.35 |
   | 2024-06-28T09:00:00 | MSFT   | 25     | 7637.50  |
   +---------------------+--------+--------+----------+
   ```
   
   This solves the problem, it generalizes to arbitrary join conditions, and it already works in `main` branch. The plan one gets from OOTB DataFusion for this query is not very efficient today, but it can be made more efficient. For example, if we specify that the input tables are ordered w.r.t. `ts` (which we already allow today for external tables), the `ORDER BY` clause becomes unnecessary and the query gets even more succinct [1]. We can make versions of join algorithms that will take that data ordering and execute even more efficiently than the standard hash join this query compiles into. The nice thing about DF is that one can make this query efficiently run even in more challenging contexts (like streaming data) by implementing custom join operators. This is what we did at Synnada.
   
   Given that we can solve this problem in a general way without any special syntax, I do not see any real benefit of adding another syntax for an already-solved problem in upstream DF. We are also improving built-in join operators constantly and adding new ones, so our plans will also get ever more efficient over time.
   
   Downstream users are obviously free to customize DF in any way they wish. I hope this helps.
   
   [1] @alamb, writing up this example makes me think it could be a good idea to file ticket to support order specification for memory tables too.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org