Re: R: R: [GENERAL] How to check if 2 series of data are equal

[Octavio Alvarez]

On Thu, 2009-02-12 at 14:21 -0800, Adrian Klaver wrote:
> On Thursday 12 February 2009 11:37:37 am Paolo Saudin wrote:
> 

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

You might want to do a UNION with all your tables to add a field to
identify the table, and use min() and max() to show the offending
tables.




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: R: R: [GENERAL] How to check if 2 series of data are equal

[Adrian Klaver]

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
akla...@comcast.net

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general