SQL Server KENDALLT_TV Function

Updated 2023-11-06 16:06:17.407000

Description

Use the table-valued function KENDALL_TV to calculate Kendall's tau (κ), a non-parametric measure of association based on the number of concordances and discordances in paired observations. Concordance occurs when paired observations vary together and discordance occurs when paired observations vary differently. The equation for Kendall's tau is:

\tau_b = \frac{C-D}{\sqrt{(\binom{n}{2} - T)(\binom{n}{2} - U)}

Where

The function also calculates

\tau_a = \frac{C-D}{\binom{n}{2}}

Syntax

SELECT * FROM [westclintech].[wct].[KENDALLT_TV](
   <@x_y_Query, nvarchar(max),>)

Arguments

@x_y_Query

a T-SQL statement, as a string, that specifies the subject, rater and rating values.

Return Type

table

Remarks

The function is insensitive to order.

If x is NULL or y is NULL the pair is not included in the calculations.

Examples

SELECT *
INTO #k
FROM
(
    SELECT 2.5,
           1
    UNION ALL
    SELECT 2.5,
           1
    UNION ALL
    SELECT 2.5,
           1
    UNION ALL
    SELECT 2.5,
           1
    UNION ALL
    SELECT 5,
           2
    UNION ALL
    SELECT 6.5,
           1
    UNION ALL
    SELECT 6.5,
           1
    UNION ALL
    SELECT 10,
           2
    UNION ALL
    SELECT 10,
           1
    UNION ALL
    SELECT 10,
           1
    UNION ALL
    SELECT 10,
           1
    UNION ALL
    SELECT 10,
           1
    UNION ALL
    SELECT 14,
           1
    UNION ALL
    SELECT 14,
           1
    UNION ALL
    SELECT 14,
           2
    UNION ALL
    SELECT 16,
           2
    UNION ALL
    SELECT 17,
           2
) n(x, y);
SELECT p.*
FROM wct.KENDALLT_TV('SELECT x,y FROM #k') k
    --This CROSS APPLY UNPIVOTS the tvf columns for formatting
    CROSS APPLY
(
    SELECT 'tau_a',
           tau_a
    UNION ALL
    SELECT 'tau_b',
           tau_b
    UNION ALL
    SELECT 'C',
           C
    UNION ALL
    SELECT 'D',
           D
    UNION ALL
    SELECT 'S',
           S
    UNION ALL
    SELECT 'T',
           T
    UNION ALL
    SELECT 'U',
           U
    UNION ALL
    SELECT 'za',
           za
    UNION ALL
    SELECT 'zb',
           zb
    UNION ALL
    SELECT 'SDa',
           SDa
    UNION ALL
    SELECT 'SDb',
           SDb
    UNION ALL
    SELECT 'pa',
           pa
    UNION ALL
    SELECT 'pb',
           pb
    UNION ALL
    SELECT 'N',
           N
) p(stat, value_stat);
DROP TABLE #k;

This produces the following result.