Databricks中用Window Function LEAD关联库存出入库与取消记录问题

问题场景 #

在Databricks中处理库存移动数据，每条ENTER（生产入库）和CANCEL（产品取消）操作对应一条记录，原始数据如下：

需求 #

需要将CANCEL记录的MOVEMENT关联到对应的ENTER记录（仅通过DATE_TIME顺序匹配，先进先取消），标记入库记录是否已取消，期望结果如下：

当前问题 #

使用LEAD窗口函数的SQL无法正确匹配多ENTER后接多CANCEL的场景，当前SQL如下：

SELECT a.*,
LEAD(a.MOVEMENT) OVER (PARTITION BY a.PRODUCT ORDER BY a.DATE_TIME) AS CANCEL_MOVEMENT
FROM table_movement a

解决方案 #

核心思路是分别对ENTER和CANCEL记录按时间顺序生成序号，再通过产品和序号关联匹配：

1. 为同产品下的ENTER、CANCEL记录分别按时间排序生成连续序号；
2. 通过产品+序号的组合，将CANCEL记录匹配到对应的ENTER记录；
3. 合并关联后的ENTER记录与原始CANCEL记录，还原时间顺序。

对应的Databricks SQL语句如下：

WITH enter_records AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY PRODUCT ORDER BY DATE_TIME) AS enter_seq
    FROM table_movement
    WHERE MOVEMENT_TYPE = 'ENTER'
),
cancel_records AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY PRODUCT ORDER BY DATE_TIME) AS cancel_seq
    FROM table_movement
    WHERE MOVEMENT_TYPE = 'CANCEL'
),
enter_with_cancel AS (
    SELECT 
        e.*,
        c.MOVEMENT AS CANCEL_MOVEMENT
    FROM enter_records e
    LEFT JOIN cancel_records c 
        ON e.PRODUCT = c.PRODUCT 
        AND e.enter_seq = c.cancel_seq
)
SELECT 
    MOVEMENT,
    PRODUCT,
    MOVEMENT_TYPE,
    DATE_TIME,
    QUANTITY,
    CANCEL_MOVEMENT
FROM enter_with_cancel
UNION ALL
SELECT 
    MOVEMENT,
    PRODUCT,
    MOVEMENT_TYPE,
    DATE_TIME,
    QUANTITY,
    NULL AS CANCEL_MOVEMENT
FROM cancel_records
ORDER BY DATE_TIME;

说明 #

• ROW_NUMBER()保证同产品下的ENTER和CANCEL按时间顺序生成一一对应的序号，解决多对多场景的顺序匹配问题；
• 左连接确保未被取消的ENTER记录（若存在）的CANCEL_MOVEMENT字段为NULL；
• 最终通过UNION ALL合并两类记录，并按时间排序还原原始数据顺序。

内容的提问来源于stack exchange，提问作者Leandro Guimarães