Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Запрос для гурманов  [new]
kirser
Member

Откуда: Киев
Сообщений: 225
Доброго дня, коллеги.

Нуждаюсь в совете/помощи с запросом.
Исходные данные:
Есть табличка с данными о звонках сотрудников
CREATE TABLE #CALL 
	(	GroupID		INT, --Код отдела/департамента
		UserID		INT, --Код сотрудника
		CallDate	INT, --Дата звонка
		Amount		INT) --Кол-во звонков за день
INSERT INTO #CALL 	(GroupID, UserID, CallDate, Amount)	
VALUES 
	(1,111,20150827,1),
	(1,111,20150828,2),
	(1,222,20150827,1),		
	(1,222,20150828,4),
	(1,222,20150829,2),
	(1,333,20150827,1),
	(1,333,20150828,4),
	(1,333,20150829,3),
	(1,333,20150830,5),
	(1,333,20150831,1),	
	(1,444,20150827,1),		
	(1,444,20150828,4),
	(1,444,20150829,2)

Задача:
Получить сведения о количестве сотрудников, совершивших 1,2,3,4,5,6,7,8,9, больше 10 звонков за определенный период по отдельно выбранному отделу/департаменту.

Что получилось сделать:
SELECT 
	tt.CoreN, 
	SUM(tt.Amount) AmountN 
FROM 
	(SELECT 
			CASE t.Core 
					when 1 then 1 
					when 2 then 2 
					when 3 then 3 
					when 4 then 4 
					when 5 then 5 
					when 6 then 6 
					when 7 then 7 
					when 8 then 8 
					when 9 then 9 
					else 10 
			END CoreN, 
			COUNT(*) Amount
       FROM 
			(SELECT 
					SUM(Amount) Core
			FROM #CALL with(readpast)
			WHERE 
				GroupID = 1
				and CallDate between 20150826 and 201500901
			GROUP BY UserID
			) t
     GROUP BY t.Core
	 ) tt
GROUP BY 
	tt.CoreN


В результате, получаем:
CoreN AmountN
3 1
7 2
10 1
Т.е. 1 сотрудник совершил 3 звонка, 2 сотрудника совершили по 7 звонков каждый, 1 сотрудник сделал более 10ти звонков

Результат верный, но не уверен в производительности кода.
На больших объемах данных работает долго.
Индексы:
1) пробовали кластерный по GroupID, CallDate, UserID
2) также протестил с некластерным по GroupID, CallDate INCLUDE (Amount, UserID)
Табличка не временная. Код накидал для простоты понимания.

Вероятно, можно написать запрос более изысканно.
Идеи на счет индексации тоже приветствуются.

Заранее спасибо
1 сен 15, 14:32    [18095478]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для гурманов  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20583
Ну пивот же...
1 сен 15, 14:38    [18095540]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для гурманов  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
select t1.CoreN, count(t1.UserID) AmountN
from
(
	select 
		cc.GroupID, cc.UserID, 
		case when sum(cc.amount)<10 then sum(cc.amount) else 10 end CoreN
	from #CALL cc
	WHERE GroupID = 1
		and CallDate between 20150826 and 201500901
	group by GroupID, UserID
) t1
group by t1.CoreN


Индекс по GroupID, CallDate, но затрат на сортировку не избежать.
1 сен 15, 15:07    [18095763]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для гурманов  [new]
kirser
Member

Откуда: Киев
Сообщений: 225
Владислав Колосов,

К сожалению, вы правы, затраты на сортировку остались прежними
1 сен 15, 15:30    [18095886]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для гурманов  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
kirser,

У вас наверняка есть стационарная таблица Users?
Можете попробовать сделать так, где вместо #Users и #CALL ваши таблицы, на таблице #CALL есть индекс create clustered index cix on #CALL(GroupID, UserID, CallDate);
+
create clustered index cix on #CALL(GroupID, UserID, CallDate);

if object_id('tempdb..#Users') is not null drop table #Users;
create table #Users (UserID int primary key)
insert #Users select distinct UserID from #CALL;


select
	us.CoreN,
	c = count(*)
