选择随机行 [英] selecting random rows

问题描述

除了ORDER BY random（）之外，有没有办法获得随机行？ ORDER BY

random（）的问题是必须在返回结果之前从表中获取所有行。

-------- -------------------（广播结束）-------------------------- -

提示9：如果您的

加入列的数据类型不匹配，计划员将忽略您选择索引扫描的愿望

Is there a way to get random rows besides ORDER BY random()? The problem with ORDER BY
random() is that is has to get all the rows from the table before the results are returned.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column''s datatypes do not match

推荐答案

Joseph Shraibman写道：

Joseph Shraibman wrote:

除了ORDER BY random（）之外，有没有办法获得随机行？ ORDER BY
random（）的问题是必须在返回结果之前从表中获取所有行。

Is there a way to get random rows besides ORDER BY random()? The problem with ORDER BY
random() is that is has to get all the rows from the table before the results are returned.

是的，我认为一个人的想法是为每个

行分配一个唯一值，然后执行：


WHERE col>随机（）

ORDER BY col

LIMIT 1


或类似的东西。


-

Bruce Momjian | http://candle.pha.pa.us
pg *** @ candle.pha.pa.us | [phone]

+如果你的生活是硬盘，|罗伯茨路13号

+基督可以成为你的备份。 |宾夕法尼亚州新城广场19073年


---------------------------（播出结束） - --------------------------

提示9：如果您的计划者忽视您选择索引扫描的愿望

加入列的数据类型不匹配

Yes, I think one person''s idea was to assign a unique value to every
row, then do:

WHERE col > random()
ORDER BY col
LIMIT 1

or something like that.

--
Bruce Momjian | http://candle.pha.pa.us
pg***@candle.pha.pa.us | [phone]
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column''s datatypes do not match

Joseph Shraibman写道：

Joseph Shraibman wrote:

是否有除了ORDER BY random（）之外获取随机行的方法？ ORDER BY
random（）的问题是必须在返回结果之前从表中获取所有行。

Is there a way to get random rows besides ORDER BY random()? The problem with ORDER BY
random() is that is has to get all the rows from the table before the results are returned.

是的，我认为一个人的想法是为每个

行分配一个唯一值，然后执行：


WHERE col>随机（）

ORDER BY col

LIMIT 1


或类似的东西。


-

Bruce Momjian | http://candle.pha.pa.us
pg *** @ candle.pha.pa.us | [phone]

+如果你的生活是硬盘，|罗伯茨路13号

+基督可以成为你的备份。 |宾夕法尼亚州新城广场19073年


---------------------------（播出结束） - --------------------------

提示9：如果您的计划者忽视您选择索引扫描的愿望

加入列的数据类型不匹配

Yes, I think one person''s idea was to assign a unique value to every
row, then do:

WHERE col > random()
ORDER BY col
LIMIT 1

or something like that.

--
Bruce Momjian | http://candle.pha.pa.us
pg***@candle.pha.pa.us | [phone]
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column''s datatypes do not match

如果桌面上有一个很好的小主键，你可以这样的事情

是这样的：


SELECT field_list

FROM table

WHERE primary_key IN（< br $>
SELECT primary_key，

FROM table

ORDER by RANDOM（）

LIMIT your_limit）;


这可能不是确切的顺序，并且有一些解决方法

IN（）关键字有些慢。其他人可以随意发表评论，并且

对此进行评论？


Joseph Shraibman写道：

If you have a nice small Primary key on the table, you can so something
like this:

SELECT field_list
FROM table
WHERE primary_key IN(
SELECT primary_key,
FROM table
ORDER by RANDOM()
LIMIT your_limit);

This may not be the exact sequence, and there is some workarounds for
some slowness in the IN() keyword. Others may comment at will, and
polish this up?

Joseph Shraibman wrote:

是否有除了ORDER BY random（）之外获取随机行的方法？ ORDER BY random（）的问题是，必须在返回结果之前从表中获取所有行。

-------- -------------------（广播结束）-------------------------- - 提示9：如果您的
加入列的数据类型不匹配，规划人员将忽略您选择索引扫描的愿望

Is there a way to get random rows besides ORDER BY random()? The
problem with ORDER BY random() is that is has to get all the rows from
the table before the results are returned.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if
your
joining column''s datatypes do not match

---------------------------（广播结束）------------------ ---------

提示1：订阅和取消订阅命令转到 ma ** *****@postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

这篇关于选择随机行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！