修正获取间隔7天循环标记峰值的SQL Server存储过程

原代码问题分析 #

• 原存储过程会将每个7天区间内的所有行插入到目标表，而非仅保留区间内峰值（最大值）对应的单行
• 使用窗口函数MAX(p.SA_data) OVER (...)计算的是滚动最大值（截至当前行的前6天+当前行的最大值），并非整个7天区间的全局最大值
• 未筛选出区间内最大值对应的具体日期和数值，不符合期望输出的单行峰值要求

正确的存储过程实现 #

CREATE PROCEDURE SelectPeaksAndInsertIntoPeakFact 
AS
BEGIN
    SET NOCOUNT ON; -- 减少不必要的消息输出

    -- 使用CTE划分7天周期，找出每个周期内的峰值记录
    WITH DatePeriods AS (
        SELECT 
            p.dateTime,
            p.SA_data,
            -- 计算每条记录所属的周期编号（从最小日期开始，每7天为一个周期）
            DATEDIFF(DAY, (SELECT MIN(dateTime) FROM Peak), p.dateTime) / 7 AS PeriodNumber
        FROM Peak p
    ),
    PeakPerPeriod AS (
        SELECT 
            dateTime AS DataDate,
            SA_data AS peak,
            PeriodNumber,
            -- 标记每个周期内的最大值行（若有多个相同最大值，取日期最晚的）
            ROW_NUMBER() OVER (PARTITION BY PeriodNumber ORDER BY SA_data DESC, dateTime DESC) AS RowRank
        FROM DatePeriods
    )
    INSERT INTO Peak_fact (DataDate, peak, peakType)
    SELECT 
        DataDate,
        peak,
        -- 按周期编号循环分配peakType：Peak1→Peak2→Peak3→Peak1...
        CASE 
            WHEN PeriodNumber % 3 = 0 THEN 'Peak1'
            WHEN PeriodNumber % 3 = 1 THEN 'Peak2'
            ELSE 'Peak3'
        END AS peakType
    FROM PeakPerPeriod
    WHERE RowRank = 1; -- 仅保留每个周期的峰值行
END;

逻辑说明 #

• DatePeriods CTE：以表中最小日期为起始点，计算每条记录所属的7天周期编号，完成数据的周期划分
• PeakPerPeriod CTE：在每个周期内按数值降序、日期降序排序，标记出最大值对应的唯一行（RowRank=1）
• 插入逻辑：筛选出每个周期的峰值行，通过PeriodNumber % 3循环分配Peak1/Peak2/Peak3类型，最终插入到目标表

验证结果 #

执行该存储过程后，Peak_fact表将得到与期望一致的结果：

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