在当下的业务数据分析场景中，运营与产品团队经常需要追踪每周的订单量、销售额等核心指标。为了更平滑地观察这些指标的趋势变化，计算近三周或近四周的滚动平均值成为了一种标准做法。这种需求看似简单，实则考验开发者对数据库日期处理函数以及高级分析函数的掌握程度。通过巧妙结合SQL的日期转换逻辑与窗口函数，我们可以高效且优雅地实现这一复杂的统计需求。
按周聚合统计的基础构建
要实现滚动平均，首要任务是构建按周聚合的基础数据模型。原始的业务流水数据通常精确到秒，我们需要将其截断或转换为统一的周维度标识。不同的关系型数据库在日期函数的实现上存在一定差异，因此需要根据实际使用的数据库引擎选择合适的处理方式。将日期标准化为周标识，是后续进行排序和窗口计算的绝对前提。
在MySQL数据库中，开发者通常会借助YEARWEEK()函数来提取年和周的组合标识。该函数接受两个参数，第一个参数是日期字段，第二个参数用于指定一周的起始日。传入数字1表示一周从周一开始，这完全符合国内企业的常规业务习惯。通过这种方式，我们可以轻松获取每周的起始日、结束日以及该周的汇总销售额。
-- 假设有订单表order_table，包含order_date和order_amount字段
SELECT
YEARWEEK(order_date, 1) AS week_id,
MIN(order_date) AS week_start,
MAX(order_date) AS week_end,
SUM(order_amount) AS week_sales
FROM order_table
GROUP BY YEARWEEK(order_date, 1)
ORDER BY week_id;
对于PostgreSQL用户而言，DATE_TRUNC()函数则是处理时间维度的利器。它可以直接将时间戳截断到指定的精度，例如周维度，从而直接返回该周的起始日期。需要注意的是，PostgreSQL默认将周日作为一周的开始，但在实际业务中，我们可以通过修改数据库配置或使用额外的日期运算逻辑来调整为周一，以确保数据统计口径的一致性。
SELECT
DATE_TRUNC('week', order_date) AS week_start,
SUM(order_amount) AS week_sales
FROM order_table
GROUP BY DATE_TRUNC('week', order_date)
ORDER BY week_start;
利用窗口函数实现滚动平均计算
在成功获取按周汇总的基础指标后，接下来的核心挑战是计算滚动平均值。传统的自连接方式在处理此类滑动窗口需求时，不仅代码冗长，而且执行效率低下。如今主流的数据库均支持窗口函数，这为滑动窗口计算提供了原生的、高性能的解决方案。窗口函数允许我们在不改变原有查询结果行数的情况下，对当前行及其相邻行的数据进行聚合运算。
计算滚动平均的核心在于AVG()函数与OVER()子句的完美结合。在OVER()子句中，我们需要明确指定数据的排序规则以及窗口的滑动范围。通过ROWS BETWEEN关键字，可以精确控制参与计算的物理行数。例如，若要计算包含当前周在内的近三周滚动平均，窗口范围应当设定为从当前行往前推两行，直至当前行本身。
这种基于物理行数的窗口定义方式非常直观。当数据集中存在连续的周记录时，数据库引擎会严格按照指定的偏移量提取数据并计算平均值。如果当前周之前的数据不足三周，窗口函数会自动适应实际存在的行数进行计算，而不会抛出异常。这种机制极大地简化了边界条件的处理逻辑，让数据分析代码更加健壮。
-- 以上一步的周统计结果作为子查询，计算近3周滚动平均
WITH week_stat AS (
SELECT
YEARWEEK(order_date, 1) AS week_id,
MIN(order_date) AS week_start,
SUM(order_amount) AS week_sales
FROM order_table
GROUP BY YEARWEEK(order_date, 1)
)
SELECT
week_id,
week_start,
week_sales,
-- 计算近3周滚动平均，不足3周时按实际行数计算
AVG(week_sales) OVER (
ORDER BY week_id
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS rolling_3w_avg
FROM week_stat
ORDER BY week_id;
复杂业务场景下的日期处理与数据补全
在真实的业务环境中，数据往往是不完美的，这就给按周统计带来了额外的挑战。首先是周起始日的设置问题，必须确保所有统计口径严格对齐，避免因数据库默认配置不同导致周划分错乱。其次是跨年周的处理，周标识必须包含年份信息，这样才能保证在跨年时，上一年的最后一周与下一年的第一周能够正确排序，避免时间序列发生倒挂现象。
更为棘手的问题是缺失周的处理。如果某一周没有任何业务数据发生，在基础的分组聚合查询中，这一周的数据将完全消失。这会导致原本连续的周序列出现断层，进而使得基于物理行数的窗口函数计算出错。例如，原本应该计算前三周的平均值，却因为中间缺失了一周，错误地将前四周的数据纳入了计算范围，导致分析结论失真。
为了解决缺失周带来的计算偏差，我们需要引入连续序列生成的机制。在MySQL中，可以利用递归公用表表达式动态生成一段连续的周日期序列。随后，将这个完整的序列作为主表，左连接实际的业务统计数据。对于没有业务数据的周，使用COALESCE()函数将其销售额填充为零。经过这样严密的数据补全后，再应用窗口函数，就能得出绝对准确的滚动平均值。
-- 生成近12周的连续周序列
WITH RECURSIVE week_series AS (
SELECT
YEARWEEK(DATE_SUB(CURDATE(), INTERVAL 11 WEEK), 1) AS week_id,
DATE_SUB(CURDATE(), INTERVAL 11 WEEK) AS week_start
UNION ALL
SELECT
YEARWEEK(DATE_ADD(week_start, INTERVAL 1 WEEK), 1),
DATE_ADD(week_start, INTERVAL 1 WEEK)
FROM week_series
WHERE week_start < CURDATE()
),
-- 原始周统计
week_stat AS (
SELECT
YEARWEEK(order_date, 1) AS week_id,
SUM(order_amount) AS week_sales
FROM order_table
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 11 WEEK)
GROUP BY YEARWEEK(order_date, 1)
)
-- 关联计算滚动平均
SELECT
ws.week_id,
ws.week_start,
COALESCE(ws2.week_sales, 0) AS week_sales,
AVG(COALESCE(ws2.week_sales, 0)) OVER (
ORDER BY ws.week_id
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS rolling_3w_avg
FROM week_series ws
LEFT JOIN week_stat ws2 ON ws.week_id = ws2.week_id
ORDER BY ws.week_id;
综上所述，利用SQL实现按周统计的滚动平均值，不仅仅是编写一个聚合查询那么简单。它要求开发者深入理解不同数据库的日期处理特性，熟练运用窗口函数的滑动机制，并且能够前瞻性地处理数据缺失等异常场景。通过构建连续的时间序列并进行合理的数据补全，我们能够确保业务指标的连续性与准确性。在未来的数据分析实践中，建议将这种连续序列生成的逻辑封装为通用的视图或函数，以便在各类时间序列分析需求中复用，从而进一步提升数据开发的效率与质量。