Oracle中实现类Excel OFFSET功能：基于日期匹配获取指定VAL值

需求回顾 #

给定数据集，需按以下逻辑生成结果：

• 匹配prd1列的日期在prd2列中的记录
• 获取匹配记录的dense_rnk值并减1，得到目标dense_rnk
• 取出目标dense_rnk对应的VAL列值，结果由prd1的日期驱动

可行性说明 #

Oracle没有与Excel OFFSET完全等价的函数，但可以通过自连接或窗口函数+子查询实现需求，完全可行。

解决方案 #

假设你的表名为your_table，字段包含prd1(DATE)、prd2(DATE)、dense_rnk(NUMBER)、VAL(VARCHAR2/NUMBER)。

方案1：自连接（通用，适配dense_rnk非连续场景）

这种方式不依赖dense_rnk的连续性，适用于所有情况：

SELECT 
    t1.prd1,
    t2.VAL AS required_val
FROM 
    your_table t1
-- 匹配prd1在prd2中的记录，拿到对应的dense_rnk
LEFT JOIN 
    your_table t_match ON t1.prd1 = t_match.prd2
-- 通过减1后的dense_rnk关联到目标VAL
LEFT JOIN 
    your_table t2 ON t2.dense_rnk = t_match.dense_rnk - 1
-- 可选：仅保留找到匹配的prd1记录，若需保留所有prd1行可删除此条件
WHERE 
    t_match.prd2 IS NOT NULL;

方案2：窗口函数（高效，适用于dense_rnk连续场景）

如果dense_rnk是连续递增的，用LAG窗口函数可以提升查询效率：

WITH matched_records AS (
    -- 筛选prd2中存在于prd1的记录，计算目标dense_rnk
    SELECT 
        prd1,
        dense_rnk - 1 AS target_rnk
    FROM 
        your_table
    WHERE 
        prd2 IN (SELECT DISTINCT prd1 FROM your_table)
),
val_ref AS (
    -- 建立dense_rnk与VAL的映射，同时用LAG预取前一个rnk的VAL
    SELECT 
        dense_rnk,
        VAL,
        -- 若需按分组维度处理，添加PARTITION BY <分组字段>
        LAG(VAL) OVER (ORDER BY dense_rnk) AS prev_val
    FROM 
        your_table
)
SELECT 
    mr.prd1,
    -- 直接匹配目标rnk对应的VAL，或用prev_val（当target_rnk = 当前rnk-1时等价）
    vr.VAL AS required_val
FROM 
    matched_records mr
JOIN 
    val_ref vr ON mr.target_rnk = vr.dense_rnk;

补充说明 #

• 若数据存在分组维度（比如按某个ID分组处理），需在自连接或窗口函数中添加PARTITION BY子句，确保逻辑在分组内生效
• 窗口函数的核心是建立dense_rnk的顺序映射，之前觉得复杂可能是未明确分组或排序逻辑

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