How to calculate the covariance in T-SQL

Wed Jan 18, 2012 by Peter Larsson in optimization, sql-server-2008, algorithms, sql-server-2005, sql-server-2000, denali

DECLARE @Sample TABLE
        (
            x INT NOT NULL,
            y INT NOT NULL

        )

INSERT  @Sample
VALUES  (3, 9),
        (2, 7),
        (4, 12),
        (5, 15),
        (6, 17)

;WITH cteSource(x, xAvg, y, yAvg, n)
AS (
        SELECT  1E * x,
                AVG(1E * x) OVER (PARTITION BY (SELECT NULL)),
                1E * y,
                AVG(1E * y) OVER (PARTITION BY (SELECT NULL)),
                COUNT(*) OVER (PARTITION BY (SELECT NULL))
        FROM    @Sample
)
SELECT  SUM((x - xAvg) *(y - yAvg)) / MAX(n) AS [COVAR(x,y)]
FROM    cteSource

Legacy Comments


dmSQL 2012-10-24	re: How to calculate the covariance in T-SQL Thanks. It seems that you only need OVER() rather than a PARTITION clause also.