Received: from echna.ne.jp (echna.ne.jp [210.175.52.4]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9DFJwr98073 for ; Sat, 13 Oct 2001 11:19:59 -0400 (EDT) (envelope-from rk73@echna.ne.jp) Received: from VIP333 [210.175.55.148] by echna.ne.jp with ESMTP (SMTPD32-6.06) id AB82298F0124; Sun, 14 Oct 2001 00:19:30 +0900 Date: Sun, 14 Oct 2001 00:19:56 +0900 From: Masaru Sugawara To: pgsql-sql@postgresql.org Subject: Re: MEDIAN as custom aggregate? In-Reply-To: References: Errors-To: Message-Id: <20011014001101.2BE2.RK73@echna.ne.jp> MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit X-Mailer: Becky! ver. 2.00.06 X-Archive-Number: 200110/168 X-Sequence-Number: 5123 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