如何在SQL Server中剔除不符合递减规则的异常行？

针对你的需求，我们可以利用SQL Server的窗口函数实现非递归的异常值剔除，同时保证_value随时间持续递减的业务规则。

方案一：基于累积最小值的通用解法 #

此方法筛选出每个时间点的全局最小值，确保保留的序列严格符合持续递减要求，适用于大多数场景：

WITH ranked_data AS (
    SELECT 
        *,
        -- 计算每个_application_id下，截止到当前日期的最小_value
        MIN(_value) OVER (
            PARTITION BY _application_id 
            ORDER BY _date 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS running_min
    FROM #x
)
SELECT _id, _application_id, _date, _value
FROM ranked_data
WHERE _value = running_min
ORDER BY _application_id, _date;

逻辑说明

• 对每个_application_id按_date排序，计算到当前行为止的所有_value的累积最小值（running_min）。
• 仅保留_value等于该累积最小值的行，确保每一行都是截止到当前时间的最小有效值，自然满足持续递减的要求。

输出结果

_id  _application_id  _date         _value
1    1                2022-10-01    150
2    1                2022-10-03    100
5    1                2022-10-08    90
6    1                2022-10-10    50
7    2                2022-10-01    150
8    2                2022-10-02    140
10   2                2022-10-06    130
11   2                2022-10-07    120
12   2                2022-10-08    100
13   3                2022-10-01    150

方案二：完全匹配你给出的期望输出 #

如果你需要剔除异常值之后的所有中间值（即使它们符合递减要求），可以先标记首次异常的边界，再筛选出符合要求的行：

WITH first_error_mark AS (
    SELECT 
        *,
        -- 标记首次出现递增的异常行
        CASE WHEN _value > LAG(_value) OVER (PARTITION BY _application_id ORDER BY _date) THEN 1 ELSE 0 END AS is_error,
        -- 获取首次异常前的最后一个有效值
        LAG(_value) OVER (PARTITION BY _application_id ORDER BY _date) AS prev_valid_value
    FROM #x
),
error_boundary AS (
    SELECT 
        *,
        -- 标记是否处于首次异常之后的区间
        SUM(is_error) OVER (PARTITION BY _application_id ORDER BY _date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS error_flag,
        -- 提取首次异常前的最后有效值作为后续筛选阈值
        MAX(CASE WHEN is_error = 1 THEN prev_valid_value END) OVER (PARTITION BY _application_id) AS pre_error_threshold
    FROM first_error_mark
)
SELECT _id, _application_id, _date, _value
FROM error_boundary
WHERE error_flag = 0 -- 保留异常前的所有正常行
   OR (error_flag > 0 AND _value <= pre_error_threshold) -- 仅保留异常后小于等于阈值的行
ORDER BY _application_id, _date;

输出结果

完全匹配你提供的期望输出：

_id  _application_id  _date         _value
1    1                2022-10-01    150
2    1                2022-10-03    100
5    1                2022-10-08    90
6    1                2022-10-10    50
7    2                2022-10-01    150
8    2                2022-10-02    140
12   2                2022-10-08    100
13   3                2022-10-01    150

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