Re: Need help for constructing query

["David Johnston" <polobo@xxxxxxxxx>]

Over complicated or not the solution makes sense and seems to be correct.
As described you ended up using a sub-query within the EXCEPT clause in
order to return just the most recent sensor reading for each sensor (with
the additional range check for min/max).  I've never actually used an EXCEPT
before and while alternatives can work this is intuitive. 

I'm just getting used to using Widows myself (since before about a month ago
I was running 8.2) but they do make doing this intra-record aggregations
easier (though non-Window solutions are often possible).  

Barring any performance concerns I'd just use what you have and move on.
SQL is very good at getting results but most non-trivial expressions do
become complicated.  Using views and/or functions can hide of the complexity
but it still ends up being present.

David J.

-----Original Message-----
From: pgsql-general-owner@xxxxxxxxxxxxxx
[mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Marco
Sent: Friday, March 25, 2011 12:24 PM
To: pgsql-general@xxxxxxxxxxxxxx
Subject: Re:  Need help for constructing query

....

Select all rows that exceeded the alarm values within the last 10 min except
those where the last inserted entry (determined by datetime) didn't exceeded
the alarm value.

I feel that my solution is overcomplicated. I solved it as follows.

SELECT sensorid, min, value, max, datetime FROM sensordata NATURAL JOIN
sensors WHERE
  datetime
    BETWEEN CURRENT_TIMESTAMP - INTERVAL '10 minutes'
    AND CURRENT_TIMESTAMP
  AND ( value<min OR value>max )
EXCEPT
(
  SELECT sensorid, min, value, max, t.datetime
    FROM
    (
      SELECT sensorid, datetime, min, value, max, MAX(datetime)
	OVER (PARTITION BY sensorid) AS last
      FROM sensordata NATURAL JOIN sensors
      WHERE
	datetime
	  BETWEEN CURRENT_TIMESTAMP - INTERVAL '10 minutes'
	  AND CURRENT_TIMESTAMP
    ) AS t
    WHERE
      t.datetime=t.last AND ( value>=min AND value<= max ) );


Marco


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


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