Блог

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

 abc123
def456
ghi789
  

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

 ACME Agency abc123 fakeemail@fake.com   2
ACME Agency abc123 thisisonlyatest@info.com 94
ACME Agency abc123 notarealemail@none.com   5
ACME Agency abc123 helloworld@dummy.com 2
ACME Agency abc123 info@info.net 2
  

Оттуда нам нужно получить историю транзакций, связанную с каждым адресом электронной почты — если все пойдет хорошо, мы должны получить количество строк, равное итоговым значениям, указанным в предыдущем запросе — таким образом, в данном случае всего 105 строк. Затем мы добавляем эти строки во временную таблицу, которая в конечном итоге будет включать весь результирующий набор для каждой основной строки.

Когда имеешь дело с такого рода ситуацией, моим первым побуждением является переход к циклу while или курсору, поскольку это та логика, с которой я наиболее знаком. Мое исследование показало, что, в общем, подходы, основанные на множестве, — это правильный путь. Однако в нем также сказано, что когда курсоры необходимы, это обычно в случаях, когда вам приходится перебирать каждую строку и что-то делать.

Мой вопрос в том, так ли это здесь? Правильно ли я представляю проблему, в которой курсоры — даже двойные вложенные курсоры — являются необходимым злом, или в этом случае существует подход, основанный на множестве?

Я могу отредактировать, чтобы включить запрос, если это необходимо, но на данный момент это немного сложно. Я хотел бы повысить скорость выполнения, но нам нужно просмотреть несколько тысяч основных идентификаторов — около 5 тыс., если я правильно фильтрую.

В итоге я нашел старшего разработчика, который показал мне, как это сделать без использования курсора.

Мой первый план состоял в том, чтобы выполнить запрос с помощью курсора, чтобы просмотреть список основных идентификаторов, на которые мы должны были ссылаться.

 DECLARE @agency_number NVARCHAR(500)

DECLARE db_cursor CURSOR FOR
SELECT Master_Agency_Number
FROM temptablelist

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @agency_number

WHILE @@FETCH_STATUS = 0
BEGIN
  

Как только я был в цикле, я бы извлек соответствующие адреса электронной почты во временную таблицу на основе номера агентства, а затем извлек другой набор данных на основе таблицы email и, наконец, добавил этот результирующий набор в конечную таблицу temptable, которая в конечном итоге содержала бы весь результирующий набор, включающий все идентификаторы основного агентства. Это сделало работу лучше, чем некоторые из моих первоначальных попыток — мы получили нужные данные за 45 минут. Но для того, что нам было нужно, нам нужно было быстрее.

Другой разработчик, наконец, показал мне другой способ сделать это.

Мы загрузили идентификатор главного агентства вместе с некоторой другой необходимой нам информацией во временную таблицу вместо того, чтобы использовать его для запуска курсора.

 INSERT 
emaillist SELECT dm.[Master_Agency_Number]
,vwp.[Email_Address]
,count(*) as TotalOccurrence
FROM   table1 vwP
INNER JOIN table2 dm
ON vwP.AgencyNum = dm.Agency_Number
WHERE
   --filter for active policies
   AND vwP.CurrentTypeAct <> 'Canceled'
   AND vwP.Email_Address is not NULL
   group by dm.[Master_Agency_Number], vwp.[Email_Address]
   HAVING count(*) >= 2
   order by dm.[Master_Agency_Number] asc, count(*) desc
  

Оттуда мы написали другой запрос, который выполнил внутреннее объединение с этой временной таблицей, чтобы убедиться, что мы извлекаем данные только для соответствующих агентств, а затем использовали IN предложение, чтобы убедиться, что мы извлекаем данные с правильных адресов электронной почты.

 SELECT DISTINCT
    [all the column names]
FROM table1 vwP
LEFT JOIN table2 stgA
    ON vwP.AgencyNum = stgA.Agency_Number
INNER JOIN table3 dm
    ON vwP.AgencyNum = dm.Agency_Number
INNER JOIN emaillist 
    ON emaillist.Master_Agency_Number = dm.Master_Agency_Number
WHERE
--filter for active policies
AND vwP.CurrentTypeAct <> 'Canceled'
and stgA.Email_Address is not null 
and vwp.[Email_Address] is not null
AND vwp.[Email_Address] IN (SELECT Email_Address FROM emaillist WHERE 
dm.Master_Agency_Number = emaillist.Master_Agency_Number)
ORDER BY
dm.[Master_Agency_Number], vwp.[Email_Address]
  

Использование INNER JOIN и IN сократило время выполнения до 45 секунд. Итак, если вы застряли на использовании курсора, попробуйте объединить их, чтобы посмотреть, поможет ли это вам проанализировать данные так, как вам нужно!