sql-聚合函数

620. 有趣的电影 - 力扣（LeetCode）

select id,
    movie,
    description,
    rating
from cinema
where id%2=1 and description != 'boring'
order by rating desc

　　注意奇数的定义，余数为1的算奇数，为0的偶数

 

1251. 平均售价 - 力扣（LeetCode）

select p.product_id,
round(ifnull(sum(p.price*u.units)/sum(u.units),0),2) as average_price 
from Prices p
left join UnitsSold u
    on p.product_id  = u.product_id and
    u.purchase_date between p.start_date and p.end_date   
group by p.product_id 

　　注意：需要用到ifnull函数，当值为null时，即不存在，需要归0

若没有这个测试案例，则不需要直接相除即可

1075. 项目员工 I - 力扣（LeetCode）

SELECT project_id, 
round(avg(experience_years),2) as average_years 
from Project p
join Employee e
    using (employee_id)
group by p.project_id

 

 

1633. 各赛事的用户注册率 - 力扣（LeetCode）

SELECT r.contest_id,
round((count(r.user_id) / (SELECT count(user_id)from Users)) * 100
    ,2) as percentage 
from Users u
right join Register r
    on u.user_id = r.contest_id
GROUP BY r.contest_id 
ORDER BY percentage desc,r.contest_id 

　　以Register表为准，使用right join右连接

1211. 查询结果的质量和占比 - 力扣（LeetCode）

select query_name,
round(sum(rating / position) / count(query_name),2) AS quality,
round(sum(if(rating<3,1,0))*100 / count(query_name),2) as poor_query_percentage
FROM Queries 
group by query_name

　　百分数计算，结果×100

1193. 每月交易 I - 力扣（LeetCode）

select date_format(trans_date,'%Y-%m') as month,
    country,
    count(state) as trans_count,
    count(if(state='approved',1,null))as approved_count,
    sum(amount) as trans_total_amount,
    sum(if(state='approved',amount,0))approved_total_amount 
from Transactions 
group by month,country 

　　date_form(date,'%Y-%m')函数

1174. 即时食物配送 II - 力扣（LeetCode）

select round((count(case when t.customer_pref_delivery_date=t.order_date then 1 end)/count(t.customer_id))
    *100,2) as immediate_percentage
from
(
    select customer_id,
    min(order_date) as order_date,
    min(customer_pref_delivery_date) as customer_pref_delivery_date
    from Delivery
    group by customer_id

)t

　　

　　count(*) 计算的是：有多少个客户的首次订单记录

550. 游戏玩法分析 IV - 力扣（LeetCode）

 

select round(count(a.player_id )/(select count(distinct player_id)from Activity)
    ,2)
    as fraction  
from 
(
    select player_id,
        min(event_date) as event_date
    from Activity
    group by player_id
)t
left join Activity a
    on a.player_id = t.player_id and
    datediff(a.event_date, t.event_date) = 1

　　还有一种优化的方式，将计算时的分母优化

round(count(a.player_id)/count(t.player_id), 2)as fraction  
用count(t.player_id)

　因为

• 内层查询 t ：每个玩家只有一条记录（首次登录日期）

• LEFT JOIN 保证 t 表的每一行都会被保留

• t.player_id 永远不会是 NULL