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.)