PostgreSQL | Как выбрать 10 случайных записей из таблицы с уникальными значениями по условию?

Пример SQL-запроса:

Такой вариант сильно зависит от количества повторений в idd

SELECT DISTINCT ON (idd) * FROM (SELECT * FROM a.t_all_dp WHERE pagestatus = 0 ORDER BY RANDOM() LIMIT 100) AS ee LIMIT 10;


Этот вариант более правильный (более случайный), так как не зависит от повторений в idd

SELECT * FROM (SELECT DISTINCT idd FROM a.t_all_dp WHERE pagestatus = 0) AS ee ORDER BY RANDOM() LIMIT 10;

Пример таблицы из которой выбираются данные:

Таблица с 22 записями в которых повторяются значения столбца idd в PostgreSQL

Задача

Из нашей таблицы мы хотим отобрать ровно 10 записей в которых:

Значения IDD будут случайными и собранными из разных частей таблицы. Не последовательные, а именно случайные!
Все 10 значений IDD будут уникальными. Ни один не должен повториться.
Одно из условий отборов записей это pagestatus = 0
Мы хотим сохранить в выводе все столбцы таблицы как они есть

Решение для первой команды

В таблице тремя разноцветными блоками подсвечены повторяющиеся значения. Они могут встречаться в любых местах таблицы.

Для простоты восприятия наша таблица имеет всего 22 записи. Но идея задачи в том, чтобы выдавать «10 случайных записей» из миллиардов записей этой таблицы.

В решении задачи мы сперва отталкиваемся от условия. Нам важно отобрать какое-то количество случайных записей, в которых «pagestatus = 0«. Для нас это первый шаг. Чем больше таких записей мы отберём из разных участков нашей таблицы, тем выше вероятность того, что мы получим повторяющиеся значения по столбцу idd. Но кроме частых повторений, мы также получим и больше вариантов самих значений в idd.

Это значит, что из 100 не уникальных idd, мы с большей вероятностью сможем отобрать 10 уникальных idd.

Если пытаться написать ORDER BY RANDOM() на одном уровне запроса с DISTINCT, то получим ошибку вида:

В конструкции SELECT DISTINCT выражения ORDER BY должны быть в списке выборки

Из-за этого мы сначала создаём подзапрос:

SELECT * FROM a.t_all_dp WHERE pagestatus = 0 ORDER BY RANDOM() LIMIT 100;

У нас данных мало, поэтому мы получим все 22 записи и так. Но при большом количестве записей ситуация будет иной (более ощутимой).

3 вызова на получение случайных значений из таблицы в PostgreSQL

Важно в подзапросе то, что он 100% перемешивает записи, найденные во всей таблице по условию.

Каждый вызов этого подзапроса венёт разные ID записей.

Перемешиваем записи случайнм образом по условию в PostgreSQL - вызов 1 — Перемешиваем записи случайнм образом по условию в PostgreSQL — вызов 1

Перемешиваем записи случайнм образом по условию в PostgreSQL - вызов 2 — Перемешиваем записи случайнм образом по условию в PostgreSQL — вызов 2

Перемешиваем записи случайнм образом по условию в PostgreSQL - вызов 3 — Перемешиваем записи случайнм образом по условию в PostgreSQL — вызов 3

В первом: 22, 6, 14, 5 …

Во втором: 2, 8, 15, 16 …

В третьем: 17, 3, 13, 22 …

После этого подзапросу нужно задать псевдоним:

AS ee

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

По сути мы получаем небольшую таблицу в максимум 100 случайных записей. И уже по этой таблице мы можем «схлопнуть» записи до уникальности при помощи DISTINCT.

Чтобы получить ВСЕ столбцы таблицы, нам нужно использовать DISTINCT в связке с ON с указанием столбца для «схлопывания» в круглых скобках, а также значок звёздочки *:

SELECT DISTINCT ON (idd) * FROM

После мы пишем наш подзапрос в круглых скобках и только после этого дописываем LIMIT 10;

На 22 записях, где уникальных всего 15, значения выборки будут похожими по набору idd, но разными по id. Это происходит потому, что перед уникализацией значений столбца происходит сортировка.

Делаем 3 вызова:

SELECT DISTINCT ON (idd) * FROM (SELECT * FROM a.t_all_dp WHERE pagestatus = 0 ORDER BY RANDOM() LIMIT 100) AS ee LIMIT 10;
SELECT DISTINCT ON (idd) * FROM (SELECT * FROM a.t_all_dp WHERE pagestatus = 0 ORDER BY RANDOM() LIMIT 100) AS ee LIMIT 10;
SELECT DISTINCT ON (idd) * FROM (SELECT * FROM a.t_all_dp WHERE pagestatus = 0 ORDER BY RANDOM() LIMIT 100) AS ee LIMIT 10;

Отобрали 10 случайных уникальных записей по столбцу idd из всей таблицы по условию в PostgreSQL - Вызов 1 — Отобрали 10 случайных уникальных записей по столбцу idd из всей таблицы по условию в PostgreSQL — Вызов 1

Отобрали 10 случайных уникальных записей по столбцу idd из всей таблицы по условию в PostgreSQL - Вызов 2 — Отобрали 10 случайных уникальных записей по столбцу idd из всей таблицы по условию в PostgreSQL — Вызов 2

Отобрали 10 случайных уникальных записей по столбцу idd из всей таблицы по условию в PostgreSQL - Вызов 3 — Отобрали 10 случайных уникальных записей по столбцу idd из всей таблицы по условию в PostgreSQL — Вызов 3

В каждом из вызовов мы получаем разные наборы идентификаторов ID. Чем больше данных будет в таблице тем сильнее будет отличаться наборы не только по ID, но и по IDD.

Информационные ссылки

Официальный сайт WEB-оболочки pgAdmin — https://www.pgadmin.org

Официальный сайт СУБД PostgreSQL — https://www.postgresql.org