15- 22题聚合函数 - 高频 SQL 50 题基础版

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
where
    description!='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 - 各赛事的用户注册率

在这里插入图片描述

-- 注册率=注册用户数/所有用户数
select
    r.contest_id,round(100*count(*)/(select count(*) from Users),2) percentage 
from 
    Register r
group by 
    r.contest_id
order by
     percentage 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 
from
    Queries
group by
    query_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
from
    Transactions 
group by
    month,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 
from
    Delivery 
where 
    (customer_id,order_date) in 
        (select
            customer_id,min(order_date)
        from 
            Delivery
        group by
            customer_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  
from
    Activity 
where
    -- 如果日期加一天的数据能在表中查到，说明连续登录了两天
    (player_id,event_date) in 
    (select 
        player_id,date_add(min(event_date),INTERVAL 1 DAY) 
    from 
        Activity 
    GROUP BY
        player_id
     );