当前位置：首页 > article >正文

MySQL高阶1831-每天的最大交易

article 2024/9/24 0:24:10

题目

编写一个解决方案，报告每天交易金额 amount 最大的交易 ID 。如果一天中有多个这样的交易，返回这些交易的 ID 。

返回结果根据 transaction_id 升序排列。

准备数据

Create table If Not Exists Transactions (transaction_id int, day date, amount int);
    Truncate table Transactions;
    insert into Transactions (transaction_id, day, amount) values ('8', '2021-4-3 15:57:28', '57');
    insert into Transactions (transaction_id, day, amount) values ('9', '2021-4-28 08:47:25', '21');
    insert into Transactions (transaction_id, day, amount) values ('1', '2021-4-29 13:28:30', '58');
    insert into Transactions (transaction_id, day, amount) values ('5', '2021-4-28 16:39:59', '40');
    insert into Transactions (transaction_id, day, amount) values ('6', '2021-4-29 23:39:28', '58');

分析数据

第一步:使用开窗函数根据amount降序

select *,
       rank() over(partition by day order by amount desc) rn
       from transactions;

第二步:选出最大交易金额的交易ID

with t1 as (
    select *,
           rank() over(partition by day order by amount desc) rn
    from transactions
)select transaction_id from t1
where rn =1
order by transaction_id asc;