Oracle中查找数据范围重复/多值的活跃记录问题

要识别Point_of_sale表中因旧记录未设置end_date导致的多活跃记录问题，我们可以通过PARTITION BY结合窗口函数来分组排查，核心思路是按Brand和Sub_brand聚合，找出同一分组在查询时间点存在多条活跃记录的情况。

方案一：统计分组内活跃记录数 #

先筛选出查询时间点的活跃记录，再统计每个分组的活跃记录总数，筛选出总数≥2的分组记录：

DECLARE @query_date DATE = '2021-06-01';

WITH active_records AS (
    SELECT 
        Brand,
        Sub_brand,
        value,
        start_date,
        end_date,
        -- 按品牌+子品牌分组，给记录按生效时间倒序编号
        ROW_NUMBER() OVER (PARTITION BY Brand, Sub_brand ORDER BY start_date DESC) AS record_rank,
        -- 统计当前分组下的活跃记录总数
        COUNT(*) OVER (PARTITION BY Brand, Sub_brand) AS active_record_count
    FROM Point_of_sale
    -- 定义活跃记录：生效时间早于等于查询日，且未过期（结束日期为空或晚于查询日）
    WHERE start_date <= @query_date
      AND (end_date IS NULL OR end_date >= @query_date)
)
-- 提取存在多活跃记录的分组数据
SELECT Brand, Sub_brand, value, start_date, end_date
FROM active_records
WHERE active_record_count >= 2
ORDER BY Brand, Sub_brand, start_date DESC;

这段代码中：

• CTE active_records先过滤出查询时点（示例为2021-06-01）的所有活跃记录；
• PARTITION BY Brand, Sub_brand确保我们只在同一品牌和子品牌的范围内统计；
• 最后筛选出活跃记录数≥2的结果，这些就是未正确归档的冲突记录——正常情况下每个分组同一时间点应只有1条最新的活跃记录。

方案二：用LAG函数对比上一条记录的状态 #

通过LAG()函数获取分组内上一条记录的end_date，直接判断是否存在未归档的旧记录：

DECLARE @query_date DATE = '2021-06-01';

WITH ordered_records AS (
    SELECT 
        Brand,
        Sub_brand,
        value,
        start_date,
        end_date,
        -- 获取同一分组中上一条记录的结束日期
        LAG(end_date) OVER (PARTITION BY Brand, Sub_brand ORDER BY start_date) AS prev_end_date
    FROM Point_of_sale
)
-- 筛选出当前记录活跃，且上一条记录未设置结束日期（也处于活跃状态）的情况
SELECT Brand, Sub_brand, value, start_date, end_date
FROM ordered_records
WHERE start_date <= @query_date
  AND (end_date IS NULL OR end_date >= @query_date)
  AND prev_end_date IS NULL
ORDER BY Brand, Sub_brand, start_date DESC;

这个方法更直接：如果当前记录是活跃状态，且它的上一条历史记录end_date为空（说明旧记录未被归档，仍处于活跃），就属于数据不一致的情况。

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