Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Предпятничная задачка. На тему случайных выборок и нераскрытой темы RANDOM()  [new]
grexhide
Member [заблокирован]

Откуда: Страна непреодолимых противоречий
Сообщений: 8553
Может быть и пропустил топик, за что извиняюсь, но суть вопроса в чем.

автор
Есть запрос у которого например несколько строк ответа... Как одним запросом написать:

Нужно выбрать одну случайную (первую, в смысле одну - попавшуюся)..


Ничего умнее, чем

WITH b AS
(
  SELECT /*+ materialize */ mod(dbms_crypto.RandomNumber, 10) rnd  
  FROM  dual
) 
SELECT * 
FROM   b,
(
  SELECT rownum ID, ROWNUM VALUE
  FROM dual 
  CONNECT BY LEVEL <= 10
) A
WHERE A.ID = B.rnd

в голову не пришло... Но !

В наброске выше - есть подвох, т.к. мощность выборки - заранее неизвестна.

Может у кого будут мысли по этому поводу (как бы покрасивше сделать, не прибегая к скалярным подзапросам расчета мозности выборки)?
26 апр 07, 14:37    [4074247]     Ответить | Цитировать Сообщить модератору
 Re: Предпятничная задачка. На тему случайных выборок и нераскрытой темы RANDOM()  [new]
Elic
Member

Откуда:
Сообщений: 30178
order by dbms_random.value) where rownum = 1
26 апр 07, 14:41    [4074289]     Ответить | Цитировать Сообщить модератору
 Re: Предпятничная задачка. На тему случайных выборок и нераскрытой темы RANDOM()  [new]
grexhide
Member [заблокирован]

Откуда: Страна непреодолимых противоречий
Сообщений: 8553
не прибегая к скалярным подзапросам расчета мозности выборки)?


В смысле вот так:

WITH A AS 
(
  SELECT /*+ materialize */ rownum ID, ROWNUM VALUE
  FROM dual 
  CONNECT BY LEVEL <= 10
),
b AS
(
  SELECT /*+ materialize */ mod(dbms_crypto.RandomNumber, (SELECT COUNT(*) FROM A)) + 1 rnd  
  FROM  dual
)
SELECT * 
FROM   A, b 
WHERE A.ID = B.rnd

Как то уж сильно мрачно....
26 апр 07, 14:42    [4074300]     Ответить | Цитировать Сообщить модератору
 Re: Предпятничная задачка. На тему случайных выборок и нераскрытой темы RANDOM()  [new]
RA\/EN
Member

Откуда:
Сообщений: 3663
WITH q AS (
  SELECT lpad('x',ROWNUM,'x') rowstr 
    FROM dual 
  CONNECT BY LEVEL <= (SELECT trunc(dbms_random.VALUE*10)+1 FROM dual))
SELECT * 
  FROM (SELECT *
          FROM q
         ORDER BY dbms_random.value)
 WHERE ROWNUM=1
26 апр 07, 14:43    [4074308]     Ответить | Цитировать Сообщить модератору
 Re: Предпятничная задачка. На тему случайных выборок и нераскрытой темы RANDOM()  [new]
grexhide
Member [заблокирован]

Откуда: Страна непреодолимых противоречий
Сообщений: 8553
Elic
order by dbms_random.value) where rownum = 1


А действительно... Сенки
26 апр 07, 14:50    [4074379]     Ответить | Цитировать Сообщить модератору
 Re: Предпятничная задачка. На тему случайных выборок и нераскрытой темы RANDOM()  [new]
mcureenab
Member

Откуда: Murmansk
Сообщений: 5964
Лотерею что-ли пишешь?

Как тут Elic уже делал:

select *
from
 (select *
  from dual
  order by dbms_random.value
 )
where rownum <= 1

Но это, ИМХО жульническая лотерея. dbms_random.value возвращает последовательность псевдослучайных чисел. Наверняка некоторые записи будут выпадать чаще других, да и на больших выборках будет тормозить.

Я делал иначе.

Сначала рандомизировал выборку (больше для внешнего эффекта). Затем запускал бесконечный цикл, каждая итерация которого (в том числе и та, где счётчик сбрасывался в 0), занимала одно и тоже число тактов процессора (+- время на обработку всяких системных прерываний). В произвольный момент времени "Рулетку" останавливал нажатием кнопки. Значение счётчика указывало победителя.
Даже на выборке из 2х-3х претендентов такая рулетка давала равномерное распределение.
26 апр 07, 15:04    [4074510]     Ответить | Цитировать Сообщить модератору
 Re: Предпятничная задачка. На тему случайных выборок и нераскрытой темы RANDOM()  [new]
