On Thu, Feb 12, 2009 at 09:06:41AM +0100, Paolo Saudin wrote:
> I have 14 tables filled with meteorological data, one record
> per parameter per hour. The id field holds the parameter type
> (1=temperature, 2=humidity ...) My problem is that for short periods
> (maybe one week, one month) there are two stations with the same data,
> I mean the temperature of table1 is equal to the humidity of table3. I
> need to discover those cases.
I'm assuming that it's one table per sensor station below.
Would something like this work:
SELECT station, fulldate,
COUNT(CASE WHEN sensor = 1 THEN 1 END) AS num_sensor1_readings,
COUNT(CASE WHEN sensor = 2 THEN 1 END) AS num_sensor2_readings,
COUNT(CASE WHEN sensor = 3 THEN 1 END) AS num_sensor3_readings
FROM (
SELECT 1 AS station, fulldate, id AS sensor FROM table1 UNION ALL
SELECT 2, fulldate, id FROM table2 UNION ALL
SELECT 3, fulldate, id FROM table 3) x
GROUP BY station, fulldate
ORDER BY station, fulldate;
That way you'll get a list of all the duplicate values. If the
"fulldate" column is the actual time it was received and isn't
truncated off to the nearest hour, you will probably want to use
date_trunc('hour',fulldate) in the outer select.
Hope that helps!
--
Sam http://samason.me.uk/
