Oracle中如何获取满足优先级与时间范围条件的上一行值？

问题描述 #

现有数据集如下：

priority   start_date    end_date           user     value
1   22.02.2024 11:50    22.02.2024 11:50    login-1  10
1   22.02.2024 11:50    22.02.2024 11:51    login-1  15
3   22.02.2024 12:34    22.02.2024 14:50    login-1  100
2   22.02.2024 12:40    22.02.2024 12:40    login-1  10
1   22.02.2024 12:40    22.02.2024 12:53    login-1  5
2   22.02.2024 12:53    22.02.2024 12:53    login-1  15
1   22.02.2024 12:53    22.02.2024 13:18    login-1  20
3   22.02.2024 14:01    22.02.2024 14:15    login-2  60
2   22.02.2024 14:05    22.02.2024 14:05    login-2  10
2   22.02.2024 14:05    22.02.2024 14:05    login-2  10
1   22.02.2024 14:05    22.02.2024 14:10    login-2  10
2   22.02.2024 14:10    22.02.2024 14:10    login-2  10

期望生成包含output列的结果集：

priority   start_date    end_date           user     value  output
1   22.02.2024 11:50    22.02.2024 11:50    login-1  10     null
1   22.02.2024 11:50    22.02.2024 11:51    login-1  15     null
3   22.02.2024 12:34    22.02.2024 14:50    login-1  100    null
2   22.02.2024 12:40    22.02.2024 12:40    login-1  10     100
1   22.02.2024 12:40    22.02.2024 12:53    login-1  5      100
2   22.02.2024 12:53    22.02.2024 12:53    login-1  15     100
1   22.02.2024 12:53    22.02.2024 13:18    login-1  20     100
1   22.02.2024 14:51    22.02.2024 14:55    login-1  20     null
3   22.02.2024 14:01    22.02.2024 14:15    login-2  60     null
2   22.02.2024 14:05    22.02.2024 14:05    login-2  10     60
2   22.02.2024 14:05    22.02.2024 14:05    login-2  10     60
1   22.02.2024 14:05    22.02.2024 14:10    login-2  10     60
2   22.02.2024 14:10    22.02.2024 14:10    login-2  10     60

需求说明：

• 为每行生成output列，取值需满足：
  • 来自同一用户的行
  • 该行优先级高于当前行
  • 当前行的start_date落在该行的start_date与end_date区间内

SQL实现方案 #

可以用关联子查询实现，逻辑直观易理解：

SELECT 
    t1.*,
    (SELECT t2.value
     FROM your_table t2
     WHERE t2.user = t1.user
       AND t2.priority > t1.priority
       AND t1.start_date BETWEEN t2.start_date AND t2.end_date
     LIMIT 1) AS output
FROM your_table t1
ORDER BY t1.user, t1.start_date, t1.priority;

逻辑解释 #

1. 按用户分组匹配：t2.user = t1.user确保只在同一用户的行中查找目标值
2. 优先级筛选：t2.priority > t1.priority锁定优先级更高的行
3. 时间区间校验：t1.start_date BETWEEN t2.start_date AND t2.end_date确保当前行的开始时间处于目标行的时间范围内
4. LIMIT 1保证只取第一个符合条件的value（从结果来看，每个用户同一时间区间内仅有一个高优先级行，直接取即可）

如果存在多个符合条件的高优先级行，可根据需求调整，比如要取最大的value，可将LIMIT 1替换为ORDER BY t2.value DESC LIMIT 1。

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