php - MySQL 查找时间片中的日期间隙

Question

我有一个 VariableValues 表，其中的值与 startdate、enddate 和 TariffVariableName 存储在一起。

另一个表变量也有 start 、 enddate 和 name

EndDates 是互斥的:因此 2016 年 2 月 月份的 timeslice 将为 2016-02-01 - 2016-03-01

我想执行两项检查(我更喜欢两次查询)

一:VariableValues 内不能有间隙

示例:

    VariableValue.Name = name1
    startdate: 2016-02-01 - enddate: 2016-03-01
    startdate: 2016-04-01 - enddate: 2016-12-01

==> Should give an error as month April is not covered.

二:Variable的整个周期应该被VariableValues覆盖

    Variable.startdate: 2016-01-01 - enddate 2017-01-01
    VariableValue.startdate 2016-01-01 - enddate 2016-06-01
    VariableValue.startdate 2016-06-01 - enddate 2016-11-01

==> Should give an error as the month November and December is not covered by Values

有什么帮助吗？

Answer 1

间隙检测器:

CREATE TABLE VariableValues (TariffVariableName CHAR(5), StartDate DATE, EndDate DATE);
INSERT INTO VariableValues (TariffVariableName, StartDate, EndDate)
VALUES ('name1', STR_TO_DATE('20160201', '%Y%m%d'), STR_TO_DATE('20160301', '%Y%m%d'));
INSERT INTO VariableValues (TariffVariableName, StartDate, EndDate)
VALUES ('name1', STR_TO_DATE('20160401', '%Y%m%d'), STR_TO_DATE('20161201', '%Y%m%d'));

SELECT *
FROM (
    SELECT
        next.TariffVariableName,
        (
            SELECT MAX(prev.EndDate)
            FROM VariableValues prev
            WHERE prev.TariffVariableName = next.TariffVariableName
              AND prev.EndDate < next.StartDate
        ) AS StartDate,
        next.StartDate As EndDate
    FROM VariableValues next
    WHERE NOT EXISTS (
        SELECT '*'
        FROM VariableValues overlap
        WHERE overlap.TariffVariableName = next.TariffVariableName
          AND overlap.EndDate >= next.StartDate
          AND overlap.StartDate < next.StartDate
    )
) gap
WHERE gap.StartDate IS NOT NULL
ORDER BY gap.TariffVariableName, gap.StartDate;

可以使用相同的方法来检查间隔覆盖:对于 Variable 中的每个间隔，从 VariableValues 中获取所有重叠间隔，添加两个人工间隔:Variable.StartDate - Variable.StartDate 和 Variable.EndDate - Variable.EndDate，并运行上面的间隙检测器。