SQL Server相当于Excel的TINV函数 [英] SQL Server equivalent of Excel's TINV function
问题描述
我试图找到X的t分布的两尾逆。可以在Excel中使用TINV函数找到它，但我需要在SQL Sever中实现。
TINV函数语法具有以下参数：
概率：与两尾学生t分布相关的概率。
Deg_freedom ：用来描述特征的自由度数
例如：
选择tinv（0.054645，60 ）;
------------------
-1.9599994129833
-（返回1行）
SQL-Server不包含很多统计功能。
tinv 在SQL Server中不存在。
添加tinv函数的唯一方法是使用CLR函数。
因此，问题将其自身简化为如何使用SQL-Server中允许的C＃子集来计算tinv？。
如果使用的是.NET 4.0，则可以使用 System.Web.DataVisualization.dll <中的图表类/ code>
例如
var someChart = new System.Web.UI.DataVisualization.Charting.Chart（）;
double res = someChart.DataManipulator.Statistics.InverseTDistribution（.05，15）;
//2.131449546
但是，您可能不希望有这样的开销。
因此，您必须将其从 Math.NET中删除的源代码（MIT / X11许可证）。
StudentT dist = new StudentT（0.0,1.0,7.0）;
double prob = dist.CumulativeDistribution（1.8946）;
应得出0.95
您需要反函数，您将需要
StudentT.InvCDF（双重位置，双重规模，双重自由，双重p）
location：分布的位置（μ）。
标度：分布的标度（σ）。范围：σ>0。
自由度：分布的自由度（ν）。范围：ν>0。
p：计算逆累积密度的位置。
[Microsoft.SqlServer.Server.SqlFunction]
公共静态System.Data.SqlTypes.SqlDouble TInv（双概率，int degFreedom）
{
double result = 0.00;
try
{
结果= fnInverseTDistribution（degFreedom，概率）;
}
抓
{
//投掷； //可以选择抛出/日志/忽略/无论什么
}
返回结果；
}
使用DataVisualization，就像这样：
[Microsoft.SqlServer.Server.SqlFunction]
公共静态System.Data.SqlTypes.SqlDouble TInv（双概率，int degFreedom）
{
双重结果= 0.00；
try
{
var someChart = new System.Web.UI.DataVisualization.Charting.Chart（）;
结果= someChart.DataManipulator.Statistics.InverseTDistribution（概率，degFreedom）;
}
抓住
{
//抛出； //可以选择抛出/日志/忽略/无论什么
}
返回结果；
}
DataVisualization技巧在SQL Server上不起作用，因为d需要添加对System.Web的引用，而在SQL Server中则无法做到。
此外，excel具有很多类似的功能，例如tinv，t.inv，T.INV.2S等，因此请务必选择正确的功能。 / p>
编辑：
找到了更多信息：
http://numerics.mathdotnet.com/api/MathNet.Numerics/ExcelFunctions.htm#TIn
在Math.NET中有一个特殊的类，叫做ExcelFunctions，您可以实际使用：
MathNet.Numerics.ExcelFunctions.TInv（1.1，55）;
所以解决问题的方法是
[Microsoft.SqlServer.Server.SqlFunction]
公共静态System.Data.SqlTypes.SqlDouble TInv（双概率，int degFreedom）
{
double result = 0.00;
试试
{
结果= MathNet.Numerics.ExcelFunctions.TInv（概率，degFreedom）；
}
抓
{
//投掷； //可以选择抛出/日志/忽略/无论什么
}
返回结果；
}
实际上与
$ b相同$ b
[Microsoft.SqlServer.Server.SqlFunction]
公共静态System.Data.SqlTypes.SqlDouble TInv（双概率，int degFreedom）
{
双重结果= 0.00；
try
{
结果= -StudentT.InvCDF（0d，1d，degFreedom，概率/ 2）;
}
抓
{
//投掷； //可以选择抛出/日志/忽略/无论什么
}
返回结果；
}
因此，现在您从
中获取Math.Net的源代码< a href = https://github.com/mathnet/mathnet-numerics rel = nofollow> https://github.com/mathnet/mathnet-numerics
并拖放使用CRL函数将mathnet-numerics / src / Numerics /的内容（或所需的部分）放入项目中，并完成。
拥有CLR dll后，您进入SSMS并执行：
EXEC dbo.sp_configure'clr enabled'，1使用
重新配置，从'C：\SQLServerStatistics.dll'中创建SQLServerStatistics，并具有PERMISSION_SET = SAFE
成功之后，您仍然必须在SQL-Server中注册该函数。
创建函数[dbo]。[tinv]（@ prob float，@degFreedom int）
使用执行返回Float AS CALLER
AS
外部名称[SQLServerStatistics]。[Functions]。[TInv]
有关更多信息，请参见此文章。
>如果要将Dll带到生产服务器上，则需要从字节数组字符串创建程序集，如下所示：
创建组件[MyFunctions]
授权[dbo]
从0x4D5A90000 [这里很长的字符串...];
您可以从字节数组中创建十六进制字符串，如下所示：
byte [] bytes = System.IO.File.ReadAllBytes（@ C：\SQLServerStatistics.dll）;
0x + BitConverter.ToString（bytes）.Replace（-，）
我已经在github上的此处上传了整个解决方案。
然后您可以运行以下函数：
SELECT dbo.tinv（0.54645，60）
==> 0.606531559343638
脚本生成器工具自动为您构建安装脚本。
看起来像这样：
> sp_configure显示高级选项，1；重新配置；去
> sp_configure'clr enabled'，1;重新配置；去
>
>
> DECLARE @sql nvarchar（MAX）SET @sql =‘ALTER DATABASE’+
> QUOTENAME（DB_NAME（））+’SET TRUSTWORTHY ON;’
> -PRINT @sql; EXECUTE（@sql）;去
>
>
> -从备份还原数据库时还原sid ... DECLARE @Command NVARCHAR（MAX）=数据库的N'ALTER授权：:<<<<<<<" DatabaseName>>>至
> [<< LoginName>>]] SELECT @Command = REPLACE（REPLACE（@Command，
> N'<< DatabaseName>>'，SD.Name），N'<< ; LoginName>>'，SL.Name）FROM
> master..sysdatabases AS SD JOIN master..syslogins AS SL ON SD.SID
> = SL.SID
>
> SD.Name = DB_NAME（）
>
> -PRINT @command EXECUTE（@command）GO
>
>如果存在（从sys.objects中选择SELECT * FROM sys.objects，则object_id = OBJECT_ID（N'[dbo]。[TDist]'）AND键入（N'FN'，N'IF'，N'TF'，N'FS'，
> N'FT'））DROP FUNCTION [dbo]。[TDist] GO
>
>如果存在（从sys.objects中选择SELECT * FROM sys.objects，而object_id = OBJECT_ID（N'[dbo]。[TInv]'））并键入（N'FN'，N'IF'，N'TF'，N'FS'，
> N'FT'））下降功能[dbo]。[TInv]转到
>
>
>
>如果存在（从sys.assemblies asms中选择*，则asms.name =
> N’SQLServerStatistics'and is_user_defined = 1）。DROP ASSEMBLY
> [SQLServerStatistics] GO
>
>
>创建组件SQLServerStatistics授权[dbo]
>从 c：\用户\管理员\文档\Visual Studio 2013\项目\SqlServerStatistics\ClrCreationScriptGenerator\bin\Debug\SqlServerStatistics.dll'
> WITH PERMISSION_SET =不安全GO
>
>
>创建函数[dbo]。[TDist]（@ x AS浮点数，@ degFreedom AS整数，@ tails
> AS整数）
>返回值以EXECUTE AS CALLER作为外部名称[SQLServerStatistics]。[SqlServerStatistics.ExcelFunctions]。[TDist] GO
>
>
>
> CREATE FUNCTION [dbo]。[TInv]（@ probability AS float，@degFreedom AS
> int）
>返回值以执行者作为外部名称[SQLServerStatistics]。[SqlServerStatistics.ExcelFunctions]。[TInv] GO
I am trying to find two-tailed inverse of the X's t-distribution. It can be find using TINV function in Excel but I need this to achieve in SQL Sever. Please suggest me idea.
The TINV function syntax has the following arguments:
Probability : The probability associated with the two-tailed Student's t-distribution.
Deg_freedom : The number of degrees of freedom with which to characterize the distribution.
Ex:
select tinv( 0.054645, 60);
-- -----------------
-- 1.9599994129833
-- (1 row returned)
SQL-Server does not incorporate a lot of statistical functions.
tinv is not present in SQL-Server.
The only way to add a tinv function, is to use a CLR-Function.
Thus, the problem reduces itselfs to "How do I calculate tinv with the subset of C# allowed in SQL-Server ?".
If you're on .NET 4.0, you could use the chart-class in System.Web.DataVisualization.dll
e.g.
var someChart = new System.Web.UI.DataVisualization.Charting.Chart();
double res = someChart.DataManipulator.Statistics.InverseTDistribution(.05, 15);
//2.131449546
However, you probably don't want that overhead. So you'll have to rip it out of Math.NET's SourceCode (MIT/X11 License).
StudentT dist = new StudentT(0.0,1.0,7.0);
double prob = dist.CumulativeDistribution(1.8946);
Which should result in 0.95
Since you need the inverse, you'll need
StudentT.InvCDF(double location, double scale, double freedom, double p)
location: The location (μ) of the distribution.
scale: The scale (σ) of the distribution. Range: σ > 0.
freedom: The degrees of freedom (ν) for the distribution. Range: ν > 0.
p: The location at which to compute the inverse cumulative density.
[Microsoft.SqlServer.Server.SqlFunction]
public static System.Data.SqlTypes.SqlDouble TInv(double probability, int degFreedom)
{
double result = 0.00;
try
{
result = fnInverseTDistribution(degFreedom, probability);
}
catch
{
// throw; // Optionally throw/log/ignore/whatever
}
return result;
}
using DataVisualization, this goes like this:
[Microsoft.SqlServer.Server.SqlFunction]
public static System.Data.SqlTypes.SqlDouble TInv(double probability, int degFreedom)
{
double result = 0.00;
try
{
var someChart = new System.Web.UI.DataVisualization.Charting.Chart();
result = someChart.DataManipulator.Statistics.InverseTDistribution( probability, degFreedom);
}
catch
{
// throw; // Optionally throw/log/ignore/whatever
}
return result;
}
The DataVisualization trick however won't work on SQL-Server, because you'd need to add a reference to System.Web, which you can't do in SQL-Server.
Also, excel has a lot of similar functions, tinv, t.inv, T.INV.2S, etc., so be sure to choose the right one.
Edit:
Found some more information: http://numerics.mathdotnet.com/api/MathNet.Numerics/ExcelFunctions.htm#TIn
There is a special class called ExcelFunctions in Math.NET that you can actually use:
MathNet.Numerics.ExcelFunctions.TInv (1.1, 55);
You find some more information on TINV and TDIST on OpenOffice.org along with a list of functions by by category
So the solution to your problem is
[Microsoft.SqlServer.Server.SqlFunction]
public static System.Data.SqlTypes.SqlDouble TInv(double probability, int degFreedom)
{
double result = 0.00;
try
{
result = MathNet.Numerics.ExcelFunctions.TInv (probability, degFreedom);
}
catch
{
// throw; // Optionally throw/log/ignore/whatever
}
return result;
}
which is actually the same as
[Microsoft.SqlServer.Server.SqlFunction]
public static System.Data.SqlTypes.SqlDouble TInv(double probability, int degFreedom)
{
double result = 0.00;
try
{
result = -StudentT.InvCDF(0d, 1d, degFreedom, probability/2);
}
catch
{
// throw; // Optionally throw/log/ignore/whatever
}
return result;
}
So now you grab the sourcecode of Math.Net from https://github.com/mathnet/mathnet-numerics and drag and drop the contents of mathnet-numerics/src/Numerics/ (or the part thereof that you need) into your project with the CRL-Function, and finished.
When you have your CLR dll, you go into SSMS and execute:
EXEC dbo.sp_configure 'clr enabled',1 RECONFIGURE WITH
CREATE ASSEMBLY SQLServerStatistics from 'C:\SQLServerStatistics.dll' WITH PERMISSION_SET = SAFE
After that has succeeded, you still have to register the function with SQL-Server.
CREATE FUNCTION [dbo].[tinv](@prob float, @degFreedom int)
RETURNS float WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLServerStatistics].[Functions].[TInv]
See this article for further information.
If you want to bring the Dll onto a productive server, you'll need to create the assembly from a byte-array-string, like this:
CREATE ASSEMBLY [MyFunctions]
AUTHORIZATION [dbo]
FROM 0x4D5A90000[very long string here...];
You create the hex string from the byte array like this:
byte[] bytes = System.IO.File.ReadAllBytes(@"C:\SQLServerStatistics.dll");
"0x" + BitConverter.ToString(bytes).Replace("-", "")
I have uploaded the entire solution here on github.
Then you can run the function like this:
SELECT dbo.tinv(0.54645, 60)
==> 0.606531559343638
The Script-Generator tool automatically builds the install script for you. Looks like this:
> sp_configure 'show advanced options', 1; GO RECONFIGURE; GO
> sp_configure 'clr enabled', 1; GO RECONFIGURE; GO
>
>
> DECLARE @sql nvarchar(MAX) SET @sql = 'ALTER DATABASE ' +
> QUOTENAME(DB_NAME()) + ' SET TRUSTWORTHY ON;'
> -- PRINT @sql; EXECUTE(@sql); GO
>
>
> -- Restore sid when db restored from backup... DECLARE @Command NVARCHAR(MAX) = N'ALTER AUTHORIZATION ON DATABASE::<<DatabaseName>> TO
> [<<LoginName>>]' SELECT @Command = REPLACE ( REPLACE(@Command,
> N'<<DatabaseName>>', SD.Name) , N'<<LoginName>>' , SL.Name ) FROM
> master..sysdatabases AS SD JOIN master..syslogins AS SL ON SD.SID
> = SL.SID
>
> WHERE SD.Name = DB_NAME()
>
> -- PRINT @command EXECUTE(@command) GO
>
> IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TDist]') AND type in (N'FN', N'IF', N'TF', N'FS',
> N'FT')) DROP FUNCTION [dbo].[TDist] GO
>
> IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TInv]') AND type in (N'FN', N'IF', N'TF', N'FS',
> N'FT')) DROP FUNCTION [dbo].[TInv] GO
>
>
>
> IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name =
> N'SQLServerStatistics' and is_user_defined = 1) DROP ASSEMBLY
> [SQLServerStatistics] GO
>
>
> CREATE ASSEMBLY SQLServerStatistics AUTHORIZATION [dbo]
> FROM 'c:\users\administrator\documents\visual studio 2013\Projects\SqlServerStatistics\ClrCreationScriptGenerator\bin\Debug\SqlServerStatistics.dll'
> WITH PERMISSION_SET = UNSAFE GO
>
>
> CREATE FUNCTION [dbo].[TDist](@x AS float, @degFreedom AS int, @tails
> AS int)
> RETURNS float WITH EXECUTE AS CALLER AS EXTERNAL NAME [SQLServerStatistics].[SqlServerStatistics.ExcelFunctions].[TDist] GO
>
>
>
> CREATE FUNCTION [dbo].[TInv](@probability AS float, @degFreedom AS
> int)
> RETURNS float WITH EXECUTE AS CALLER AS EXTERNAL NAME [SQLServerStatistics].[SqlServerStatistics.ExcelFunctions].[TInv] GO
这篇关于SQL Server相当于Excel的TINV函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
