Oracle 19c中用LAG获取分区内非相同的上一order_date

先明确核心需求：

• 按episode_id+file_id分区
• 同分区内，相同order_date的所有行，previous_order_date为null
• 仅取当前order_date之前的更早日期；可选要求：更早日期需与当前日期间隔至少1分钟

示例数据（建表+插入） #

-- 建表语句
CREATE TABLE my_table (
    episode_id NUMBER,
    file_id NUMBER,
    offer_id NUMBER,
    order_date TIMESTAMP
);

-- 插入示例数据
INSERT INTO my_table VALUES (1, 1, 100, TO_TIMESTAMP('2024-05-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO my_table VALUES (1, 1, 101, TO_TIMESTAMP('2024-05-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO my_table VALUES (1, 1, 102, TO_TIMESTAMP('2024-05-01 10:02:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO my_table VALUES (1, 1, 103, TO_TIMESTAMP('2024-05-01 10:05:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO my_table VALUES (1, 1, 104, TO_TIMESTAMP('2024-05-01 10:05:00', 'YYYY-MM-DD HH24:MI:SS'));
COMMIT;

方案1：基础需求（仅取更早order_date，无时间间隔限制） #

核心思路：先对分区内的order_date去重，再计算每个唯一日期的上一个更早日期，最后关联回原表。避免同order_date的行互相引用。

WITH unique_dates AS (
    SELECT 
        episode_id,
        file_id,
        order_date,
        -- 对去重后的日期计算上一个更早日期
        LAG(order_date) OVER (PARTITION BY episode_id, file_id ORDER BY order_date) AS prev_date
    FROM (
        -- 去重分区内的order_date
        SELECT DISTINCT episode_id, file_id, order_date
        FROM my_table
    )
)
SELECT 
    t.episode_id,
    t.file_id,
    t.offer_id,
    t.order_date,
    ud.prev_date AS previous_order_date
FROM my_table t
LEFT JOIN unique_dates ud 
    ON t.episode_id = ud.episode_id 
    AND t.file_id = ud.file_id 
    AND t.order_date = ud.order_date
ORDER BY t.episode_id, t.file_id, t.order_date, t.offer_id;

输出结果（符合预期） #

方案2：满足时间间隔至少1分钟的需求 #

在方案1基础上，增加时间间隔判断，仅当上一个日期与当前日期间隔≥1分钟时才赋值，否则设为null。

WITH unique_dates AS (
    SELECT 
        episode_id,
        file_id,
        order_date,
        -- 仅间隔≥1分钟时保留上一个日期，否则设为null
        CASE 
            WHEN order_date - LAG(order_date) OVER (PARTITION BY episode_id, file_id ORDER BY order_date) >= INTERVAL '1' MINUTE
            THEN LAG(order_date) OVER (PARTITION BY episode_id, file_id ORDER BY order_date)
            ELSE NULL
        END AS prev_date
    FROM (
        SELECT DISTINCT episode_id, file_id, order_date
        FROM my_table
    )
)
SELECT 
    t.episode_id,
    t.file_id,
    t.offer_id,
    t.order_date,
    ud.prev_date AS previous_order_date
FROM my_table t
LEFT JOIN unique_dates ud 
    ON t.episode_id = ud.episode_id 
    AND t.file_id = ud.file_id 
    AND t.order_date = ud.order_date
ORDER BY t.episode_id, t.file_id, t.order_date, t.offer_id;

直接新增并填充表列 #

如果需要将previous_order_date作为物理列添加到表中，可执行以下操作：

基础需求版本 #

-- 新增列
ALTER TABLE my_table ADD previous_order_date TIMESTAMP;

-- 填充数据
MERGE INTO my_table t
USING (
    SELECT 
        episode_id,
        file_id,
        order_date,
        LAG(order_date) OVER (PARTITION BY episode_id, file_id ORDER BY order_date) AS prev_date
    FROM (
        SELECT DISTINCT episode_id, file_id, order_date
        FROM my_table
    )
) ud
ON (t.episode_id = ud.episode_id AND t.file_id = ud.file_id AND t.order_date = ud.order_date)
WHEN MATCHED THEN UPDATE SET t.previous_order_date = ud.prev_date;

时间间隔版本 #

将上述MERGE语句中的USING子查询替换为方案2的unique_dates逻辑即可。

原方法问题分析 #

• 基础LAG：直接在原表上使用LAG(order_date)会将同order_date的不同offer_id行视为连续行，导致后一行拿到前一行的同日期值，不符合需求。
• CTE+DENSE_RANK：若未先对order_date去重，仅靠排名关联会导致行重复，无法保证同order_date行的previous_order_date统一且正确。

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