在MS SQL Server中计算任意矩阵相关系数的方案问询

假设你的源数据如下：

WITH cte AS
(
 SELECT *
   FROM ( values
          (1, 4, 10),
          (2, 8, 20),
          (3, -2, 50)
        ) as dummy (a,b,c)
)

核心逻辑 #

相关系数的计算公式为：
corr(X,Y) = COVAR_POP(X,Y) / (STDDEV_POP(X) * STDDEV_POP(Y))
核心是先获取每对变量的协方差、各自的总体标准差，再代入公式计算。下面分步骤实现，同时解决“任意n变量”的动态适配问题。

步骤1：将宽表转为带行号的长表 #

把多列变量转换成「行号-变量名-变量值」的长格式，方便后续配对计算同一行的不同变量值：

WITH cte AS (
 SELECT * FROM ( values (1,4,10),(2,8,20),(3,-2,50) ) as dummy(a,b,c)
),
row_nums AS (
  SELECT *, ROW_NUMBER() OVER () AS rn FROM cte
),
long_data_with_rn AS (
  SELECT rn, 'a' AS var_name, a AS val FROM row_nums
  UNION ALL
  SELECT rn, 'b' AS var_name, b AS val FROM row_nums
  UNION ALL
  SELECT rn, 'c' AS var_name, c AS val FROM row_nums
)
SELECT * FROM long_data_with_rn;

步骤2：计算所有变量对的相关系数 #

通过自连接长表，按变量对分组计算协方差和标准差，最终得到相关系数：

WITH cte AS (
 SELECT * FROM ( values (1,4,10),(2,8,20),(3,-2,50) ) as dummy(a,b,c)
),
row_nums AS (
  SELECT *, ROW_NUMBER() OVER () AS rn FROM cte
),
long_data_with_rn AS (
  SELECT rn, 'a' AS var_name, a AS val FROM row_nums
  UNION ALL
  SELECT rn, 'b' AS var_name, b AS val FROM row_nums
  UNION ALL
  SELECT rn, 'c' AS var_name, c AS val FROM row_nums
),
var_stats AS (
  SELECT
    var1.var_name AS var_x,
    var2.var_name AS var_y,
    COVAR_POP(var1.val, var2.val) AS covar_xy,
    STDDEV_POP(var1.val) AS std_x,
    STDDEV_POP(var2.val) AS std_y
  FROM long_data_with_rn var1
  JOIN long_data_with_rn var2 ON var1.rn = var2.rn
  GROUP BY var1.var_name, var2.var_name
)
SELECT
  var_x,
  var_y,
  CASE
    WHEN std_x = 0 OR std_y = 0 THEN NULL -- 变量无波动时相关系数无意义，避免除以0
    ELSE covar_xy / (std_x * std_y)
  END AS correlation
FROM var_stats;

执行后会得到所有变量对的相关系数（包括变量自身的相关系数1）。

步骤3：动态适配任意n个变量 #

上面的代码是写死变量名的，要适配任意n个变量，需要用动态SQL自动生成变量转换的逻辑。以下是SQL Server的示例写法，其他数据库可根据语法调整：

DECLARE @cols NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

-- 自动获取目标表的所有列名（替换成你的表名，若用CTE可先将数据存入临时表）
SELECT @cols = STRING_AGG(
  N'SELECT rn, ''' + name + ''' AS var_name, ' + QUOTENAME(name) + ' AS val FROM row_nums',
  ' UNION ALL '
) FROM sys.columns WHERE object_id = OBJECT_ID('your_table');

-- 构建完整动态SQL
SET @sql = N'
WITH row_nums AS (
  SELECT *, ROW_NUMBER() OVER () AS rn FROM your_table
),
long_data_with_rn AS (
  ' + @cols + N'
),
var_stats AS (
  SELECT
    var1.var_name AS var_x,
    var2.var_name AS var_y,
    CASE
      WHEN STDDEV_POP(var1.val) = 0 OR STDDEV_POP(var2.val) = 0 THEN NULL
      ELSE COVAR_POP(var1.val, var2.val) / (STDDEV_POP(var1.val) * STDDEV_POP(var2.val))
    END AS correlation
  FROM long_data_with_rn var1
  JOIN long_data_with_rn var2 ON var1.rn = var2.rn
  GROUP BY var1.var_name, var2.var_name
)
SELECT var_x, var_y, correlation FROM var_stats;
';

EXEC sp_executesql @sql;

• MySQL可替换STRING_AGG为GROUP_CONCAT，用PREPARE+EXECUTE执行动态SQL
• PostgreSQL用STRING_AGG和EXECUTE语句

步骤4：可选：将结果转为矩阵格式 #

如果需要输出传统的n×n矩阵样式（行/列均为变量名，单元格为相关系数），可以在动态SQL中加入透视逻辑，SQL Server示例：

DECLARE @cols NVARCHAR(MAX);
DECLARE @pivot_cols NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

-- 获取变量列名，用于生成转换和透视逻辑
SELECT @cols = STRING_AGG(
  N'SELECT rn, ''' + name + ''' AS var_name, ' + QUOTENAME(name) + ' AS val FROM row_nums',
  ' UNION ALL '
) FROM sys.columns WHERE object_id = OBJECT_ID('your_table');

SELECT @pivot_cols = STRING_AGG(QUOTENAME(name), ', ')
FROM sys.columns WHERE object_id = OBJECT_ID('your_table');

-- 构建带透视的动态SQL
SET @sql = N'
WITH row_nums AS (
  SELECT *, ROW_NUMBER() OVER () AS rn FROM your_table
),
long_data_with_rn AS (
  ' + @cols + N'
),
var_stats AS (
  SELECT
    var1.var_name AS var_x,
    var2.var_name AS var_y,
    CASE
      WHEN STDDEV_POP(var1.val) = 0 OR STDDEV_POP(var2.val) = 0 THEN NULL
      ELSE COVAR_POP(var1.val, var2.val) / (STDDEV_POP(var1.val) * STDDEV_POP(var2.val))
    END AS correlation
  FROM long_data_with_rn var1
  JOIN long_data_with_rn var2 ON var1.rn = var2.rn
  GROUP BY var1.var_name, var2.var_name
)
SELECT var_x, ' + @pivot_cols + N'
FROM var_stats
PIVOT (
  MAX(correlation) FOR var_y IN (' + @pivot_cols + N')
) AS pivot_matrix;
';

EXEC sp_executesql @sql;

注意事项 #

• 数据库兼容性：不同数据库的统计函数、字符串聚合、动态SQL语法有差异，需对应调整
• 性能限制：当变量数n或观测行数很大时，自连接会产生大量中间数据，性能会明显下降，这也是SQL不适合这类统计分析的核心原因
• 空值处理：COVAR_POP和STDDEV_POP会自动忽略空值，若需处理空值可先用COALESCE填充，或改用样本统计量函数（COVAR_SAMP/STDDEV_SAMP）

内容的提问来源于stack exchange，提问作者Pavel Tashkinov