为每组选择随机行 [英] Select random row for each group

问题描述

我有一张这样的桌子

ID    ATTRIBUTE
 1    A
 1    A
 1    B
 1    C
 2    B
 2    C
 2    C
 3    A
 3    B
 3    C

我想为每个 ID 选择仅一个随机属性.因此，结果可能如下所示(尽管这只是众多选择之一

I'd like to select just one random attribute for each ID. The result therefore could look like this (although this is just one of many options

ATTRIBUTE
B
C
C

这是我对这个问题的尝试

This is my attempt on this problem

SELECT
  "ATTRIBUTE"
FROM
  (
  SELECT
    "ID",
    "ATTRIBUTE",
    row_number() OVER (PARTITION BY "ID" ORDER BY random()) rownum
  FROM
    table
  ) shuffled
WHERE
  rownum = 1

但是不知道这个好不好，因为需要引入行号，有点麻烦.

however, I don't know if this is a good solution, as I need to introduce row numbers, which is a bit cumbersome.

你有更好的吗?

推荐答案

select distinct on (id) id, attribute
from like_this
order by id, random()

如果你只需要属性列:

select distinct on (id) attribute
from like_this
order by id, random()

注意还是要先按id排序，因为它是distinct on的一列.

Notice that you still need to order by id first as it is a column of the distinct on.

如果你只想要不同的属性:

If you only want the distinct attributes:

select distinct attribute
from (
    select distinct on (id) attribute
    from like_this
    order by id, random()
) s

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