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

Откуда:
Сообщений: 556
приветствую

есть некий набор данньіх чуть больше 200МБ в котором около 20% "дубликатов".
нужно вьібрать только уникальньіе записи
для повторяющихся записи с максимальной датой udate.

Без индексов используется параллелизм и дата спильі в сортировке
С индексами получается что датаспильі я делаю в ручную, но планьі вьіполнения проще и лучше
Собсна результатьі +- одинаковьіе во всех решениях

Может у кого есть идеи по-лучше

семпл
+

use tempdb;
go

drop table if exists dbo.sample;

create table dbo.sample
(
	 i int identity(1,1)
	,id int
	,sdate datetime
	,udate as dateadd(ss, i, sdate)
	,attrib1 uniqueidentifier
	,attrib2 uniqueidentifier
	,attrib3 uniqueidentifier
	,attrib4 uniqueidentifier
	,attrib5 uniqueidentifier
	,attrib6 uniqueidentifier
	,attrib7 uniqueidentifier
)

declare @i int, @j int;
declare @n int;
declare @sdate datetime;
declare @attrib1 uniqueidentifier
	,@attrib2 uniqueidentifier
	,@attrib3 uniqueidentifier
	,@attrib4 uniqueidentifier
	,@attrib5 uniqueidentifier
	,@attrib6 uniqueidentifier
	,@attrib7 uniqueidentifier

set @i = 1;
set @j = 1;

while @i < power(10, 5)
begin
	while (@j < round(10*rand(), 0))
	begin
		set @n = round(10*rand(), 0);
		set @sdate = dateadd(day, round(10000*rand(), 0),'19000101') ;
		set @attrib1 = newid();
		set @attrib2 = newid();
		set @attrib3 = newid();
		set @attrib4 = newid();
		set @attrib5 = newid();
		set @attrib6 = newid();
		set @attrib7 = newid();
		
		insert dbo.sample(id, sdate, attrib1, attrib2, attrib3, attrib4, attrib5, attrib6, attrib7)
		-- generate changing part
		select 
			top (convert(int, round(10*rand(), 0)))
			@i,
			@sdate,
			--computed date
			newid() as attrib1,
			newid() as attrib2,
			newid() as attrib3,
			newid() as attrib4,
			newid() as attrib5,
			newid() as attrib6,
			newid() as attrib7 
		from master..spt_values
		union all
		-- generate duplicates
		select 
			top (convert(int, round(10.*rand(), 0)/2))
			@i,
			dateadd(hh, 1, @sdate),
			--computed date
			@attrib1,
			@attrib2,
			@attrib3,
			@attrib4,
			@attrib5,
			@attrib6,
			@attrib7
		from master..spt_values
		set @j = @j+1;
		
	end

	set @i = @i+1;
	set @j = 1
end


select count(*) 
	,100.*count(distinct attrib1)/count(*)
from dbo.sample

решения
+

use tempdb
go

-- row_number solution
drop procedure if exists dbo.rn;
go
create procedure dbo.rn as
begin
	;with CTE as
	(
	
		select 
			row_number() over(partition by 
			  id
			, sdate
			, attrib1
			, attrib2
			, attrib3
			, attrib4
			, attrib5
			, attrib6
			, attrib7
			order by udate desc) as rn
			, id
			, sdate
			, attrib1
			, attrib2
			, attrib3
			, attrib4
			, attrib5
			, attrib6
			, attrib7
		from dbo.sample
	
	)
	
	select *
	from cte 
	where rn = 1
end
go

-- max solution
drop procedure if exists dbo.max
go

create procedure dbo.max as
begin
	select
			  id
			, sdate
			, max(udate)
			, attrib1
			, attrib2
			, attrib3
			, attrib4
			, attrib5
			, attrib6
			, attrib7
	from dbo.sample
	group by  id
			, sdate
			, attrib1
			, attrib2
			, attrib3
			, attrib4
			, attrib5
			, attrib6
			, attrib7

end
go

-- cursor solution
drop procedure if exists dbo.loop;
go

