Если порядок нужно «перетасовать», но не случайно...

(Обновление: см. другой мой ответ для получения более гибкого и рандомизируемого решения.)

Вы говорите «случайный» порядок, который вы получаете при вызове ORDER BY random() — для каждой строки PostgreSQL вызывает random(), получает значение и использует его, чтобы решить, как сортировать эту строку в наборе результатов.

Чтобы сделать это повторяемым, вы должны возиться с семенами. Это неприятно. Согласно документам:

эффекты будут сохраняться до конца сеанса, если только они не будут переопределены другим SET

Я думаю, это означает, что при использовании пула соединений setseed изменяет соединение для следующего процесса, который использует это соединение.

А по модулю?

У меня есть случай, когда мне не нужна настоящая случайность. Мои критерии:

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

Например, это было бы хорошо:

• Listing 1
  • page 1: items 1, 4
  • страница 2: пункты 3, 2
• Listing 2 (different user, or same user coming back later)
  • page 1: items 3, 1
  • стр. 2: пункты 2, 4

Чтобы получить что-то подобное, модуль работает хорошо. Например, ORDER BY id % 7, id для всех страниц запроса 1 и ORDER BY id % 11, id для всех страниц запроса 2. То есть для каждой строки разделите ее идентификатор на модуль и отсортируйте по остатку. В строках с одинаковым остатком отсортируйте по идентификатору (чтобы обеспечить стабильность сортировки).

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

Вы можете увидеть, как это может работать для вашей базы данных, например:

echo "select id, id % 7 FROM my_table ORDER BY id % 77, id" | psql my_db > sort.txt

Простой модуль, вероятно, даст вам наибольшее разнообразие. И если ваши идентификаторы начинаются с 1 (так что % 77 заставит первые 77 строк возвращаться в обычном порядке), вы можете вместо этого попробовать выполнить модуль в поле метки времени. Например:

ORDER BY (extract(epoch from inserted_at)* 100000)::bigint % 77

Но вам понадобится индекс функции, чтобы сделать это производительным.

С помощью этой техники union all случайный порядок повторяется

select a, b
from (
    select setseed(0.1), null as a, null as b

    union all

    select null, a, b
    from t

    offset 1
) s
order by random()
offset 0
limit 5
;

Вы можете использовать setseed(dp) для заполнения random() начальным числом в [-1.0, 1.0]. Например.:

engine=> SELECT SETSEED(0.16111981);
 setseed 
---------

(1 row)

engine=> SELECT RANDOM();
      random       
-------------------
 0.205839179921895
(1 row)

engine=> SELECT RANDOM();
      random       
-------------------
 0.379503262229264
(1 row)

engine=> SELECT RANDOM();
      random       
-------------------
 0.268553872592747
(1 row)

engine=> SELECT RANDOM();
      random       
-------------------
 0.788029655814171
(1 row)

И, конечно же, каждый раз при повторном заполнении вы получите один и тот же результат:

engine=> SELECT SETSEED(0.16111981), RANDOM();
 setseed |      random       
---------+-------------------
         | 0.205839179921895
(1 row)

engine=> SELECT SETSEED(0.16111981), RANDOM();
 setseed |      random       
---------+-------------------
         | 0.205839179921895
(1 row)

engine=> SELECT SETSEED(0.16111981), RANDOM();
 setseed |      random       
---------+-------------------
         | 0.205839179921895
(1 row)

engine=> SELECT SETSEED(0.16111981), RANDOM();
 setseed |      random       
---------+-------------------
         | 0.205839179921895

(уточнение: вывод был скопирован из psql, движок - это имя моей базы данных)

Укажите точные идентификаторы строк (рандомизированные заранее)

Этот запрос снова даст вам строки с идентификаторами 4, 2, 1 и 4 именно в таком порядке.

SELECT items.id, items.name
FROM items
-- unnest expands array values into rows
INNER JOIN unnest(ARRAY[4,2,1,4]) AS item_id
ON items.id = item_id

урожаи

 id |     name
----+---------------
  4 | Toast Mitten
  2 | Pickle Juicer
  1 | Horse Paint
  4 | Toast Mitten

Зная это, вы можете указать идентификаторы, которые должны быть включены на каждой странице, как хотите.

Например, вы можете SELECT id FROM items ORDER BY random() разбить список на «страницы» (скажем) по 5 идентификаторов в каждой и хранить их в памяти приложения, или в Redis, или где-то еще. Для каждой запрошенной страницы вы должны выполнить приведенный выше запрос с правильной страницей идентификаторов.

Вариации:

• Для настоящей случайности вы можете включить pgcrypto и ORDER BY gen_random_uuid().
• Вы можете опустить ORDER BY и перетасовать идентификаторы в памяти на вашем языке программирования.
• Вы можете создать другую перетасовку для каждого пользователя или дня.

Очень быстрый и грязный вариант для таблицы с первичными ключами, которые являются UUID (или каким-либо другим видом хеша), — упорядочить результаты по подстрокам UUID. В вашем приложении случайным образом сгенерируйте несколько положительных целых чисел от 1 до 36 и используйте эти позиции в своем запросе.

Например, если мне нужно пять чисел и сгенерировано {23, 12, 35, 16, 3}, я бы использовал:

select * from (
  select
    organization_id,
    substring(cast(organization_id as text) from 23 for 1) as o1,
    substring(cast(organization_id as text) from 12 for 1) as o2,
    substring(cast(organization_id as text) from 35 for 1) as o3,
    substring(cast(organization_id as text) from 16 for 1) as o4,
    substring(cast(organization_id as text) from 3 for 1) as o5
  from channels_organizations
) t order by o1, o2, o3, o4, o5;

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

organization_id                       o1  o2  o3  o4  o5
a059cd76-9d91-48db-8982-986fcd217b2a   2   9   2   8   5
3ce14f26-3e56-46eb-9a74-22862cc3ed4e   4   5   4   6   e
8e115b7e-2e7e-480e-9bc6-296deff3ed87   6   7   8   8   1
e1969c52-5028-47da-92ea-9f2918dcbf4d   a   2   4   7   9
42eb7292-e881-4a04-b83a-3bf78548dab4   a   8   b   a   e
e8a33112-532f-4fec-b25b-416c5409ac7e   b   2   7   f   a
a763efaa-79a4-4cfa-92bc-803ebc5ff221   c   a   2   c   6
581cae5b-5000-4aa6-837d-002ccf806e28   d   0   2   a   1
6b0ed7b4-b44d-4a51-910f-f3f2f0354d55   f   4   5   a   0
9369a547-f7e0-43e7-96ef-62bf631a0f0b   f   e   0   3   6

В этом примере я выбираю индекс в каждой группе формата UUID и перемешиваю их перед отправкой в ​​БД.

Вы можете упростить это, если вас меньше беспокоит псевдослучайность, используя только один индекс подстроки, хотя вы хотели бы увеличить длину подстроки (n в from x for n) по крайней мере до 3, так как есть шанс, что вы в конечном итоге тире в вашей подстроке.