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

Откуда: Киев
Сообщений: 98
Привет!

Мой вопрос не нов, конечно, но на просторах интернета столько фигни написано в качестве ответа, а толковой реализации нет. Пытаюсь решить вопрос сам, но пока в голову приходят только слишком рессурсоёмкие реализации. Итак: как организовать выбор рандомной записи из результатов?

Например, есть некий запрос:

select id from People where country = 'Ukraine';


Прикинем, что в результате запроса я получил 500 результатов. Как мне получить, например, 28 результат?

Думаю, вы понимаете о чём я: мне нужна простая реализация выбора рандомной записи из кучи результатов.

Я придумал вариант реализации, но он «тупой», я считаю: нужно все результаты поиска вначале сохранить во временную таблицу, потом сгенерировать случайное число процедурой RAND() и в цикле FOR получть запись, соответстующую сгенерированному случайному числу — ерунда ведь...

Может у кого-то есть готовое решение?

Спасибо!
21 ноя 13, 14:40    [15168511]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная/случайная выбока в SQL  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2415
roman_lenko,

select top 28 не устраивает? :)
а вобще ищите не там, надо не просторах инета, а на этом сайте. буквально пару недель назад была очередная тема (с кодом)
21 ноя 13, 14:42    [15168536]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная/случайная выбока в SQL  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3058
часто встречается такой вариант

select top (1) id from People where country = 'Ukraine' order by newid()

это конечно не 28 результат, но некоторая псевдослучайная запись
21 ноя 13, 14:45    [15168561]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная/случайная выбока в SQL  [new]
roman_lenko
Member

Откуда: Киев
Сообщений: 98
StarikNavy
roman_lenko,

select top 28 не устраивает? :)
а вобще ищите не там, надо не просторах инета, а на этом сайте. буквально пару недель назад была очередная тема (с кодом)


HandKot
часто встречается такой вариант

select top (1) id from People where country = 'Ukraine' order by newid()


это конечно не 28 результат, но некоторая псевдослучайная запись


Ну, как-же так.. Получается, что, если у меня таблица проиндексирована (nonclustered index) про полю country, то в результате запроса ... where country = 'Ukraine' все записи всегда будут возвращаться в одном и том же порядке. Соответственно top 28 мне всегда будет выдавать первых 28 записей и она всегда будут одинаковыми.

В примере HandKot то же самое — мне будет возвращаться 1-я запись из списка результатов, построенного на основе индекса для country.
21 ноя 13, 14:52    [15168639]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная/случайная выбока в SQL  [new]
Glory
Member

Откуда:
Сообщений: 104751
roman_lenko
Получается, что, если у меня таблица проиндексирована (nonclustered index) про полю country, то в результате запроса ... where country = 'Ukraine' все записи всегда будут возвращаться в одном и том же порядке.

Наличие индекса НЕ гарантирует упорядочивание результатов запроса.

roman_lenko
В примере HandKot то же самое — мне будет возвращаться 1-я запись из списка результатов, построенного на основе индекса для country.

Т.е. по-вашему, сервер всегда игнорирует order by ?
21 ноя 13, 14:54    [15168663]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная/случайная выбока в SQL  [new]
roman_lenko
Member

Откуда: Киев
Сообщений: 98
Glory
Наличие индекса НЕ гарантирует упорядочивание результатов запроса.


Ну, я не спорю — может я чего-то не знаю, но у меня реально ВСЕГДА, в условии WHERE, для поля входящего в индекс, результаты возвращаются в одинаковом порядке — платформа SQL Server 2012.

Glory
Т.е. по-вашему, сервер всегда игнорирует order by?


Ой, блин — не заметил ORDER BY NEWID()! Да-да, эту схему я ранее использовал — она работает, то почему-то не очень хорошую рандомность даёт — не знаю почему, но даже имея, например, 20 результатов — очень часто повторяется один и тот же результат (например, 3,3,3,3,3,12,17,14,14,14,20,12,3,3,3 ...). Во-вторых, не знаю насколько быстро работает ORDER BY NEWID() — как-то стрёмно с точки зрения производительности. Поэтому я спрашиваю о более изящном варианте.
21 ноя 13, 15:02    [15168743]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная/случайная выбока в SQL  [new]
Glory
Member

Откуда:
Сообщений: 104751
roman_lenko
Ну, я не спорю — может я чего-то не знаю, но у меня реально ВСЕГДА, в условии WHERE, для поля входящего в индекс, результаты возвращаются в одинаковом порядке

Потому что один кластерный индекс, который собственно и есть таблица ? В которой 500 записей, те меньше 64Кб данных ?

