M5-015Medium窗口函数 · Window functions

如何计算每个用户截至当前的累计消费?

How do you compute cumulative spend per user up to each date?
💡 考场高分一句话答案:

用 SUM() OVER (PARTITION BY user_id ORDER BY dt ROWS UNBOUNDED PRECEDING) 做累计。

SUM() OVER (PARTITION BY user_id ORDER BY dt ROWS UNBOUNDED PRECEDING). Aggregate to user-day first to avoid double counting.

📌 核心考察要点 (Key Points)

  • 累计窗口:ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  • 先聚合再累计:先按用户-日聚合,再窗口,避免重复计算
  • 并列日期:同一天多笔要先去重聚合,否则累计会重复
  • 占比:累计值 / 用户总额 = 消费进度
  • Window frame: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  • Aggregate first: collapse to user-day before the window, otherwise multiple orders per day are double counted
  • Tied dates: aggregate them first, then the running total is correct
  • Use: running total divided by the user's final total gives progress

🔍 大厂面试官连环追问 (Follow-ups)

  • 如何计算“用户消费达到 1000 元的日期”?
  • 累计窗口与 RANGE 的区别?

嫌字多看太累?进入苏格拉底式对话推演实验室

零打字负担,以大厂考官真实困境切入,通过点选直觉选项暴露思维盲区,手把手推导 Why。

💬 开启对话式破冰 ➔
⚡
想检验本题的肌肉记忆?进入 3 阶闪卡主动回忆盲测
隐藏答案遮罩,自测:审题锚点 ➔ 数理公式机理 ➔ 工业标杆代码与连环追问。3 分钟攻破认知盲区!
🎯 立即在闪卡中盲测此题➔