Login     Register

        Contact Us     Search

XLeratorDB/statistics Documentation

SQL Server Cohen's Kappa function


KAPPA_COHEN_TV

Updated: 24 May 2013


Use the table-valued function KAPPA_COHEN_TV to calculate Cohen’s kappa (Κ) or weighted kappa as an index of inter-rater agreement between 2 raters. For weighted kappa, ratings must be ordinal. KAPPA_COHEN_TV supports quadratic and linear weighting.
The equation for Κ is

XLeratorDB formula for KAPPA_COHEN function for SQL Server
Where:
Pa is the relative observed agreement among raters
Pc is the hypothetical probability of chance agreement
Syntax
SELECT * FROM [wctStatistics].[wct].[KAPPA_COHEN_TV](
  <@InputData_RangeQuery, nvarchar(max),>
 ,<@Weight, nvarchar(max),>)
Arguments
@InputData_RangeQuery
a T-SQL statement, as a string, that specifies the rating 1- and rating2- values.
@Weight
identifies the weighting algorithm to be used. Permitted values are un-weighted, linear, and quadratic.
Return Type
RETURNS TABLE (
      [Pa] [float] NULL,
      [Pc] [float] NULL,
      [K] [float] NULL,
      [P] [float] NULL,
      [Z] [float] NULL,
      [SE] [float] NULL,
      [NS] [float] NULL,
      [NR] [float] NULL
)

Column
Column Description
Pa
the relative observed agreement among raters
Pc
the hypothetical probability of chance agreement
K
the test statistic
P
the p-value
Z
K / SE
SE
the standard error
NS
the number of subjects
NR
the number of ratings
Remarks
·         The function is insensitive to order; it does not matter what order the ratings are passed in.
·         Ratings are passed in as pairs.
·         For more than 2 raters, consider using KAPPA_FLEISS_TV.
·         If @Weight is NULL, then @Weight = 'U'.
·         For quadratic weighting, set @Weight = 'Q'.
·         For linear weighting, set @Weight = 'L'.
Examples
SELECT *
INTO #k
FROM (
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 1,1 UNION ALL
      SELECT 2,1 UNION ALL
      SELECT 2,1 UNION ALL
      SELECT 2,1 UNION ALL
      SELECT 2,1 UNION ALL
      SELECT 2,1 UNION ALL
      SELECT 2,1 UNION ALL
      SELECT 2,1 UNION ALL
      SELECT 2,1 UNION ALL
      SELECT 2,1 UNION ALL
      SELECT 2,1 UNION ALL
      SELECT 3,1 UNION ALL
      SELECT 3,1 UNION ALL
      SELECT 1,2 UNION ALL
      SELECT 1,2 UNION ALL
      SELECT 1,2 UNION ALL
      SELECT 1,2 UNION ALL
      SELECT 1,2 UNION ALL
      SELECT 1,2 UNION ALL
      SELECT 1,2 UNION ALL
      SELECT 1,2 UNION ALL
      SELECT 1,2 UNION ALL
      SELECT 1,2 UNION ALL
      SELECT 1,2 UNION ALL
      SELECT 1,2 UNION ALL
      SELECT 1,2 UNION ALL
      SELECT 1,2 UNION ALL
      SELECT 2,2 UNION ALL
      SELECT 2,2 UNION ALL
      SELECT 2,2 UNION ALL
      SELECT 2,2 UNION ALL
      SELECT 2,2 UNION ALL
      SELECT 2,2 UNION ALL
      SELECT 2,2 UNION ALL
      SELECT 2,2 UNION ALL
      SELECT 2,2 UNION ALL
      SELECT 2,2 UNION ALL
      SELECT 2,2 UNION ALL
      SELECT 2,2 UNION ALL
      SELECT 2,2 UNION ALL
      SELECT 2,2 UNION ALL
      SELECT 2,2 UNION ALL
      SELECT 2,2 UNION ALL
      SELECT 2,2 UNION ALL
      SELECT 2,2 UNION ALL
      SELECT 2,2 UNION ALL
      SELECT 2,2 UNION ALL
      SELECT 2,2 UNION ALL
      SELECT 2,2 UNION ALL
      SELECT 2,2 UNION ALL
      SELECT 2,2 UNION ALL
      SELECT 2,2 UNION ALL
      SELECT 2,2 UNION ALL
      SELECT 2,2 UNION ALL
      SELECT 2,2 UNION ALL
      SELECT 2,2 UNION ALL
      SELECT 2,2 UNION ALL
      SELECT 2,2 UNION ALL
      SELECT 2,2 UNION ALL
      SELECT 2,2 UNION ALL
      SELECT 2,2 UNION ALL
      SELECT 2,2 UNION ALL
      SELECT 2,2 UNION ALL
      SELECT 2,2 UNION ALL
      SELECT 2,2 UNION ALL
      SELECT 2,2 UNION ALL
      SELECT 2,2 UNION ALL
      SELECT 3,2 UNION ALL
      SELECT 3,2 UNION ALL
      SELECT 3,2 UNION ALL
      SELECT 3,2 UNION ALL
      SELECT 3,2 UNION ALL
      SELECT 3,2 UNION ALL
      SELECT 1,3 UNION ALL
      SELECT 1,3 UNION ALL
      SELECT 1,3 UNION ALL
      SELECT 1,3 UNION ALL
      SELECT 1,3 UNION ALL
      SELECT 1,3 UNION ALL
      SELECT 1,3 UNION ALL
      SELECT 1,3 UNION ALL
      SELECT 1,3 UNION ALL
      SELECT 1,3 UNION ALL
      SELECT 1,3 UNION ALL
      SELECT 1,3 UNION ALL
      SELECT 1,3 UNION ALL
      SELECT 1,3 UNION ALL
      SELECT 1,3 UNION ALL
      SELECT 1,3 UNION ALL
      SELECT 1,3 UNION ALL
      SELECT 1,3 UNION ALL
      SELECT 2,3 UNION ALL
      SELECT 2,3 UNION ALL
      SELECT 2,3 UNION ALL
      SELECT 2,3 UNION ALL
      SELECT 2,3 UNION ALL
      SELECT 2,3 UNION ALL
      SELECT 2,3 UNION ALL
      SELECT 2,3 UNION ALL
      SELECT 2,3 UNION ALL
      SELECT 2,3 UNION ALL
      SELECT 3,3 UNION ALL
      SELECT 3,3 UNION ALL
      SELECT 3,3 UNION ALL
      SELECT 3,3 UNION ALL
      SELECT 3,3 UNION ALL
      SELECT 3,3 UNION ALL
      SELECT 3,3 UNION ALL
      SELECT 3,3 UNION ALL
      SELECT 3,3 UNION ALL
      SELECT 3,3 UNION ALL
      SELECT 3,3 UNION ALL
      SELECT 3,3
      )n(r1, r2)
 
