使用窗口函数 lag() 获取每个用户历史付费记录中倒数第三、第二和第一次的金额,并将这三个值相加即可得到总额。具体做法是在子查询中按用户分组并按付费时间降序排列,利用 lag(amount, 1) 和 lag(amount, 2) 分别提取前两次和前三次的金额,最后在外层求和并过滤掉不足三次记录的无效数据。

如何获取单条历史记录中的特定偏移量
通过指定 LAG() 函数的第二个参数为偏移量,可以精确提取当前行之前第 N 行的数据。在按时间倒序排列的分区中,偏移量为 1 即代表上一次(倒数第二次),偏移量为 2 即代表上上次(倒数第三次)。

WITH RankedPayments AS (
SELECT
user_id,
amount,
LAG(amount, 1) OVER (PARTITION BY user_id ORDER BY pay_date DESC) as prev_amount,
LAG(amount, 2) OVER (PARTITION BY user_id ORDER BY pay_date DESC) as prev_prev_amount
FROM payments
)
SELECT * FROM RankedPayments WHERE user_id = 1001;
预期结果:对于用户 1001,若其最近三次支付分别为 100, 200, 300(按时间正序),则倒序后第一条记录的 prev_amount 为 200,prev_prev_amount 为 300。
如何处理不足三次支付的边界情况
当用户累计支付次数少于三次时,LAG() 会返回 NULL。直接对包含 NULL 的值求和会导致结果为 NULL,因此必须使用 COALESCE() 将缺失值替换为 0,并在外层查询中排除总和为 0 或 NULL 的记录,确保只统计满足条件的用户。

WITH RankedPayments AS (
SELECT
user_id,
amount,
LAG(amount, 1) OVER (PARTITION BY user_id ORDER BY pay_date DESC) as prev_amount,
LAG(amount, 2) OVER (PARTITION BY user_id ORDER BY pay_date DESC) as prev_prev_amount
FROM payments
),
CalculatedTotals AS (
SELECT
user_id,
COALESCE(amount, 0) + COALESCE(prev_amount, 0) + COALESCE(prev_prev_amount, 0) as last_three_total
FROM RankedPayments
WHERE row_number() OVER (PARTITION BY user_id ORDER BY pay_date DESC) = 1
)
SELECT user_id, last_three_total
FROM CalculatedTotals
WHERE last_three_total > 0;
预期结果:用户 A 仅有 1 次支付,结果为 NULL 被过滤;用户 B 有 3 次支付,正确计算总和。
基于程序中某个输入、条件或结果的具体问题
如果输入数据中存在同一时刻的多笔支付,排序的不确定性可能导致选取的“最后三次”不准确。此时需要在 ORDER BY 中加入唯一标识符(如 payment_id)作为次要排序键,以确保每次执行都能获得确定且一致的结果。

















