MEDIAN function
Someone asked whether Informix has a built-in MEDIAN function for a column. Answer: no. Art Kagel first posted SQL using GROUP BY/COUNT with FIRST 1, but Dave Griffen pointed out that returns the mode (most frequent value), not the median. Jonathon Wyza suggested the right approach: count the rows, then use SKIP n FIRST 1 (or FIRST 2 averaged, for an even row count). Art then posted a working SPL function (mymedian) that takes table and column names, counts rows, and uses a prepared SKIP/FIRST cursor, averaging the two middle values when the count is even.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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
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
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
Couldn't you do this in SPL sort of like:
select count(*) into num_records
from a
where blah;
num_records = round(num_records/2);
select first 1 skip num_records value
from a
where blah;
??
Jonathon Wyza
CX & CBORD System Administrator
CX Programmer/Analyst
Administrative Computing
Bethel College
(574)-257-3381
AIM: Iamwyza
jonathon.wyza@bethelcollege.edu
==============================
SLES 11x64 & IDS 11.50.FC6
"Don't document the problem, fix it."
- Atli Björgvin Oddsson
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of DAVE
GRIFFEN
Sent: Friday, September 03, 2010 1:34 PM
To: ids@iiug.org
Subject: Re: MEDIAN function [21166]
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.
Hmm, that doesn't account for the average of the middle 2 values. IF SPL has a
modular function then...
select count(*) into num_records
from a
where blah;
num_records = num_records/2;
if (num_records % 1 == .5)
select first 2 skip num_records avg(value)
from a
where blah;
else
select first 1 skip num_records value
from a
where blah;
this isn't anything close to real SPL but if you can apply this logic then I
think you're set.
Jonathon Wyza
CX & CBORD System Administrator
CX Programmer/Analyst
Administrative Computing
Bethel College
(574)-257-3381
AIM: Iamwyza
jonathon.wyza@bethelcollege.edu
==============================
SLES 11x64 & IDS 11.50.FC6
"Don't document the problem, fix it."
- Atli Björgvin Oddsson
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Wyza,
Jonathon
Sent: Friday, September 03, 2010 1:42 PM
To: ids@iiug.org
Subject: RE: MEDIAN function [21167]
Couldn't you do this in SPL sort of like:
select count(*) into num_records
from a
where blah;
num_records = round(num_records/2);
select first 1 skip num_records value
from a
where blah;
??
Jonathon Wyza
CX & CBORD System Administrator
CX Programmer/Analyst
Administrative Computing
Bethel College
(574)-257-3381
AIM: Iamwyza
jonathon.wyza@bethelcollege.edu
==============================
SLES 11x64 & IDS 11.50.FC6
"Don't document the problem, fix it."
- Atli Björgvin Oddsson
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of DAVE
GRIFFEN
Sent: Friday, September 03, 2010 1:34 PM
To: ids@iiug.org
Subject: Re: MEDIAN function [21166]
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.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
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