Solved

Cumulative Beta Distribution function as an SQL Query?

Posted on 2007-03-26
14
2,748 Views
Last Modified: 2013-11-13
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!
0
Comment
Question by:nbb007
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 18796366
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
 

Author Comment

by:nbb007
ID: 18796576
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
 
LVL 27

Expert Comment

by:aburr
ID: 18797751
I would suggest that you move this question to the
Database section
under mySQL or Access
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 50

Expert Comment

by:Lowfatspread
ID: 18799331
Can you confirm which database system this will run in eventually?
0
 
LVL 22

Expert Comment

by:NovaDenizen
ID: 18800149
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
 
LVL 22

Expert Comment

by:NovaDenizen
ID: 18800153
I assumed you're working with mysql in my solution.  No guarantees about other databases.
0
 

Author Comment

by:nbb007
ID: 18801108
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
 
LVL 22

Expert Comment

by:NovaDenizen
ID: 18801587
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
 

Author Comment

by:nbb007
ID: 18801856
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
 
LVL 22

Expert Comment

by:NovaDenizen
ID: 18801956
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
 

Author Comment

by:nbb007
ID: 18802192
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
 
LVL 22

Expert Comment

by:NovaDenizen
ID: 18803025
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
 

Author Comment

by:nbb007
ID: 18805245
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
 
LVL 22

Accepted Solution

by:
NovaDenizen earned 500 total points
ID: 18807916
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
"Disruption" is the most feared word for C-level executives these days. They agonize over their industry being disturbed by another player - most likely by startups.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question