Cumulative Beta Distribution function as an SQL Query?

I'd like to implement a "Cumulative Beta Distribution" function as an SQL query.  Any ideas on how I'd do this?  Info on this function can be found here: http://en.wikipedia.org/wiki/Beta_distribution.

Thanks in advance!
nbb007Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
what would the output look like?

on the face of it if it s "simply" mathematics then you just write the equation as a function (set of functions)
and add it your select statement...

you need to translate the math/method into something that a non mathematican.statistian cann understand...

0
nbb007Author Commented:
Well, the output would be a number.  Excel has a built-in function called "BETADISTRIBUTION" that I use frequently, but I now need to be able to perform this calculation on data in an SQL Server database.  I was hoping that somebody might have this function defined as a query, as I'm not sure how to do it myself.  Is there any other info/data I could provide?
0
aburrCommented:
I would suggest that you move this question to the
Database section
under mySQL or Access
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

LowfatspreadCommented:
Can you confirm which database system this will run in eventually?
0
NovaDenizenCommented:
There's a method for calculating estimates of the alpha and beta parameters here:  http://en.wikipedia.org/wiki/Beta_distribution#Parameter_estimation

SELECT AVG(x) * (AVG(x)*(1 - AVG(x))/VAR_POP(x) - 1) AS est_alpha, (1 - AVG(x)) * ( AVG(x)*(1 - AVG(x))/VAR_POP(x) - 1) AS est_beta FROM thetable WHERE ....
0
NovaDenizenCommented:
I assumed you're working with mysql in my solution.  No guarantees about other databases.
0
nbb007Author Commented:
Yes, I'm using MySQL, but I already have queries for both Alpha and Beta:

SELECT  ((1-(dbo_rdm_port.PERSPVALUE/dbo_rdm_port.EXPVALUE))/(((dbo_rdm_port.STDDEVI+dbo_rdm_port.STDDEVC)/dbo_rdm_port.PERSPVALUE)*((dbo_rdm_port.STDDEVI+dbo_rdm_port.STDDEVC)/dbo_rdm_port.PERSPVALUE)))-(dbo_rdm_port.PERSPVALUE/dbo_rdm_port.EXPVALUE) AS ALPHA, (((1-(dbo_rdm_port.PERSPVALUE/dbo_rdm_port.EXPVALUE))/(((dbo_rdm_port.STDDEVI+dbo_rdm_port.STDDEVC)/dbo_rdm_port.PERSPVALUE)*((dbo_rdm_port.STDDEVI+dbo_rdm_port.STDDEVC)/dbo_rdm_port.PERSPVALUE)))-(dbo_rdm_port.PERSPVALUE/dbo_rdm_port.EXPVALUE))*((dbo_rdm_port.EXPVALUE/dbo_rdm_port.PERSPVALUE)-1) AS BETA

this really translates into:
alpha = (1 - u/cv^2) - u
beta = alpha(1-u)/u

where u = loss/expvalu
and     cv = (stdevi + stdevc)/loss

the problem is that I need the "Cumulative Beta Distribution" function to plug these alpha and beta parameters into...
0
NovaDenizenCommented:
Are you saying that you already have the alpha and beta values, and you want to calculate values of the cumulative distribution function for that alpha and beta?

 According to http://mathworld.wolfram.com/BetaDistribution.html you will need to implement the Regularized Beta Function (http://mathworld.wolfram.com/RegularizedBetaFunction.html).  That function is definied in terms of the Incomplete Beta Function (http://mathworld.wolfram.com/IncompleteBetaFunction.html) and the Beta Function (http://mathworld.wolfram.com/BetaFunction.html).  

The Incomplete Beta function is a nontrivial function to calculate, requiring an approximation to an infinite series.

The Beta function is easily defined in terms of factorials when alpha and beta are integers, but requires the use of the gamma function when either is not an integer.
0
nbb007Author Commented:
Yes, I need to calculate the cumulative beta distrubution for values of Alpha and Beta that I already have.  This can easily be done in Microsoft Excel with the BETADISTRIBUTION funtion, but I need to do this on a MySQL database instead of an Excel workbook.
0
NovaDenizenCommented:
MySQL is a database server, not a numerical processing engine.  I think this sort of calculation belongs in a specialized application.

If you really must calculate the Beta Distribution function within sql, then you would be best off implementing it in C and linking your custom function into mysql.  See http://dev.mysql.com/doc/refman/5.0/en/adding-functions.html
0
nbb007Author Commented:
Well, I was hoping that I could make the query cross-platform, i.e. run my Cumulative Beta Distribution SQL Query on a MySQL database, SQL Server 200/2005 database, MS Access database, Oracle database, etc.  Perhaps there is an approximation function that could be implemented as an SQL query?
0
NovaDenizenCommented:
You ask for something akin to plans for a scanning tunneling electron microscope that can be constructed in an afternoon using office supplies.  :)

I suggest you rethink the big picture about how you want your application to work, and put the difficult mathematical computations in a module that can handle them.
0
nbb007Author Commented:
Well, I actually have done that already.  I get the Alpha and Beta values from my database using the SQL query I previously indicated, and then I use a library I purchased from SyncFusion to calculate the Cumulative Beta Distribution for those Alpha and Beta values:
"Syncfusion.Windows.Forms.Chart.Statistics.UtilityFunctions.BetaCumulativeDistribution"

The problem is that the result of this Cumulative Beta Distribution calculation must be used in subsequent SQL queries, hence the wish to perform this calculation in SQL so I can keep everything on the database side.  My solution was to write the Cumulative Beta Distribution value calculated via my application back to the table I queried Alpha and Beta from.  This process is rather time consuming however, since the database has around 1 million records.

Does this sound like the best solution?
0
NovaDenizenCommented:
I'm not clear on the big picture of what you're doing.  If you need to calculate one distribution value for each value in your database, then it makes sense to automatically calculate it in an external module and store the value in the database.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming Theory

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.