grexhide
Member [заблокирован]

Откуда: Страна непреодолимых противоречий
Сообщений: 8553
mcureenab
В произвольный момент времени "Рулетку" останавливал нажатием кнопки. Значение счётчика указывало победителя.
Даже на выборке из 2х-3х претендентов такая рулетка давала равномерное распределение.


А чем плох предварительный вызов SEED в произвольный момент выполнения запроса клиентом?
26 апр 07, 15:51    [4074882]     Ответить | Цитировать Сообщить модератору
 Re: Предпятничная задачка. На тему случайных выборок и нераскрытой темы RANDOM()  [new]
RA\/EN
Member

Откуда:
Сообщений: 3663
mcureenab
Лотерею что-ли пишешь?

Как тут Elic уже делал:

select *
from
 (select *
  from dual
  order by dbms_random.value
 )
where rownum <= 1

Но это, ИМХО жульническая лотерея.

Ясен пень, жульническая, из дуала-то одну строку тягать
mcureenab
dbms_random.value возвращает последовательность псевдослучайных чисел. Наверняка некоторые записи будут выпадать чаще других, да и на больших выборках будет тормозить.

Я делал иначе.

Сначала рандомизировал выборку (больше для внешнего эффекта). Затем запускал бесконечный цикл, каждая итерация которого (в том числе и та, где счётчик сбрасывался в 0), занимала одно и тоже число тактов процессора (+- время на обработку всяких системных прерываний). В произвольный момент времени "Рулетку" останавливал нажатием кнопки. Значение счётчика указывало победителя.
Даже на выборке из 2х-3х претендентов такая рулетка давала равномерное распределение.

А это не "псевдослучайное" распределение? Вообще, на "гарантированно случайном" распределении, умные дядьки исписали килотонны бумаги и защитили кучу диссеров, а не менее умные дядьки зашибают бабло на генераторах случайных чисел для криптографии и их сертификации.
P.S. А бесконечный цикл и остановка рулетки нажатием кнопки убыстряет алгоритм?
26 апр 07, 16:39    [4075174]     Ответить | Цитировать Сообщить модератору
 Re: Предпятничная задачка. На тему случайных выборок и нераскрытой темы RANDOM()  [new]
mcureenab
Member

Откуда: Murmansk
Сообщений: 5964
RA\/EN
P.S. А бесконечный цикл и остановка рулетки нажатием кнопки убыстряет алгоритм?


По крайней мере я сразу получаю результат и этот результат никому неизвестен до самого момента нажатия кнопки. Если использовать запрос, то сначала придётся рандомизировать всю выборку, отсортировать и только после этого найти победителя, причём уже после вызова seed результат станет детерминированным.
26 апр 07, 17:02    [4075334]     Ответить | Цитировать Сообщить модератору
 Re: Предпятничная задачка. На тему случайных выборок и нераскрытой темы RANDOM()  [new]
mcureenab
Member

Откуда: Murmansk
Сообщений: 5964
RA\/EN
mcureenab
Даже на выборке из 2х-3х претендентов такая рулетка давала равномерное распределение.

А это не "псевдослучайное" распределение?


Думаю, что нет. Поскольку оператор нажимающий кнопку - термодинамическая система, то результат непредсказуем.

PS. Во какая трава!
26 апр 07, 17:06    [4075355]     Ответить | Цитировать Сообщить модератору
 Re: Предпятничная задачка. На тему случайных выборок и нераскрытой темы RANDOM()  [new]
RA\/EN
Member

Откуда:
Сообщений: 3663
mcureenab
RA\/EN
mcureenab
Даже на выборке из 2х-3х претендентов такая рулетка давала равномерное распределение.

А это не "псевдослучайное" распределение?


Думаю, что нет. Поскольку оператор нажимающий кнопку - термодинамическая система, то результат непредсказуем.

PS. Во какая трава!

Рулетку тоже термодинамический крупье запускает, однако у начинающих попадание бывает очень даже не равномерное - и это факт, на котором хитрозадые дяди делают бабло.
В данном случае у оператора есть некий постоянный элемент нежелания ждать долго, поэтому он будет нажимать кнопку в одно и тоже время (приблизительно).
Флейм уже пошел, вот ссылочки на тему для размышлений:
http://leo.yuriev.ru/random
http://vx.netlux.org/lib/vsl04.html
P.S. Сидование таймстампом (младшими битами) тоже, кстати, является результатом действия термодинамической системы , запускающей процесс.
26 апр 07, 17:30    [4075502]     Ответить | Цитировать Сообщить модератору
 Re: Предпятничная задачка. На тему случайных выборок и нераскрытой темы RANDOM()  [new]
