检查基于相似的一列中的所有其他列的变化(Check for changes in all other

FirstName     LastName     SSN            Phone          EncounterID
Justin        Kelley      555-55-5555     517-555-1212    123456789
Justin        Kelly       555-55-5555     517-555-1212    123456789
James         Smith       444-44-4444     312-555-3434    99944444
James         Smith       444-44-4444     312-555-3434    99944444

我有一个表像上百万EncounterIDs上面的一个。 我需要知道有在每列的差异（缺陷）的次数。 我的例子输出为：

First Name - 2/2
Last Name - 1/2
SSN - 2/2
Phone - 2/2

任何帮助这里？

Answer 1:

你基本上要的数据是，在一列多个值的实体的数量。

这是最容易计算的列的基础上：

select sum(case when NumFirstNames <> 1 then 1 else 0 end) as DifferentFirstNames,
       sum(case when NumLastNames <> 1 then 1 else 0 end) as DifferentLastNames,
       sum(case when NumSSN <> 1 then 1 else 0 end) as DifferentSSN,
       sum(case when NumPhone <> 1 then 1 else 0 end) as DifferentPhone       
from (select EncounterId, count(*) as Num,
             count(distinct FirstName) as NumFirstNames,
             count(distinct LastName) as NumLastNames,
             count(distinct SSN) as NumSSN,
             count(distinct Phone) as NumPhone
      from table t
      group by EncounterId
     ) e;

您可以格式化你喜欢的结果。

文章来源: Check for changes in all other columns based on similarities one column