Useless stats (how to calculate the median)
Hi folks, Due to some ineptitude upstream, I am required to calculate the median of a set of values. Anybody know of a straight forward way of doing this? TIA Steve --------------------------------------------------------- Steve Roach: Remove NOSPAM from address to reply: steve_roach@NOSPAMibm.net steve_roach@NOSPAMhotmail.com
Stephen Roach wrote:
> Hi folks,
>
> Due to some ineptitude upstream, I am required to calculate the median
> of a set of values. Anybody know of a straight forward way of doing
> this?
>
> TIA
>
> Steve
>
> ---------------------------------------------------------
> Steve Roach: Remove NOSPAM from address to reply:
> steve_roach@NOSPAMibm.net
> steve_roach@NOSPAMhotmail.com
The median as I understand, would be the middle value of an ordered set.
i.e
10 14 15 28 29 would have the median 15 in which case if your select
statement is something like
select count(stuf) into var1 from ..........
where ..........
let j=(var1+1)/2
This would give you a count for the middle number if the var1 is an odd
number, (if var1 is even you have to decide which number is middle, I
leave that to you.
That would give you the total numbers available to you (var1 is an
integer)
You have then to prepare a cursor:
let sql_text = select stuff from .......... where ......... order by stuff
prepare xyz_cur from sql_text
let i=1
foreach xyz_cur into var2[i]
if i > var1/2
then
exit foreach
end if
let i=i+1
end foreach
note var2 is an array the element var2[i-1] is the median you are looking
for.
--
Compliments of QueriX
--------------------------------------------------------------------------------------------------
QueriX 4GL Compilers are Informix 4GL Compatible, and Connection to other
RDBMS such as Oracle.
Hydra 4GL Compiler Compile once, run everywhere
Phoenix Windows GUI.
Chimera Java GUI The only GUI you will ever need...
Arachne Web Technology
For more details visit: http://www.querix.com/
---------------------------------------------------------------------------------------------------
Not exactly straight forward, but fairly straight :-)
unload to junk
select 0, col_to_be_medianed from <source_table>
where <whatever>
order by 2;
create temp table t1 (
item_no serial,
col_to_be_medianed integer
) with no log;
load from junk
insert into t1;
select avg(col_to_be_medianed) from t1
where item_no > (select (count(*)/2) - 1 from t1)
and item_no < (select (count(*)/2) + 1 from t1);
Rudy
Stephen Roach wrote:
> Hi folks,
>
> Due to some ineptitude upstream, I am required to calculate the median
> of a set of values. Anybody know of a straight forward way of doing
> this?
>
> TIA
>
> Steve
>
> ---------------------------------------------------------
> Steve Roach: Remove NOSPAM from address to reply:
> steve_roach@NOSPAMibm.net
> steve_roach@NOSPAMhotmail.com