Re: More complex median calculation in Oracle SQL

From: joel garry <joel-garry_at_home.com>
Date: Thu, 26 Aug 2010 14:42:36 -0700 (PDT)
Message-ID: <505f352f-6add-4b2b-abee-082ae15480bf_at_f20g2000pro.googlegroups.com>

On Aug 25, 8:44 am, Hans Mayr <mayr1..._at_gmx.de> wrote:
> Hello,
>
> Please consider the following example:
>
> CREATE TABLE TMP_ARTICLES
> (
>   ARTICLE_ID NUMBER,
>   UNIT_PRICE NUMBER,
>   SOLD_UNITS NUMBER
> )
> ;
>
> INSERT INTO "TMP_ARTICLES" (ARTICLE_ID, UNIT_PRICE, SOLD_UNITS) VALUES
> ('1', '10', '100');
> INSERT INTO "TMP_ARTICLES" (ARTICLE_ID, UNIT_PRICE, SOLD_UNITS) VALUES
> ('2', '20', '110');
> INSERT INTO "TMP_ARTICLES" (ARTICLE_ID, UNIT_PRICE, SOLD_UNITS) VALUES
> ('3', '30', '120');
> INSERT INTO "TMP_ARTICLES" (ARTICLE_ID, UNIT_PRICE, SOLD_UNITS) VALUES
> ('4', '40', '130');
> INSERT INTO "TMP_ARTICLES" (ARTICLE_ID, UNIT_PRICE, SOLD_UNITS) VALUES
> ('5', '50', '140');
> INSERT INTO "TMP_ARTICLES" (ARTICLE_ID, UNIT_PRICE, SOLD_UNITS) VALUES
> ('6', '60', '150');
> INSERT INTO "TMP_ARTICLES" (ARTICLE_ID, UNIT_PRICE, SOLD_UNITS) VALUES
> ('7', '70', '160');
> INSERT INTO "TMP_ARTICLES" (ARTICLE_ID, UNIT_PRICE, SOLD_UNITS) VALUES
> ('8', '80', '170');
> INSERT INTO "TMP_ARTICLES" (ARTICLE_ID, UNIT_PRICE, SOLD_UNITS) VALUES
> ('9', '90', '180');
> INSERT INTO "TMP_ARTICLES" (ARTICLE_ID, UNIT_PRICE, SOLD_UNITS) VALUES
> ('10', '100', '190');
>
> How do I determine the median of the unit prices "x" in the following
> meaning:
>
> My total turnover of articles with an unit price lower or equal to x
> is more than fifty percent of my total turnover, but the total
> turnover of all unit prices with any unit price y < x is less than
> fity percent of my total turnover.
>
> In the example above x = 80 because:
>
> select sum(unit_price * sold_units) as total_turnover from
> tmp_articles;
>
> => My total turnover is 88000
>
> select
>      sum(unit_price * sold_units) as total_turnover
> from
>      tmp_articles
> where
>      unit_price <= 80;
>
> => 52800 (more than 50% of 88000)
>
> select
>      sum(unit_price * sold_units) as total_turnover
> from
>      tmp_articles
> where
>      unit_price <= 79.99999;
>
> => 39200 (less than 50% of 88000).
>
> I am not sure if "median" is the correct term for what I am looking
> for and I am not an expert for statistics. However, is there an oracle
> function to do what I want?
>
> Thanks for you help.
>
> Best regards,
>
> Hans

I don't have time, unfortunately, to think through this all the way, but I suspect what you are looking for is an analytic. See http://orafaq.com/node/55 you probably want to sum your total turnover and partition by 50% of that. Various examples http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:17329730362010

You may also be able to brute-force the sql without analytics by putting the sum in a subquery, called by the various things you want to show.

I don't remember much about statistics, except that I had to memorize a dozen or so different definitions of mean, median and mode. I don't think the Oracle median function is the one you want, as it divides the number of values above and below equally.

jg

--
_at_home.com is bogus.
http://en.wikipedia.org/wiki/UVB-76

Received on Thu Aug 26 2010 - 16:42:36 CDT