Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

Wednesday, January 18, 2012

How to calculate the covariance in T-SQL

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

posted @ Wednesday, January 18, 2012 1:01 PM | Feedback (1) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 Denali ]

Powered by:
Powered By Subtext Powered By ASP.NET