【MySQL】窗口函数实现滑动平均计算，数据清洗缺失值填补均值

---

路过了学校花店
荒野到海边
有一种浪漫的爱
是浪费时间
徘徊到繁华世界
才发现你背影
平凡得特别
绕过了城外边界
还是没告别
爱错过了太久
反而错得完美无缺
幸福兜了一个圈

                     🎵 林宥嘉《兜圈》

---

在数据分析和处理过程中，常常需要对一系列时间序列数据进行处理，例如计算滑动平均值。这篇博客将通过一个具体的SQL查询案例，展示如何利用SQL窗口函数来实现这一目标。

案例背景

假设我们有一个名为user_balance的表，记录了用户的账户余额信息。该表的结构如下：

• user_id：用户ID
• op_time：操作时间
• balance：账户余额
  我们的目标是计算每个用户在特定时间点的余额，如果余额缺失，则使用滑动窗口内的平均余额来填补。具体地，我们希望在特定日期2023-06-01获取每个用户的余额或滑动平均余额。

SQL 查询解释

下面是实现这一需求的SQL查询：

SELECT
    user_id,
    op_time,
    COALESCE(balance, avg_balance) AS balance
FROM (
    SELECT
        user_id,
        op_time,
        balance,
        AVG(balance) OVER (
            PARTITION BY user_id
            ORDER BY op_time
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ) AS avg_balance
    FROM
        user_balance
) AS subquery
WHERE
    op_time = '2023-06-01'
ORDER BY
    user_id, op_time;

查询分解

1. 内部查询：

   首先，我们在内部子查询中使用窗口函数AVG()计算每个用户在操作时间窗口内的平均余额。
   PARTITION BY user_id：将数据按用户ID分区，确保每个用户的计算是独立进行的。
   ORDER BY op_time：按操作时间排序，确保计算的滑动窗口是基于时间顺序的。
   ROWS BETWEEN 2 PRECEDING AND CURRENT ROW：定义窗口范围为当前行及前两行。

2. 外部查询：

   使用COALESCE(balance, avg_balance)函数来处理缺失值，如果balance为空，则使用avg_balance替代。
   过滤出在特定日期2023-06-01的数据。
   最后按用户ID和操作时间排序，确保结果的可读性。

数据案例

假设我们有以下user_balance表数据：

user_id	op_time	balance
1	2023-04-01	100
1	2023-05-01	NULL
1	2023-06-01	150
2	2023-04-01	200
2	2023-05-01	250
2	2023-06-01	NULL

查询结果

执行上述查询后，我们将得到以下结果：

user_id	op_time	balance
1	2023-06-01	150
2	2023-06-01	NULL

1 2023-06-01 150
2 2023-06-01 225

结果解释

对于user_id为1的用户，在2023-06-01，余额值为150，因为该日期的balance不为空。
对于user_id为2的用户，在2023-06-01，余额值为225，这是因为该日期的balance为空，因此使用前两天的滑动平均值来填补（即(200 + 250)/2 = 225）。

总结

通过这个示例，我们展示了如何使用SQL窗口函数来计算滑动平均，并处理缺失值。窗口函数提供了一种灵活且强大的方式来进行各种复杂的数据计算，特别是在处理时间序列数据时非常有用。希望这个案例能帮助你更好地理解和应用SQL窗口函数来解决实际问题。