pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed`statistical' aggregate functions etc.
3+ messages / 3 participants
[nested] [flat]
* `statistical' aggregate functions etc.
@ 1998-10-19 07:39 Ulf Mehlig <umehlig@uni-bremen.de>
0 siblings, 2 replies; 3+ messages in thread
From: Ulf Mehlig @ 1998-10-19 07:39 UTC (permalink / raw)
To: pgsql-sql
I'm a novice to postgresql, so please excuse if I'm addressing to the
wrong mailing list!
I need to specify some column functions for statistical analysis,
e.g. standard deviation or variance. After reading the user's guideI
think that's to realize with aggregates, but with those docs only I
cannot figure out how to do it -- does anybody know whether there is a
ready-for-use "statistics" package, or how to write something that's
usable in a SQL statement like this:
select date, avg(temperature), stddev(temperature)
from temperatures
where date between '01.01.1999' and '15.02.1999'
group by date ?
Other question: I'm used to use DB2/2, and DB2/2 has functions like
"month(some_date)" and "hour(some_timestamp)". Are there equivalents
in postgresql?
Many thanks in advance for help + patience!
Ulf
--
======================================================================
%%%%% Ulf Mehlig <ulf.mehlig@uni-bremen.de>
%%%%!%%% Projekt "MADAM" <umehlig@uni-bremen.de>
%%%% %!% %%%% ----------------------------------------------------
---| %%% MADAM: MAngrove | Center for Tropical Marine
||--%!% Dynamics | Biology
|| And | Fahrenheitstrasse 1
_ /||\_/\_ Management |
/ / \ \ ~~~~~~~~~~~~~~~~~ | 28359 Bremen/Germany
~~~~~~~~~~~~~~~~~~~~
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] `statistical' aggregate functions etc.
@ 1998-10-22 09:45 Aleksey Dashevsky <postgres@luckynet.co.il>
parent: Ulf Mehlig <umehlig@uni-bremen.de>
1 sibling, 0 replies; 3+ messages in thread
From: Aleksey Dashevsky @ 1998-10-22 09:45 UTC (permalink / raw)
To: Ulf Mehlig <umehlig@uni-bremen.de>; +Cc: pgsql-sql
On Mon, 19 Oct 1998, Ulf Mehlig wrote:
>
> I'm a novice to postgresql, so please excuse if I'm addressing to the
> wrong mailing list!
>
.....
>
> Other question: I'm used to use DB2/2, and DB2/2 has functions like
> "month(some_date)" and "hour(some_timestamp)". Are there equivalents
> in postgresql?
>
I think you should try date_part function, which is described in
pgintro(1) man page:
select date_part('month', some_date);
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] `statistical' aggregate functions etc.
@ 1998-10-22 10:04 Jan Wieck <jwieck@debis.com>
parent: Ulf Mehlig <umehlig@uni-bremen.de>
1 sibling, 0 replies; 3+ messages in thread
From: jwieck@debis.com @ 1998-10-22 10:04 UTC (permalink / raw)
To: umehlig@uni-bremen.de; +Cc: pgsql-sql
> I'm a novice to postgresql, so please excuse if I'm addressing to the
> wrong mailing list!
>
> I need to specify some column functions for statistical analysis,
> e.g. standard deviation or variance. After reading the user's guideI
> think that's to realize with aggregates, but with those docs only I
> cannot figure out how to do it -- does anybody know whether there is a
> ready-for-use "statistics" package, or how to write something that's
> usable in a SQL statement like this:
>
> select date, avg(temperature), stddev(temperature)
> from temperatures
> where date between '01.01.1999' and '15.02.1999'
> group by date ?
>
The example below works and produces
select a, count(b) as n, average(b),
variance(b), stddev(b) from t1 group by a;
a| n|average| variance| stddev
-+--+-------+----------------+--------------
1| 5| 6.4| 0.8| 0.894427191
2|10| 3.53| 0.0401111111111|0.200277585144
3|10| 3.56|0.00488888888889|0.069920589878
(3 rows)
but it is slow. On my good old 486DX4 100MHz here in the
office it runs approx. 80 seconds if there are 6400 rows in
t1. If there are only a few rows to output, most of the time
is spent in the collector. So you would need at least to
create a new postgres basetype that holds two float8's
internal where the external representation is a Tcl list and
replace the stat_collect() function by a C language one. But
when already doing so, implementing the others in C too isn't
that much more work.
Doing it all in C would require 6 functions to implement.
The C equivalents of the four functions below plus the input
and output functions for the new data type.
Call me on +49 40 5395-1404 (office) if you have trouble or
don't know how to do it at all.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #
Statistical aggregate example in PL/Tcl:
create table t1 (a int4, b float8);
insert into t1 values (1, 5.0);
insert into t1 values (1, 6.0);
insert into t1 values (1, 7.0);
insert into t1 values (1, 7.0);
insert into t1 values (1, 7.0);
insert into t1 values (2, 3.3);
insert into t1 values (2, 3.5);
insert into t1 values (2, 3.7);
insert into t1 values (2, 3.2);
insert into t1 values (2, 3.6);
insert into t1 values (2, 3.5);
insert into t1 values (2, 3.6);
insert into t1 values (2, 3.4);
insert into t1 values (2, 3.6);
insert into t1 values (2, 3.9);
insert into t1 values (3, 3.5);
insert into t1 values (3, 3.6);
insert into t1 values (3, 3.6);
insert into t1 values (3, 3.7);
insert into t1 values (3, 3.5);
insert into t1 values (3, 3.6);
insert into t1 values (3, 3.5);
insert into t1 values (3, 3.5);
insert into t1 values (3, 3.6);
insert into t1 values (3, 3.5);
create function stat_collect (text, float8) returns text as '
if {[llength $1] == 0} {
set sum [expr double($2)]
set qsum [expr double($2) * double($2)]
} else {
set sum [expr [lindex $1 0] + double($2)]
set qsum [expr [lindex $1 1] + double($2) * double($2)]
}
return [list $sum $qsum]
' language 'pltcl';
create function stat_average (float8, int4) returns float8 as '
if {$2 == 0} {return 0.0}
return [expr double($1) / double($2)]
' language 'pltcl';
create function stat_variance (text, int4) returns float8 as '
if {$2 < 2} {return 0.0}
set n [expr double($2)]
set sum [lindex $1 0]
set qsum [lindex $1 1]
set average [expr $sum / $n]
set avgqsum [expr $average * $average * $n]
set variance [expr ($qsum - $avgqsum) / ($n - 1.0)]
return $variance
' language 'pltcl';
create function stat_stddev (text, int4) returns float8 as '
if {$2 < 2} {return 0.0}
set n [expr double($2)]
set sum [lindex $1 0]
set qsum [lindex $1 1]
set average [expr $sum / $n]
set avgqsum [expr $average * $average * $n]
set variance [expr ($qsum - $avgqsum) / ($n - 1.0)]
set stddev [expr sqrt($variance)]
return $stddev
' language 'pltcl';
create aggregate average (
basetype = float8,
stype1 = float8,
stype2 = int4,
sfunc1 = float8pl,
sfunc2 = int4inc,
finalfunc = stat_average,
initcond1 = '0.0',
initcond2 = '0'
);
create aggregate variance (
basetype = float8,
stype1 = text,
stype2 = int4,
sfunc1 = stat_collect,
sfunc2 = int4inc,
finalfunc = stat_variance,
initcond1 = '',
initcond2 = '0'
);
create aggregate stddev (
basetype = float8,
stype1 = text,
stype2 = int4,
sfunc1 = stat_collect,
sfunc2 = int4inc,
finalfunc = stat_stddev,
initcond1 = '',
initcond2 = '0'
);
select a, count(b) as n, average(b),
variance(b), stddev(b) from t1 group by a;
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~1998-10-22 10:04 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-10-19 07:39 `statistical' aggregate functions etc. Ulf Mehlig <umehlig@uni-bremen.de>
1998-10-22 09:45 ` Aleksey Dashevsky <postgres@luckynet.co.il>
1998-10-22 10:04 ` Jan Wieck <jwieck@debis.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox