Teradata高性能SQL关联：获取销售记录对应最新价格

需求明确 #

从销售表T1中提取指定日期范围内的所有记录，为每条记录匹配同商品(Itm)、同地点(Loc)、价格变动日期≤销售日期的最新价格（取自价格变动表T2）。

最优方案：Teradata原生ASOF JOIN（性能最优） #

Teradata的ASOF JOIN是专为时间序列匹配场景优化的原生算子，无需生成笛卡尔积或逐行子查询，百万级数据下性能远优于常规关联方式。通过LAST选项可直接获取符合时间条件的最新价格记录：

SELECT 
    t1.Itm,
    t1.Loc,
    t1.Date,
    t1.Sales,
    t2.Price
FROM 
    T1 AS t1
ASOF JOIN LAST T2 AS t2
ON 
    t1.Itm = t2.Itm
    AND t1.Loc = t2.Loc
    AND t1.Date >= t2.Date  -- 匹配销售日期之前的价格变动
WHERE 
    t1.Date BETWEEN '2024-01-01' AND '2024-01-03'  -- 替换为实际筛选日期范围

逻辑说明 #

1. ASOF JOIN LAST会先按关联键（Itm, Loc）对两张表排序
2. 为每条T1记录快速定位到满足t1.Date >= t2.Date的最新T2记录
3. Teradata优化器会对该逻辑做全流程性能优化，避免低效的全表扫描或笛卡尔积

备选方案：预预处理T2的窗口函数关联 #

若无法使用ASOF JOIN，可通过预处理T2生成价格生效区间，再进行关联，性能优于子查询方式：

WITH T2_Price_Ranges AS (
    SELECT 
        Itm,
        Loc,
        Date AS Price_Effective_Date,
        Price,
        -- 获取下一次价格变动日期，确定当前价格的生效结束时间
        LEAD(Date) OVER (PARTITION BY Itm, Loc ORDER BY Date) AS Next_Effective_Date
    FROM T2
)
SELECT 
    t1.Itm,
    t1.Loc,
    t1.Date,
    t1.Sales,
    t2.Price
FROM 
    T1 AS t1
JOIN 
    T2_Price_Ranges AS t2
ON 
    t1.Itm = t2.Itm
    AND t1.Loc = t2.Loc
    AND t1.Date >= t2.Price_Effective_Date
    AND (t1.Date < t2.Next_Effective_Date OR t2.Next_Effective_Date IS NULL)
WHERE 
    t1.Date BETWEEN '2024-01-01' AND '2024-01-03'

逻辑说明 #

1. 预处理T2时，用LEAD窗口函数获取每个价格变动的下一次生效日期，确定当前价格的生效区间
2. 关联时直接匹配销售日期落在价格生效区间的记录，避免逐行判断

原尝试方案的问题分析 #

• 原CTE中PARTITION BY Itm, Loc后取RN=1，是取每个商品地点的全局最新价格，而非对应销售日期之前的最新价格，逻辑不符合需求
• 子查询关联方式会导致逐行执行，百万级数据下性能极差，Teradata不推荐此类写法

内容的提问来源于stack exchange，提问作者a.s.1