Home » SQL & PL/SQL » SQL & PL/SQL » median (10g)
median Fri, 23 October 2009 07:16
 ayush_anand Messages: 417Registered: 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.
```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
 Michel Cadot Messages: 65082Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 pablolee Messages: 2844Registered: 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
 ayush_anand Messages: 417Registered: November 2008 Senior Member
many many Thanks Guys

I was writing my own stuff
```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
 pablolee Messages: 2844Registered: May 2007 Location: Scotland Senior Member
Quote:
I was writing my own stuff
You might have been better googling
Re: median [message #427596 is a reply to message #427595] Fri, 23 October 2009 07:48
 cookiemonster Messages: 12793Registered: 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

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

Current Time: Fri Jul 21 05:40:09 CDT 2017

Total time taken to generate the page: 0.16714 seconds