Teradata实现含周末节假日的最近3个工作日交易回溯逻辑

前置依赖确认 #

• 日历表calender_days需覆盖全量日期，包含两个核心字段：day_date（日期类型，存储自然日值）、is_workday（数值/布尔类型，1代表工作日<含调休补班的周末>，0代表非工作日<含普通周末、法定节假日>）
• 交易表tran_details的交易日期字段TranDate为日期类型，和calender_days.day_date格式对齐，避免关联时出现类型隐式转换

核心实现逻辑（无需硬编码回溯天数，全场景适配） #

整个逻辑不需要提前估算要往前翻多少自然日，完全靠日历表的工作日标记自动圈定范围：

• 第一步：确定每个账号的基准日期
  先拿到每个待统计Account_No对应的基准日，也就是例子里的交易日期2022/06/14。如果是取账号最新交易对应的近3个工作日，就取每个账号最大的TranDate作为基准日；如果是固定统计日的维度，直接把统计日作为所有账号的基准日即可。
• 第二步：工作日倒序打标，确定日期区间
  关联日历表，取出所有小于等于基准日的工作日记录，按日期从近到远倒序排序后给每条记录编序号：离基准日最近的工作日序号为1，往前依次递增。取序号≤3的所有日期，其中最小的日期就是查询范围的左边界，基准日就是右边界。

  对应你举的示例场景：基准日2022/06/14是周二（工作日，序号1），往前2022/06/13是法定节假日跳过，2022/06/12、2022/06/11是周末跳过，2022/06/10是工作日（序号2），2022/06/09是工作日（序号3），最终左边界就是2022/06/09，查询区间为2022/06/09~2022/06/14，和预期完全一致。

• 第三步：按圈定的区间拉取交易数据
  拿到每个账号对应的日期区间后，直接关联交易表筛选TranDate落在区间内的记录即可。

可直接复用的SQL参考（MySQL语法，其他数据库仅需调整日期函数即可） #

WITH account_base AS (
    -- 此处可替换为实际的基准日获取逻辑
    SELECT 
        Account_No,
        -- 示例取每个账号最新交易日期作为基准日，固定统计日可直接写常量比如'2022-06-14'
        MAX(TranDate) AS base_date
    FROM tran_details
    -- 指定账号范围可在此处加过滤条件，例如 WHERE Account_No IN ('abc','def')
    GROUP BY Account_No
),
workday_sorted AS (
    SELECT
        ab.Account_No,
        ab.base_date,
        cd.day_date,
        ROW_NUMBER() OVER (
            PARTITION BY ab.Account_No 
            ORDER BY cd.day_date DESC
        ) AS workday_rn
    FROM account_base ab
    INNER JOIN calender_days cd
        ON cd.day_date <= ab.base_date
        AND cd.is_workday = 1
        -- 性能优化：最大回溯30天，哪怕遇到春节/国庆超长连休也足够覆盖3个工作日，避免关联过多历史数据
        AND cd.day_date >= DATE_SUB(ab.base_date, INTERVAL 30 DAY)
)
SELECT t.*
FROM (
    SELECT
        Account_No,
        MIN(day_date) AS range_start,
        base_date AS range_end
    FROM workday_sorted
    WHERE workday_rn <= 3 -- 要取N个工作日就把3改成对应数值即可
    GROUP BY Account_No, base_date
) dr
INNER JOIN tran_details t
    ON t.Account_No = dr.Account_No
    AND t.TranDate BETWEEN dr.range_start AND dr.range_end;

落地注意事项 #

• 日历表必须同步维护调休规则：比如周末安排补班的日期要标记为is_workday=1，工作日安排休假的要标记为0，否则会出现统计偏差
• 30天回溯截断是性能优化项，可根据实际节假日安排调整，只要大于最长连休天数+3即可，不会影响统计准确性
• 逻辑支持灵活扩展：如果需要取最近5/10个工作日，仅需修改workday_rn <= N的数值，不需要调整其他逻辑

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