聚合函数（基础版）

QUESTION ONE:

# Write your MySQL query statement below
select id,movie,description,rating
from cinema
where description <> 'boring' and mod(id,2) = 1
order by rating desc
1
2
3
4
5

很简单的两个条件，一个通过 <> 解决不等于的情况，而确定奇数，可以 id % 2 = 1，也可以使用mod(id,2) = 1实现。

QUESTION TWO:

# Write your MySQL query statement below
select product_id,round(sum(sum_price)/sum(units),2) average_price from 
(select p.product_id as product_id,
   price * units as sum_price,
   units as units
   from Prices p left join UnitsSold u
   on u.purchase_date between p.start_date and p.end_date and p.product_id = u.product_id) t
group by product_id 

1
2
3
4
5
6
7
8
9

本题要能想到构建临时表，要得到一个product_id的产品价值总和，很容易想到对price * units,但是price 和 units是需要通过时间确认的，所以我们连接两表，连接的条件有两个，一个就是时间的限定，通过between and实现实现限制，还有一个条件便是product_id一致，这个如果忽略的话，不同的id可能存在时间段符合的情况。有了product_id、对应时间的总价值、units，然后就是简单的数值处理，最后按照product_id排序。

QUESTION THREE:

# Write your MySQL query statement below
select p.Project_id, round(sum(experience_years)/count(1),2) as average_years from Project p
left join Employee e
on p.employee_id = e.employee_id 
group by p.Project_id
1
2
3
4
5

本题主要是要得到每个project中的experience总和以及员工人数总和，因此我们需要将两张表的数据进行拼接，让临时表同时拥有Project_id和experience_years，拼接条件为employee_id 一致，最后通过round函数实现保留两位小数的四舍五入的操作。

QUESTION FOUR:

# Write your MySQL query statement below
select r.contest_id, round(count(1) / (select count(1) from Users ) * 100,2) as percentage 
from Register r left join Users u
on r.user_id = u.user_id
group by contest_id
order by percentage desc,r.contest_id
1
2
3
4
5
6

和上一题的情况类似，我们需要联立两表后进行计算，而percentage 的分子为分组后每个contest_id对应的user_id的数量，而分母为Users 的数据条数，最后按照条件进行排序。

QUESTION FIVE:

# Write your MySQL query statement below
select query_name,
round(avg(rating/position),2) as quality,
round(sum(if(rating < 3,1,0)) * 100 / count(1),2) as poor_query_percentage 
from Queries 
group by query_name
1
2
3
4
5
6

本题和前面的题目相比，变为了只有一张表进行数据处理，quality的求值可以直接通过avg聚合函数实现，这样省去了许多的sql，而poor_query_percentage 的数值可以 通过if聚合函数实现，判断rating是否小于三来对结果进行求和。

QUESTION SIX:

# Write your MySQL query statement below
select date_format(trans_date,'%Y-%m') as month,country,
count(trans_date) as trans_count,
sum(if(state = 'approved',1,0)) as approved_count,
sum(amount) as trans_total_amount,
sum(if(state = 'approved',amount,0)) as approved_total_amount
from Transactions 
group by month,country
1
2
3
4
5
6
7
8

本题类似一个大杂烩，涉及到了比较基础的聚合函数的使用，date_format能使日期按照自定义的形式进行转变，count进行计数，if进行判断.

QUESTION SEVEN:

# Write your MySQL query statement below
select round(sum(if(order_date = customer_pref_delivery_date,1,0) *100) / count(*),2) as immediate_percentage 
from Delivery 
where (customer_id,order_date) in
    (select customer_id,min(order_date)
    from Delivery
    group by customer_id)
1
2
3
4
5
6
7

本题的难点在于如何判断订单的日期是不是首日，其实转变思想，只需要取最小的order_date即可，让最后的order_date结果在 “取最小的order_date”的集合中即可，当然还需要customer_id来确定，是不是同一个顾客。其他知识点主要是聚合函数的使用。

QUESTION EIGHT:

select round(avg(a.event_date is not null), 2) fraction
from 
    (select player_id, min(event_date) as login
    from activity
    group by player_id) p 
left join activity a 
on p.player_id=a.player_id and datediff(a.event_date, p.login)=1

1
2
3
4
5
6
7
8

涉及到今天和明天的时间对比，但我们只有一张表，很容易想到将一张表分为两张表，两张表的player_id一致，但是得取出首日，通过min获取最小的日期，然后两表联立，判断是否存在比首日大一天的日期数据，如果存在event_date就会有值，否则为null，这样我们就可以使用avg聚合函数直接算出比例。