pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMedian
5+ messages / 5 participants
[nested] [flat]
* Median
@ 2000-07-01 00:37 Kermani, Bahram <BKermani@illumina.com>
0 siblings, 0 replies; 5+ messages in thread
From: Kermani, Bahram @ 2000-07-01 00:37 UTC (permalink / raw)
To: pgsql-sql
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
bkermani@illumina.com <mailto:bkermani@illumina.com>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Median
@ 2000-07-05 08:38 omid omoomi <oomoomi@hotmail.com>
2000-07-18 19:35 ` Re: Median Jan Wieck <JanWieck@t-online.de>
0 siblings, 1 reply; 5+ messages in thread
From: omid omoomi @ 2000-07-05 08:38 UTC (permalink / raw)
To: BKermani@illumina.com; pgsql-sql
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" <BKermani@illumina.com>
>To: "'pgsql-sql@postgresql.org'" <pgsql-sql@postgresql.org>
>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 was
>wondering if anybody knew how to do it. I appreciate it if you reply to my
>email address.
>
>Thanks,
>Bahram Kermani
>bkermani@illumina.com <mailto:bkermani@illumina.com>
________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Median
2000-07-05 08:38 Re: Median omid omoomi <oomoomi@hotmail.com>
@ 2000-07-18 19:35 ` Jan Wieck <JanWieck@t-online.de>
2000-07-18 21:43 ` Re: Median Tom Lane <tgl@sss.pgh.pa.us>
2000-07-19 02:12 ` Re: Median Thomas Lockhart <lockhart@alumni.caltech.edu>
0 siblings, 2 replies; 5+ messages in thread
From: JanWieck@t-online.de @ 2000-07-18 19:35 UTC (permalink / raw)
To: omid omoomi <oomoomi@hotmail.com>; +Cc: BKermani@illumina.com; pgsql-sql
omid omoomi wrote:
> Hi,
> I'll be glad if you describe more details about the problem. Is it a kind of
> statistical analysis or what?
> Omid Omoomi
Sorry to respond that slow.
It's something, Ulf Mehlig described to me a couple of months
ago. The median is the value, below and above of which
exactly half of the sorted items would be. Having an odd
number of entries, it's exactly the one in the middle like
1 2 3 4 5 6 7
------|------
M = 4
Having an even number of entries, it's the average between
the two in the middle like
1 2 3 4 5 6 7 8
-------|-------
M = (4 + 5) / 2
As he said, other ranges like Quartile (the position where
25% of the entries are below and 75% are above) would also be
of interest. So the most useful thing would be an aggregate
like qantil(n), where n is value between 0.0 and 100.0, so
that quantil(50.0) is the Median, quantil(25.0) is the first
Quartile and so on.
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 you need to
look at the entire sorted list of selected items.
Maybe someone else has an idea.
>
> >From: "Kermani, Bahram" <BKermani@illumina.com>
> >To: "'pgsql-sql@postgresql.org'" <pgsql-sql@postgresql.org>
> >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 was
> >wondering if anybody knew how to do it. I appreciate it if you reply to my
> >email address.
> >
> >Thanks,
> >Bahram Kermani
> >bkermani@illumina.com <mailto:bkermani@illumina.com>
>
> ________________________________________________________________________
> Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
>
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Median
2000-07-05 08:38 Re: Median omid omoomi <oomoomi@hotmail.com>
2000-07-18 19:35 ` Re: Median Jan Wieck <JanWieck@t-online.de>
@ 2000-07-18 21:43 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2000-07-18 21:43 UTC (permalink / raw)
To: Jan Wieck <JanWieck@yahoo.com>; +Cc: omid omoomi <oomoomi@hotmail.com>; BKermani@illumina.com; pgsql-sql
JanWieck@t-online.de (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 you need to
> look at the entire sorted list of selected items.
Hmm. It would be a pretty straightforward extension of the existing
support for DISTINCT aggregates to allow the agg function to receive
all the inputs in sorted order along with a count of how many there
are, whereupon a percentile aggregate would be trivial.
Slow, but trivial.
Probably a better way would be to skip evaluating the agg's transition
function as such, and instead call the agg's final function just once
with a pointer to the tuplesort object that contains the sorted input
data. Then you reach in and pull out just the items you want, instead
of having to read 'em all. Or you can scan 'em if you want. We
might need to add a few features to the tuplesort API to allow access
to the N'th item in the sorted data, but it's surely doable.
Anyone care to work up a detailed proposal for something along this
line? It seems like more work than it's worth to me, but if someone
else wants to do the legwork...
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Median
2000-07-05 08:38 Re: Median omid omoomi <oomoomi@hotmail.com>
2000-07-18 19:35 ` Re: Median Jan Wieck <JanWieck@t-online.de>
@ 2000-07-19 02:12 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
1 sibling, 0 replies; 5+ messages in thread
From: Thomas Lockhart @ 2000-07-19 02:12 UTC (permalink / raw)
To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: omid omoomi <oomoomi@hotmail.com>; BKermani@illumina.com; pgsql-sql
> 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 *really bad* C
code which looks like Fortran ;)
Anyway, once the bugs were fixed (and there were several :( and a better
endgame algorithm was coded, it was suitable for hypercube distributed
processing, where you just exchange a small amount of info between
iterations. And it involved simply *counting* how many values were above
and below an initial or updated guess, then iterating on a new guess.
afaik that wouldn't be suitable for the existing aggregate capabilities,
but multi-pass algorithms would be nice to be able to do.
- Thomas
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2000-07-19 02:12 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-07-01 00:37 Median Kermani, Bahram <BKermani@illumina.com>
2000-07-05 08:38 Re: Median omid omoomi <oomoomi@hotmail.com>
2000-07-18 19:35 ` Jan Wieck <JanWieck@t-online.de>
2000-07-18 21:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-07-19 02:12 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
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