如何对sval表去重重复id并与ival表按timestamp取交集？

需求1：过滤sval表，保留每个id对应最早timestamp的记录 #

提供两种高效实现方式：

方式1：窗口函数精准筛选 #

通过窗口函数按id分组、时间升序排序，直接取每组第一条（最早时间）记录：

SELECT id, fid, timestamp, val
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY timestamp ASC) AS row_num
    FROM sval
    WHERE fid = 4044 AND val = 'True'
) filtered_sval
WHERE row_num = 1;

方式2：聚合函数分组取最小时间 #

先分组id并提取每组最早时间，再关联原表获取完整记录：

SELECT s.id, s.fid, s.timestamp, s.val
FROM sval s
JOIN (
    SELECT id, MIN(timestamp) AS earliest_ts
    FROM sval
    WHERE fid = 4044 AND val = 'True'
    GROUP BY id
) min_ts ON s.id = min_ts.id AND s.timestamp = min_ts.earliest_ts
WHERE s.fid = 4044 AND s.val = 'True';

需求2：找出ival表中与过滤后sval记录timestamp相同的所有id列表 #

基于需求1的结果，关联ival表筛选匹配时间的id：

方式1：JOIN关联查询 #

SELECT DISTINCT i.id
FROM ival i
JOIN (
    SELECT timestamp
    FROM (
        SELECT 
            *,
            ROW_NUMBER() OVER (PARTITION BY id ORDER BY timestamp ASC) AS row_num
        FROM sval
        WHERE fid = 4044 AND val = 'True'
    ) filtered_sval
    WHERE row_num = 1
) s_ts ON i.timestamp = s_ts.timestamp
WHERE i.fid = 3994 AND i.val = 0;

方式2：IN子查询 #

SELECT DISTINCT id
FROM ival
WHERE fid = 3994 AND val = 0
AND timestamp IN (
    SELECT MIN(timestamp)
    FROM sval
    WHERE fid = 4044 AND val = 'True'
    GROUP BY id
);

补充说明 #

• DISTINCT用于避免ival表中同一时间对应重复id时出现结果重复
• 两种方式均可实现需求，窗口函数在大数据量场景下性能表现更优

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