pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
MEDIAN as custom aggregate?
6+ messages / 4 participants
[nested] [flat]

* MEDIAN as custom aggregate?
@ 2001-10-12 19:38 Josh Berkus <josh@agliodbs.com>
  2001-10-12 21:34 ` Re: MEDIAN as custom aggregate? Allan Engelhardt <allane@cybaea.com>
  2001-10-13 15:19 ` Re: MEDIAN as custom aggregate? Masaru Sugawara <rk73@echna.ne.jp>
  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 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   ` Re: MEDIAN as custom aggregate? 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 19:38 MEDIAN as custom aggregate? Josh Berkus <josh@agliodbs.com>
  2001-10-12 21:34 ` Re: MEDIAN as custom aggregate? Allan Engelhardt <allane@cybaea.com>
@ 2001-10-12 22:08   ` Josh Berkus <josh@agliodbs.com>
  2001-10-13 05:31     ` Re: MEDIAN as custom aggregate? Tom Lane <tgl@sss.pgh.pa.us>
  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-12 19:38 MEDIAN as custom aggregate? Josh Berkus <josh@agliodbs.com>
  2001-10-12 21:34 ` Re: MEDIAN as custom aggregate? Allan Engelhardt <allane@cybaea.com>
  2001-10-12 22:08   ` Re: MEDIAN as custom aggregate? Josh Berkus <josh@agliodbs.com>
@ 2001-10-13 05:31     ` Tom Lane <tgl@sss.pgh.pa.us>
  2001-10-15 22:44       ` Re: MEDIAN as custom aggregate? 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-12 19:38 MEDIAN as custom aggregate? Josh Berkus <josh@agliodbs.com>
  2001-10-12 21:34 ` Re: MEDIAN as custom aggregate? Allan Engelhardt <allane@cybaea.com>
  2001-10-12 22:08   ` Re: MEDIAN as custom aggregate? Josh Berkus <josh@agliodbs.com>
  2001-10-13 05:31     ` Re: MEDIAN as custom aggregate? Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-10-15 22:44       ` Josh Berkus <josh@agliodbs.com>
  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

* Re: MEDIAN as custom aggregate?
  2001-10-12 19:38 MEDIAN as custom aggregate? Josh Berkus <josh@agliodbs.com>
@ 2001-10-13 15:19 ` Masaru Sugawara <rk73@echna.ne.jp>
  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


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