3. SQL窗口函数入门

在反洗钱交易监测中，我们经常需要回答这类问题：某账户当前这笔交易，与它过去7天的交易相比是否异常？同一客户在同一天内是否在多个地区发生交易？这些问题的共同点是——既要保留每一行明细，又要在某个“范围”内做聚合比较。传统GROUP BY会把多行压缩成一行，丢失明细；而窗口函数恰好解决这个矛盾：它在不减少行数的前提下，为每一行计算一个基于“窗口”的值。

一、核心原理：窗口函数到底在做什么

窗口函数（Window Function）的本质是：对结果集中的每一行，定义一个与之关联的行集合（称为窗口），然后在这个窗口上执行计算，并把计算结果附加到当前行上。它不会像GROUP BY那样合并行，因此你可以在同一行里同时看到“交易明细”和“该账户近30天交易总额”。

理解窗口函数，关键要抓住三个层次：

• 分区（PARTITION BY）：把数据切成互不重叠的组，窗口计算在每个组内独立进行。例如按账户号分区，每个账户各自计算。
• 排序（ORDER BY）：决定窗口内行的顺序，影响累计、排名、前后行取值等计算。
• 窗口帧（Frame）：进一步限定“当前行”参与计算的具体行范围，例如“从分区第一行到当前行”或“当前行的前3行到后3行”。

二、语法结构与OVER子句

窗口函数的标准写法是在函数名后加 OVER()。OVER子句内部可以包含PARTITION BY、ORDER BY和窗口帧定义。基本语法如下：

函数名(参数) OVER (
    [PARTITION BY 分区列, ...]
    [ORDER BY 排序列 [ASC|DESC], ...]
    [ROWS|RANGE 窗口帧定义]
)

常见的窗口函数分为几类：聚合类（SUM、AVG、COUNT、MAX、MIN）、排名类（ROW_NUMBER、RANK、DENSE_RANK）、偏移类（LAG、LEAD、FIRST_VALUE、LAST_VALUE）。在反洗钱场景中，聚合类用于计算累计金额、滚动均值；排名类用于找出某账户交易金额的排名；偏移类用于对比上一笔交易的时间间隔或金额变化。

下面是一个最基础的例子：为每笔交易附加该账户的累计交易金额。

SELECT
    txn_id,
    account_id,
    txn_time,
    amount,
    SUM(amount) OVER (
        PARTITION BY account_id
        ORDER BY txn_time
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_amount
FROM transactions;

这里 PARTITION BY account_id 让每个账户独立累计，ORDER BY txn_time 保证按时间顺序累加，窗口帧 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 表示从分区第一行累加到当前行。

三、PARTITION BY、ORDER BY与窗口帧的协作

三者不是孤立的，它们的组合决定了计算结果。为了直观理解，下面用一个简化的交易表说明不同窗口帧的差异。假设某账户有5笔按时间排序的交易，金额分别为100、200、300、400、500。

需要特别注意：当ORDER BY存在但未显式指定窗口帧时，默认帧是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。RANGE与ROWS的区别在于：ROWS按物理行数计算，RANGE按排序值的逻辑范围计算。如果排序值有重复（例如同一天多笔交易），RANGE会把所有相同值的行都纳入当前行范围，导致累计结果与预期不同。在反洗钱监测中，交易时间常有重复，建议明确使用ROWS以避免歧义。

四、反洗钱场景案例：滚动窗口识别异常交易

假设我们需要监测“同一账户在最近3笔交易中，是否出现金额突然放大3倍以上”的可疑行为。可以利用窗口函数计算前3笔交易的平均金额，再与当前交易金额比较。

SELECT
    txn_id,
    account_id,
    txn_time,
    amount,
    AVG(amount) OVER (
        PARTITION BY account_id
        ORDER BY txn_time
        ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING
    ) AS prev3_avg,
    amount / NULLIF(AVG(amount) OVER (
        PARTITION BY account_id
        ORDER BY txn_time
        ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING
    ), 0) AS ratio
FROM transactions;

这里窗口帧 ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING 表示“当前行之前的3行到前1行”，即不含当前行的前3笔交易。这样计算出的 prev3_avg 是历史基准，ratio 大于3即可标记为异常。这个例子体现了窗口帧的精确控制能力：如果写成 3 PRECEDING AND CURRENT ROW，当前笔的大额交易会拉高平均值，反而掩盖异常。

五、复盘与常见误区

初学者在使用窗口函数时，容易踩以下几类坑：

1. 混淆ROWS与RANGE：在排序值有重复时，两者结果不同。反洗钱场景中时间戳重复常见，优先使用ROWS。
2. 忽略默认窗口帧：写了ORDER BY却没写帧，导致累计而非整体聚合。若需要整体聚合，应写 OVER(PARTITION BY account_id) 不带ORDER BY。
3. 在WHERE中直接过滤窗口函数结果：窗口函数在WHERE之后执行，不能直接用于WHERE。需要先用子查询或CTE计算，再在外层过滤。
4. PARTITION BY与GROUP BY混用：两者语义不同，GROUP BY改变行数，PARTITION BY不改变行数。混用时需明确最终输出粒度。
5. 窗口帧边界写反：例如 BETWEEN CURRENT ROW AND 3 PRECEDING 是非法的，起始边界必须在结束边界之前。