SQL题目解析：外卖平台数据分析

天冬忘忧

已于 2024-09-10 20:11:41 修改

阅读量750

点赞数 25

分类专栏： SQL 文章标签： sql 数据库 mysql hive

于 2024-09-10 20:03:41 首次发布

本文链接：https://blog.csdn.net/weixin_64726356/article/details/142107342

版权

SQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

在本次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;