Statistical aggregates with intervals

From: Thomas Munro <munro(at)ip9(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Statistical aggregates with intervals
Date: 2012-08-22 20:25:47
Message-ID: CADLWmXUy2RqUOj9Z5Hhe1sVFQNRbRBtF=gW5SAbnXCPA1GY=hg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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!

Responses

Browse pgsql-general by date

  From Date Subject
Next Message John R Pierce 2012-08-22 20:31:49 Re: Database Bloat
Previous Message Andrew Hannon 2012-08-22 20:10:01 Re: Amazon High I/O instances