从数据库中的每个用户获取最后一个条目。

I have a Postgresql database, and I'm having trouble getting my query right, even though this seems like a common problem.

我有一个Postgresql数据库，尽管这看起来是一个常见的问题，但是我很难正确地得到我的查询。

My table looks like this:

我的桌子是这样的:

CREATE TABLE orders (
   account_id   INTEGER,
   order_id     INTEGER,
   ts           TIMESTAMP DEFAULT NOW()
)

Everytime there is a new order, I use it to link the account_id and order_id.

每次有新订单时，我都使用它来链接account_id和order_id。

Now my problem is that I want to get a list that has the last order (by looking at ts) for each account.

现在我的问题是，我想要得到一个列表，每个帐户都有最后一个订单(通过查看ts)。

For example, if my data is:

例如，如果我的数据是:

account_id    order_id            ts
         5         178        July 1
         5         129        July 6
         4         190        July 1
         4         181        July 9
         3         348        July 1
         3         578        July 4
         3         198        July 1
         3         270       July 12

Then I'd like the query to return only the last row for each account:

然后，我希望查询只返回每个帐户的最后一行:

account_id    order_id            ts
         5         129        July 6
         4         181        July 9
         3         270       July 12

I've tried GROUP BY account_id, and I can use that to get the MAX(ts) for each account, but then I have no way to get the associated order_id. I've also tried sub-queries, but I just can't seem to get it right.

我尝试过用account_id组，我可以用它来获取每个帐户的最大(ts)，但是我没有办法获得相关的order_id。我也尝试过子查询，但是我似乎做不到。

Thanks!

谢谢!

2 个解决方案

#1

select distinct on (account_id) *
from orders
order by account_id, ts desc

https://www.postgresql.org/docs/current/static/sql-select.html#SQL-DISTINCT:

https://www.postgresql.org/docs/current/static/sql-select.html SQL-DISTINCT:

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.

选择DISTINCT ON(表达式[，…)只保留给定表达式求值为相等的每组行的第一行。使用与ORDER BY相同的规则解释表达式上的不同之处(参见上面)。请注意，每个集合的“第一行”是不可预测的，除非使用ORDER来确保所期望的行首先出现。

#2

The row_number() window function can help:

row_number()窗口函数可以帮助:

select account_id, order_id, ts
  from (select account_id, order_id, ts,
               row_number() over(partition by account_id order by ts desc) as rn
          from tbl) t
 where rn = 1

#1

select distinct on (account_id) *
from orders
order by account_id, ts desc