create procedure dbo.loop as
begin
	set nocount on;
	drop table if exists #ids;

	select distinct id
	into #ids
	from dbo.sample

	 create unique index uix_#ids on #ids (id);

	declare @id int;

	drop table if exists #result;

	select id, sdate, udate, attrib1, attrib2, attrib3, attrib4, attrib5, attrib6, attrib7
	into #result
	from dbo.sample
	where 1=0;

	while exists (select top 1 1 from #ids order by id)
	begin

		set @id = (select top 1 id from #ids)
	
		insert #result
		select id, sdate, max(udate), attrib1, attrib2, attrib3, attrib4, attrib5, attrib6, attrib7
		from dbo.sample 
		where id = @id
		group by id, sdate, attrib1, attrib2, attrib3, attrib4, attrib5, attrib6, attrib7;	

		delete #ids
		where id = @id
	end

	select *
	from #result
end
go
-- lead solution
drop procedure if exists dbo.lead
go

create procedure dbo.lead as
begin
	;with lead_cte as (
		select 
			  id
			, sdate
			, udate
			, attrib1
			, attrib2
			, attrib3
			, attrib4
			, attrib5
			, attrib6
			, attrib7
			, lead(id)      over(partition by id, sdate order by udate) as pid
			, lead(attrib1) over(partition by id, sdate order by udate) as pattrib1
			, lead(attrib2) over(partition by id, sdate order by udate) as pattrib2
			, lead(attrib3) over(partition by id, sdate order by udate) as pattrib3
			, lead(attrib4) over(partition by id, sdate order by udate) as pattrib4
			, lead(attrib5) over(partition by id, sdate order by udate) as pattrib5
			, lead(attrib6) over(partition by id, sdate order by udate) as pattrib6
			, lead(attrib7) over(partition by id, sdate order by udate) as pattrib7
		from dbo.sample
	)

	select
			id
		, sdate
		, udate
		, attrib1
		, attrib2
		, attrib3
		, attrib4
		, attrib5
		, attrib6
		, attrib7
	from lead_cte
	where
		attrib1 <> pattrib1 or
		attrib2 <> pattrib2 or
		attrib3 <> pattrib3 or
		attrib4 <> pattrib4 or
		attrib5 <> pattrib5 or
		attrib6 <> pattrib6 or
		attrib7 <> pattrib7 
		or pid is null
end	
go

-- cross apply solution
drop procedure if exists dbo.ca;
go

create procedure dbo.ca as
begin
	set nocount on;
	drop table if exists #ids;

	select id
	into #ids
	from dbo.sample
	group by id

	create unique index uix_#ids on #ids (id);
	
	select i.id, c.sdate, c.udate, c.attrib1, c.attrib2, c.attrib3, c.attrib4, c.attrib5, c.attrib6, c.attrib7
	from #ids i
	cross apply 
		(
			select s.id, s.sdate, max(s.udate) as udate, s.attrib1, s.attrib2, s.attrib3, s.attrib4, s.attrib5, s.attrib6, s.attrib7
			from dbo.sample s
			where i.id = s.id
			group by s.id, s.sdate, s.attrib1, s.attrib2, s.attrib3, s.attrib4, s.attrib5, s.attrib6, s.attrib7
		) as c

end
go


+

use tempdb
go
	-- no index
	drop index if exists idx_sample_all on dbo.sample ;
	go

	DBCC DROPCLEANBUFFERS; 
	go
	set statistics time, io on;
	exec [dbo].[rn]   with recompile;
	set statistics time, io off;

	DBCC DROPCLEANBUFFERS; 
	go
	set statistics time, io on;
	exec [dbo].[max]	 with recompile;
	set statistics time, io off;

	DBCC DROPCLEANBUFFERS; 
	go
	set statistics time, io on;
	exec [dbo].[lead] with recompile;
	set statistics time, io off;
	
	-- index
	
	drop index if exists idx_sample_all on dbo.sample ;
	go
	create index idx_sample_all on dbo.sample (id, sdate, attrib1, attrib2, attrib3, attrib4, attrib5, attrib6, attrib7, udate desc);
	go

	DBCC DROPCLEANBUFFERS; 
	go

	set statistics time, io on;
	exec [dbo].[rn]   with recompile;
	set statistics time, io off;

	DBCC DROPCLEANBUFFERS; 
	go
	set statistics time, io on;
	exec [dbo].[max]	 with recompile;
	set statistics time, io off;

	DBCC DROPCLEANBUFFERS; 
	go
	set statistics time, io on;
	exec dbo.loop with recompile;
	set statistics time, io off;

	DBCC DROPCLEANBUFFERS; 
	go
	set statistics time, io on;
	exec dbo.ca with recompile;
	set statistics time, io off;

	
	drop index if exists idx_sample_all on dbo.sample ;
	go
	create index idx_sample_all on dbo.sample (id, sdate, udate) include (attrib1, attrib2, attrib3, attrib4, attrib5, attrib6, attrib7);
	go

	DBCC DROPCLEANBUFFERS; 
	go
	set statistics time, io on;
	exec dbo.lead with recompile;
	set statistics time, io off;

13 ноя 18, 21:32    [21733596]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7399
_human,

200 мб хоть курсором можно обработать, это вообще не объём.
14 ноя 18, 12:45    [21734057]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация  [new]
Glebanski
Member

Откуда: Msk ->NL
Сообщений: 306
_human,

Кстати, курсор не так ужасен, как кажется. Best approaches for running totals
14 ноя 18, 17:35    [21734449]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация  [new]
PizzaPizza
Member

Откуда:
Сообщений: 309
_human
Без индексов используется параллелизм и дата спильі в сортировке
С индексами получается что датаспильі я делаю в ручную, но планьі вьіполнения проще и лучше


Что такое эти "дата спильі" они же "датаспильі"?
14 ноя 18, 19:19    [21734556]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация  [new]
Владимир Затуливетер
Member

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

А вариант почистить этот мусор (дубликаты) не рассматривается? :)
14 ноя 18, 19:33    [21734565]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация  [new]
_human
Member

Откуда:
Сообщений: 556
PizzaPizza,

[url=Что такое эти "дата спильі" они же "датаспильі"?]https://docs.microsoft.com/ru-ru/sql/relational-databases/event-classes/exchange-spill-event-class?view=sql-server-2017[/url]

Владимир Затуливетер
Есть вариант завести таблицу в которой хранить только последние значения.

Такая таблица есть.
Но есть другая в которой история с дубликатами.
Там есть еще промежуточньіе значения по sdate которбьіе нужньі.

Владимир Затуливетер
А вариант почистить этот мусор (дубликаты) не рассматривается? :)

