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

问题描述

我有这样的表格

  ID属性
 1 A 
 1 A 
 1 B 
 1 C 
 2 B 
 2 C 
 2 C 
 3 A 
 3 B 
 3 C 
  

我想为每个ID选择一个随机属性。因此，结果可能如下所示（尽管这只是众多选项中的一种）

  ATTRIBUTE 
 B 
 C 
 C 
  

这是我对这个问题的尝试

  SELECT 
ATTRIBUTE
 FROM 
（
 SELECT 
ID，
ATTRIBUTE，
 row_number（）OVER（PARTITION BYIDORDER BY random（））rownum 
 FROM 
表
）混洗
 WHERE 
 rownum = 1 
  

然而，我不知道这是否是一个很好的解决方案，因为我需要引入行号，这有点麻烦。

你有更好的吗？

解决方案

 选择distinct（id）id，属性
 from like_this 
按ID排序，random（）
  

如果您只需要属性列：

 从like_this 
选择不同的on（id）属性
 by id，random（） 
  

请注意，您仍然需要按 id 第一，因为它是在上的独立字段。

如果您只想要不同的属性：

 选择不同的属性
从（
）选择不同的属性（id）属性
 from like_this 
按ID编号，随机（）
）s 
  

I have a table like this

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

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.

Do you have a better one?

解决方案

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

If you only need the attribute column:

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

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