Home » SQL & PL/SQL » SQL & PL/SQL » median (10g)
median [message #427589] Fri, 23 October 2009 07:16 Go to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Deployment time of a script can be counted using a distribution median. The script deployment time using distribution median is calculated the following way: the median of a finite list of deployments of the same script can be found by arranging all the values from the lowest to the highest one and picking the middle one. If there is an even number of values, the median is not unique, so the mean of two middle values is taken. For example, if a < b < c, then the median of the list {a, b, c} is b, and if a < b < c < d, then the median of the list {a, b, c, d} is the mean of b and c, i.e. it is (b + c) / 2.
can you please suggest something?
create table test
values(
id number,
some_value number
);

insert into test values(1,10);
insert into test values(2,1);
insert into test values(2,2);
insert into test values(2,3);
insert into test values(2,4);
insert into test values(3,10);
insert into test values(3,20);
insert into test values(3,30);

the output expected
1 10
2 2.5
3 20

[Updated on: Fri, 23 October 2009 07:22]

Report message to a moderator

Re: median [message #427592 is a reply to message #427589] Fri, 23 October 2009 07:34 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select distinct
  2         id,
  3         PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY some_value) OVER(PARTITION BY id) med
  4  from test
  5  /
        ID        MED
---------- ----------
         1         10
         2        2.5
         3         20

3 rows selected.

Regards
Michel
Re: median [message #427593 is a reply to message #427589] Fri, 23 October 2009 07:34 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
select id, median(some_value) from test group by id;
Re: median [message #427594 is a reply to message #427593] Fri, 23 October 2009 07:37 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
many many Thanks Guys

I was writing my own stuff Smile
SELECT   ID, AVG (some_value)
    FROM (SELECT *
            FROM (SELECT ID, some_value, rnk,
                         MAX (rnk) OVER (PARTITION BY ID) max_rnk
                    FROM (SELECT ID, some_value,
                                 DENSE_RANK () OVER (PARTITION BY ID ORDER BY some_value)
                                                                          rnk
                            FROM TEST))
           WHERE rnk = CEIL ((max_rnk + 1) / 2)
              OR rnk = FLOOR ((max_rnk + 1) / 2))
GROUP BY ID
Re: median [message #427595 is a reply to message #427594] Fri, 23 October 2009 07:46 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Quote:
I was writing my own stuff
You might have been better googling Wink
Re: median [message #427596 is a reply to message #427595] Fri, 23 October 2009 07:48 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
pablolee wrote on Fri, 23 October 2009 13:46
Quote:
I was writing my own stuff
You might have been better googling Wink


I'd have just typed median into the oracle documentation search personally. Smile
Re: median [message #427600 is a reply to message #427596] Fri, 23 October 2009 08:07 Go to previous message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Eh? you mean there's documentation on Oracle that you can search through? Wow! Wink
Previous Topic: to get rowcount of dynamically executed query
Next Topic: Not able to kill Oracle session
Goto Forum:
  


Current Time: Mon Sep 26 21:28:29 CDT 2016

Total time taken to generate the page: 0.11987 seconds