SQL Server中的BetaInv函数
本文关键字：函数 BetaInv 中的 Server SQL | 更新日期: 2023-09-27 18:06:30
类似的问题:在MySQL中我需要使用BetaInv函数在SQL Server存储过程中。
Excel的BETAINV 函数描述如下:有没有人知道TSQL中有类似的东西，或者你会把它包装在CLR .NET管理的SQL用户定义函数中?
我真的需要在存储过程中使用它，而不是在使用存储过程检索数据后在c#端执行代码，因为我应该将所有逻辑保留在db服务器上以便更好地重用。
我可以假设在SQL Server中运行的。net管理的udf会像正常的本地TSQL函数一样快吗?
谢谢!
最后我自己实现了整个函数，这里是源代码，以防有人需要:
public static class UDFs
{
private const int MAXIT = 100;
private const double EPS = 0.0000003;
private const double FPMIN = 1.0E-30;
[SqlFunction(Name = "BetaInv", DataAccess = DataAccessKind.Read)]
public static SqlDouble BetaInv(SqlDouble p, SqlDouble alpha, SqlDouble beta, SqlDouble A, SqlDouble B)
{
return InverseBeta(p.Value, alpha.Value, beta.Value, A.Value, B.Value);
}
private static double InverseBeta(double p, double alpha, double beta, double A, double B)
{
double x = 0;
double a = 0;
double b = 1;
double precision = Math.Pow(10, -6); // converge until there is 6 decimal places precision
while ((b - a) > precision)
{
x = (a + b) / 2;
if (IncompleteBetaFunction(x, alpha, beta) > p)
{
b = x;
}
else
{
a = x;
}
}
if ((B > 0) && (A > 0))
{
x = x * (B - A) + A;
}
return x;
}
private static double IncompleteBetaFunction(double x, double a, double b)
{
double bt = 0;
if (x <= 0.0)
{
return 0;
}
if (x >= 1)
{
return 1;
}
bt = System.Math.Exp(Gammln(a + b) - Gammln(a) - Gammln(b) + a * System.Math.Log(x) + b * System.Math.Log(1.0 - x));
if (x < ((a + 1.0) / (a + b + 2.0)))
{
// Use continued fraction directly.
return (bt * betacf(a, b, x) / a);
}
else
{
// Use continued fraction after making the symmetry transformation.
return (1.0 - bt * betacf(b, a, 1.0 - x) / b);
}
}
private static double betacf(double a, double b, double x)
{
int m, m2;
double aa, c, d, del, h, qab, qam, qap;
qab = a + b; // These q’s will be used in factors that occur in the coe.cients (6.4.6).
qap = a + 1.0;
qam = a - 1.0;
c = 1.0; // First step of Lentz’s method.
d = 1.0 - qab * x / qap;
if (System.Math.Abs(d) < FPMIN)
{
d = FPMIN;
}
d = 1.0 / d;
h = d;
for (m = 1; m <= MAXIT; ++m)
{
m2 = 2 * m;
aa = m * (b - m) * x / ((qam + m2) * (a + m2));
d = 1.0 + aa * d; //One step (the even one) of the recurrence.
if (System.Math.Abs(d) < FPMIN)
{
d = FPMIN;
}
c = 1.0 + aa / c;
if (System.Math.Abs(c) < FPMIN)
{
c = FPMIN;
}
d = 1.0 / d;
h *= d * c;
aa = -(a + m) * (qab + m) * x / ((a + m2) * (qap + m2));
d = 1.0 + aa * d; // Next step of the recurrence (the odd one).
if (System.Math.Abs(d) < FPMIN)
{
d = FPMIN;
}
c = 1.0 + aa / c;
if (System.Math.Abs(c) < FPMIN)
{
c = FPMIN;
}
d = 1.0 / d;
del = d * c;
h *= del;
if (System.Math.Abs(del - 1.0) < EPS)
{
// Are we done?
break;
}
}
if (m > MAXIT)
{
return 0;
}
else
{
return h;
}
}
public static double Gammln(double xx)
{
double x, y, tmp, ser;
double[] cof = new double[] { 76.180091729471457, -86.505320329416776, 24.014098240830911, -1.231739572450155, 0.001208650973866179, -0.000005395239384953 };
y = xx;
x = xx;
tmp = x + 5.5;
tmp -= (x + 0.5) * System.Math.Log(tmp);
ser = 1.0000000001900149;
for (int j = 0; j <= 5; ++j)
{
y += 1;
ser += cof[j] / y;
}
return -tmp + System.Math.Log(2.5066282746310007 * ser / x);
}
}
}
正如您在代码中看到的，SqlFunction正在调用InverseBeta私有方法，该方法使用其他几个方法来完成这项工作。
结果与Excel相同。