Chuck,
Thanks very much for the direction on the v5r3 stored procedure example
which includes the Median logic. I will use it.
As for the quartile question, it would be desirable to have the average
value represented as the calc result.
However, something came to me last night that I think will allow me to
approximate the quartiles using the Mean and Standard Deviation, for which
there are already SQL statements available and can be used without a stored
procedure. While the result is an approximation, I think it may serve my
purpose well enough.
Thanks so much for the time you put into consideration of my question. I
very much appreciate it.
Best Regards,
Thomas Garvey
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Tuesday, February 28, 2012 10:05 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: Calc median and quartiles in SQL on IBM i
On 28-Feb-2012 09:33 , Thomas Garvey wrote:
I'm trying to keep my code compatible back to v5r3, but may be
forced to go up to v5r4 (I have a v5r4 system and a v7r1 system).
The v5r3 docs give some stored procedures as examples [though not
generically] to calculate and return [and in one example also to return a
result set of values greater than] the median value for a column.
Search MEDIAN in the v5r3 InfoCenter.
I have source for a SQL scalar function I have used that returns the
median value for a named TABLE and COLUMN using dynamically prepared SELECT
statements for the COUNT and the ordered row data; used in code that would
SET :HV = MEDIAN('MyTableName', 'ColumnName');. The source for the CREATE
FUNCTION is not /pretty/ nor robust, but I can post that if desired.
Calculating a quartile however, would need further definition. What
rules must be followed to determine the value, other than the median which
is presumed to be [and the same algorithm my MEDIAN uses] either the middle
value [for an odd number of values] or the average of the two middle values
[for an even number of values]?
So would the following produce a desirable result for the value between
the first and second quartile; as denoted by V?
? Even number of ordered values:
{ v1, v2, v3, v4, v5, v6, v7, v8 }
{ v1, v2, v3, v4 } { v5, v6, v7, v8 }
V=(v2+v3)/2
? Odd number of ordered values;
Median of original set becomes part of both new sets:
{ v1, v2, v3, v4, v5, v6, v7, v8, v9 }
{ v1, v2, v3, v4, v5 } { v5, v6, v7, v8, v9 }
V=v3
Or perhaps the following produces a desirable result for the value
between the first and second quartile; as denoted by V?
? Even number of ordered values:
{ v1, v2, v3, v4, v5, v6, v7, v8 }
{ v1, v2, v3, v4 } { v5, v6, v7, v8 }
V=(v2+v3)/2
? Odd number of ordered values;
Median of original set omitted from both new sets:
{ v1, v2, v3, v4, v5, v6, v7, v8, v9 }
{ v1, v2, v3, v4 } { v6, v7, v8, v9 }
V=(v2+v3)/2
Or perhaps yet another algorithm is more desirable than either of those?
Regards, Chuck
Charles Wilt on Tuesday, February 28, 2012 11:14 AM wrote:
You should be able to adapt the code found online...
Probably would be easiest if you're running on 7.1, but even 5.4 has
PARTITION/RANK OVER()...
On Tue, Feb 28, 2012 at 11:59 AM, Thomas Garvey wrote:
Anybody got an SQL statement that will (reliably) select the median
value from a table of numeric values? By reliably I mean no matter
what the count of numeric values is (even or odd). It's got to work
on the IBM i.
Even better, how about calculating the 1st (25% of values are less
than this) and 3rd quartiles (75% of values are less than
this) from the table?
These have to work on the IBM i. I've seen several techniques
online but none that use only the features of IBM SQL (no OVER
phrase, etc.)
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
