Выбрать случайную запись из таблицы

Совершенно типовая задача с совершенно очевидным решением. Либо top 1 order by newid(), либо последовательно пронумеровать все записи от 1 до n, где n = select count, раскрутить датчик случайных чисел в диапазоне [1, n], выбрать случайную по порядку запись, вернув значение ее первичного ключа. Все.

 

use tempdb

 

create table Customer (CustomerID nchar(5) primary key, CompanyName nvarchar(40))

insert Customer values ('ALFKI', 'Alfreds Futterkiste'), ('ANATR', 'Ana Trujillo Emparedados y helados'), ('ANTON', 'Antonio Moreno Taqueria'), ('AROUT', 'Around the Horn'), ('BERGS', 'Berglunds snabbkop')

 

;with cte(ID, n) as (select CustomerID, ROW_NUMBER() over (order by CustomerID) from Customer)

select ID from cte where n = cast(RAND() * (select COUNT(1) from cte) as int) + 1

Скрипт 1

 

Единственно, поскольку наше очередное все нынче Денали, данное упражнение можно выполнить с помощью пейджинговых предикатов.

 

select CustomerID from Customer order by CustomerID offset cast(rand() * (select count(1) from Customer) as int) rows fetch next 1 row only

Скрипт 2

 

Приятно, что offset и fetch воспринимают выражения и локальные переменные.

По сравнению с предыдущим скриптом я убрал +1 к датчику случайных чисел, чтобы значение offset приходилось на диапазон [0, n-1]. select … offset m rows fetch next n rows only возвращает n записей, начиная с m+1-й. Таким образом, select … offset 0 rows fetch next 1 row only вернет 1-ю запись.

 

Как-то я генерил фейковые данные для теста. Каждому клиенту нужно было добавить случайный регион. Создадим еще таблицу-справочник регионов:

create sequence RegionSeq as int minvalue 1 no maxvalue start with 1 increment by 1 cycle no cache

create table Region (RegionID int default (next value for RegionSeq) primary key, RegionName nvarchar(50))

insert Region (RegionName) values ('Бургундия'), ('Нормандия'), ('Шампань'), ('Прованс'), ('Гасконь')

Скрипт 3

Добавим в таблицу клиентов поле RegionID, которое сошлем на таблицу Region:

 

alter table Customer add RegionID int foreign key references Region (RegionID)

Скрипт 4

 

Осталось его обновить, присвоив в каждую ячейку случайную запись из таблицы регионов наподобие Скрипта 2

update Customer set RegionID = (select RegionID from Region order by RegionID offset cast(rand() * (select count(1) from Region) as int) rows fetch next 1 row only)

Скрипт 5

 

думал я. Не тут-то было:

 

Ах ну да. Я забыл, что дурацкая функция rand() оценивается не для каждой записи, а в начале запроса:

select CustomerID, rand() from Customer

 

 

Скрипт 6

 

getdate() тоже этим страдает. Чтобы получить случайное число в каждой записи, надо засидировать случайный датчик функцией newid(), которая гарантированно оценивается для каждой записи:

 

select CustomerID, rand(cast(newid() as varbinary)) from Customer

 

 

Скрипт 7

 

или использовать нормальную функцию crypt_gen_random(), которая генерит случайную последовательность байтов заданной длины:

 

select CustomerID, cast(crypt_gen_random(4) as int) from Customer

 

 

Скрипт 8

 

Поэтому я переписал Скрипт 5 как

 

update Customer set RegionID = (selectRegionIDfromRegionorderbyRegionIDoffsetcast(rand(cast(newid()asvarbinary))*(selectcount(1)fromRegion)asint)rowsfetchnext 1 rowonly)

 

 

Скрипт 9

 

Во, циферка поменялась аж с 4 на 5. Это хорошо. Плохо, что опять во всех записях кряду. Значит, дело не только в датчике случайных чисел, догадался Штирлиц. Поскольку в выделенном жирным на Скрипт 9 запросе нет ничего относящегося к таблице Customer, SQL Server решает, что и незачем его выполнять для каждой ее записи. Он выполняет запрос один раз перед началом апдейта, а результат как константу рассовывает во все записи. Чтобы отвратить его от этой затеи, следует явно замешать вычисление в запрос.

 

with cte as (

 select *, cast(rand(cast(newid() as varbinary)) * (select count(1) from Region) as int) + 1 as RegionID_Calc from Customer

)

select * from cte

 

 

Скрипт 10

 

Окончательно имеем:

 

with cte as (

 select *, cast(rand(cast(newid() as varbinary)) * (select count(1) from Region) as int) + 1 as RegionID_Calc from Customer

)

update cte set RegionID = RegionID_Calc

Скрипт 11

 

Алексей Шуленин