Нет)
15 ноя 18, 14:07    [21735194]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация  [new]
_human
Member

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

вполне может бьіть что я что-то не так написал в курсоре т.к. не часто приходиться писать, но он показьівает наихудший результат

+
drop procedure if exists dbo.cur;
go

create procedure dbo.cur as 
begin
	set nocount on;
	declare 
		 @id int
		,@sdate datetime
		,@udate datetime
		,@attrib1 uniqueidentifier
		,@attrib2 uniqueidentifier
		,@attrib3 uniqueidentifier
		,@attrib4 uniqueidentifier
		,@attrib5 uniqueidentifier
		,@attrib6 uniqueidentifier
		,@attrib7 uniqueidentifier

		,@nid int
		,@nsdate datetime
		,@nudate datetime
		,@nattrib1 uniqueidentifier
		,@nattrib2 uniqueidentifier
		,@nattrib3 uniqueidentifier
		,@nattrib4 uniqueidentifier
		,@nattrib5 uniqueidentifier
		,@nattrib6 uniqueidentifier
		,@nattrib7 uniqueidentifier


	declare @result table 
	(	 id int
		,sdate datetime
		,udate datetime
		,attrib1 uniqueidentifier
		,attrib2 uniqueidentifier
		,attrib3 uniqueidentifier
		,attrib4 uniqueidentifier
		,attrib5 uniqueidentifier
		,attrib6 uniqueidentifier
		,attrib7 uniqueidentifier)

	declare cur cursor local static forward_only read_only 
	for
		select id, sdate, udate, attrib1, attrib2, attrib3, attrib4, attrib5, attrib6, attrib7
		from dbo.sample
		order by id desc, sdate desc, udate desc;

	open cur;
	fetch next from cur into
		@id, @sdate, @udate, @attrib1, @attrib2, @attrib3, @attrib4, @attrib5, @attrib6, @attrib7;

	insert @result
	select @id, @sdate, @udate, @attrib1, @attrib2, @attrib3, @attrib4, @attrib5, @attrib6, @attrib7;

	while @@FETCH_STATUS = 0
	begin

		fetch next from cur into
			@nid, @nsdate, @nudate, @nattrib1, @nattrib2, @nattrib3, @nattrib4, @nattrib5, @nattrib6, @nattrib7;

		if(		@id		<>	@nid		
			or @sdate	<>	@nsdate	
			or @attrib1	<>	@nattrib1
			or @attrib2	<>	@nattrib2
			or @attrib3	<>	@nattrib3
			or @attrib4	<>	@nattrib4
			or @attrib5	<>	@nattrib5
			or @attrib6	<>	@nattrib6
			or @attrib7	<>	@nattrib7)

			insert @result
			select @nid, @nsdate, @nudate, @nattrib1, @nattrib2, @nattrib3, @nattrib4, @nattrib5, @nattrib6, @nattrib7;

		select 	
				@id		=	@nid	
			,@sdate		=	@nsdate	
			,@udate		=	@nudate	
			,@attrib1	=	@nattrib1
			,@attrib2	=	@nattrib2
			,@attrib3	=	@nattrib3
			,@attrib4	=	@nattrib4
			,@attrib5	=	@nattrib5
			,@attrib6	=	@nattrib6
			,@attrib7	=	@nattrib7

	end

	close cur;
	deallocate cur;

	select * from @result

