R: R: R: How to check if 2 series of data are equal - Mailing list pgsql-general

-----Messaggio originale-----
Da: Adrian Klaver [mailto:aklaver@comcast.net]
Inviato: giovedì 12 febbraio 2009 23.22
A: Paolo Saudin
Cc: pgsql-general@postgresql.org
Oggetto: Re: R: R: [GENERAL] 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
couldbe corrupted for short periods. Now I must found them out ... 

Thanks once more

Paolo Saudin

>--
>Adrian Klaver
>aklaver@comcast.net