mcureenab
Member

Откуда: Murmansk
Сообщений: 5964
RA\/EN
вот ссылочки на тему для размышлений:
http://leo.yuriev.ru/random
http://vx.netlux.org/lib/vsl04.html


Добавлю, умный дядька Кнут этой теме тоже уделил своё драгоценное время в одной из глав "Искусства програмирования".
26 апр 07, 17:37    [4075551]     Ответить | Цитировать Сообщить модератору
 Re: Предпятничная задачка. На тему случайных выборок и нераскрытой темы RANDOM()  [new]
mr.silly
Member

Откуда:
Сообщений: 33
Господа, лучше расскажите зачем в этом зопросе использовать хинт /*+ materialize */, да и вообще когда и зачем он используется ? :)
Или ссылку дайте.

Спасибо :)
26 апр 07, 17:45    [4075601]     Ответить | Цитировать Сообщить модератору
 Re: Предпятничная задачка. На тему случайных выборок и нераскрытой темы RANDOM()  [new]
mcureenab
Member

Откуда: Murmansk
Сообщений: 5964
RA\/EN
P.S. Сидование таймстампом (младшими битами) тоже, кстати, является результатом действия термодинамической системы , запускающей процесс.


В том то и дело, что младшими битами. Т.е. количество возможных последовательностей сильно ограничено (хотя для многих приложений этого должно быть достаточно). Положение улучшается с повышением частоты тиков таймера. В моём случе таймер тикал на частоте соизмеримой с частотой процессора (сотни MHz). За время розыгрыша прокручивались десятки миллионов вариантов так что на выборке из десятка тысяч претендентов у всех шансы были практически одинаковыми.

В своё время игрался с генераторами псевдослучайных чисел. На одном из них обнаружил, что в 3х мерном пространстве (x, y, цвет) образуется вполне регулярная ячеестая структура. Вот тебе и рулетка!
26 апр 07, 17:57    [4075680]     Ответить | Цитировать Сообщить модератору
 Re: Предпятничная задачка. На тему случайных выборок и нераскрытой темы RANDOM()  [new]
mcureenab
Member

Откуда: Murmansk
Сообщений: 5964
mr.silly
Господа, лучше расскажите зачем в этом зопросе использовать хинт /*+ materialize */, да и вообще когда и зачем он используется ? :)
Или ссылку дайте.

Спасибо :)


Какой то недокументированный хинт.
26 апр 07, 18:03    [4075710]     Ответить | Цитировать Сообщить модератору
 Re: Предпятничная задачка. На тему случайных выборок и нераскрытой темы RANDOM()  [new]
mr.silly
Member

Откуда:
Сообщений: 33
mcureenab
mr.silly
Господа, лучше расскажите зачем в этом зопросе использовать хинт /*+ materialize */, да и вообще когда и зачем он используется ? :)
Или ссылку дайте.

Спасибо :)


Какой то недокументированный хинт.


Ну.. по крайней мере в документации к 10 я его не нашёл
26 апр 07, 18:07    [4075732]     Ответить | Цитировать Сообщить модератору
 Re: Предпятничная задачка. На тему случайных выборок и нераскрытой темы RANDOM()  [new]
Timm
Member

Откуда: Moscow, Ё-burg
Сообщений: 3696
тынц
26 апр 07, 18:09    [4075748]     Ответить | Цитировать Сообщить модератору
 Re: Предпятничная задачка. На тему случайных выборок и нераскрытой темы RANDOM()  [new]
Splain
Member

Откуда: Череповец
Сообщений: 924
Elic
order by dbms_random.value) where rownum = 1


Метод хороший. Но, к сожалению, универсальный. Основной его недостаток - требуется получение всей выборки данных для того, чтобы в результате отфильтровать 1 строку данных.

Рассмотрим одну частную задачу, когда такой способ напрямую использовать нельзя. Предположим, имеется достаточно большая таблица данных, состоящая из 1 миллиона блоков данных. Требуется случайным образом выбрать некоторую порцию данных из этой таблицы. Читать каждый раз всю таблицу не представляется возможным по причине огромной длительности процесса.

В этом случае можно взять на "вооружение" один из приемов, используемых механизмами сбора статистики. Выражением sample block (%blocks) задается требуемый процент считываемых блоков данных.

Вот что получилось у меня:

SQL> select /*+ FULL(a) */ count(*) from aaa SAMPLE BLOCK (6.6432823129251700680272108843537e-5) a;

  COUNT(*)                                                                      
----------                                                                      
         0                                                                      

Execution Plan
----------------------------------------------------------                      
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1)                     
   1    0   SORT (AGGREGATE)                                                    
   2    1     PARTITION RANGE (ALL)                                             
   3    2       TABLE ACCESS (SAMPLE) OF 'AAA' (Cost=4 Card=7)            

Statistics
----------------------------------------------------------                      
          0  recursive calls                                                    
          0  db block gets                                                      
         99  consistent gets                                                    
          0  physical reads                                                     

SQL> select /*+ FULL(a) */ count(*) from aaa SAMPLE BLOCK (6.6432823129251700680272108843537e-5) a;

  COUNT(*)                                                                      
----------                                                                      
         0                                                                      

Execution Plan
----------------------------------------------------------                      
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1)                     
   1    0   SORT (AGGREGATE)                                                    
   2    1     PARTITION RANGE (ALL)                                             
   3    2       TABLE ACCESS (SAMPLE) OF 'AAA' (Cost=4 Card=7)            


Statistics
----------------------------------------------------------                      
          0  recursive calls                                                    
          0  db block gets                                                      
         99  consistent gets                                                    
          0  physical reads                                                     

SQL> select /*+ FULL(a) */ count(*) from aaa SAMPLE BLOCK (6.6432823129251700680272108843537e-5) a;

  COUNT(*)                                                                      
----------                                                                      
        13                                                                      

Execution Plan
----------------------------------------------------------                      
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1)                     
   1    0   SORT (AGGREGATE)                                                    
   2    1     PARTITION RANGE (ALL)                                             
   3    2       TABLE ACCESS (SAMPLE) OF 'AAA' (Cost=4 Card=7)            

Statistics
----------------------------------------------------------                      
          0  recursive calls                                                    
          0  db block gets                                                      
        102  consistent gets                                                    
         48  physical reads                                                     

Подозреваю, что в моем примере партиционированность не позволила стабилизировать результат. В этом случае нужно просто увеличить процент.

Возможность недокументированная. Проверял на 9.2.0.8. В других версиях может не работать. Подсмотрел как обычно у Льюиса.
26 апр 07, 18:14    [4075776]     Ответить | Цитировать Сообщить модератору
 Re: Предпятничная задачка. На тему случайных выборок и нераскрытой темы RANDOM()  [new]
mr.silly
Member

Откуда:
Сообщений: 33
Timm
тынц


Спасибо большое, но я тоже умею пользоваться поиском :)
26 апр 07, 18:24    [4075825]     Ответить | Цитировать Сообщить модератору
 Re: Предпятничная задачка. На тему случайных выборок и нераскрытой темы RANDOM()  [new]
Timm
Member

Откуда: Moscow, Ё-burg
Сообщений: 3696
mr.silly
Спасибо большое, но я тоже умею пользоваться поиском :)

Не умеешь.
26 апр 07, 18:26    [4075829]     Ответить | Цитировать Сообщить модератору
 Re: Предпятничная задачка. На тему случайных выборок и нераскрытой темы RANDOM()  [new]
Elic
Member

Откуда:
Сообщений: 30178
Splain
from aaa SAMPLE BLOCK
Возможность недокументированная.
RTFM sample_clause (FAQ) + STFF :)
26 апр 07, 18:28    [4075842]     Ответить | Цитировать Сообщить модератору
 Re: Предпятничная задачка. На тему случайных выборок и нераскрытой темы RANDOM()  [new]
RA\/EN
Member

Откуда:
Сообщений: 3663
Splain
...

Не покатит по простой причине - если блоке №123 одна запись, а в блоке №321 - 10 записей, то при таком подходе при начальном условии "одна строка" строка из блока №123 будет выводиться в 10 раз чаще, чем любая из строк блока №321 (это еще не учли то, что мы не знаем, как выбирает блоки SAMPLE )
И, естетственно, самое первое условие задачи: "Есть запрос" - к нему SAMPLE не прикрутишь уже...
26 апр 07, 18:50    [4075928]     Ответить | Цитировать Сообщить модератору
 Re: Предпятничная задачка. На тему случайных выборок и нераскрытой темы RANDOM()  [new]
Splain
Member

Откуда: Череповец
Сообщений: 924
RA\/EN

Не покатит по простой причине - если блоке №123 одна запись, а в блоке №321 - 10 записей, то при таком подходе при начальном условии "одна строка" строка из блока №123 будет выводиться в 10 раз чаще, чем любая из строк блока №321 (это еще не учли то, что мы не знаем, как выбирает блоки SAMPLE )


У меня один блок не получилось выбрать :( А когда мы выбираем 1% блоков, затем сортируем их по dbms_random.value и выбираем несколько строк - разницы не будет. Закон больших чисел :)

Кстати, несколько более показательный тест:


SQL> select count(*) from aaa SAMPLE (1) a;

  COUNT(*)                                                                      
----------                                                                      
    110299                                                                      

Execution Plan
----------------------------------------------------------                      
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=11552 Card=1)                 
   1    0   SORT (AGGREGATE)                                                    
   2    1     PARTITION RANGE (ALL)                                             
   3    2       INDEX (SAMPLE FAST FULL SCAN) OF 'AA_ID' (UNIQUE) (Cos          
          t=11552 Card=110073)                                                  
                                                                              
Statistics
----------------------------------------------------------                      
      55144  consistent gets                                                    
      68467  physical reads                                                     

SQL> select /*+ FULL(a) */ count(*) from aaa SAMPLE (1) a;

  COUNT(*)                                                                      
----------                                                                      
    110547                                                                      

Execution Plan
----------------------------------------------------------                      
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=145501 Card=1)                
   1    0   SORT (AGGREGATE)                                                    
   2    1     PARTITION RANGE (ALL)                                             
   3    2       TABLE ACCESS (SAMPLE) OF 'AAA' (Cost=145501 Card          
          =110073)                                                              
                                                                                
Statistics
----------------------------------------------------------                      
     597902  consistent gets                                                    
    1174214  physical reads                                                     

SQL> select count(*) from aaa SAMPLE BLOCK (1) a;
  COUNT(*)                                                                      
----------                                                                      
    105764                                                                      

Execution Plan
----------------------------------------------------------                      
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1206 Card=1)                  
   1    0   SORT (AGGREGATE)                                                    
   2    1     PARTITION RANGE (ALL)                                             
   3    2       INDEX (SAMPLE FAST FULL SCAN) OF 'AA_ID' (UNIQUE) (Cos          
          t=1206 Card=110073)                                                   
                                                                                
Statistics
----------------------------------------------------------                      
        705  consistent gets                                                    
       9475  physical reads                                                     

SQL> select /*+ FULL(a) */ count(*) from aaa SAMPLE BLOCK (1) a;

  COUNT(*)                                                                      
----------                                                                      
    112945                                                                      

Execution Plan
----------------------------------------------------------                      
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=21066 Card=1)                 
   1    0   SORT (AGGREGATE)                                                    
   2    1     PARTITION RANGE (ALL)                                             
   3    2       TABLE ACCESS (SAMPLE) OF 'AAA' (Cost=21066 Card=          
          110073)                                                               
                                                                                
Statistics
----------------------------------------------------------                      
      13053  consistent gets                                                    
     176516  physical reads                                                     

RA\/EN

И, естетственно, самое первое условие задачи: "Есть запрос" - к нему SAMPLE не прикрутишь уже...


С этим не поспоришь. Это просто способ ограничить количество считываемых блоков данных. Самостоятельная ценность его не очень высока. Однако в совокупности с другими способами возможность может быть весьма полезна.
27 апр 07, 09:02    [4076977]     Ответить | Цитировать Сообщить модератору
 Re: Предпятничная задачка. На тему случайных выборок и нераскрытой темы RANDOM()  [new]
Splain
Member

Откуда: Череповец
Сообщений: 924
mcureenab

Но это, ИМХО жульническая лотерея. dbms_random.value возвращает последовательность псевдослучайных чисел. Наверняка некоторые записи будут выпадать чаще других, да и на больших выборках будет тормозить.


Мне кажется псевдослучайность на задаче выбрать случайную строку не будет играть существенной роли.

Мы сейчас выбираем ее используя равномерный закон распределения. Однако в реальной ситуации закон распределения не будет равномерным и не будет нормальным. Он скорее всего вообще не может быть описан известными законами распределения. Так что придется описывать его гистограммами. В этом случае если мы говорим о точности моделирования, существенным будет количество измерений и количество диапазонов гистограммы. А влияние ограничений пакета dbms_random будет минимальным.
27 апр 07, 09:13    [4077020]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить