如何将CRUD状态变更日志表转换为含日期与实体列的表？

原始日志表说明 #

现有一张跟踪parent/child实体状态变更的日志表，结构及示例数据如下：

状态含义：

• new：实体创建
• existing：实体持续存在（日志行可选，无定期生成保证）
• deleted：实体失效（不再活跃）

其中new和deleted状态行是每个parent/child组合必有的，existing行可省略。

目标表要求 #

需要将上述日志转换为包含每日活跃parent/child实体的记录表，结构及示例数据如下：

核心规则 #

• 同一parent/child组合允许多次创建、删除，需处理全生命周期的活跃日期
• 若某组合只有new事件无deleted事件：生成从new日期到当前日期的每日记录
• 若某组合只有deleted事件：生成指定时间范围（如最近30天）内，deleted日期及之前的每日记录

通用SQL解决方案 #

步骤1：提取有效时间区间 #

首先为每个parent/child组合提取所有活跃区间：

• 对于有new和deleted的组合，每个new对应后续最近的deleted，形成一个活跃区间
• 只有new无deleted的组合，活跃区间是new日期到当前日期
• 只有deleted的组合，活跃区间是时间范围起始日期到deleted日期

WITH event_pairs AS (
    SELECT
        parent,
        child,
        -- 提取new事件的日期
        DATE(timestamp) AS start_date,
        -- 找到当前new之后最近的deleted日期，若无则用当前日期
        COALESCE(
            LEAD(CASE WHEN status = 'deleted' THEN DATE(timestamp) END) OVER (
                PARTITION BY parent, child ORDER BY timestamp
            ),
            CURRENT_DATE()
        ) AS end_date
    FROM log_table
    WHERE status = 'new'

    UNION ALL

    -- 处理只有deleted事件的情况（假设时间范围是最近30天）
    SELECT
        parent,
        child,
        DATE_SUB(CURRENT_DATE(), 29) AS start_date, -- 最近30天起始
        DATE(timestamp) AS end_date
    FROM log_table
    WHERE status = 'deleted'
    AND NOT EXISTS (
        SELECT 1 FROM log_table t2
        WHERE t2.parent = log_table.parent
        AND t2.child = log_table.child
        AND t2.status = 'new'
    )
),
-- 步骤2：生成日期序列（不同数据库语法略有差异，此处为通用写法）
date_series AS (
    SELECT DATE_ADD(start_date, n) AS active_date
    FROM event_pairs
    -- 生成0-9的数字序列，如需更长范围可继续扩展或用递归语法
    JOIN (
        SELECT 0 AS n 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
    ) numbers
    WHERE DATE_ADD(start_date, n) <= end_date
)
-- 步骤3：最终输出
SELECT
    ds.active_date AS date,
    ep.parent,
    ep.child
FROM event_pairs ep
JOIN date_series ds ON ds.active_date BETWEEN ep.start_date AND ep.end_date
ORDER BY ep.parent, ep.child, ds.active_date;

Spark SQL适配优化 #

Spark SQL支持递归CTE生成日期序列，可更灵活处理长周期场景，替换上述date_series部分：

WITH event_pairs AS (
    -- 同通用SQL的event_pairs逻辑
    ...
),
date_series AS (
    SELECT start_date AS active_date, end_date, parent, child
    FROM event_pairs
    UNION ALL
    SELECT DATE_ADD(active_date, 1), end_date, parent, child
    FROM date_series
    WHERE active_date < end_date
)
SELECT
    active_date AS date,
    parent,
    child
FROM date_series
ORDER BY parent, child, active_date;

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