当前位置：文江博客话题详情

SQLite - 按 RAND 排序()

发布于 2024-08-01 20:29:13 字数 51 浏览 15 评论 0原文

在 MySQL 中我可以使用 RAND() 函数，SQLite 3 中有其他替代方法吗？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

木森分化 2024-08-08 20:29:13

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

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

回复收藏 0 原文

泅人 2024-08-08 20:29:13

使用 random()：

SELECT foo FROM bar
  WHERE id >= (abs(random()) % (SELECT max(id) FROM bar))
  LIMIT 1;

编辑（按 QOP）： 自文档以来在 SQLite 自动增量ed 列上指出：

上面描述的正常 ROWID 选择算法将生成
只要您从不使用单调递增的唯一 ROWID
最大 ROWID 值，并且您永远不会删除表中的条目
最大的ROWID。如果您曾经删除过行，那么 ROWID 会来自
创建新行时可能会重用以前删除的行。

仅当您没有INTEGER PRIMARY KEY AUTOINCRMENT列时，上述情况才成立（它仍然可以与INTEGER PRIMARY KEY列一起正常工作）。无论如何，这应该更便携/可靠：

SELECT foo FROM bar
  WHERE _ROWID_ >= (abs(random()) % (SELECT max(_ROWID_) FROM bar))
LIMIT 1;

ROWID、_ROWID_ 和 OID 都是 SQLite 内部行 id 的别名。

using random():

SELECT foo FROM bar
  WHERE id >= (abs(random()) % (SELECT max(id) FROM bar))
  LIMIT 1;

EDIT (by QOP): Since the docs on SQLite Autoincremented columns states that:

The normal ROWID selection algorithm described above will generate
monotonically increasing unique ROWIDs as long as you never use the
maximum ROWID value and you never delete the entry in the table with
the largest ROWID. If you ever delete rows, then ROWIDs from
previously deleted rows might be reused when creating new rows.

The above is only true if you don't have a INTEGER PRIMARY KEY AUTOINCREMENT column (it will still work fine with INTEGER PRIMARY KEY columns). Anyway, this should be more portable / reliable:

SELECT foo FROM bar
  WHERE _ROWID_ >= (abs(random()) % (SELECT max(_ROWID_) FROM bar))
LIMIT 1;

ROWID, _ROWID_ and OID are all aliases for the SQLite internal row id.

回复收藏 0 原文

暖风昔人 2024-08-08 20:29:13

为了获得更好的性能，请在 SQLite 中使用它：

SELECT * FROM table WHERE id IN (SELECT id FROM table ORDER BY RANDOM() LIMIT x)

这也适用于 MySQL。这运行得更快，因为SQL引擎首先将行的投影字段加载到内存中，然后对它们进行排序，这里我们只是加载并随机排序行的id字段，然后我们得到其中的X个，并找到整行这些 X id 默认情况下已建立索引。

For a much better performance use this in SQLite:

SELECT * FROM table WHERE id IN (SELECT id FROM table ORDER BY RANDOM() LIMIT x)

This is also applicable to MySQL. This runs faster because SQL engines first load projected fields of rows to memory then sort them, here we just load and random sort the id field of rows, then we get X of them, and find the whole rows of these X ids which is by default indexed.

回复收藏 0 原文

梦幻的心爱 2024-08-08 20:29:13

解决了：

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

Solved:

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

回复收藏 0 原文

另类 2024-08-08 20:29:13

目标是获得随机结果（无限滚动），同时能够对结果进行 SQL 分页（LIMIT a,b），这需要可预测的结果（伪随机又名 PRNG）。

SIN(id + seed) 似乎是 RANDOM(seed) 的一个很好的替代方案。

请考虑这个完全用 JS 编写的演示，它使用 SIN(id + Seed) 评分模拟 ORDER BY 子句：

// Inspired by:
// https://www.sqlite.org/forum/forumpost/e2216583a4
// https://stackoverflow.com/a/24511461/7776828

// Simulate N autoincrement stable ids 
// (Avoid rowid which is unstable)
const max = 20;
const a = Array();
for (let id = 0; id < max; ++id) {
  a.push({id});
}
console.log(a);

// Order the results by random
const orderByRandom = ({a, seed}) => {
  // For each result, 
  // Use sin(id + seed) to get a stable random score
  const randomScored = a.map(x => { 
    return { ...x, score: Math.sin(x.id + seed) }
  });
  // Sort by the random score
  randomScored.sort((a,b) => a.score - b.score);
  return randomScored;
}

// Used for generating the seed
const random = () => 1 + Math.floor(Math.random() * Number.MAX_SAFE_INTEGER - 1);

let seed;

seed = random(); // seed #1
console.log(orderByRandom({a, seed})); 
console.log(orderByRandom({a, seed})); // Stable, can paginate

seed = random(); // seed #2
console.log(orderByRandom({a, seed})); // New order because new seed

The goal is to get random results (Infinite Scroll) while being able to SQL paginate the results (LIMIT a,b), which needs a predictible outcome (pseudorandom aka PRNG).

SIN(id + seed) seems a great alternative to RANDOM(seed).

Please consider this demo entirely written in JS that simulates an ORDER BY clause using a SIN(id + seed) scoring :

// Inspired by:
// https://www.sqlite.org/forum/forumpost/e2216583a4
// https://stackoverflow.com/a/24511461/7776828

// Simulate N autoincrement stable ids 
// (Avoid rowid which is unstable)
const max = 20;
const a = Array();
for (let id = 0; id < max; ++id) {
  a.push({id});
}
console.log(a);

// Order the results by random
const orderByRandom = ({a, seed}) => {
  // For each result, 
  // Use sin(id + seed) to get a stable random score
  const randomScored = a.map(x => { 
    return { ...x, score: Math.sin(x.id + seed) }
  });
  // Sort by the random score
  randomScored.sort((a,b) => a.score - b.score);
  return randomScored;
}

// Used for generating the seed
const random = () => 1 + Math.floor(Math.random() * Number.MAX_SAFE_INTEGER - 1);

let seed;

seed = random(); // seed #1
console.log(orderByRandom({a, seed})); 
console.log(orderByRandom({a, seed})); // Stable, can paginate

seed = random(); // seed #2
console.log(orderByRandom({a, seed})); // New order because new seed

回复收藏 0 原文

~没有更多了~