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

Откуда:
Сообщений: 27
Имеем две таблицы @t1, @t2.
Набор значений @t1.id1 является подмножеством набора значений из @t1.id2.
Соединяем таблицы по следующему принципу. В случае отсутствия в @t2 индекса соответствующего индексу @t1.id1, необходимо поля таблицы @t2 вместо NULL заполнять значениями из строки с наибольшим индексом @t2.id2 но меньшим относительно текущего индекса @t1.id1.

declare table (id1 int, c11 int);
declare @t2 table (id2 int, c21 int);
insert into @t1 values (1,1);
insert into @t1 values (2,2);
insert into @t1 values (3,3);
insert into @t1 values (4,4);
insert into @t1 values (5,5);
insert into @t2 values (1,11);
insert into @t2 values (4,14);
insert into @t2 values (5,15);
-- объединим по ID

select  _t12.*, c21
from (
	select id1, c11, isnull(id2, (
		select max(_t2.id2)
		from @t2 _t2
		where _t2.id2 <= id1
		) -- isnull
	) id21 
	from @t1 left join @t2
	on id1 = id2
) _t12 join @t2 _t22
on _t12.id21 = _t22.id2
1. Как оптимизировать код? 2. Можно ли уйти от корреллированного подзапроса? 3. Вообще интересуют другие вариаты кода (пусть неоптимальные).
4. Как можно упростить код, в случае, если значение индекса в первой таблице идет без разрывов?
5 дек 09, 21:32    [8024594]     Ответить | Цитировать Сообщить модератору
 Re: помогите оптимизировать запрос на соединение "полного" и "частичного" наборов  [new]
iljy
Member

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

declare @t1 table (id1 int primary key, c11 int);
declare @t2 table (id2 int primary key, c21 int);
insert into @t1 values (1,1);
insert into @t1 values (2,2);
insert into @t1 values (3,3);
insert into @t1 values (4,4);
insert into @t1 values (5,5);
insert into @t2 values (1,11);
insert into @t2 values (4,14);
insert into @t2 values (5,15);
-- объединим по ID

select * from
@t1 t1 left join @t2 t2 on t2.id2 = (select MAX(id2) from @t2 where id2 <= id1)

select * from
@t1 t1 outer apply
(select top 1 * from @t2 where id2 <= id1) t2

5 дек 09, 21:43    [8024611]     Ответить | Цитировать Сообщить модератору
 Re: помогите оптимизировать запрос на соединение "полного" и "частичного" наборов  [new]
_proper_
Member

Откуда:
Сообщений: 27
iljy, спасибо. В продолжение темы, следующий вопрос.
declare @MoneyInOut table (ID int 
, dateInOut date
, sumInOut money
);
insert into @MoneyInOut values (1, '20090101', 1);
insert into @MoneyInOut values (1, '20090102', 1);
--insert into @MoneyInOut values (1, '20090103', 1);
insert into @MoneyInOut values (2, '20090104', 1);
insert into @MoneyInOut values (1, '20090105', -1);
insert into @MoneyInOut values (1, '20090106', 1);

declare @dateStart date = '20090101';
declare @dateEnd date = '20090106';

with cte as(
select @dateStart as d
union all
select dateadd(dd,1, d) from cte where d < @dateEnd
)

select *
from cte _t3 outer apply
(select * from
(	select _t1.ID, _t1.dateInOut, max(_t1.sumInOut) InOut, sum(isnull(_t2.sumInOut, 0)) Summ
	from @MoneyInOut _t1 left join @MoneyInOut _t2 -- where dateInOut = 
	on _t1.ID = _t2.ID and _t1.dateInOut >= _t2.dateInOut
	group by _t1.ID, _t1.dateInOut
) as _t where _t.dateInOut = 
    (select MAX(dateInOut) bbb from _t as _ttt where _t.dateInOut <= _t3.d)
) _jjj
order by d
Интеллисенс нормально подставил _t и _t3 в подзапросе, но при этом выдается ошибка: "Invalid object name 't'. Соответственно _ttt уже недоступен в интеллисесе, не получается построить нормально подзапрос. Как можно решить эту проблему?
6 дек 09, 07:10    [8024979]     Ответить | Цитировать Сообщить модератору
 Re: помогите оптимизировать запрос на соединение "полного" и "частичного" наборов  [new]
iljy
Member

Откуда:
Сообщений: 8711
_proper_

Интеллисенс нормально подставил _t и _t3 в подзапросе, но при этом выдается ошибка: "Invalid object name 't'. Соответственно _ttt уже недоступен в интеллисесе, не получается построить нормально подзапрос. Как можно решить эту проблему?

Естесственно недоступен, не умеет так скуль. А решить - проще надо быть ;)
declare @MoneyInOut table (ID int 
, dateInOut date
, sumInOut money
);
insert into @MoneyInOut values (1, '20090101', 1);
insert into @MoneyInOut values (1, '20090102', 1);
--insert into @MoneyInOut values (1, '20090103', 1);
insert into @MoneyInOut values (2, '20090104', 1);
insert into @MoneyInOut values (1, '20090105', -1);
insert into @MoneyInOut values (1, '20090106', 1);

declare @dateStart date = '20090101';
declare @dateEnd date = '20090106';

with cte as(
select @dateStart as d
union all
select dateadd(dd,1, d) from cte where d < @dateEnd
)
select * from cte t3 outer apply
(
	select top 1 t1.ID, t1.dateInOut, max(t1.sumInOut) InOut, sum(t2.sumInOut) Summ
	from @MoneyInOut t1 left join @MoneyInOut t2
	on t1.ID = t2.ID and t1.dateInOut >= t2.dateInOut
	where t1.dateInOut <= t3.d
	group by t1.ID, t1.dateInOut
	order by t1.dateInOut desc
) t
option(maxrecursion 0)
6 дек 09, 13:01    [8025169]     Ответить | Цитировать Сообщить модератору
 Re: помогите оптимизировать запрос на соединение "полного" и "частичного" наборов  [new]
_proper_
Member

Откуда:
Сообщений: 27
iljy, спасибо. Но есть еще вопрос. Как должен выглядет следующий фрагмет кода на весрии 2000 и ниже.
with cte as(
select @dateStart as d
union all
select dateadd(dd,1, d) from cte where d < @dateEnd
)
Я читал что нужно использовать вспомогательную таблицу с набором дат. Но не совсем понятно как такой набор наращивать. Есть подозрение, что запрос будет не очень красивым. Или вообще одним запросом не обойтись?
declare @Days table (ID int identity Primary Key
, dat date
);
insert into @Days values ('20090101');
insert into @Days values ('20090102');
insert into @Days values ('20090103');
insert into @Days values ('20090104');
insert into @Days values ('20090105'); -- максимальное значение в вспомогательной таблице

declare @dateStart date = '20090101';
declare @dateEnd date = '20090108';  -- число болше чем max(@Days.dat) 

select * from @Days where dat between @dateStart and @dateEnd -- ???
6 дек 09, 17:24    [8025533]     Ответить | Цитировать Сообщить модератору
 непрерывный диапазон дат  [new]
_proper_
Member

Откуда:
Сообщений: 27
ап
7 дек 09, 11:20    [8027145]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить