Случайность — маска неузнанной закономерности
Александр Волков

Как-то я писал о том как быстро можно сделать выборку в MySQL по большой таблице в случайном порядке не используя ORDER BY RAND(), теперь этот же вопрос всплыл на Postgre. Если вы не знаете, то ORDER BY RAND достаточно медленная операция из-за того, что сортируется фактически вся таблица. Некоторые советовали всё-таки обычную сортировку или чуть-изменённую с использованием Primary Key, но я на своём опыте убедился что конструкция IN всегда медленней чем EXISTS.

Электроны и дырки

Вообще задача подразумевает выборку и нескольких случайных рядов, но она сводится к выборке одного ряда, а точней ID. Самый простой способ - взять минимум (MIN или 1), максимум (MAX или значение Sequence) и случайным образом тыкнуть в промежутке. Задача чем-то напоминает процесс хэширования. И всё хорошо покуда всё идёт по порядку и ряды не удаляются. Как только в базе возникают пустоты (а они возникают вероятностью пропорционально размеру проекта), то получается что ряд не находится.

Использовать наиболее близкий к этой дырке ID'шник мы не можем из-за неравномерного распределения вероятностей и не гарантированного присутсвия вообще каких-то данных если мы в конце. Хотя можно плюнуть..

Лотерея

Делать цикл с простукиванием каждой дыры тоже рискованно - вставит кто-нибудь гипер прыжок ID с 435 на 43600 и сложность алгоритма прыгнет основательно. 

Можно оторваться от ID'шек и внести случайны float-параметр. Такой лотерее дыры в ID уже неважны. Но  неравномерность вероятности всё-таки существует и в таком случае. Правда чем больше рядов, тем она незначительней, но тем больше места занимают эти лишние данные.

ALTER TABLE mytable ADD myrand DOUBLE PRECISION; --добавляем колонку
UPDATE mytable SET myrand = RANDOM(); --очень медленная процедура
ALTER TABLE mytable ALTER myrand SET NOT NULL DEFAULT RANDOM(); --ставим случайное значение для новых рядов по умолчанию
SELECT * FROM mytable WHERE myrand >= (SELECT RANDOM() OFFSET 0) ORDER BY myrand ASC LIMIT 1;

Горст и грабен

Ещё простое решение - использовать случайный OFFSET. Сразу минус - LIMIT конструкция не позволяет динамически задавать значения, но образно идея такая:

SELECT CEIL(RANDOM()*(SELECT COUNT(*) AS cnt FROM mytable)); --получим из базы offset
SELECT * FROM quotes LIMIT 1 OFFSET 43600; --и передадим в явном виде в запрос


LIMIT сразу перейдёт к нужному ряду - нет нужды в ID, а значит больше свободы. Единственный вопрос в скорости. Ведь используемая внутри COUNT функция - аггрегирующая, а в Postgre она отличается от обычного счётчика в MySQL. Ко всему прочему, как говорит документация - OFFSET может быть неэффективным в больших базах из-за того, что обработка предыдущих рядов всё-равно происходит.

Встала задача экспортирования данных из таблицы Firma в качестве строки. Если бы дело было в одноразовом копировании то проблемы особой и не-было бы - всегда есть клиентские программы, которые экспортируют хоть в Excel, хоть в текстовый файл какие угодно колонки. Но мне необходимо было это сделать при помощи SQL, потому что прямого доступа к live-базе данных у меня нет, а размер таблицы мне неизвестен.

Агрегирующие функции занимаются именно такой задачей - на входе у них массив данных, а на выходе - одно значение. Типичным примером являются функции COUNT, MAX, SUM. Postgre пошла по пути универсальности и позволяет пользователю самому создавать такие функции. Создаём CONCAT функцию:

CREATE AGGREGATE concat (
BASETYPE = text,
SFUNC = textcat,
STYPE = text,
INITCOND = ''
);

Теперь можно сразу получить строку с данными, но мне нужна сортировка по названию фирмы, но никакие вложенные запросы и ORDER BY этому не помогают:

SELECT CONCAT(name||';') FROM Firma;

Поэтому приходится изучить ещё одну новую для себя функциональность и создать Представление (View). View это виртуальная таблица, данные которой формируются на лету. Она может использоваться и в качестве ограничения прав на изменение родительской таблицы. Представления можно кэшировать - тн. материализация. Но сейчас оно поможет реализовать обычную сортировку:

CREATE VIEW FirmaSorted AS SELECT * FROM Firma ORDER BY name;

Остаётся сделать выборку, в которую я добавлю ещё одно поле, уберу последний символ-разделитель и сделаю UPDATE полученного значения в другую таблицу:

UPDATE tblLists SET strValue=(
SELECT TRIM(';' FROM (SELECT CONCAT(id||'=>'||name||';') FROM FirmaSorted))
) WHERE strName='companies';

Обычно в Postgre последовательности (sequence) обновляются естественно, но как и в случае с дупликатами, порой иногда хочется одним запросом восстановить правильный максимум .. Сначала я подумал что сработает обычный под-запрос типа

ALTER SEQUENCE sysusers_id_seq RESTART SELECT MAX(id)+1 FROM sysusers ; 

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


SELECT setval('sysusers_id_seq', (select max(id)+1 from sysusers))+1;

Теперь можно просто продублировать этот запрос для разных таблиц и последовательностей и беззаботно продолжать работу.. 

PostgreSQL - крутая СУБД, потому что..

1. Она не принадлежит какой-либо компании, как например Oracle или mysql и распространяется по BSD-лицензии
2. Имеет поддержку продвинутых фишек - PL/pgSQL, триггеров. Теоретически это очень полезно при создании очень масштабной системы.
   
3. Транзакции по принципам ACID придают скорости - несколько запросов в одной транзакции выполняются как одна атомарная операция. А мультиверсионность помогает избежать блокировки таблиц.
   
4. Объектность. Таблица выступает фактически в виде класса, а ряды - в виде объектов, так же как классы могут наследоваться, так и таблицы могут наследоваться.
5. Система привилегий операций с таблицами, схожа с системой по принципу с Oracle.
6. Репликации, т.е. использование нескольких серверов даёт масштабируемость.

Переход на PostgreSQL как правило связан с обработкой больших объемов данных - когда число рядов переваливает миллион, то mysql начинает испытывать трудности, а Natural Join нескольких таких таблиц может привести вообще к падению сервера. Да и тесты на параллельность поддерживают Postgre. Энтузиастам может быть интересна же не столь масштабируемость, сколько объектность, позволяющая большую свободу, или если хотите - сложность, по сравнению с mysql.

При переходе из mysql, следует отметить различия, которые естественно следует предусмотреть:

• Различные типы данных. Нет datetime, зато есть возможность создать тучу своих типов.
• Отсутсвие autoincrement. Такая функция заменена наличием sequence, и в упрощённом виде может использоваться так:
  CREATE TABLE mynewtable (
id SERIAL
}

• pgAdmin III очень удобная консольная программа, а в качестве замены phpMyAdmin есть phpPgAdmin
• Аналогом DATE_FORMAT является to_char
• Заглавные буквы в названиях таблиц и полей по умолчанию переводятся в нижний регистр, это можно обойти используя двойные кавычки
• Форматом для LIMIT конструкции стал также поддерживаемый с mysql 4LIMIT # OFFSET #
• Просмотр запущенных процессов реализуется аналогом
  --show processlisеSELECT * from pg_stat_activity ;

Читайте также:

• Субъективная разница Postgre и MySQL
  
• Замена SQL_CALC_FOUND_ROWS из MySQL в Postgre