SQL Query to Join Two Tables Based Off Closest Timestamp

SQL Query to Join Two Tables Based Off Closest Timestamp

If you can make changes to the table structures, I recommend changing the classification table to include an end date as well as a start date - it will be much easier to join to the table that way.

If not, I suggest the following:

SELECT case.id, case.resolution, case.timestamp, class.value
FROM closed_cases AS case
LEFT JOIN (select c.*,
(select min(timestamp)
from classifications c1
where c1.timestamp > c.timestamp) timeend
from classifications c) AS class
ON case.timestamp >= class.timestamp and
(case.timestamp < class.timeend or class.timeend IS NULL)
WHERE case.timestamp BETWEEN $1 AND $2;

EDIT - with the end date on classification:

SELECT case.id, case.resolution, case.timestamp, class.value
FROM closed_cases AS case
LEFT JOIN classifications AS class
ON case.timestamp >= class.timestamp and case.timestamp < class.timeend
WHERE case.timestamp BETWEEN $1 AND $2;

SQL Query to Join Two Tables Based On Closest Timestamp

It would be helpful if you posted your table structures, but ...

  1. I think your inner query needs a join condition. (That is not actually in your question)

  2. Your ORDER BY clause in the inner query could be ABS(TIMESTAMP - DB0.BAL.TIME). That should give you the smallest difference between the 2.

Does that help ?

Based on the follwing Sql Fiddle http://sqlfiddle.com/#!3/7a900/15 I came up with ...

SELECT 
bal.uid,
bal.userId,
bal.balance,
bal.time,
trn.timestamp,
trn.description,
datediff(ms, bal.time, trn.timestamp)
FROM
money_balances bal
JOIN money_transaction trn on
trn.userid = bal.userid and
trn.uid =
(
select top 1 uid
from money_transaction trn2
where trn2.userid = trn.userid
order by abs(datediff(ms, bal.time, trn2.timestamp))
)
WHERE
bal.time IS NOT NULL
ORDER BY
bal.time DESC

I cannot vouch for its performance because I know nothing of your data, but I believe it works.

I have simplified my answer - I believe what you need is

SELECT 
bal.uid as baluid,
(
select top 1 uid
from money_transaction trn2
where trn2.userid = bal.userid
order by abs(datediff(ms, bal.time, trn2.timestamp))
) as tranuid
FROM
money_balances bal

and from that you can derive all the datasets you need.
for example :

with matched_credits as
(
SELECT
bal.uid as baluid,
(
select top 1 uid
from money_transaction trn2
where trn2.userid = bal.userid
order by abs(datediff(ms, bal.time, trn2.timestamp))
) as tranuid
FROM
money_balances bal
)
select
*
from
matched_credits mc
join money_balances mb on
mb.uid = mc.baluid
join money_transaction trn on
trn.uid = mc.tranuid

Joining tables based on closest times

One method is a correlated subquery:

select a.*,
(select b.clickid
from b
where b.username = a.username and
b.timestamp <= a.timestamp
order by b.timestamp desc
limit 1
) as clickid
from a;

You can also do this with window functions:

select a.*, b.*
from a left join
(select b.*,
lead(timestamp) over (partition by username) as next_timestamp
from b
) b
on b.username = a.username and
a.timestamp >= b.timestamp and
(a.timestamp < b.next_timestamp or b.next_timestamp is null);

This is handy if you want more than one column.

Joining on closest timestamp? Presto SQL

Answered my own question. It was as simple as first adding some offset such as a LEAD() between each minute sample and then using a BETWEEN in the join between the tables on the current minute sample looking ahead 59 seconds. Such that:

WITH tbl1 AS (

SELECT
*
FROM table_1

),

tbl2 AS (

SELECT
*,
LEAD(epoch_time) OVER (
PARTITION BY
name,
home
ORDER BY
epoch_time
) - 1 AS next_time
FROM table_2
)

SELECT
t1.Id,
t1.Name,
t2.Home,
t1.epoch_time
FROM tbl1 t1
LEFT JOIN tbl2 t2
ON t1.Id = t2.Id
AND t1.epoch_time BETWEEN t2.epoch_time AND t2.next_time

Join of two tables in prestoDB based on closest value

You could do join of both tables based on item_id and then select only the elements that are closest in time, for example:

select purchase_date, 
user_id,
item_id,
min_by(price, abs(purchase_date-price_date)) as price
from timeseries join price using(item_id)
group by purchase_date, user_id, item_id

See the docs for the min_by function here: https://prestodb.io/docs/current/functions/aggregate.html#min_by

I'm purposefully placing the price table to the right of the join since it's the smallest of the two. Ideally though, when a user makes a purchase you would keep a pointer to a unique identifier in the price table.



Related Topics



Leave a reply



Submit