Following up on my previous question, I have created a table for storing measurements of different physical quantities. The schema looks like this:
CREATE TABLE sensorreadings ( time TIMESTAMP, subject TEXT, quantity TEXT, value NUMERIC, PRIMARY KEY (time, subject, quantity));
It now turns out that for some set of quantities (which happen to be usually measured simultaneously) the client code needs to see a more tabular view with one row per timestamp and subject, and one column for each of the quantities of interest.
I'm minded to create a view for that, but what would be the most optimal way of doing that? My best attempt is the following:
SELECT DISTINCT sr.time, sr.subject, sensorA.Value AS A, sensorB.Value AS B FROM sensorreadings AS sr LEFT OUTER JOIN sensorreadings AS sensorA ON sensorA.time == sr.time AND sr.subject == sensorA.subject AND sensorA.quantity == "Sensor A" LEFT OUTER JOIN sensorreadings AS sensorB ON sensorB.time == sr.time AND sensorB.subject == sr.subject AND sensorB.quantity == "Sensor B"
(well, not quite. I don't store the full name of the sensors, only a key into a table of possible sensor names. Ditto for the subject column.)