SELECT p.*
FROM wct.KAPPA_COHEN_TV('SELECT r1,r2 FROM #k','U') k
--This CROSS APPLY UNPIVOTS the tvf columns for formatting
CROSS APPLY(          
      SELECT 'Pa', Pa UNION ALL
      SELECT 'Pc', Pc UNION ALL
      SELECT 'K', K UNION ALL
      SELECT 'P', P UNION ALL
      SELECT 'Z', Z UNION ALL
      SELECT 'SE', SE UNION ALL
      SELECT 'NS', NS UNION ALL
      SELECT 'NR', NR
   )p(stat,val)
 
This produces the following result.



Here an example using quadratic weighting.
;with mycte(subj,rater,rating) as (
SELECT 1,'R1','c' UNION ALL   SELECT 1,'R2','d' UNION ALL
SELECT 2,'R1','c' UNION ALL   SELECT 2,'R2','a' UNION ALL
SELECT 3,'R1','c' UNION ALL   SELECT 3,'R2','a' UNION ALL
SELECT 4,'R1','c' UNION ALL   SELECT 4,'R2','c' UNION ALL
SELECT 5,'R1','c' UNION ALL   SELECT 5,'R2','b' UNION ALL
SELECT 6,'R1','b' UNION ALL   SELECT 6,'R2','b' UNION ALL
SELECT 7,'R1','c' UNION ALL   SELECT 7,'R2','a' UNION ALL
SELECT 8,'R1','d' UNION ALL   SELECT 8,'R2','c' UNION ALL
SELECT 9,'R1','b' UNION ALL   SELECT 9,'R2','a' UNION ALL
SELECT 10,'R1','b' UNION ALL SELECT 10,'R2','b' UNION ALL
SELECT 11,'R1','a' UNION ALL SELECT 11,'R2','d' UNION ALL
SELECT 12,'R1','c' UNION ALL SELECT 12,'R2','d' UNION ALL
SELECT 13,'R1','a' UNION ALL SELECT 13,'R2','d' UNION ALL
SELECT 14,'R1','b' UNION ALL SELECT 14,'R2','c' UNION ALL
SELECT 15,'R1','a' UNION ALL SELECT 15,'R2','a' UNION ALL
SELECT 16,'R1','c' UNION ALL SELECT 16,'R2','a' UNION ALL
SELECT 17,'R1','a' UNION ALL SELECT 17,'R2','c' UNION ALL
SELECT 18,'R1','b' UNION ALL SELECT 18,'R2','d' UNION ALL
SELECT 19,'R1','b' UNION ALL SELECT 19,'R2','b' UNION ALL
SELECT 20,'R1','c' UNION ALL SELECT 20,'R2','c' UNION ALL
SELECT 21,'R1','d' UNION ALL SELECT 21,'R2','d' UNION ALL
SELECT 22,'R1','b' UNION ALL SELECT 22,'R2','d' UNION ALL
SELECT 23,'R1','a' UNION ALL SELECT 23,'R2','a' UNION ALL
SELECT 24,'R1','d' UNION ALL SELECT 24,'R2','b' UNION ALL
SELECT 25,'R1','b' UNION ALL SELECT 25,'R2','b'
)
SELECT m1.rating as r1
,m2.rating as r2
INTO #c
FROM mycte m1
JOIN mycte m2
ON m1.subj = m2.subj
WHERE m1.rater = 'R1'
AND m2.rater = 'R2'
 
SELECT p.*
FROM wct.KAPPA_COHEN_TV('SELECT r1,r2 FROM #C','Q') k
--This CROSS APPLY UNPIVOTS the tvf columns for formatting
CROSS APPLY(
      SELECT 'Pa', Pa UNION ALL
      SELECT 'Pc', Pc UNION ALL
      SELECT 'K', K UNION ALL
      SELECT 'P', P UNION ALL
      SELECT 'Z', Z UNION ALL
      SELECT 'SE', SE UNION ALL
      SELECT 'NS', NS UNION ALL
      SELECT 'NR', NR
      )p(stat,val)
This produces the following result.
 
 


Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service