pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Median
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>
  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-18 19:35  Jan Wieck <JanWieck@t-online.de>
  parent: omid omoomi <oomoomi@hotmail.com>
  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-18 21:43  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Jan Wieck <JanWieck@t-online.de>
  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-19 02:12  Thomas Lockhart <lockhart@alumni.caltech.edu>
  parent: Jan Wieck <JanWieck@t-online.de>
  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