windows7建设网站,免费的网页设计成品资源,龙华做棋牌网站建设哪家好,单页网站模板wap目录标题1194. 锦标赛优胜者--做出来了1225. 报告系统状态的连续日期-勉强1159. 市场分析 II1205. 每月交易II1194. 锦标赛优胜者–做出来了
题型#xff1a;看题 解答#xff1a;先处理matches表#xff0c;整出分数列和players表连接 注意点#xff1a; union all 时…
目录标题1194. 锦标赛优胜者--做出来了1225. 报告系统状态的连续日期-勉强1159. 市场分析 II1205. 每月交易II1194. 锦标赛优胜者–做出来了
题型看题 解答先处理matches表整出分数列和players表连接 注意点 union all 时会存在一个 player_id有多行记录这样再和p表连接时会变多所以需要先group by聚合一下
with t as
(select player_id,sum(score) as score
from (select first_player player_id,first_score scorefrom Matchesunion allselect second_player player_id,second_score scorefrom Matches)agroup by player_id
)select distinct a.group_id,a.player_id
from
(select p.player_id,p.group_id,rank()over(partition by p.group_id order by ifnull(t.score,0) desc, p.player_id asc) as r
from Players p left join t on p.player_idt.player_id) a
where r1窗口函数总结 窗口函数 over (partition by 分组列名 order by 排序列名)
rank() : 阶梯排序-前两个是并列的1接下来就是第3名 dense_rank(): 连续排序-前两个是并列的1接下来就是第2名 row_number(): 不会出现重复的排序
1225. 报告系统状态的连续日期-勉强
题型连续天数问题 select type as period_state, min(date) as start_date, max(date) as end_date
from
(select type, date, subdate(date,row_number()over(partition by type order by date)) as difffrom(select failed as type, fail_date as date from Failedunion allselect succeeded as type, success_date as date from Succeeded) a
)a
where date between 2019-01-01 and 2019-12-31
group by type,diff
order by start_date
1159. 市场分析 II select user_id as seller_id,
if(t.item_brandu.favorite_brand,yes,no) 2nd_item_fav_brand
from Users u
left join (select o.item_id, i.item_brand, seller_id,rank() over (partition by seller_id order by order_date) rk from orders o left join items i on o.item_idi.item_id) t
on u.user_idt.seller_id and rk21205. 每月交易II with cte as
(select * from transactionsunion allselect id, country, chargeback state, amount, c.trans_datefrom chargebacks c left join transactions t on c.trans_id t.id
) select date_format(trans_date, %Y-%m) month, country,sum(state approved) approved_count,sum(if(state approved, amount, 0)) approved_amount,sum(state chargeback) chargeback_count,sum(if(state chargeback, amount, 0)) chargeback_amount
from cte
group by month, country
having approved_amount or chargeback_amount
