First post here... I'm trying to create select values from tableA in last 5 minutes and to compare those values each row by row with average(value) from another table.
For example:
tableA
id | time_stmp | name | location | value
-----------------------------------------------------
1 2019-08-09-13.20 name1 loc1 450
2 2019-08-09-13.20 name2 loc2 550
3 2019-08-09-13.20 name3 loc3 480
4 2019-08-09-13.19 name4 loc4 420
5 2019-08-09-13.19 name5 loc5 50
tableB is basicaly the same, only values and time_stmp are different
tableB
id | time_stmp | name | location | value
-----------------------------------------------------
1 |2019-08-09-11.20 | name1 | loc1 | 475
2 |2019-08-09-11.00 | name2 | loc2 | 525
3 |2019-08-09-10.40 | name3 | loc3 | 435
4 |2019-08-09-10.20 | name4 | loc4 | 256
5 |2019-08-09-10.00 | name5 | loc5 | 420
Now, I want to create select statement where I'll compare fields (values) from tableA in last 3 minutes with average value of tableB where name and location are same.
I tried something like:
select
ta.name,
ta.location,
ta.value from tableA ta
where ta.value < (select avg(value) from tableB tb
inner join tableB tb on tb.name=ta.name and tb.location=ta.location)
but didn't quite worked...
Output should be only values from tableA where value is less then average value of complete second table.
Any suggestion?
If I understand correctly:
select a.*
from table a
where a.time_stamp > current_timestamp - 5 minute and
a.value < (select avg(b.value)
from tableB b
where b.name = a.name and b.location = a.location
);
For performance, you want indexes on tableB(name, location, value) and tableA(time_stamp).