头哥sql魔鬼训练——实验11 SQL互联网业务查询-2

任务描述

有的同学会购买网上训练课程来学习，某教育网站对购买记录会产生订单存到数据库，有一个订单信息表(order_info)，表的部分内容如下：

第1行表示user_id为557336的用户在2021-10-10的时候使用了client_id为1的客户端下了C++课程的订单，但是状态为没有购买成功。第2行为购买成功。

查询满足以下条件的用户： 找出双11预热期间，(2021.10.16-2021.10.31)，各product_name(要呈现)的用户未完成订单率(%后显示2位小数)排前三名(降序排序，不为0，且用dense_rank函数)的user_id、rnk(名次)、incomp_rate(未完成订单率)，并先按product_name升序排序,再按rnk升序排序。

函数提示 1、round(x,y):按y位小数，对x四舍五入。

2、concat(s1,s2...sn):字符串s1,s2等多个字符串合并为一个字符串。

3、dense_rank() over(partition by a字段order by b字段)：窗口函数按a字段分组，按b字段排序(缺省升序)生成从1开始数值编号，多值并列但排名仅+1。 (说明：和rank() over 的作用相同，区别在于dense_rank() over 排名是密集连续的。例如学生排名，使用这个函数，成绩相同的两名是并列，下一位同学接着下一个名次。即：1 1 2 3 4 5 5 6)

4、if函数是MySQL中的条件函数之一，用于在满足条件时返回一个值，否则返回另一个值。IF函数的语法如下：

1. if(condition, true_value, false_value)

其中，condition是一个布尔表达式，true_value是满足条件时返回的值，false_value是不满足条件时返回的值。

5、在MySQL中，我们可以使用if和sum函数的组合来实现多条件计算。假设我们有一个名为"orders"的表格，其中包含了订单信息，包括订单ID、客户ID、订单金额amount和订单状态status。现在我们要计算不同状态订单的总金额，可以使用如下的MySQL查询语句：

1. select
2. sum(if(status = 'pending', amount, 0)) as pending_amount,
3. sum(if(status = 'completed', amount, 0)) as completed_amount,
4. sum(if(status = 'canceled', amount, 0)) as canceled_amount
5. from
6. orders;

6、MySQL语句with是 MySQL 8.0中的一个新特性，用于帮助简化复杂查询以及提高查询效率。它是一种临时表的方式，得到的结果集可以作为查询的结果集。在with语句中，可以定义多个别名，然后嵌套使用。

1. with t1 as (
2. select col1 from table1
3. ),
4. t2 as (
5. select col1 from table2
6. )
7. --使用
8. select * from t1 inner join t2 on t1.col1 = t2.col1;

在这个例子中，我们定义了两张表，t1和t2，然后使用了这两张表来进行查询，这样我们就可以避免在查询语句中多次嵌套使用相同的子查询，并且也不需要为每个查询定义一个临时表。

注意，本次实验切换到了mysql8.0，要注意以下问题： 1、order by的关键字段，要来自select关键字段； 2、group by的关键字段，要和select关键字段一致.

示例1 输入： drop table if exists order_info; create table order_info( id int(4) not null, user_id int(4) not null, product_name char(20) not null, status char(20) not null, client_id int(4) not null, date date not null, primary key (id));

