SQL Server相当于Excel的TINV函数

Vignesh 发表于 Dev

维格涅什

我试图找到X的t分布的两尾逆。可以在Excel中使用TINV函数找到它，但我需要在SQL Sever中实现。请提出我的想法。

TINV函数语法具有以下参数：

概率：与两尾学生t分布相关的概率。

Deg_freedom：表征分布的自由度数。

前任：

select tinv( 0.054645, 60);
-- -----------------
--  1.9599994129833
--  (1 row returned)

斯蒂芬·斯蒂格（Stefan Steiger）

SQL-Server没有合并很多统计功能。
tinv在SQL Server中不存在。

添加tinv函数的唯一方法是使用CLR函数。
因此，问题将其自身简化为“如何使用SQL-Server中允许的C＃子集计算tinv？”。

如果您使用的是.NET 4.0，则可以在 System.Web.DataVisualization.dll

例如

var someChart = new System.Web.UI.DataVisualization.Charting.Chart();
double res = someChart.DataManipulator.Statistics.InverseTDistribution(.05, 15);

//2.131449546

但是，您可能不希望这样的开销。因此，您必须将其从Math.NET的SourceCode（MIT / X11许可证）中剔除。

StudentT dist = new StudentT(0.0,1.0,7.0);          
double prob = dist.CumulativeDistribution(1.8946);

这应该导致0.95

由于需要逆，因此需要

StudentT.InvCDF(double location, double scale, double freedom, double p)

location：分布的位置（μ）。
标度：分布的标度（σ）。范围：σ>0。
自由度：分布的自由度（ν）。范围：ν>0。p
：计算逆累积密度的位置。

[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;
}

使用DataVisualization，如下所示：

[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;
}

但是，DataVisualization技巧在SQL Server上不起作用，因为您需要添加对System.Web的引用，而在SQL Server中则无法做到。

此外，excel具有很多类似的功能，例如tinv，t.inv，T.INV.2S等，因此请务必选择正确的功能。

编辑：

您可以实际使用Math.NET中一个称为ExcelFunctions的特殊类：

MathNet.Numerics.ExcelFunctions.TInv (1.1, 55);

您可以在OpenOffice.org上找到有关TINV和TDIST的更多信息，以及按类别列出的功能列表

因此，解决您的问题的方法是

[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;
}

实际上与

[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;
}

因此，现在您可以从https://github.com/mathnet/mathnet-numerics获取Math.Net的源代码，并将mathnet-numerics / src / Numerics /的内容（或所需部分）拖放到您的具有CRL功能的项目，并完成。

当您拥有CLR dll时，您进入SSMS并执行：

EXEC dbo.sp_configure 'clr enabled',1 RECONFIGURE WITH

CREATE ASSEMBLY SQLServerStatistics from 'C:\SQLServerStatistics.dll' WITH PERMISSION_SET = SAFE

成功之后，您仍然必须在SQL-Server中注册该功能。

CREATE FUNCTION [dbo].[tinv](@prob float, @degFreedom int)
    RETURNS float WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLServerStatistics].[Functions].[TInv]

请参阅本文章以获取更多信息。

如果要将Dll带到生产服务器上，则需要从字节数组字符串创建程序集，如下所示：

CREATE ASSEMBLY [MyFunctions]
AUTHORIZATION [dbo]
FROM 0x4D5A90000[very long string here...];

您可以从字节数组创建十六进制字符串，如下所示：

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 '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