返回在PostgreSQL的查询第一个X记录了一个独特的领域 [英] Returning the first X records in a postgresql query with a unique field

问题描述

好了，所以我在这里有一个学习的时刻，并计算出的 A 的方式来得到这个工作后，我很好奇，如果有人用多一点Postgres的经验可以帮助我想出一个办法做到这一点没有做一个整体洛塔幕后轨的东西（或做每个项目我试图得到一个单一的查询）......现在的交代：

Ok so I'm having a bit of a learning moment here and after figuring out A way to get this to work, I'm curious if anyone with a bit more postgres experience could help me figure out a way to do this without doing a whole lotta behind the scene rails stuff (or doing a single query for each item i'm trying to get)... now for an explaination:

说我有1000条记录，我们会打电话给他们实例，在有这些领域的数据库：

Say I have 1000 records, we'll call them "Instances", in the database that have these fields:

id
user_id
other_id

我要创造，我可以调用10个实例，它们有一个独特的other_id场，用简单的英语（我知道这不会工作:)），拉的方法：

I want to create a method that I can call that pulls in 10 instances that all have a unique other_id field, in plain english (I realize this won't work :) ):

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

因此​​，而不是在10个实例，其中user_id是3，你可以得到多个实例与other_id是5的阵列中拔出，我希望能够在这10个实例运行一个地图功能，并取回类似[1 ，2,3,4,5,6,7,8,9,10]

So instead of pulling in an array of 10 instances where user_id is 3 and you can get multiple instances with the other_id is 5, I want to be able to run a map function on those 10 instances and get back something like [1,2,3,4,5,6,7,8,9,10].

在理论上，我也许可以做两件事情之一目前，尽管我试图避免他们：

In theory, I can probably do one of two things currently, though I'm trying to avoid them:

1. 存储ID的数组，并做单个呼叫确保下次调用写着不在此阵。这里的问题是我在做10个人的数据库查询。

1. Store an array of id's and do individual calls making sure the next call says "not in this array". The problem here is I'm doing 10 individual db queries.

拉一大块比如说50个实例，并通过他们在红宝石土地整理发现10个独特的人。这不会让我走在数据库中已经做过优化的优势，我也想这样做运行对于不具备10个独特的other_id的，我会与那些除非我被卡住50项查询的风险做了另一个查询。

Pull in a large chunk of say, 50 instances and sorting through them in ruby-land to find 10 unique ones. This wouldn't allow me to take advantage of any optimizations already done in the database and I'd also run the risk of doing a query for 50 items that don't have 10 unique other_id's and I'd be stuck with those unless I did another query.

不管怎么说，希望有人也许能告诉我，我俯瞰一个容易的选择:)我知道这是的样的的真正需要之前，但优化该功能将被跑过去，一遍又一遍，所以我推测这不是浪费时间现在。

Anyways, hoping someone may be able to tell me I'm overlooking an easy option :) I know this is kind of optimizing before it's really needed but this function is going to be run over and over and over again so I figure it's not a waste of time right now.

有关记录，我使用Ruby 1.9.3，Rails的3.2.13和PostgreSQL（Heroku的）

For the record, I'm using Ruby 1.9.3, Rails 3.2.13, and Postgresql (Heroku)

谢谢！

修改：只是想给在技术上不工作的函数的例子（及以上1号）

EDIT: Just wanted to give an example of a function that technically DOES work (and is number 1 above)

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个独立的查询：（

While this works, it's not ideal because it's leading to 10 separate queries every time it's called :(

推荐答案

在一个SQL查询时，它可以很容易地与实现SELECT DISTINCT ON ... 这是一个PostgreSQL相关的功能。

In a single SQL query, it can be achieved easily with SELECT DISTINCT ON... which is a PostgreSQL-specific feature.

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

SELECT DISTINCT ON（例如pression [，...]）只保留的第一行
  每一组中给定的前pressions评估等于行。该
  DISTINCT ON前pressions都使用相同的规则PTED间$ P $
  ORDER BY（见上文）。请注意，每个组中的第一行是
  未predictable除非ORDER BY用于确保所希望的行
  第一次出现

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

这篇关于返回在PostgreSQL的查询第一个X记录了一个独特的领域的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！