SQL查询的值与相似记录的平均值比较

Question

表有3列:Category, Value(int)， Date

我想要的SQL查询要做的是检查属于特定类别的每条记录，如果该值位于特定的公差范围内(例如t)的平均值超过过去100条记录，这些记录具有相同的工作日(星期一，星期二等)和相同的类别作为有关记录。

我能够部分地实现这一点，因为我事先知道类别，但是工作日取决于查询的记录。此外，目前我只是检查值是否大于平均值，而不是检查它是否在某个公差范围内。

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'

示例:

<表类>类别价值日期tbody><<tr>CategoryX50002022-06-29CategoryX45002022-06-27CategoryX10002022-06-22CategoryY45002022-06-15CategoryX20002022-06-15CategoryX30002022-06-08

验证在两个查询中您正在评估相同的类别和相同的工作日。然后对将用于按日期计算平均值的值进行排序，只获取最近的前100条记录。最后，检查电流值与平均值之间的差值是否低于公差区间epsilon。

SELECT Value, Date, 
CASE WHEN 
ABS(value - (SELECT AVG(Value) FROM (SELECT TOP 100 Value FROM Table WHERE Category = t.Category and DATEPART(WEEKDAY, Date)=DATEPART(WEEKDAY, t.Date) AND Date <= t.Date ORDER BY Date DESC ))) < epsilon THEN 1 
ELSE 0 
END AS check_avg
FROM Table t
WHERE Category = 'CategoryX'