1. 相關知識點

• 函數

函數	含義
order by	排序
group by	分組
between 小值 and 大值	取值范圍
date_format(trans_date,‘%Y-%m’)	日期轉換格式
count(*)	表示數據的行數，如果有分組，為分組后數據的行數
date_add(min(event_date),INTERVAL 1 DAY)	日期相加

2. 例子

2.15 - 有趣的電影

select* 
from cinema
wheredescription!='boring' and id%2!=0
order by rating desc;

2.16 - 平均售價

# 注意：between 小值 and 大值 select u.product_id, round(sum(u.units*p.price)/sum(u.units),2) average_price 
from Prices p left join UnitsSold u 
on p.product_id=u.product_id 
-- and
where u.purchase_date between p.start_date and p.end_date 
group by u.product_id;

2.17 - 項目員工 I

select-- round(sum(e.experience_years)/count(*),2) p.project_id,round(avg(e.experience_years),2) average_years
from Project p left join Employee e
on p.employee_id=e.employee_id 
group by p.project_id;

2.18 - 各賽事的用戶注冊率

-- 注冊率=注冊用戶數/所有用戶數
selectr.contest_id,round(100*count(*)/(select count(*) from Users),2) percentage 
from Register r
group by r.contest_id
order bypercentage desc,r.contest_id ASC;

2.19 - 查詢結果的質量和占比

-- round(avg(rating<3)*100,2)=round(avg(if(rating<3,1,0))*100,2) 
select query_name,round(avg(rating/position),2) quality,round(avg(if(rating<3,1,0))*100,2) poor_query_percentage 
fromQueries
group byquery_name;

2.20 - 每月交易 I

--  考點：日期轉換格式
--  date_format(trans_date,'%Y-%m')select date_format(trans_date,'%Y-%m') month,country,count(*) trans_count,sum(if(state='approved',1,0)) approved_count,sum(amount) trans_total_amount,sum(if(state="approved",amount,0)) approved_total_amount
fromTransactions 
group bymonth,country;

2.21 - 即時食物配送 II

-- sum(if(order_date=customer_pref_delivery_date,1,0))/count(*)=sum(order_date=customer_pref_delivery_date)/count(*)
-- count(*),表示數據的行數，如果有分組，為分組后數據的行數select round(100*sum(if(order_date=customer_pref_delivery_date,1,0))/count(*),2) immediate_percentage 
fromDelivery 
where (customer_id,order_date) in (selectcustomer_id,min(order_date)from Deliverygroup bycustomer_id);

2.22 - 游戲玩法分析 IV

• 考點： 聚合函數
  
  

# 日期相加 date_add(min(event_date),INTERVAL 1 DAY) 
select round(count(distinct player_id)/(select count(distinct player_id) from Activity),2) fraction  
fromActivity 
where-- 如果日期加一天的數據能在表中查到，說明連續登錄了兩天(player_id,event_date) in (select player_id,date_add(min(event_date),INTERVAL 1 DAY) from Activity GROUP BYplayer_id);