roman_lenko
20 результатов — очень часто повторяется один и тот же результат (например, 3,3,3,3,3,12,17,14,14,14,20,12,3,3,3 ...)

Т.е. вы хотите не просто рандомность, а еще неповторение скольки то предыдущих выполнений ?
roman_lenko
Во-вторых, не знаю насколько быстро работает ORDER BY NEWID() — как-то стрёмно с точки зрения производительности.

Для 500 записей смешно говорить о сколько нибудь заметном падении производительности
21 ноя 13, 15:07    [15168794]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная/случайная выбока в SQL  [new]
roman_lenko
Member

Откуда: Киев
Сообщений: 98
Glory
Потому что один кластерный индекс, который собственно и есть таблица? В которой 500 записей, те меньше 64Кб данных?


Не совсем понял вопрос, но да — у меня пока была тестовая таблица в которой было около 100 записей и один кластерный индекс ID.
Glory
Т.е. вы хотите не просто рандомность, а еще неповторение скольки то предыдущих выполнений?


Нет — не хочу. Просто хочу «лучшей» рандомности, чем получается на практике. Согласитесь, не очень круто когда вместо рандомности получаешь подряд 5 или 6 раз одно и то же число.

Glory
Для 500 записей смешно говорить о сколько нибудь заметном падении производительности


Ну, у меня тестовая таблица. Да — 500 записей там пока не наберётся, но согласитесь, что конструкция ORDER BY NEWID() ущербна сама по себе — т.е., в неё уже заложен определённый «лимит возможностей», который рано или поздно прийдётся решить.
21 ноя 13, 15:13    [15168860]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная/случайная выбока в SQL  [new]
Glory
Member

Откуда:
Сообщений: 104751
roman_lenko
Glory
Потому что один кластерный индекс, который собственно и есть таблица? В которой 500 записей, те меньше 64Кб данных?


Не совсем понял вопрос, но да — у меня пока была тестовая таблица в которой было около 100 записей и один кластерный индекс ID.

Т.е. вы ограничили сервер и теперь уверяте, что мол всегда порядок записей будет одинаков ?

roman_lenko
Нет — не хочу. Просто хочу «лучшей» рандомности, чем получается на практике. Согласитесь, не очень круто когда вместо рандомности получаешь подряд 5 или 6 раз одно и то же число.

Вы вообще понимаете, что такое рандомность ? Если предыдущий рандомный результат влияет на следующий, то это уже не рандомность.

roman_lenko
Ну, у меня тестовая таблица. Да — 500 записей там пока не наберётся, но согласитесь, что конструкция ORDER BY NEWID() ущербна сама по себе — т.е., в неё уже заложен определённый «лимит возможностей», который рано или поздно прийдётся решить.

И какой же там «лимит возможностей» ?
21 ноя 13, 15:17    [15168894]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная/случайная выбока в SQL  [new]
roman_lenko
Member

Откуда: Киев
Сообщений: 98
Glory
Т.е. вы ограничили сервер и теперь уверяте, что мол всегда порядок записей будет одинаков ?


Хм.. Извините, но я вас не понимаю. Если кластерный индекс мешает тому, чтобы записи выводились в случайном порядке, то я могу это понять, но как тогда быть?

Glory
Вы вообще понимаете, что такое рандомность ? Если предыдущий рандомный результат влияет на следующий, то это уже не рандомность.


Я понимаю о чём вы. Но, в том-то и дело, что я подозреваю (но НЕ утверждаю), что это самое повторение одних и тех же чисел какраз и есть не случайность, а некая «внутреняя фишка» SQL Server'а, ну, например, он кеширует сгенерированный ранее NEWID() и выдаёт его вновь, вместо того, чтобы генерировать новый — типа, экономит свои рессурсы (NEWID() всё-таки, видимо, не для текущей задачи предназначается, поэтому я и подозреваю, что из-за внутренних оптимизаций числа часто повторяются). Но это всё могут быть мои домыслы — может действительно эти все числа сгенерировались абсолютно рандомно и никак одно от другого не зависят, но тогда да — мне нужна не «рандомность», а набор максимально уникальных чисел (вы же понимаете, что теория — есть теория, а практика).

Glory
И какой же там «лимит возможностей» ?


