SQL需求：为月度快照表按成员生成Target标记字段

现有月度余额快照表，包含ID、Date、Percent_Change字段（Percent_Change为当月相对上月的余额变化率）。需生成Target字段，规则如下：

• 首月（2021-12-31）的Target值为NULL；
• 若当月相对首月的余额变化率≤-30%，则该行及该成员后续所有行的Target为1，否则为0。

已通过CTE计算出相对首月的变化率，但无法实现「触发条件后后续行持续标记1」的逻辑，寻求SQL解决方案。

with Dec2021Balance as(
select 
SnapshotDate
,MemberID
,Balance
from
balance_snapshot_table
where SnapshotDate= '2021-12-31'
)

select 
tbl2.SnapshotDate,
tbl2.MemberID,
(tbl2.Balance-Dec2021Balance.Balance)/(Dec2021Balance.Balance)
from
balance_snapshot_table tbl2
right join Dec2021Balance on Dec2021Balance.MemberID= tbl2.MemberID

可以利用窗口函数的累计最大值特性，实现触发条件后后续行持续标记1的逻辑。以下是完整的SQL代码：

with Dec2021Balance as (
    select 
        MemberID,
        Balance as FirstMonthBalance
    from balance_snapshot_table
    where SnapshotDate = '2021-12-31'
),
MemberMonthMetrics as (
    select 
        tbl2.SnapshotDate,
        tbl2.MemberID,
        -- 计算相对首月的余额变化率（保留4位小数便于查看）
        round((tbl2.Balance - d.FirstMonthBalance) / d.FirstMonthBalance, 4) as Change_From_FirstMonth,
        -- 标记当月是否触发≤-30%的阈值
        case 
            when (tbl2.Balance - d.FirstMonthBalance) / d.FirstMonthBalance <= -0.3 
            then 1 
            else 0 
        end as IsThresholdHit
    from balance_snapshot_table tbl2
    inner join Dec2021Balance d on d.MemberID = tbl2.MemberID
),
TargetCalculation as (
    select 
        *,
        -- 累计最大值：一旦某行触发阈值，后续所有行都会保留1
        max(IsThresholdHit) over (
            partition by MemberID 
            order by SnapshotDate 
            rows between unbounded preceding and current row
        ) as RawTarget
    from MemberMonthMetrics
)
select 
    SnapshotDate,
    MemberID,
    Change_From_FirstMonth,
    -- 处理首月的NULL规则
    case when SnapshotDate = '2021-12-31' then NULL else RawTarget end as Target
from TargetCalculation
order by MemberID, SnapshotDate;

关键逻辑说明 #

1. Dec2021Balance：提取每个成员2021年12月的初始余额，作为变化率计算的基准。
2. MemberMonthMetrics：计算每月相对首月的变化率，并标记当月是否达到≤-30%的阈值。
3. TargetCalculation：使用MAX(IsThresholdHit) OVER(...)窗口函数，按成员分组、日期排序。一旦某行触发阈值（IsThresholdHit=1），该成员后续所有行的累计最大值都会保持1，实现持续标记的效果。
4. 最终查询：对首月的Target单独设置为NULL，其他行使用计算出的RawTarget值。

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