mysql - 使用 "effective date"从表构建每日 View

Question

我有一个使用“开始日期”或生效日期的表格。表中的值从开始日期开始生效，直到它被同一表中具有较晚开始日期的另一个条目覆盖。

我的架构和示例数据:

CREATE TABLE VALUE_DATA (
    `start_date` DATE,
    `value` FLOAT
);

INSERT INTO VALUE_DATA (start_date, value) VALUES
('2015-01-01', 10),
('2015-01-03', 20),
('2015-01-08', 30),
('2015-01-09', 15);

产生所需结果的查询:

SELECT date, value
FROM(
    SELECT date, MAX(start_date) as max_start
    FROM (
        select curdate() - INTERVAL (ones.digit + (10 * tens.digit) + (100 * hundreds.digit)) DAY as date
        from (select 0 as digit union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as ones
        cross join (select 0 as digit union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as tens
        cross join (select 0 as digit union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as hundreds
    ) DATE_TABLE
    LEFT JOIN VALUE_DATA ON (DATE_TABLE.date >= VALUE_DATA.start_date)
    WHERE DATE_TABLE.date between '2015-01-01' and '2015-01-10'
    GROUP BY date
) START_DATES LEFT JOIN VALUE_DATA ON (START_DATES.max_start = VALUE_DATA.start_date);

我创建了一个 SQL Fiddle模拟问题。

虽然 SQL Fiddle 可以工作(给出正确的结果)，但我不认为这是执行此操作的最佳方法。我不得不使用的查询有点复杂。我最终想为此表创建一个 View ，其中包含每天的正确值，无论它是否在开始日期(如 Fiddle 产生的输出)，以便更容易加入此表。显然，我想确保这个 View 尽可能快。所以我的问题是，如何改进(优化)此查询以用于此类 View ？

Answer 1

您需要非常小心地使用这种类型的 View 。编写一个擅长给出每条记录有效的所有单独日期但在询问哪个记录在一个特定日期有效时速度很慢的 View 将很容易。

(因为回答第二个问题需要先回答每个日期的第一个问题，然后丢弃失败。)

在获取日期并返回在该日期有效的行时，以下是合理的。

CREATE VIEW DAILY_VALUE_DATA AS (
    SELECT
        DATE_TABLE.date,
        VALUE_TABLE.value
    FROM
        DATE_TABLE
    LEFT JOIN
        VALUE_DATA
            ON  VALUE_DATA.start_date = (SELECT MAX(lookup.start_date)
                                           FROM VALUE_DATA lookup
                                          WHERE lookup.start_date <= DATE_TABLE.date
                                        )
);

SELECT * FROM DAILY_VALUE_DATA WHERE date = '2015-08-11'

注意:这里假设 DateTable 是一个真正的持久物化表，而不是您使用的内联 View ，使用它会大大降低性能。

它还假定 VALUE_DATA 由 start_date 编制索引。

编辑:

我还发现您的值表可能会有其他列。假设它是每人的值(value)。也许是他们在任何给定日期的地址。

要扩展上面的查询，您还需要加入 person 表...

CREATE VIEW DAILY_VALUE_DATA AS (
    SELECT
        PERSON.id   AS person_id,
        DATE_TABLE.date,
        VALUE_TABLE.value
    FROM
        PERSON
    INNER JOIN
        DATE_TABLE
            ON  DATE_TABLE.date >=          PERSON.date_of_birth
            AND DATE_TABLE.date <  COALESCE(PERSON.date_of_death, CURDATE() + 1)
    LEFT JOIN
        VALUE_DATA
            ON  VALUE_DATA.start_date = (SELECT MAX(lookup.start_date)
                                           FROM VALUE_DATA lookup
                                          WHERE lookup.start_date <= DATE_TABLE.date
                                            AND lookup.person_id   = PERSON.id
                                        )
);

SELECT * FROM DAILY_VALUE_DATA WHERE person_id = 1 AND date = '2015-08-11'

编辑:

LEFT JOIN 的另一种替代方法是将相关的子查询嵌入到 SELECT block 中。当您只有一个值要从目标表中提取时，这很有效，但如果您需要从目标表中提取许多值，则效果不佳...

CREATE VIEW DAILY_VALUE_DATA AS (
    SELECT
        PERSON.id   AS person_id,
        DATE_TABLE.date,
        (SELECT VALUE_DATA.value
           FROM VALUE_DATA
          WHERE VALUE_DATA.start_date <= DATE_TABLE.date
            AND VALUE_DATA.person_id   = PERSON.id
       ORDER BY VALUE_DATA.start_date DESC
          LIMIT 1
        )   AS value
    FROM
        PERSON
    INNER JOIN
        DATE_TABLE
            ON  DATE_TABLE.date >=          PERSON.date_of_birth
            AND DATE_TABLE.date <  COALESCE(PERSON.date_of_death, CURDATE() + 1)
);

SELECT * FROM DAILY_VALUE_DATA WHERE person_id = 1 AND date = '2015-08-11'