按用户ID计算多次随访测量值的Z-score问题求助

你的核心问题出在GROUP BY的分组逻辑和Z-score的计算公式上：

• 原SQL把VNo1-VNo4也加入了GROUP BY，这会让每个ID的每一条随访记录单独成组，计算出的均值和标准差都是单行的，完全无法反映该ID的整体数据分布。
• 另外Z-score的公式写错了，正确的计算应该是(单个测量值 - 该ID的均值) / 该ID的标准差，而不是标准差的平方。

下面分两种常见的表结构场景给出正确的SQL：

场景1：每个ID一行，包含四次随访的测量列 #

如果你的表是宽表结构（比如ID, W1_VNo1, W1_VNo2, W1_VNo3, W1_VNo4，VNo1-VNo4对应四次随访的W1测量值），需要先把宽表转成窄表，计算完Z-score后可以再转回宽表：

步骤1：转窄表并计算Z-score #

WITH unpivoted_data AS (
    SELECT 
        ID,
        -- 把四次随访的列转成行，记录随访编号和对应W1值
        CAST(SUBSTRING(vno_col, 6, 1) AS INT) AS VNo,
        w1_value
    FROM dbo.measurement
    UNPIVOT (
        w1_value FOR vno_col IN (W1_VNo1, W1_VNo2, W1_VNo3, W1_VNo4)
    ) AS up
),
id_stats AS (
    SELECT 
        ID,
        AVG(w1_value) OVER (PARTITION BY ID) AS mean_w1,
        STDEVP(w1_value) OVER (PARTITION BY ID) AS stddev_w1
    FROM unpivoted_data
)
SELECT 
    us.ID,
    us.VNo,
    us.w1_value,
    -- 计算Z-score，注意处理标准差为0的情况（避免除以0）
    CASE WHEN istd.stddev_w1 = 0 THEN 0 ELSE (us.w1_value - istd.mean_w1) / istd.stddev_w1 END AS z_score
FROM unpivoted_data us
JOIN id_stats istd ON us.ID = istd.ID

步骤2（可选）：转回宽表 #

如果需要回到原来的宽表格式，可以用PIVOT：

WITH unpivoted_data AS (
    SELECT 
        ID,
        CAST(SUBSTRING(vno_col, 6, 1) AS INT) AS VNo,
        w1_value
    FROM dbo.measurement
    UNPIVOT (
        w1_value FOR vno_col IN (W1_VNo1, W1_VNo2, W1_VNo3, W1_VNo4)
    ) AS up
),
id_stats AS (
    SELECT 
        ID,
        AVG(w1_value) OVER (PARTITION BY ID) AS mean_w1,
        STDEVP(w1_value) OVER (PARTITION BY ID) AS stddev_w1
    FROM unpivoted_data
),
z_scores AS (
    SELECT 
        us.ID,
        us.VNo,
        CASE WHEN istd.stddev_w1 = 0 THEN 0 ELSE (us.w1_value - istd.mean_w1) / istd.stddev_w1 END AS z_score
    FROM unpivoted_data us
    JOIN id_stats istd ON us.ID = istd.ID
)
SELECT 
    ID,
    [1] AS Zscore_VNo1,
    [2] AS Zscore_VNo2,
    [3] AS Zscore_VNo3,
    [4] AS Zscore_VNo4
FROM z_scores
PIVOT (
    MAX(z_score) FOR VNo IN ([1], [2], [3], [4])
) AS p

场景2：每个ID多行，每行对应一次随访 #

如果你的表是窄表结构（比如ID, VNo, W1，VNo取值1-4，每行是一次随访的记录），用窗口函数可以直接计算：

SELECT 
    ID,
    VNo,
    W1,
    AVG(W1) OVER (PARTITION BY ID) AS mean_w1,
    STDEVP(W1) OVER (PARTITION BY ID) AS stddev_w1,
    -- 计算Z-score，处理标准差为0的边界情况
    CASE WHEN STDEVP(W1) OVER (PARTITION BY ID) = 0 THEN 0 ELSE (W1 - AVG(W1) OVER (PARTITION BY ID)) / STDEVP(W1) OVER (PARTITION BY ID) END AS z_score
FROM dbo.measurement

关键说明： #

• 用PARTITION BY ID让均值和标准差的计算范围限定在同一个ID的所有随访记录里，这才是你需要的分组逻辑。
• 加入CASE处理标准差为0的情况，避免出现除以0的错误（当同一个ID的所有W1值都相同时，标准差为0，此时Z-score设为0是合理的）。

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