数据对比查询，如何优化呢？

Question

我在Postgresql中的两个表之间做了一个比较，逐行寻找差异以将其保存到另一个表中，但我想知道是否有其他方法可以做到这一点，或者我如何优化它：

insert into changes
select 
daily2.id_registro 
from daily2 
inner join 
daily on daily2.id_registro = daily.id_registro
where
daily2.fecha_ingreso!=daily.fecha_ingreso
or
daily2.fecha_sintomas!=daily.fecha_sintomas
or
daily2.fecha_def!=daily.fecha_def
or
daily2.intubado!=daily.intubado
or
daily2.neumonia!=daily.neumonia
or
daily2.diabetes!=daily.diabetes
or
daily2.epoc!=daily.epoc
or
daily2.asma!=daily.asma
or
daily2.inmusupr!=daily.inmusupr
or
daily2.hipertension!=daily.hipertension
or
daily2.otra_com!=daily.otra_com
or
daily2.cardiovascular!=daily.cardiovascular
or
daily2.obesidad!=daily.obesidad
or
daily2.renal_cronica!=daily.renal_cronica
or
daily2.tabaquismo!=daily.tabaquismo
or
daily2.otro_caso!=daily.otro_caso
or
daily2.resultado!=daily.resultado

Answer 1

如果您知道每个id都有一个匹配项，那么您只需：

insert into changes
select id_registro from 
(
    select * from daily2 
    except
    select * from daily
) as a

这样你就不需要写每一列了。

如果您需要确保id在两个表中，您只需添加一个检查，以查看id是否在两个表中：

insert into changes
select id_registro from 
(
    select * from daily2 
    except
    select * from daily
) as a
where a.id_registro in
(
    select daily2.id_registro
    from daily inner join daily2
    on daily.id_registro = daily2.id_registro 
);

这是相当重的，所以在巨型桌子上要小心。

Answer 2

你想要元组相等吗？

where
    (daily2.fecha_ingreso, daily2.fecha_sintomas, daily2.fecha_def, ...) 
    <> (daily.fecha_ingreso, daily.fecha_sintomas, daily.fecha_def, ...)

我想知道整个查询是否可以用exists更有效地表达

insert into changes (id_registro)
select d2.id_registro 
from daily2 d2
where exists (
    select 1
    from daily d
    where 
        d1.id_registro = d.id_registro
        and (d2.fecha_ingreso, d2.fecha_sintomas, d2.fecha_def, ...) 
         <> (d.fecha_ingreso, d.fecha_sintomas, d.fecha_def, ...)
)