SQL进阶——数据分析

原创于 2026-07-08 00:01:44 发布 · 63 阅读

2 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

收录于

SQL经典实例

数据分析

1、查询每个区域的用户数

现在有一张全部用户信息表stu_table，这张表存储了id（用户ID）、name（姓名）、area（区域）和sex（性别）四个字段，现在我们想知道每个区域有多少用户，该怎么实现呢？

stu_table表如下所示。

在这里插入图片描述

-- 删除已存在表（如不需要可删除该行）
DROP TABLE IF EXISTS `stu_table`;

-- 创建表结构
CREATE TABLE `stu_table` (
  `id` INT PRIMARY KEY,
  `name` VARCHAR(20) NOT NULL,
  `area` VARCHAR(10) NOT NULL,
  `sex` VARCHAR(5) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 插入数据
INSERT INTO `stu_table` (`id`, `name`, `area`, `sex`) VALUES (4, '张文华', '二区', '男');
INSERT INTO `stu_table` (`id`, `name`, `area`, `sex`) VALUES (3, '李思雨', '一区', '女');
INSERT INTO `stu_table` (`id`, `name`, `area`, `sex`) VALUES (1, '王小凤', '一区', '女');
INSERT INTO `stu_table` (`id`, `name`, `area`, `sex`) VALUES (7, '李智瑞', '三区', '男');
INSERT INTO `stu_table` (`id`, `name`, `area`, `sex`) VALUES (6, '徐文杰', '二区', '男');
INSERT INTO `stu_table` (`id`, `name`, `area`, `sex`) VALUES (8, '徐雨秋', '三区', '男');
INSERT INTO `stu_table` (`id`, `name`, `area`, `sex`) VALUES (5, '张青云', '二区', '女');
INSERT INTO `stu_table` (`id`, `name`, `area`, `sex`) VALUES (9, '孙皓然', '三区', '男');
INSERT INTO `stu_table` (`id`, `name`, `area`, `sex`) VALUES (10, '李春山', '三区', '男');
INSERT INTO `stu_table` (`id`, `name`, `area`, `sex`) VALUES (2, '刘诗迪', '一区', '女');

解题思路如下。我们想要获取的是每个区域的用户数，首先需要对区域进行分组，使用的是group by，然后对每个组内的用户进行计数聚合运算，使用的是count，最后运行结果如下表所示。

select area, count(*) as stu_num
from stu_table
group by area;

+------+---------+
| area | stu_num |
+------+---------+
| 一区 |       3 |
| 二区 |       3 |
| 三区 |       4 |
+------+---------+

2、查询每个区域的男女用户数

本节使用的是1节的全部用户信息表stu_table，现在我们想知道每个区域内男生、女生分别有多少个。

我们不仅需要查询每个区域的信息，还需要分别查询每个区域内男生、女生的信息，主要考察的就是按照多列进行分组聚合的知识，直接在group by后面指明要分组的列名即可，且列名与列名之间用逗号分隔，最后运行结果如下表所示。

select area, sex, count(*) as stu_num
from stu_table
group by area, sex;

+------+-----+---------+
| area | sex | stu_num |
+------+-----+---------+
| 一区 | 女  |       3 |
| 二区 | 男  |       2 |
| 二区 | 女  |       1 |
| 三区 | 男  |       4 |
+------+-----+---------+

3、查询姓张的用户数

本节使用的是1节的全部用户信息表stu_table，现在我们想知道这张表中姓张的用户有多少个？

我们想要查询姓张的用户有多少个，首先需要思考的是怎么判断用户是否姓张，假设表中存储的姓名都是先姓后名的形式，那么就可以使用字符串匹配函数like；知道怎么判断用户是否姓张以后，接下来将这些用户筛选出来，使用的是where；最后针对筛选出来的用户进行计数，使用的是count，运行结果如下表所示。

select count(*) as stu_num
from stu_table
where name like '张%';

+---------+
| stu_num |
+---------+
|       2 |
+---------+

4、筛选出id3～id5的用户

本节使用的是1节的全部用户信息表stu_table，现在我们想要获取id按照从小到大的顺序排列以后id3～id5的用户的信息。

因为不确定id是否连续，所以无法直接用where来筛选id。我们先对id列进行升序排列，然后利用limit进行筛选，最后运行结果如下表所示。

select *
from stu_table
order by id asc 
limit 4,6;

+----+--------+------+-----+
| id | name   | area | sex |
+----+--------+------+-----+
|  5 | 张青云 | 二区 | 女  |
|  6 | 徐文杰 | 二区 | 男  |
|  7 | 李智瑞 | 三区 | 男  |
|  8 | 徐雨秋 | 三区 | 男  |
|  9 | 孙皓然 | 三区 | 男  |
| 10 | 李春山 | 三区 | 男  |
+----+--------+------+-----+

5、筛选出绩效不达标的员工

现在有一张员工绩效表score_table，这张表存储了id、name（姓名）、group（部门）和score（绩效得分）四个字段，现在我们想把绩效不达标（绩效得分小于60分）的员工的信息筛选出来。

score_table表如下所示。
在这里插入图片描述

-- 删除已存在表（如不需要可删除该行）
DROP TABLE IF EXISTS `score_table`;

-- 创建表结构
CREATE TABLE `score_table` (
  `id` INT PRIMARY KEY,
  `name` VARCHAR(20) NOT NULL,
  `group` VARCHAR(10) NOT NULL,
  `score` INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 插入数据
INSERT INTO `score_table` (`id`, `name`, `group`, `score`) VALUES (1, '王小凤', '一部', 88);
INSERT INTO `score_table` (`id`, `name`, `group`, `score`) VALUES (2, '刘诗迪', '一部', 70);
INSERT INTO `score_table` (`id`, `name`, `group`, `score`) VALUES (3, '李思雨', '一部', 92);
INSERT INTO `score_table` (`id`, `name`, `group`, `score`) VALUES (4, '张文华', '二部', 55);
INSERT INTO `score_table` (`id`, `name`, `group`, `score`) VALUES (5, '张青云', '二部', 77);
INSERT INTO `score_table` (`id`, `name`, `group`, `score`) VALUES (6, '徐文杰', '二部', 77);
INSERT INTO `score_table` (`id`, `name`, `group`, `score`) VALUES (7, '李智瑞', '三部', 56);
INSERT INTO `score_table` (`id`, `name`, `group`, `score`) VALUES (8, '徐雨秋', '三部', 91);
INSERT INTO `score_table` (`id`, `name`, `group`, `score`) VALUES (9, '孙皓然', '三部', 93);
INSERT INTO `score_table` (`id`, `name`, `group`, `score`) VALUES (10, '李春山', '三部', 57);

我们要获取绩效不达标的员工的信息，首先需要知道不达标的标准是什么，然后利用where来限定不达标的条件即可，最后运行结果如下表所示。

select *
from score_table
where score < 60;

+----+--------+-------+-------+
| id | name   | group | score |
+----+--------+-------+-------+
|  4 | 张文华 | 二部  |    55 |
|  7 | 李智瑞 | 三部  |    56 |
| 10 | 李春山 | 三部  |    57 |
+----+--------+-------+-------+

6、筛选出姓张的且绩效不达标的员工

本节使用的是5节的员工绩效表score_table，我们现在想根据这张表筛选出姓张的且绩效不达标的员工的信息。

这里面主要用到了多条件筛选的知识点，多个条件之间用and进行关联，然后将关联后的代码放在where后面即可，最后运行结果如下表所示。

select *
from score_table
where score < 60 and name like '张%';

+----+--------+-------+-------+
| id | name   | group | score |
+----+--------+-------+-------+
|  4 | 张文华 | 二部  |    55 |
+----+--------+-------+-------+

7、查询获得销售冠军超过两次的人

现在有一张month_table表记录了每月的销售冠军信息，这张表存储了id、name（姓名）和month_num（月份）三个字段，现在需要查询获得销售冠军的次数超过2次的人及其获得销售冠军的次数。

month_table表如下所示。

在这里插入图片描述

-- 删除已存在表（如不需要可删除该行）
DROP TABLE IF EXISTS `month_table`;

-- 创建表结构
CREATE TABLE `month_table` (
  `id` VARCHAR(10),
  `name` VARCHAR(20) NOT NULL,
  `month_num` INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 插入数据
INSERT INTO `month_table` (`id`, `name`, `month_num`) VALUES ('E002', '王小凤', 1);
INSERT INTO `month_table` (`id`, `name`, `month_num`) VALUES ('E001', '张文华', 2);
INSERT INTO `month_table` (`id`, `name`, `month_num`) VALUES ('E003', '孙皓然', 3);
INSERT INTO `month_table` (`id`, `name`, `month_num`) VALUES ('E001', '张文华', 4);
INSERT INTO `month_table` (`id`, `name`, `month_num`) VALUES ('E002', '王小凤', 5);
INSERT INTO `month_table` (`id`, `name`, `month_num`) VALUES ('E001', '张文华', 6);
INSERT INTO `month_table` (`id`, `name`, `month_num`) VALUES ('E004', '李智瑞', 7);
INSERT INTO `month_table` (`id`, `name`, `month_num`) VALUES ('E002', '王小凤', 8);
INSERT INTO `month_table` (`id`, `name`, `month_num`) VALUES ('E003', '孙皓然', 9);

我们要查询获得销售冠军的次数超过2次的人及其获得销售冠军的次数，首先需要获取每个人获得销售冠军的次数，对id列和name列进行group by；然后在组内对month_num列进行计数就可以得到每个人获得销售冠军的次数；接着利用having对分组聚合后的结果进行条件筛选；最后运行结果如下表所示。

select name, count(*)
from month_table
group by name 
having count(*) > 2;

+--------+----------+
| name   | count(*) |
+--------+----------+
| 王小凤 |        3 |
| 张文华 |        3 |
+--------+----------+

8、查询某部门一年的月销售额最高涨幅

现在有一张月销售额记录表sale_table，这张表记录了某部门2018年和2019年某几个月的销售额，现在我们想查询今年（2019年）的月销售额最高涨幅是多少。

sale_table表如下所示。

在这里插入图片描述

-- 删除已存在表（如不需要可删除该行）
DROP TABLE IF EXISTS `sale_table`;

-- 创建表结构
CREATE TABLE `sale_table` (
  `year_num` INT NOT NULL,
  `month_num` INT NOT NULL,
  `sales` INT NOT NULL,
  PRIMARY KEY (`year_num`, `month_num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 插入数据
INSERT INTO `sale_table` (`year_num`, `month_num`, `sales`) VALUES (2019, 1, 2854);
INSERT INTO `sale_table` (`year_num`, `month_num`, `sales`) VALUES (2019, 2, 4772);
INSERT INTO `sale_table` (`year_num`, `month_num`, `sales`) VALUES (2019, 3, 3542);
INSERT INTO `sale_table` (`year_num`, `month_num`, `sales`) VALUES (2019, 4, 1336);
INSERT INTO `sale_table` (`year_num`, `month_num`, `sales`) VALUES (2019, 5, 3544);
INSERT INTO `sale_table` (`year_num`, `month_num`, `sales`) VALUES (2018, 1, 2293);
INSERT INTO `sale_table` (`year_num`, `month_num`, `sales`) VALUES (2018, 2, 2559);
INSERT INTO `sale_table` (`year_num`, `month_num`, `sales`) VALUES (2018, 3, 2597);
INSERT INTO `sale_table` (`year_num`, `month_num`, `sales`) VALUES (2018, 4, 2363);

我们要查询2019年的月销售额最高涨幅，首先需要通过where把2019年的每月销售额筛选出来，然后在2019年的月销售额中寻找最大和最小的销售额，对两者做差并进行相应计算，就是我们想要的结果，最后运行结果如下表所示。

select
    max(sales) as max_sales,
		min(sales) as min_sales,
		max(sales)-min(sales) as cha,
		(max(sales)-min(sales))/min(sales) as growth
from sale_table
where year_num = 2019;

+-----------+-----------+------+--------+
| max_sales | min_sales |  cha | growth |
+-----------+-----------+------+--------+
|      4772 |      1336 | 3436 | 2.5719 |
+-----------+-----------+------+--------+

9、查询每个季度绩效得分大于70分的员工

现在有一张每季度员工绩效得分表score_info_table，这张表记录了每位员工每个季度的绩效得分，现在我们想要通过这张表查询每个季度绩效得分都大于70分的员工。

score_info_table表如下所示。
在这里插入图片描述

-- 删除已存在表（如不需要可删除该行）
DROP TABLE IF EXISTS `score_info_table`;

-- 创建表结构
CREATE TABLE `score_info_table` (
  `id` INT NOT NULL,
  `name` VARCHAR(20) NOT NULL,
  `subject` VARCHAR(20) NOT NULL,
  `score` INT NOT NULL,
  PRIMARY KEY (`id`, `subject`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 插入数据
INSERT INTO `score_info_table` (`id`, `name`, `subject`, `score`) VALUES (1, '王小凤', '第一季度', 88);
INSERT INTO `score_info_table` (`id`, `name`, `subject`, `score`) VALUES (1, '王小凤', '第二季度', 55);
INSERT INTO `score_info_table` (`id`, `name`, `subject`, `score`) VALUES (1, '王小凤', '第三季度', 72);
INSERT INTO `score_info_table` (`id`, `name`, `subject`, `score`) VALUES (3, '徐雨秋', '第一季度', 92);
INSERT INTO `score_info_table` (`id`, `name`, `subject`, `score`) VALUES (3, '徐雨秋', '第二季度', 77);
INSERT INTO `score_info_table` (`id`, `name`, `subject`, `score`) VALUES (3, '徐雨秋', '第三季度', 93);
INSERT INTO `score_info_table` (`id`, `name`, `subject`, `score`) VALUES (2, '张文华', '第一季度', 70);
INSERT INTO `score_info_table` (`id`, `name`, `subject`, `score`) VALUES (2, '张文华', '第二季度', 77);
INSERT INTO `score_info_table` (`id`, `name`, `subject`, `score`) VALUES (2, '张文华', '第三季度', 91);

我们要查询的是每个季度绩效得分都大于70分的员工，只要能够保证每个季度每位员工的最小绩效得分是大于70分的，就可以说明这位员工的每个季度绩效得分都大于70分。所以第一步需要查询每位员工的最小绩效得分，先对id列和name列进行group by分组，然后在组内求最小值，接着将最小绩效得分大于70分的员工通过having筛选出来即可，最后运行结果如下表所示。

select name
from score_info_table
where score > 70
group by name 
having count(*) = 3;

-- 或
select id, name, min(score)
from score_info_table
group by id, name 
having min(score) > 70;

+----+--------+------------+
| id | name   | min(score) |
+----+--------+------------+
|  3 | 徐雨秋 |         77 |
+----+--------+------------+

10、删除重复值

现在有一张员工信息表stu_info_table，这张表包含id、name（姓名）、t_1（一级部门）和t_2（二级部门）四个字段，现在我们想获取该公司一级部门及二级部门的信息，即哪些一级部门下包含哪些二级部门，该如何获取？

stu_info_table表如下所示。
在这里插入图片描述

-- 删除已存在表（如不需要可删除该行）
DROP TABLE IF EXISTS `stu_info_table`;

-- 创建表结构
CREATE TABLE `stu_info_table` (
  `id` INT PRIMARY KEY,
  `name` VARCHAR(20) NOT NULL,
  `t_1` VARCHAR(20) NOT NULL,
  `t_2` VARCHAR(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 插入数据
INSERT INTO `stu_info_table` (`id`, `name`, `t_1`, `t_2`) VALUES (1, '王小凤', '产品技术部', 'B端产品');
INSERT INTO `stu_info_table` (`id`, `name`, `t_1`, `t_2`) VALUES (2, '刘诗迪', '产品技术部', 'C端产品');
INSERT INTO `stu_info_table` (`id`, `name`, `t_1`, `t_2`) VALUES (3, '李思雨', '产品技术部', 'B端产品');
INSERT INTO `stu_info_table` (`id`, `name`, `t_1`, `t_2`) VALUES (5, '张青云', '销售运营部', '数据分析');
INSERT INTO `stu_info_table` (`id`, `name`, `t_1`, `t_2`) VALUES (4, '张文华', '销售运营部', '销售管理');
INSERT INTO `stu_info_table` (`id`, `name`, `t_1`, `t_2`) VALUES (6, '徐文杰', '销售运营部', '销售管理');
INSERT INTO `stu_info_table` (`id`, `name`, `t_1`, `t_2`) VALUES (7, '李智瑞', '产品技术部', 'B端产品');
INSERT INTO `stu_info_table` (`id`, `name`, `t_1`, `t_2`) VALUES (8, '徐雨秋', '销售运营部', '销售管理');
INSERT INTO `stu_info_table` (`id`, `name`, `t_1`, `t_2`) VALUES (9, '孙皓然', '产品技术部', 'B端产品');

stu_info_table表中的id列是主键，是不重复的，但是t_1列和t_2列是重复的，多个id列会属于同一个t_1列和t_2列。而我们只要t_1列和t_2列的信息，所以需要对这两列进行删除重复值操作，删除重复值的操作除了可以用distinct，还可以用group by，最后运行结果如下表所示。

select t_1,t_2
from stu_info_table
group by t_1, t_2
order by t_1;

+------------+----------+
| t_1        | t_2      |
+------------+----------+
| 产品技术部 | B端产品  |
| 产品技术部 | C端产品  |
| 销售运营部 | 数据分析 |
| 销售运营部 | 销售管理 |
+------------+----------+

11、行列互换

row_col_table表如下所示，这张表中每年每月的销量是一行数据。

在这里插入图片描述
我们需要把如上表所示的纵向存储数据的方式改成如下表所示的横向存储数据的方式。

在这里插入图片描述

-- 删除已存在表（如不需要可删除该行）
DROP TABLE IF EXISTS `row_col_table`;

-- 创建表结构
CREATE TABLE `row_col_table` (
  `year_num` INT NOT NULL,
  `month_num` INT NOT NULL,
  `sales` INT NOT NULL,
  PRIMARY KEY (`year_num`, `month_num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 插入数据
INSERT INTO `row_col_table` (`year_num`, `month_num`, `sales`) VALUES (2019, 1, 100);
INSERT INTO `row_col_table` (`year_num`, `month_num`, `sales`) VALUES (2019, 2, 200);
INSERT INTO `row_col_table` (`year_num`, `month_num`, `sales`) VALUES (2019, 3, 300);
INSERT INTO `row_col_table` (`year_num`, `month_num`, `sales`) VALUES (2019, 4, 400);
INSERT INTO `row_col_table` (`year_num`, `month_num`, `sales`) VALUES (2020, 1, 200);
INSERT INTO `row_col_table` (`year_num`, `month_num`, `sales`) VALUES (2020, 2, 400);
INSERT INTO `row_col_table` (`year_num`, `month_num`, `sales`) VALUES (2020, 3, 600);
INSERT INTO `row_col_table` (`year_num`, `month_num`, `sales`) VALUES (2020, 4, 800);

我们要把纵向数据表转换成横向数据表，首先要把多行的年数据转化为一年是一行，可以通过group by实现，group by一般需要与聚合函数一起使用，现在不需要对所有数据进行聚合，所以我们通过case when来对指定月份的数据进行聚合。

select
    year_num,
		sum(case when month_num = 1 then sales end) as m1,
		sum(case when month_num = 2 then sales end) as m2,
		sum(case when month_num = 3 then sales end) as m3,
		sum(case when month_num = 4 then sales end) as m4
from row_col_table
group by year_num;

+----------+-----+-----+-----+-----+
| year_num |  m1 |  m2 |  m3 |  m4 |
+----------+-----+-----+-----+-----+
|     2019 | 100 | 200 | 300 | 400 |
|     2020 | 200 | 400 | 600 | 800 |
+----------+-----+-----+-----+-----+

12、多列比较

现在col_table表中有col_1、col_2、col_3三列数据，我们需要根据这三列数据生成一列结果列，结果列的生成规则为：如果col_1列大于col_2列，则结果为col_1列的数据；如果col_2列大于col_3列，则结果为col_3列的数据，否则结果为col_2列的数据。

col_table表如下所示。

在这里插入图片描述

-- 删除已存在表（如不需要可删除该行）
DROP TABLE IF EXISTS `col_table`;

-- 创建表结构
CREATE TABLE `col_table` (
  `col_1` INT NOT NULL,
  `col_2` INT NOT NULL,
  `col_3` INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 插入数据
INSERT INTO `col_table` (`col_1`, `col_2`, `col_3`) VALUES (5, 10, 7);
INSERT INTO `col_table` (`col_1`, `col_2`, `col_3`) VALUES (1, 10, 6);
INSERT INTO `col_table` (`col_1`, `col_2`, `col_3`) VALUES (9, 3, 5);
INSERT INTO `col_table` (`col_1`, `col_2`, `col_3`) VALUES (5, 2, 9);
INSERT INTO `col_table` (`col_1`, `col_2`, `col_3`) VALUES (10, 4, 3);
INSERT INTO `col_table` (`col_1`, `col_2`, `col_3`) VALUES (5, 2, 9);
INSERT INTO `col_table` (`col_1`, `col_2`, `col_3`) VALUES (5, 8, 6);
INSERT INTO `col_table` (`col_1`, `col_2`, `col_3`) VALUES (8, 8, 6);

多列比较其实就是一个多重判断的过程，借助case when即可实现，先判断col_1 列和col_2列的关系，然后判断col_2列和col_3列的关系。这里需要注意的是，判断的执行顺序是先执行第一行case when，然后执行第二行casewhen，最后运行结果如下表所示。

select col_1, col_2, col_3,
	case when col_1 > col_2 then col_1
			 when col_2 > col_3 then col_3 
			 else col_2 end as col_4
from col_table;

+-------+-------+-------+-------+
| col_1 | col_2 | col_3 | col_4 |
+-------+-------+-------+-------+
|     5 |    10 |     7 |     7 |
|     1 |    10 |     6 |     6 |
|     9 |     3 |     5 |     9 |
|     5 |     2 |     9 |     5 |
|    10 |     4 |     3 |    10 |
|     5 |     2 |     9 |     5 |
|     5 |     8 |     6 |     6 |
|     8 |     8 |     6 |     6 |
+-------+-------+-------+-------+

13、对成绩进行分组

现在有一张某科目的学生成绩表subject_table，这张表包含id、score（成绩）两个字段，我们想知道60分以下（不包含60分）、60～80分（不包含80分）、80～100分三个成绩段内分别有多少个学生，该怎么实现呢？

subject_table表如下所示。

在这里插入图片描述

-- 删除已存在表（如不需要可删除该行）
DROP TABLE IF EXISTS `subject_table`;

-- 创建表结构
CREATE TABLE `subject_table` (
  `id` INT PRIMARY KEY,
  `score` INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 插入数据
INSERT INTO `subject_table` (`id`, `score`) VALUES (1, 56);
INSERT INTO `subject_table` (`id`, `score`) VALUES (2, 91);
INSERT INTO `subject_table` (`id`, `score`) VALUES (3, 67);
INSERT INTO `subject_table` (`id`, `score`) VALUES (4, 54);
INSERT INTO `subject_table` (`id`, `score`) VALUES (5, 56);
INSERT INTO `subject_table` (`id`, `score`) VALUES (6, 69);
INSERT INTO `subject_table` (`id`, `score`) VALUES (7, 61);
INSERT INTO `subject_table` (`id`, `score`) VALUES (8, 83);
INSERT INTO `subject_table` (`id`, `score`) VALUES (9, 99);

我们现在想知道每个成绩段内的学生数，需要做的第一件事就是对成绩进行分段，利用的是case when，完成成绩分段以后再对分段结果进行group by，接着在组内计数获得每个成绩段内的学生数，最后运行结果如下表所示。

select
	case when score < 60 then '60分以下'
			 when score >= 60 and score < 80 then '60~80分'
			 when score >= 80 and score <=100 then '80~100分' 
			 end as '成绩分段',
	 count(*) as '人数'
from subject_table
group by 1;

+----------+------+
| 成绩分段 | 人数 |
+----------+------+
| 60分以下 |    3 |
| 80~100分 |    3 |
| 60~80分  |    3 |
+----------+------+

14、周累计数据获取

现在有一张订单明细表order_table，这张表包含order_id（订单ID）、order_date（订单日期）两个字段，现在每天需要获取本周累计的订单数，本周累计是指本周一到获取数据当天，比如，今天是周三，那么本周累计就是周一到周三，该怎么实现呢？

order_table表如下所示。

在这里插入图片描述

-- 删除已存在表（如不需要可删除该行）
DROP TABLE IF EXISTS `order_table`;

-- 创建表结构
CREATE TABLE `order_table` (
  `order_id` INT PRIMARY KEY,
  `order_date` DATE NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 插入数据
INSERT INTO `order_table` (`order_id`, `order_date`) VALUES (1, '2019-01-08');
INSERT INTO `order_table` (`order_id`, `order_date`) VALUES (2, '2019-01-09');
INSERT INTO `order_table` (`order_id`, `order_date`) VALUES (3, '2019-01-10');
INSERT INTO `order_table` (`order_id`, `order_date`) VALUES (4, '2019-01-11');
INSERT INTO `order_table` (`order_id`, `order_date`) VALUES (5, '2020-01-08');
INSERT INTO `order_table` (`order_id`, `order_date`) VALUES (6, '2020-01-09');
INSERT INTO `order_table` (`order_id`, `order_date`) VALUES (7, '2020-01-10');
INSERT INTO `order_table` (`order_id`, `order_date`) VALUES (8, '2020-01-11');
INSERT INTO `order_table` (`order_id`, `order_date`) VALUES (9, '2020-01-12');

我们要获取本周累计的订单数，只需要把本周的订单明细筛选出来，然后对订单ID进行计数即可。如何把本周的订单明细筛选出来呢？让订单日期所属的周与程序运行当日所属的周是同一周，且所属的年是同一年。后面这个条件一定要注意，因为周数在不同年份是会重复的，但是在同一年内是不重复的。比如，2019年有一个52周，2020年也会有，但是不会在一年中出现两个52周。最后运行结果如下表所示。

select
	curdate(),
	count(order_id) as order_cnt
from order_table
where weekofyear(order_date) = weekofyear(curdate())
and year(order_date) = year(curdate());

在这里插入图片描述

15、周环比数据获取

我们现在需要根据上一节中的订单明细表order_table，获取当日的订单数和当日的环比订单数（即昨日的数据）。

当日的订单数比较好获取，主要需要思考的是当日的环比订单数如何获取，当订单日期等于当日日期向前偏移1日的日期时，对order_id进行计数就是昨日的订单数。这里需要注意的是，当if条件不满足时，结果应为null，而不能是其他值，因为count(null)=0，而count()函数的括号中若为其他内容则结果不一定是0，比如，count(0)的结果就不一定是0。最后运行结果如下表所示。

select
	count(order_id) as order_cnt,
	count(if(date_sub(curdate(),interval 1 day) = order_date,order_id, null)) last_order_cnt
from order_table;

在这里插入图片描述

16、查询获奖员工信息

现在有一张员工信息表table1，这张表包含id、name两个字段；还有另一张获奖名单表table2，这张表包含获奖员工的id和name两个字段。现在我们想通过table1表获取获奖员工的更多信息。

table1表如下所示。

在这里插入图片描述
table2表如下所示。

DROP TABLE IF EXISTS `table1`;

CREATE TABLE `table1` (
  `id` INT PRIMARY KEY,
  `name` VARCHAR(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `table1` (`id`, `name`) VALUES (1, '王小凤');
INSERT INTO `table1` (`id`, `name`) VALUES (2, '刘诗迪');
INSERT INTO `table1` (`id`, `name`) VALUES (3, '李思雨');
INSERT INTO `table1` (`id`, `name`) VALUES (4, '张文华');
INSERT INTO `table1` (`id`, `name`) VALUES (5, '张青云');
INSERT INTO `table1` (`id`, `name`) VALUES (6, '徐文杰');
INSERT INTO `table1` (`id`, `name`) VALUES (7, '李智瑞');
INSERT INTO `table1` (`id`, `name`) VALUES (8, '徐雨秋');
INSERT INTO `table1` (`id`, `name`) VALUES (9, '孙皓然');


DROP TABLE IF EXISTS `table2`;

CREATE TABLE `table2` (
  `id` INT PRIMARY KEY,
  `name` VARCHAR(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `table2` (`id`, `name`) VALUES (1, '王小凤');
INSERT INTO `table2` (`id`, `name`) VALUES (2, '刘诗迪');
INSERT INTO `table2` (`id`, `name`) VALUES (3, '李思雨');
INSERT INTO `table2` (`id`, `name`) VALUES (7, '李智瑞');
INSERT INTO `table2` (`id`, `name`) VALUES (8, '徐雨秋');
INSERT INTO `table2` (`id`, `name`) VALUES (9, '孙皓然');

已知table1表中存储了全部员工的id和姓名，我们用table1表去左连接table2表，如果该员工有获奖，就会在table2表中找到，反之则找不到。所以我们就可以利用table2 表的id列是否为null来判断该员工有没有获奖，进而把我们想要的信息通过where筛选出来，最后运行结果如下表所示。

select table1.*
from table1
left join table2
on table1.id = table2.id
where table2.id is not null;


+----+--------+
| id | name   |
+----+--------+
|  1 | 王小凤 |
|  2 | 刘诗迪 |
|  3 | 李思雨 |
|  7 | 李智瑞 |
|  8 | 徐雨秋 |
|  9 | 孙皓然 |
+----+--------+

17、计算用户留存情况

现在有一张用户登录表user_login，这张表记录了每个用户每次登录的时间，包含uid（用户ID）、login_time（登录时间）两个字段。我们想看用户的次日留存数、三日留存数、七日留存数（只要用户首次登录以后再登录就算留存下来了），该怎么实现呢？

user_login表如下所示。

在这里插入图片描述

-- 删除已存在表（如不需要可删除该行）
DROP TABLE IF EXISTS `user_login`;

-- 创建表结构
CREATE TABLE `user_login` (
  `uid` INT NOT NULL,
  `login_time` DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 插入数据
INSERT INTO `user_login` (`uid`, `login_time`) VALUES (1, '2019-01-01 06:00:00');
INSERT INTO `user_login` (`uid`, `login_time`) VALUES (1, '2019-01-02 10:00:00');
INSERT INTO `user_login` (`uid`, `login_time`) VALUES (1, '2019-01-04 19:00:00');
INSERT INTO `user_login` (`uid`, `login_time`) VALUES (2, '2019-01-02 10:00:00');
INSERT INTO `user_login` (`uid`, `login_time`) VALUES (2, '2019-01-03 09:00:00');
INSERT INTO `user_login` (`uid`, `login_time`) VALUES (2, '2019-01-09 14:00:00');
INSERT INTO `user_login` (`uid`, `login_time`) VALUES (3, '2019-01-03 08:00:00');
INSERT INTO `user_login` (`uid`, `login_time`) VALUES (3, '2019-01-04 10:00:00');

用户从首次登录到统计当日依然有登录就算该用户留存下来了，不同时长的留存表示不同时长以后仍会再次登录，比如，三日留存表示用户自首次登录以后第三天也会进行登录。我们现在要计算不同留存时长的用户数，首先需要计算不同用户的留存时长，将该用户的最后一次登录时间与首次登录时间做差就是该用户的留存时长，然后对留存时长进行分组聚合就得到了我们想要的不同留存时长的用户数，最后运行结果如下表所示。

select 
	case when tmp.v = 1 then '次日留存'
		   when tmp.v = 3 then '三日留存'
			 when tmp.v = 7 then '七日留存'
  end as '留存',
	count(*) as '人数'
from (
	select 
		uid, 
		datediff(max(login_time), min(login_time)) as v
	from user_login
	group by uid
) tmp 
group by 1;


+----------+------+
| 留存     | 人数 |
+----------+------+
| 三日留存 |    1 |
| 七日留存 |    1 |
| 次日留存 |    1 |
+----------+------+

18、筛选最受欢迎的课程

现在有一张学生科目表course_table，这张表存储了id、name（姓名）、grade（年级）和course（选修课程）四个字段，现在我们想知道最受欢迎的课程是哪一门。

course_table表如下所示。

在这里插入图片描述

-- 创建课程表
CREATE TABLE IF NOT EXISTS course_table (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    grade VARCHAR(20) NOT NULL,
    course VARCHAR(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 插入数据
INSERT INTO course_table (id, name, grade, course) VALUES
(1, '王小凤', '一年级', '心理学'),
(2, '刘诗迪', '二年级', '心理学'),
(3, '李思雨', '三年级', '社会学'),
(4, '张文华', '一年级', '心理学'),
(5, '张青云', '二年级', '心理学'),
(6, '徐文杰', '三年级', '计算机'),
(7, '李智瑞', '一年级', '心理学'),
(8, '徐雨秋', '二年级', '计算机'),
(9, '孙皓然', '三年级', '社会学'),
(10, '李春山', '一年级', '社会学');

我们要获取最受欢迎的课程，首先需要对课程进行分组，使用的是group by；然后对组内人数进行计数，即选择该课程的人数，使用的是count；接着对课程人数进行降序排列，使用的是order by；最后把排在第一名的课程筛选出来，就是最受欢迎的课程，运行结果如下表所示。

select course, count(*)
from course_table
group by course 
order by count(*) desc
limit 1;

+--------+----------+
| course | count(*) |
+--------+----------+
| 心理学 |        5 |
+--------+----------+

想一下上面这种思路是否有问题呢？当多门课程的选择人数一样多的时候，上面这种思路得出来的结果是否正确呢？显然是不正确的。当多门课程的选择人数一样多时，该如何处理？

如果存在选择人数一样多的课程，我们要把这些课程全部筛选出来。首先我们还是需要把每门课程以及选择的人数获取到，获取思路与第一种思路是一样的，也是针对课程进行分组，然后针对组内的人数进行计数，不同点在于最多人数的获取上。第一种思路默认选择人数最多的课程只有一门，而第二种思路假设选择人数最多的课程有多门时，也就是我们常说的并列第一或第二，我们就需要把选择人数最多的对应的最值算出来，这里利用子查询来生成，最后利用having对分组后的结果进行筛选，从而得到最受欢迎的课程。

select course, count(*) as cnt
from course_table
group by course 
having count(*) = (
	select max(cnt)
	from (
		select course, count(*) as cnt
		from course_table
		group by course 
	) a
);

+--------+-----+
| course | cnt |
+--------+-----+
| 心理学 |   5 |
+--------+-----+

19、筛选出每个年级最受欢迎的三门课程

本节使用的是18节的学生科目表course_table，现在我们想知道每个年级最受欢迎的三门课程，该怎么实现呢？

这是典型的获取组内排名的问题，上一节中获取的是报名人数最多的课程，只需要把每门课程的报名人数获取到，然后把最多的取出来就是我们想要的。但是现在这个问题不仅要获取最多的，还要获取第二名、第三名的。而且还是每个年级内的第一名、第二名、第三名。对于这个问题，我们可以利用窗口函数来实现，先生成每门课程的报名人数，然后利用row_number()函数生成每个年级内每门课程的排序结果，最后通过排序结果筛选出我们需要的数据，运行结果如下表所示。

select *
from (
	select grade, course, cnt,
		row_number() over(partition by grade order by cnt desc) as rk
	from (
		select grade, course, count(*) as cnt 
		from course_table 
		group by grade, course 
	) a
) b 
where b.rk <= 3;


+--------+--------+-----+----+
| grade  | course | cnt | rk |
+--------+--------+-----+----+
| 一年级 | 心理学 |   3 |  1 |
| 一年级 | 社会学 |   1 |  2 |
| 三年级 | 社会学 |   2 |  1 |
| 三年级 | 计算机 |   1 |  2 |
| 二年级 | 心理学 |   2 |  1 |
| 二年级 | 计算机 |   1 |  2 |
+--------+--------+-----+----+

当然，这里可以通过where筛选任意排名的课程。比如，如果要筛选排名第5～8的课程，只需要将where后面改为b.course_rank between 5 and 8即可。

20、求累积和

现在有一张2019年一整年的订单表consum_order_table，这张表包含order_id（订单ID）、uid（用户ID）和amount（订单金额）三个字段，现在我们想看下80%的订单金额最少是由多少用户贡献的，该怎么实现呢？

在这里插入图片描述

-- 创建消费订单表
CREATE TABLE IF NOT EXISTS consum_order_table (
    order_id INT COMMENT '订单ID',
    uid INT COMMENT '用户ID',
    amount DECIMAL(10,2) COMMENT '消费金额',
    month VARCHAR(6) COMMENT '月份，如201901'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='消费订单表';

-- 插入数据
INSERT INTO consum_order_table (order_id, uid, amount, month) VALUES
(201901, 1, 10.00, '201901'),
(201902, 2, 20.00, '201902'),
(201903, 3, 15.00, '201903'),
(201904, 3, 15.00, '201904'),
(201905, 4, 20.00, '201905'),
(201906, 4, 20.00, '201906'),
(201907, 5, 25.00, '201907'),
(201908, 5, 25.00, '201908'),
(201909, 6, 30.00, '201909'),
(201910, 6, 30.00, '201910'),
(201911, 7, 35.00, '201911'),
(201912, 7, 35.00, '201912');

因为现在只有一张订单明细表，所以我们需要先生成一张用户维度的订单金额表，然后在这张用户维度的订单金额表的基础上进行累积和运算，累积和可以通过窗口函数来实现，这样就可以得到用户维度的累积订单金额，在生成累积和的时候需要按照订单金额进行降序排列，这样就可以得到最少的人数，最后利用子查询获取到全部的订单金额，用累积订单金额除以全部订单金额，就可以得到累积的订单金额贡献情况，运行结果如下表所示。

select count(*)
from (
	select uid, amount,
		sum(amount) over (order by amount desc) as sum_amount,
		sum(amount) over (order by amount desc) / (
			select sum(amount)
			from consum_order_table
		) as n 
	from (
		select uid, sum(amount) as amount
		from consum_order_table
		group by uid
	) a
) t 
where t.n < 0.8;


+----------+
| count(*) |
+----------+
|        4 |
+----------+

21、获取新增用户数

现在有一张用户表user_reg_table，这张表包含uid（用户ID）、reg_time（注册时间）两个字段，我们想获取某一天的新增用户数，以及该天对应的过去7天内每天的平均新增用户数，该怎么实现呢？

user_reg_table表如下所示。

在这里插入图片描述

-- 创建用户注册表
CREATE TABLE IF NOT EXISTS user_reg_table (
    uid INT PRIMARY KEY COMMENT '用户ID',
    reg_time DATETIME COMMENT '注册时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户注册表';

-- 插入数据
INSERT INTO user_reg_table (uid, reg_time) VALUES
(1,  '2019-12-25 10:00:00'),
(2,  '2019-12-26 10:00:00'),
(3,  '2019-12-27 10:00:00'),
(4,  '2019-12-28 10:00:00'),
(5,  '2019-12-29 10:00:00'),
(6,  '2019-12-30 10:00:00'),
(7,  '2019-12-31 10:00:00'),
(8,  '2020-01-01 10:00:00'),
(9,  '2020-01-02 10:00:00'),
(10, '2020-01-03 10:00:00'),
(11, '2020-01-04 10:00:00');

我们想要获取某一天的新增用户数，这个某一天是一个可变的值，所以我们想到了变量，通过设置变量来满足日期的变化；其次我们还需要获取过去7天内每天的平均新增用户数，在变量的基础上减去6天即可，这里需要注意的是，between用来筛选介于过去7天和今天之间的用户数，而不能直接使用大于7天前的日期这个条件，因为大于7天前的日期很可能包括你设置的变量后面的日期，最后运行结果如下表所示。

select t.reg, t.cnt,
	count(*) over (
		order by t.reg 
		range between interval 7 day preceding and current row 
	) as reg_cnt_7d,
	count(*) over (
		order by t.reg 
		range between interval 7 day preceding and current row 
	) / 7 as avg_reg_cnt_7d
from (
	select date(reg_time) as reg, count(*) as cnt 
	from user_reg_table
	group by date(reg_time)
) t;


+------------+-----+------------+----------------+
| reg        | cnt | reg_cnt_7d | avg_reg_cnt_7d |
+------------+-----+------------+----------------+
| 2019-12-25 |   1 |          1 |         0.1429 |
| 2019-12-26 |   1 |          2 |         0.2857 |
| 2019-12-27 |   1 |          3 |         0.4286 |
| 2019-12-28 |   1 |          4 |         0.5714 |
| 2019-12-29 |   1 |          5 |         0.7143 |
| 2019-12-30 |   1 |          6 |         0.8571 |
| 2019-12-31 |   1 |          7 |         1.0000 |
| 2020-01-01 |   1 |          8 |         1.1429 |
| 2020-01-02 |   1 |          8 |         1.1429 |
| 2020-01-03 |   1 |          8 |         1.1429 |
| 2020-01-04 |   1 |          8 |         1.1429 |
+------------+-----+------------+----------------+

22、获取用户首次购买时间

现在有一张first_order_table表，这张表包含order_id（订单ID）、uid（用户ID）和order_time（订单时间）三个字段，我们想获取每个用户的首次购买时间，以及首次购买时间是否在最近7天内，该怎么实现呢？

first_order_table表如下所示。

在这里插入图片描述

-- 创建首次订单表
CREATE TABLE IF NOT EXISTS first_order_table (
    order_id INT PRIMARY KEY COMMENT '订单ID',
    uid INT NOT NULL COMMENT '用户ID',
    order_time DATETIME NOT NULL COMMENT '下单时间',
    INDEX idx_uid (uid),
    INDEX idx_order_time (order_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='首次订单表';

-- 插入数据
INSERT INTO first_order_table (order_id, uid, order_time) VALUES
(201901, 1, '2020-01-01 10:00:00'),
(201902, 2, '2020-01-02 10:00:00'),
(201903, 3, '2020-01-03 10:00:00'),
(201904, 1, '2020-01-04 10:00:00'),
(201905, 2, '2020-01-05 10:00:00'),
(201906, 3, '2020-01-06 10:00:00'),
(201907, 1, '2020-01-07 10:00:00'),
(201908, 2, '2020-01-08 10:00:00'),
(201909, 3, '2020-01-09 10:00:00'),
(201910, 1, '2020-01-10 10:00:00'),
(201911, 2, '2020-01-11 10:00:00');

这里需要解决两件事情：第一件事是获取每个用户的首次购买时间，其实就是最小时间；第二件事情是将最小时间和最近7天进行比较，得出首次购买时间是否在最近7天内。最后运行结果如下表所示。

select uid, min(order_time), 
	case when min(order_time) between curdate() and CURDATE() - INTERVAL 7 DAY
					then 'yes'
			 else 'no' end as '是否在最近七天内'
from first_order_table
group by uid;


+-----+---------------------+------------------+
| uid | min(order_time)     | 是否在最近七天内 |
+-----+---------------------+------------------+
|   1 | 2020-01-01 10:00:00 | no               |
|   2 | 2020-01-02 10:00:00 | no               |
|   3 | 2020-01-03 10:00:00 | no               |
+-----+---------------------+------------------+

23、同时获取用户和订单数据

本节使用的是21节的用户表user_reg_table和22节的first_order_table表，现在我们想获取过去7天每天的新增用户数、订单数、下单用户数，该怎么实现呢？

新增用户和订单数据存储在两张不同的表中，所以我们可以先分别获取过去7天每天的新增用户数、订单数和下单用户数，然后根据日期把两张表连接在一起，最后运行结果如下表所示。

set @day_date = "2020-01-04";
select
    t1.tdate
    ,t1.new_cnt
    ,t2.order_cnt
    ,t2.uid_cnt
from
    (
    select
        date(reg_time) tdate
        ,count(uid) new_cnt
    from
        user_reg_table
    where
        date(reg_time) between date_sub(@day_date,interval 6 day) and
@day_date
    group by
        date(reg_time)
    )t1
left join
    (
    select
        date(order_time) tdate
        ,count(order_id) order_cnt
        ,count(distinct uid) uid_cnt
    from
        first_order_table
    where
        date(order_time) between date_sub(@day_date,interval 6 day) and
@day_date
    group by
        date(order_time)
    )t2
on t1.tdate = t2.tdate


+------------+---------+-----------+---------+
| tdate      | new_cnt | order_cnt | uid_cnt |
+------------+---------+-----------+---------+
| 2019-12-29 |       1 |    <null> |  <null> |
| 2019-12-30 |       1 |    <null> |  <null> |
| 2019-12-31 |       1 |    <null> |  <null> |
| 2020-01-01 |       1 |         1 |       1 |
| 2020-01-02 |       1 |         1 |       1 |
| 2020-01-03 |       1 |         1 |       1 |
| 2020-01-04 |       1 |         1 |       1 |
+------------+---------+-----------+---------+

24、随机抽样

本节使用的是21节的用户表user_reg_table和22节的first_order_table表，现在我们想从用户表中随机抽取5个用户，并获取这5个用户的历史购买订单数，该怎么实现呢？

我们要随机抽取5个用户并获取他们的历史购买订单数，首先需要生成每个用户的历史购买订单数，然后从中随机抽取5个。具体的思路为利用rand()函数生成随机数，然后利用order by进行排序，最后利用limit将前5条数据显示出来，运行结果如下表所示。

select user_table.uid, t.order_cnt
from user_reg_table user_table
left join (
	select uid, count(order_id) as order_cnt
	from first_order_table
	group by uid
)t
on user_table.uid = t.uid
order by rand()
limit 5;


+-----+-----------+
| uid | order_cnt |
+-----+-----------+
|   3 |         3 |
|   1 |         4 |
|   6 |    <null> |
|   8 |    <null> |
|   9 |    <null> |
+-----+-----------+

25、获取沉默用户数

本节使用的是21节的用户表user_reg_table和22节的first_order_table表，现在我们想获取沉默用户的数量，沉默的定义是已注册但最近30天内没有购买记录的用户，该怎么实现呢？

我们要获取已注册但最近30天内没有购买记录的用户，可以先把最近30天内有购买记录的用户提取出来，然后用用户表user_reg_table中的uid去连接最近30天有购买记录的用户，如果不能连接到，即连接结果为null，就表示这部分用户最近30天内没有购买记录，把null的部分取出来，对uid进行计数即可。最后运行结果为14，因为我们用的是curdate()函数，所以在不同时间运行，得到的结果是不一样的。

select count(user_table.uid) chenmo_cnt
from user_reg_table user_table
left join (
	select uid
	from first_order_table
	where date(order_time) < date_sub(curdate(),interval 29 day)
	group by uid
)t
on user_table.uid = t.uid
where t.uid is null;


+------------+
| chenmo_cnt |
+------------+
|          8 |
+------------+

26、获取新用户的订单数

本节使用的是21节的用户表user_reg_table和22节的first_order_table表，现在我们想获取最近7天注册的新用户在最近7天内的订单数是多少，该怎么实现呢？

我们要获取最近7天注册的新用户在最近7天内的订单数，首先获取最近7天新注册的用户，生成新用户表，然后获取每个用户在最近7天内的订单数，生成订单表，最后将两张表进行连接，且以新用户表为主表，进行左连接。最后运行结果为14，在不同时间运行，得到的结果是不一样的。

select sum(t2.order_cnt)
from (
	select uid
	from user_reg_table
	where date(reg_time) < date_sub(curdate(),interval 6 day)
) t1
left join (
	select uid, count(order_id) order_cnt
	from first_order_table
	where date(order_time) < date_sub(curdate(),interval 6 day)
	group by uid
) t2
on t1.uid = t2.uid;

+-------------------+
| sum(t2.order_cnt) |
+-------------------+
|                11 |
+-------------------+

27、获取借款到期名单

现在有一张借款表loan_table，这张表包含id、loan_time（借款时间）、expire_time（到期时间）、reback_time（还款时间）、amount（金额）和status（还款状态，1表示已还款、0表示未还款）六个字段，我们想获取每天到期的借款笔数、借款金额和平均借款天数，该怎么实现呢？

loan_table表如下所示。
在这里插入图片描述


-- 创建借款表
CREATE TABLE IF NOT EXISTS loan_table (
    id INT PRIMARY KEY COMMENT '借款ID',
    loan_time DATE NOT NULL COMMENT '借款时间',
    expire_time DATE NOT NULL COMMENT '到期时间',
    reback_time DATE DEFAULT NULL COMMENT '还款时间，未还款则为空',
    amount DECIMAL(10,2) NOT NULL COMMENT '借款金额',
    status TINYINT NOT NULL DEFAULT 0 COMMENT '状态：0-未还款，1-已还款'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='借款表';

-- 插入数据
INSERT INTO loan_table (id, loan_time, expire_time, reback_time, amount, status) VALUES
(1,  '2019-12-01', '2019-12-31', NULL,        2208, 0),
(2,  '2019-12-01', '2019-12-31', '2019-12-31', 5283, 1),
(3,  '2019-12-05', '2020-01-04', NULL,         5397, 0),
(4,  '2019-12-05', '2020-01-04', NULL,         4506, 0),
(5,  '2019-12-10', '2020-01-09', NULL,         3244, 0),
(6,  '2019-12-10', '2020-01-09', '2020-01-12', 4541, 1),
(7,  '2020-01-01', '2020-01-31', '2020-01-10', 3580, 1),
(8,  '2020-01-01', '2020-01-31', NULL,         7045, 0),
(9,  '2020-01-05', '2020-02-04', NULL,         2067, 0),
(10, '2020-01-05', '2020-02-04', NULL,         7225, 0);

我们要获取每天到期的借款数据，首先筛选到期时间等于当天的数据，筛选出的结果就是当天到期的借款数据，然后对id列进行计数得到到期借款笔数，对amount列进行求和得到到期借款金额，对还款时间和借款时间做差取平均值得到平均借款天数，注意这里是对还款时间和借款时间做差，而非对到期时间和借款时间做差，因为有可能用户提前还款或逾期。最后运行结果为空，表示今天没有到期的借款。

select
    count(id) as loan_cnt,
		sum(amount) as loan_amount,
		avg(datediff(reback_time,loan_time)) avg_day
from loan_table
where expire_time = curdate();

+----------+-------------+---------+
| loan_cnt | loan_amount | avg_day |
+----------+-------------+---------+
|        0 |      <null> |  <null> |
+----------+-------------+---------+

28、获取即将到期的借款信息

本节使用的是27节的借款表loan_table，现在我们想知道有多少笔借款会在未来7天内到期，以及其中有多少笔是已经还款的，该怎么实现呢？

我们要获取未来7天内到期的借款笔数和其中已经还款的笔数，首先把最近7天内到期的数据筛选出来，然后通过还款状态（status）进行判断，接着获取已经还款的笔数，最后运行结果为空。

select count(id) as loan_cnt, count(if(status = 1,id,null)) as reback_cnt
from loan_table
where expire_time between curdate() and date_sub(curdate(),interval 6 day);

+----------+------------+
| loan_cnt | reback_cnt |
+----------+------------+
|        0 |          0 |
+----------+------------+

29、获取历史逾期借款信息

本节使用的是27节的借款表loan_table，现在我们想知道历史逾期的借款笔数和金额，以及至今还逾期的借款笔数和金额，该怎么实现呢？

这里面的关键信息在于，逾期怎么判断，对到期时间和还款时间进行比较，如果是逾期且现在已经还款的，可以直接比较到期时间和还款时间，如果还款时间大于到期时间，则说明是逾期的；还有一种是逾期且至今还未还款的，这种情况是没有还款时间的，也就是还款时间为空，但是到期时间是在今天之前，说明已经到期但是未还款。最后运行结果如下表所示。

select
    count(id) as loan_cnt,
		sum(amount) as loan_amount,
		count(if(status = 0,id,null)) as no_reback_cnt,
		sum(if(status = 0,amount,0)) as no_reback_amount
from loan_table
where (reback_time > expire_time)
or (reback_time is null and expire_time < curdate());


+----------+-------------+---------------+------------------+
| loan_cnt | loan_amount | no_reback_cnt | no_reback_amount |
+----------+-------------+---------------+------------------+
|        8 |    36233.00 |             7 |         31692.00 |
+----------+-------------+---------------+------------------+