How to calculate the covariance in T-SQL

Среда, 18 Января 2012 г. 16:01 + в цитатник

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

http://weblogs.sqlteam.com/peterl/archive/2012/01/18/how-to-calculate-the-covariance-in-t-sql.aspx