Statistical aggregates with intervals

Поиск
Список
Период
Сортировка
От Thomas Munro
Тема Statistical aggregates with intervals
Дата
Msg-id CADLWmXUy2RqUOj9Z5Hhe1sVFQNRbRBtF=gW5SAbnXCPA1GY=hg@mail.gmail.com
обсуждение исходный текст
Ответы Re: Statistical aggregates with intervals  (Christopher Swingley <cswingle@gmail.com>)
Список pgsql-general
Hi

I noticed that 'avg' works on 'interval', but 'stddev' and 'variance' don't:

hack=> create table test (start_time timestamptz, end_time timestamptz);
CREATE TABLE
hack=> insert into test values (now(), now() + interval '1 second');
INSERT 0 1`
hack=> insert into test values (now(), now() + interval '1 second');
INSERT 0 1
hack=> insert into test values (now(), now() + interval '4 second');
INSERT 0 1
hack=> select avg(end_time - start_time) from test;
   avg
----------
 00:00:02
(1 row)

hack=> select stddev(end_time - start_time) from test;
ERROR:  function stddev(interval) does not exist
LINE 1: select stddev(end_time - start_time) from test;
               ^
HINT:  No function matches the given name and argument types. You
might need to add explicit type casts.

Sure enough the standard deviation of time intervals can be computed
by following that hint:

hack=> select interval '1 second' * stddev(extract(epoch from end_time
- start_time)) as stddev from test;
     stddev
-----------------
 00:00:01.732051
(1 row)

But is there some way I can use CREATE AGGREGATE to define stddev for
intervals in terms of the built-in stddev aggregate, just transforming
the inputs and output?  Or am I missing something fundamental that
explains why stddev(interval) isn't supported?

Thanks!


В списке pgsql-general по дате отправления:

Предыдущее
От: Andrew Hannon
Дата:
Сообщение: Re: Amazon High I/O instances
Следующее
От: John R Pierce
Дата:
Сообщение: Re: Database Bloat