SQL Server equivalent of Excel's TINV function

Vignesh

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)
Stefan Steiger

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

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

SQL Server equivalent of Excel's TINV function

From Dev

C# equivalent of Excel's TINV function

From Dev

SQL Server - any equivalent of Excel's CHOOSE function?

From Dev

SQL round down by significance (equivalent of excel's floor function)

From Dev

What is the MATLAB equivalent of Excel's NORMSDIST function?

From Dev

Cognos equivalent of excel's sumif() function

From Dev

What is the MATLAB equivalent of Excel's NORMSDIST function?

From Dev

Cognos equivalent of excel's sumif() function

From Dev

Equivalent of MySQL HEX / UNHEX function in SQL Server?

From Dev

Equivalent of MySQL HEX / UNHEX function in SQL Server?

From Dev

Equivalent of Coldfusion's find command in sql server

From Dev

pandas: function equivalent to SQL's datediff()?

From Dev

R equivalent of Excel's "Sumif(s)" function across like columns

From Dev

Equivalent of Excel Round function

From Dev

SQL Server's ISNUMERIC function

From Dev

SQL Server equivalent of substring_index function in MySQL

From Dev

SQL Server equivalent of substring_index function in MySQL

From Dev

SQL Server datetime convert function equivalent to postgresql (pivotal hawq)

From Dev

SQL Server LEAD Equivalent Function in MS Access 2016

From Dev

Equivalent of PHP Die(): how to stop function's execution on server

From Dev

SQL Server equivalent of Trunc

From Dev

SQL SERVER equivalent of a HashMap?

From Dev

Equivalent to NULL in SQL Server

From Dev

Oracle's RETURNING INTO :variable equivalent using SQL Server

From Dev

Is there any equivalent method for Oracle's INSERT ALL in SQL Server

From Dev

What is the Oracle equivalent of SQL Server's SET NOEXEC or NOPARSE?

From Dev

What is the DB2 equivalent of SQL Server's SET NOCOUNT ON?

From Dev

DB2/SQL equivalent of SAS's sum(of ) function

From Dev

Excel VBA equivalent of ADDRESS function

Related Related

  1. 1

    SQL Server equivalent of Excel's TINV function

  2. 2

    C# equivalent of Excel's TINV function

  3. 3

    SQL Server - any equivalent of Excel's CHOOSE function?

  4. 4

    SQL round down by significance (equivalent of excel's floor function)

  5. 5

    What is the MATLAB equivalent of Excel's NORMSDIST function?

  6. 6

    Cognos equivalent of excel's sumif() function

  7. 7

    What is the MATLAB equivalent of Excel's NORMSDIST function?

  8. 8

    Cognos equivalent of excel's sumif() function

  9. 9

    Equivalent of MySQL HEX / UNHEX function in SQL Server?

  10. 10

    Equivalent of MySQL HEX / UNHEX function in SQL Server?

  11. 11

    Equivalent of Coldfusion's find command in sql server

  12. 12

    pandas: function equivalent to SQL's datediff()?

  13. 13

    R equivalent of Excel's "Sumif(s)" function across like columns

  14. 14

    Equivalent of Excel Round function

  15. 15

    SQL Server's ISNUMERIC function

  16. 16

    SQL Server equivalent of substring_index function in MySQL

  17. 17

    SQL Server equivalent of substring_index function in MySQL

  18. 18

    SQL Server datetime convert function equivalent to postgresql (pivotal hawq)

  19. 19

    SQL Server LEAD Equivalent Function in MS Access 2016

  20. 20

    Equivalent of PHP Die(): how to stop function's execution on server

  21. 21

    SQL Server equivalent of Trunc

  22. 22

    SQL SERVER equivalent of a HashMap?

  23. 23

    Equivalent to NULL in SQL Server

  24. 24

    Oracle's RETURNING INTO :variable equivalent using SQL Server

  25. 25

    Is there any equivalent method for Oracle's INSERT ALL in SQL Server

  26. 26

    What is the Oracle equivalent of SQL Server's SET NOEXEC or NOPARSE?

  27. 27

    What is the DB2 equivalent of SQL Server's SET NOCOUNT ON?

  28. 28

    DB2/SQL equivalent of SAS's sum(of ) function

  29. 29

    Excel VBA equivalent of ADDRESS function

HotTag

Archive