Postgres：仅针对一列的Distinct

Question

我在pgsql上有一张含有超过100万行名称的表格，但是里面也有很多重复的。我选择了3个字段：id，name，metadata。

我想要用ORDER BY RANDOM()和LIMIT 1000随机选择它们，所以我会把这些步骤分开进行，以节省PHP脚本中的一些内存。

但是，如何才能只给我一个没有重复名称的列表呢？

例如，[1,"Michael Fox","2003-03-03,34,M,4545"]将被返回，但不包括[2,"Michael Fox","1989-02-23,M,5633"]。名称字段是最重要的，并且每次选择时必须唯一且随机。

我尝试过用GROUP BY name，但是它要求我在GROUP BY中也有id和metadata，或者在聚合函数中使用它们，但我不想对它们进行筛选。

有人知道如何获取许多列，但仅对一列执行去重吗？

- NovumCoder

Answer 1

在仅对一个（或 n 个）列执行 DISTINCT：

select distinct on (name)
    name, col1, col2
from names

这将返回包含该名称的所有行。如果您想控制将返回哪些行，则需要对其进行排序：

select distinct on (name)
    name, col1, col2
from names
order by name, col1

按照 col1 排序后返回第一行。

distinct on：

对于每组符合给定表达式相等条件的行，SELECT DISTINCT ON ( expression [, ...] ) 只保留第一行。DISTINCT ON 表达式使用的规则与 ORDER BY 相同（参见上文）。请注意，除非使用 ORDER BY 确保所需的行出现在首位，否则每组的“第一行”是不可预测的。

DISTINCT ON 表达式必须与最左边的 ORDER BY 表达式匹配。ORDER BY 子句通常包含额外的表达式，以确定每个 DISTINCT ON 组内行的所需优先级。

- Clodoaldo Neto

---

需要使用 order by name 吗？如果使用 order by col1 会产生不同的结果吗？ - Elliot Chance

@elliot 是的，name 是必需的。请查看手册中的 distinct on。 - Clodoaldo Neto

我希望TSQL团队可以提供一种这样明智的做法。 - JTW

我也是，这个问题困扰了我好几周了。我想在一个列上使用distinct，但是按照另一个不同的列排序。为什么在Postgres中这么难呢？子查询太慢了，因为它会在返回外部order by之前评估整个查询。真是让人无法忍受的挫败感！ - Kevin Parker

@KevinParker https://dev59.com/k2kw5IYBdhLWcg3wlbd3? - jian

Answer 2

有人知道如何在多列上获取数据但只针对一列进行去重吗？

您需要使用DISTINCT ON子句。

由于您没有提供示例数据或完整的查询，因此我无法向您展示。您需要编写类似于以下代码：

SELECT DISTINCT ON (name) fields, id, name, metadata FROM the_table;

这会返回一个不可预测的（但不是“随机”的）行集。如果您想使其可预测，请按照Clodaldo的答案添加ORDER BY。如果您想使其真正随机，请使用ORDER BY random()。

- Craig Ringer

---

请注意，使用DISTINCT ON子句时，您只能按相同的内容+更多内容进行排序。因此，如果您说DISTINCT ON（name），则必须按名称排序，然后按您想要的任何其他内容排序。这几乎不是理想的情况。 - Kevin Parker

Kevin，你可以使用CTE或FROM子查询，并在外部查询中使用ORDER BY。 - Craig Ringer

是的，看着性能提高吧...将从索引空间中搜索所有可能的结果。它会将本来可以使用正确索引进行10-20毫秒查询的操作变成了900毫秒，只是因为posgres无法处理不同的distinct/order by。甚至外部查询顺序是什么都无所谓，它将首先使用内部子查询中的索引查找匹配项，然后重新排序。很高兴为我们在https://dba.stackexchange.com/questions/260852/fastest-way-to-choose-distinct-rows-and-a-different-order-by-without-using-a-sub提供真正解决问题的咨询费用。 - Kevin Parker

Answer 3

要在 n 个列上执行 DISTINCT 操作：

select distinct on (col1, col2) col1, col2, col3, col4 from names

- Sunil Kumar

Answer 4

SELECT NAME,MAX(ID) as ID,MAX(METADATA) as METADATA 
from SOMETABLE
GROUP BY NAME

- David Jashi

---

只是提醒一下：这可能无法返回属于“一起”的ID值或元数据值。 - user330315

@Novum 不是。它意味着可以从 Michael 的某一行中取得 id 值，从另一个行中取得元数据，因为这是要求 Michael 的最大值。 - Clodoaldo Neto

嗯，是的，这在很大程度上取决于实际数据操作者使用的数据，而我对此完全不了解。您可能需要使用MIN或其他函数。只是演示了如何包括未在“GROUP BY”子句中的字段。 - David Jashi

这不是一个好的解决方案，因为来自不同行的不同值会混合在一起。 - Elliot Chance