文章目录
569. 员工薪水中位数
SQL查询来查找每个公司的薪水中位数。挑战点:你是否可以在不使用任何内置的SQL函数的情况下解决此问题。
select id,company,salary from
(
select *,row_number() over(partition by Company order by salary) rn ,
count(salary) over(partition by Company) cn
from employee
) tmp
where rn in (cn/2,cn/2+1,cn/2+0.5)
不用窗口函数的做法
中位数求法的思路:
将整个有序集合分为两个子集,每个子集包含(集合的一半+1)个元素,两个子集的共同部分就是集合的中位数了
解题过程:
运用CASE表达式,非等值自连接和HAVING子句来找中位数
通过 WHERE e1.Company = e2.Company 进行分组
最后通过GROUP BY 去重
select Id, Company, Salary
from Employee
where Id in (select e1.Id
from Employee e1, Employee e2
WHERE e1.Company = e2.Company
GROUP BY e1.Id
HAVING SUM(CASE WHEN e1.Salary >= e2.Salary THEN 1 ELSE 0 END) >= COUNT(*)/2 AND SUM(CASE WHEN e1.Salary <= e2.Salary THEN 1 ELSE 0 END) >= COUNT(*)/2)
GROUP BY Company, Salary
ORDER BY Company
571. 给定数字的频率查询中位数
Numbers 表保存数字的值及其频率。
新增两列,将从后往前和从前往后的频数相加,两个数都需要大于等于总数一半,再取平均
select avg(tmp.`number`) as median
from (
select `Number`, Frequency,
sum(Frequency) over(order by `number`) snk1,
sum(Frequency) over(order by `number` desc) snk2
from numbers
) tmp
where tmp.snk1>=(select sum(Frequency) from numbers)/2
and tmp.snk2>=(select sum(Frequency) from numbers)/2
585. 2016年的投资
查找条件
1.他在 2015 年的投保额 (TIV_2015) 至少跟一个其他投保人在 2015 年的投保额相同。
2.他所在的城市必须与其他投保人都不同(也就是说维度和经度不能跟其他任何一个投保人完全相同)
不用concat()直接用lat,lon分组查询也可以
select sum(TIV_2016) as TIV_2016 from insurance
where TIV_2015 in
(select TIV_2015
from insurance
group by TIV_2015
having count(TIV_2015)>1 )
and concat(lat,lon) in (
select concat(lat,lon)
from insurance
group by concat(lat,lon)
having count(*)=1
)
窗口函数
select sum(TIV_2016) as TIV_2016 from(
select *,
count(PID) over (partition by TIV_2015) as num_tiv,
count(PID) over(partition by LAT,LON) as num_city
from insurance)h
where h.num_tiv >1 and num_city=1
597. 好友申请 I :总体通过率
分别统计两个表不重复的,然后做商,但是!当 friend_request 表为空时,总请求数,也就是分母,有可能为 0。所以我们需要使用 ifnull 函数来处理这种特殊情况。
select round(
ifnull(
(select count(distinct requester_id ,accepter_id) from request_accepted) /
(select count(distinct sender_id ,send_to_id) from friend_request)
,0)
,2) as accept_rate
select round(ifnull(count(distinct b.requester_id, b.accepter_id)/count(distinct a.sender_id, a.send_to_id),0), 2) accept_rate
from friend_request a, request_accepted b
607. 销售员
给定 3 个表: salesperson, company, orders。
输出所有表 salesperson 中,没有向公司 ‘RED’ 销售任何东西的销售员。
select s.name
from salesperson s
left join orders o on s.sales_id=o.sales_id
left join company c on o.com_id =c.com_id
group by s.name
having sum(if(c.name='RED',1,0))=0
子查询+in
SELECT name
FROM salesperson
WHERE sales_id NOT IN
(
SELECT o.sales_id
FROM company AS c INNER JOIN orders AS o
ON c.com_id = o.com_id
WHERE c.name = 'RED'
);
612. 平面上的最近距离
写一个查询语句找到两点之间的最近距离,保留 2 位小数
min、sqrt、power、round、where中可以两个列合并不等式
select round(min(sqrt(power(p1.x-p2.x,2) + power(p1.y-p2.y,2))),2) shortest
from point_2d p1, point_2d p2
where (p1.x, p1.y) <> (p2.x,p2.y)
614. 二级关注者
在 facebook 中,表 follow 会有 2 个字段: followee, follower ,分别表示被关注者和关注者。
请写一个 sql 查询语句,对每一个关注者,查询关注他的关注者的数目。
select followee as follower,count(distinct follower) as num
from follow
where followee in (select follower from follow)
group by followee
order by follower
-- select f1.follower,count(distinct f2.follower) as num
-- from follow f1 ,follow f2
-- where f1.follower=f2.followee
-- group by f1.follower
-- having count(*)>0
1083. 销售分析 II
编写一个 SQL 查询,查询购买了 S8 手机却没有购买 iPhone 的买家。注意这里 S8 和 iPhone 是 Product 表中的产品。
not in +子查询
select distinct s.buyer_id
from product p, sales s
where p.product_id = s.product_id
and s.buyer_id not in
(select s.buyer_id
from product p, sales s
where p.product_id = s.product_id
and p.product_name = 'iphone')
and p.product_name = 'S8'
sum
select s.buyer_id
from product p, sales s
where p.product_id = s.product_id
group by s.buyer_id
having sum(p.product_name='S8') > 0 and sum(p.product_name='iphone') < 1
1084. 销售分析III
编写一个SQL查询,报告2019年春季才售出的产品。即仅在2019-01-01至2019-03-31(含)之间出售的商品。
SELECT p.product_id,product_name FROM sales s,product p
WHERE s.product_id=p.product_id
GROUP BY p.product_id
HAVING SUM(sale_date < '2019-01-01')=0
AND SUM(sale_date>'2019-03-31')=0;