从 sqlite 表中选择随机行

看看 从 SQLite 表中选择随机行

SELECT * FROM table ORDER BY RANDOM() LIMIT 1;

SELECT   bar
FROM     foo
ORDER BY Random()
LIMIT    1

那么:

SELECT COUNT(*) AS n FROM foo;

然后在[0，n)中选择一个随机数 我

SELECT * FROM foo LIMIT 1 OFFSET m;

您甚至可以将第一个数字(N)保存在某个地方，只有在数据库计数更改时才更新它。这样，您就不必每次都执行 SELECT 计数。

下面的解决方案比 anktastic 的要快得多(count (*)花费很多，但是如果你可以缓存它，那么差别不应该那么大) ，当你有大量行时，它本身比“ order by Random ()”要快得多，尽管它们有一些不方便。

如果你的鱼群相当密集。少量删除) ，然后您可以执行以下操作(使用 (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;

第二个解决方案并不完美: 最后一行的概率分布较高(rowid 最高的那一行) ，但如果你经常向表中添加内容，它将成为一个移动的目标，概率分布应该更好。

还有另一种解决方案，如果你经常从一个有很多洞的表中随机选择东西，那么你可能想要创建一个表，其中包含按随机顺序排列的原始表的行:

create table random_foo(foo_id);

然后，定期地重新填充表 Random _ foo

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

要选择一个随机行，可以使用我的第一个方法(这里没有空白)。当然，最后这个方法有一些并发性问题，但是重新构建 Random _ foo 是一个维护操作，不太可能经常发生。

然而，我最近在 邮件列表上发现的另一种方法是在 delete 上设置一个触发器，将拥有最大 rowid 的行移动到当前被删除的行中，这样就不会留下任何漏洞。

最后，请注意 rowid 和一个整数主键自增量的行为是不相同的(对于 rowid，当插入一个新行时，选择 max (rowid) + 1，而对于一个主键，选择 max (rowid) + 1是有史以来最高的值 + 1) ，所以最后一个解决方案不能在 Random _ foo 中使用自增量，但是其他方法可以。

以下是@ank 解决方案的一个修改:

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

这个解决方案也适用于带有间隔的索引，因为我们随机化了一个范围内的偏移量[0，count)。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

您需要在查询中添加 “ 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);

A default select:

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
--

If you want to return only one row

select * from quest order by RANDOM() LIMIT 1;
| id |   quest  | resp_id |
2     256/2       12
--

我为 大型 sqlite3数据库提出了以下解决方案:

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

Abs (X)函数返回数值参数的绝对值 X.

函数返回一个伪随机整数 -9223372036854775808 + 9223372036854775807.

运算符% 将其左操作数的整数值输出为右操作数的模。

最后，添加 + 1以防止 rowid 等于0。