基于匹配ISIN列对比PostgreSQL表整行数据，返回差异行ID

建表语句 #

create table sval(id,closing_price,opening_price,ISIN,closing_fx,market_cap)
as values(104, 55.3, 44, 'KKJJ102',0,0)
        ,(432, 99  , 77, 'JJII333',0,0)
        ,(444, 44  , 33, 'KKJJ102',0,0)
        ,(888, 33  , 41, 'JJEOD23',0,0)
        ,(422, 99  , 77, 'JJII333',0,0)
        ,(222, 33  , 41, 'JJEOD23',0,0);

查询方法 #

已知每个ISIN恰好对应两行，我们可以通过自连接对比同一ISIN下两行的非ID、非ISIN列，筛选出存在差异的行ID：

方法一：行比较简洁版 #

SELECT id
FROM sval
WHERE ISIN IN (
    SELECT s1.ISIN
    FROM sval s1
    JOIN sval s2 ON s1.ISIN = s2.ISIN AND s1.id <> s2.id
    WHERE (s1.closing_price, s1.opening_price, s1.closing_fx, s1.market_cap) <> (s2.closing_price, s2.opening_price, s2.closing_fx, s2.market_cap)
);

方法二：显式列对比版 #

如果需要更直观的列级对比，可使用以下语句：

SELECT s1.id, s2.id
FROM sval s1
JOIN sval s2 ON s1.ISIN = s2.ISIN AND s1.id < s2.id
WHERE NOT (
    s1.closing_price = s2.closing_price
    AND s1.opening_price = s2.opening_price
    AND s1.closing_fx = s2.closing_fx
    AND s1.market_cap = s2.market_cap
)
UNION ALL
SELECT s2.id, s1.id
FROM sval s1
JOIN sval s2 ON s1.ISIN = s2.ISIN AND s1.id < s2.id
WHERE NOT (
    s1.closing_price = s2.closing_price
    AND s1.opening_price = s2.opening_price
    AND s1.closing_fx = s2.closing_fx
    AND s1.market_cap = s2.market_cap
);

结果说明 #

执行后会返回104和444这两个ID，它们属于同一ISIN但其他列存在差异；而ISIN相同且其余列完全一致的432与422、888与222则不会出现在结果中。

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