Ну, не переиначивайте — вы понимаете о чём я. Есть конструкции, которые изначально ущербны, т.к., изначально имеют предел, а есть конструкции(реализации), которые теоретически предела не имеют. Например, вообще не в тему: скажем, нужно ежесекундно выполнять сложные операции поиска данных с условиями по нескольким таблицам, с использованием JOIN'а и конструкций типа WHERE id IN (...), и периодической вставкой/удалением записей(т.е., блокировкой таблиц) — это конструкция с «лимитом возможностей», т.к., напрямую зависит от мощности ЦПУ, чтобы выполнять ЕЩЕ больше операций — понадобится более мощный ЦПУ. Но вот если эти все данные (из разных таблиц) слить в одну таблицу и периодически выполнять синхронизацию (например, раз в 5 минут), то нужно всёго-навсего делать выборку по одной таблице (без ожидания блокировки!), что в тысячи раз быстрее — это я называю реализацией без лимита возможностей.

Вот и NEWID() точно так же — он расчитан для чего-то другого, и не совсем правильно заставлять платформу каждый раз выстраивать массив из 500 результатов в порядке не определённом индексом. Надеюсь, вы понимаете о чём я.
21 ноя 13, 15:34    [15169024]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная/случайная выбока в SQL  [new]
Glory
Member

Откуда:
Сообщений: 104751
roman_lenko
Я понимаю о чём вы. Но, в том-то и дело, что я подозреваю (но НЕ утверждаю), что это самое повторение одних и тех же чисел какраз и есть не случайность, а некая «внутреняя фишка» SQL Server'а, ну, например, он кеширует сгенерированный ранее NEWID() и выдаёт его вновь, вместо того, чтобы генерировать новый — типа, экономит свои рессурсы (NEWID() всё-таки, видимо, не для текущей задачи предназначается, поэтому я и подозреваю, что из-за внутренних оптимизаций числа часто повторяются). Но это всё могут быть мои домыслы — может действительно эти все числа сгенерировались абсолютно рандомно и никак одно от другого не зависят, но тогда да — мне нужна не «рандомность», а набор максимально уникальных чисел (вы же понимаете, что теория — есть теория, а практика).

Фейспалм.
Если я подбросил монетку 5 раз и она 4 раза выпала решкой и 1 орлом, то значит Бог "закешировал" значение решка.
Если вы про равномерное распределение случайных значений, то это не есть рандомное значение.

roman_lenko
Ну, не переиначивайте — вы понимаете о чём я.

Не понимаю. Привидите пример «лимита возможностей»

roman_lenko
Хм.. Извините, но я вас не понимаю. Если кластерный индекс мешает тому, чтобы записи выводились в случайном порядке, то я могу это понять, но как тогда быть?

Вы знаете, что кроме кластерного может быть еще индекс. И может даже 2 индекса. Или даже 3 индекса.
21 ноя 13, 15:40    [15169090]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная/случайная выбока в SQL  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3058
ну используйте стандарт от MS
Dim MyValue
MyValue = Int((6 * Rnd) + 1)    ' Generate random value between 1 and 6.

таблица = 20 записей
число попыток = 40
результатвыпало
23
42
174
73
202
193
181
54
13
152
33
163
111
122
141
61
131
81

тоже не лучший вариант, и более трудоемкий, т.к надо пронумеровать записи и потом отсекать

ЗЫЖ других способов не знаю
21 ноя 13, 15:52    [15169202]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная/случайная выбока в SQL  [new]
BuKTaP
Member

Откуда:
Сообщений: 132
SELECT id FROM People
WHERE country = 'Ukraine' AND id = ROUND(RAND()*<какое-нибудь число>,0)

хардкор :)
21 ноя 13, 15:59    [15169269]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная/случайная выбока в SQL  [new]
KRS544
Member

Откуда:
Сообщений: 497
roman_lenko,
генерация псевдослучайных чисел
Генерируете число и делаете селект из таблицы по индексу
21 ноя 13, 16:00    [15169277]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная/случайная выбока в SQL  [new]
roman_lenko
Member

Откуда: Киев
Сообщений: 98
Glory
Фейспалм.
Если я подбросил монетку 5 раз и она 4 раза выпала решкой и 1 орлом, то значит Бог "закешировал" значение решка.
Если вы про равномерное распределение случайных значений, то это не есть рандомное значение.


Бог — не SQL Server, у бога нету «кеширования» (ну, по крайней мере, из истории и философии я не слышал о таком :)) И монетка — тоже не SQL Server, у монетки нету «кеширования». SQL Server — это «коробка», интерфейс — что внутри него находится — знают только индусы из Майкрософт.

Опять-же, я НЕ утверждаю — это может быть моя выдумка. Ответ один — NEWID() меня устраивает только когда нет других вариантов.

Glory
Не понимаю. Привидите пример «лимита возможностей»


Ну, я же привёл.

HandKot
ну используйте стандарт от MS


Это же вне SQL, я так понимаю. Это код для выполнения внутри основной рутины программы (т.е., в приложении .NET, например).
21 ноя 13, 16:00    [15169278]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная/случайная выбока в SQL  [new]
roman_lenko
Member

Откуда: Киев
Сообщений: 98
KRS544
roman_lenko,
генерация псевдослучайных чисел
Генерируете число и делаете селект из таблицы по индексу


Спасибо — сейчас почитаю.
21 ноя 13, 16:01    [15169284]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная/случайная выбока в SQL  [new]
Glory
Member

Откуда:
Сообщений: 104751
roman_lenko
Glory
Не понимаю. Привидите пример «лимита возможностей»

Вы вот это "Например, вообще не в тему: скажем, нужно ежесекундно выполнять сложные операции поиска данных с условиями по нескольким таблицам, с использованием JOIN'а и конструкций типа WHERE id IN (...), и периодической вставкой/удалением записей(т.е., блокировкой таблиц) — это конструкция с «лимитом возможностей», т.к., напрямую зависит от мощности ЦПУ, чтобы выполнять ЕЩЕ больше операций — понадобится более мощный ЦПУ. Но вот если эти все данные (из разных таблиц) слить в одну таблицу и периодически выполнять синхронизацию (например, раз в 5 минут), то нужно всёго-навсего делать выборку по одной таблице (без ожидания блокировки!), что в тысячи раз быстрее — это я называю реализацией без лимита возможностей."

называете привел пример про лимит возможностей NEWID() ?

roman_lenko
Бог — не SQL Server, у бога нету «кеширования» (ну, по крайней мере, из истории и философии я не слышал о таком :)) И монетка — тоже не SQL Server, у монетки нету «кеширования». SQL Server — это «коробка», интерфейс — что внутри него находится — знают только индусы из Майкрософт.

Мне кажется, что ваши познания про Бога и SQL Server на одинаково низком уровне.
21 ноя 13, 16:04    [15169325]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная/случайная выбока в SQL  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3058
roman_lenko
HandKot
ну используйте стандарт от MS


Это же вне SQL, я так понимаю. Это код для выполнения внутри основной рутины программы (т.е., в приложении .NET, например).

не правильно понимаете, я привел реализацию, которую в чистом скуле написал BuKTaP в посте 15169277
21 ноя 13, 16:11    [15169383]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная/случайная выбока в SQL  [new]
roman_lenko
Member

Откуда: Киев
Сообщений: 98
Glory
Мне кажется, что ваши познания про Бога и SQL Server на одинаково низком уровне.


Если бы эти оба познания у меня были на достаточном уровне — я бы сейчас не сидел на этом форуме.
21 ноя 13, 16:14    [15169422]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная/случайная выбока в SQL  [new]
Glory
Member

Откуда:
Сообщений: 104751
roman_lenko
Если бы эти оба познания у меня были на достаточном уровне — я бы сейчас не сидел на этом форуме.

Что не мешает вам делать выводы о "кэшировании", "лимите возможностей" и "постоянстве порядка записей"
21 ноя 13, 16:16    [15169437]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная/случайная выбока в SQL  [new]
roman_lenko
Member

Откуда: Киев
Сообщений: 98
HandKot
не правильно понимаете, я привел реализацию, которую в чистом скуле написал BuKTaP в посте 15169277


Понял. Спасибо — пока только начал читать.
21 ноя 13, 16:16    [15169438]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная/случайная выбока в SQL  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3058
roman_lenko,
только, как я и писал, Вам надо сначала пронумеровать записи в соответствии с условием

select row_number() Over (order by ...) id from People where country = 'Ukraine';
а уже потом наворачивать RAND

ИМХО лучше использовать
order by newid()
21 ноя 13, 16:22    [15169488]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная/случайная выбока в SQL  [new]
roman_lenko
Member

Откуда: Киев
Сообщений: 98
HandKot,

спасибо, понял. ORDER BY NEWID() я оставлю на самый конец, если не придумаю что-то иное.
21 ноя 13, 16:30    [15169556]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная/случайная выбока в SQL  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3058
roman_lenko,
отпишитесь, думаю многим будет интересен Ваш результат
21 ноя 13, 16:32    [15169573]     Ответить | Цитировать Сообщить модератору
 Re: Рандомная/случайная выбока в SQL  [new]
KRS544
Member

Откуда:
Сообщений: 497
можно как то так.
select top 1 id from People where country = 'Ukraine' 
and ID<(ABS(CHECKSUM(NEWID())) % @MAX_ID)
order by ID desc

с newid нормальный тоже вариант.

Вы бы сказали, что конкретно хотите реализовать.
Может вас еще отговорят :)
21 ноя 13, 16:32    [15169579]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить