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

Откуда:
Сообщений: 9
Скажите пожалуйста есть ли функция которая делает рандомный выбор и из всех строк в таблице выбирает любую?
31 авг 13, 23:34    [14780508]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная выборка  [new]
qwerty112
Guest
ArtemVS
Скажите пожалуйста есть ли функция которая делает рандомный выбор и из всех строк в таблице выбирает любую?

select top 1 ...
..
order by newid()
31 авг 13, 23:39    [14780518]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная выборка  [new]
ArtemVS
Member

Откуда:
Сообщений: 9
спасибо!
31 авг 13, 23:45    [14780524]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная выборка  [new]
Твой Друх
Member

Откуда:
Сообщений: 35
Вах, как то же сделать через linq не подскажите db.TABLE.OrderBy(x=>{??? - что тут}).ToList();?
1 сен 13, 17:53    [14781259]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная выборка  [new]
aleks2
Guest
qwerty112
ArtemVS
Скажите пожалуйста есть ли функция которая делает рандомный выбор и из всех строк в таблице выбирает любую?

select top 1 ...
..
order by newid()


Шоб тибе так выбирать из... бальшой таблицы.

ЗЫ. Фуфло, а не метод.
1 сен 13, 18:37    [14781295]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная выборка  [new]
qwerty112
Guest
aleks2
qwerty112
пропущено...

select top 1 ...
..
order by newid()


Шоб тибе так выбирать из... бальшой таблицы.

ЗЫ. Фуфло, а не метод.

дык, а какие ещё варианты есть ?

была бы таблица статическая, можно было бы пронумеровать один раз все строки от 1 до N ,
а потом с клиента генерить нужный номер и забирать строку с этим номером ...
(хотя можно жж и в ХП генерить и отдавать строку)

но это если строки не удаляются ... не перенумеровывать же каждый раз ... ?
1 сен 13, 18:49    [14781313]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная выборка  [new]
Exproment
Member

Откуда:
Сообщений: 416
aleks2
ЗЫ. Фуфло, а не метод.

действительно, а вы что предлагаете ?
1 сен 13, 18:52    [14781318]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная выборка  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
Мне кажется, что при наличии в таблице целочисленного первичного ключа,
можно выбрать N-ую запись таблицы в порядке первичного ключа, причём вместо N
применить функцию от RAND(), возвращающую целое число от 1 до количества записей в таблице.

Для MSSQL 2012
SELECT *
FROM T
ORDER BY ID OFFSET (SELECT COUNT(*)-1 FROM T)*RAND()+1 ROWS FETCH FIRST 1 ROW ONLY;
1 сен 13, 19:46    [14781401]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная выборка  [new]
Exproment
Member

Откуда:
Сообщений: 416
iap, К сожалению на 2012 проверить не могу. Но думается мне особо это производительности не прибавит. Все равно надо делать сканирование. И для больших таблиц умрешь на этом :) Разве что сортировка наверное будет по проще, что так-же надо смотреть.
1 сен 13, 21:56    [14781694]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная выборка  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
Exproment
iap, К сожалению на 2012 проверить не могу. Но думается мне особо это производительности не прибавит. Все равно надо делать сканирование. И для больших таблиц умрешь на этом :) Разве что сортировка наверное будет по проще, что так-же надо смотреть.
Вы прямо как Обама!
Проверить не могу, но не прибавит!
("Результаты расследования нам не нужны. Всё равно Сирию бомбить будем!")
1 сен 13, 22:19    [14781751]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная выборка  [new]
Exproment
Member

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

Да ладно вам, это была жалкая попытка задать тему дискуссии. Может найдется человек с предустановленным 2012 сервером и проверит :)
1 сен 13, 22:48    [14781833]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная выборка  [new]
invm
Member

Откуда: Москва
Сообщений: 9413
В качестве идеи:
+ Подготовка
use tempdb;
go

create table dbo.TestTable
(
 id int not null constraint PK_TestTable primary key clustered,
 v1 char(500) not null default cast(newid() as char(500))
);

insert into dbo.TestTable
 (id)
select top (1000000)
 row_number() over (order by (select 1))
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b;
use tempdb;
go

