在sql server 2022中,编写带有时间序列处理的存储过程主要依赖窗口函数(如lag、lead)和日期算术函数。通过定义输入参数控制时间范围,利用cte或临时表预处理数据,再结合窗口函数计算移动平均或同比环比,即可实现高效的时间序列分析。

如何按小时聚合传感器读数并计算移动平均
该场景下,核心做法是将原始高频数据按小时分组,并使用AVG()配合OVER()子句计算7小时的滑动平均值。这能平滑噪声并突出趋势。

CREATE PROCEDURE dbo.GetSensorHourlyAvg
@StartTime DATETIME,
@EndTime DATETIME
AS
BEGIN
SET NOCOUNT ON;
WITH HourlyData AS (
SELECT
DATEADD(HOUR, DATEDIFF(HOUR, 0, ReadingTime), 0) AS HourBucket,
AVG(Value) AS AvgValue
FROM dbo.SensorReadings
WHERE ReadingTime BETWEEN @StartTime AND @EndTime
GROUP BY DATEADD(HOUR, DATEDIFF(HOUR, 0, ReadingTime), 0)
)
SELECT
HourBucket,
AvgValue,
AVG(AvgValue) OVER (ORDER BY HourBucket ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS MovingAvg7H
FROM HourlyData
ORDER BY HourBucket;
END;预期结果:返回包含HourBucket、AvgValue和MovingAvg7H三列的结果集。前6行MovingAvg7H为部分平均值,之后为完整7小时均值。
如何识别连续异常状态段
针对需要标记“连续3次以上”异常的场景,使用LAG()获取前一时刻状态,并通过逻辑判断生成组ID。这种方法避免了自连接,性能更优。

CREATE PROCEDURE dbo.IdentifyAnomalyStreaks
@Threshold DECIMAL(18,2)
AS
BEGIN
SET NOCOUNT ON;
WITH StatusFlags AS (
SELECT
ReadingTime,
Value,
CASE WHEN Value > @Threshold THEN 1 ELSE 0 END AS IsAnomaly,
LAG(CASE WHEN Value > @Threshold THEN 1 ELSE 0 END, 1, 0) OVER (ORDER BY ReadingTime) AS PrevStatus
FROM dbo.SensorReadings
),
GroupedAnomalies AS (
SELECT
ReadingTime,
Value,
IsAnomaly,
SUM(IsAnomaly - PrevStatus) OVER (ORDER BY ReadingTime) AS AnomalyGroup
FROM StatusFlags
WHERE IsAnomaly = 1 OR PrevStatus = 1
)
SELECT
MIN(ReadingTime) AS StartTIme,
MAX(ReadingTime) AS EndTime,
COUNT(*) AS DurationSamples
FROM GroupedAnomalies
WHERE IsAnomaly = 1
GROUP BY AnomalyGroup
HAVING COUNT(*) >= 3;
END;预期结果:仅返回持续时间大于等于3个采样点的异常区间,包含开始时间、结束时间和样本数。AnomalyGroup用于区分不连续的异常段。
当输入时间范围跨越夏令时切换时会发生什么
若@StartTime或@EndTime跨越夏令时边界,DATEADD和DATEDIFF基于UTC偏移量计算,可能导致小时桶错位。需显式转换时区或使用AT TIME ZONE确保一致性。

















