# 分组 统计 计算 注意判断null
select ad_id,
ifnull(round(sum(action="Clicked")/sum(action<>"Ignored")*100,2),0.00) as ctr # 主要是sum函数和判空
from ads
group by ad_id
order by ctr desc,ad_id # 一个升序一个降序排列
## 585.2 代码实现
# Write your MySQL query statement below
# 法一：窗口函数
# SELECT ROUND(SUM(TIV_2016), 2) AS TIV_2016
# FROM (
# SELECT TIV_2016,
# COUNT(*) OVER(PARTITION BY TIV_2015) AS count_tiv_2015,
# COUNT(*) OVER(PARTITION BY lat, lon) AS count_lat_lon
# FROM insurance
# ) AS temp
# WHERE count_lat_lon = 1 and count_tiv_2015 > 1
# # 法二：连接
# SELECT ROUND(SUM(distinct i1.TIV_2016),2) as TIV_2016
# FROM insurance i1, insurance i2
# WHERE i1.TIV_2015 = i2.TIV_2015
# AND i1.PID != i2.PID
# AND (i1.LAT,i1.LON) NOT IN (SELECT i2.LAT,i2.LON FROM insurance i2 WHERE i1.PID != i2.PID) # 难点：多列
# 法三：嵌套子查询
SELECT
ROUND(SUM(insurance.TIV_2016),2) AS TIV_2016
FROM
insurance
WHERE
insurance.TIV_2015 IN
(
SELECT
TIV_2015
FROM
insurance
GROUP BY TIV_2015
HAVING COUNT(*) > 1
)
AND CONCAT(LAT, LON) IN
(
SELECT
CONCAT(LAT, LON)
FROM
insurance
GROUP BY LAT , LON
HAVING COUNT(*) = 1
)
# Write your MySQL query statement below
# 法一：窗口函数 + 嵌套子查询
# SELECT temp.product_name, temp.unit
# FROM (
# SELECT distinct Products.product_name AS product_name, SUM(Orders.unit) OVER(PARTITION BY Products.product_id ORDER BY Products.product_id) AS unit
# FROM Products join Orders on Products.product_id = Orders.product_id
# WHERE Orders.order_date like '2020-02%'
# ) AS temp
# WHERE temp.unit >= 100
# 法二：
SELECT distinct Products.product_name, SUM(unit) AS unit
FROM Products join Orders on Products.product_id = Orders.product_id
WHERE Orders.order_date like '2020-02%'
GROUP BY Products.product_id HAVING SUM(unit) >= 100
# Write your MySQL query statement below
# 法一：右外连接+范围判断
# SELECT Students.id, Students.name
# FROM Departments RIGHT JOIN Students ON Departments.id = Students.department_id
# WHERE Students.department_id NOT IN (
# SELECT id
# FROM Departments
# )
SELECT distinct s.id, s.name
FROM departments d RIGHT JOIN Students s ON d.id = s.department_id
WHERE d.id is null
# 要搞清楚右外连接的输出范围，技巧在于null值的处理
# Write your MySQL query statement below
# 左外连接（关于范围和输出的空值处理）
SELECT EmployeeUNI.unique_id, Employees.name
FROM Employees LEFT JOIN EmployeeUNI ON Employees.id = EmployeeUNI.id