SQL Server相当于Excel的TINV函数

本文介绍了SQL Server相当于Excel的TINV函数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 我试图找到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 例如 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）; 您可以在 TINV 和 TDIST 以及按类别 所以解决问题的方法是 [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的源代码 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.dlle.g.var someChart = new System.Web.UI.DataVisualization.Charting.Chart();double res = someChart.DataManipulator.Statistics.InverseTDistribution(.05, 15);//2.131449546However, 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.95Since 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#TInThere 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 categorySo 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 fromhttps://github.com/mathnet/mathnet-numericsand 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 WITHCREATE ASSEMBLY SQLServerStatistics from 'C:\SQLServerStatistics.dll' WITH PERMISSION_SET = SAFEAfter 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 CALLERASEXTERNAL 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.606531559343638The 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函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！