SQL实现：同分类同星期历史均值容差校验查询

基础信息 #

现有业务表包含3个字段：

• Category：数据分类标识
• Value：int类型，待校验的数值
• Date：数据对应的日期

需求规则 #

需要实现的校验逻辑：

1. 对每条记录，找到和它同分类、同星期几、且日期早于该记录的历史数据
2. 从符合条件的历史数据中取时间最近的100条，计算这部分数据Value的平均值作为容差基准
3. 按照传入的容差比例参数t，计算合法区间为[基准值*(1-t), 基准值*(1+t)]，当前记录Value落在区间内则校验标识check_avg返回1，否则返回0

原有代码问题 #

原有实现存在两处明显缺陷：

• 分类值写死为CategoryX，没有做动态关联匹配，也未加入「同星期几」的筛选维度，仅用固定的700天日期范围捞取数据，不符合取最近100条同维度历史数据的规则
• 校验逻辑仅判断Value是否大于均值，没有实现双向容差区间的校验

原有代码片段：

SELECT Value, Date, 
CASE WHEN 
    value > (SELECT AVG(value) FROM Table WHERE Category = 'CategoryX' and Date BETWEEN current_date - 700 and current_date - 1) THEN 1 
    ELSE 0 
    END AS check_avg
FROM Table
WHERE Category = 'CategoryX'

样例验证 #

给出的样例数据如下：

以2022-06-29的CategoryX记录为例：

• 该日期为周三，同分类下早于该日期、且同为周三的历史数据Value分别为1000（2022-06-22）、2000（2022-06-15）、3000（2022-06-08），平均值为2000
• 若容差t为50%，合法区间为1000~3000，当前记录Value为5000超出范围，check_avg应返回0，符合预期。

可运行实现代码（支持MySQL 8.0及以上、PostgreSQL等支持窗口函数的数据库） #

WITH data_with_avg AS (
    SELECT
        Category,
        Value,
        `Date`,
        -- 按同分类、同星期分区，按日期倒序，取当前记录之前（即日期更早）的最近100条计算均值
        AVG(Value) OVER (
            PARTITION BY Category, WEEKDAY(`Date`) -- 不同数据库星期函数可按需替换：MySQL用WEEKDAY/DAYOFWEEK，PG用DATE_PART('dow', Date)
            ORDER BY `Date` DESC
            ROWS BETWEEN 1 FOLLOWING AND 100 FOLLOWING
        ) AS hist_avg
    FROM `Table`
)
SELECT
    Category,
    Value,
    `Date`,
    -- 下方0.5替换为实际容差参数t即可，比如t=0.2代表20%容差
    CASE WHEN Value BETWEEN hist_avg * (1 - 0.5) AND hist_avg * (1 + 0.5) THEN 1 ELSE 0 END AS check_avg
FROM data_with_avg
-- 若需要仅查询指定分类，放开下方注释替换分类值即可
-- WHERE Category = 'CategoryX'
;

说明：如果使用的是不支持窗口函数的低版本MySQL，可以通过关联子查询加行号限制的方式实现，核心逻辑是对每条记录关联同分类、同星期、日期更早的数据，按日期倒序取前100条算均值后再做区间判断。

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