sql - 如何根据最近的日期将值归因于行

Question

我正在尝试根据最近的 run_date 将“收入”的值归因于一行，并及时向前看。我使用日期范围作为连接条件，以仅在 10 天的“先行”期间内使用以下内容连接行......

SELECT 
* 
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)

最右边的表格是我想要实现的:

有人对如何获得预期结果有任何建议吗？ (图片最右边的表格)

我有一个 SQL fiddle here如果有人有兴趣帮忙。

Answer 1

t1 需要与自身进行比较，以便找到 t2 所属的 t1 行。这样做你需要加入 t1 两次。您可以在 OUTER JOIN 的条件中放置一个子选择来测试是否存在更合适的 t1，如下所示:

SELECT 
t1.id, c_id, run_date, coalesce(revenue,0) revenue
FROM t1 
  LEFT OUTER JOIN t2 ON t1.id = t2.id 
  AND t2.date BETWEEN t1.run_date AND DATE_ADD(t1.run_date, INTERVAL 10 DAY)
  AND NOT EXISTS (
    SELECT 'X'
    FROM t1 as later
    WHERE t1.id = t2.id
    AND later.run_date < t2.date
    AND t1.run_date < later.run_date
)
ORDER BY run_date