median

median

Post by Charles W. Schal » Fri, 27 Jan 1995 14:28:10



Normal aggregate functions in Ingres/SQL include count, sum, avg, max,
and min. Has anyone tried to obtain a median value from a table
of data? I imagine one would have to use a view or something, but
has anyone done this?

Thanks
CWS

 
 
 

median

Post by Roy Ha » Sat, 28 Jan 1995 01:19:07



: Normal aggregate functions in Ingres/SQL include count, sum, avg, max,
: and min. Has anyone tried to obtain a median value from a table
: of data? I imagine one would have to use a view or something, but
: has anyone done this?

I have tried this and in the end decided it was just too difficult
to be practical.  I don't think it is possible even in principal using
SQL alone.  

For a set of N data, the median is the element indexed as (N+1)/2
(or the mean of the two middle values when N is even).  This poses
two problems: you have to number all of your data in rank order, and
you have to use a different algorithm depending on whether N is even
or odd.  It is not impossibly difficult to sort and number the data
using SQL, but it is achingly laborious to code the SQL and exceedingly
computationally intensive for even modest values of N.  Then, once you
have created a temporary table with your numbered data, you have to
decide which of the two algorithms to use, but SQL has no conditional
execution mechanism, so you have to put together a union with two
mutually exclusive WHERE clauses to fake it out.

In the end you are left with something that looks like hell, takes
forever to execute, and leaves you sweaty, angry, and exhausted.
I decided it was a waste of my energy and a bad solution, and wrote
a shell script that did the job in a trice.

--Roy Hann

 
 
 

median

Post by Colleen Babcoc » Sun, 29 Jan 1995 18:27:11


Chuck,

I've been out of the office for a while and I'm not sure that you got an
answer, so here's mine.  No I have not figured out a way to get the median
using ingres/sql.  What I do for basic statistics is use RDB, the reducetbl
command works well in combination with tsql to retrieve the data.

Let me know if you want some help with this, Thanks Colleen

On Wed, 25 Jan 1995 20:19:01 GMT
"Charles W. Schalk" said:

Quote:> Normal aggregate functions in Ingres/SQL include count, sum, avg, max,
> and min. Has anyone tried to obtain a median value from a table
> of data? I imagine one would have to use a view or something, but
> has anyone done this?

> Thanks
> CWS

 
 
 

1. Median Function

I am trying to write a median function, but I'm very new to user
defined functions in SQL Server, so I'd appreciate any guidance anyone
can give me.

I need to write a median function so that it may be used inline in
queries, but with different criteria, as in:

SELECT MEDIAN(SELECT NumField FROM TBLB WHERE TypeOfRecord='AAA') AS
MedianValue

or maybe

SELECT MEDIAN(NumField) As MedianValue FROM TBLB WHERE
TypeOfRecord='AAA'

Can you pass a result set or something similar *TO* a user defined
function?  I can't seem to find any (productive) documentation on
this.  I have found references to RETURNING a result set, but not
relating to using a set as a parameter.

Any info is appreciated.

Thanks,
Klynt Klimek

2. logsegment full :(

3. Using @@ROWCOUNT/ median function

4. Help - log reader agent error!

5. How to find Median and Mode By SQL?

6. Help: Data Lost!

7. Median

8. problem after not enough space in harddisk

9. OLAP Calculated Member - Median Function

10. Median for groups...

11. Median function?

12. Calc Member - Median

13. Median calc-member - filter out some rows