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

Откуда:
Сообщений: 31
Есть две таблицы #Temp и Calendar
Надо получить третью
declare @d1 datetime = '20130101'
declare @d2 datetime = '20130103'

if not (SELECT OBJECT_ID('tempdb..#temp')) is null
	drop table #temp

select 
 1 as Fact
,@d1 Data
 into #temp
union 
select 
 2 as Fact
,@d2 Data

if not (SELECT OBJECT_ID('tempdb..#Calendar')) is null
	drop table #Calendar
	
	select @d1 as Data into #Calendar
	
	  while @d1<@d2
	   begin 
	    set @d1=@d1+1
	    insert into #Calendar select @d1
	   end


Вид целевой таблицы

Data          Fact 
2013-01-01 	1
2013-01-02 2
2013-01-03 2
28 окт 13, 14:11    [15041230]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ссылкой, просто туплю.  [new]
Гость333
Member

Откуда:
Сообщений: 3683
i-n-d-e-x,

Объясните подробнее принцип формирования третьей таблицы.
28 окт 13, 14:16    [15041260]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ссылкой, просто туплю.  [new]
i-n-d-e-x
Member

Откуда:
Сообщений: 31
Принцип формирования целевой таблицы:
- есть календарь с 1 по 3 января 2013 года
- есть таблица в которой описываются события произошедшие 1 января и 3 января 2013 года.
-- в целевой таблице должна быть информация о том, что:
1 января действует результат события, свершившегося 1 января
2 января действует результат события, свершившегося 1 января
3 января действует результат события, свершившегося 3 января
28 окт 13, 14:31    [15041393]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ссылкой, просто туплю.  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1887
select top 1 with ties c.Data ,  t.Fact
from #calendar c
left join #temp t on t.Data <=c.Data
order by ROW_NUMBER() over(partition by c.Data order by t.Data desc)
28 окт 13, 14:54    [15041525]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ссылкой, просто туплю.  [new]
i-n-d-e-x
Member

Откуда:
Сообщений: 31
LexusR, огромное спасибо.
Ваше решение работает идеально.
28 окт 13, 14:58    [15041561]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ссылкой, просто туплю.  [new]
Гость333
Member

Откуда:
Сообщений: 3683
i-n-d-e-x
работает идеально...

... с крошечными таблицами.
28 окт 13, 15:09    [15041620]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ссылкой, просто туплю.  [new]
i-n-d-e-x
Member

Откуда:
Сообщений: 31
А как с некрошечными?
28 окт 13, 15:25    [15041718]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ссылкой, просто туплю.  [new]
Гость333
Member

Откуда:
Сообщений: 3683
i-n-d-e-x,

Подготовка данных:
declare @d1 datetime = '20000101'
declare @d2 datetime = '20130103'

if not (SELECT OBJECT_ID('tempdb..#temp')) is null
	drop table #temp

select @d1 as Fact, floor(rand() * 10) as Data into #temp

	  while @d1<@d2
	   begin 
	    set @d1=@d1+4
	    insert into #temp select @d1, floor(rand() * 10) as Data
	   end

select @d1 = '20000101', @d2 = '20130103';

if not (SELECT OBJECT_ID('tempdb..#Calendar')) is null
	drop table #Calendar
	
	select @d1 as Data into #Calendar
	
	  while @d1<@d2
	   begin 
	    set @d1=@d1+1
	    insert into #Calendar select @d1
	   end

create index i on #temp(Data, Fact)

(Обратите внимание на созданный в конце индекс.)

Запрос от LexusR:
set statistics time on
set statistics io on

select top 1 with ties c.Data ,  t.Fact
from #calendar c
left join #temp t on t.Data <=c.Data
order by ROW_NUMBER() over(partition by c.Data order by t.Data desc)

(4752 row(s) affected)
Table '#temp______00000000000F'. Scan count 4, logical reads 19008, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Calendar__000000000010'. Scan count 5, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 15632 ms,  elapsed time = 20863 ms.


Более оптимальный запрос:
set statistics time on
set statistics io on

select c.Data, x.Fact
from #calendar c
     cross apply (select top 1 t.Fact from #temp t where t.Data <= c.Data) x


(4752 row(s) affected)
Table '#temp______00000000000F'. Scan count 1, logical reads 4752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Calendar__000000000010'. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 162 ms.

Разница в сто раз.

А всё потому, что в первом запросе выполняется "треугольное соединение" (если интересно, что это, то Google -> triangular join).
28 окт 13, 15:58    [15041904]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ссылкой, просто туплю.  [new]
i-n-d-e-x
Member

Откуда:
Сообщений: 31
Гость333, Огромное спасибо за науку. К своему стыду я совсем не знал ни о запросе "треугольное соединение" ни о cross apply.
Попробую.
28 окт 13, 16:08    [15041971]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ссылкой, просто туплю.  [new]
baclanov
Member

Откуда:
Сообщений: 82
[quot Гость333]i-n-d-e-x,

set statistics time on
set statistics io on

select c.Data, x.Fact
from #calendar c
     cross apply (select top 1 t.Fact from #temp t where t.Data <= c.Data) x



Забыли отсортировать:

select c.Data, x.Data
from #calendar c
     cross apply (select top 1 t.Data from #temp t where t.Fact <= c.Data Order by t.Fact desc) x

(4752 row(s) affected)
Таблица "Worktable". Число просмотров 4752, логических чтений 18413, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "#temp_00000000016A". Число просмотров 1, логических чтений 4, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "#Calendar_00000000016B". Число просмотров 1, логических чтений 11, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.

 Время работы SQL Server:
   Время ЦП = 1529 мс, затраченное время = 2019 мс.
28 окт 13, 17:46    [15042549]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ссылкой, просто туплю.  [new]
Добрый Э - Эх
Guest
Если я все правильно понял, то банальный LEFT JOIN с последующей "протяжкой" события...
28 окт 13, 17:51    [15042575]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ссылкой, просто туплю.  [new]
Добрый Э - Эх
Guest
оу, про ссылку-то совсем забыл... :)
28 окт 13, 17:52    [15042587]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ссылкой, просто туплю.  [new]
Гость333
Member

Откуда:
Сообщений: 3683
baclanov
Забыли отсортировать:

Действительно, забыл. Моя ошибка, поторопился.

Кроме того, накосячил с созданием таблицы #temp: в столбец Fact записал даты, а в столбец Data — факты.
Если оставить такую таблицу, то нужен другой индекс:
create index i2 on #temp(Fact, Data)

Тогда время выполнения будет:
set statistics time on
set statistics io on

select c.Data, x.Data
from #calendar c
     cross apply (select top 1 t.Data from #temp t where t.Fact <= c.Data Order by t.Fact desc) x

(4752 row(s) affected)
Table '#temp______00000000001A'. Scan count 4752, logical reads 9504, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Calendar__00000000001B'. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 31 ms,  elapsed time = 165 ms.
28 окт 13, 18:52    [15042822]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ссылкой, просто туплю.  [new]
baclanov
Member

Откуда:
Сообщений: 82
Гость333,

спасибо, действительно, правильный индекс помог:

create index i2 on #temp(Fact, Data)


(4752 row(s) affected)
Таблица "#temp_0000000001A4". Число просмотров 4752, логических чтений 9504, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "#Calendar_0000000001A5". Число просмотров 1, логических чтений 11, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.

Время работы SQL Server:
Время ЦП = 16 мс, затраченное время = 194 мс.
29 окт 13, 10:06    [15044802]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить