如何从存在重叠数据的SQL表中获取start_period对应的最新处理数据

看起来你需要处理一张按每月11日至17日7天周期每日插入且存在数据重叠的SQL表，核心目标是提取每个业务分组下对应最新start_period的记录。我来帮你梳理逻辑并给出优化后的解决方案：

问题回顾 #

表数据示例 #

预期输出 #

我们需要保留每个重复业务分组中start_period最新的记录，最终结果如下：

解决方案 #

你的核心需求可以通过窗口函数分组排序+过滤来实现，比原查询的逻辑更简洁高效：

基础版查询（直接满足核心需求） #

如果不需要原查询中的日期维度关联和偏移量计算，直接用下面的语句即可：

SELECT 
    start_period,
    ID,
    Management_unit,
    business_unit,
    activity,
    actual_start_date,
    actual_end_date
FROM (
    SELECT 
        *,
        -- 按业务维度分组，每组内按start_period降序排序，最新的标记为1
        ROW_NUMBER() OVER (
            PARTITION BY ID, Management_unit, business_unit, activity, actual_start_date
            ORDER BY start_period DESC
        ) AS record_rank
    FROM MYTABLE
) ranked_records
-- 只保留每组中最新的记录
WHERE record_rank = 1;

整合原查询逻辑的优化版 #

如果需要保留原查询中的日期截断、偏移量计算等业务逻辑，可以将窗口函数逻辑整合进去：

SELECT 
    start_period,
    agent_id AS ID,
    MANAGEMENTUNIT_NAME AS Management_unit,
    BUSINESSUNIT_NAME AS Business_unit,
    ACTUAL_ACTIVITY_CATEGORY AS Activity,
    actual_start_date,
    actual_end_date,
    Start_offset,
    end_offset,
    actual_offset
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY agent_id, MANAGEMENTUNIT_NAME, BUSINESSUNIT_NAME, ACTUAL_ACTIVITY_CATEGORY, actual_start_date
            ORDER BY start_period DESC
        ) AS record_rank
    FROM (
        SELECT DISTINCT 
            dt.start_date AS start_period,
            dt.agent_id,
            dt.MANAGEMENTUNIT_NAME,
            dt.BUSINESSUNIT_NAME,
            dt.ACTUAL_ACTIVITY_CATEGORY,
            -- 处理跨天的时间边界
            CASE WHEN dt.actual_start_date > dd.date THEN dt.actual_start_date ELSE dd.date END AS actual_start_date,
            CASE WHEN dt.actual_end_date < DATEADD(d,1,dd.date) THEN dt.actual_end_date ELSE DATEADD(s,-1,DATEADD(d,1,dd.date)) END AS actual_end_date,
            -- 计算时间偏移量
            DATE_PART('epoch_second', TO_TIMESTAMP_NTZ(actual_start_date)) AS Start_offset,
            DATE_PART('epoch_second', TO_TIMESTAMP_NTZ(actual_end_date)) AS end_offset,
            DATE_PART('epoch_second', TO_TIMESTAMP_NTZ(actual_end_date)) - DATE_PART('epoch_second', TO_TIMESTAMP_NTZ(actual_start_date)) AS actual_offset
        FROM (
            SELECT 
                seq4() AS id,
                start_date,
                agent_id,
                BUSINESSUNIT_NAME,
                MANAGEMENTUNIT_NAME,
                ACTUAL_ACTIVITY_CATEGORY,
                ACTUAL_START_DATE,
                ACTUAL_END_DATE,
                ACTIVITY_DURATION
            FROM MYTABLE
        ) dt
        -- 关联日期维度表，拆分跨天记录
        JOIN (
            SELECT 
                seq4() AS id,
                ROW_NUMBER() OVER (ORDER BY id) AS row_num,
                DATEADD(day,row_num,'1999-12-31'::timestamp) AS date
            FROM TABLE(GENERATOR(ROWCOUNT => 36500))
        ) dd ON dd.date BETWEEN DATE_TRUNC(day,dt.actual_start_date) AND dt.actual_end_date
    ) base_data
) final_data
WHERE record_rank = 1;

逻辑说明 #

1. 分组维度：根据业务场景，我们按ID、管理单元、业务单元、活动类型以及实际开始时间分组，确保同一业务场景下的记录被归为一组。
2. 排序规则：在每个分组内，按start_period降序排列，这样最新插入周期的记录会被标记为record_rank=1。
3. 过滤结果：通过WHERE record_rank=1筛选出每个分组内的最新记录，完美匹配你的预期输出。

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