Skip to content
Advertisement

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?

Advertisement

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

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement