Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Агрегация / 15 сообщений из 15, страница 1 из 1
13.11.2018, 21:32
    #39732718
_human
_human
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегация
приветствую

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

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

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

семпл
 

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
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


решения
 

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
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



 

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
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;


...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.11.2018, 12:45
    #39732946
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегация
_human,

200 мб хоть курсором можно обработать, это вообще не объём.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.11.2018, 17:35
    #39733134
Glebanski
Glebanski
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегация
_human,

Кстати, курсор не так ужасен, как кажется. Best approaches for running totals
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.11.2018, 19:19
    #39733201
PizzaPizza
PizzaPizza
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегация
_humanБез индексов используется параллелизм и дата спильі в сортировке
С индексами получается что датаспильі я делаю в ручную, но планьі вьіполнения проще и лучше


Что такое эти "дата спильі" они же "датаспильі"?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.11.2018, 19:33
    #39733208
Владимир Затуливетер
Владимир Затуливетер
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегация
Есть вариант завести таблицу в которой хранить только последние значения.
т.е. при каждом инсерте нужно будет обновлять эту новую таблицу.
но не думаю что это вам подойдет, это бы не плохо работало если бы дубликатов было бы очень много.

А вариант почистить этот мусор (дубликаты) не рассматривается? :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.11.2018, 14:07
    #39733521
_human
_human
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегация
PizzaPizza,

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

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

Владимир ЗатуливетерА вариант почистить этот мусор (дубликаты) не рассматривается? :)
Нет)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.11.2018, 14:24
    #39733535
_human
_human
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегация
Владислав Колосов,
Glebanski,

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

 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
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

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.11.2018, 14:50
    #39733559
Glebanski
Glebanski
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегация
_human,

Ну я прям не знаю, вы уже все 100500 вариантов перепробовали. Разве что еще BINARY_CHECKSUM/CHECKSUM посчитать для всех колонок и его использовать для детекта уникальности вместо сравнения каждого поля. Но это скорее для успокоения совести :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.11.2018, 17:24
    #39733701
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегация
_humanможет бьіть что я что-то не так написал в курсореУ вас курсора нет. Есть цикл.

Без "правильного" индекса на таблице, в любом случае потребуется сортировка или хеш-агрегирование.
Перекладывание во временную таблицу и создание индекса бессмысленно, т.к. потребует аналогичную сортировку.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.11.2018, 17:40
    #39733716
_human
_human
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегация
invm,

invmУ вас курсора нет. Есть цикл.
Есть курсор 21735212
и есть цикл тоже есть в 1-м посте
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.11.2018, 17:50
    #39733726
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегация
_human,

любопытно - почему в варианте с курсором в пять раз больше строк?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.11.2018, 17:56
    #39733729
_human
_human
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегация
Владислав Колосов,

Он возвращает правильное кол-во строк как и все остальньіе
Дело в RowCount - он суммирует все кол-ва строк во всех SELECT-ах
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.11.2018, 18:06
    #39733733
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегация
_humanЕсть курсорДа, проглядел.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.11.2018, 18:44
    #39733762
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегация
_human,

как-то долго курсор работает, у меня за 33 секунды выбирает на довольно древней персоналке. Курсор лучше завернуть в транзакцию, быстрее будет.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.11.2018, 19:19
    #39733784
_human
_human
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегация
Владислав Колосов,

не сильно помогло
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Агрегация / 15 сообщений из 15, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]