如何在Redshift中按条件查询行并校验ID对应值匹配关系

我来帮你搞定这个Redshift上的SQL查询需求，先把所有信息理清楚：

Table 1 数据 #

ID    Date                Value
1254  2018-01-01 15:20:45 RT-RF
1254  2018-01-10 18:22:45 RE-RI
1255  2018-01-25 17:35:40 RR-RU
1255  2018-01-30 13:19:55 RY-RR

Table 2 数据 #

ID    Date2               Value2
1254  2018-01-01 08:12:16 RT-RF
1255  2018-01-25 18:14:18 RT-RF

• 针对每个唯一ID，检查table_2中的Value2是否与table_1中该ID对应最早日期的Value不匹配
• 输出符合条件的结果集，正确的预期输出应该是：

ID    Date                Date2               Value  Value2
1255  2018-01-25 17:35:40 2018-01-25 18:14:18 RR-RU  RT-RF

（注：你给出的预期输出里日期和Value存在笔误，我这里按逻辑修正了）

这里推荐用CTE结合窗口函数的方式，在Redshift上性能稳定且逻辑清晰：

-- 先获取table_1中每个ID的最早记录
WITH table_1_earliest AS (
    SELECT 
        ID,
        Date,
        Value,
        -- 按ID分组，日期升序编号，最早的记录编号为1
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Date ASC) AS rn
    FROM table_1
)
-- 关联table_2，筛选出Value不匹配的记录
SELECT 
    t1.ID,
    t1.Date,
    t2.Date2,
    t1.Value,
    t2.Value2
FROM table_1_earliest t1
INNER JOIN table_2 t2 ON t1.ID = t2.ID
WHERE t1.rn = 1  -- 只保留每个ID的最早记录
AND t1.Value != t2.Value2;  -- 匹配Value不相等的条件

逻辑说明 #

1. 用table_1_earliest这个CTE给每个ID的所有记录按日期从小到大编号，最早的记录会被标记为rn=1
2. 把这个CTE和table_2通过ID关联，筛选出编号为1且Value与Value2不相等的记录，就是我们要的结果

如果想要更简洁的写法，也可以用FIRST_VALUE()窗口函数直接获取最早值：

SELECT DISTINCT
    t1.ID,
    FIRST_VALUE(t1.Date) OVER (PARTITION BY t1.ID ORDER BY t1.Date ASC) AS Date,
    t2.Date2,
    FIRST_VALUE(t1.Value) OVER (PARTITION BY t1.ID ORDER BY t1.Date ASC) AS Value,
    t2.Value2
FROM table_1 t1
JOIN table_2 t2 ON t1.ID = t2.ID
WHERE FIRST_VALUE(t1.Value) OVER (PARTITION BY t1.ID ORDER BY t1.Date ASC) != t2.Value2;

不过第一种CTE的写法在数据量较大时，Redshift的执行计划会更高效，因为提前过滤掉了非最早的记录。

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