SQL表重叠有效时间槽去重及关联查询实现方案咨询

核心逻辑 #

利用窗口函数按minor_id分组，同分组内匹配到目标时间点的记录按major_id倒序排序，取排序后的第一条，即可保证重叠场景下仅保留major_id最大的行。

1. 单表查询实现 #

SELECT * 
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY minor_id ORDER BY major_id DESC) AS rn
    FROM mytable m2 
    WHERE minor_id = 666 
      AND '02.01.2001' >= m2.valid_time_from 
      AND '02.01.2001' < m2.valid_time_until -- 注意与表实际字段名统一，原查询写为valid_time_to请自行调整
) t
WHERE rn = 1;

如果使用支持QUALIFY语法的SQL引擎（如Spark SQL、BigQuery、Snowflake等），可简化为：

SELECT *
FROM mytable m2 
WHERE minor_id = 666 
  AND '02.01.2001' >= m2.valid_time_from 
  AND '02.01.2001' < m2.valid_time_until
QUALIFY ROW_NUMBER() OVER (PARTITION BY minor_id ORDER BY major_id DESC) = 1;

2. 关联查询实现 #

将mytable的去重逻辑封装为子查询后再与另一张表关联即可：

SELECT * 
FROM my_other_table m2
JOIN (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY minor_id ORDER BY major_id DESC) AS rn
    FROM mytable
) m ON m2.minor_id = m.minor_id 
   AND m2.timestamp >= m.valid_time_from 
   AND m2.timestamp < m.valid_time_until
   AND m.rn = 1;

使用QUALIFY的简化版本：

SELECT * 
FROM my_other_table m2
JOIN mytable m 
  ON m2.minor_id = m.minor_id 
  AND m2.timestamp >= m.valid_time_from 
  AND m2.timestamp < m.valid_time_until
QUALIFY ROW_NUMBER() OVER (PARTITION BY m2.主键字段名 ORDER BY m.major_id DESC) = 1;

注意：简化写法的PARTITION BY中需替换为my_other_table的主键字段，保证每一条主表记录仅匹配一条mytable的行。

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