得到基于秩函数的最大值

Question

我有如下示例数据:

entity  account amount  id
ac     1394     156.00  3453
ac    2512      100.00  3453
ac    2512     -70.00   3453
ac    1315     -156.00  3453
ac    1315     156.00   3453

我需要显示金额列的总金额，而不考虑帐户列，并且需要在金额列中获得最大值的帐户。

我使用下面的脚本相同。我能够在金额栏中获得最大价值的帐户，但总额不正确。在这种情况下，总金额应为186。

select a.account, a.total from (
select account,sum(amount) as total,
rank() over (partition by id order by sum(amount) desc) as rank
from test1
group by account,id) a
where rank=1

上述查询的输出为

account total
1394    156.00

Answer 1

给定需求，您可以使用窗口函数获得不分区的总sum()，然后应用row_number()函数顺序为amount desc的子句:

with windows as (
select
account,
sum(amount) over () as total_sum_amount,
row_number() over (order by amount desc) as rn
from test1
)
select
account,
total_sum_amount
from windows
where rn = 1;

输出:

account total
1394    186.00