Re: median function?
Hello, The original question from Michael is: Is there a SQL function available for finding the median value from a column of numbers? I see AVG() but no reference to medians. Thanks. Does the sql AVG() skip ruled out values by some non-parametric test? Every SQL description of AVG() at my disposal does not discuss non-paramteric tests or ruled out values. Can we assume that if sql had a MEDIAN() it would not be any more conditioned than the AVG() function? Is the column of numbers a series of samples? If so is the median of these samples what you want? What is your goal? What is the situation of your data? Are you comparing medians from different number series where the populations have different shapes? Then neither means nor variances nor medians may provide the proper comparative measures; and then nonparametric methods come into play. But if the shapes of the populations are similar why complicate matters and make your data analysis operation system dependent? On the other hand, from rereading our discussion I believe Sinisa (and he may confirm this) is correct in cautioning against implementing a median based on its definition(which is correct) as I suggested and not regard what you want to do with the median in context of the condition of your data. It can happen where statistical analysis is intended to work for a particular condition but eventually a user may stuff in data where the analysis will give them answers which they will use for years not knowing the underlying analysis is not using comparative measures intended for their data's condition. Eventually everybody may forget how the analysis works but use it blindly. Regards, Roger - Original Message - From: "Sinisa Milivojevic" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Saturday, December 01, 2001 9:36 AM Subject: Re: median function? > Michael writes: > > Umm does this mean there is no way to do what I need? Is it inappropiate > > to ask if it'd be difficult for such a function to be added into MySQL if > > it is the case that this is an impossible thing to do (or even just very > > challenging)? A median is a very common function to need in accounting I > > think. Thanks. > > > > > > *^*^*^* > > Michael McGlothlin <[EMAIL PROTECTED]> > > http://mlug.missouri.edu/~mogmios/projects/ > > > No, it is very easy to do. > > Add it as an UDF. This will not work on server running on Windows, > though. > > Take a look at udf_example.cc in sql/ subdir of the source. Most > notably, analyse : > > CREATE AGGREGATE FUNCTION avgcost RETURNS REAL SONAME "udf_example.so"; > > -- > Regards, >__ ___ ___ __ > / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer > /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ><___/ www.mysql.com > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: median function?
Michael writes: > Umm does this mean there is no way to do what I need? Is it inappropiate > to ask if it'd be difficult for such a function to be added into MySQL if > it is the case that this is an impossible thing to do (or even just very > challenging)? A median is a very common function to need in accounting I > think. Thanks. > > > *^*^*^* > Michael McGlothlin <[EMAIL PROTECTED]> > http://mlug.missouri.edu/~mogmios/projects/ No, it is very easy to do. Add it as an UDF. This will not work on server running on Windows, though. Take a look at udf_example.cc in sql/ subdir of the source. Most notably, analyse : CREATE AGGREGATE FUNCTION avgcost RETURNS REAL SONAME "udf_example.so"; -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: median function?
Umm does this mean there is no way to do what I need? Is it inappropiate to ask if it'd be difficult for such a function to be added into MySQL if it is the case that this is an impossible thing to do (or even just very challenging)? A median is a very common function to need in accounting I think. Thanks. *^*^*^* Michael McGlothlin <[EMAIL PROTECTED]> http://mlug.missouri.edu/~mogmios/projects/ On Thu, 29 Nov 2001, Sinisa Milivojevic wrote: > Christopher R. Jones writes: > > Correct > > The mean (average) depends on the distribution. If the distribution is > > either positively or negatively skewed, then the median will not equal the > > mean. The median of an ordered list is the middle value if the number of > > elements is odd. If the number of elements is even, then the median is the > > average of the two middle values. > > > > > > Yes, this is correct, but it is still sampling median. > > On the other hand, some non-parametric test might rule out some of the > values, so we are again in the beginning. > > -- > Regards, >__ ___ ___ __ > / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer > /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ><___/ www.mysql.com > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: median function?
Christopher R. Jones writes: > Correct > The mean (average) depends on the distribution. If the distribution is > either positively or negatively skewed, then the median will not equal the > mean. The median of an ordered list is the middle value if the number of > elements is odd. If the number of elements is even, then the median is the > average of the two middle values. > > Yes, this is correct, but it is still sampling median. On the other hand, some non-parametric test might rule out some of the values, so we are again in the beginning. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: median function?
Correct The mean (average) depends on the distribution. If the distribution is either positively or negatively skewed, then the median will not equal the mean. The median of an ordered list is the middle value if the number of elements is odd. If the number of elements is even, then the median is the average of the two middle values. >median (Websters) 1. being in the middle; medial, middle. 2. Statistics. >Designating a point so chosen in a series that half of the individuals in >the series are on one side of it, and half on the other. To illustrate the >distinction between median and average, suppose five persons have wages >respectively of $3, $4, $5, $7 and $11. The average wage is $6; the median >wage is $5. > >What statistical distribution is going to change the median from $5? What is >the dependency? > >Redundant numbers do not change the median? > >Roger > >P.S. In this case I prefer the max function.:) >- Original Message - >From: "Sinisa Milivojevic" <[EMAIL PROTECTED]> >To: <[EMAIL PROTECTED]> >Cc: <[EMAIL PROTECTED]> >Sent: Thursday, November 29, 2001 8:19 AM >Subject: Re: median function? > > > > Roger I Martin PhD writes: > > > Hi, > > > > > > Median as a function in sql does not exests that I know of. But if you >get a > > > resultset ordered by the column of numbers, you can then pick the median >at > > > the row index=n/2 where n is the total number of values in the column? > > > > > > Roger > > > > Nope . > > > > Median depends on statistical distribution. > > > > There is one that is valid for sampling distribution, but there are > > still many alternatives. > > > > This is something that could be done as an UDF. > > > > -- > > Regards, > >__ ___ ___ __ > > / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> > > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer > > /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus > ><___/ www.mysql.com > > > > > > - > > Before posting, please check: > >http://www.mysql.com/manual.php (the manual) > >http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail ><[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > > > > >- >Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > >To request this thread, e-mail <[EMAIL PROTECTED]> >To unsubscribe, e-mail <[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Christopher R. Jones, P.Eng. 14 Oneida Avenue Toronto, Ontario M5J 2E3 Tel. 416 203-7465 Fax. 416 203-8249 Email [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: median function?
median (Websters) 1. being in the middle; medial, middle. 2. Statistics. Designating a point so chosen in a series that half of the individuals in the series are on one side of it, and half on the other. To illustrate the distinction between median and average, suppose five persons have wages respectively of $3, $4, $5, $7 and $11. The average wage is $6; the median wage is $5. What statistical distribution is going to change the median from $5? What is the dependency? Redundant numbers do not change the median? Roger P.S. In this case I prefer the max function.:) - Original Message - From: "Sinisa Milivojevic" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, November 29, 2001 8:19 AM Subject: Re: median function? > Roger I Martin PhD writes: > > Hi, > > > > Median as a function in sql does not exests that I know of. But if you get a > > resultset ordered by the column of numbers, you can then pick the median at > > the row index=n/2 where n is the total number of values in the column? > > > > Roger > > Nope . > > Median depends on statistical distribution. > > There is one that is valid for sampling distribution, but there are > still many alternatives. > > This is something that could be done as an UDF. > > -- > Regards, >__ ___ ___ __ > / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer > /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ><___/ www.mysql.com > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: median function?
Roger I Martin PhD writes: > Hi, > > Median as a function in sql does not exests that I know of. But if you get a > resultset ordered by the column of numbers, you can then pick the median at > the row index=n/2 where n is the total number of values in the column? > > Roger Nope . Median depends on statistical distribution. There is one that is valid for sampling distribution, but there are still many alternatives. This is something that could be done as an UDF. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: median function?
Hi, Median as a function in sql does not exests that I know of. But if you get a resultset ordered by the column of numbers, you can then pick the median at the row index=n/2 where n is the total number of values in the column? Roger - Original Message - From: "Michael" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, November 29, 2001 8:00 AM Subject: median function? > Is there a SQL function available for finding the median value from a > column of numbers? I see AVG() but no reference to medians. Thanks. > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php