Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Джойн со случайной строкой таблицы  [new]
Random join
Guest
Задача: сделать выборку из таблицы A, приджойнив к каждой её строке случайную строку из таблицы B. Пробовал сделать так:
select a.id, a.name, b.id, b.name
from A a
cross join (
  select top 1 id, name from B order by rand(checksum(newid()))
)b(id, name)

- нужного эффекта нет: присоединяется случайная строка, но она одинакова для всей выборки. Если cross join заменить на cross apply, то ситуация остается той же. Попробовал нарисовать такой вариант:
;with cte as (
  select id, name, row_number() over(order by id) ord
  from B
)
select a.id, a.name, b.id, b.name
from A a
cross apply (
  select convert(int,rand(checksum(newid()))*(count(1)))+1 from B
) f(rnd)
cross apply (
  select e.id, e.name from cte e where e.ord=f.rnd
) b(id, name)

- но как-то тяжеловесно получается. Может, можно как-то проще?
24 сен 13, 11:56    [14876528]     Ответить | Цитировать Сообщить модератору
 Re: Джойн со случайной строкой таблицы  [new]
force order
Guest
наступите на горло оптимизатору
declare @a table (id int not null identity, name varchar(20))
declare @b table (id int not null identity, name varchar(20))


insert @a (name)
values ('a'), ('b')

insert @b (name)
values ('c'), ('d')




select 
	a.id
	, a.name
	, b.id
	, b.name
from @a a
cross apply (
	select top 1 id, name from @b b order by NEWID()
) b 
option (force order)
24 сен 13, 12:17    [14876691]     Ответить | Цитировать Сообщить модератору
 Re: Джойн со случайной строкой таблицы  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1887
declare @a table (id int)
declare @b table (id int)
insert into @a select 1 union select 2 union select 3 union select 4
insert into @b select 1 union select 2 union select 3 union select 4 union select 5  union select 6

select TOP 1 WITH TIES
 a.id as aid 
,b.id as bid
from @a a
cross join @b b
order by ROW_NUMBER() OVER(PARTITION BY a.id ORDER BY NEWID())
24 сен 13, 12:19    [14876706]     Ответить | Цитировать Сообщить модератору
 Re: Джойн со случайной строкой таблицы  [new]
Гость333
Member

Откуда:
Сообщений: 3683
force order
наступите на горло оптимизатору

Можно и не наступать, а немного схитрить:
select a.id, a.name, b.id, b.name
from A a
cross join (
  select top 1 id, name from B order by rand(checksum(newid())) , a.id
)b(id, name)
24 сен 13, 13:14    [14877082]     Ответить | Цитировать Сообщить модератору
 Re: Джойн со случайной строкой таблицы  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Гость333
Можно и не наступать, а немного схитрить:
select a.id, a.name, b.id, b.name
from A a
cross apply (
  select top 1 id, name from B order by rand(checksum(newid())) , a.id
)b(id, name)

Исправил неудачный копипаст из первого сообщения темы.
24 сен 13, 13:16    [14877098]     Ответить | Цитировать Сообщить модератору
 Re: Джойн со случайной строкой таблицы  [new]
Random join
Guest
Спасибо. Пока требуемый результат дал только вариант с TOP .. WITH TIES. Ни наступить на горло оптимизатору, ни обмануть его не в рабочем запросе не получается. На приложенной картинке верхний план - от рабочего запроса, нижний - от тестового примера. Чтобы план в тестовом примере более-менее соответствовал рабочему, я его модифицировал так:
declare @a table(id int, name varchar(3));
insert into @a
  select number*100, replicate(char(number+64),3)
  from master..spt_values
  where type='P' and number between 1 and 5;
declare @b table(id int, name varchar(3), flag int, unique(flag, name, id));
insert into @b
  select number, char(number+64), 10
  from master..spt_values
  where type='P' and number between 1 and 5;
select top 5 a.id, a.name, b.id, b.name
from @a a
cross apply (
  select top 1 id, name from @b
  where flag=10
  order by rand(checksum(newid())) , a.id
)b(id, name)

в результате в тестовом примере от cross apply приджойниваются рандомные строки, в рабочем - одна и та же.
Посмотрел по узлам плана - в рабочем плане узел TopNSort содержит инструкцию OrderBy: Expr1007 Ascending, [Database1].[Schema1].[Table1].[Column1] Ascending, в тестовом запросе тот же узел содержит Order By: Expr1007 Ascending.

К сообщению приложен файл. Размер - 31Kb
24 сен 13, 14:51    [14877707]     Ответить | Цитировать Сообщить модератору
 Re: Джойн со случайной строкой таблицы  [new]
force order
Guest
Random join
Спасибо. Пока требуемый результат дал только вариант с TOP .. WITH TIES. Ни наступить на горло оптимизатору, ни обмануть его не в рабочем запросе не получается. На приложенной картинке верхний план - от рабочего запроса, нижний - от тестового примера. Чтобы план в тестовом примере более-менее соответствовал рабочему, я его модифицировал так:
declare @a table(id int, name varchar(3));
insert into @a
  select number*100, replicate(char(number+64),3)
  from master..spt_values
  where type='P' and number between 1 and 5;
declare @b table(id int, name varchar(3), flag int, unique(flag, name, id));
insert into @b
  select number, char(number+64), 10
  from master..spt_values
  where type='P' and number between 1 and 5;
select top 5 a.id, a.name, b.id, b.name
from @a a
cross apply (
  select top 1 id, name from @b
  where flag=10
  order by rand(checksum(newid())) , a.id
)b(id, name)

в результате в тестовом примере от cross apply приджойниваются рандомные строки, в рабочем - одна и та же.
Посмотрел по узлам плана - в рабочем плане узел TopNSort содержит инструкцию OrderBy: Expr1007 Ascending, [Database1].[Schema1].[Table1].[Column1] Ascending, в тестовом запросе тот же узел содержит Order By: Expr1007 Ascending.


а с force order план каков?
24 сен 13, 15:16    [14877876]     Ответить | Цитировать Сообщить модератору
 Re: Джойн со случайной строкой таблицы  [new]
Random join
Guest
force order,

К сообщению приложен файл. Размер - 17Kb
24 сен 13, 15:20    [14877901]     Ответить | Цитировать Сообщить модератору
 Re: Джойн со случайной строкой таблицы  [new]
Random join
Guest
Сильно подозреваю, что дело в Table spool (lazy spool), но вот какими средствами можно от него избавиться?
24 сен 13, 15:23    [14877920]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить