CODEWITHSUNDEEP
mysqlsql
user3658800
user3658800

Reputation: 456

How to execute a query on an unrelated table for every row in SQL

Let's say I have a table like this that tracks the balance of an asset I have in an account:

Delta NetBalance Timestamp
2 2 2020-01-01 00:00:00.000
4 6 2020-01-02 00:00:00.000
-1 5 2020-01-03 00:00:00.000

Let's say I have another unrelated table that keeps of track of pricing for my asset:

Price Timestamp
1.00 2020-01-01 00:00:00.000
1.02 2020-01-01 23:59:00.000
2.01 2020-01-02 10:00:00.000
2.02 2020-01-02 18:00:00.000
3.01 2020-01-03 12:00:00.000
3.02 2020-01-03 13:59:00.000

I'm looking for a query that will yield a result set with the columns from the first table, plus the closest price (from the exact moment, or the past) from the second table and its associated timestamp, so, something like this:

Delta NetBalance Timestamp MostRecentPrice MostRecentPriceTimestamp
2 2 2020-01-01 00:00:00.000 1.00 2020-01-01 00:00:00.000
4 6 2020-01-02 00:00:00.000 1.02 2020-01-01 23:59:00.000
-1 5 2020-01-03 00:00:00.000 2.02 2020-01-02 18:00:00.000

Working with MySQL here. Would prefer to avoid things like cross joins because the tables themselves are pretty huge, but open to suggestions.

Upvotes: 0

Views: 65

Answers (1)

D-Shih
D-Shih

Reputation: 46229

You can try to use LAG window function get previous Timestamp from account then do join with unrelated table.

Then use ROW_NUMBER window function to get MostRecent data rows.

SELECT *
FROM (
    SELECT *,
           row_number() OVER(PARTITION BY MONTH(Timestamp),DAY(Timestamp) ORDER BY MostRecentPriceTimestamp DESC) rn
    FROM (
      SELECT a.Delta,
            a.NetBalance,   
            a.Timestamp,
            u.Timestamp MostRecentPriceTimestamp,
            u.Price MostRecentPrice
      FROM (
        SELECT *,LAG(Timestamp,1,Timestamp) OVER(ORDER BY Timestamp) prev_Timestamp
        FROM account a
      ) a
      INNER JOIN unrelated u
      ON u.Timestamp BETWEEN a.prev_Timestamp AND a.Timestamp 
    ) t1
) t1
WHERE rn = 1

sqlfiddle

Upvotes: 1

Related Questions