-----Messaggio originale----- Da: Adrian Klaver [mailto:aklaver@xxxxxxxxxxx] Inviato: giovedì 12 febbraio 2009 23.22 A: Paolo Saudin Cc: pgsql-general@xxxxxxxxxxxxxx Oggetto: Re: R: R: How to check if 2 series of data are equal 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 Thank you very much to you all, this one works perfectly !! >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. The problem has been fixed and does not happens any more. Unfortunately there are 14 years (1992-2006) in which data could be corrupted for short periods. Now I must found them out ... Thanks once more Paolo Saudin >-- >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
