求SQLite中移除分位数异常值的滚动平均值计算SQL代码

问题说明 #

需要计算传感器数据字段Y0的滚动平均值Y_clean_avg，规则为：在大小为20的滚动窗口内（包含当前行，最少1条数据），先移除超出窗口内0.9分位数的异常值，再计算剩余值的平均值。

实现思路 #

SQLite没有原生支持窗口分位数函数，因此需要通过子查询关联滚动窗口数据的方式实现：

• 先为每条数据按时间排序生成行号，用于确定滚动窗口的范围
• 对每一行，关联其前19行（加上当前行共20行）组成滚动窗口
• 在窗口内计算0.9分位数，筛选出小于等于该分位数的Y0值后计算平均值

完整SQL代码 #

WITH ranked_data AS (
    -- 为数据按时间排序并生成行号，用于确定滚动窗口范围
    SELECT 
        time,
        Y0,
        ROW_NUMBER() OVER (ORDER BY time) AS row_num
    FROM sensor_data
)
SELECT 
    rd.time,
    rd.Y0,
    -- 普通滚动平均值
    AVG(rd2.Y0) OVER (ORDER BY rd.time ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS Y_avg,
    -- 滚动最小值
    MIN(rd2.Y0) OVER (ORDER BY rd.time ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS Y_min,
    -- 移除异常值后的滚动平均值
    (SELECT AVG(Y0)
     FROM (
         SELECT Y0
         FROM ranked_data
         WHERE row_num BETWEEN MAX(1, rd.row_num - 19) AND rd.row_num
         ORDER BY Y0
         LIMIT (SELECT CAST(COUNT(*) * 0.9 AS INTEGER) + 1
                FROM ranked_data
                WHERE row_num BETWEEN MAX(1, rd.row_num - 19) AND rd.row_num)
     ) AS filtered) AS Y_clean_avg
FROM ranked_data rd
LEFT JOIN ranked_data rd2 ON rd2.row_num BETWEEN MAX(1, rd.row_num - 19) AND rd.row_num
GROUP BY rd.row_num, rd.time, rd.Y0
ORDER BY rd.time;

代码解释 #

1. ranked_data CTE：为每条数据按time排序生成row_num，方便后续确定滚动窗口的起始和结束行。
2. 滚动窗口范围：通过row_num BETWEEN MAX(1, rd.row_num - 19) AND rd.row_num确保窗口最多包含20条数据（当前行+前19行），且当数据不足20条时从第1行开始（满足min_periods=1的要求）。
3. 0.9分位数筛选：
   • 先统计窗口内的数据条数，计算COUNT(*) * 0.9并取整加1，得到需要保留的前N条数据（即小于等于0.9分位数的部分）
   • 对窗口内的Y0排序后取前N条，再计算这些值的平均值
4. 原有字段保留：保留了原SQL中的Y_avg和Y_min字段，确保功能兼容

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