如何在SQL中基于最近的未来日期为行分配对应revenue值

问题本质 #

你当前的JOIN逻辑会返回所有满足10天窗口的匹配行，而需求是每个t2行仅关联「距离其date字段最近、且小于等于date的run_date」对应的t1记录，最终实现1对1匹配分配revenue值。

方案1：支持窗口函数的数据库（MySQL 8.0+、PostgreSQL等通用写法） #

通过ROW_NUMBER()窗口函数给每个t2行的匹配结果按run_date倒序排序，取排名第一的最近run_date对应行即可：

SELECT * FROM (
  SELECT 
    t2.*,
    t1.revenue,
    t1.run_date,
    -- 同个t2行的匹配结果按run_date从新到旧排序
    ROW_NUMBER() OVER (PARTITION BY t2.id, t2.date ORDER BY t1.run_date DESC) AS rn
  FROM t1 
  INNER JOIN t2 
    ON t1.id = t2.id 
    AND t2.date BETWEEN t1.run_date AND DATE_ADD(t1.run_date, INTERVAL 10 DAY)
) t
WHERE rn = 1; -- 仅保留最近的run_date对应的匹配行

方案2：MySQL 5.x 兼容写法 #

先通过分组聚合找到每个t2行对应的最近run_date，再二次关联t1获取对应的revenue字段：

SELECT 
  t2.*,
  t1_final.revenue,
  t1_final.run_date
FROM t2
-- 第一步：找到每个t2行对应的符合窗口要求的最近run_date
INNER JOIN (
  SELECT 
    t2.id,
    t2.date,
    MAX(t1.run_date) AS latest_run_date
  FROM t1
  INNER JOIN t2 
    ON t1.id = t2.id 
    AND t2.date BETWEEN t1.run_date AND DATE_ADD(t1.run_date, INTERVAL 10 DAY)
  GROUP BY t2.id, t2.date
) t_match ON t2.id = t_match.id AND t2.date = t_match.date
-- 第二步：关联t1拿到对应run_date的revenue
INNER JOIN t1 t1_final 
  ON t_match.id = t1_final.id 
  AND t_match.latest_run_date = t1_final.run_date;

注意事项 #

• 如果存在同一个id和run_date下t1有重复记录的情况，可根据业务需求补充去重逻辑
• 10天窗口的边界可以根据实际需求调整DATE_ADD的间隔参数

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