pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMEDIAN as custom aggregate?
6+ messages / 4 participants
[nested] [flat]
* MEDIAN as custom aggregate?
@ 2001-10-12 19:38 Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 6+ messages in thread
From: Josh Berkus @ 2001-10-12 19:38 UTC (permalink / raw)
To: pgsql-sql
Folks,
Hey, anybody have a custom aggregate for median calucation? I'm doing
this through a PL/pgSQL function, and a custom aggregate would probably
be faster.
For those whose stats terminology is rusty, the "median" is the "middle"
value in a distribution. For example, if we had the following data:
Table ages
person age
Jim 21
Rusty 24
Carol 37
Bob 62
Leah 78
Our Median would be Carol's age, 37. This is a different figure from
the Mean, or Average, which is 44.4. Using the combination of the Mean
and the Median you can do all kinds of interesting statistical analysis.
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: MEDIAN as custom aggregate?
@ 2001-10-12 21:34 Allan Engelhardt <allane@cybaea.com>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 6+ messages in thread
From: Allan Engelhardt @ 2001-10-12 21:34 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>
Can't you do something like
select age from ages order by age limit 1 offset (select count(*) from ages)/2;
except you can't nest the select so you'll have to use a variable to hold it...
Make sure it does the right thing when there is an odd number of rows.
I don't understand why you want the median and not some parameters of your assumed distribution (mean and variance, for example) but each to his own...
Allan.
Josh Berkus wrote:
> Folks,
>
> Hey, anybody have a custom aggregate for median calucation? I'm doing
> this through a PL/pgSQL function, and a custom aggregate would probably
> be faster.
>
> For those whose stats terminology is rusty, the "median" is the "middle"
> value in a distribution. For example, if we had the following data:
>
> Table ages
> person age
> Jim 21
> Rusty 24
> Carol 37
> Bob 62
> Leah 78
>
> Our Median would be Carol's age, 37. This is a different figure from
> the Mean, or Average, which is 44.4. Using the combination of the Mean
> and the Median you can do all kinds of interesting statistical analysis.
>
> -Josh Berkus
>
> ______AGLIO DATABASE SOLUTIONS___________________________
> Josh Berkus
> Complete information technology josh@agliodbs.com
> and data management solutions (415) 565-7293
> for law firms, small businesses fax 621-2533
> and non-profit organizations. San Francisco
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: MEDIAN as custom aggregate?
@ 2001-10-12 22:08 Josh Berkus <josh@agliodbs.com>
parent: Allan Engelhardt <allane@cybaea.com>
0 siblings, 1 reply; 6+ messages in thread
From: Josh Berkus @ 2001-10-12 22:08 UTC (permalink / raw)
To: Allan Engelhardt <allane@cybaea.com>; +Cc: pgsql-sql
Allan,
> Can't you do something like
>
> select age from ages order by age limit 1 offset (select count(*)
> from ages)/2;
>
> except you can't nest the select so you'll have to use a variable to
> hold it...
>
> Make sure it does the right thing when there is an odd number of
> rows.
Duuuuuuuh! I feel like a real idiot now.
The query I'll use is this:
SELECT site, COUNT(pageviews) as count_views, AVG(pageviews) AS
mean_views, median_views
FROM sites,
(SELECT pageviews as median_view
FROM pageviews
LIMIT 1 OFFSET middlerec('pageviews')) med
GROUP BY site, median_views;
Where middlerec is a custom function that counts the records and returns
the middle one.
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: MEDIAN as custom aggregate?
@ 2001-10-13 05:31 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2001-10-13 05:31 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Allan Engelhardt <allane@cybaea.com>; pgsql-sql
"Josh Berkus" <josh@agliodbs.com> writes:
> The query I'll use is this:
> SELECT site, COUNT(pageviews) as count_views, AVG(pageviews) AS
> mean_views, median_views
> FROM sites,
> (SELECT pageviews as median_view
> FROM pageviews
> LIMIT 1 OFFSET middlerec('pageviews')) med
> GROUP BY site, median_views;
> Where middlerec is a custom function that counts the records and returns
> the middle one.
Um ... does that work? I thought LIMIT was fairly restrictive about
what it would take as a parameter --- like, constants or $n parameters
only.
I do not know of any median-finding algorithm that doesn't require a
depressingly large amount of storage...
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: MEDIAN as custom aggregate?
@ 2001-10-13 15:19 Masaru Sugawara <rk73@echna.ne.jp>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 6+ messages in thread
From: Masaru Sugawara @ 2001-10-13 15:19 UTC (permalink / raw)
To: pgsql-sql
On Fri, 12 Oct 2001 12:38:12 -0700
"Josh Berkus" wrote:
> For those whose stats terminology is rusty, the "median" is the "middle"
> value in a distribution. For example, if we had the following data:
>
> Table ages
> person age
> Jim 21
> Rusty 24
> Carol 37
> Bob 62
> Leah 78
>
> Our Median would be Carol's age, 37. This is a different figure from
> the Mean, or Average, which is 44.4. Using the combination of the Mean
> and the Median you can do all kinds of interesting statistical analysis.
>
In such case of this, there needs "nextval" in a query to deal with
a large number of rows. I think the following query, is not smart,
will return the necessary rows (or an average of the rows). But even
so it may need considerable time...
-- (on 7.1.3)
create sequence seq_ages start 1;
select a1.age, a1.rank -- or select avg(a1.age)
from (select a0.person, a0.age, (nextval('seq_ages') - 1) as rank
from (select *, setval('seq_ages',1) -- to reset a sequence
from ages
order by age -- this insignificant "order by" is
-- needed in order to work "setval"
) as a0
order by a0.age
) as a1
where exists (select * from ages
where a1.rank >= (select (count(*)+1)/2 from ages)
and a1.rank <= (select count(*)/2+1 from ages)
)
;
Regards,
Masaru Sugawara
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: MEDIAN as custom aggregate?
@ 2001-10-15 22:44 Josh Berkus <josh@agliodbs.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Josh Berkus @ 2001-10-15 22:44 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Josh Berkus <josh@agliodbs.com>; +Cc: Allan Engelhardt <allane@cybaea.com>; pgsql-sql
Tom,
> Um ... does that work? I thought LIMIT was fairly restrictive about
> what it would take as a parameter --- like, constants or $n
> parameters
> only.
>
> I do not know of any median-finding algorithm that doesn't require a
> depressingly large amount of storage...
Me neither. You're right; the query didn't work.
Here's a link to the median-finding function I posted to the CookBook:
http://www.brasileiro.net/postgres/cookbook/view-one-recipe.adp?recipe_id=1654
Anyone who can improve it is welcome!
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2001-10-15 22:44 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-10-12 19:38 MEDIAN as custom aggregate? Josh Berkus <josh@agliodbs.com>
2001-10-12 21:34 ` Allan Engelhardt <allane@cybaea.com>
2001-10-12 22:08 ` Josh Berkus <josh@agliodbs.com>
2001-10-13 05:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-10-15 22:44 ` Josh Berkus <josh@agliodbs.com>
2001-10-13 15:19 ` Masaru Sugawara <rk73@echna.ne.jp>
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