Maximum Transaction Each Day
题目分析:题目的意思很简单,要求查出每天的最大的交易数,注意的是,有些天数会同时存在多个最大值。思路可以是:首先筛选出对应日期的最大值,然后按照对应日期和最大值与原来的表进行inner join即可
首先格式化一下时间,只需要日期
select transaction_id,date_format(day,"%Y-%m-%d") f_day,amount from Transactions
依据上面的结果取出每天的最大值
select transaction_id,max(amount) amount,f_day from (
select transaction_id,date_format(day,"%Y-%m-%d") f_day,amount from Transactions) F group by f_day
与原来的表进行匹配,取出最终结果
select F1.transaction_id from (select transaction_id,date_format(day,"%Y-%m-%d") f_day,amount from Transactions) F1 inner join
(select transaction_id,max(amount) amount,f_day from (
select transaction_id,date_format(day,"%Y-%m-%d") f_day,amount from Transactions) F group by f_day) F2 on
F1.f_day = F2.f_day and F1.amount = F2.amount order by transaction_id;
提交结果如下:
方法二:直接使用窗口函数中的dense_rank()按照天进行排序,取第一的值即可
select transaction_id from (
select transaction_id,f_day,amount,dense_rank() over(partition by f_day order by amount desc) dk from (select transaction_id,date_format(day,"%Y-%m-%d") f_day,amount from Transactions) F) F1 where dk = 1 order by transaction_id
提交结果如下: