sql - 有没有办法使用 OVER 子句而不是 CTE 来计算 SQL 中的相关性？

Question

假设您有一个包含日期、GroupID、X 和 Y 列的表。

CREATE TABLE #sample
  (
     [Date]  DATETIME,
     GroupID INT,
     X       FLOAT,
     Y       FLOAT
  )

DECLARE @date DATETIME = getdate()

INSERT INTO #sample VALUES(@date, 1, 1,3)
INSERT INTO #sample VALUES(DATEADD(d, 1, @date), 1, 1,1)
INSERT INTO #sample VALUES(DATEADD(d, 2, @date), 1, 4,2)
INSERT INTO #sample VALUES(DATEADD(d, 3, @date), 1, 3,3)
INSERT INTO #sample VALUES(DATEADD(d, 4, @date), 1, 6,4)
INSERT INTO #sample VALUES(DATEADD(d, 5, @date), 1, 7,5)
INSERT INTO #sample VALUES(DATEADD(d, 6, @date), 1, 1,6)

并且您想要计算每个组的 X 和 Y 的相关性。目前我使用的 CTE 有点困惑:

;WITH DataAvgStd
     AS (SELECT GroupID,
                AVG(X)   AS XAvg,
                AVG(Y)   AS YAvg,
                STDEV(X) AS XStdev,
                STDEV(Y) AS YSTDev,
                COUNT(*) AS SampleSize
         FROM   #sample
         GROUP  BY GroupID),
     ExpectedVal
     AS (SELECT s.GroupID,
                SUM(( X - XAvg ) * ( Y - YAvg )) AS ExpectedValue
         FROM   #sample s
                JOIN DataAvgStd das
                  ON s.GroupID = das.GroupID
         GROUP  BY s.GroupID)
SELECT das.GroupID,
       ev.ExpectedValue / ( das.SampleSize - 1 ) / ( das.XStdev * das.YSTDev )
       AS
       Correlation
FROM   DataAvgStd das
       JOIN ExpectedVal ev
         ON das.GroupID = ev.GroupID

DROP TABLE #sample  

似乎应该有一种方法可以使用 OVER 和 PARTITION 一次性完成此操作，而无需任何子查询。理想情况下，TSQL 应该有一个函数，这样您就可以编写:

SELECT GroupID, CORR(X, Y) OVER(PARTITION BY GroupID)
FROM #sample
GROUP BY GroupID

Answer 1

使用这个关联公式，即使您使用 over()，也无法避免所有嵌套查询。问题是你不能在同一个查询中同时使用 group by 和 over，而且你不能有嵌套的聚合函数，例如总和(x - 平均值(x))。因此，在最好的情况下，根据您的数据，您将需要至少保留 with。

你的代码看起来像这样

;WITH DataAvgStd
     AS (SELECT GroupID,
                STDEV(X) over(partition by GroupID) AS XStdev,
                STDEV(Y) over(partition by GroupID) AS YSTDev,
                COUNT(*) over(partition by GroupID) AS SampleSize,
                ( X - AVG(X) over(partition by GroupID)) * ( Y - AVG(Y) over(partition by GroupID)) AS ExpectedValue
         FROM   #sample s)         
SELECT distinct GroupID,
       SUM(ExpectedValue) over(partition by GroupID) / (SampleSize - 1 ) / ( XStdev * YSTDev ) AS Correlation
FROM DataAvgStd 

另一种方法是使用等效的相关公式:Wikipedia描述。

这可以写成

SELECT GroupID,
       Correlation=(COUNT(*) * SUM(X * Y) - SUM(X) * SUM(Y)) / 
                   (SQRT(COUNT(*) * SUM(X * X) - SUM(X) * SUM(x))
                    * SQRT(COUNT(*) * SUM(Y* Y) - SUM(Y) * SUM(Y)))
FROM #sample s
GROUP BY GroupID;