多分区场景下用lag/lead函数计算采购环比及事件标签的SQL方法

原有SQL逻辑失效核心是两个疏漏：

• 用于判断首月记录的min(month) over()窗口仅按seller分区，漏掉了product维度，会把同卖家下其他商品的最早月份错当成当前商品的首月，导致标签判断串数据
• 没有对带欧元符号的字符串类型金额做预处理，直接做数值计算会报错，同时事件标签的判断逻辑没有严格匹配null/0的判定规则，边界场景下标签会打错

直接用分层CTE做清洗和计算，不需要逐商品写判断，不管商品量级多大都能自动适配：

WITH cleaned_data AS (
    SELECT
        month,
        seller,
        product,
        -- 清洗金额字段：移除欧元符号转数值，保留null状态
        CASE WHEN amount IS NOT NULL THEN CAST(REPLACE(amount, '€', '') AS INT) ELSE NULL END AS amount_num
    FROM purchase_table
),
calc_base AS (
    SELECT
        month,
        seller,
        product,
        amount_num,
        -- 取同卖家同商品的上一月金额，分区必须同时带seller+product，避免串商品
        LAG(amount_num) OVER (PARTITION BY seller, product ORDER BY month) AS last_amount_num
    FROM cleaned_data
)
SELECT
    month,
    seller,
    product,
    -- 拼回原金额格式
    CASE WHEN amount_num IS NOT NULL THEN CONCAT(amount_num, '€') ELSE NULL END AS amount,
    CASE WHEN last_amount_num IS NOT NULL THEN CONCAT(last_amount_num, '€') ELSE NULL END AS last_month_amount,
    -- 计算delta，兼容null场景
    CONCAT(
        COALESCE(amount_num, 0) - COALESCE(last_amount_num, 0),
        '€'
    ) AS delta,
    -- 严格按规则打标签
    CASE
        WHEN amount_num > 0 AND (last_amount_num IS NULL OR last_amount_num = 0) THEN 'new'
        WHEN (amount_num IS NULL OR amount_num = 0) AND last_amount_num > 0 THEN 'stop'
        WHEN amount_num > last_amount_num THEN 'increase'
        WHEN amount_num < last_amount_num THEN 'decrease'
    END AS event
FROM calc_base
ORDER BY month, seller, product;

• 所有需要按「卖家+商品」维度隔离计算的窗口，分区字段统一加上product，从分区层面就阻断不同商品的数据互相引用，不会出现玉米取到谷物上月金额的问题
• 先做金额字段清洗：把金额里的€符号移除转为整数类型，保留null值状态用于判断采购是否中断，所有数值计算完成后再拼回€格式，和原输出要求一致
• 事件判断完全对齐给定规则，不再依赖delta的正负间接推导，直接基于当月、上月的金额有效值（>0判定为有采购，null/0判定为无采购）做判断，覆盖所有边界场景：
  • 当月有采购、上月无采购 → 标记new
  • 当月无采购、上月有采购 → 标记stop
  • 两期都有采购且当月金额更高 → 标记increase
  • 两期都有采购且当月金额更低 → 标记decrease

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