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
3
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
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
3
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
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