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 ...
I think your inner query needs a join condition. (That is not actually in your question)
Your
ORDER BY
clause in the inner query could beABS(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
Difference Between Information_Schema VS Sys Tables in SQL Server
How to Subtract 2 Dates in Oracle to Get the Result in Hour and Minute
Row with Minimum Value of a Column
Default Value of Guid in for a Column in MySQL
Oracle Insert Failure:Not a Valid Month
Does Liquibase Support Dry Run
Two Columns in Subquery in Where Clause
Tool for Importing CSV Files into MySQL Database
How to Use in Clause with Preparedstatement in Postgresql
Insert Record to SQL Table with Identity Column
How to Make Comment Reply Query in MySQL
SQL Listing All Column Names Alphabetically
Use Google Bigquery to Build Histogram Graph
Difference Between Inner Join and Where in Select Join SQL Statement