如何高效筛选未被同用户其他记录完全包含的数据？

原表数据 #

预期结果 #

高效查询方案（推荐） #

使用EXISTS子查询是最直观且高效的方式，它支持短路匹配（找到符合条件的记录立即停止），适合大数据量场景：

SELECT thing_id
FROM your_table t1
WHERE NOT EXISTS (
    SELECT 1
    FROM your_table t2
    WHERE t2.user_id = t1.user_id
      AND t2.thing_id != t1.thing_id
      AND t2.start <= t1.start
      AND t2.end >= t1.end
);

逻辑解释 #

• 对每条记录t1，检查同一用户下是否存在另一条不同的记录t2，其时间范围完全覆盖t1：
  • t2.start <= t1.start：t2的开始时间不晚于t1
  • t2.end >= t1.end：t2的结束时间不早于t1
• 如果存在这样的t2，则排除t1；否则保留t1的thing_id。

窗口函数方案（备选） #

如果偏好窗口函数写法，可通过统计同用户下能包含当前记录的条目数来筛选：

WITH record_check AS (
    SELECT
        thing_id,
        -- 统计同用户下，能包含当前记录的其他条目数量
        COUNT(*) OVER (PARTITION BY user_id) FILTER (
            WHERE user_id = t.user_id
              AND thing_id != t.thing_id
              AND start <= t.start
              AND end >= t.end
        ) AS has_containing_record
    FROM your_table t
)
SELECT thing_id
FROM record_check
WHERE has_containing_record = 0;

性能优化建议 #

为了加速查询，创建复合索引：

CREATE INDEX idx_user_time_range ON your_table(user_id, start, end);

这个索引能让数据库快速定位同一用户下的时间范围记录，大幅提升EXISTS子查询的效率。

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