Выберите случайную строку из таблицы sqlite

У меня есть sqlite таблица со следующей схемой:

CREATE TABLE foo (bar VARCHAR)

Я использую эту таблицу в качестве хранилища для списка строк.

Как выбрать случайную строку из этой таблицы?

Источник

7 ответов

Посмотрите на Выбор случайной строки из таблицы SQLite

SELECT * FROM table ORDER BY RANDOM() LIMIT 1;
Источник

Следующие решения намного быстрее, чем у Anktastic (count(*) стоит дорого, но если вы можете его кэшировать, тогда разница не должна быть такой большой), что само по себе намного быстрее, чем "order by random()" когда у вас есть большое количество строк, хотя у них есть несколько неудобств.

Если ваши rowids довольно упакованы (то есть, несколько удалений), то вы можете сделать следующее (используя (select max(rowid) from foo)+1 вместо max(rowid)+1 дает лучшую производительность, как объяснено в комментариях):

select * from foo where rowid = (abs(random()) % (select (select max(rowid) from foo)+1));

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

select * from foo where rowid = (abs(random()) % (select (select max(rowid) from foo)+1)) or rowid = (select max(rowid) from node) order by rowid limit 1;

Это второе решение не идеально: распределение вероятностей выше в последнем ряду (тот, который имеет наибольший идентификатор строки), но если вы часто добавляете материал в таблицу, он станет движущейся целью, и распределение вероятностей должно быть намного лучше.

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

create table random_foo(foo_id);

Затем, периодически, снова заполните таблицу random_foo

delete from random_foo;
insert into random_foo select id from foo;

И чтобы выбрать случайную строку, вы можете использовать мой первый метод (здесь нет дыр). Конечно, у этого последнего метода есть некоторые проблемы с параллелизмом, но пересборка random_foo - это операция сопровождения, которая вряд ли будет происходить очень часто.

Тем не менее, еще один способ, который я недавно нашел в списке рассылки, - это установить триггер на удаление, чтобы переместить строку с наибольшим идентификатором строки в текущую удаленную строку, чтобы не осталось никаких дыр.

Наконец, обратите внимание, что поведение rowid и автоинкремента первичного ключа целых чисел не идентично (с rowid, когда вставляется новая строка, выбирается max(rowid)+1, тогда как это самое высокое значение из всех когда-либо замеченных + 1 для первичный ключ), поэтому последнее решение не будет работать с автоинкрементом в random_foo, но другие методы будут работать.

Источник

Вам нужно поставить "order by RANDOM()" на ваш запрос.

Пример:

select * from quest order by RANDOM();

Давайте посмотрим полный пример

  1. Создать таблицу: 
CREATE TABLE  quest  (
    id  INTEGER PRIMARY KEY AUTOINCREMENT,
    quest TEXT NOT NULL,
    resp_id INTEGER NOT NULL
);

Вставка некоторых значений:

insert into quest(quest, resp_id) values ('1024/4',6), ('256/2',12), ('128/1',24);

Выбор по умолчанию:

select * from quest;

| id |   quest  | resp_id |
   1     1024/4       6
   2     256/2       12
   3     128/1       24
--

Случайный выбор:

select * from quest order by RANDOM();
| id |   quest  | resp_id |
   3     128/1       24
   1     1024/4       6
   2     256/2       12
--
* Каждый раз, когда вы выбираете, порядок будет отличаться.

Если вы хотите вернуть только одну строку

select * from quest order by RANDOM() LIMIT 1;
| id |   quest  | resp_id |
   2     256/2       12
--
* Каждый раз, когда вы выбираете, возврат будет отличаться.

Источник

Как насчет:

SELECT COUNT(*) AS n FROM foo;

затем выберите случайное число m в [0, n) и

SELECT * FROM foo LIMIT 1 OFFSET m;

Вы даже можете сохранить первое число (n) где-нибудь и обновлять его только тогда, когда изменяется количество баз данных. Таким образом, вам не нужно каждый раз выполнять команду SELECT COUNT.

Источник

Вот модификация решения @ank:

SELECT * 
FROM table
LIMIT 1 
OFFSET ABS(RANDOM()) % MAX((SELECT COUNT(*) FROM table), 1)

Это решение также работает для индексов с пробелами, потому что мы рандомизируем смещение в диапазоне [0, количество). MAX используется для обработки дела с пустой таблицей.

Вот простые результаты теста для таблицы с 16k строк:

sqlite> .timer on
sqlite> select count(*) from payment;
16049
Run Time: real 0.000 user 0.000140 sys 0.000117

sqlite> select payment_id from payment limit 1 offset abs(random()) % (select count(*) from payment);
14746
Run Time: real 0.002 user 0.000899 sys 0.000132
sqlite> select payment_id from payment limit 1 offset abs(random()) % (select count(*) from payment);
12486
Run Time: real 0.001 user 0.000952 sys 0.000103

sqlite> select payment_id from payment order by random() limit 1;
3134
Run Time: real 0.015 user 0.014022 sys 0.000309
sqlite> select payment_id from payment order by random() limit 1;
9407
Run Time: real 0.018 user 0.013757 sys 0.000208
Источник 
SELECT   bar
FROM     foo
ORDER BY Random()
LIMIT    1
Источник

Я предложил следующее решение для больших баз данных sqlite3:

SELECT * FROM foo WHERE rowid = abs(random()) % (SELECT max(rowid) FROM foo) + 1;

Функция abs(X) возвращает абсолютное значение числового аргумента X.

Функция random() возвращает псевдослучайное целое число от -9223372036854775808 до +9223372036854775807.

Оператор% выводит целочисленное значение своего левого операнда по модулю правого операнда.

Наконец, вы добавляете +1, чтобы rowid равнялся 0.

Источник
Другие вопросы по тегам