【Ryo】MySQL：刷题力扣篇（二）

数据库是进行数据分析的“源泉”，对冗杂的数据进行整理和编排是数据分析的首要任务。同时将来职业生活中，对数据库系统进行DB操作也会是重要任务之一，因此掌握数据库语言刷题刷起来，至少对语言运用和数据库管理有所了解是题中之义。
本篇为力扣刷题SQL的练习记录（二），离上次刷题已经过去半年多，租了共号会员中难题开刷！

研究内容	语言	日期
数据库	sql	2021年10月7-14日

△△△△△本文为个人项目练习，仅供参考，如有不足欢迎讨论 △△△△△

1. 两表查询，分组并序

题目描述：两表，写一个 SQL 语句，找到每一个顾客最经常订购的商品。结果表单应该有每一位至少下过一次单的顾客 customer_id , 他最经常订购的商品的 product_id 和product_name。

解答：窗口函数先按不同顾客分组 降序统计购买个数，rank函数的话如果三样东西都买一个那会返回（即并列的），然后再从这个表中 选出每组的第一行（rn=1）也就是降序后最上面的（即购买最多的物品）

select A.Customer_Id, A.Product_Id, C.Product_Name
from (select a.customer_id, a.product_id,
      rank() over(partition by a.customer_id order by COUNT(1) desc) rn
      from Orders a
      group by a.customer_id, a.product_id) A, Products C
where rn = 1
and A.Product_Id = C.Product_Id

2. 两表查询，分组并序

题目描述：积分规则如下：赢一场得三分；平一场得一分； 输一场不得分。写出一条SQL语句以查询每个队的 team_id，team_name 和 num_points。结果根据 num_points 降序排序，如果有两队积分相同，那么这两队按 team_id 升序排序。

解答：用case when条件，如果赢的队伍就+3，平局各+1.然后求和sum，最后分组排序。

SELECT team_id, team_name, 
	sum(case 
	when host_goals>guest_goals then if(team_id = host_team,3,0)
	when host_goals<guest_goals then if(team_id = guest_team,3,0) 
	else if(team_id in(host_team,guest_team),1,0) end)  #平局就各积一分
	num_points
from Teams t, Matches m 
GROUP by team_id,team_name
ORDER BY num_points desc, team_id

3. 两表查询，分组并序

题目描述：编写一个 SQL 查询，以查询从今天起最多 90 天内，每个日期该日期首次登录的用户数。假设今天是 2019-06-30.

解答：分组前先筛选login，分组后筛选日期获得一个记录表，再从表中count作为记录数按照日期分组。

select dt as login_date, count(*) as user_count
from(
    select user_id,min(activity_date) as dt
    from traffic
    where activity = 'login'
    group by user_id
    having min(activity_date) >= date_sub('2019-06-30',interval 90 day) ) as t
group by dt

4. 自我感觉很难很难的一题

题目描述：编写一个 SQL 查询，以查找每个月和每个国家/地区的信息：已批准交易的数量及其总金额、退单的数量及其总金额。注意查询中，只需显示给定月份和国家，忽略所有为零的行。

参考解答：首先第一步是创先两个子表，运用最关键的一步tag标签，select 1相当于每一行记录都 有1这个tag，同理0，然后union all两个表（对两个结果集进行并集操作，包括重复行，不进行排序）再根据tag求和（疑问：不理解tag为什么是（1,1，0）这样子筛选？）

select a.month, a.country
,sum(if(tag=1,1,0)) approved_count
,sum(if(tag=1,a.amount,0)) approved_amount
,sum(if(tag=0,1,0)) chargeback_count
,sum(if(tag=0,a.amount,0)) chargeback_amount
from
(select country,amount,date_format(trans_date,"%Y-%m") month,1 as tag
from Transactions where state = "approved"
union all
select t.country,t.amount,date_format(c.trans_date,"%Y-%m") month,0 as tag from
chargebacks c left join Transactions t on c.trans_id = t.id) a
group by a.month,a.country

5. 可以很长也可以很短的题

题目描述：一家电信公司想要投资新的国家. 该公司想要投资的国家是: 该国的平均通话时长要严格地大于全球平均通话时长，写一段 SQL, 找到所有该公司可以投资的国家。

参考思路（比较好懂的一种）：先通过union构造全部用户的通信时间表，再通过截取电话的前三位匹配国家，最后通过group by国家获取不同国家的平均通话时间并和总的平局通话时间比较。

with a as (
    select caller_id caller, duration from Calls
    union all
    select callee_id caller, duration from Calls
)
select c.name country from a left join Person p on a.caller=p.id
left join Country c on left(p.phone_number, 3)=c.country_code
group by c.name
having avg(a.duration) > (select avg(duration) from a)

另外有一种更狂暴的5行解法如下：都不需要创建union 更不需要计算很复杂的平均时间。

select c2.name as country 
from Calls c1,Person p,Country c2
where (p.id=c1.caller_id or p.id=c1.callee_id) and c2.country_code=left(p.phone_number,3)
group by c2.name 
having avg(duration)>(select avg(duration) from Calls)

后记

  后面再更新，搬砖前继续刷！作业太太太太太太太太多了，读研堪比坐牢。