leetcode sql总结2

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;