如何用 LAG 函数计算用户消费环比增长率
使用 lag() 窗口函数获取上一期的消费金额,然后与当期金额进行对比计算。具体做法是在查询中按用户和日期排序,通过 lag(amount, 1) over (partition by user_id order by date) 拿到上期值,再用 (current - previous) / previous 算出比率。若上期值为空或零,需处理除零错误并返回 null。
SELECT
user_id,
order_date,
amount,
LAG(amount, 1) OVER (PARTITION BY user_id ORDER BY order_date) AS prev_amount,
CASE
WHEN LAG(amount, 1) OVER (PARTITION BY user_id ORDER BY order_date) IS NULL OR LAG(amount, 1) OVER (PARTITION BY user_id ORDER BY order_date) = 0 THEN NULL
ELSE (amount - LAG(amount, 1) OVER (PARTITION BY user_id ORDER BY order_date)) * 1.0 / LAG(amount, 1) OVER (PARTITION BY user_id ORDER BY order_date)
END AS growth_rate
FROM orders;预期结果:对于每个用户的每一行订单,prev_amount 显示前一行的金额,growth_rate 显示增长比例。第一行数据因无上期记录,growth_rate 为 NULL。
如何处理第一期数据的环比缺失问题
第一期数据没有上期金额,导致 LAG() 返回 NULL,进而使环比计算结果为 NULL。这是正常现象,因为环比需要至少两个时间点的数据。可以通过 COALESCE() 或 CASE 语句将 NULL 替换为特定值(如 0 或 'N/A'),或者在应用层过滤掉第一期数据。
SELECT
user_id,
order_date,
amount,
COALESCE(
(amount - LAG(amount, 1) OVER (PARTITION BY user_id ORDER BY order_date)) * 1.0 /
NULLIF(LAG(amount, 1) OVER (PARTITION BY user_id ORDER BY order_date), 0),
0
) AS growth_rate_or_zero
FROM orders;预期结果:当上期金额为 NULL 或 0 时,growth_rate_or_zero 返回 0。其他情况正常计算环比。注意 NULLIF 用于防止除以零错误。
基于用户ID分组时的性能优化建议
当数据量大且用户众多时,PARTITION BY user_id 会导致大量内存开销。可考虑预聚合到月粒度,减少行数。例如先按用户和月份汇总消费额,再对聚合后的数据应用 LAG()。这样既保持逻辑正确,又提升查询效率。
WITH monthly_spending AS (
SELECT
user_id,
DATE_TRUNC('month', order_date) AS month,
SUM(amount) AS total_amount
FROM orders
GROUP BY user_id, DATE_TRUNC('month', order_date)
)
SELECT
user_id,
month,
total_amount,
LAG(total_amount, 1) OVER (PARTITION BY user_id ORDER BY month) AS prev_month_amount,
CASE
WHEN LAG(total_amount, 1) OVER (PARTITION BY user_id ORDER BY month) IS NULL OR LAG(total_amount, 1) OVER (PARTITION BY user_id ORDER BY month) = 0 THEN NULL
ELSE (total_amount - LAG(total_amount, 1) OVER (PARTITION BY user_id ORDER BY month)) * 1.0 / LAG(total_amount, 1) OVER (PARTITION BY user_id ORDER BY month)
END AS monthly_growth_rate
FROM monthly_spending;预期结果:monthly_spending CTE 生成每月每用户总消费表,外层查询在此基础上计算月度环比。结果行数大幅减少,适合大数据量场景。

















