含可选日期值的Catalog版本与文章日期范围重叠检测技术问询

这个场景确实比普通的日期重叠检测多了一层复杂度，核心是先把每个文章的实际生效日期范围确定下来，再套用经典的重叠判断逻辑就行。我给你拆解一下思路，再给出具体的SQL示例：

第一步：明确每个文章的实际生效/失效日期 #

根据你的规则，我们需要用COALESCE函数来继承版本的日期：

• 文章的实际生效日期：COALESCE(ca.valid_from, cv.valid_from, '1900-01-01') —— 如果文章和版本都没设置，就用一个极早的日期代表“从一开始就生效”
• 文章的实际失效日期：COALESCE(ca.valid_to, cv.valid_to, '9999-12-31') —— 如果都没设置，用极晚的日期代表“永久有效”

第二步：经典日期范围重叠判断 #

两个日期范围[start1, end1]和[start2, end2]重叠的核心条件是：

start1 < end2 AND start2 < end1

这里要注意用小于而不是小于等于，因为如果一个的结束日期刚好等于另一个的开始日期，通常不算重叠（比如A到2024-05-01，B从2024-05-01开始，这两个是衔接而非重叠），如果你的业务认为这种情况算重叠，就改成<=。

第三步：完整SQL查询示例 #

假设你的表结构和字段名如下：

• catalogs：id（主键）
• catalog_versions：id, catalog_id, valid_from, valid_to
• catalog_articles：id, catalog_version_id, valid_from, valid_to, article_code（假设用来区分不同文章）

下面的查询会找出同一个Catalog下，日期范围重叠的文章对：

WITH article_effective_dates AS (
    SELECT
        ca.id AS article_id,
        ca.article_code,
        c.id AS catalog_id,
        cv.id AS catalog_version_id, -- 保留版本ID方便后续调整
        -- 计算实际生效日期
        COALESCE(ca.valid_from, cv.valid_from, '1900-01-01') AS effective_from,
        -- 计算实际失效日期
        COALESCE(ca.valid_to, cv.valid_to, '9999-12-31') AS effective_to
    FROM catalog_articles ca
    JOIN catalog_versions cv ON ca.catalog_version_id = cv.id
    JOIN catalogs c ON cv.catalog_id = c.id
)
SELECT
    a1.article_id AS article1_id,
    a1.article_code AS article1_code,
    a2.article_id AS article2_id,
    a2.article_code AS article2_code,
    a1.catalog_id,
    a1.catalog_version_id AS article1_version_id,
    a2.catalog_version_id AS article2_version_id,
    a1.effective_from AS article1_from,
    a1.effective_to AS article1_to,
    a2.effective_from AS article2_from,
    a2.effective_to AS article2_to
FROM article_effective_dates a1
JOIN article_effective_dates a2
    ON a1.catalog_id = a2.catalog_id
    AND a1.article_id < a2.article_id -- 避免重复检测（比如A-B和B-A视为同一对）
    AND a1.effective_from < a2.effective_to
    AND a2.effective_from < a1.effective_to
ORDER BY a1.catalog_id, a1.effective_from;

一些可调整的细节 #

• 如果只需要检测同一个版本下的文章重叠，把JOIN条件里的a1.catalog_id = a2.catalog_id改成a1.catalog_version_id = a2.catalog_version_id即可
• 如果你的数据库支持DATE类型的极值（比如PostgreSQL的'-infinity'和'infinity'），可以替换掉'1900-01-01'和'9999-12-31'，逻辑会更严谨
• 如果要快速排查是否存在重叠，只需要把SELECT部分改成COUNT(*)或者用EXISTS子句即可

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