Агрегатное поведение postgres STDDEV, когда n<2

Question

Мой запрос Postgres вычисляет статистические агрегаты из набора показаний датчика:

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;

Это работает нормально, пока у меня>1 ipsvalue/ час. Однако, если ежечасный COUNT(ipsvalue)<2, StatisticsValue возвращает NULL без каких-либо ошибок Postgres.

Если я закомментирую STTDEV, как показано ниже:

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

тогда все три характеристики рассчитываются правильно. Поэтому я прихожу к выводу, что неверный STDDEV сбивает весь запрос. Я предпочел бы, чтобы нелегитимные значения STDDEV возвращали 0. Я попытался объединить строку STDDEV, но безрезультатно. Что можно сделать???

Answer 1

COALESCE должно сработать.

Вы также можете использовать (если вам подходит) " стандартное отклонение населения" stddev_popвместо "стандартного отклонения выборки" stddev_samp; последний делится на n-1 и псевдоним STDDEV, stddev_popвместо этого делится на n и возвращает ноль (вместо NULL) когда дан один образец.

Если вы не знаете разницу между этими оценками, это объясняется в каждом учебнике по статистике, например, http://en.wikipedia.org/wiki/Standard_deviation

Answer 2

Я нашел обходной путь, который является альтернативой COALESCE. В моем конкретном случае COALESCE, вероятно, будет работать лучше, но обходной путь потенциально более гибкий.

Я воспользовался симуляцией ИИФ, описанной Эмануэлем Кальво Франко и Гектором де лос Сантосом. IIF работает почти так же, как его гомолог в MS Access. В моем случае функция IIF проверяет результат STDDEV на NULL и возвращает "0", если истина. Преимущество IIF в том, что он может тестировать всевозможные условия, не только NULL.