PostgreSQL同表按字段比对：查询全额取款后未再存款的流失客户

架构老林

2026-9-30

问题原因

你现有的SQL仅匹配了「客户存在从非0存款变为0的交易记录」这一个条件，没有校验两个核心规则：

客户当前最新的账户余额必须为0
本次全额取款的交易之后，没有任何大于0的存款记录
因此会误返回101这类后续有新存款的客户。

修正后的SQL

SELECT DISTINCT ON (customer_id)
    customer_id,
    deposit,
    ts,
    pre_deposit
FROM (
    SELECT 
        customer_id,
        deposit,
        ts,
        LAG(deposit) OVER (PARTITION BY customer_id ORDER BY ts) AS pre_deposit,
        -- 统计当前记录之后所有存款的最大值，为0说明后续无新增存款
        MAX(deposit) OVER (PARTITION BY customer_id ORDER BY ts ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS later_max_deposit,
        -- 取客户最新的账户余额
        FIRST_VALUE(deposit) OVER (PARTITION BY customer_id ORDER BY ts DESC) AS latest_deposit
    FROM t
) t
WHERE 
    latest_deposit = 0 -- 过滤当前仍有存款的客户
    AND deposit = 0 -- 当前记录为全额取款
    AND pre_deposit > 0 -- 上一笔交易为正存款
    AND COALESCE(later_max_deposit, 0) = 0 -- 全额取款后无正存款记录
ORDER BY customer_id, ts ASC;