基于日期列计算利率差值：解决LAG函数缺失月份失效问题

我们有一张包含INTEREST_DATE（格式为Mon-YY）和INTEREST_RATE列的Interest表，需要衍生出interest_year、interest_month、interest_quarter三个计算列。目前用LAG函数实现interest_year的计算，但该方案仅在月份连续有序时有效，一旦出现月份缺失或数据无序的情况就会失效，需要寻找可靠的替代方案。

数据示例 #

INTEREST_DATE | INTEREST_RATE
--------------|--------------
Dec-21        | 7.5
Jan-22        | 7.6
Feb-22        | 7.6
Mar-22        | 7.8
Apr-22        | 7.9
May-22        | 8.2
Jun-22        | 8.5
July-22       | 8.5
Aug-22        | 8.6
Sep-22        | 8.7
Oct-22        | 8.9
Nov-22        | 9.1
Dec-22        | 9.3
Jan-23        | 9.5

现有失效的SQL语句 #

select
  interest_date,
  interest_rate,
  case
    when LAG(interest_rate,12) over (order by to_date(interest_date,'Mon-YY')) is null
    then 0
    else interest_rate - LAG(interest_rate,12) over (order by to_date(interest_date,'Mon-YY')) 
  end as interest_year
from Interest

预期输出示例 #

INTEREST_DATE | INTEREST_RATE | INTEREST_YEAR
--------------|---------------|--------------
Dec-21        | 7.5           | 0
Jan-22        | 7.6           | 0
Feb-22        | 7.6           | 0
Mar-22        | 7.8           | 0
Apr-22        | 7.9           | 0
May-22        | 8.2           | 0
Jun-22        | 8.5           | 0
July-22       | 8.5           | 0
Aug-22        | 8.6           | 0
Sep-22        | 8.7           | 0
Oct-22        | 8.9           | 0
Nov-22        | 9.1           | 0
Dec-22        | 9.3           | 1.8
Jan-23        | 9.5           | 1.9

注：interest_year的计算逻辑为当前月份利率减去去年同月利率，例如Dec-22的interest_year是9.3-7.5=1.8。

替代解决方案 #

核心思路是通过日期逻辑匹配目标周期的记录，而非依赖LAG函数的行位置偏移，彻底摆脱数据顺序和月份连续性的限制。

方案1：自连接匹配目标周期日期 #

通过将字符串日期转换为标准日期类型，计算出目标周期（去年同月、上月、上季）的日期，再通过自连接获取对应利率：

select
  curr.interest_date,
  curr.interest_rate,
  -- 计算interest_year：当前利率减去年同月利率
  coalesce(curr.interest_rate - prev_year.interest_rate, 0) as interest_year,
  -- 计算interest_month：当前利率减上月利率（环比）
  coalesce(curr.interest_rate - prev_month.interest_rate, 0) as interest_month,
  -- 计算interest_quarter：当前利率减上季同月利率（季同比）
  coalesce(curr.interest_rate - prev_quarter.interest_rate, 0) as interest_quarter
from Interest curr
-- 匹配去年同月
left join Interest prev_year
  on to_date(curr.interest_date, 'Mon-YY') = add_months(to_date(prev_year.interest_date, 'Mon-YY'), 12)
-- 匹配上月
left join Interest prev_month
  on to_date(curr.interest_date, 'Mon-YY') = add_months(to_date(prev_month.interest_date, 'Mon-YY'), 1)
-- 匹配上季同月
left join Interest prev_quarter
  on to_date(curr.interest_date, 'Mon-YY') = add_months(to_date(prev_quarter.interest_date, 'Mon-YY'), 3)
order by to_date(curr.interest_date, 'Mon-YY')

说明：

• 不同数据库的日期函数有差异：PostgreSQL用date_val + interval '12 months'代替add_months，SQL Server用DATEADD(month,12, date_val)，需根据实际数据库调整。
• coalesce函数用于处理无匹配记录的情况，返回0，与原逻辑保持一致。
• 不受数据顺序影响，即使数据无序也能通过日期匹配找到目标记录；即使存在月份缺失，只要目标周期有数据就会正确计算，无数据则返回0。

方案2：窗口函数+日期分组 #

利用窗口函数按日期维度（月份、年份、季度）分组，直接匹配目标周期的数据：

select
  interest_date,
  interest_rate,
  -- 计算interest_year：按月份分组，取去年同月利率
  coalesce(interest_rate - max(case when extract(year from date_val) = extract(year from curr.date_val) -1 then interest_rate end) over (partition by extract(month from date_val)), 0) as interest_year,
  -- 计算interest_month：按年份分组，取上月利率
  coalesce(interest_rate - max(case when extract(month from date_val) = extract(month from curr.date_val) -1 then interest_rate end) over (partition by extract(year from date_val)), 0) as interest_month,
  -- 计算interest_quarter：按年份分组，取上季同月利率
  coalesce(interest_rate - max(case when extract(quarter from date_val) = extract(quarter from curr.date_val) -1 then interest_rate end) over (partition by extract(year from date_val)), 0) as interest_quarter
from (
  select
    interest_date,
    interest_rate,
    to_date(interest_date, 'Mon-YY') as date_val
  from Interest
) curr
order by date_val

说明：

• 先将INTEREST_DATE转换为标准日期date_val，方便日期维度提取。
• 通过partition by按月份/年份分组，在组内筛选目标周期的利率值，实现跨周期匹配。
• 同样不受数据顺序和月份缺失影响，逻辑清晰且性能稳定。

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