Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / JOIN со случайной строкой таблицы / 23 сообщений из 23, страница 1 из 1
20.10.2021, 03:01
    #40105489
Шамиль Фаридович
Шамиль Фаридович
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
JOIN со случайной строкой таблицы
Необходимо соединить одну таблицу со случайной строкой из другой таблицы
Конструкция вида
Код: 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:17
    #40105490
Massa52
Massa52
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
JOIN со случайной строкой таблицы
Шамиль Фаридович,
Вроде как цикл напрашивается.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.10.2021, 07:25
    #40105496
Guf
Guf
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
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.
-----Если в 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, 08:17
    #40105500
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
JOIN со случайной строкой таблицы
Код: 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, 13:25
    #40105574
uaggster
uaggster
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
JOIN со случайной строкой таблицы
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:43
    #40105586
uaggster
uaggster
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
JOIN со случайной строкой таблицы
Блин, туплю. Это одно и то же...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.10.2021, 14:01
    #40105590
uaggster
uaggster
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
JOIN со случайной строкой таблицы
Короче говоря, почему не годятся скрипты ни 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:09
    #40105594
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
JOIN со случайной строкой таблицы
uaggster

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


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

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


Вы бредите.

Неа. Это вы бредите :-)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.10.2021, 03:48
    #40105725
Шамиль Фаридович
Шамиль Фаридович
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
JOIN со случайной строкой таблицы
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, 06:56
    #40105726
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
JOIN со случайной строкой таблицы
[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
    #40105727
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
JOIN со случайной строкой таблицы
Если у те нужно "к каждой"

Код: 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, 07:02
    #40105728
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
JOIN со случайной строкой таблицы
дубль
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.10.2021, 09:08
    #40105743
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
JOIN со случайной строкой таблицы
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:18
    #40105748
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
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.
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:38
    #40105752
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
JOIN со случайной строкой таблицы
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, 12:22
    #40105793
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
JOIN со случайной строкой таблицы
Надо выбирать случайные ключи, а не строки.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.10.2021, 13:52
    #40105815
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
JOIN со случайной строкой таблицы
Владислав Колосов
Надо выбирать случайные ключи, а не строки.

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

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

Но это столь ужасно, что лучше уж 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, 17:14
    #40105938
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
JOIN со случайной строкой таблицы
uaggster
что лучше уж cross join


Cross join плох тем, что вы выполняете абсолютно ненужную генерацию херовой тучи строк.
Из которых вам нужны только 20.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.10.2021, 18:30
    #40105979
uaggster
uaggster
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
JOIN со случайной строкой таблицы
Всё, нашел быстрое и адекватное решение, подходящее для больших выборок.
Жаль, работать будет только в 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:56
    #40105992
uaggster
uaggster
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
JOIN со случайной строкой таблицы
Кстати, последовательность, с т.з. количества строк случайной таблицы - получилось в высшей степени равномерной:
Код: 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
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / JOIN со случайной строкой таблицы / 23 сообщений из 23, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 142ms
Закрыть
start [/forum/topic.php?fid=46&tablet=1&tid=1684177]:
 0ms
get settings:
 8ms
get forum list:
 12ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 30ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 60ms
get tp. blocked users:
 1ms
others: 13ms
total:  142ms
x
x
Закрыть


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