如何在PostgreSQL查询中排列不同的元组

Question

我试图在Postgres中提交一个只返回不同元组的查询。在我的示例查询中，我不希望对于cluster_id/feed_id组合多次存在条目的重复条目。如果我做一个简单：

select distinct on (cluster_info.cluster_id, feed_id) 
    cluster_info.cluster_id, num_docs, feed_id, url_time 
    from url_info 
    join cluster_info on (cluster_info.cluster_id = url_info.cluster_id) 
    where feed_id in (select pot_seeder from potentials) 
    and num_docs > 5 and url_time > '2012-04-16'; 

我得到了这一点，但我也想组根据num_docs。所以，当我做到以下几点：

select distinct on (cluster_info.cluster_id, feed_id) 
    cluster_info.cluster_id, num_docs, feed_id, url_time 
    from url_info join cluster_info 
    on (cluster_info.cluster_id = url_info.cluster_id) 
    where feed_id in (select pot_seeder from potentials) 
    and num_docs > 5 and url_time > '2012-04-16' 
    order by num_docs desc; 

我收到以下错误：

ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions 
LINE 1: select distinct on (cluster_info.cluster_id, feed_id) cluste... 

我想我明白为什么我收到错误（不能按元组，除非我明确地描述该组不知何故），但我该怎么做？或者，如果我对错误的解释不正确，是否有办法实现我最初的目标？

Answer 1

最左边的ORDER BY项目不能不同意DISTINCT条款的项目。我引用the manual about DISTINCT：

The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s). The ORDER BY clause will normally contain additional expression(s) that determine the desired precedence of rows within each DISTINCT ON group.

尝试：

SELECT * 
FROM (
    SELECT DISTINCT ON (c.cluster_id, feed_id) 
      c.cluster_id, num_docs, feed_id, url_time 
    FROM url_info u 
    JOIN cluster_info c ON (c.cluster_id = u.cluster_id) 
    WHERE feed_id IN (SELECT pot_seeder FROM potentials) 
    AND num_docs > 5 
    AND url_time > '2012-04-16' 
    ORDER BY c.cluster_id, feed_id, num_docs, url_time 
      -- first columns match DISTINCT 
      -- the rest to pick certain values for dupes 
      -- or did you want to pick random values for dupes? 
    ) x 
ORDER BY num_docs DESC; 

或者使用GROUP BY：

SELECT c.cluster_id 
    , num_docs 
    , feed_id 
    , url_time 
FROM url_info u 
JOIN cluster_info c ON (c.cluster_id = u.cluster_id) 
WHERE feed_id IN (SELECT pot_seeder FROM potentials) 
AND num_docs > 5 
AND url_time > '2012-04-16' 
GROUP BY c.cluster_id, feed_id 
ORDER BY num_docs DESC; 

如果c.cluster_id, feed_id都（无论是在这种情况下）表的主键列，你包括从SELECT列表中列出，然后这只适用于PostgreSQL 9.1或更高版本。

否则您需要GROUP BY其余列或聚合或提供更多信息。