4. 常用窗口函数概览

在反洗钱交易监测中，窗口函数的价值不在于“多一个函数”，而在于它能在不折叠明细行的前提下，为每一笔交易附加“相对位置、前后关系、累计规模、组内统计”等上下文。传统GROUP BY会把同一客户的多笔交易压缩成一行，丢失单笔交易的时间、金额、对手方等关键证据；窗口函数则保留明细粒度，同时把分组统计“贴”回每一行，这正是可疑交易识别最需要的表达能力。

一、核心原理：OVER() 决定“窗口”，函数决定“计算”

窗口函数的通用形式是 函数名(...) OVER (PARTITION BY ... ORDER BY ... 窗口帧)。其中 PARTITION BY 把数据切成互不重叠的组，类似“按客户分组”；ORDER BY 决定组内行的顺序，对排名函数和偏移函数是必需的，对聚合窗口函数则决定累计方向；窗口帧（如 ROWS BETWEEN ... AND ...）进一步限定参与计算的行范围。理解这三层，才能解释为什么同一个SUM在不同ORDER BY下会得到“累计值”或“全组总值”。

关键区分：排名类函数（ROW_NUMBER、RANK、DENSE_RANK）回答“第几”；偏移类函数（LAG、LEAD）回答“上一笔/下一笔是什么”；聚合类函数（SUM、AVG、COUNT、MIN、MAX）回答“这一组或这一段的统计量是多少”。

二、排名三兄弟：ROW_NUMBER、RANK、DENSE_RANK

三者都依赖ORDER BY，但遇到并列值时行为不同。假设同一客户在同一天有两笔金额相同的交易：

  • ROW_NUMBER()：强制给出唯一序号，即使值相同也按任意顺序编号为1、2、3，绝不重复。
  • RANK()：并列值共享同一名次，后续名次跳号，例如1、1、3。
  • DENSE_RANK()：并列值共享同一名次，后续名次不跳号，例如1、1、2。

在反洗钱场景中，ROW_NUMBER常用于“每个客户取金额最大的一笔”或“去重保留最新一条”；RANK适合“找出排名前N的可疑客户，允许并列”；DENSE_RANK适合“按风险等级分层，层与层之间连续编号”。选错函数会导致“取Top 3”时多取或少取，直接影响预警名单。

三、偏移函数：LAG 与 LEAD

LAG(col, n)取当前行之前第n行的值，LEAD(col, n)取之后第n行的值，默认n为1。它们让“跨行比较”变得自然：计算相邻两笔交易的时间间隔、金额变化率、交易对手是否切换。没有LAG/LEAD时，通常需要自连接并处理复杂的行号匹配，既慢又易错。

SELECT
  customer_id,
  txn_time,
  amount,
  LAG(amount, 1) OVER (
    PARTITION BY customer_id ORDER BY txn_time
  ) AS prev_amount,
  amount - LAG(amount, 1) OVER (
    PARTITION BY customer_id ORDER BY txn_time
  ) AS amount_delta
FROM transactions;

注意：窗口的第一行没有“上一行”，LAG返回NULL。业务上应把NULL视为“无前序交易”，而不是金额为0，否则会误判首笔交易为异常骤增。

四、聚合窗口函数：SUM、AVG、COUNT、MIN、MAX

这四个函数加上MIN/MAX，在窗口语境下有两种典型用法。第一种是不带ORDER BY的整组聚合：SUM(amount) OVER (PARTITION BY customer_id)，结果为该客户所有交易的总金额，并重复出现在每一行，便于计算“单笔占客户总额比例”。第二种是带ORDER BY的累计聚合：SUM(amount) OVER (PARTITION BY customer_id ORDER BY txn_time)，结果为截至当前行的累计金额，默认窗口帧是RANGE UNBOUNDED PRECEDING到CURRENT ROW。

函数语义反洗钱典型用途易错点
ROW_NUMBER组内唯一序号去重、取最新/最大一笔并列时顺序不确定
RANK并列同名次，后续跳号Top N可疑客户跳号导致名次不连续
DENSE_RANK并列同名次，不跳号风险分层编号与RANK混用造成分层错位
LAG / LEAD前/后第n行值相邻交易间隔、金额变化首尾行返回NULL
SUM窗口内求和累计交易额、占比漏写ORDER BY变成全组和
AVG窗口内平均偏离客户均值程度NULL参与计算被忽略
COUNT窗口内计数短时间交易频次COUNT(*)与COUNT(col)差异
MIN / MAX窗口内最小/最大金额突增、时间跨度与聚合GROUP BY混淆

五、案例：识别“分散转入、集中转出”的可疑结构

假设某客户在一天内有多笔小额转入，随后一笔大额转出。我们可以用窗口函数在明细行上同时计算累计转入、累计转出和交易序号：

SELECT
  customer_id,
  txn_time,
  direction,
  amount,
  SUM(CASE WHEN direction = 'IN' THEN amount ELSE 0 END)
    OVER (PARTITION BY customer_id ORDER BY txn_time) AS cum_in,
  SUM(CASE WHEN direction = 'OUT' THEN amount ELSE 0 END)
    OVER (PARTITION BY customer_id ORDER BY txn_time) AS cum_out,
  ROW_NUMBER() OVER (
    PARTITION BY customer_id, direction ORDER BY txn_time
  ) AS seq_in_dir
FROM transactions
WHERE txn_date = DATE '2024-06-01';

当 cum_in 快速上升而 cum_out 紧随其后出现大额跳变，且 seq_in_dir 显示多笔小额在前，就形成了典型的“分散转入、集中转出”模式。这里ROW_NUMBER负责定位笔次，SUM负责刻画累计规模，二者配合比单纯看单笔金额更可靠。

六、误区与复盘

误区一：把窗口函数当GROUP BY用。窗口函数不减少行数，若需要“每个客户一行汇总”，仍应使用GROUP BY或先窗口后去重。

误区二：忽略ORDER BY对聚合窗口的影响。加不加ORDER BY，SUM的结果可能是“累计值”或“全组总值”，语义完全不同。

误区三：在WHERE中直接过滤窗口函数结果。窗口函数在WHERE之后、ORDER BY之前执行，必须用子查询或CTE包裹后再过滤。

误区四：认为RANK和DENSE_RANK可互换。在“取前3名”时，RANK可能返回超过3行，DENSE_RANK则按名次层级返回，业务口径必须先定义清楚。

复盘建议：在编写任何窗口函数前，先明确三个问题——分组键是什么、排序键是什么、窗口帧是否需要限制。把这三个问题写进注释，再选择函数，能显著减少反洗钱规则上线后的口径争议。

← 上一章 📖 返回目录 下一章 →