Оптимизация запроса MS SQL

#sql #sql_server

У меня есть оригинальный запрос нахождения дубликатов:

Original query:

SELECT
    cd1.cust_number_id, cd1.cust_number_id, cd1.First_Name, cd1.Last_Name
FROM @Customer_Data cd1
    inner join @Customer_Data cd2 on
        cd1.Cd_Id <> cd2.Cd_Id
        and cd2.cust_number_id <> cd1.cust_number_id
        AND cd2.Flag = N'A'
        AND cd2.Cust_active = 1
        and cd2.First_Name = cd1.First_Name
        and cd2.Last_Name = cd1.Last_Name
    inner join @Customer c1 on c1.Cust_id = cd1.cust_number_id
    inner join @Customer c2 on c2.cust_id = cd2.cust_number_id
WHERE c1.cust_number <> c2.cust_number  
        AND cd1.Flag = N'A'
        AND cd1.Cust_active = 1




Я оптимизировал его следующим образом.

Optimized query:

 SELECT cd1.cust_number_id, cd1.cust_number_id, cd1.First_Name,cd1.Last_Name
 FROM (
    SELECT cdResult.cust_number_id, cdResult.First_Name,cdResult.Last_Name, COUNT(*)
OVER (PARTITION BY cdResult.First_Name, cdResult.Last_Name) as cnt_name_bday  
    FROM @Customer_Data cdResult
    WHERE cdResult.Flag = N'A'
        AND cdResult.Cust_active = 1
        AND cdResult.First_Name IS NOT NULL
        AND cdResult.Last_Name IS NOT NULL) AS cd1
 WHERE cd1.cnt_name_bday > 1;



4ой и 5ой строки не должно быть в результате выполнения запроса

Test data:

DECLARE @Customer_Data TABLE
(
    Cd_Id INT,
    cust_number_id INT,
    First_Name NVARCHAR(30),
    Last_Name NVARCHAR(30),
    Flag NVARCHAR(10),
    Cust_active INT
)

INSERT @Customer_Data (Cd_Id,cust_number_id,First_Name,Last_Name, Flag, Cust_active)
VALUES (1, 22, N'Alex', N'Bor',  'A', 1),
       (2, 22, N'Alex', N'Bor',  'A', 1),
       (3, 24, N'Alex', N'Bor',  'A', 1),
       (4, 24, N'Tom', N'Cruse', 'A', 1),
       (5, 24, N'Tom', N'Cruse', 'A', 1)

DECLARE @Customer TABLE
(
    Cust_id INT,
    Cust_number INT
)


INSERT @Customer (Cust_id, Cust_number)
VALUES (22, 022),
       (23, 022),
       (24, 024),
       (25, 024)


У меня возникла проблема в том, что я не могу исключить записи с одинаковым cust_numebr.
Результат должен быть, как на первом скриншоте.
    

Ответы

Ответ 1

Исключить одинаковые cust_number довольно просто. Получаем кроме количества строк
еще максимальный и минимальный cust_number и проверяем что они не равны. Хотя проверка
на количество строк в этом случае становится не нужна.

SELECT cd1.cust_number_id, cd1.cust_number_id, cd1.First_Name,cd1.Last_Name
  FROM (
    SELECT cdResult.cust_number_id, cdResult.First_Name,cdResult.Last_Name,
           MIN(cust_number_id) OVER (PARTITION BY cdResult.First_Name, cdResult.Last_Name)
as min_cust,
           MAX(cust_number_id) OVER (PARTITION BY cdResult.First_Name, cdResult.Last_Name)
as max_cust
      FROM @Customer_Data cdResult
     WHERE cdResult.Flag = N'A'
       AND cdResult.Cust_active = 1
       AND cdResult.First_Name IS NOT NULL
       AND cdResult.Last_Name IS NOT NULL
  ) AS cd1
 WHERE min_cust!=max_cust;


Ответ 2

можно вот так!

SELECT DISTINCT cd1.CD_ID, cd1.CUST_NUMBER_ID,
   cd1.CUST_NUMBER_ID,
   cd1.FIRST_NAME,
   cd1.LAST_NAME
FROM @CUSTOMER_DATA cd1
  INNER JOIN @CUSTOMER_DATA cd2
    ON cd1.CD_ID <> cd2.CD_ID
    AND cd2.CUST_NUMBER_ID <> cd1.CUST_NUMBER_ID
    AND cd2.FIRST_NAME = cd1.FIRST_NAME
    AND cd2.LAST_NAME = cd1.LAST_NAME
WHERE cd1.FLAG = N'A'
  AND cd1.CUST_ACTIVE = 1
ORDER BY cd1.CD_ID;