如何在MySQL或Google Sheet中计算MRR Expansion（客户升级收入增量）

需求说明 #

需要计算MRR Expansion指标，即客户升级产品后的收入差值，计算规则为：
当月客户升级至高级产品的收入 - 上月该客户使用Basic产品的收入
仅在客户当月产品非Basic且与上月产品不同时计算该值，其余情况为空。

原始数据表 #

期望结果表 #

MySQL实现方案 #

使用LAG()窗口函数获取客户上月的产品和收入，再通过条件判断计算MRR Expansion值。假设数据表名为customer_subscriptions：

SELECT
    订单月份,
    客户,
    产品,
    收入,
    CASE
        WHEN 产品 != 'Basic' 
             AND LAG(产品) OVER (PARTITION BY 客户 ORDER BY 订单月份) != 产品
        THEN 收入 - LAG(收入) OVER (PARTITION BY 客户 ORDER BY 订单月份)
        ELSE NULL
    END AS `MRR Expansion`
FROM customer_subscriptions
ORDER BY 客户, 订单月份;

逻辑说明 #

1. LAG(产品) OVER (PARTITION BY 客户 ORDER BY 订单月份)：按客户分组、按订单月份排序，获取该客户上月的产品类型。
2. LAG(收入) OVER (...)：获取该客户上月的收入金额。
3. CASE条件判断：仅当当月产品不是Basic，且上月产品与当月不同时，计算收入差值，否则返回空值。

Google Sheet实现方案 #

假设原始数据在A2:D9区域（A列订单月份、B列客户、C列产品、D列收入），在E2单元格输入以下公式，然后下拉填充至E9：

=IF(AND(C2<>"Basic", C2<>OFFSET(C2,-1,0)), D2-OFFSET(D2,-1,0), "")

逻辑说明 #

1. OFFSET(C2,-1,0)：获取当前单元格上方一行（上月）的产品值。
2. OFFSET(D2,-1,0)：获取当前单元格上方一行的收入值。
3. AND(C2<>"Basic", C2<>OFFSET(C2,-1,0))：判断当月产品非Basic且与上月产品不同。
4. 满足条件时计算收入差值，否则返回空字符串。

注意：需确保数据已按客户和订单月份排序，否则OFFSET引用会出错。

内容的提问来源于stack exchange，提问作者aisyah m.