ReSQL.ru
     
powered by simpleCommunicator - 2.0.50     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / JOIN со случайной строкой таблицы
23 сообщений из 23, страница 1 из 1
JOIN со случайной строкой таблицы
    #40105489
Шамиль Фаридович
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Шамиль Фаридович
Участник
Необходимо соединить одну таблицу со случайной строкой из другой таблицы
Конструкция вида
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
select top 20
    t1.Id, 
    (
        SELECT TOP 1 t2.id
        FROM dbo.table2 t2
        where t1_2.EntityTypeid = 1 -- lp
        ORDER BY NEWID()
    ) as rnd_table2_id,
    tmp.rnd_table2_id_2
from dbo.table1 t1
cross apply
(
        SELECT TOP 1 t2.id as rnd_table2_id_2
        FROM dbo.table2 t2
        where t2.EntityTypeid = 1 -- lp
        ORDER BY NEWID()
    ) as tmp


дает 2 случайных значения для rnd_table2_id и rnd_table2_id_2,
одинаковое для всех 20 строк. А мне нужно случайно значение для каждой строки
...
Рейтинг: 0 / 0
20.10.2021, 03:01
| Ответить | Цитировать | Написать  
JOIN со случайной строкой таблицы
    #40105490
Massa52
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Massa52
Участник
Шамиль Фаридович,
Вроде как цикл напрашивается.
...
Рейтинг: 0 / 0
20.10.2021, 03:17
| Ответить | Цитировать | Написать  
JOIN со случайной строкой таблицы
    #40105496
Guf
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Guf
Участник
Шамиль Фаридович,

Зависит от того, в какой таблице строк больше.
Код: 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.
-----Если в table1 строк мало, в table2 много, то:
with t
as (
    select  rn = row_number() over (order by newid())
          , t1.*
        from master.dbo.spt_values t1
        where t1.type = N'O9T'
   )
, rnd
as (
    select  rn = row_number() over (order by newid())
          , t2.*
        from master.dbo.spt_values t2
        where t2.type = N'P'
    )
select *
    from t
         inner join rnd on rnd.rn = t.rn
    order by t.rn
;

-----Если в table1 строк много, в table2 мало, то:
with t
as (
    select  rn = row_number() over (order by newid())
          , t2.*
        from master.dbo.spt_values t2
        where t2.type = N'P'
   )
, rnd
as (
    select  rn = row_number() over (order by newid())
          , t1.*
        from master.dbo.spt_values t1
        cross join master.dbo.spt_values v1
        where t1.type = N'O9T'
    )
select *
    from t
         inner join rnd on rnd.rn = t.rn
    order by t.rn
;


P.S. Хотя второй вариант можно использовать в любом случаее
...
Рейтинг: 0 / 0
20.10.2021, 07:25
| Ответить | Цитировать | Написать  
JOIN со случайной строкой таблицы
    #40105500
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
Код: sql
1.
2.
3.
4.
with t1 as ( select top(20) *, n = row_number() over(order by 1/0) from dbo.table1 )
   , t2 as ( select top(20) *, n = row_number() over(order by 1/0) from dbo.table2 where EntityTypeid = 1 ORDER BY NEWID() )
select *
  from t1 inner join t2 on t1.n = t2.n
...
Рейтинг: 0 / 0
20.10.2021, 08:17
| Ответить | Цитировать | Написать  
JOIN со случайной строкой таблицы
    #40105574
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
aleks222 , Guf , ИМХО неверно.
Должно быть что-то типа:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
Create table #t1 (id int identity (1,1) Not Null Primary key clustered);
Create table #t2 (id int identity (1,1) Not Null Primary key clustered);
Go
Insert into #t1 default values
go 100
Insert into #t2 default values
go 10

