高频 sql 50题（基础3）

你有柿嘛

已于 2024-02-28 20:41:58 修改

阅读量789

点赞数 20

分类专栏： sql 文章标签： sql

于 2024-02-28 20:19:05 首次发布

本文链接：https://blog.youkuaiyun.com/chenmumu233/article/details/136353507

版权

sql 专栏收录该内容

11 篇文章

订阅专栏

文章详细介绍了SQL中的聚合函数在不同场景下的应用，如计算电影奇数ID、平均售价、员工经验、用户注册率、查询质量、交易统计和即时配送比例，以及游戏玩法中的用户行为分析。每个示例都展示了如何使用SQL进行数据处理和计算。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

三、聚合函数

1.有趣的电影

题目描述

代码编写

做法一:

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

总结判断奇数的常用方法：

1.mod（x,1）= 1

2.x % 2 = 1

3.x & 1 = 1

4.power（-1，x）=-1

做法二（利用位运算，参考网友做法）

select * 
from cinema
where description <> 'boring' and id & 1
order by rating desc;

Tip：

1.<>的作用与！=相似，都是不等于，非的意思

2.& 是逻辑“与”运算，在对两个二进制数的每一位进行逻辑运算的时候，只有两个二进制数的对应位都是 1 的时候，返回结果才会是1。在这道题中，当id为奇数的时候，它对应的最小数位必然是1，此时返回结果是1，比如id是5，001 & 101，最后一位都是1，返回1。

2.平均售价

题目描述

代码编写

select p.product_id,ifnull(round(sum(price*units)/sum(units),2),0)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

3.项目员工

题目描述

代码编写

select project_id,round(avg(e.experience_years),2) as average_years
from Project p left join Employee e
on p.employee_id=e.employee_id
group by p.project_id

4.赛事的用户注册率

题目描述

代码编写

select contest_id,
round(count(user_id)*100/(select count(*) from Users),2) as percentage from Register
group by contest_id
order by percentage desc,contest_id asc

5.查询结果的质量和占比

题目描述

代码编写

select 
query_name,
round(avg(rating/position),2) as quality,
round(avg(rating<3)*100,2) as poor_query_percentage 
from Queries
where query_name is not null
group by query_name

Tip：

AVG（）函数是可以写逻辑判断的，AVG（条件）相当于SUM（IF（条件，1，0））/COUNT(全体)。

6.每月交易

题目描述

代码编写

select date_format(trans_date,'%Y-%m')as month ,
country ,
count(*) 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

Tip：

DATE_FORMAT(date，format)

date：日期

format：规定日期/时间格式

常用常数：

%Y：4位数字年份

%y：2位数字年份

%M：月份名字（January...December）

%m：数字月份（00,01，...12）

%d：月份中的天数（00,01，...31）

%e：月份中的天数（0,1，...31）

了解该函数可以点击以下链接，转自山茶花开时的博客

[Mysql] DATE_FORMAT函数_mysql date_format-优快云博客

7.即时食物配送

题目描述

题目分析：

这道题目的要求是求出即时订单在所有用户的首次订单中的比例

难点在于要判断该订单必须为“首次订单” 且“即时订单”

1.将顾客id分组，筛选出最小的订单日期，即“首次订单”

2.判断首次订单的下单日期和配送日期是否相同，即判断“即时订单”或“计划订单”

3.利用AVG函数计算首次订单是即时订单的比例并保留两位小数

代码编写

select 
round(avg(if(order_date=customer_pref_delivery_date,1,0))*100,2)as immediate_percentage 
from delivery
where (customer_id,order_date) in (
    select customer_id,min(order_date) from delivery
    group by customer_id
)

8.游戏玩法分析

题目描述：

题目思路：

题目的要求是算出在首次登录的第二天再次登录的玩家的比率

题目的难点在于筛出第二天再次登录的玩家

1.筛选出玩家id和最早登录日期

2.筛选出id一致，但登录日期与最早登录日期相差一天的玩家

3.计算该部分玩家与所有玩家的比例

代码编写

select round(count(a1.player_id)/(select count(distinct player_id) from Activity),2)as fraction 
from Activity a1 join (
    select player_id,min(event_date) as event_date
    from Activity 
    group by player_id
) a2 on a1.player_id=a2.player_id
where datediff(a1.event_date,a2.event_date)=1