Compare values from one table with average calculation of value with same properties in other table

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?

Answer

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).