mysql - 将为日期范围(从、到)指定的值应用于包含每个日期的单行的表中的值

Question

首先，抱歉标题，但我的英语太差，无法解释我的问题的含义。 :)

假设我们有两个表。第一个表 tbl_percents 包含日期范围内的百分比值历史记录。如果日期字段to等于0000-00-00，则表示它是未完成的范围。

table: tbl_percents
from date
to date
percent int

example content:
2001-01-01 | 2015-01-21 | 10%
2015-01-21 | 0000-00-00 | 20%

第二个表是tbl_revenue，其中包含特定日期的收入值。

table: tbl_revenue
date date
revenue bigint

example content:
2014-01-10 | 10
2015-01-22 | 10

现在我们要将表 tbl_percents 中指定的百分比应用于收入。结果我们想要得到这样的结果:

2014-01-10 | 1 #because from 2001-01-01 to 2015-01-21 percent = 10%
2015-01-22 | 2 #because from 2015-01-22 till now percent = 20%

是否可以通过单个 SQL 查询获得此结果？

Answer 1

是的。您想要使用 BETWEEN 条件进行联接。我必须提醒您，这些类型的查询非常昂贵，速度非常快，因此您不想在巨大的数据集上执行此操作。话虽这么说，您可以使用如下内容来连接表:

SELECT b.revenue, a.percent
FROM tbl_percents AS a
INNER JOIN tbl_revenue AS b
ON b.date BETWEEN a.from_date AND 
     CASE WHEN a.to_date = DATE("0000-00-00") THEN DATE("2100-01-01")
          ELSE a.to_date END

基本上我正在做的是将 to_date 设置为非常大的 future 日期(即 2100 年 1 月 1 日)。如果 to_date 是 0000-00-00，那么我会在未来的日期中应用非常大的值。否则我只使用 to_date。使用它，我按收入日期连接到百分比表，其中收入日期位于百分比开始日期和修改后的百分比结束日期之间。

同样，这在计算上对于庞大的数据集来说并不是一个好主意......但对于一般目的，它应该工作得很好。如果您开始在速度/性能方面遇到问题，我建议尝试使用 R 或 Python 等脚本语言来应用类似的逻辑。

祝你好运!