Re: [SQL] MEDIAN as custom aggregate?

2001-10-16 Thread Josh Berkus
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 r

Re: [SQL] MEDIAN as custom aggregate?

2001-10-13 Thread Masaru Sugawara
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 > personage > Jim 21 > Rusty 24 > Carol

Re: [SQL] MEDIAN as custom aggregate?

2001-10-13 Thread Tom Lane
"Josh Berkus" <[EMAIL PROTECTED]> 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

Re: [SQL] MEDIAN as custom aggregate?

2001-10-12 Thread Josh Berkus
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. Duu

Re: [SQL] MEDIAN as custom aggregate?

2001-10-12 Thread Allan Engelhardt
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 th

[SQL] MEDIAN as custom aggregate?

2001-10-12 Thread Josh Berkus
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

Re: [SQL] Median

2000-07-18 Thread Thomas Lockhart
> Maybe someone else has an idea. I implemented a different algorithm several years ago. It is an O(log(N)) process (unlike most other techniques), and was borrowed from the "Algorithms" book (it's at work; but it is the classic "yellow jacket" book with Fortran code and the other volume with *re

Re: [SQL] Median

2000-07-18 Thread Tom Lane
[EMAIL PROTECTED] (Jan Wieck) writes: > I don't see any quick solution how to solve this problem with > an aggregate. Aggregates get all selected values in unsorted > order, and don't know ahead how many items there will be. > Even if, all this wouldn't be of any use, because

Re: [SQL] Median

2000-07-18 Thread Jan Wieck
<[EMAIL PROTECTED]> > >To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> > >Subject: [SQL] Median > >Date: Fri, 30 Jun 2000 17:37:06 -0700 > > > >Hello, > > > >I am trying to do a Median or Trimmed-mean operation in postgreSQL. I wa

Re: [SQL] Median

2000-07-05 Thread omid omoomi
Hi, I'll be glad if you describe more details about the problem. Is it a kind of statistical analysis or what? Omid Omoomi >From: "Kermani, Bahram" <[EMAIL PROTECTED]> >To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> >Subject: [SQL] Median

[SQL] Median

2000-07-05 Thread Kermani, Bahram
Hello,   I am trying to do a Median or Trimmed-mean operation in postgreSQL. I was wondering if anybody knew how to do it. I appreciate it if you reply to my email address.   Thanks, Bahram Kermani [EMAIL PROTECTED]