提取Postgres中最小日期的金额 [英] Extract amount for the minimum date in 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中最小日期的金额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！