insert into order_info values (1,557336,'C++','no_completed',1,'2021-10-10'), (2,230173543,'Python','completed',2,'2021-10-12'), (3,230173543,'Python','completed',2,'2021-10-15'), (4,557336,'Java','completed',1,'2021-10-15'), (5,230173543,'Java','no_completed',2,'2021-10-16'), (6,230173543,'Java','completed',2,'2021-10-20'), (7,230173543,'C++','completed',2,'2021-10-21'), (8,557336,'Java','completed',1,'2021-10-21'), (9,557336,'离散数学','completed',1,'2021-10-22'), (10,230173543,'离散数学','completed',2,'2021-10-22'), (11,663466,'离散数学','completed',3,'2021-10-22'), (12,663466,'C++','completed',3,'2021-10-22'), (13,663466,'Python','no_completed',3,'2021-10-23'), (14,663466,'Python','completed',3,'2021-10-24'), (15,663466,'C++','completed',3,'2021-10-25'), (16,8912311,'Java','completed',1,'2021-10-25'), (17,3345600,'高等数学','completed',1,'2021-10-27'), (18,3345600,'数学分析','completed',1,'2021-10-28'), (19,3345600,'数据库系统','completed',1,'2021-10-28'), (20,663466,'C++','no_completed',3,'2021-10-29'), (21,557336,'Python','no_completed',3,'2021-10-29'), (22,557336,'Python','completed',2,'2021-10-29'), (23,230173543,'离散数学','no_completed',2,'2021-10-29'), (24,663466,'离散数学','no_completed',3,'2021-10-29'), (25,230173543,'Python','no_completed',2,'2021-10-30'), (26,557336,'Python','completed',2,'2021-10-30'), (27,823345,'Python','no_completed',1,'2021-10-31'), (28,823345,'Python','no_completed',1,'2021-10-31'), (29,823345,'Python','completed',1,'2021-10-31'), (30,230173543,'Python','no_completed',4,'2021-10-31'), (31,3345600,'数据库系统','no_completed',1,'2021-11-01');

输出： product_name user_id rnk incomp_rate C++ 663466 1 33.33% Java 230173543 1 50.00% Python 230173543 1 100.00% Python 823345 2 66.67% Python 663466 3 50.00% 离散数学 230173543 1 50.00% 离散数学 663466 1 50.00%

 

WITH temp AS ( -- 创建一个临时表 temp
  SELECT
    user_id, -- 选择 user_id 列
    product_name, -- 选择 product_name 列
    SUM(CASE WHEN status = 'no_completed' THEN 1 ELSE 0 END) AS no_completed_count, -- 计算每个 user_id 和 product_name 组合的 no_completed 订单数量
    SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed_count -- 计算每个 user_id 和 product_name 组合的 completed 订单数量
  FROM
    order_info -- 从 order_info 表中选取数据
  WHERE
    date BETWEEN '2021-10-16' AND '2021-10-31' -- 筛选出 2021 年 10 月 16 日到 10 月 31 日之间的订单数据
  GROUP BY
    user_id, -- 按 user_id 和 product_name 进行分组
    product_name
),
ranked AS ( -- 创建一个派生表 ranked
  SELECT
    product_name, -- 选择 product_name 列
    user_id, -- 选择 user_id 列
    CASE WHEN no_completed_count + completed_count = 0 THEN 0 -- 如果订单总数为 0,则设置 incomp_rate 为 0
         ELSE no_completed_count / (no_completed_count + completed_count) -- 否则计算未完成订单率 incomp_rate
    END AS incomp_rate, -- 别名为 incomp_rate
    DENSE_RANK() OVER (PARTITION BY product_name ORDER BY (no_completed_count / (no_completed_count + completed_count)) DESC) AS rnk -- 计算每个 product_name 内 incomp_rate 的降序排名,并将排名赋值给 rnk 列
  FROM
    temp -- 从临时表 temp 中选取数据
)

SELECT
  product_name, -- 选择 product_name 列
  user_id, -- 选择 user_id 列
  rnk, -- 选择 rnk 列,即每个 product_name 内 incomp_rate 的排名
  CONCAT(ROUND(incomp_rate * 100, 2), '%') AS incomp_rate -- 将 incomp_rate 乘以 100 并保留两位小数,最后使用百分号格式化输出
FROM
  ranked -- 从派生表 ranked 中选取数据
WHERE
  incomp_rate <> 0 AND rnk <= 3 -- 筛选出 incomp_rate 不为 0 且排名前三的记录
ORDER BY
  product_name, rnk; -- 按照 product_name 升序和 rnk 升序进行排序输出