IDS Forum

Re: MEDIAN function

Posted By: Art Kagel
Date: Friday, 3 September 2010, at 4:57 p.m.

In Response To: Re: MEDIAN function (DAVE GRIFFEN)

Sorry, you are correct, I gave you the "mode" not the median. This SPL
function will do the trick:

CREATE FUNCTION "art".mymedian ( atable char(128), acolumn char(128) )

RETURNING int8 AS value;
define sel char(1024);
define cnt, skipit int8;
define ret, hold, get int8;

let sel = "select count(*) from " || atable;
prepare sel_s from sel;
declare sel_c cursor for sel_s;
open sel_c;
fetch sel_c into cnt;
close sel_c;
free sel_s;
free sel_c;

let skipit = cnt / 2;
if mod( cnt, 2 ) == 0 then
return 'in mod 0' with resume;

let skipit = skipit - 1;

let get = 2;
else

let get = 1;
end if

let sel = "select skip " || skipit || " first " || get || acolumn || ' from
' || atable;
prepare sel2_s from sel;
declare sel2_c cursor for sel2_s;
open sel2_c;
fetch sel2_c into hold;
let ret = hold;
if mod( cnt, 2 ) == 0 then

fetch sel2_c into hold;

let ret = (ret + hold) / 2;
end if;

close sel2_c;
free sel2_c;
free sel2_s;

return ret;
END FUNCTION;

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.

On Fri, Sep 3, 2010 at 1:34 PM, DAVE GRIFFEN <dgriffen@finishline.com>wrote:

> Art,
> Your sql is returning the most common value in the set. This doesn't really
> match the definition of median with which I'm familiar.
>
> median(a): relating to or constituting the middle value of an ordered set
> of
> values (or the average of the middle two in a set with an even number of
> values);
>
> Given your example set the most common value is coincidentally the same as
> the
> median value. However, given a set such as...
>
> insert into valtest values( 1 );
> insert into valtest values( 1 );
> insert into valtest values( 2 );
> insert into valtest values( 2 );
> insert into valtest values( 3 );
> insert into valtest values( 4 );
> insert into valtest values( 4 );
> insert into valtest values( 5 );
> insert into valtest values( 5 );
> insert into valtest values( 5 );
> insert into valtest values( 5 );
>
> Your sql returns 5. Whereas the median 6th of 11 records has a value of 4.
>
> I haven't been able to work out a good way to return a median via sql.
> However, I think it is more complicated than what you presented.
>
> Dave Griffen
>
> Art Kagel wrote:
> >
> >No median function. But the SQL for a median isn't hard:
> >
> >create table valtest( value int );
> >insert into valtest values( 1 );
> >insert into valtest values( 1 );
> >insert into valtest values( 2 );
> >insert into valtest values( 2 );
> >insert into valtest values( 2 );
> >insert into valtest values( 2 );
> >insert into valtest values( 2 );
> >insert into valtest values( 3 );
> >insert into valtest values( 4 );
> >insert into valtest values( 4 );
> >insert into valtest values( 5 );
> >insert into valtest values( 5 );
> >
> >select first 1 t.value
> >from valtest t, (
> >
> >select first 1 value, count(*) from valtest group by 1 order by 2 desc)
> >as m
> >where t.value = m.value;
> >
> >value
> >
> >2
> >
> >1 row(s) retrieved.
> >
> >Or using temp table:
> >> select value, count(*) med from valtest group by 1 into temp vt;
> >
> >5 row(s) retrieved into temp table.
> >
> >> select first 1 t.value
> >> from valtest t, vt m
> >> where t.value = m.value
> >> and m.med = (select max(med) from vt);
> >
> >value
> >
> >2
> >
> >1 row(s) retrieved.
> >
> >Art S. Kagel
> >Advanced DataTools (www.advancedatatools.com)
> >IIUG Board of Directors (art@iiug.org)
> >
> >Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> >do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
> >organization with which I am associated either explicitly, implicitly, or
> by
> >inference. Neither do those opinions reflect those of other individuals
> >affiliated with any entity with which I am affiliated nor those of the
> >entities themselves.
> >
> >On Thu, Sep 2, 2010 at 11:36 PM, Walter Lowich <walterlowich@yahoo.com
> >wrote:
> >
> >> Does Informix have a function to compute the median value for a given
> >> column
> >> or columns ? If so, how does it operate ? If not, has anyone written
> such a
> >> function ?
> >>
> >> Thanks.
> >>
> >> Walt Lowich
> >>
> >>
> >>
> >>
>
> >*******************************************************************************
> >> Forum Note: Use "Reply" to post a response in the discussion forum.
> >>
> >>
> >
> >--0016368e1b8a880fc7048f58a8bc
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--001636c927f0005963048f612ebe

Messages In This Thread

• MEDIAN function
  Walter Lowich -- Thursday, 2 September 2010, at 11:36 p.m.
  • Re: MEDIAN function
    Art Kagel -- Friday, 3 September 2010, at 6:47 a.m.
    • Re: MEDIAN function
      DAVE GRIFFEN -- Friday, 3 September 2010, at 1:34 p.m.
      • RE: MEDIAN function
        Wyza, Jonathon -- Friday, 3 September 2010, at 1:42 p.m.
        • RE: MEDIAN function
          Wyza, Jonathon -- Friday, 3 September 2010, at 1:44 p.m.
      • Re: MEDIAN function
        Art Kagel -- Friday, 3 September 2010, at 4:57 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.