Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9D5WAr73274 for ; Sat, 13 Oct 2001 01:32:11 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id f9D5VtI08806; Sat, 13 Oct 2001 01:31:55 -0400 (EDT) To: "Josh Berkus" cc: Allan Engelhardt , pgsql-sql@postgresql.org Subject: Re: MEDIAN as custom aggregate? In-reply-to: References: Comments: In-reply-to "Josh Berkus" message dated "Fri, 12 Oct 2001 15:08:42 -0700" Date: Sat, 13 Oct 2001 01:31:54 -0400 Message-ID: <8803.1002951114@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200110/166 X-Sequence-Number: 5121 "Josh Berkus" 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