SQL Server中实现扩展均值、标准差及区间校验的方法

现有SQL Server表格数据如下：

需求：

• 按var分组、date排序，计算扩展均值（截至当前行的所有历史数据均值）和扩展标准差
• 生成1倍标准差的上下区间
• 校验每行（除第一行）的val是否落在前一个时间点的上下区间内，输出校验状态

用户尝试代码 #

-- Create the table
CREATE TABLE DataTable (
    [date] DATE,
    var CHAR(1),
    val DECIMAL(4, 2)
);

-- Insert the data
INSERT INTO DataTable ([date], var, val)
VALUES 
('2022-02-01', 'A', 1.1),
('2022-03-01', 'A', 2.3),
('2022-04-01', 'A', 1.5),
('2022-05-01', 'A', 1.7),
('2022-09-01', 'B', 1.8),
('2022-10-01', 'B', 1.9),
('2022-11-01', 'B', 2.1),
('2022-12-01', 'B', 2.22);


WITH DataWithStats AS (
    SELECT 
        [date],
        var,
        val,
        AVG(val) OVER (PARTITION BY var ORDER BY [date] 
                       ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS exp_avg,
        STDEV(val) OVER (PARTITION BY var ORDER BY [date] 
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS exp_std
    FROM DataTable
)
SELECT 
    [date],
    var,
    exp_avg - 2 * exp_std AS lower,
    val,
    exp_avg + 2 * exp_std AS upper,
    CASE 
        WHEN val < LAG(exp_avg - 2 * exp_std, 1) OVER (PARTITION BY var ORDER BY [date]) OR  
             val > LAG(exp_avg + 2 * exp_std, 1) OVER (PARTITION BY var ORDER BY [date]) 
        THEN 'Warning'
        ELSE 'ok'
    END AS status
FROM DataWithStats;

问题分析 #

当前代码存在两个核心问题：

1. 需求是1倍标准差区间，但代码误用了2*exp_std计算区间
2. status列逻辑虽能取到滞后区间，但未处理第一行无前置数据的情况，且计算逻辑可优化为更清晰的分步获取前一期统计量

正确解决方案 #

步骤说明 #

1. 计算每个分组内截至当前行的扩展均值和标准差
2. 获取前一行的扩展均值和标准差（滞后1期）
3. 用前一行的统计量计算1倍标准差的上下区间
4. 校验当前行val是否落在该区间内，第一行标记为无前置数据状态

完整代码 #

-- 建表插入数据
CREATE TABLE DataTable (
    [date] DATE,
    var CHAR(1),
    val DECIMAL(4, 2)
);

INSERT INTO DataTable ([date], var, val)
VALUES 
('2022-02-01', 'A', 1.1),
('2022-03-01', 'A', 2.3),
('2022-04-01', 'A', 1.5),
('2022-05-01', 'A', 1.7),
('2022-09-01', 'B', 1.8),
('2022-10-01', 'B', 1.9),
('2022-11-01', 'B', 2.1),
('2022-12-01', 'B', 2.22);

WITH ExpandingStats AS (
    SELECT 
        [date],
        var,
        val,
        -- 计算截至当前行的扩展均值和标准差
        AVG(val) OVER (PARTITION BY var ORDER BY [date] ROWS UNBOUNDED PRECEDING) AS exp_avg,
        STDEV(val) OVER (PARTITION BY var ORDER BY [date] ROWS UNBOUNDED PRECEDING) AS exp_std,
        -- 标记行号，识别分组内第一行
        ROW_NUMBER() OVER (PARTITION BY var ORDER BY [date]) AS row_num
    FROM DataTable
),
LaggedStats AS (
    SELECT 
        [date],
        var,
        val,
        exp_avg,
        exp_std,
        row_num,
        -- 获取前一行的扩展均值和标准差
        LAG(exp_avg) OVER (PARTITION BY var ORDER BY [date]) AS prev_exp_avg,
        LAG(exp_std) OVER (PARTITION BY var ORDER BY [date]) AS prev_exp_std
    FROM ExpandingStats
)
SELECT 
    [date],
    var,
    val,
    -- 当前行的扩展统计量与区间（可选展示）
    exp_avg AS current_exp_avg,
    exp_std AS current_exp_std,
    exp_avg - exp_std AS current_lower,
    exp_avg + exp_std AS current_upper,
    -- 前一行的1倍标准差区间
    prev_exp_avg - prev_exp_std AS prev_lower,
    prev_exp_avg + prev_exp_std AS prev_upper,
    -- 校验状态：第一行无前置数据标记为NULL，其余判断是否落在前一期区间内
    CASE
        WHEN row_num = 1 THEN NULL
        WHEN val < (prev_exp_avg - prev_exp_std) OR val > (prev_exp_avg + prev_exp_std) THEN 'Warning'
        ELSE 'ok'
    END AS status
FROM LaggedStats
ORDER BY var, [date];

代码解释 #

• ExpandingStats CTE：完成扩展统计量计算，同时标记行号用于区分分组内第一行
• LaggedStats CTE：单独获取前一行的统计量，让逻辑更清晰，避免重复计算滞后值
• 最终查询：明确区分当前行与前一行的区间，按需求用前一期统计量校验当前val，第一行状态设为NULL以体现无前置数据

内容的提问来源于stack exchange，提问作者Homer Jay Simpson