db2为给定的id选择x个随机行 [英] db2 select x random rows for a given id

问题描述

如果我有两列-一个ID字段和一个可以获取10个可能值的得分字段，那么如何为每个ID选择5个随机行？我知道我可以使用以下命令从表中选择5个随机行：

If I have two columns - an ID field and a score field that can take 10 possible values, how can I select 5 random rows per ID? I know I can select 5 random rows from a table by using the following:

select *, rand() as idx
from mytable 
order by idx fetch first 5 rows only

但是5行如何

推荐答案

您可以使用 row_number（）进行此操作：

You can do this using row_number():

select t.*
from (select t.*,
             row_number() over (partition by idx order by rand()) as seqnum
      from mytable t
     ) t
where seqnum <= 5;

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