Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.