end


К сообщению приложен файл. Размер - 26Kb
15 ноя 18, 14:24    [21735212]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация  [new]
Glebanski
Member

Откуда: Msk ->NL
Сообщений: 306
_human,

Ну я прям не знаю, вы уже все 100500 вариантов перепробовали. Разве что еще BINARY_CHECKSUM/CHECKSUM посчитать для всех колонок и его использовать для детекта уникальности вместо сравнения каждого поля. Но это скорее для успокоения совести :)
15 ноя 18, 14:50    [21735258]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация  [new]
invm
Member

Откуда: Москва
Сообщений: 9122
_human
может бьіть что я что-то не так написал в курсоре
У вас курсора нет. Есть цикл.

Без "правильного" индекса на таблице, в любом случае потребуется сортировка или хеш-агрегирование.
Перекладывание во временную таблицу и создание индекса бессмысленно, т.к. потребует аналогичную сортировку.
15 ноя 18, 17:24    [21735465]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация  [new]
_human
Member

Откуда:
Сообщений: 556
invm,

invm
У вас курсора нет. Есть цикл.

Есть курсор 21735212
и есть цикл тоже есть в 1-м посте
15 ноя 18, 17:40    [21735489]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7399
_human,

любопытно - почему в варианте с курсором в пять раз больше строк?
15 ноя 18, 17:50    [21735508]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация  [new]
_human
Member

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

Он возвращает правильное кол-во строк как и все остальньіе
Дело в RowCount - он суммирует все кол-ва строк во всех SELECT-ах
15 ноя 18, 17:56    [21735512]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация  [new]
invm
Member

Откуда: Москва
Сообщений: 9122
_human
Есть курсор
Да, проглядел.
15 ноя 18, 18:06    [21735522]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7399
_human,

как-то долго курсор работает, у меня за 33 секунды выбирает на довольно древней персоналке. Курсор лучше завернуть в транзакцию, быстрее будет.
15 ноя 18, 18:44    [21735571]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация  [new]
_human
Member

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

не сильно помогло

К сообщению приложен файл. Размер - 28Kb
15 ноя 18, 19:19    [21735620]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить