如何让rank()窗口函数忽略NULL值，统计客户已支付交易数

需求说明 #

统计每个客户在每笔交易（按CODE排序，CODE值越小交易时间越早）之前的已支付交易数量，无论当前交易的PAYMENT是否为NULL，都需要返回正确的累计计数。

正确SQL代码 #

SELECT 
  CLIENT,
  CODE,
  PAYMENT,
  COALESCE(
    SUM(CASE WHEN PAYMENT IS NOT NULL THEN 1 ELSE 0 END) 
    OVER (
      PARTITION BY CLIENT 
      ORDER BY CODE 
      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ), 
    0
  ) AS PAID_PURCHASES_SO_FAR
FROM FOO.BAR
ORDER BY CLIENT, CODE;

代码解释 #

1. 累计求和窗口函数：使用SUM() OVER()逐行累计符合条件的记录数。通过CASE WHEN PAYMENT IS NOT NULL THEN 1 ELSE 0 END将已支付交易标记为1，未支付（NULL）的标记为0。
2. 窗口范围控制：ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING指定统计范围为当前客户的第一行到当前行的前一行，精准匹配“当前交易之前”的需求。
3. 首行值处理：用COALESCE()将首行的NULL结果替换为0，因为首行之前无任何交易，已支付数量为0。

原代码问题分析 #

原代码使用DENSE_RANK()并按CLIENT和PAYMENT是否非NULL分区，导致两个核心问题：

• NULL支付的交易被单独划分到一个分区，无法累计前面的已支付交易数量；
• 仅在PAYMENT非NULL时计算排名，NULL行的结果直接为NULL，不符合预期中NULL行也要显示累计数的要求。

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