up vote 3 down vote favorite

my Postgres query calculates statistical aggregates from a bunch of sensor readings:

SELECT to_char(ipstimestamp, 'YYYYMMDDHH24') As row_name, 
to_char(ipstimestamp, 'FMDD mon FMHH24h') As hour_row_name, 
varid As category, 

(AVG(ipsvalue)::NUMERIC(5,2)) ||', ' || 
(MAX(ipsvalue)::NUMERIC(5,2))::TEXT  ||', ' || 
(MIN(ipsvalue)::NUMERIC(5,2))::TEXT  ||', ' || 
(STDDEV(ipsvalue)::NUMERIC(5,2))::TEXT  ||', ' As StatisticsValue 

FROM loggingdb_ips_integer As log 
JOIN ipsobjects_with_parent ips ON log.varid = ips.objectid 
AND (ipstimestamp > (now()- '2 days'::interval)) 
GROUP BY row_name, hour_row_name, category;

This works fine as long as I have >1 ipsvalue/hour. If the hourly COUNT(ipsvalue)<2, however, StatisticsValue returns NULL without any Postgres errors.

If I comment out STTDEV, as in the following:

(AVG(ipsvalue)::NUMERIC(5,2)) ||', ' || 
(MAX(ipsvalue)::NUMERIC(5,2))::TEXT  ||', ' || 
(MIN(ipsvalue)::NUMERIC(5,2))::TEXT  ||', ' As value

then all three stats are calculated correctly. I therefore conclude that an illegittimate STDDEV brings down the whole query. I would rather have illegittimate STDDEVs returning 0. I tried to COALESCE the STDDEV line, to no avail. What can be done???

share|improve this question
2  
How did you use coalesce? This should work: (coalesce(STDDEV(ipsvalue), 0)::NUMERIC(5,2))::TEXT – Clodoaldo Neto Jun 26 '12 at 20:07
    
Clodoaldo, many thanks!!! I have tried your syntax, and indeed it works as expected! Apparently I had messed up the COALESCE syntax, I had too many colons and too many transforms from number to text and back! – aag Jun 27 '12 at 10:27
up vote 5 down vote accepted

COALESCE should work.

You could also use (it that fits you) the "population standard deviation" stddev_pop, instead of the "sample standard deviation" stddev_samp; the later is divides by n-1 and is aliased to STDDEV. stddev_pop, instead , divides by n , and it returns zero (instead of NULL) when given one sample.

If you don't know the difference between these estimators, it's explained in every statistic textbook, eg http://en.wikipedia.org/wiki/Standard_deviation#Estimation

share|improve this answer
    
Leonbloy, many thanks indeed for your help. Your explanations are very clear and helpful. I appreciate your taking the time to guide me through this! I eventually managed to get COALESCE to work (see above). – aag Jun 27 '12 at 10:28
up vote 0 down vote

I found a workaround which is an alternative to COALESCE. In my specific instance, COALESCE is likely to perform better, but the workaround is potentially more flexible.

I have taken advantage of the IIF simulation described by Emanuel Calvo Franco and Hector de los Santos. IIF works pretty much like its homologue in MS Access. In my instance, the IIF function tests the result of STDDEV for NULL, and returns a "0" if true. The good thing about IIF is that it can test all sorts of conditions, not only NULL.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.