本文介绍了提取Postgres中最小日期的金额的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
非常基本：我有一个表格，其中包含日期，帐户和该日期特定帐户所完成的金额。我遇到了一个非常基本的问题-获取每个帐户的最短日期金额。
Very basic: I have a table with dates, account and amount done by a particular account on that date. I am stuck on a very basic problem - get the amount for the minimum date per account.
输入：
所需：
如果我在下面的查询中显然也返回了按金额分组。
If I do the query below it obviously returns the grouping by the amount, too.
SELECT account_ref AS account_alias,
Min(timestamp_made) AS reg_date,
amount
FROM stg_payment_mysql
GROUP BY account_ref,
amount
推荐答案
也许是性能最高的这样做的方法是使用 DISTINCT ON ：
Perhaps the most performant way to do this would be to use DISTINCT ON:
SELECT DISTINCT ON (account) account, date, amount
FROM stg_payment_mysql
ORDER BY account, date;
更通用的ANSI SQL方法将使用 ROW_NUMBER ：
A more general ANSI SQL approach to this would use ROW_NUMBER:
WITH cte AS (
SELECT account, date, amount,
ROW_NUMBER() OVER (PARTITION BY account ORDER BY date) rn
FROM stg_payment_mysql
)
SELECT account, date, amount
FROM cte
WHERE rn = 1
ORDER BY account;
这篇关于提取Postgres中最小日期的金额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！