sql - 使用唯一字段返回 postgresql 查询中的前 X 条记录

Question

好吧，我在这里学习了一段时间，在想出 A 方法来让它工作之后，我很好奇是否有更多 postgres 经验的人可以帮助我想出一种方法来做到这一点，而无需在幕后做很多事情(或者对我试图获得的每个项目进行一次查询)...现在进行解释:

假设我有 1000 条记录，我们称它们为“实例”，在数据库中有这些字段:

id
user_id
other_id

我想创建一个我可以调用的方法，用简单的英语提取 10 个实例，这些实例都有一个唯一的 other_id 字段(我意识到这行不通:)):

Select * from instances where user_id = 3 and other_id is unique limit 10

因此，我希望能够在这 10 个实例上运行 map 函数并返回类似 [1 ,2,3,4,5,6,7,8,9,10]。

理论上，我目前可能可以做以下两件事之一，尽管我正在努力避免它们:

1. 存储一个 id 数组并进行单独调用，确保下一个调用说“不在这个数组中”。这里的问题是我正在执行 10 个单独的数据库查询。

2. 提取大量实例，例如 50 个实例，并在 ruby​​-land 中对它们进行排序，以找到 10 个独特的实例。这不允许我利用数据库中已经完成的任何优化，而且我还冒着查询 50 个没有 10 个唯一 other_id 的项目的风险，除非我做了另一个查询。

无论如何，希望有人能告诉我我忽略了一个简单的选项 :) 我知道这是一种在真正需要它之前进行优化，但是这个函数将被运行并且一遍又一遍，所以我认为现在这不是浪费时间。

郑重声明，我使用的是 Ruby 1.9.3、Rails 3.2.13 和 Postgresql (Heroku)

谢谢!

编辑:只是想举一个技术上可行的函数示例(并且是上面的第一个)

def getInstances(limit, user)
  out_of_instances = false
  available = []
  other_ids = [-1] # added -1 to avoid submitting a NULL query

  until other_ids.length == limit || out_of_instances == true

    instance = Instance.where("user_id IS ? AND other_id <> ALL (ARRAY[?])", user.id, other_ids).limit(1)

    if instance != []
      available << instance.first
      other_ids << instance.first.other_id
    else
      out_of_instances = true
    end
  end
end

然后你会跑:

getInstances(10, current_user)

虽然这可行，但并不理想，因为每次调用它都会导致 10 个单独的查询 :(

Answer 1

在单个 SQL 查询中，可以使用 SELECT DISTINCT ON... 轻松实现，这是 PostgreSQL 特定的功能。

参见 http://www.postgresql.org/docs/current/static/sql-select.html

SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first

以你的例子:

 SELECT DISTINCT ON (other_id) * 
 FROM instances
 WHERE user_id = 3 
 ORDER BY other_id LIMIT 10