SQL查询：如何计算不含当前行的用户历史最高付款比例

要实现当前交易前用户是否有至少33%付款的历史销售这个需求，核心是要在窗口函数中排除当前行，只计算历史交易的最高paidp。

问题根源 #

你之前使用的窗口函数max(paidp) over (partition by cpf order by id_trx asc)，默认窗口范围是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，会包含当前行的数值，导致判断逻辑错误。

解决方案 #

以下两种方法都能实现排除当前行的需求，可根据你的数据库类型选择：

方法1：显式指定窗口范围（通用兼容）

通过ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING明确限定窗口只包含当前行之前的所有历史行，这是所有支持窗口函数的数据库都兼容的写法：

SELECT
    id_trx,
    cpf,
    paidp,
    -- 处理首次交易无历史记录的情况，用COALESCE把NULL转为0
    CASE
        WHEN COALESCE(max_paidp_before, 0) >= 0.33 THEN '有历史达标记录'
        ELSE '无历史达标记录'
    END AS 标记
FROM (
    SELECT
        id_trx,
        cpf,
        paidp,
        max(paidp) OVER (
            PARTITION BY cpf
            ORDER BY id_trx ASC
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS max_paidp_before
    FROM 你的销售表名 -- 替换为实际表名
) AS trx_with_history;

方法2：使用EXCLUDE CURRENT ROW子句（简洁但需数据库支持）

部分数据库（如PostgreSQL 11+、MySQL 8.0.22+）支持EXCLUDE CURRENT ROW子句，可直接排除当前行，写法更简洁：

SELECT
    id_trx,
    cpf,
    paidp,
    CASE
        WHEN COALESCE(max(paidp) OVER (
            PARTITION BY cpf
            ORDER BY id_trx ASC
            EXCLUDE CURRENT ROW
        ), 0) >= 0.33 THEN '有历史达标记录'
        ELSE '无历史达标记录'
    END AS 标记
FROM 你的销售表名; -- 替换为实际表名

关键说明 #

• 如果id_trx存在重复值，优先用ROWS子句（基于行位置），避免RANGE子句因值重复误包含同批次的其他行。
• COALESCE函数用于处理用户首次交易的场景：此时没有历史行，max_paidp_before会返回NULL，转成0后就能正确标记为“无历史达标记录”。

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