在BigQuery中按name分组计算metric_a与metric_b的相关性

首先需要处理数据中的格式异常值（比如1.、1.2.、5.这类带多余小数点的字符串），因为BigQuery的CORR函数要求输入为数值类型，无效格式会导致计算失败。之后通过GROUP BY子句按name分组，调用CORR函数计算每组内metric_a与metric_b的相关性。

完整SQL示例 #

WITH cleaned_data AS (
  SELECT
    name,
    -- 清洗并转换metric_a为数值：去除末尾多余小数点后转浮点型
    SAFE_CAST(REGEXP_REPLACE(metric_a, r'\.$', '') AS FLOAT64) AS metric_a,
    -- 同理处理metric_b
    SAFE_CAST(REGEXP_REPLACE(metric_b, r'\.$', '') AS FLOAT64) AS metric_b
  FROM
    `your-project.your-dataset.your-table` -- 替换为你的实际表路径
)
SELECT
  name,
  CORR(metric_a, metric_b) AS corr
FROM
  cleaned_data
GROUP BY
  name;

关键说明 #

• 脏数据处理：REGEXP_REPLACE(列名, r'\.$', '')会去掉字符串末尾的无效小数点（比如把1.转为1、1.2.转为1.2），再用SAFE_CAST转换为浮点型——若遇到其他无效格式，该值会被设为NULL，CORR函数会自动忽略含NULL的行，不影响计算。
• 分组计算逻辑：GROUP BY name确保对每个独立的name单独计算相关性，CORR(X, Y)返回两列的皮尔逊相关系数，取值范围为[-1, 1]。

针对你提供的测试数据的计算结果 #

• name=A：清洗后数据为(1.1,1.2)和(1,1.2)，相关系数为1.0（样本量过小，结果仅为计算值，无统计显著性）
• name=B：清洗后数据为(10,-1)和(12,5)，相关系数为1.0（两个点完全正相关）

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