Блог

Мне нужно выполнить некоторый статистический анализ интервалов, т.Е. Разницы между двумя полями даты и времени в таблице.

Согласно документации aggregate function здесь. Агрегатные функции для MAX , MIN , AVG и т. Д., т. Е. Общие агрегатные функции, Принимают аргументы для полей даты, времени и интервала.

Однако для более продвинутых статистических функций, таких как stddev_pop , var_pop , var_sam и std_samp поддерживаемые входные данные, по-видимому, являются только числовыми или аналогичными. Хотя в документации предполагается, что между двумя типами функций нет разницы

… (Они выделены просто для того, чтобы не загромождать список наиболее часто используемых агрегатов.)…

Есть ли какой-нибудь простой способ вычислить эти параметры? и почему тип интервала в качестве аргумента не поддерживается? Эти типы статистических агрегированных функций инвариантны к единицам измерения?

PS Я не уверен, что смогу извлечь эпоху и использовать ее, поскольку некоторые значения могут быть отрицательными.

Как я уже сказал в комментарии, чтобы вручную определить стандартное отклонение выборки, в какой-то момент вы умножаете интервал на интервал. PostgreSQL этого не поддерживает.

Чтобы обойти эту проблему, уменьшите интервал до часов, минут или секунд (или чего угодно). Это оказывается намного проще, чем выполнять вычисления вручную, и это говорит о том, почему PostgreSQL не поддерживает такого рода вычисления из коробки.

Во-первых, функция из общего списка рассылки PostgreSQL

 CREATE OR REPLACE FUNCTION interval_to_seconds(interval)
RETURNS double precision AS $$
    SELECT (extract(days from $1) * 86400)
           (extract(hours from $1) * 3600)
           (extract(minutes from $1) * 60)
           extract(seconds from $1);
$$ LANGUAGE SQL;
  

Теперь мы можем взять стандартное отклонение простого набора интервалов.

 with intervals (i) as (
  values (interval '1 hour'), (interval '2 hour'), (interval '3 hour'), 
         (interval '4 hour'), (interval '5 hour') 
)
, intervals_as_seconds as (
  select interval_to_seconds(i) as seconds
  from intervals
)
select stddev(seconds), stddev(seconds)/60
from intervals_as_seconds
  

in_sec in_min
двойная точность двойная точность
--
5692.09978830308 94.8683298050514

Вы можете проверить результаты, как вам нравится.

Теперь предположим, что вам нужна часовая детализация вместо секунд. Очевидно, что выбор степени детализации сильно зависит от приложения. Вы можете определить другую функцию, interval_to_hours(interval) . Вы можете использовать очень похожий запрос для вычисления стандартного отклонения.

 with intervals (i) as (
  values (interval '1 hour'), (interval '2 hour'), (interval '3 hour'), 
         (interval '4 hour'), (interval '5 hour') 
)
, intervals_as_hours as (
  select interval_to_hours(i) as hours
  from intervals
)
select stddev(hours) as stddev_in_hrs
from intervals_as_hours
  

stddev_in_hr
двойная точность
--
1.58113883008419

Значение стандартного отклонения в часах явно отличается от значения в минутах или секундах. Но они измеряют одно и то же. Дело в том, что «правильный» ответ зависит от степени детализации (единиц измерения), которую вы хотите использовать, и есть много вариантов. (Я полагаю, от микросекунд до столетий.)

Также рассмотрите это утверждение.

 select interval_to_hours(interval '45 minutes')
  

интервал_to_hours
двойная точность
--
0

Это правильный ответ? Вы не можете сказать; правильный ответ зависит от приложения. Я могу представить приложения, которые хотели бы, чтобы 45 минут рассматривались как 1 час. Я также могу представить приложения, которые хотели бы, чтобы 45 минут рассматривались как 1 час для некоторых вычислений и как 0 часов для других вычислений.

И подумайте об этом вопросе. Сколько секунд в месяце? Выражение select interval '1' month; допустимо; количество секунд зависит от того, сколько дней в месяце.

И я думаю, именно поэтому PostgreSQL не поддерживает такого рода вычисления из коробки. Правильный способ сделать это с интервальными аргументами слишком зависит от приложения.

Позже . . .

Я нашел это обсуждение в одном из списков рассылки PostgreSQL.

Нет stddev() для интервала?