首购是用户首次下单时间，复购是其后任意下单；需用min() over(partition by user_id)广播首购时间或row_number()排序标记，避免直接min过滤丢失行上下文，注意时区、精度及引擎兼容性。
什么是首购和复购的窗口函数定义
首购是用户第一次下单的时间，复购是该用户在首购之后的任意一次下单。用窗口函数实现的关键在于：对每个 user_id 按 order_time 排序，取第一条记录作为首购时间，再判断当前订单是否晚于该时间。
不能直接用 MIN(order_time) 做条件过滤——那会丢失行级上下文；必须用窗口函数把首购时间“广播”到每一行，才能做逐行比对。
用 MIN() OVER() 计算首购时间并标记首购订单
核心是给每个用户打上统一的首购时间戳，再用布尔表达式识别首购行：
SELECT user_id, order_id, order_time, MIN(order_time) OVER (PARTITION BY user_id ORDER BY order_time ROWS UNBOUNDED PRECEDING) AS first_order_time, CASE WHEN order_time = MIN(order_time) OVER (PARTITION BY user_id) THEN 1 ELSE 0 END AS is_first_purchase FROM orders;
-
MIN(order_time) OVER (PARTITION BY user_id)才能拿到全局最小值；带ORDER BY的写法（如上面第一行）在某些引擎里可能产生非预期结果，慎用 - 注意时区和精度：如果
order_time是TIMESTAMP且含毫秒，相等判断可能失败，建议先CAST(... AS DATE)或用TRUNC()对齐粒度 - MySQL 8.0+、PostgreSQL、Snowflake、BigQuery 都支持；但 Hive 和早期 Spark SQL 不支持
CASE中嵌套窗口函数，需拆成子查询
用 ROW_NUMBER() 区分首购与复购行为
比起时间比对，用序号更稳定，尤其当存在多笔同时间首单（如批量导入）时：
SELECT user_id, order_id, order_time, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_time, order_id) AS rn, CASE WHEN ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_time, order_id) = 1 THEN 'first' ELSE 'repeat' END AS purchase_type FROM orders;
- 务必在
ORDER BY中加入唯一列（如order_id），避免ROW_NUMBER()非确定性排序 - 复购不等于“第二次购买”，而是“非首次购买”，所以不用
rn = 2判定复购，而用rn > 1 - 如果业务要求区分“二购”“三购”等，可直接用
rn值，无需额外逻辑
统计生命周期阶段人数时容易漏掉空值或重复计数
常见错误是直接对标记字段 GROUP BY 后 COUNT(*)，却没处理：未完成首购的用户（user_id 只有无效订单）、同一用户多设备注册导致的跨 ID 行为、或测试订单混入生产数据。
- 先用
DISTINCT user_id去重再统计，比对原始订单数更能反映真实用户量 - 首购率 =
COUNT(DISTINCT CASE WHEN is_first_purchase = 1 THEN user_id END) * 1.0 / COUNT(DISTINCT user_id)，分母必须是全量去重用户，不是订单数 - 复购用户数 ≠ 复购订单数，要统计“至少有一笔复购的用户”，得用
COUNT(DISTINCT CASE WHEN rn > 1 THEN user_id END)
窗口函数本身不解决数据质量问题，但它让清洗逻辑可以下推到 SQL 层——只要原始表里 user_id 和 order_time 可信，后续所有生命周期指标就都可控。