declare @t table (RANGE_HI_KEY int, RANGE_ROWS int, EQ_ROWS int, DISTINCT_RANGE_ROWS int, AVG_RANGE_ROWS int);

insert into @t
 exec('dbcc show_statistics(''dbo.TestTable'', ''PK_TestTable'') with no_infomsgs, histogram;');

declare @l int, @h int;

with x as
(
 select
  v, row_number() over (order by v) as rn
 from
  (
   select -2147483648
   union all
   select 2147483647
   union all
   select RANGE_HI_KEY from @t
  ) t(v)
)
select top (1)
 @l = t1.v, @h = t2.v
from
 x t1 join
 x t2 on t2.rn = t1.rn + 1
where
 t1.rn % 2 = 1
order by
 newid();

set statistics xml, time, io on;

select top (1) * from dbo.TestTable order by newid();
select top (1) * from dbo.TestTable where id between @l and @h order by newid();

set statistics xml, time, io off;
1 сен 13, 23:05    [14781892]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная выборка  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
iap
Мне кажется, что при наличии в таблице целочисленного первичного ключа,
можно выбрать N-ую запись таблицы в порядке первичного ключа, причём вместо N
применить функцию от RAND(), возвращающую целое число от 1 до количества записей в таблице.

Для MSSQL 2012
SELECT *
FROM T
ORDER BY ID OFFSET (SELECT COUNT(*)-1 FROM T)*RAND()+1 ROWS FETCH FIRST 1 ROW ONLY;
Ошибка:
SELECT *
FROM T
ORDER BY ID OFFSET (SELECT COUNT(*)-1 FROM T)*RAND() ROWS FETCH FIRST 1 ROW ONLY;
Ибо OFFSETом надо пропустить на одну строку меньше, чем позиция, с которой будет работать FETCH
2 сен 13, 09:39    [14782616]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная выборка  [new]
Гость333
Member

Откуда:
Сообщений: 3683
iap
Ошибка:
SELECT *
FROM T
ORDER BY ID OFFSET (SELECT COUNT(*)-1 FROM T)*RAND() ROWS FETCH FIRST 1 ROW ONLY;
Ибо OFFSETом надо пропустить на одну строку меньше, чем позиция, с которой будет работать FETCH

Msg 10743, Level 15, State 1, Line 10
The number of rows provided for a OFFSET clause must be an integer.

То есть так надо:
SELECT *
FROM T
ORDER BY ID OFFSET CAST((SELECT COUNT(*)-1 FROM T)*RAND() AS bigint) ROWS FETCH FIRST 1 ROW ONLY;

Ну и, в случае больших таблиц, запрос помрёт на "SELECT COUNT(*)". Можно доставать количество строк таблицы из sys.partitions, но тут, конечно, будут свои издержки.
2 сен 13, 09:52    [14782652]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная выборка  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
Гость333
Ну и, в случае больших таблиц, запрос помрёт на "SELECT COUNT(*)". Можно доставать количество строк таблицы из sys.partitions, но тут, конечно, будут свои издержки.
Помрёт-то помрёт, но знание количества строк в этом случае по-любому необходимо.
2 сен 13, 09:54    [14782659]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная выборка  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Если я правильно понимаю, задача избежать дорогостоящей операции сортировки и еще лучше сканирования в случае большой таблицы. Вараинт с OFFSET решает проблему избавления от сортировки, однако, если таблица большая, а случайное значение определяющее сколько строк нужно пропустить выбрало, допустим, 75%, то чтобы найти это значение, сервер все равно вынужден просканировать эти 75%, отсчитывая строки.

Имхо, для больших таблиц, лучше позиционироваться случайно, т.е. выполнить некий random seek. Для этого, нужен индекс, если он есть, то я бы сделал так (использую таблицу invm для примера):
set statistics io on
declare @s int, @e int, @r int;
select top(1) @s = id from TestTable order by id; --Scan only first row(page) with Ordered=Forward
select top(1) @e = id from TestTable order by id desc;--Scan only last row(page) with Ordered=Backward
-- generate pseudo random possible value between min value and max value, @r C [@s ; @e], hope i'm not messing with it
set @r = @s + ( abs(checksum(newid())) % (@e-@s+1) ); 
--perform index seek, to find first equal or greater value than our random number
select top (1) * from TestTable t with(forceseek) where t.id >= @r;
set statistics io off

автор
Table 'TestTable'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TestTable'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TestTable'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
2 сен 13, 11:34    [14783138]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная выборка  [new]
qwerty112
Guest
SomewhereSomehow
--perform index seek, to find first equal or greater value than our random number
select top (1) * from TestTable t with(forceseek) where t.id >= @r;


имхо, при Вашем подходе "теряется" главное - "равномерное распределение" этой самой случайной записи

на примере:
есть таб.
id
1
1000
1001
1002
...
1999
2000

вот банальная эрудиция :) теория вероятности, говорит о том, что запись с id=1000 будет выбранна с почти 50% вероятностью
2 сен 13, 12:06    [14783310]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная выборка  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
qwerty112,

А я как оптимизатор, использую предположение равномерности =)))

Но вообще вы правы, ценное уточнение. Если таблица неравномерная, то значение можно генерировать опираясь на статистику, примерно как invm показал, и аглоритм выбора значения можно любой придумывать - тут уже нужно смотреть на реальные данные, я только проиллюстрировал идею случайного поиска.
2 сен 13, 12:33    [14783495]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная выборка  [new]
qwerty112
Guest
вот такая, ещё идейка "на уровне бреда" :), у меня была ...

while 1=1
begin
  set @r = @s + ( abs(checksum(newid())) % (@e-@s+1) ); 
  --perform index seek, to find first equal or greater value than our random number
  select top (1) * from TestTable t with(forceseek) where t.id = @r;
  if @@rowcount>0 break
end
2 сен 13, 12:42    [14783560]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная выборка  [new]
SHKoder
Member

Откуда: Moscow city
Сообщений: 616
select * from <tablename> tablesample (100 rows)
2 сен 13, 13:55    [14784164]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная выборка  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Еще интереснее, если нет индекса или не хочется заморачиваться с поиском. Вот так можно выкрутиться:
alter table TestTable drop constraint PK_TestTable;
go
set statistics io on
select top(1) * from TestTable t tablesample(0.1 PERCENT) order by newid()
set statistics io off

автор
Table 'TestTable'. Scan count 1, logical reads 48, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Но с tablesample также есть много нюансов, необходимо их учитывать (например, про случайность на уровне строк). Для этого внимаительно прочитать Ограничение результирующего набора с помощью предложения TABLESAMPLE, чтобы понимать как формируется результат при сканировании с tablesample. Учитывать, что при очень малом проценке, строк может не быть, например:
set statistics io on
select top(1) * from TestTable tablesample(0.001 PERCENT) order by newid()
set statistics io off
go 5

автор
Beginning execution loop

(0 row(s) affected)
Table 'TestTable'. Scan count 1, logical reads 0

(0 row(s) affected)
Table 'TestTable'. Scan count 1, logical reads 0

(1 row(s) affected)
Table 'TestTable'. Scan count 1, logical reads 2

(1 row(s) affected)
Table 'TestTable'. Scan count 1, logical reads 1

(0 row(s) affected)
Table 'TestTable'. Scan count 1, logical reads 0

Batch execution completed 5 times.
2 сен 13, 13:58    [14784189]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная выборка  [new]
йоптырь
Guest
ребят, ну что вы, я извиняюсь, фигнёй занимаетесь?
ТС спросил про случайную выборку и все, а вы уже чуть-чуть и начнете учитывать если Фукусима еще раз рванет и до Москвы достанет...
тьфу!
2 сен 13, 14:02    [14784222]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная выборка  [new]
Гость333
Member

Откуда:
Сообщений: 3683
йоптырь
ребят, ну что вы, я извиняюсь, фигнёй занимаетесь?
ТС спросил про случайную выборку и все, а вы уже чуть-чуть и начнете учитывать если Фукусима еще раз рванет и до Москвы достанет...
тьфу!

Да проблемы ТСа тут уже давно никого не волнуют ;-)
Для его студенческой микроБД хватило и первого ответа.
Интереснее поразмыслить в промышленных масштабах.
2 сен 13, 14:12    [14784273]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить