Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Интересно, реально ли выбрать одну случайную запись из таблицы  [new]
mishgan2000
Guest
Почему-то меня вдруг заинтересовал сабж. Возможно это уже многократно обсуждалась, но я ничего не нашел.
Как бы это сделать максимально эффективно? Желательно в рамках одного селекта.
30 окт 02, 09:27    [70220]     Ответить | Цитировать Сообщить модератору
 Re: Интересно, реально ли выбрать одну случайную запись из таблицы  [new]
vap
Member

Откуда:
Сообщений: 487
Недавно пробегала идея использования newid()
При очень большой таблице этот вариант неэффективен.

select top 1 * from table order by newid()
30 окт 02, 09:41    [70228]     Ответить | Цитировать Сообщить модератору
 Re: Интересно, реально ли выбрать одну случайную запись из таблицы  [new]
schoooltz
Member

Откуда:
Сообщений: 7
ИМХО селектом вряд ли. а вот куросором точно реально.
30 окт 02, 09:43    [70229]     Ответить | Цитировать Сообщить модератору
 Re: Интересно, реально ли выбрать одну случайную запись из таблицы  [new]
mishgan2000
Guest
newid не совсем подходит. Во первых, у меня этих полей нету. А во вторых в этом случае:
>>select top 1 * from table order by newid()
при n запусках результат будет одним и тем же (если таблица не менялась).
30 окт 02, 10:26    [70271]     Ответить | Цитировать Сообщить модератору
 Re: Интересно, реально ли выбрать одну случайную запись из таблицы  [new]
mishgan2000
Guest
Курсорами не охота...
30 окт 02, 10:26    [70272]     Ответить | Цитировать Сообщить модератору
 Re: Интересно, реально ли выбрать одну случайную запись из таблицы  [new]
SergSuper
Member

Откуда: SPb
Сообщений: 5488
newid() использовать нельзя - то, что он выдаёт нельзя назвать случайным числом, зачастую он может выдавать явно упорядоченные значения

Без курсора тоже можно обойтись

Если не гигантская таблица, то можно [url=https://www.sql.ru/forum/actualthread.aspx?bid=1&tid=11999
]так

Если таблица очень большая, то надо как-то что бы записи были пронумерованы и случайным образом перебирать ключ, пока не подойдёт, но я думаю будет достаточно первого способа
30 окт 02, 10:27    [70274]     Ответить | Цитировать Сообщить модератору
 Re: Интересно, реально ли выбрать одну случайную запись из таблицы  [new]
VVG_
Member

Откуда: Санкт-Петербург
Сообщений: 1203
Как-нибудь так наверное:


use northwind

declare @min int,@max int
select @max=max(orderid),@min=min(orderid) from orders

select top 1 * from orders
where orderid>=(select @min+rand()*(@max-@min))
order by orderid

При желании можно и в один селект запихнуть.

Да и newid() оказывается очень шустренько работает. Примерно 10 000 000 штук в секунду генерится.

newid не совсем подходит. Во первых, у меня этих полей нету. А во вторых в этом случае:
>>select top 1 * from table order by newid()
при n запусках результат будет одним и тем же (если таблица не менялась).


Никаких полей не надо. newid() генерится для каждой записи при каждом запросе.
30 окт 02, 11:34    [70321]     Ответить | Цитировать Сообщить модератору
 Re: Интересно, реально ли выбрать одну случайную запись из таблицы  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31194
2VVG_
Ваш способ подходит, если orderid равномерно распределено.
А newid() не всегда имеет случайные характер - иногда они последовательные.

Об одном способе как-то писали:


create view v_rnd_int
as
select convert(int, rand()*1000000000) as rnd
go

create function i_now()
returns int
as
begin
declare @r int
select @r = rnd from v_rnd_int
return @r
end
go

select top 1 *
from sysobjects
order by dbo.i_now()
30 окт 02, 11:48    [70331]     Ответить | Цитировать Сообщить модератору
 Re: Интересно, реально ли выбрать одну случайную запись из таблицы  [new]
Garya
Member

Откуда: Москва
Сообщений: 31154
Блог
select * from table order by rand()
30 окт 02, 11:51    [70335]     Ответить | Цитировать Сообщить модератору
 Re: Интересно, реально ли выбрать одну случайную запись из таблицы  [new]
SergSuper
Member

Откуда: SPb
Сообщений: 5488
2 Garya
Попробуй сначала
select rand() from sysobjects :)
30 окт 02, 11:54    [70339]     Ответить | Цитировать Сообщить модератору
 Re: Интересно, реально ли выбрать одну случайную запись из таблицы  [new]
VVG_
Member

Откуда: Санкт-Петербург
Сообщений: 1203
2 Garya Интересные результаты:

use northwind


select * from orders order by newid()
select * from orders order by rand()
30 окт 02, 11:55    [70340]     Ответить | Цитировать Сообщить модератору
 Re: Интересно, реально ли выбрать одну случайную запись из таблицы  [new]
VVG_
Member

Откуда: Санкт-Петербург
Сообщений: 1203
Но опять же, при order by <что угодно> выбирается <что угодно> для каждой строки таблицы, потом эта выборка сортируется и выдается первый результат.

И вообще - на каждый чих не наздравствуешься. Универсальный способ очень трудно найти - приходится чем-то жертвовать.
30 окт 02, 11:59    [70345]     Ответить | Цитировать Сообщить модератору
 Re: Интересно, реально ли выбрать одну случайную запись из таблицы  [new]
mishgan2000
Guest
2 VVG_
>>Никаких полей не надо. newid() генерится для каждой
>>записи при каждом запросе
Понял, сразу не сообраил...
30 окт 02, 12:52    [70387]     Ответить | Цитировать Сообщить модератору
 Re: Интересно, реально ли выбрать одну случайную запись из таблицы  [new]
Garya
Member

Откуда: Москва
Сообщений: 31154
Блог
Если в таблице есть какой-то целочисленный ID, то можно вычислить максимальный ID, а потом генерить в цикле ID, умножая максимальное значение ID на Rand() до тех пор, пока не сгенерится ID существующей в таблице записи. Этот вариант годится только для тех таблиц, в ID которых мало дырок (мало пропусков в нумерации). В таблице sysobjects есть поле ID, которое годится для нашей задачи, но оно имеет почему-то ТАКИЕ БОЛЬШИЕ ДЫРЫ В НУМЕРАЦИИ, что я минут пятнадцать ждал, пока рандомайзом сгенерится существующий ID, после чего так и не дождавшись прервал запрос.
Если таблица небольшая (вроде sysobjects), и одновременно ID имеет огромные дыры в нумерации, то можно попробовать так, как показано ниже. Я попробовал, вроде бы работает:
declare @ID int

declare @T table(ident int identity(1,1) unique clustered,ID int)
insert into @T(id)
select ID from sysobjects
set @ID=(select @@identity*rand())
set @ID=(select ID from @T where ident=@ID)
select * from sysobjects where id=@ID
30 окт 02, 13:01    [70392]     Ответить | Цитировать Сообщить модератору
 Re: Интересно, реально ли выбрать одну случайную запись из таблицы  [new]
mishgan2000
Guest
В принципе такой вариант вполне устраивает.
declare @i INT;
select @i=(select top 1 id from sysobjects order by newid())
select @i
Мне это нужно было для генерации некоторого тестового набора данных.
ЗЫ:
Объясните кто-нибудь эффект:
select rand() from sysobjects
30 окт 02, 13:02    [70394]     Ответить | Цитировать Сообщить модератору
 Re: Интересно, реально ли выбрать одну случайную запись из таблицы  [new]
Garya
Member

Откуда: Москва
Сообщений: 31154
Блог
Сэры, не нужно искать глубокий смысл в пятой строчке приведенного скрипта :).
Конечно же, она должна выглядеть так:
set @ID=@@identity*rand()


Это просто издержки перебора разных вариантов кода... Правда, и впрежней редакции - тоже работает, причем правильно... :)
30 окт 02, 13:06    [70396]     Ответить | Цитировать Сообщить модератору
 Re: Интересно, реально ли выбрать одну случайную запись из таблицы  [new]
Garya
Member

Откуда: Москва
Сообщений: 31154
Блог
>Объясните кто-нибудь эффект:
>select rand() from sysobjects

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

Я пытался его обмануть таким образом:
select rand()+ID*0 from sysobjects
... но он, собака такая, не обманывается и все равно сначала вычисляет значение rand(), а потом уже вычисленное значение подставляет в качестве операнда в эту формулу. Если бы у rand() был аргумент, в который можно было бы подсунуть значение какого-нибудь поля, не сломав при этом "случайность" работы rand()... Аргумент есть, но при попытке его использования rand() возвращает одни и те же значения для одного и того же значения аргумента, что тоже не годится.
30 окт 02, 13:15    [70406]     Ответить | Цитировать Сообщить модератору
 Re: Интересно, реально ли выбрать одну случайную запись из таблицы  [new]
VVG_
Member

Откуда: Санкт-Петербург
Сообщений: 1203
2 Garya
Объясни лучше, чем вызов rand() отличается от вызова newid() и почему результаты такие разные.

А вариант с временной таблицей дает, конечно, равномерное распределение, но уж очень он тормозной...
30 окт 02, 13:24    [70411]     Ответить | Цитировать Сообщить модератору
 Re: Интересно, реально ли выбрать одну случайную запись из таблицы  [new]
SergSuper
Member

Откуда: SPb
Сообщений: 5488
Господа!
Посмотрите внимательно скрипт alexeyvg от 11:48
Он удовлетворяет всем требованиям. Не нужно ничего изобретать!
30 окт 02, 13:47    [70419]     Ответить | Цитировать Сообщить модератору
 Re: Интересно, реально ли выбрать одну случайную запись из таблицы  [new]
VVG_
Member

Откуда: Санкт-Петербург
Сообщений: 1203
2 SergSuper
Удовлетворять-то удовлетворяет, но у меня на 130000 записях вариант с UDF работал двенадцать секунд против секунды для newid. Причем удивительно - Execution Plan для обоих запросов одинаковый!!! Кстати, через временную таблицу отработало за восемь секунд.

А курсорный вариант c fetch absolute стабильно работал за 2-3 секунды!

declare @cnt int, @c int

select @cnt=floor(rand()*count(*)) from table

declare c cursor static for select id from table
open c
fetch absolute @cnt from c into @c
select * from table where id=@c
close c
deallocate c

О как.
30 окт 02, 14:19    [70443]     Ответить | Цитировать Сообщить модератору
 Re: Интересно, реально ли выбрать одну случайную запись из таблицы  [new]
Glory
Member

Откуда:
Сообщений: 104760
Подведем некоторые итоги
Я насчитал следующие способы


/*1-st begin*/
USE pubs
declare @a int, @b int, @rand_num int, @table_name varchar(50), @pk_field_name varchar(50), @mysql varchar(4000)

set @a = 1
set @table_name = 'authors'
set @pk_field_name = 'au_id'

select @b=rows
from sysindexes a
inner join sysobjects b on b.id = a.id
where indid IN(0, 1) and b.name = @table_name and b.xtype = 'U'

/*Random record number between 1 and total table record count*/
set @rand_num = cast((@b-@a+1)*RAND()+@a as int)

set @mysql = 'select top 1 '+@pk_field_name
+' from (select top '+CAST(@rand_num AS varchar)+' '+@pk_field_name+' from '+@table_name+' order by '+@pk_field_name
+') AS a order by '+@pk_field_name+' desc'
exec(@mysql)
/*1-st end*/
go

/*2-nd begin*/
use northwind

declare @table_name varchar(50), @pk_field_name varchar(50), @mysql varchar(4000)
set @table_name = 'orders'
set @pk_field_name = 'orderid'

