I have two tables. And I want to check if a value from one, falls between any two numbers in the other table. For example the tables are:
x
table_a:
+--------+-------+---------------------+
| name | count | time |
+--------+-------+---------------------+
| table1 | 10 | 2019-05-03 10:30:00 |
| table1 | 20 | 2019-05-03 11:30:00 |
| table1 | 30 | 2019-05-03 12:30:00 |
| table1 | 40 | 2019-05-03 13:30:00 |
| table1 | 50 | 2019-05-03 14:30:00 |
+--------+-------+---------------------+
table_b:
+--------+-------+
| name | count |
+--------+-------+
| table1 | 35 |
+--------+-------+
From this I want to get the closest value above and below the number in table_b that exists in table_a, and then show this as a “match” and then also display the times of the closest value above and below this number. So the results should look something similar to:
Result:
+--------+--------------+---------------------+---------------------+
| name | count | time_before | time_after |
+--------+--------------+---------------------+---------------------+
| table1 | Counts Match | 2019-05-03 12:30:00 | 2019-05-03 13:30:00 |
+--------+--------------+---------------------+---------------------+
If this doesn’t match, then it will show this in the results as not matching and the times will just be blank.
Thanks
Advertisement
Answer
You can do this by generating a table of before
and after
values for count
and time
in table_a
, and then JOIN
ing that to table_b
such that table_b.count
is between count_before
and count_after
:
SELECT a.name,
a.count_before || ',' || a.count_after AS count,
a.time_before,
a.time_after
FROM (SELECT name,
lag(count) over (order by time) AS count_before,
count AS count_after,
lag(time) over (order by time) AS time_before,
time AS time_after
FROM table_a) a
JOIN table_b b ON b.count BETWEEN a.count_before AND a.count_after
Output:
name count time_before time_after
table1 30,40 2019-05-03T12:30:00.000Z 2019-05-03T13:30:00.000Z