from
	#Users u
	cross apply (
		select 
			CoreN = case when sum(c.amount)<10 then sum(c.amount) else 10 end
		from
			#CALL c
		where
			c.GroupID = 1 and
			c.UserID = u.UserID and
			c.CallDate between 20150826 and 201500901
	) us
group by
	us.CoreN
option(hash group)

Сортировок не будет. Эффективность запроса зависит от того, насколько много пользователей и насколько много звонков для каждого пользователя. также в реальном примере надо попробовать закомментировать option(hash group), чтобы посмотреть, выберет ли оптимизатор план с Hash Match без хинта. Это зависит от числа групп которые он оценит, что зависит от данных.

Оптимизатору может быть трудно оценить число строк, т.к. оценивать надо по выражению "CoreN = case when ...". И если оценка сильно разойдется с реальностью, то это может вызватьп роблемы с производительностью. В таком случае, можно поместить промежуточный результат во временную таблицу.
+
if object_id('tempdb..#cores') is not null drop table #cores;

select
	CoreN = case when sum(c.amount)<10 then sum(c.amount) else 10 end
into #cores
from
	#Users u
	join #CALL c on c.UserID = u.UserID
where
	c.GroupID = 1 and
	c.CallDate between 20150826 and 201500901
group by
	u.UserID

create clustered index cix on #cores(CoreN);

select CoreN, count_big(*) from #cores group by CoreN;


Сортировка будет в момент создания индекса, но эта сортирвока лучше, т.к. 1) в момент создания индекса число строк точно известно и гадать не нужно, 2) сортировка при создании индекса умеет до-запрашивать память, если ей не хватило и не делать слив в tempdb.

Больше нигде ни сортировок, ни хинтов, ни хэшировния нет.

В общем, это мысли вслух и вараинты наугад, можете их попробовать, если будет медленно - то нужно анализировать - почему так и анализ лучше всего начать с плана выполнения, времени выполнения, статистики чтений выполнения.
1 сен 15, 16:07    [18096097]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для гурманов  [new]
kirser
Member

Откуда: Киев
Сообщений: 225
SomewhereSomehow,

Отличный вариант.
Но он тоже не принес ощутимых улучшений. Статистика чтений выполнения даже 1% прироста не дала.
Пробовал и с хинтом и без.

Вариант с промежуточной #tbl не пробовал, т.к. "активностей" за выбранный период может быть десятки миллионов. Заранее прогнозируемо, что эти записи будут заливаться в табличку долго(+ индексирование).

В любом случае - спасибо
1 сен 15, 17:54    [18096675]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для гурманов  [new]
aleks2
Guest
kirser
1) пробовали кластерный по GroupID, CallDate, UserID
Идеи на счет индексации тоже приветствуются.

Дык, не умеешь ты готовить индексы.
надо
GroupID, UserID, CallDate, INCLUDE (Amount)
1 сен 15, 18:33    [18096717]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для гурманов  [new]
aleks2
Guest
А запросы так позорно писать - я полчаса не мог въехать...

SELECT 
	t.Amount as CoreN, 
	count(*) as AmountN 
FROM 
			(SELECT  CASE when SUM(Amount) >= 10 then 10 else SUM(Amount) END as Amount FROM #CALL with(readpast)
			   WHERE GroupID = 1 and CallDate between 20150826 and 201500901
			   GROUP BY UserID
			) t
GROUP BY t.Amount
1 сен 15, 18:52    [18096783]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для гурманов  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
kirser
Но он тоже не принес ощутимых улучшений. Статистика чтений выполнения даже 1% прироста не дала.

Нужно план смотреть, ту же самую статистику чтений и время, ожидания. Иначе, как я говорил выше это просто мысли вслух, т.к. вопрос абстрактный.

kirser
Вариант с промежуточной #tbl не пробовал, т.к. "активностей" за выбранный период может быть десятки миллионов. Заранее прогнозируемо, что эти записи будут заливаться в табличку долго(+ индексирование).

Активностей - это понятно. Но вы же группируете по пользователям, что у вас пользователей десятки миллионов? Обычно это не так.

Можете попробовать оба варианта и выложить сюда планы.

Если в планах все ок, то нужно искать где-то ниже, не планом единым, как говорится: блокировки, память, диск и прочее.
Начать можно с анализа ожиданий.
1 сен 15, 19:41    [18096928]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить