SQL中使用Partition和Lag函数标记数值下降的前一行

原查询使用lag()函数对比当前行与前一行的金额，导致标记出现在金额下降的当前行。要将标记转移到下降行的前一行，需要改用lead()函数提前预判下一行的金额变化和日期连续性：

修正后的SQL语句：

select 
    account, 
    amount,
    case 
        when amount > lead(amount) over (partition by account order by start_date) 
             and lead(start_date) over (partition by account order by start_date) = end_date + 1
        then 1 
        else 0 
    end as flag, 
    start_date, 
    end_date
from table

逻辑说明 #

• lead(amount) over (...)：获取当前行的下一行金额，用来判断当前行金额是否比下一行高（即下一行是下降行）
• lead(start_date) over (...) = end_date + 1：验证下一行的起始日期是否与当前行的结束日期连续，保留原逻辑的日期校验规则
• 满足条件时，当前行（即下降行的前一行）的flag设为1，否则为0

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