set @mysql = 'declare @min int, @max int
select @max=max('
+@pk_field_name+'),@min=min('+@pk_field_name+') from orders '+@table_name+'
select top 1 '
+@pk_field_name+' from '+@table_name+' where '+@pk_field_name+' >=(select @min+rand()*(@max-@min+1)) order by '+@pk_field_name
exec(@mysql)

/*2-nd end*/
go


/*3-rd begin*/
USE pubs
declare @table_name varchar(50), @pk_field_name varchar(50), @mysql varchar(4000)
set @table_name = 'authors'
set @pk_field_name = 'au_id'

set @mysql = 'select top 1 '+@pk_field_name+' from '+@table_name+' order by newid()'
exec(@mysql)
/*3-rd end*/
go


/*4-th begin*/
USE northwind
declare @table_name varchar(50), @pk_field_name varchar(50), @mysql varchar(4000)
set @table_name = 'orders'
set @pk_field_name = 'orderid'

set @mysql = 'select top 1 '+@pk_field_name+' from '+@table_name
+' order by rand('+@pk_field_name+') * rand(datepart(ms,getdate())) * 100000000 - floor(rand('+@pk_field_name
+') * rand(datepart(ms,getdate())) * 100000000)'
exec(@mysql)
/*4-th end*/
go



/*5-th begin*/
use northwind
go

declare @table_name varchar(50), @pk_field_name varchar(50), @mysql nvarchar(4000), @cur cursor
set @table_name = 'orders'
set @pk_field_name = 'orderid'

declare @cnt int, @c int
set @mysql = N'select @cnt=floor(rand()*count(*)) from '+@table_name
exec sp_executesql @mysql, N'@cnt int output ', @cnt=@cnt output

set @mysql = N'set @curvar= cursor local static for select '+@pk_field_name+' from '+@table_name+' open @curvar'
exec sp_executesql @mysql, N'@curvar cursor output ', @curvar=@cur output

fetch absolute @cnt from @cur into @c
close @cur
deallocate @cur
select @c
/*5-th begin*/
go

/*6-th begin SQL2000 only*/
use pubs
go
if object_id('v_rnd_int') is not null drop view v_rnd_int
go
create view v_rnd_int
as
select convert(int, rand()*1000000000) as rnd
go

if object_id('i_now') is not null drop function i_now
go
create function i_now()
returns int
as
begin
declare @r int
select @r = rnd from v_rnd_int
return @r
end
go

declare @table_name varchar(50), @pk_field_name varchar(50), @mysql varchar(4000)
set @table_name = 'authors'
set @pk_field_name = 'au_id'
set @mysql = 'select top 1 '+@pk_field_name+' from '+@table_name+' order by dbo.i_now()'
exec(@mysql)
/*6-th end SQL2000 only*/
go
30 окт 02, 14:50    [70465]     Ответить | Цитировать Сообщить модератору
 Re: Интересно, реально ли выбрать одну случайную запись из таблицы  [new]
VVG_
Member

Откуда: Санкт-Петербург
Сообщений: 1203
Предлагаю

1) вычисление count(*) обернуть в функцию:

create function GetCount(@tablename varchar(100))

returns bigint
as
begin
declare @cnt bigint
select @cnt=a.rows from sysobjects b
inner join sysindexes a on b.id = a.id
where indid IN(0, 1) and b.name = @tablename and b.xtype = 'U'
return @cnt
end

Кстати, есть ли абсолютная уверенность, что она всегда будет работать? Со съехавшими статистиками например...

2) Все варианты подогнать под одну и ту же базу/таблицу.

3) Где можно, не использовать динамический SQL.

4) Отправить в FAQ.
30 окт 02, 15:17    [70486]     Ответить | Цитировать Сообщить модератору
 Re: Интересно, реально ли выбрать одну случайную запись из таблицы  [new]
Glory
Member

Откуда:
Сообщений: 104760
1) вычисление count(*) обернуть в функцию:

Тогда это не будет работать в SQL7

Кстати, есть ли абсолютная уверенность, что она всегда будет работать? Со съехавшими статистиками например...
Уверенности нету

2) Все варианты подогнать под одну и ту же базу/таблицу.

Под одну таблицу не получится.
Все будет зависить от типа данных PK. Варианты 2 и 4 будут работать если PK есть tinyint, smallint или int.

3) Где можно, не использовать динамический SQL
Динамический запрос был использован для того, чтобы можно было легко проверить работоспособность скрипта на любой таблице.
30 окт 02, 16:56    [70557]     Ответить | Цитировать Сообщить модератору
 Re: Интересно, реально ли выбрать одну случайную запись из таблицы  [new]
Glory
Member

Откуда:
Сообщений: 104760
Кстати, кто-нибудь объяснит странность. У меня при запуске всего скрипта варианты 1, 2 и 5 при повторных запусках начинают выдавать одно и то же значение. Если же варианты запускать по отдельности, то все в порядке. Во вариантах 1, 2 и 5 используется rand(). Если во всем скрипте оставить только один любой вариант с rand(), то он начинает работать правильно

????
30 окт 02, 17:13    [70572]     Ответить | Цитировать Сообщить модератору
 Re: Интересно, реально ли выбрать одну случайную запись из таблицы  [new]
VVG_
Member

Откуда: Санкт-Петербург
Сообщений: 1203
Надо rand инициализировать чем-нибудь, например миллисекундами: rand(datepart(ms,getdate()))

Он же все таки псевдослучайный...
30 окт 02, 17:25    [70583]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить