Выберите случайное значение на основе вероятности

Как выбрать случайную строку из базы данных на основе вероятности, назначенной для каждой строки.
Пример:

Make        Chance  Value
ALFA ROMEO  0.0024  20000
AUDI        0.0338  35000
BMW         0.0376  40000
CHEVROLET   0.0087  15000
CITROEN     0.016   15000
........

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

Будет ли сочетание rand() а также ORDER BY Работа? Если так, что является лучшим способом сделать это?

Источник

2 ответа

Решение

Вы можете сделать это с помощью rand() а затем с помощью накопительной суммы. Предполагая, что они составляют до 100%:

select t.*
from (select t.*, (@cumep := @cumep + chance) as cumep
      from t cross join
           (select @cumep := 0, @r := rand()) params
     ) t
where @r between cumep - chance and cumep
limit 1;

Заметки:

  • rand() вызывается один раз в подзапросе для инициализации переменной. Несколько звонков на rand() не желательны.
  • Существует небольшая вероятность того, что случайное число будет точно на границе между двумя значениями. limit 1 произвольно выбирает 1.
  • Это может быть сделано более эффективным путем остановки подзапроса, когда cumep > @r,
  • Значения не должны быть в каком-то определенном порядке.
  • Это может быть изменено для обработки шансов, когда сумма не равна 1, но это был бы другой вопрос.
Источник

У меня были те же требования, и я пытался написать запрос для SQL Sever. Мой ответ основан на запросе @gordon-linoff об использовании кумулятивной суммы для вероятностей.

      with rand AS (SELECT id, random=RAND() FROM Tiers)
   , cumsum AS (SELECT id, cum_sum=SUM(probability) Over (Order By id) from Tiers)
Select TOP 1 t.id,
             t.name,
             t.probability
FROM Tiers t
         inner join rand r on t.id = r.id
         inner join cumsum c on t.id = c.id
WHERE c.cum_sum - r.random >= 0
ORDER BY c.cum_sum - r.random ASC

Я написал полное резюме, чтобы проверить это на https://github.com/AlahmadiQ8/cumulative-probability-sql .

Вот визуальное объяснение кумулятивной суммы вероятности

Если случайное числоx = 0.45, то скорее всего нам следует вернуть товарBпотому чтоxявляется0.2 < x <= 0.5.

                                 x
|-----|-----|-----|-----|-----|-----|-----|-----|-----|-----|
           0.2               0.5                            1
└──────────┘ └───────────────┘ └───────────────────────────┘
      A              B                        C
Источник
Другие вопросы по тегам