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