在本次SQL中,我们将通过一系列查询来分析外卖平台的数据,包括用户、餐厅和订单信息。这些查询将帮助我们理解用户行为、餐厅表现和订单趋势。
题目部分
数据
见文章上方
某外卖平台有关于如下的表
users(用户表)
- user_id(用户 ID)
- user_name(用户名)
- password(用户密码)
- email(电子邮件)
- phone_number(手机号码)
- address(地址)
restaurants(餐厅)
- restaurant_id(餐厅 ID)
- restaurant_name(餐厅名字)
- restaurant_address(餐厅地址)
- restaurant_phonenumber(餐厅电话)
- cuisine_type(菜系类型)
orders(订单表)
- order_id(订单 ID)
- user_id(用户 ID)
- restaurant_id(餐厅 ID)
- order_date(订单日期)
- total_amount(订单总额)
- order_status(订单状态)
根据如上表格信息,实现如下需求
1、查询五一期间(2023-05-01 ~ 2023-05-07),每个餐厅的订单总数量及排名
2、查看最近一个月内在一家餐厅重复购买3次以上的餐厅名字、用户名字、购买次数
3、查看订单最多的用户ID、用户名(包含并列第一的情况)
4、查询每个餐厅最近一个月内经常来消费的顾客前三名,需要查询出顾客的名字
5、查询在五一期间,每个顾客在每一个餐厅的平均消费金额
6、查询具有单笔最高金额的订单的餐厅名字
7、查询每一个顾客的总下单数量和总消费金额
8、查询五一期间,消费总金额高于平均值的用户
9、查询最后一个订单距离现在时间超过 5 天的用户,以及天数
10、查询至少被三个不同用户消费过的餐厅
11、查询每一个用户在最近一个月内的订单量,以及上一个月的订单量
12、查询在五一期间,每个用户消费的餐厅前三名(按照消费的金额计算)
13、查询具有最多订单的餐厅所在城市的名称和订单数量
14、查询每一个餐厅当月订单数量、上月订单数量、订单数量环比增长率,按照环比增长率降序排列
15、查询每个用户的订单金额排名,显示排名前三的用户及其订单金额
解答
题目1
1、查询五一期间(2023-05-01 ~ 2023-05-07),每个餐厅的订单总数量及排名
with t as (
-- 五一期间每一个餐厅的订单总数
select restaurant_id, count(1) totalNum from orders
where order_date >= '2023-05-01 00:00:00' and order_date <= '2023-05-07 23:59:59'
group by restaurant_id
) select *, dense_rank() over (order by totalNum desc) from t ;
题目2
2、查看最近一个月内在一家餐厅重复购买3次以上的餐厅名字、用户名字、购买次数
select restaurant_name, user_name, count(*) as `购买次数` from restaurants
join orders on orders.restaurant_id = restaurants.restaurant_id
join users on orders.user_id = users.user_id
where order_date >= date_sub((select max(order_date) from orders), 30)
group by restaurant_name, user_name
having count(*) >= 3;
-- 如果是实时数据
with t as (
select r.restaurant_name,u.user_name,o.order_id from orders o , restaurants r ,users u
where o.restaurant_id = r.restaurant_id and o.user_id = u.user_id
and substr(o.order_date,1,10) >= add_months(`current_date`(),-1)
)
select t.restaurant_name,t.user_name,count(1) orderNum from t group by t.restaurant_name,t.user_name having orderNum >=3;
题目3
3、查看订单最多的用户ID、用户名(包含并列第一的情况)
-- 队用户订单进行排名
select u.user_id, user_name from users u join orders o on u.user_id = o.user_id
group by u.user_id, user_name
order by count(*) desc;
-- 方法一
with t as (
select u.user_id, user_name, count(*) as order_count
from users u join orders o on u.user_id = o.user_id
group by u.user_id, user_name
), t2 as (
select max(order_count) as max_count from t
) select user_id, user_name
from t join t2 on t.order_count = t2.max_count
order by order_count desc;
-- 方法二(比较简洁)
with t as (
select u.user_id,u.user_name,count(1) ordernum
from orders o join users u on o.user_id = u.user_id
group by u.user_id,u.user_name
),t2 as (
select *,dense_rank() over (order by ordernum desc) pm from t
)
select * from t2 where pm = 1;
题目4
4、查询每个餐厅最近一个月内经常来消费的顾客前三名,需要查询出顾客的名字
-- 方法一
select restaurant_name, user_name, count(*) as visit_count from restaurants
join orders on orders.restaurant_id = restaurants.restaurant_id
join users on orders.user_id = users.user_id
where order_date >= date_sub((select max(order_date) from orders), 30)
group by restaurant_name, user_name
order by restaurant_name, visit_count desc;
-- 方法二
with t as (
select restaurant_name, user_name, count(*) as visit_count from restaurants
join orders on orders.restaurant_id = restaurants.restaurant_id
join users on orders.user_id = users.user_id
where order_date >= date_sub((select max(order_date) from orders), 30)
group by restaurant_name, user_name
order by restaurant_name, visit_count desc
), t2 as (
select restaurant_name, user_name, visit_count,
dense_rank() over (partition by restaurant_name order by visit_count desc ) as ranks
from t
)select restaurant_name, user_name, visit_count, ranks from t2
where ranks <= 3;
题目5
5、查询在五一期间,每个顾客在每一个餐厅的平均消费金额
select u.user_name, r.restaurant_name, avg(o.total_amount) as `平均消费金额` from restaurants r
join orders o on r.restaurant_id = o.restaurant_id
join users u on o.user_id = u.user_id
where o.order_date between '2023-05-01' and '2023-05-07'
group by u.user_name, r.restaurant_name;
题目6
6、查询具有单笔最高金额的订单的餐厅名字
select max(total_amount) from orders;
select distinct restaurant_name, total_amount from restaurants r
join orders o on r.restaurant_id = o.restaurant_id
where o.total_amount = (select max(total_amount) from orders);
题目7
7、查询每一个顾客的总下单数量和总消费金额
select user_id,count(1),sum(total_amount) from orders group by user_id;
题目8
8、查询五一期间,消费总金额高于平均值的用户
-- 无一旗舰店平均值
select avg(total_amount) from orders
where substr(order_date,6,5) >='05-01' and substr(order_date,6,5) <='05-07';
-- 消费总金额高于平均值的用户
select user_id,sum(total_amount) totalMoney from orders
where substr(order_date,6,5) >='05-01' and
substr(order_date,6,5) <='05-07' group by user_id
having sum(total_amount) >(select avg(total_amount) from orders
where substr(order_date,6,5) >='05-01' and substr(order_date,6,5) <='05-07');
题目9
9、查询最后一个订单距离现在时间超过 5 天的用户,以及天数
select user_id,max(order_date),datediff(`current_date`(),max(order_date)) days
from orders group by user_id having max(user_id) < date_sub(`current_date`(),5);
题目10
10、查询至少被三个不同用户消费过的餐厅
select restaurant_id,count(distinct user_id) userNum
from orders group by restaurant_id having userNum>=3;
题目11
11、查询每一个用户在最近一个月内的订单量,以及上一个月的订单量
select
user_id,
count(case when substr(order_date,1,10) between
add_months(`current_date`(),-1) and `current_date`() then 1 else null end ) `最近一个月订单数`,
count(case when substr(order_date,1,10) between
add_months(`current_date`(),-2) and date_sub(
add_months(`current_date`(),-1),1) then 1 else null end ) `上个月订单数`
from orders group by user_id ;
题目12
12、查询在五一期间,每个用户消费的餐厅前三名(按照消费的金额计算)
select substr(order_date,6,5) from orders limit 1;
with t as (
select user_id,restaurant_id,sum(total_amount) orderMoney
from orders where substr(order_date,6,5) >='05-01' and substr(order_date,6,5) <='05-07'
group by user_id,restaurant_id
),t2 as (
select *,dense_rank() over (partition by user_id order by orderMoney desc) pm from t
) select * from t2 where pm <=3;
题目13
13、查询具有最多订单的餐厅所在城市的名称和订单数量
with t as (
select r.restaurant_name,r.restaurant_address,count(1) orderNum
from orders o join restaurants r on o.restaurant_id = r.restaurant_id
group by r.restaurant_name,r.restaurant_address
),t2 as (
select *,dense_rank() over ( order by orderNum desc) pm from t
) select * from t2 where pm = 1;
题目14
14、查询每一个餐厅当月订单数量、上月订单数量、订单数量环比增长率,按照环比增长率降序排列
-- 环比增长率:(当月订单数量 - 上月订单数量)/ 上月订单数量
-- 理解为:当月就是最近一个月 ,上月就是最近的两个月
with t as (
select restaurant_id,
count(case when substr(order_date,1,10) between
add_months(`current_date`(),-1) and `current_date`() then 1 else null end ) dydds,
count(case when substr(order_date,1,10) between
add_months(`current_date`(),-2) and date_sub(add_months(
`current_date`(),-1),1) then 1 else null end ) sydds
from orders group by restaurant_id
) select restaurant_id,(dydds-sydds)/sydds zzlv from t order by zzlv desc ;
题目15
15、查询每个用户的订单金额排名,显示排名前三的用户及其订单金额
with t as (
select user_id,sum(total_amount) totalMoney from orders group by user_id
),t2 as (
select *,dense_rank() over (order by totalMoney desc) pm from t
) select *,u.user_name from t2,users u where t2.user_id=u.user_id and pm <= 3;