SQL窗口函数lead()中使用sum()聚合报42803错误咨询

• 核心触发点是SQL逻辑混用了普通聚合与窗口计算：未写GROUP BY子句的前提下直接使用普通聚合函数sum(amount)，数据库会要求SELECT列表中所有非聚合计算的字段（比如p.payment_id、c.first_name等）必须出现在GROUP BY子句中，直接抛出42803错误。
• 窗口函数本身的逻辑存在3处偏差：
  • 分区字段错误：partition by p.payment_id以支付单唯一ID分区，每个分区仅存在1条记录，无法获取同客户的连续支付数据
  • 排序字段错误：需求要求按支付时间排序，原SQL按amount金额排序，不符合业务规则
  • 函数用法错误：要计算连续3笔的金额和，不需要嵌套sum()再套lead()，且lead(xxx,3)是取当前行之后第3行的单个值，无法实现多笔金额求和

方案1：滑动窗口写法（推荐，通用性强） #

直接使用窗口聚合的滑动窗口子句，指定分区为客户ID、按支付时间升序排序，窗口范围覆盖当前行及之后2行，直接求和即可，代码简洁易维护：

select 
    p.payment_id,
    c.first_name,
    c.last_name,
    p.amount,
    p.payment_date,
    sum(p.amount) over (
        partition by p.customer_id 
        order by p.payment_date 
        rows between current row and 2 following
    ) as sum_pay
from payment p
left join customer c on c.customer_id = p.customer_id
order by p.customer_id, p.payment_date;

方案2：lead偏移取值写法（适配lead函数使用思路） #

如果要使用lead()函数实现，需要分别取当前行、后1行、后2行的金额，三者相加即可，不需要嵌套sum聚合：

select 
    p.payment_id,
    c.first_name,
    c.last_name,
    p.amount,
    p.payment_date,
    p.amount + 
        lead(p.amount, 1, 0) over (partition by p.customer_id order by p.payment_date) +
        lead(p.amount, 2, 0) over (partition by p.customer_id order by p.payment_date)
    as sum_pay
from payment p
left join customer c on c.customer_id = p.customer_id
order by p.customer_id, p.payment_date;

说明：lead()的第三个参数是偏移后无数据时的默认值，设为0可以避免末尾不足3笔时出现NULL值，和滑动窗口写法的计算逻辑完全一致。

计算结果验证（以测试样例中客户341的数据为例） #

• 2007-02-15支付7.99，后两笔为1.99、7.99，sum_pay=17.97
• 2007-02-16支付1.99，后两笔为7.99、2.99，sum_pay=12.97
• 最后1笔2007-02-21支付5.99，后面无支付记录，sum_pay=5.99
  完全符合每笔支付及其后连续2笔的金额总和统计要求。

内容的提问来源于stack exchange，提问作者Inthemaking