#sql-server
#sql-сервер
Вопрос:
Это мой старый запрос:
Select AccountID,AccountDesc,
CASE
WHEN NOT EXISTS (select AccountID from #B where #B.AccountID = P1.AccountID) THEN cast(cast(GETDATE() as date) as varchar)
ELSE cast(cast(CreateDate as date) as varchar)
END
FROM #A P1
WHERE P1.EndDate = (Select max(EndDate) from #A P2 where P1.AccountID = P2.AccountID and P2.Categ1 = 'F'
group by P2.AccountID)
and P1.StartDate = (Select max(StartDate) from #A P2 where P1.AccountID = P2.AccountID and P2.Categ1 = 'F'
group by P2.AccountID)
and P1.CreateDate = (Select max(CreateDate ) from #A P2 where P1.AccountID = P2.AccountID and P2.Categ1 = 'F'
group by P2.AccountID)
and P1.NewStartDate= (Select max(NewStartDate) from #A P2 where P1.AccountID = P2.AccountID and P2.Categ1 = 'F'
group by P2.AccountID)
Я хотел избавиться от повторяющихся самосоединений и групповых операций в предложении where, поэтому я избавился от части «Category1 = F» и поместил ее вместо этого в предложение where рабочей таблицы #A, когда я вставляю в нее. И я полагал, что самосоединение предназначено только для получения максимальных значений, поэтому, поскольку я создал рабочую таблицу, я не думал, что мне больше понадобится это самосоединение. Итак, я заканчиваю с этим новым запросом:
Select AccountID,AccountDesc,
CASE
WHEN NOT EXISTS (select AccountID from #B where #B.AccountID = P1.AccountID) THEN cast(cast(GETDATE() as date) as varchar)
ELSE cast(cast(CreateDate as date) as varchar)
END
FROM #A P1
WHERE P1.EndDate = (Select max(EndDate) from #A)
and P1.StartDate = (Select max(StartDate) from #A)
and P1.CreateDate = (Select max(CreateDate) from #A)
and P1.NewStartDate= (Select max(NewStartDate) from #A)
Но это не возвращает то же количество записей, я полагаю, потому что я также удалил предложения «Group By» в условиях where?
Может ли кто-нибудь помочь мне ускорить этот исходный запрос, но при этом вернуть те же результаты?
Комментарии:
1. Все эти подзапросы вообще не помогают. Примеры данных и ожидаемые результаты также помогли бы здесь.
2. Ларну, спасибо за comment…it мне потребуется некоторое время, чтобы разобраться, как предоставить образцы данных (некоторые причины — очистка личной информации, очень большой набор данных, а также я никогда не добавлял данные в stack overflow)… Я попытаюсь посмотреть, как это как-то работает.
3. Какую версию SQL Server вы используете? Этот запрос можно переписать для использования оконных функций ( learn.microsoft.com/en-us/sql/t-sql/queries /… ).
Ответ №1:
Для начала: все эти GROUP BY AccountID
предложения в вашем запросе излишни, поскольку подзапросы в любом случае всегда выбирают данные только для одного идентификатора учетной записи.
Что было опробовано в WHERE
предложении, так это:
WHERE (AccountID, EndDate, StartDate, CreateDate, NewStartDate) IN
(
SELECT AccountID, MAX(EndDate), MAX(StartDate), MAX(CreateDate), MAX(NewStartDate)
FROM #A
WHERE Categ1 = 'F'
GROUP BY AccountID
)
Однако SQL Server не поддерживает этот синтаксис. Итак, преобразуйте это в EXISTS
:
WHERE EXISTS
(
SELECT 1
FROM #A P2
WHERE P2.AccountID = P1.AccountID AND P2.Categ1 = 'F'
HAVING P1.EndDate = MAX(P2.EndDate)
AND P1.StartDate = MAX(P2.StartDate)
AND P1.CreateDate = MAX(P2.CreateDate)
AND P1.NewStartDate = MAX(P2.NewStartDate)
)
Комментарии:
1. Хорошо, это проницательно — я уже принял другой ответ, но ваше объяснение имеет смысл, и я тоже попробую его использовать. Приятно видеть несколько способов решения проблемы. Спасибо!
Ответ №2:
вы можете создать подзапрос и объединить его, чтобы вычисления выполнялись один раз.
;with cte as
(
select Account,max(EndDate) ED ,max(StartDate) SD,max(CreateDate ) CD ,max(NewStartDate) NSD
from #a
where Categ1 = 'F'
group by Account
)
Select AccountID,AccountDesc,
CASE
WHEN NOT EXISTS (select AccountID from #B where #B.AccountID = P1.AccountID) THEN cast(cast(GETDATE() as date) as varchar)
ELSE cast(cast(CreateDate as date) as varchar)
END
FROM #A p1
join cte on p1.Account=cte.Account
and p1.StartDate=cte.SD
and p1.EndDate = cte.ED
and p1.CreateDate = cte.CD
and p1.NewStartDate=cte.NSD
вот еще одно усовершенствование:
CASE
WHEN NOT EXISTS (select AccountID from #B where #B.AccountID = P1.AccountID) THEN cast(cast(GETDATE() as date) as varchar)
ELSE cast(cast(CreateDate as date) as varchar)
может быть изменено путем объединения слева #B
;with cte as
(
select Account,max(EndDate) ED ,max(StartDate) SD,max(CreateDate ) CD ,max(NewStartDate) NSD
from #a
where Categ1 = 'F'
group by Account
)
Select AccountID,AccountDesc,
case when p2.Account is not null
then cast(CreateDate as varchar(10))
else cast(cast(getdate() as date) as varchar(10)) end
FROM #A p1
join cte on p1.Account=cte.Account
and p1.StartDate=cte.SD
and p1.EndDate = cte.ED
and p1.CreateDate = cte.CD
and p1.NewStartDate=cte.NSD
left join #B p2 on p1.Account=p2.Account
Комментарии:
1. Это хорошо работает! Это действительно быстрее и возвращает то же количество записей. Я пытался использовать CTE, но потом почему-то подумал, что это бесполезно …. но теперь я вижу причину, по которой это полезно, заключается в применении функции group-by к каждому условию where, но при этом механизм запросов должен был вычислять ее только один раз. Спасибо!
2. Помните, что всегда лучше (по крайней мере, с DBS) пытаться выполнить анализ набора по одной строке.
3. В качестве последующего вопроса, допустим, теперь я хочу выбрать другой столбец, например ‘EmployeeID’, в конечном запросе из #A …. или я хочу выбрать больше дополнительных столбцов. Будет ли этот метод неэффективным, потому что, если # A — это очень большой набор данных, мне кажется, что я запрашиваю его дважды, что замедлилось бы при запросе многих столбцов?
4. На самом деле нет никакой дополнительной нагрузки по добавлению столбцов из #A в окончательный выбор. Работа уже выполнена.
5. Когда я выбираю только эти 3 столбца AccountId и AccountDesc и CreateDate, как показано здесь, мой запрос занимает около 30 секунд. Когда я выбираю еще кучу столбцов, таких как «Выберите AccountId, AccountDesc, ColA, ColB, ColC, ColD, ColE, ColF, ColG …», запрос выполняется в течение 3 минут, и новое изменение с присоединением # B ничего не изменило.
Ответ №3:
Я верю, что это поможет вам
Select AccountID,AccountDesc,
CASE WHEN NOT EXISTS (select AccountID from #B where #B.AccountID = P1.AccountID) THEN cast(cast(GETDATE() as date) as varchar)
ELSE cast(cast(CreateDate as date) as varchar) END
FROM #A P1
CROSS APPLY (
SELECT max(EndDate) EndDate, max(StartDate) StartDate, max(CreateDate ) CreateDate ,max(NewStartDate)NewStartDate
FROM #A P2
WHERE P1.AccountID = P2.AccountID and P2.Categ1 = 'F'
) AS P3
WHERE P1.EndDate = P3.EndDate AND P1.StartDate = P3.StartDate
AND P1.CreateDate = P3.CreateDate AND P1.NewStartDate = p3.NewStartDate