; With s1 as (Select *, N = NEWID() from #t1)
, s2 as (Select *, N = NEWID() from #t2)
Select top(1) with ties
	s1.id, s2.id
	from s1 cross join s2
Order by ROW_NUMBER() over (partition by s1.id order by s1.N, s2.N)
...
Рейтинг: 0 / 0
20.10.2021, 13:25
| Ответить | Цитировать | Написать  
JOIN со случайной строкой таблицы
    #40105586
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
Блин, туплю. Это одно и то же...
...
Рейтинг: 0 / 0
20.10.2021, 13:43
| Ответить | Цитировать | Написать  
JOIN со случайной строкой таблицы
    #40105590
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
Короче говоря, почему не годятся скрипты ни Guf, aleks222.

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

Создан он должен быть следующим образом: Из полного соединения первой таблицы и второй (генсовокупность) нужно случайным образом отбирать 1 строку для каждого ИД из первой таблицы.

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
Create table #t1 (id int identity (1,1) Not Null Primary key clustered);
Create table #t2 (id int identity (1,1) Not Null Primary key clustered);
Go
Insert into #t1 default values
go 100
Insert into #t2 default values
go 10

Select top(1) with ties
	s1.id, s2.id
	from #t1 s1 cross join #t2 s2
Order by ROW_NUMBER() over (partition by s1.id order by NewID() )


Короче вот, по здравому размышлению.
...
Рейтинг: 0 / 0
20.10.2021, 14:01
| Ответить | Цитировать | Написать  
JOIN со случайной строкой таблицы
    #40105594
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
uaggster

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


Вы бредите.
...
Рейтинг: 0 / 0
20.10.2021, 14:09
| Ответить | Цитировать | Написать  
JOIN со случайной строкой таблицы
    #40105600
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
aleks222
uaggster

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


Вы бредите.

Неа. Это вы бредите :-)
...
Рейтинг: 0 / 0
20.10.2021, 14:22
| Ответить | Цитировать | Написать  
JOIN со случайной строкой таблицы
    #40105725
Шамиль Фаридович
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Шамиль Фаридович
Участник
uaggster

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

Именно так!

Поэтому метод aleks222
Код: sql
1.
2.
3.
4.
with t1 as ( select top(20) *, n = row_number() over(order by 1/0) from dbo.table1 )
   , t2 as ( select top(20) *, n = row_number() over(order by 1/0) from dbo.table2 where EntityTypeid = 1 ORDER BY NEWID() )
select *
  from t1 inner join t2 on t1.n = t2.n



работает только если в обеих таблицах ровно по 20 строк, и то, работать он начинает только после того, как второй cte поправить на
Код: sql
1.
t2 as ( select top(20) *, n = row_number() over(ORDER BY NEWID()) from dbo.table2 where EntityTypeid = 1 )



Оба метода Guf работают.
Но вариант uaggster кажется мне наиболее изящным.

Я правда не совсем соображу, как использовать его для UPDATE первой таблицы.
Сам же я знаю точно количество строк во второй таблице-справочнике table2 (118), поэтому использовал такую конструкцию:
Код: sql
1.
2.
3.
4.
5.
6.
with trg as ( select  *, rn = row_number() over(ORDER BY NEWID()) from dbo.table1 )
   , rnd as ( select  id, rn = row_number() over(ORDER BY NEWID()) from dbo.table2 where EntityTypeid = 1)
update trg
set table2_id = rnd.id
from trg 
inner join t2 on trg.rn (trg.rn % 118) + 1 = rnd.rn
...
Рейтинг: 0 / 0
21.10.2021, 03:48
| Ответить | Цитировать | Написать  
JOIN со случайной строкой таблицы
    #40105726
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
[quot Шамиль Фаридович#22386176]
uaggster


работает только если в обеих таблицах ровно по 20 строк, и то, работать он начинает только после того, как второй cte поправить на
Код: sql
1.
t2 as ( select top(20) *, n = row_number() over(ORDER BY NEWID()) from dbo.table2 where EntityTypeid = 1 )



Налицо абсолютное непонимание сути.

Шамиль Фаридович

Но вариант uaggster кажется мне наиболее изящным.

Видимо с точки зрения "подогреть процессор"?
...
Рейтинг: 0 / 0
21.10.2021, 06:56
| Ответить | Цитировать | Написать  
JOIN со случайной строкой таблицы
    #40105727
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
Если у те нужно "к каждой"

Код: sql
1.
2.
3.
4.
with t1 as ( select top(20) * from dbo.table1 )
   , t2 as ( select * from dbo.table2 where EntityTypeid = 1 )
select *
  from t1 cross apply( select top(1) * from t2 order by newid() ) as t2



Но подогрев процессора будет нехилый.
Хотя... 118 строк выдержит.
...
Рейтинг: 0 / 0
21.10.2021, 06:56
| Ответить | Цитировать | Написать  
JOIN со случайной строкой таблицы
    #40105728
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
дубль
...
Рейтинг: 0 / 0
21.10.2021, 07:02
| Ответить | Цитировать | Написать  
JOIN со случайной строкой таблицы
    #40105743
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
aleks222
Если у те нужно "к каждой"

Код: sql
1.
2.
3.
4.
with t1 as ( select top(20) * from dbo.table1 )
   , t2 as ( select * from dbo.table2 where EntityTypeid = 1 )
select *
  from t1 cross apply( select top(1) * from t2 order by newid() ) as t2



Но подогрев процессора будет нехилый.
Хотя... 118 строк выдержит.


Маленькая поправка...
Код: sql
1.
2.
3.
4.
with t1 as ( select top(20) * from dbo.table1 )
   , t2 as ( select * from dbo.table2 where EntityTypeid = 1 )
select *
  from t1 cross apply( select top(1) * from t2 where t1.id = t1.id order by newid() ) as t2
...
Рейтинг: 0 / 0
21.10.2021, 09:08
| Ответить | Цитировать | Написать  
JOIN со случайной строкой таблицы
    #40105748
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
 
Код: 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.
use tempdb;
set ansi_nulls, quoted_identifier, xact_abort on;
go

create table dbo.t1 (id int primary key, s varchar(50));
create table dbo.t2 (id int primary key, s varchar(50));
go

insert into dbo.t1
 (id, s)
 select top (1000000)
  row_number() over (order by 1/0), cast(newid() as varchar(50))
 from
  master.dbo.spt_values a cross join
  master.dbo.spt_values b;

insert into dbo.t2
 (id, s)
 select top (10000)
  row_number() over (order by 1/0), cast(newid() as varchar(50))
 from
  master.dbo.spt_values a cross join
  master.dbo.spt_values b;
go

set statistics xml on;

select
 a.id, a.s, c.id, c.s
from
 (select row_number() over (order by id) - 1, id, s from dbo.t1) a (n, id, s) cross apply
 (select count(*) from dbo.t2) b (cnt) join
 (select row_number() over (order by newid()) - 1, id, s from dbo.t2) c (n, id, s) on c.n = a.n % b.cnt

set statistics xml off;
go

drop table dbo.t1, dbo.t2;
go

...
Рейтинг: 0 / 0
21.10.2021, 09:18
| Ответить | Цитировать | Написать  
JOIN со случайной строкой таблицы
    #40105752
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
invm
Код: sql
1.
2.
3.
4.
from
 (select row_number() over (order by id) - 1, id, s from dbo.t1) a (n, id, s) cross apply
 (select count(*) from dbo.t2) b (cnt) join
 (select row_number() over (order by newid()) - 1, id, s from dbo.t2) c (n, id, s) on c.n = a.n % b.cnt


Формально - не канает.
Шамиль Фаридович
Необходимо соединить одну таблицу со случайной строкой из другой таблицы


"Случайной" - подразумевает ненулевую вероятность повтора.
Т.е. две разные строки т1 могут присоединить одинаковые строки из т2.

ЗЫ. Ну тредстартеру простительно, он думать не обучен.
...
Рейтинг: 0 / 0
21.10.2021, 09:38
| Ответить | Цитировать | Написать  
JOIN со случайной строкой таблицы
    #40105793
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Надо выбирать случайные ключи, а не строки.
...
Рейтинг: 0 / 0
21.10.2021, 12:22
| Ответить | Цитировать | Написать  
JOIN со случайной строкой таблицы
    #40105815
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
Владислав Колосов
Надо выбирать случайные ключи, а не строки.

И в чем будет разница?
...
Рейтинг: 0 / 0
21.10.2021, 13:52
| Ответить | Цитировать | Написать  
JOIN со случайной строкой таблицы
    #40105872
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
Ну... чтобы не греть процессор, как я понимаю, нужно сгенерировать последовательность 1..N (псевдо)случайных чисел в диапазоне 1..M, где N - это Select count(*) from Первая_таблица, а M - это Select count(*) from Вторая_таблица.
Затем пронумеровать выборку из первой таблицы ROW_NUMBER() over (ORDER by 1/0), выборку из второй таблицы - аналогично, и соединить две выборки в соответствие с ранее полученной последовательностью.

Собственно, вопрос в генерации последовательности.
...
Рейтинг: 0 / 0
21.10.2021, 15:37
| Ответить | Цитировать | Написать  
JOIN со случайной строкой таблицы
    #40105910
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
И, собственно, сделать так - можно.

Но это столь ужасно, что лучше уж cross join
Код: 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.
use tempdb;
set ansi_nulls, quoted_identifier, xact_abort on;
go

create table dbo.t1 (id int primary key, s varchar(50));
create table dbo.t2 (id int primary key, s varchar(50));
go

insert into dbo.t1
 (id, s)
 select top (1000)
  row_number() over (order by 1/0), cast(newid() as varchar(50))
 from
  master.dbo.spt_values a cross join
  master.dbo.spt_values b;

insert into dbo.t2
 (id, s)
 select top (100)
  row_number() over (order by 1/0), cast(newid() as varchar(50))
 from
  master.dbo.spt_values a cross join
  master.dbo.spt_values b;
go


;With
N(N) as (Select count(*) from dbo.t2),
M(M) as (Select count(*) from dbo.t1),
s as (
 Select ABS(CHECKSUM(NewId())) % (Select N.N from N) + 1 R, 1 N
 Union all
 Select ABS(CHECKSUM(NewId())) % (Select N.N from N ) + 1 R, s.N + 1
	from s
	Where S.N < (Select M.M from M)
),
tt1 as (Select *, ROW_NUMBER() over (order by 1/0) N from t1),
tt2 as (select *, ROW_NUMBER() over (order by 1/0) R from t2)
Select tt1.id, tt1.s, tt2.id, tt2.s from s
	inner join tt1 on s.N = tt1.N
	inner join tt2 on s.R = tt2.R
Order by tt1.N
Option (maxrecursion 0)

GO

drop table dbo.t1
drop table dbo.t2



Хотя, возможно, есть вариант заменить рекурсию... чем нибудь.
...
Рейтинг: 0 / 0
21.10.2021, 16:25
| Ответить | Цитировать | Написать  
JOIN со случайной строкой таблицы
    #40105938
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
uaggster
что лучше уж cross join


Cross join плох тем, что вы выполняете абсолютно ненужную генерацию херовой тучи строк.
Из которых вам нужны только 20.
...
Рейтинг: 0 / 0
21.10.2021, 17:14
| Ответить | Цитировать | Написать  
JOIN со случайной строкой таблицы
    #40105979
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
Всё, нашел быстрое и адекватное решение, подходящее для больших выборок.
Жаль, работать будет только в 2017+

Код: 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.
use tempdb;
set ansi_nulls, quoted_identifier, xact_abort on;
go

create table dbo.t1 (id int primary key, s varchar(50));
create table dbo.t2 (id int primary key, s varchar(50));
go

insert into dbo.t1
 (id, s)
 select top (1000000)
  row_number() over (order by 1/0), cast(newid() as varchar(50))
 from
  master.dbo.spt_values a cross join
  master.dbo.spt_values b;

insert into dbo.t2
 (id, s)
 select top (100)
  row_number() over (order by 1/0), cast(newid() as varchar(50))
 from
  master.dbo.spt_values a cross join
  master.dbo.spt_values b;
go


;With
N(N) as (Select count(*) from dbo.t2),
M(M) as (Select count(*) from dbo.t1),
s as (
 Select ABS(CHECKSUM(NewId())) % (Select N.N from N) + 1 R, ROW_NUMBER() over (order by 1/0) N
	from string_split(Replicate(Cast(N' ' as nvarchar(max)),(Select M.M from M)),' ') t 
),
tt1 as (Select *, ROW_NUMBER() over (order by 1/0) N from t1),
tt2 as (select *, ROW_NUMBER() over (order by 1/0) R from t2)
Select tt1.id, tt1.s, tt2.id, tt2.s from s
	inner join tt1 on s.N = tt1.N
	inner join tt2 on s.R = tt2.R
Order by tt1.N
Option (maxrecursion 0)

GO

drop table dbo.t1
drop table dbo.t2
...
Рейтинг: 0 / 0
21.10.2021, 18:30
| Ответить | Цитировать | Написать  
JOIN со случайной строкой таблицы
    #40105992
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
Кстати, последовательность, с т.з. количества строк случайной таблицы - получилось в высшей степени равномерной:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
;With
N(N) as (Select count(*) from dbo.t2),
M(M) as (Select count(*) from dbo.t1),
s as (
 Select ABS(CHECKSUM(NewId())) % (Select N.N from N) + 1 R, ROW_NUMBER() over (order by 1/0) N
	from string_split(Replicate(Cast(N' ' as nvarchar(max)),(Select M.M - 1 from M)),' ') t 
)
Select Min(t), max(t), avg(t), (max(t)-min(t))*1.0/Max(t), count(distinct R) from (
Select s.R, Count(*) t from s
Group by S.R
) tt



9765 10212 10000 0.043772032902 100
Жаль только добротность (равновероятность появления конкретного значения R после некоторого значения R) и цикличность исследовать не получается.
Я просто не знаю как это сделать на TSQL.
Но вроде как прямо мастхэв получается!
...
Рейтинг: 0 / 0
21.10.2021, 18:56
| Ответить | Цитировать | Написать  
23 сообщений из 23, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / JOIN со случайной строкой таблицы
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&gotonew=1&tid=1684177]:
 0ms
get settings:
 11ms
get forum list:
 16ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 155ms
get topic data:
 12ms
get first new msg:
 8ms
get forum data:
 3ms
get page messages:
 69ms
get tp. blocked users:
 2ms
others: 14ms
total:  298ms
созд. / загр.: 298ms
x
x
Закрыть


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