How do I solve the following problem:
Imagine we have a large building with about 100 temperature readers and each one collects the temperature every minute.
I have a rather large table (~100m) rows with the following columns:
Table TempEvents:
Timestamp - one entry per minute
Reader ID - about 100 separate readers
Temperature - Integer (-40 -> +40)
Timestamp and Reader ID are primary+secondary keys to the table. I want to perform a query which finds all the timestamps where
reader_01 = 10 degrees,
reader_02 = 15 degrees and
reader_03 = 20 degrees.
In other words something like this:
SELECT Timestamp FROM TempEvents
WHERE (readerID=01 AND temperature=10)
AND (readerID=02 AND temperature=15)
AND (readerID=03 AND temperature=20)
==> Resulting in a list of timestamps:
Timestamp::
2016-01-01 05:45:00
2016-02-01 07:23:00
2016-03-01 11:56:00
2016-04-01 23:21:00
The above query returns nothing since a single row does not include all conditions at once. Using OR in between the conditions is also not producing the desired result since all readers should match the condition.
Using INTERSECT, I can get the result by:
SELECT * FROM
(SELECT Timestamp FROM TempEvents WHERE readerID=01 AND temperature=10
INTERSECT SELECT Timestamp FROM TempEvents WHERE readerID=02 AND temperature=15
INTERSECT SELECT Timestamp FROM TempEvents WHERE readerID=03 AND temperature=20
)
GROUP BY Timestamp ORDER BY Timestamp ASC;
The above query is extremely costly and takes about 5 minutes to execute.
Is there a better (quicker) way to get the result?
Try this:
with Q(readerID,temperature) as(
select 01, 10 from dual
union all
select 02,15 from dual
union all
select 03,20 from dual
)
select Timestamp FROM TempEvents T, Q
where T.readerID=Q.readerID and T.temperature=Q.temperature
group by Timestamp
having count(1)=(select count(1) from Q)
Perhaps this will give a better plan than using OR or IN clause.