Oracle 12.2.0.2.1数据库：查询同一物品的连续PROT违规事件

我们有一张记录物品保护操作的表（Oracle 12.2.0.2.1），字段说明如下：

• TENURE_NUMBER_ID：物品唯一标识符
• EVENT_NUMBER：事件唯一标识符
• EVENT_TYPE：事件类型，PROT代表添加保护，RMPR代表移除保护

规则要求同一物品不能连续添加保护，必须先移除现有保护才能添加新的。现在需要找出所有违反该规则的连续PROT事件。

样本数据 #

解决方案SQL #

SELECT
    TENURE_NUMBER_ID,
    EVENT_NUMBER,
    EVENT_TYPE,
    PREV_EVENT_NUMBER,
    PREV_EVENT_TYPE
FROM (
    SELECT
        TENURE_NUMBER_ID,
        EVENT_NUMBER,
        EVENT_TYPE,
        LAG(EVENT_NUMBER) OVER (PARTITION BY TENURE_NUMBER_ID ORDER BY EVENT_NUMBER) AS PREV_EVENT_NUMBER,
        LAG(EVENT_TYPE) OVER (PARTITION BY TENURE_NUMBER_ID ORDER BY EVENT_NUMBER) AS PREV_EVENT_TYPE
    FROM
        your_table_name -- 替换为实际表名
)
WHERE
    EVENT_TYPE = 'PROT'
    AND PREV_EVENT_TYPE = 'PROT';

逻辑说明 #

1. 窗口函数LAG()：按物品ID（TENURE_NUMBER_ID）分组，按事件编号（EVENT_NUMBER，假设编号递增对应事件发生顺序，若表中有时间字段请替换为时间列）排序，获取当前事件的上一个事件的编号和类型。
2. 筛选条件：只保留当前事件和上一个事件均为PROT的记录，这些就是违反规则的连续添加操作。

样本数据的预期结果 #

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