如何基于当前日期为日期维度表的日期字段计算对应偏移量

方案1：直接使用日期差值计算（推荐） #

你要实现的偏移本质是指定基准日和各日期的天数差，无需窗口函数即可实现，逻辑更简洁性能更好：

-- 以下为SQL Server语法，其他数据库仅需调整日期函数参数顺序即可
SELECT 
    actualDate,
    DATEDIFF(day, '2021-09-24', CAST(actualDate AS DATE)) AS dateOffset
FROM (
    VALUES ('2021-09-20')
    ,      ('2021-09-21')
    ,      ('2021-09-22')
    ,      ('2021-09-23')
    ,      ('2021-09-24')
    ,      ('2021-09-25')
    ,      ('2021-09-26')
    ,      ('2021-09-27')
    ,      ('2021-09-28')
) as t(actualDate)

如果需要基准日动态生效（每日运行时自动取昨日为0），把固定基准日'2021-09-24'替换为CAST(DATEADD(day,-1,GETDATE()) AS DATE)即可。

方案2：窗口函数实现 #

如果你明确需要用窗口函数实现，可以先给日期排序取行号，再用行号减去基准日对应的行号得到偏移值：

WITH date_rn AS (
    SELECT 
        actualDate,
        ROW_NUMBER() OVER(ORDER BY CAST(actualDate AS DATE) ASC) AS rn
    FROM (
        VALUES ('2021-09-20')
        ,      ('2021-09-21')
        ,      ('2021-09-22')
        ,      ('2021-09-23')
        ,      ('2021-09-24')
        ,      ('2021-09-25')
        ,      ('2021-09-26')
        ,      ('2021-09-27')
        ,      ('2021-09-28')
    ) as t(actualDate)
)
SELECT 
    actualDate,
    rn - (SELECT rn FROM date_rn WHERE actualDate = '2021-09-24') AS dateOffset
FROM date_rn
ORDER BY actualDate

该方案更适用于日期维度表不连续、需要按现有日期序列计算偏移的场景。

内容的提问来源于stack exchange，提问作者Umar.H