Вывод случайных значений из БД с учетом коэффициента "частоты показов"

Синтаксис sql запроса вызывает большое сомнение.
Покажи какой запрос в итоге получается.

Включи вывод ошибок

 

Какова изначальная задача? Допустим в бд 10 записей без показов, выбрали 2 случайных, увеличили количество показов, следующая выборка должна быть из 8 оставшихся? И так далее до показа всех из 10?

 

Похоже на то, но при таком условии теряется всякий смысл рандомности (случайности)

 

@AlexandrS, там где есть коэффициент показов, там уже и не рандомность вовсе. Опять же я ещё не закончил с вопросами. Это только прелюдия.

 

Что-то думается мне, что можно написать куда проще алгоритм.

 

Сразу скажу, что топик по теме, но не совсем то, что нужно топикстартеру.

Короче, рандомная выборка с учетом веса записи в pgsql. Го.

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

PHP:

1. <?php
2.  
3. try {
4.     $db = new PDO("pgsql:dbname=test;host=localhost", "postgres", "");
5.     test($db);
6. } catch (PDOException $e) {
7.     echo $e->getMessage();
8. }
9.  
10. function create(PDO $db)
11. {
12.     $db->exec("alter sequence table_name_id_seq RESTART");
13.     $db->exec("TRUNCATE TABLE random_with_weight");
14.     for($i = 0; $i <= 1000; $i++) {
15.         $values = [];
16.         for ($k = 0; $k <= 1000; $k++) {
17.             $rand = rand(1, 5);
18.             $values[] = "($rand)";
19.         }
20.         $values = implode(',', $values);
21.         $db->exec("INSERT INTO random_with_weight (weight) VALUES $values");
22.         echo $i . "\r";
23.     }
24. }
25.  
26. function test(PDO $db) {
27.     $values = [];
28.     for ($i = 0; $i <= 100; $i++) {
29.         $result = $db->query("SELECT weight, count(weight) AS cnt FROM
30.                                  (SELECT * FROM random_with_weight ORDER BY random() / weight LIMIT 100) as rand
31.                                  GROUP BY weight")->fetchAll(PDO::FETCH_ASSOC);
32.         foreach ($result as $item) {
33.             if (!isset($values[$item['weight']])) {
34.                 $values[$item['weight']] = 0;
35.             }
36.             $values[$item['weight']] += $item['cnt'];
37.         }
38.         echo $i . "\r";
39.     }
40.     var_dump($values);
41. }

Как видно, в таблице вида (id, weight) ~1кк записей с весом от 1 до 5 включительно.

Результат ORDER BY random()

Код (Text):

1. array(5) {
2.   [1]=>
3.   int(2067)
4.   [2]=>
5.   int(2007)
6.   [3]=>
7.   int(2026)
8.   [4]=>
9.   int(1932)
10.   [5]=>
11.   int(2068)
12. }

Результат ORDER BY random() / weight

Код (Text):

1. array(5) {
2.  
3.   [1]=>
4.   int(694)
5.   [2]=>
6.   int(1376)
7.   [3]=>
8.   int(2021)
9.   [4]=>
10.   int(2632)
11.   [5]=>
12.   int(3377)
13. }

Собственно, что и требовалось - рандом присутствует, но в выборке чаще попадаются более "тяжелые" записи. Единственная проблема которая приходит в голову - это скорость, как известно, ORDER BY RAND не самый дешевый запрос. И действительно, random() выполняется за ~250мс, a random() / weight так вообще ~300мс. Долго, короче.

Код (Text):

1.  
2. Limit  (cost=60264.78..60265.03 rows=100 width=16) (actual time=296.310..296.328 rows=100 loops=1)
3.   ->  Sort  (cost=60264.78..62769.78 rows=1002001 width=16) (actual time=296.308..296.320 rows=100 loops=1)
4.         Sort Key: ((random() / (weight)::double precision))
5.         Sort Method: top-N heapsort  Memory: 29kB
6.         ->  Seq Scan on random_with_weight  (cost=0.00..21969.02 rows=1002001 width=16) (actual time=0.012..178.947 rows=1002001 loops=1)
7. Planning time: 0.089 ms
8. Execution time: 296.353 ms

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

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

Код (Text):

1. EXPLAIN ANALYSE SELECT id, weight FROM random_with_weight WHERE id < 200000 ORDER BY random() / weight LIMIT 100;
2.  
3. Limit  (cost=15610.31..15610.56 rows=100 width=16) (actual time=83.899..83.918 rows=100 loops=1)
4.   ->  Sort  (cost=15610.31..16106.52 rows=198482 width=16) (actual time=83.897..83.907 rows=100 loops=1)
5.         Sort Key: ((random() / (weight)::double precision))
6.         Sort Method: top-N heapsort  Memory: 29kB
7.         ->  Index Scan using table_name_pk on random_with_weight  (cost=0.42..8024.47 rows=198482 width=16) (actual time=0.018..58.861 rows=199999 loops=1)
8.               Index Cond: (id < 200000)
9. Planning time: 0.085 ms
10. Execution time: 83.941 ms

Ну или брать не всю таблицу, а лишь её части (https://habr.com/ru/post/266759/)

Код (Text):

1. EXPLAIN ANALYSE SELECT id, weight FROM random_with_weight TABLESAMPLE bernoulli(5) ORDER BY random() / weight LIMIT 100;
2.  
3. Limit  (cost=7225.54..7225.79 rows=100 width=16) (actual time=35.075..35.094 rows=100 loops=1)
4.   ->  Sort  (cost=7225.54..7350.79 rows=50100 width=16) (actual time=35.074..35.089 rows=100 loops=1)
5.         Sort Key: ((random() / (weight)::double precision))
6.         Sort Method: top-N heapsort  Memory: 29kB
7.         ->  Sample Scan on random_with_weight  (cost=0.00..5310.75 rows=50100 width=16) (actual time=0.013..28.675 rows=50011 loops=1)
8.               Sampling: bernoulli ('5'::real)
9. Planning time: 0.068 ms
10. Execution time: 35.121 ms

Код (Text):

1. EXPLAIN ANALYSE SELECT id, weight FROM random_with_weight TABLESAMPLE system(5) ORDER BY random() / weight LIMIT 100;
2.  
3. Limit  (cost=3679.54..3679.79 rows=100 width=16) (actual time=16.768..16.784 rows=100 loops=1)
4.   ->  Sort  (cost=3679.54..3804.79 rows=50100 width=16) (actual time=16.767..16.778 rows=100 loops=1)
5.         Sort Key: ((random() / (weight)::double precision))
6.         Sort Method: top-N heapsort  Memory: 29kB
7.         ->  Sample Scan on random_with_weight  (cost=0.00..1764.75 rows=50100 width=16) (actual time=0.014..9.792 rows=50172 loops=1)
8.               Sampling: system ('5'::real)
9. Planning time: 0.068 ms
10. Execution time: 16.816 ms

Но тут уже надо понимать, что мы имеем дело с псевдослучайностью и лишь с кусками таблицы. К примеру, если количество записей с weight=5 существенно меньше остальных, то в выборке их может и не появиться вовсе, потому как вес вроде способствует более частому появлению, но в изначальную выборку он просто не попал.

Короче, возможны варианты )

Такие дела.

 

Ты что-то больно мудреное замутил для 2 случайных значений из базы. Касательно кода:

PHP:

1. "UPDATE prases SET ratio = $ratio WHERE (phrase = ${'$q5'.$i})";

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

Насколько я смог догадаться тебе нужно выводить 2 наиболее редких по показам сообщения. Самые редкие сообщения имеют самые низкие показы, зачем тебе тут вообще ratio? Лишнее поле. Дальше задача сводится к тому, чтобы показать 2 сообщения из таблицы отсортированной по возрастанию показов. Вот тебе простые и быстрые запросы.

PHP:

1. $mysqli = new mysqli("localhost", "my_user", "my_password", "world");
2. $sql = "SELECT * FROM table ORDER BY counts ASC LIMIT 2"; /* этот простой запрос покажет 2 самых редких сообщений */
3. $res = $mysqli->query($sql);
4. /* предположим тут ты сложишь номера показанных сообщений */
5. $string = implode(", ", $ids); /*это расставит значения в строку через запятую*/
6. $update = "UPDATE table SET counts = counts + 1 WHERE id in($string)"; /*увеличиваешь показы для показанных сообщений*/