力扣SQL刷题11

最新推荐文章于 2024-08-14 11:26:43 发布

海星？海欣！

最新推荐文章于 2024-08-14 11:26:43 发布

阅读量1.9k

点赞数 1

分类专栏：数据库SQL刷题文章标签： sql leetcode 数据库

本文链接：https://blog.csdn.net/Sun123234/article/details/129007289

版权

数据库SQL刷题专栏收录该内容

36 篇文章 4 订阅

订阅专栏

文章介绍了如何使用SQL进行复杂的数据分析，包括计算锦标赛的优胜者，报告系统状态的连续日期，市场销售策略评估以及月度交易统计。涉及到的关键技术有窗口函数如rank(),dense_rank(),row_number()，以及数据表连接和分组查询。

摘要由CSDN通过智能技术生成

目录标题

1194. 锦标赛优胜者–做出来了

题型：看题
解答：先处理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 score
    from Matches
    union all
    select second_player player_id,second_score score
    from Matches)a
    group 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_id=t.player_id) a
where r=1

窗口函数总结：
<窗口函数> 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 diff
    from
    (
        select 'failed' as type, fail_date as date from Failed
        union all
        select '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_brand=u.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_id=i.item_id
    ) t
on u.user_id=t.seller_id and rk=2

1205. 每月交易II

在这里插入图片描述

with cte as
(
    select * from transactions
    union all
    select id, country, 'chargeback' state, amount, c.trans_date
    from 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