On Thursday 12 February 2009 11:37:37 am Paolo Saudin wrote:
> >
> > Can be the same data ( and it is ) because of errors in the remote
> > stations configurations.
> > The Stations and parameters IDs were mixed up resulting in same data
> > in different tables ...
> >
> >
> >I am afraid I more confused now. From the table schema the value is a real
> > number only and has no units. As I understand the units >designation lies
> > in the id. If the ids are mixed up I can't see how it is possible to
> > differentiate between a value of 25 that maybe >degrees C or % relative
> > humidity for instance. You are going to have to step me through this.
>
> Yes, the parameter is defined by the id and stored in another table with
> the name, units and other properties. I need to find out a sequence of
> meanvalues (without taking care of ids) which exists in another table
>
> Here is some sample data, I need to found out if some sequence of data in
> table1 is equal to data in table2, table3 ... tableN.
>
> Table1
> fulldate, id, meanvalue
> 2009-01-01 00:00:00, 1, 12.3 -- temperature
> 2009-01-01 01:00:00, 1, 12.5
> 2009-01-01 02:00:00, 1, 12.6
> 2009-01-01 03:00:00, 1, 12.7
> 2009-01-01 04:00:00, 1, 12.8
> 2009-01-01 05:00:00, 1, 12.2
>
> Table1
> fulldate, id, meanvalue
> 2009-01-01 00:00:00, 2, 80.3 -- humidity
> 2009-01-01 01:00:00, 2, 81.6
> 2009-01-01 02:00:00, 2, 82.1
> 2009-01-01 03:00:00, 2, 79.8
> 2009-01-01 04:00:00, 2, 77.2
> 2009-01-01 05:00:00, 2, 77.1
> ------------------------------------------------------------------
>
> Table2
> fulldate, id, meanvalue
> 2009-01-01 00:00:00, 1, 12.3 -- temperature
> 2009-01-01 01:00:00, 1, 11.8
> 2009-01-01 02:00:00, 1, 82.1 ! WRONG DATA - SAME AS Table2 id->2
> 2009-01-01 03:00:00, 1, 79.8 ! WRONG DATA - SAME AS Table2 id->2
> 2009-01-01 04:00:00, 1, 77.2 ! WRONG DATA - SAME AS Table2 id->2
> 2009-01-01 05:00:00, 1, 13.1
I am going to assume you mean Table1 above.
>
> Table2
> fulldate, id, meanvalue
> 2009-01-01 00:00:00, 2, 78.9 -- humidity
> 2009-01-01 01:00:00, 2, 76.4
> 2009-01-01 02:00:00, 2, 74.7
> 2009-01-01 03:00:00, 2, 73.1
> 2009-01-01 04:00:00, 2, 71.6
> 2009-01-01 05:00:00, 1, 70.8
>
> Hope this might help,
> Paolo Saudin
I modified Sams query-
SELECT fulldate,sensor
FROM (SELECT fulldate,sensor,count(sensor)
FROM (SELECT 1 AS station, fulldate, meanvalue AS sensor FROM table1
UNION
SELECT 2, fulldate, meanvalue FROM table2 ORDER BY
fulldate,sensor) AS x
GROUP BY fulldate,sensor) AS y
WHERE y.count>1;
and got-
fulldate | sensor
---------------------+--------
2009-01-01 00:00:00 | 12.3
2009-01-01 02:00:00 | 82.1
2009-01-01 03:00:00 | 79.8
2009-01-01 04:00:00 | 77.2
Though I think you might want to deal with the remote sensor problem first. I
would be hesitant to trust any of the data. Just a thought.
--
Adrian Klaver
aklaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
