SQL中使用Lag()计算Diff时规避首个零值问题

问题核心是：首日的客户基数是当日新客户数（10000），而非回头客数（0），但原SQL取的是前一天的回头客数，导致次日Diff计算错误。

解决方案 #

通过判断前一天的回头客数值，动态切换取数逻辑：次日取首日的新客户数作为基数；后续日期仍取前一天的回头客数作为基数。

优化后的SQL（使用CTE简化重复计算）

with daily_data as (
    select 
        fact_date,
        NewCustomer,
        ReturningCustomer,
        lag(ReturningCustomer) over (order by fact_date) as prev_returning,
        lag(NewCustomer) over (order by fact_date) as prev_new
    from your_table
)
select 
    fact_date,
    NewCustomer,
    ReturningCustomer,
    case 
        when prev_returning = 0 then prev_new
        else prev_returning
    end as PrevDay,
    ReturningCustomer - case 
        when prev_returning = 0 then prev_new
        else prev_returning
    end as Diff
from daily_data
order by fact_date;

执行结果

逻辑说明 #

• 用CTE提前计算前一天的回头客（prev_returning）和前一天的新客户（prev_new）
• 通过case语句判断：如果前一天的回头客为0（仅首日满足），则取前一天的新客户数作为计算基数；否则取前一天的回头客数
• 最终Diff值为当日回头客数减去对应基数，得到正确的流失量

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