Блог

У меня есть таблица базы данных записей пользователей со столбцами для идентификатора пользователя, имени, фамилии, DOB и адреса электронной почты. Имя, фамилия и адрес электронной почты являются обязательными значениями, но DOB может быть нулевым, если пользователь не предоставил эту информацию, поэтому несколько строк могут выглядеть следующим образом:

 FirstName          LastName           DOB               Email                      UserID
John               Doe                1990-01-01        johndoe@gmail.com          1
Jane               Doe                1990-02-01        janedoe@gmail.com          2
John               Doe                NULL              johndoe@gmail.com          3
Paul               Blart              1985-01-01        mallcop@gmail.com          4
Clark              Kent               NULL              ImNotSuperman@gmail.com    5
Paul               Blart              1985-01-01        mallcop@gmail.com          6
  

И я пытаюсь написать запрос (который является частью более крупной программы), чтобы идентифицировать повторяющиеся записи пользователей в базе данных. Требования заключаются в том, что FirstName, LastName и Email должны быть идентичными, и если есть значение для DOB, то оно должно быть идентичным, но если оно равно null, оно все равно может быть помечено как дубликат. Итак, в приведенной выше таблице будут выбраны два имени Джона Доу и два имени Пола Бларта. Я хочу сделать это в инструкции partition. Итак, моя первоначальная попытка:

 SELECT  COUNT(UserID) OVER (Partition BY FirstName, LastName, DOB, Email) AS Count,
DENSE_RANK() OVER (ORDER BY FirstName, LastName, DOB, Email) AS RANK,
UserID, FirstName, LastName, DOB, Email
FROM People
where COUNT(UserID) OVER (Partition BY FirstName, LastName, DOB, Email) > 1
  

Который правильно выбирает Поля Бларта в качестве дубликатов, но не Джона Доу, потому что у одного есть нулевое значение для DOB. Есть ли какой-либо способ сделать так, чтобы эти записи были выбраны правильно?

Это может быть проще выразить с помощью exists :

 select t.*
from mytable t
where exists (
    select 1
    from mytable t1
    where 
        t1.id <> t.id
        and t1.firstname = t.firstname
        and t1.lastname = t.lastname
        and t1.email = t.email
        and (t1.dob = t.dob or t1.dob is null or t.dob is null)
)
  

Вы можете сделать это с помощью оконных функций:

 select t.*
from (select t.*,
             count(*) over (partition by firstname, lastname, email, dob) as cnt,
             sum(case when dob is null then 1 else 0 end) over (partition by firstname, lastname, email) as cnt_null
      from t
     ) t
where cnt > 1 or 
      (dob is not null and cnt_null > 0);