RE: How do I get the median value ?
Gary
What you seem to be looking for is the "Financial Median" which is =
calculated as follows:
1. Divide the dataset into 2 halves of equal size such that all values =
in the lower half are lower than any value in the upper half.
2. The median is the average of the highest value in the upper half and =
the lowest value in the lower half.
I dont think you can write a single SQL that will do this for you. So =
one option for you would be to write a stored procedure that will return =
the median for the table if you want to minimize network traffic. I have =
written a Stored Procedure that does this. However the procedure may not =
be terribly efficient since it does a lot of aggregation.
You may want to investigate keeping an aggregate table that will get =
updated using a INSERT/UPDATE/DELETE trigger on your 90,000 row table =
and just read the aggregate table for the median.
I have also made an assumption that there is a transaction counter on =
your table with no holes (or relatively few so that the median =
calculation is approximate within tolerable limits). This may not be the =
case and if so, YMMV.
So assuming a table such as:
CREATE TABLE trans (
trans_count INTEGER,
trans_value DECIMAL(5,2),
trans_others CHAR(50));
Heres the Stored Procedure that will do the job for you:
CREATE PROCEDURE "informix".myMedian() RETURNING DECIMAL(5,2);
DEFINE myMedianVal DECIMAL(5,2);
DEFINE t_count, t_hcount INTEGER;
DEFINE t_minval, t_maxval DECIMAL(5,2);
SELECT COUNT(*) INTO t_count FROM trans; LET t_hcount =3D t_count / 2;
IF (t_count =3D (t_hcount * 2)) THEN -- even #rows
SELECT MAX(trans_value) INTO t_maxval
FROM trans
WHERE trans_count <=3D t_hcount;
SELECT MIN(trans_value) INTO t_minval
FROM trans
WHERE trans_count > t_hcount;
ELSE -- odd #rows
SELECT MAX(trans_value) INTO t_maxval
FROM trans
WHERE trans_count <=3D t_hcount;
SELECT MIN(trans_value) INTO t_minval
FROM trans
WHERE trans_count > (t_hcount + 1);
END IF
LET myMedianVal =3D (t_minval + t_maxval) / 2;
RETURN myMedianVal;
END PROCEDURE;
To call this procedure use a SQL like:
SELECT myMedian() FROM systables
WHERE tabname =3D "systables";
HTH
Sujit Pal
----------
From: =
gary.vaughan@workcover.nsw.gov.au[SMTP:gary.vaughan@workcover.nsw.gov.au]=
Sent: Tuesday, July 29, 1997 7:52 PM
To: informix-list@rmy.emory.edu; jonathan.stagg@workcover.nsw.gov.au
Subject: How do I get the median value ?
Hi all,
=20
Within a powerbuilder report I want to calculate a median value =
from a=20
table. However the table has more rows (about 90,000 growing by =
approx.=20
2,000 per month) in it than I wish to retrieve across the network =
so I want=20
to calculate the median on the server using SQL and return only the =
one row=20
per group. It is running on a DEC alpha using 7.20 Engine
=20
How do I get the median value using Informix SQL?
e.g..
row # cost
1 $ 10
2 $ 10
3 $ 10
4 $ 11
5 $ 12
6 $ 15
7 $ 30
=20
average =3D $ 14
median =3D $ 11
=20
Thanks in advance
Gary Vaughan
gary.vaughan@workcover.nsw.gov.au