动态平均计算：如何自动配置半周期平均的窗口行数

问题 #

需要在成本数据表中添加两个平均成本列，计算不同时间段的平均值：

• 全时段平均列：计算所有周期的成本平均值，比如6个月数据就取全部6个值的平均
• 半周期平均列：根据总周期数自动计算最近半段周期的平均值，比如6个周期时取最近3个值的平均；尝试用窗口函数AVG(COST) OVER (ORDER BY PERIOD ROWS BETWEEN x PRECEDING AND CURRENT ROW)实现，但需要自动确定x的值（如6周期时x=2，由(CEILING(COUNT(PERIOD)/2)-1)计算得出）

示例数据表：

期望输出：

解决方案 #

可以通过子查询获取总周期数计算出半周期的偏移量x，再结合窗口函数实现需求，完整SQL语句如下：

WITH total_periods AS (
    SELECT CEILING(COUNT(PERIOD) / 2) - 1 AS half_offset
    FROM your_table_name
)
SELECT
    t.Period,
    t.Cost,
    ROUND(AVG(t.Cost) OVER (), 1) AS `All Time Average Cost`,
    ROUND(AVG(t.Cost) OVER (
        ORDER BY 
            CASE t.Period 
                WHEN 'Jan' THEN 1 WHEN 'Feb' THEN 2 WHEN 'Mar' THEN 3
                WHEN 'Apr' THEN 4 WHEN 'May' THEN 5 WHEN 'Jun' THEN 6
            END
        ROWS BETWEEN total_periods.half_offset PRECEDING AND CURRENT ROW
    ), 1) AS `Half Period Average Cost`
FROM your_table_name t, total_periods
ORDER BY 
    CASE t.Period 
        WHEN 'Jan' THEN 1 WHEN 'Feb' THEN 2 WHEN 'Mar' THEN 3
        WHEN 'Apr' THEN 4 WHEN 'May' THEN 5 WHEN 'Jun' THEN 6
    END;

语句解释 #

1. CTE部分（total_periods）：先计算出半周期的偏移量half_offset，比如6个周期时，CEILING(6/2)-1 = 3-1=2，对应窗口函数中取当前行及前2行的平均值。
2. 全时段平均：AVG(t.Cost) OVER ()会计算整个数据集的平均值，不需要分区或排序，确保每行都显示相同的全时段平均。
3. 半周期平均：
   • 用CASE语句将月份转换为数字排序，保证Period按时间顺序排列（如果你的Period字段是日期类型，直接用ORDER BY t.Period即可）
   • 窗口范围指定为ROWS BETWEEN half_offset PRECEDING AND CURRENT ROW，自动应用计算好的偏移量，实现最近半段周期的平均
4. ROUND函数：用于将平均值保留1位小数，和期望输出格式一致。

注意事项 #

• 替换your_table_name为实际表名
• 如果Period是标准日期格式（如'2024-01'），可以去掉CASE排序，直接用ORDER BY t.Period，更通用。

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