注意一些语法
1、group by出现在having前面，但是having中所使用的聚合必须是select中的
2、date类型之间的比较：datediff（）= 差的绝对值 or 用字符框起来比较边界
3、算日期长度需要相减之后加一
4、round(, n)n默认是0，到最近的整数
5、 几个算percentage的题目都要好好看
题目
-- 注意left join
-- 基准是求quality的name列
select q.query_name, q.quality, round(ifnull(100 *p.poor_query / q.total, 0), 2) as poor_query_percentage
from(
select query_name, round(sum(rating / position) / count(*), 2) as quality, count(*) as total
from Queries
group by query_name
) as q
left join (
select query_name, count(*) as poor_query
from Queries
where rating < 3
group by query_name
) as p
on q.query_name = p.query_name
where q.query_name is not null
# Write your MySQL query statement below
select DATE_FORMAT(trans_date, '%Y-%m') as month, -- 注意时间的提取格式
country,
count(*) as trans_count,
count(case when state = 'approved' then 1 else null end) as approved_count,
sum(amount) as trans_total_amount,
sum(case when state = 'approved' then amount else 0 end) as approved_total_amount
from Transactions
group by month, country
-- min提取最小的一个日期作为注册时间
-- 注意在这里sum和count的区别：count统计行数，sum统计括号里的数字累计和，
-- 所以，如果让满足条件的case为1，不满足的为0，将他们sum之后得到的就是满足条件的总数，除以全部的个数（行数，count）得到比例
-- 注意，百分比的100* 应该写在round函数里面，因为结果需要保留2位
WITH FirstEvent AS (
SELECT player_id, MIN(event_date) AS first_event_date
FROM Activity
GROUP BY player_id
)
SELECT
ROUND(SUM(CASE
WHEN DATEDIFF(a.event_date, fe.first_event_date) = 1 THEN 1
ELSE 0
END) / COUNT(DISTINCT a.player_id), 2) AS fraction
FROM Activity a
JOIN FirstEvent fe
ON a.player_id = fe.player_id;
-- 首先选出最早的日期，这里用all
-- 然后case when将满足条件的标记为1，sum之后的结果除以总行
-- 注意，这里已经用最早的一天筛选出了全部顾客第一天的日期，所以同时，顾客的id也是distinct的
select round(100 * sum(case when d1.order_date = d1.customer_pref_delivery_date then 1 else 0 end) / count(*), 2) as immediate_percentage
from Delivery d1
where d1.order_date <= all(
select order_date
from Delivery d2
where d1.customer_id = d2.customer_id
)
-- 注意productid可能在p中但是不在s中，所以不能只用not in来约束
-- having放在 where 和 group 后面
select p.product_id, p.product_name
from Product p, Sales s
where p.product_id = s.product_id
group by p.product_id
having min(sale_date) >= '2019-01-01' and max(sale_date) <= '2019-03-31'
-- max函数在没有值的时候返回null
select max(n.num) as num
from (
select num
from MyNumbers
group by num
having count(*) = 1
) n
-- 注意group by 和 count的顺序以及作用对象。这里group by作用于后面的count聚合函数，但是需要写在前面
-- 本题customer表格可能会出现重复，所以count 中需要加 distinct
select customer_id
from Customer
group by customer_id
having count(distinct product_key) = (select count(*) from Product)
-- 对于异步行，一般是需要用自查询，需要创建多个表
select DISTINCT l1.Num AS ConsecutiveNums
from Logs l1, Logs l2, Logs l3
where l1.Id = l2.Id - 1
and l2.Id = l3.Id - 1
and l1.num = l2.num
and l2.num = l3.num
-- 对于同行的比较和 case when 表达式
select x, y, z,
case when x + y > z and x + z > y and z + y > x then 'Yes' else 'No' end as 'Triangle'
from Triangle 
-- with写法和之前的一道题一样，从重复列作为manager，null也会被返回，需要去掉
-- distinct：where会产生笛卡尔积，需要去重
select e.employee_id, e.name, m.reports_count, m.average_age
from Employees e
join (
select distinct reports_to as managerid, round(sum(age) / count(*)) as average_age, count(*) as reports_count
from Employees
where reports_to is not null
group by reports_to) m
on e.employee_id = m.managerid
order by e.employee_id
