使用Oracle MATCH_RECOGNIZE提取满足前置日期条件的分区数据

S1 P1 21/09/2022
S1 P2 20/09/2022
S1 P1 19/09/2022
S2 P1 21/09/2022
S2 P2 19/09/2022
S2 P1 19/09/2022

## 预期输出

SPLIT PART DATE OUTPUT #

S1 P1 21/09/2022 X
S1 P2 20/09/2022 NULL
S1 P1 19/09/2022 NULL
S2 P1 21/09/2022 NULL
S2 P2 19/09/2022 NULL
S2 P1 19/09/2022 NULL

## 解决方案
### 方法1：关联子查询实现
通过`EXISTS`子查询直接检查同分区内是否存在符合条件的P2记录：
```sql
SELECT 
    SPLIT,
    PART,
    DATE,
    CASE
        WHEN PART = 'P1'
             AND EXISTS (
                 SELECT 1
                 FROM your_table t2
                 WHERE t2.SPLIT = t.SPLIT
                       AND t2.PART = 'P2'
                       AND t2.DATE = DATE_SUB(t.DATE, INTERVAL 1 DAY)
             ) THEN 'X'
        ELSE NULL
    END AS OUTPUT
FROM your_table t
ORDER BY SPLIT, DATE DESC;

方法2：窗口函数辅助判断 #

先在分区内提取所有P2的日期集合，再匹配当前P1记录的前一天日期：

SELECT 
    SPLIT,
    PART,
    DATE,
    CASE
        WHEN PART = 'P1'
             AND DATE_SUB(DATE, INTERVAL 1 DAY) IN (
                 SELECT DATE
                 FROM your_table t2
                 WHERE t2.SPLIT = t.SPLIT AND t2.PART = 'P2'
             ) THEN 'X'
        ELSE NULL
    END AS OUTPUT
FROM your_table t
ORDER BY SPLIT, DATE DESC;

注：日期计算函数需适配对应数据库，比如PostgreSQL用DATE - INTERVAL '1 day'，SQL Server用DATEADD(day, -1, DATE)。

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