[sqlite] sqlite 테이블에서 임의의 행 선택

다음 솔루션은 anktastic보다 훨씬 빠릅니다 (count (*)는 비용이 많이 들지만 캐시 할 수 있다면 차이가 크지 않아야합니다). 자체적으로 “order by random ()”보다 훨씬 빠릅니다. 많은 수의 행이 있지만 몇 가지 불편한 점이 있습니다.

rowid가 다소 압축 된 경우 (즉, 삭제가 거의 없음) 다음을 수행 할 수 있습니다 ( 주석에 설명 된대로 (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 * 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;

이 두 번째 솔루션은 완벽하지 않습니다. 확률 분포는 마지막 행 (가장 높은 rowid를 가진 행)에서 더 높지만 테이블에 항목을 자주 추가하면 이동 대상이되고 확률 분포는 다음과 같아야합니다. 훨씬 낫다.

또 다른 해결책은 구멍이 많은 테이블에서 임의의 항목을 자주 선택하는 경우 임의의 순서로 정렬 된 원래 테이블의 행을 포함하는 테이블을 만들 수 있습니다.

create table random_foo(foo_id);

그런 다음 주기적으로 random_foo 테이블을 다시 채 웁니다.

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

임의의 행을 선택하려면 첫 번째 방법을 사용할 수 있습니다 (여기에는 구멍이 없습니다). 물론이 마지막 방법에는 동시성 문제가 있지만 random_foo를 다시 빌드하는 것은 자주 발생하지 않는 유지 관리 작업입니다.

그러나 최근에 메일 링리스트 에서 찾은 또 다른 방법 은 가장 큰 rowid가있는 행을 현재 삭제 된 행으로 이동하여 구멍이 남지 않도록 삭제 트리거를 설정하는 것입니다.

마지막으로, rowid와 정수 기본 키 자동 증가의 동작은 동일하지 않습니다 (rowid를 사용하면 새 행이 삽입 될 때 max (rowid) +1이 선택됩니다. 기본 키)이므로 마지막 솔루션은 random_foo의 자동 증가와 함께 작동하지 않지만 다른 방법은 작동합니다.

답변

select * from quest order by RANDOM();

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;

SELECT * FROM foo LIMIT 1 OFFSET m;

SELECT   bar
FROM     foo
ORDER BY Random()
LIMIT    1

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

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 * FROM foo WHERE rowid = abs(random()) % (SELECT max(rowid) FROM foo) + 1;