客户生命周期价值（CLV）不能靠单个窗口函数一步算出，必须组合LAG、LEAD、SUM OVER和子查询，核心是先锁定每个客户的“首单日”和“末单日”，再在该时间范围内聚合消费。
直接说结论：客户生命周期价值（CLV）不能靠单个窗口函数一步算出，必须组合 LAG、LEAD、SUM OVER 和子查询，核心是先锁定每个客户的“首单日”和“末单日”，再在该时间范围内聚合消费。
怎么定义客户生命周期起点和终点
CLV 的前提是明确“生命周期”边界——不是按自然年，而是按客户行为。常见错误是直接用 MIN(order_date) 和 MAX(order_date) 当作起止点，但实际中客户可能沉睡后复购，导致窗口拉得太长，把非活跃期也计入。
- 稳妥做法：以首次下单为起点，以最近一次下单为终点，且要求中间无超 180 天断档（可根据业务调）
- 若需识别“流失再激活”，就得用
LAG(order_date, 1) OVER (PARTITION BY user_id ORDER BY order_date)算间隔，过滤掉大于阈值的断档行 - 起点必须是真实首单，不能用注册时间——很多用户注册不购物，会高估 CLV
用窗口函数算累计消费时别漏掉排序和框架
很多人写 SUM(amount) OVER (PARTITION BY user_id) 就停了，结果得到的是客户全量历史总消费，不是“生命周期内”的——因为没限定时间范围，也没保证顺序。
- 必须加
ORDER BY order_date，否则窗口默认无序，SUM 可能跨时间乱加 - 要算“截至当前订单的累计消费”，得显式写
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW - 如果只想要整个生命周期总值（不是滚动值），就不用
ORDER BY，但得先通过子查询把订单限制在生命周期区间内，再做SUM(amount) OVER (PARTITION BY user_id)
为什么不能直接用 RANGE 框架算 CLV
RANGE BETWEEN INTERVAL '365' DAY PRECEDING AND CURRENT ROW 看似方便，但在客户生命周期场景里极易出错。
- 客户订单不连续，RANGE 会把“过去一年内所有订单”都拉进来，哪怕中间断了两年，也会把早期订单重复计入多个周期
- 金融/电商数据常有节假日、促销囤货，按时间滑动会导致同客户不同订单的 CLV 值跳变，失去可比性
- 正确做法是用
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING配合生命周期子查询，确保只算固定区间内的订单
CLV 分阶段建模时窗口函数怎么嵌套
业务常把 CLV 拆成“获客成本回收期”“稳定贡献期”“衰退预警期”，这时不能只靠一层窗口，得用多层 CTE 或子查询打标。
- 第一层：用
FIRST_VALUE(order_date) OVER (PARTITION BY user_id ORDER BY order_date)算首单日 - 第二层：用
LEAD(order_date, 1) OVER (PARTITION BY user_id ORDER BY order_date)算下次下单日，标记是否流失 - 第三层：用
CASE WHEN DATEDIFF(order_date, first_order) BETWEEN 0 AND 90 THEN 'early'打阶段标签，再对标签分组 SUM - 注意：
LAST_VALUE默认不包含当前行，必须加ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING才能取到末单
真正麻烦的不是语法，而是生命周期边界的业务定义——它会随渠道、产品、地域变化。写死 365 天或“首末单之间”只是起点，上线前一定得和业务方对齐断档阈值、是否剔除退款单、是否含试用订单这些细节，否则窗口算得再准，结果也是错的。
