如何关联日历表与企业历史变更数据，避免记录重复？

问题背景 #

现有两张表：

• companies_with_owners：存储企业及其所有者的历史变更记录，同一Company_ID对应多条版本记录
• date_dim：日期维度表

需求：

• 若企业Archived=FALSE，将其记录从Created_at到Current_date按日展开
• 若企业Archived=TRUE，则从Created_at到最新Updated_at按日展开

当前使用Inner Join的查询会导致企业ID记录重复，希望通过合理的关联方式直接实现需求，避免后续去重操作。

表结构 #

companies_with_owners表 #

date_dim表 #

当前SQL语句 #

with exploded_dates AS (
    SELECT 
        c.company_ID,
        d.date,
        c.owner,
        c.created_at,
        c.archived,
        c.updated_at,
        c.current_date
    FROM
        companies_with_owners c
    INNER JOIN
        date_dim d
    WHERE
        (
            c.archived = FALSE
            AND (d.Date BETWEEN date(c.created_at) AND date(c.current_date))
        )
        OR (
            c.archived = TRUE
            AND (d.Date BETWEEN date(c.created_at) AND date(c.updated_at))
        )
)

SELECT * from exploded_dates

企业123的预期结果 #

解决方案 #

核心思路是先为每个企业版本记录计算唯一不重叠的有效时间区间，再与日期维度表关联，从根源避免重复记录。

WITH company_version_ranges AS (
    SELECT
        c.*,
        -- 获取下一个版本的更新日期（按更新时间降序）
        LEAD(date(c.Updated_At)) OVER (PARTITION BY c.Company_ID ORDER BY c.Updated_At DESC) AS next_version_updated_at,
        -- 计算当前版本的有效结束日期
        CASE
            -- 最新版本：根据归档状态确定截止日期
            WHEN LEAD(date(c.Updated_At)) OVER (PARTITION BY c.Company_ID ORDER BY c.Updated_At DESC) IS NULL THEN
                CASE WHEN c.Archived = FALSE THEN date(c.Current_date) ELSE date(c.Updated_At) END
            -- 非最新版本：截止到下一个版本更新日期的前一天
            ELSE DATE_SUB(LEAD(date(c.Updated_At)) OVER (PARTITION BY c.Company_ID ORDER BY c.Updated_At DESC), INTERVAL 1 DAY)
        END AS valid_end_date,
        -- 有效起始日期为当前版本的创建日期
        date(c.Created_at) AS valid_start_date
    FROM companies_with_owners c
),
exploded_dates AS (
    SELECT
        cv.Company_ID,
        cv.Priority,
        cv.Owner,
        cv.Created_at,
        cv.Current_date,
        cv.Archived,
        cv.Updated_At,
        d.DATE
    FROM company_version_ranges cv
    INNER JOIN date_dim d
    ON d.DATE BETWEEN cv.valid_start_date AND cv.valid_end_date
)
SELECT * FROM exploded_dates
ORDER BY Company_ID, DATE DESC;

关键说明 #

1. 窗口函数LEAD：按企业ID分组、更新日期降序排序，获取每个版本的下一个更新时间，用于划分当前版本的有效边界
2. 有效区间计算：
   • 最新版本：未归档则截止到Current_date，已归档则截止到自身Updated_at
   • 非最新版本：截止到下一个版本更新日期的前一天，确保区间不重叠
3. 关联逻辑：通过唯一有效区间与日期表关联，每个日期仅匹配一个企业版本，彻底避免重复

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