SQL数据分析（简单版）

一、常见数据库分类

（1）关系型数据库

采用关系模型组织数据的数据库，以行和列的形式存储数据，形成数据表，一组数据表组成了数据库

（2）非关系型数据库

非关系型数据库在严格意义上不是一种数据库，应该是一种数据结构化存储方法的集合，可以是文档或者键值对等。

二、数据库常用功能

（1）表

数据表是数据库中存储数据的基本组成单位，例如用户信息表、订单表、采购表等。

（2）查询

查询是数据库中应用最多的对象之一，最常用的功能是根据指定条件从表中检索数据。

（查询实际上是一个固定化的筛选，它根据指定条件将表中的数据筛选出来，并以表的形式返回筛选结果）

三、常用SQL语句

1、select语句

SELECT 字段
FROM 表
WHERE 过滤行条件
GROUP BY 字段
HAVING 过滤组条件
ORDER BY 字段

为了提高速度、节省计算资源，如果只是查看数据表中的数据样例，了解数据结构，我们只需将前n条记录查询出来即可。如下：

SELECT 字段
FROM 表
LIMIT n

SELECT * FROM list LIMIT 3； // 查询list表前三条数据

2、create table 语句

用于创建新的数据表

CREATE TABLE 表名
（字段1 数据类型1，
... ...
 字段n 数据类型n）

MySQL数据库常用数据类型

数据类型	MySQL
字符型	VARCHAR、CHAR、TEXT
整数型	INT、SMALLINT、BIGINT
浮点型	FLOAT、DOUBLE
日期、时间型	TIME、DATE、DATETIME、TIMESTAMP

CREATE TABLE list
(ID INT,
 name VARCHAR,
 age INT,
 birthday DATE)

3、select into 语句

用于将查询结果保存至指定的新数据表中，如指定的数据表已存在，将删除数据表中的原有数据，再保存查询结果。

注意：MySQL数据库不支持select into语句，SQL server支持该语句

MySQL写法：

CREATE TABLE 新表 AS
SELECT 字段
FROM 数据源（表或查询）

例如：

CREATE TABLE list_2 AS  //保存至新数据库表“list_2”中
SELECT name FROM list;  //查询数据表“list”中所有的姓名

SQL server写法：

SELECT 字段
INTO 新表
FROM 数据源（表或查询）

例如：

SELET name INTO list_2 FROM list

4、insert into 语句

用于将查询结果追加插入指定的数据表，要求指定的数据表必须存在。

INSERT INTO 表
SELECT 字段
FROM 数据源（表或查询）

例如：

INSERT INTO list    //将数据插入数据表“list”中
SELECT name, age FROM list_new  //查询数据表list_new的数据

四、SQL语句注意事项

1、SQL语句中，“*”代表选择选定表格中所有字段，并且按照它们在数据库中的固定顺序来显示。数据表比较大时，尽量用具体的字段代表“*”，以免查询到过多用不到的字段，从而提高查询速度。

2、SQL语句中，SELECT、FROM等关键字使用大写或者小写形式均可。在编写SQL语句时，建议尽量统一关键字的大小写。例如，以大写字母的形式输入关键字，以小写的形式输入表名或字段，这样SQL语句看起来会更加清了。要求在编写SQL语句时，不能将这些关键字用作字段名、表名。

3、SQL语句中，关键字与表名、字段名、运算符之间需要使用空格分隔，如果SQL语句较长，可以使用换行符换行。

4、SQL语句中，字段或参数之间用逗号分隔。SQL语句结束时。在结尾处添加分号，以表示一条语句结束。（SELECT name, age FROM list;）

5、SQL语句中，如果参数为字符型，name需要在其两端加上单引号或者双引号，数值型参数无须使用引号。（SELECT * FROM list WHERE name='小黑' OR age<17）

6、对于SQL语句，如果表名、字段名中存在空格、"/"、"\"等特殊字符，在MYSQL数据库中使用单引号或反引号，将有特殊字符的表名或字段名引起来。（为了SQL语句能够顺利运行，尽量避免表名、字段名出现特殊字符。）

7、SQL语句中，如果函数参数或查询条件为日期和时间，在MYSQL数据库中需要在两端加上单引号或双引号。（SELECT * FROM 表 WHERE 日期='2022/10/1'）

8、SQL语句中符号需要在输入法的英文半角状态下输入的符号，否则SQL语句将无法运行。

9、编写SQL语句时，应尽量优化代码以提升查询速度。下面时SQL语句中各个子句的执行顺序。（顺序从上往下）

FROM 表
WHERE 过滤行条件
GROUP BY 字段
HAVING 过滤组条件
SELECT 字段
ORDER BY 字段

从SQL语句的执行顺序得知，可以先使用WHERE子句过滤出需要的数据，尽可能减小查询的数据规模，以提升查询速度。

其次，在WHERE子句中，条件表达式应尽量避免使用 "!="、"<>"或OR、LIKE等，以提升查询速度。

最后，应尽量避免在WHERE子句中对字段进行函数操作，以提升查询速度。

10、SQL语句中，关键字AS用于为表或字段取别名，特别是在多表查询中，关键字AS经常被使用到。编写SQL语句时，关键字AS可以省略，省略后需要将表名、字段名和别名用空格分隔开。

SELECT name AS a, age AS b
FROM list AS c;

SELECT name a, age b
FROM list c;

//上面两条SQL语句等价

PS：表的别名不能与该数据库的其他表同名。同理，字段的别名不能与该表的其他字段同名。

五、数据处理

数据处理，即根据数据分析的目的，对收集到的数据进行加工、整理，使数据保持准确性、一致性和有效性，以形成符合数据分析要求的样式，也就是数据清单。

数据处理是数据分析前必不可少的工作，并且在整个数据分析过程中占据了大部分的工作量。

常用的数据处理方法，主要包括数据导入/导出、数据清洗、数据转换、数据抽取、数据合并、数据计算等几大类。

5.1 数据导入/导出

在MYSQL可视化工具中可以之间拖拽SQL文件到查询空白处，点击执行即可导入数据库。找到菜单中的导出即可导出SQL文件。

5.2 数据清洗

在日常工作中，受系统BUG或人为等原因影响，难免会出现数据错误、数据缺失、数据重复等问题。具有此类问题的数据通常被称为“脏数据”。我们可以按照一定的规律将“脏数据”处理“干净”，这个操作被称为数据清洗。

数据清洗的目的就是将原始数据转化为可以进行分析的数据，使数据保持准确性、一致性、有效性。

进行数据清洗的常见方法有数据排序、重复数据处理、空格数据处理、缺失数据处理等。

1、数据排序

数据排序是指按照一定顺序对数据进行排列，以便研究者能够通过浏览数据发现一些明显的特征、规律，找到解决问题的线索。除此之外，排序还有助于对数据进行检查、纠错，以及为重新归类或分组等提供方便。

在SQL语句中，ORDER BY子句可用于根据指定的字段对数据进行排序，其用法如下所示。

ORDER BY 字段 ASC/DESC
ASC（或省略）：升序（从小到大）
DESC：降序（从大到小）

SELECT * FROM list
ORDER BY age ASC, birthdate DESC

2、重复数据处理

在日常的数据分析工作中，数据表中会存在各种各样的重复数据，在有些情况下，允许重复数据存在，而有时候需要将这些重复数据删除

重复数据删除，就是将数据中重复、多余的数据删除，以保证数据的唯一性，也称数据去重。

（1）关键字DISTINCT

在SQL语句中，第一个数据去重方法是使用关键字DISTINCT，它用于返回不同的唯一值，也就是将数据表中的重复数据删除。

SELECT DISTINCT 字段
FROM 表

DISTINCT需要放在所有字段的前面。DISTINCT其实是对后面所有字段的组合去重，也就是所有字段的值的组合如果不唯一，将删除多余重复值，只保留唯一值。

SELECT DISTINCT name, age, birthdate
FROM list_1

（2）GROUP BY子句

在SQL语句中，第二个数据去重方法是使用GROUP BY子句，它根据指定字段对数据进行分组，分组后保留该组的唯一值，也就实现了数据去重的效果。

SELECT 字段1,字段n
FROM 表
GROUP BY 字段1,字段n

GROUP BY子句后面的所有字段，要依次出现在SELECT的后面。

GROUP BY子句不能使用字段的别名，因为SELECT在GROUP BY之后执行，别名还未生效。

SELECT name, age, birthdate
FROM list_1
GROUP BY name, age, birthdate

3、空格数据处理

空格数据，是指字符型数据的前面或者后面存在的空格。由于系统BUG或人为等原因，空格数据在日常工作中经常出现。

（1）Trim函数

在SQL语句中，可以使用Trim函数删除字符型字段数据前后的空格。

需要注意的是Trim函数对字符串中间的空格不做处理，例如“Jack Wang”这个姓名中间的空格，是用来分隔姓氏跟名字的，属于正常的用法。

SELECT Trim(name) AS name_new, age, birthdate
FROM list_2

（2）Replace函数

在SQL语句中，还可以使用Replace函数替换掉字符型字段数据中的空格。

Replace函数（express, find, replace）

express	包含要替换的子字符串的字符串或字段
find	要查找的子字符串，需要使用双引号引起来
replace	替换子字符串，需要使用双引号引起来

Replace函数可以轻松解决Trim函数无法去除字符串中间空格的问题。根据实际需求选择相应函数进行空格处理即可。

SELECT Replace(name, " ", "") AS name_new, age, birthdate
FROM list_2

4、缺失数据处理

缺失数据也被称为缺省值，是指由于系统BUG或人为等原因，造成数据表中某些字段的值缺少的情况。

缺失值常见的表现形式有NULL和空值两种。空值可以使用英文单引号''或英文双引号""来表示。对于空值，可以使用运算符“=”，“!=”，“<>”判断；而对于NULL，直接使用运算符IS NULL、IS NOT NULL进行判断即可。

常用的缺失值处理方法有两种。

第一种是只在使用有缺失值的字段时，使用WHERE子句将字段中的缺失值过滤掉，而不是简单粗暴地将含有缺失值的数据记录都过滤掉或删除掉，那样会造成数据的浪费。

第二种是在有明确的填充规则时，使用某个值填充缺失值。例如，如果有出生日期信息，可以重新计算年龄并填充，如果没有出生日期信息或其他明确规则，则不建议填充任何值。

（1）If函数

在MYSQL数据库中，可以使用If函数判断字段中是否存在缺失值，并使用某个值填充缺失值。

例如：name为NULL时，填充为“无名氏”，新增一列列名为name_new

SELECT ID, name, age,birthdate,
       If(name IS NULL, '无名氏', name) AS name_new   
FROM list_3;

（2）Ifnull函数

在MYSQL数据库中，可以使用Ifnull函数判断字段值是否为NULL并使用某个值替换NULL。

Ifnull(expr1, expr2)

expr1	要判断的值或表达式，如果不为NULL，则返回expr1
expr2	expr1如果为NULL，则返回的值或表达式

SELECT ID, name, age, birthdate,
       Ifnull(name, '无名氏') AS name_new
FROM list_3

（3）Coalesce函数

在MYSQL数据库中，还可以使用Coalesce函数判断字段值是否为NULL并使用某个值替换NULL。

Coalesce函数用于返回参数列表中的第一个非NULL参数，如果所有参数都为NULL，则返回NULL。如果Coalesce函数输入参数的数量为2时，则作用于Ifnull函数相同。

SELECT ID, name, age, birthdate,
       Coalesce(name, '无名氏') AS name_new
FROM list_3

PS：在MYSQL数据库中，还可以使用CASE语句判断字段中是否存在缺失值并使用某个值填充缺失值。

5.3 数据抽取

数据抽取，也称数据拆分，是指抽取原数据库表中的某些字段、记录的部分信息，形成新字段、新纪录。常用的数据抽取方法包括字段拆分和记录抽取。

1、字段拆分

字段拆分，就是指抽取某一字段中的部分信息，形成新字段。

例如：身份证号码包含了很多信息，例如持证人所在地区、出生日期、性别等，将它们从身份证号码字段中抽取出来，可以得到新字段，这样就可以做相应的分析了，如用户地区分布、用户出生年份分布、用户性别构成等，甚至可以根据出生日期做进一步的处理，得到年龄、星座、生肖等新字段。

进行字段拆分的常用方法有按位置拆分、按分隔符拆分，另外还有一种时间属性抽取方法。

（1）按位置拆分

按位置拆分是进行字段拆分的常用方法，例如身份证号码这样具有明确编码规则的数据，就可以采用按位置拆分字段的方法获取新字段。

在SQL语句中，可以使用Left、Right、Mid三个文本类函数按位置拆分抽取字段，它们与Excel中的Left、Right、Mid函数使用方法一致。

Left函数用于从字符串左边第一个字符开始往右抽取指定个数的字符。Left(string, length)

Right函数用于从字符串右边第一个字符开始往左抽取指定个数的字符。Right(string, length)

Mid函数用于从字符串左边第n个字符开始往右抽取指定个数的字符。Mid(string, start, length)

在MYSQL中，字符型、数值型、日期型数据均可使用Left、Right、Mid函数抽取字段。在MYSQL中还可以使用Substr、Substring函数代替Mid函数，用法都是一样的。

例如：从身份证号码字段中抽取用户的出生日期信息。

SELECT uid, card_num, Mid(card_num, 7, 8) AS birthdate
From user

（2）按分隔符拆分

按分隔符拆分也是进行字段拆分的常用方法，是指按照指定的分隔符对已有的字符型字段进行拆分。

在MYSQL数据库中，可以使用Substring_index函数根据指定的分隔符拆分字段。

Substring_index(str, delim, count)

str	要拆分的字符串
delim	字符串拆分指定的分隔符
count	为正数时，取第n个分隔符之前的所有字符，为负数时，取倒数第n个分隔符之后的所有字符

例如：使用Substring_index函数将数据表的“name”字段中的姓名与年龄信息以减号作为分隔符进行拆分。

SELECT name,
       Substring_index(name, '-', 1) AS name_new,
       Substring_index(name, '-', -1) AS age
FROM list_4;

即name列中数据“张三-18”，被拆分为name_new：张三，age：18

（3）时间属性抽取

时间属性抽取，是指从日期、时间型数据中，抽取出需要的部分日期、时间属性数据，例如年、月、日、时、分、秒等。

在SQL语句中，可以使用Year、Month、Day、Hour、Minute、Second等日期、时间函数抽取属性数据。

SELECT uid, card_num, reg_date,
       Year(reg_date) AS year,
       Month(reg_date) AS month,
       Day(reg_date) AS day
FROM user;

其中reg_date格式为“2024/8/18”，即对于的year为2024，month为8，day为18

2、记录抽取

记录抽取，是指根据一定的条件抽取相应的数据记录，类似于Excel中的筛选功能。记录抽取式数据处理中非常常见的操作之一。

常用的记录抽取方式有指定值抽取、关键词抽取、数据范围抽取、时间范围抽取，以及组合条件抽取。

在SQL语句中，主要使用WHERE子句实现记录抽取。

SELECT 字段
FROM 表
WHERE 条件表达式

当运行SQL语句时，数据库会筛选出满足WHERE子句所列条件表达式的数据记录，如果没有指定WHERE子句，将返回表中所有的数据记录。

条件表达式是由运算符和操作数（变量或常量）组成的式子。

条件表达式常用运算符

算数运算符	+、-、*、/、%（取余）
关系运算符	=、>、<、>=、<=、!=、<>、IN、NOT IN、BETWEEN AND、NOT BETWEEN AND、LIKE、NOT LIKE、IS NULL、IS NOT NULL
逻辑运算符	AND、OR、NOT

2.1 指定值抽取

指定值抽取，是指将指定字段中包含指定值（字符串或数值）的数据记录抽取出来，指定的值可以是单个值或多个值。

（1）指定单个值抽取

例如：抽取出版社名称为“作家出版社”的商品数据记录。

SELECT * 
FROM item
WHERE press = '作家出版社'

（2）指定多个值抽取

例如：需要同时取出出版社名称为“作家出版社”“开明出版社”的商品数据记录。

SELECT * 
FROM item
WHERE press IN ('作家出版社', '开明出版社')

（3）缺失值抽取

将指定字段包含缺失值的数据记录抽取出来。

例如：需要抽取出版社字段为空值的商品数据记录。

SELECT *
FROM item
WHERE press = ''

SELECT *
FROM item
WHERE press IS NULL

2.2 关键词抽取

将指定字段中包含指定关键词的数据记录抽取出来，指定的关键词同样可以是单个或多个。

在SQL语句中，可以使用运算符LIKE根据指定关键词抽取数据记录，或者使用运算符NOT LIKE排查包含指定关键词的数据记录。

关键词可以是一个完整的字符串，也可以包含通配符。MYSQL中，通配符有“%”、下划线“_”。百分号表示零个、一个或多个任意字符，下划线表示任何单个字符。

例如：需要抽取出版社名称包含关键字“人民”的商品数据记录。

SELECT * 
FROM item
WHERE press LIKE '%人民%'

2.3 数据范围抽取

数据范围抽取，是指将指定的数值型字段值符号指定的数值范围条件数据记录抽取出来。

例如：需要抽取价格大于100元的商品数据记录。

SELECT *
FROM item
WHERE price>100

还可以使用BETWEEN AND或NOT BETWEEN AND运算符，用来判断是否位于两个数之间，或者说是否位于某个范围内。

例如：抽取价格大于或等于100元且小于或等于200元的商品数据记录。

SELECT *
FROM item
WHERE price BETWEEN 100 AND 200

2.4 日期范围抽取

日期范围抽取，是指将指定的日期型字段值符合指定的日期范围条件的数据记录抽取出来。

如：需要抽取上线日期大于或等于2018年1月21日且小于或等于2018年1月24日的商品数据记录。

SELECT *
FROM item
WHERE ptime BETWEEN '2018/1/21' AND '2018/1/24'

2.5 组合条件抽取

组合条件抽取是指通过逻辑运算，将指定值抽取、关键词抽取、数据范围抽取、日期范围抽取等多种抽取方式灵活组合起来抽取数据记录。常用的组合条件逻辑运算符有：AND（与）、OR（或）、NOT（非）。

PS：在没有“()”的情况下，逻辑运算的优先顺序为：非>与>或。在有括号的情况下，逻辑运算的优先顺序为：括号()>非>与>或。对于同一优先级的运算符，计算顺序为：从左往右。

例如，需要抽取上线日期大于或等于2018年1月10日、小于或等于2018年1月25日，且出版社名称不包含“文艺”、价格大于100元的商品数据记录。

SELECT * FROM item
WHERE ptime BETWEEN '2018/1/10' AND '2018/1/25'
AND press NOT LIKE '%文艺%'
AND price>100;

5.4 数据合并

数据合并是指将数据表中的某几个字段或不同数据记录的数据，组合成一个新字段、新数据记录数据。进行数据合并的常用操作有记录合并、字段合并、字段匹配。

1、记录合并

记录合并，也称纵向合并，是指将具有相同的数据字段、结构，但数据记录不同的数据表，合并成为一张新的数据表。

在SQL语句中，主要使用UNION ALL或UNION操作符进行两表或多表合并操作。

PS：

• UNION ALL就是将各数据表的记录不做任何更改，直接合并到一起。而UNION会删除各数据表中存在的重复记录，再进行合并操作。
• 进行UNION ALL或UNION操作的数据表，包含的字段必须拥有相同数量、顺序、数据类型。

例如：将商品表20180101、商品表20180102、商品表20180103中的数据合并为一张数据表item_20180101_20180103

CREATE TABLE item_20180101_20180103 AS
(SELECT * FROM item_20180101
UNION ALL
SELECT * FROM item_20180102
UNION ALL
SELECT * FROM item_20180103);

2、字段合并

字段合并，是指将同一个数据表中的某几个字段合并为一个新字段，即字段拆分的逆操作。

例如：从card_num（身份证号码）字段取出year、month字段，现在使用连接符“&”将“year”“month”两个字段合并成一个字符型的“year_month”字段，这个过程就是字段合并。

在MySQL数据库中，可以使用Concat函数将多个字段数据合并成一个新的字段数据。

Concat(str1, str2, ......)

str	要合并的字符串，参数可以是一个或多个

SELECT uid, card_num,
Concat(Mid(card_num,7,4), '-', Mid(card_num, 11, 2))  // 取身份证第7位往后4个数字为年份，身份证第11位往后2个数字为月份
AS year_month
FROM user;

3、字段匹配

字段匹配，也称横向合并，是将原数据表中没有但其他数据表（维表）中有的字段，通过共同的关键字段进行一一对应并匹配至原数据表中，从而达到获取新字段的目的。

PS：进行字段匹配的前提是需要匹配合并的两张表必须具有共同的关键字段，并且数据类型还需要一致。

在SQL语句中，需要使用连接的方法来实现字段匹配。

常用的连接方法主要有三种，分别为INNER JOIN（内连接）、LEFT JOIN（左连接）、RIGHT JOIN（右连接）

内连接	又称等值连接，只返回两个表中连接字段相等的数据记录。
左连接	返回表1中所有的数据记录以及表2中连接字段相等的数据记录。
右连接	返回表2中所有的数据记录以及表1中连接字段相等的数据记录。

// 内连接用法
SELECT * FROM 表1 AS a // 起别名，简化表名之用，AS可省略不写
INNER JOIN 表2 AS b
ON a.关键字段 = b.关键字段 // ON用于设置连接的连接条件

PS：左连接、右连接和内连接用法一致，只需要将INNER更改为LEFT或RIGHT即可。

连接使用注意事项

1	用于连接两表的共同关键字段的名称可以不同，但数据类型必须一致
2	用于连接两表的表名，建议都取个别名，方便两表字段名称书写引用
3	一般情况下，将表1（大表）作为主表，表2（小表）作为维表，维表中关键字段一般都是唯一、不重复的
4	连接时需要使用ON子句设置连接条件，否则会得到笛卡尔积结果表（结果表行数为两表行数的乘积）
5	在明确两表连接后包含需要的数据时，可以使用内连接，如果不确定，建议使用左连接，这样表1（主表）的数据就不会丢失

根据用于连接的共同的关键字段个数，可以分为单条件匹配、多条件匹配。

（1）单条件匹配

单条件匹配，就是在一个共同的关键字段相等的情况下进行连接匹配。

例如：将登录表“login”、用户表“user”两张数据表，通过共同的关键字段“uid”，使用内连接方式合并成一张表，这就是单条件匹配。

SELECT a.uid, a.date, a.type, b.card_num, b.reg_date
FROM login AS a
INNER JOIN user AS b
ON a.uid = b.uid;

（2）多条件匹配

多条件匹配，就是在多个共同的关键字段同时相等的情况下进行连接匹配。

在SQL语句中，只需要在ON子句中使用AND运算符将两个或多个字段相等的条件结合起来，即可实现多条件匹配。

例如，将登录表“login”、用户表“user”两张表，通过共同的关键字段“uid”相等，以及登录日期“date”与注册日期“reg_date”相等的情况，使用左连接方式合并成一张表。

SELECT a.uid, a.date, a.type, b.card_num, b.reg_date
FROM login AS a
LEFT JOIN user AS b
ON a.uid = b.uid AND a.date = b.reg_date;

5.5 数据转换

数据转换是指将数据从一种数据类型转换为另一种数据类型的操作。有时候，现有的数据类型并不是我们需要的数据类型，需要经过数据类型转换，才能进行下一步数据处理或计算。

PS：在进行字段匹配时，若两张数据表用于匹配的关键字段的数据类型不一致，是无法进行字段匹配操作的。这时需要将两张数据表用于匹配的关键字统一为一种数据类型。

在SQL语句中，常见数据转换操作有数值转字符、字符转数值、字符转日期、日期转字符。

1、数值转字符

MySQL数据库中，Cast函数可以用于将数据从一种数据类型转换为指定的另一种数据类型。

Cast(value AS datatype)

value	要转换的数据
datatype	要转换成的数据类型，常用的datatype参数： Date（日期型）、decimal（浮点型）、signed（整数型）、char（字符型）

Cast函数中的关键字AS用于分隔两个参数，在AS之前是要转换的数据，在AS之后是要转换成的数据类型。

在MySQL数据库中，将数据表“user”中的“uid”字段的数据类型从数值转换为字符型，并保存至新数据表“user_new”中。

CREATE TABLE user_new AS
SELECT uid, card_num, reg_date, Cast(uid AS char) AS uid_str
FROM user;

2、字符转数值

前提是字符型数据必须是纯数字字符，否则无法转换。

在MySQL数据库中，同样可以使用Cast函数将字符型数据转换为数值型数据。

例如，使用Cast函数将刚刚创建的数据表“user_new”中的“uid_str”字段的数据类型从字符型转换为数值型，因为用户ID为整数，所以datatype参数设为“signed”，即转换为整数型。

SELECT uid_str, Cast(uid AS signed) AS uid_num
FROM user;

3、字符转日期

就是将具有日期格式要素的字符型数据转换为日期型数据，不包含相应信息的数据是无法转换的。

例如，使用Mid函数从身份证号码字段“card_num”中抽取出用户的出生日期信息，如“19620709”这个字符串，具备了年、月、日这三个日期要素，如果需要获取年龄等相关信息，就需要先将这个字符串转换为数据库识别的日期型数据，再计算年龄。

而在MySQL数据库中无需单独抽取出年、月、日信息，可以直接抽取出8位出生日期数字，然后使用Date函数或Cast函数，将其转换为日期型数据。

Date函数用于提取日期或日期表达式的日期部分，函数中的date参数要求是合法的日期表达式。

Date(date)

date	合法的日期表达式，如20110101、2022-1-1、2011/1/1

在MySQL数据库中，从数据库表“user”的身份证号码字段“card_num”中抽取出用户的出生日期信息，并转换为日期型数据

SELECT uid, card_num,
       Date(Mid(card_num, 7, 8)) AS birthdate1,
       Cast(Substr(card_num, 7, 8) AS date) AS birthdate2
FROM user;

4、日期转字符

将日期型数据按照指定的格式转换为字符型数据，也称日期格式化

在MySQL数据库中，需要使用Date_format函数将日期型数据格式化为特定格式。

Date_formate(date, format)

date	要格式化的有效日期
format	由说明符组成的格式字符串，每个说明符前面都有一个百分比字符“%”，常用的说明符： %Y（年份）、%m（月份）、%d（日）、%H（时）、%i（分）、%s（秒）

在MySQL数据库中，将数据表“user”中的字段“reg_date”转换为“年-月”格式

SELECT uid, card_num, reg_date,
       Date_format(reg_date, '%Y-%m') AS year_month
FROM user;

5.6 数据计算

数据计算是指根据原有的字段数据，采用简单、函数等计算方式得到新的字段数据，以便进行下一步数据处理或数据分析工作。

1、简单计算

简单计算，是指通过对已有字段进行加、减、乘、除等运算，得到新字段的操作。

例如，商品表“item”的“price”字段中记录了价格数据，如果需要计算5折后的商品折扣价，就直接使用“price”字段乘以折扣比例50%，即可得到打折后的价格字段“price_dis”。

SELECT a.item_id, a.item_name, a.price, a.price*0.5 AS price_dis
FROM item AS a;

2、时间计算

时间计算主要包含时间间隔计算、时间偏移计算两种常用方法。

（1）时间间隔计算

时间间隔计算是指计算两个时间点之间的距离间隔数，间隔单位可以是年、月、日等。

在MySQL数据库中，可以使用TimestampDiff函数计算两个时间点之间的距离间隔数。

TimestampDiff(unit, datetime_expr1, datetime_expr2)

unit	时间间隔参数，year（年）、quarter（季）、month（月）、week（周）、day（日）、hour（时）、minute（分）、second（秒）
datetime_expr1	起始日期
datetime_expr2	结束日期

在MySQL数据库中，从数据表“user”的身份证号码字段“card_num”中抽取出用户的出生日期，使用TimestampDiff函数计算截止至2022年12月31日的用户年龄。

SELECT uid, card_num,
       TimestampDiff(year,
                    Date(Mid(card_num, 7, 8)),
                    '2022/12/31' AS age)
FROM user;

（2）时间偏移计算

时间偏移计算是指将指定时间往后推或者往前推一段时间，即获得加、减一段时间之后的时间值，时间偏移单位可以为年、季、月、周、天、时、分、秒等。

在MySQL数据库中，可以使用TimestampAdd函数进行时间偏移计算。

TimestampAdd(unit, interval, datetime_expr)

unit	时间偏移参数，year（年）、quarter（季）、month（月）、week（周）、day（日）、hour（时）、minute（分）、second（秒）
interval	需要计算的偏移量，正数表示向后偏移，负数表示向前偏移
datetime_expr	需要计算偏移量的日期

在MySQL数据库中，将“login”数据表中的登录日期“date”往前偏移一天。

SELECT a.*, TimestampAdd(day, -1, a.date) AS date_bf_1
FROM login AS a

 3、数据分组

数据分组，是指根据分析目的，将某个数值型字段划分为不同的区间（等距或非等距），生成一个分组字段，以便进行下一步数据处理或数据分析工作。

（1）lf函数

在MySQL数据库中，可以使用if函数对数值型数据进行区间分组。

例如，使用If函数对商品表“item”的商品价格字段“price”进行是否大于100元的分组操作。

SELECT a.item_id, a.item_name, a.price,
       If(a.price>100, '(100,+∞)'，'(0,100]') AS labels
FROM item AS a;

上面If函数第一个参数表示判断表达式，满足表达式显示则'(100,+∞)'，否则显示'(0,100]'。

如果需要在是否大于100元的分组基础上，增加一个是否小于或等于50的分组字段，那么可以使用函数嵌套的方式实现。使用函数嵌套时，只需将If函数中的第3个参数替换成另外一个If函数即可，更多层嵌套以此类推。

SELECT a.item_id, a.item_name, a.price,
If(a.price>100, '(100,+∞)', If(a.price>50, '(50,100]', '(0,50]'))
AS labels
FROM item AS a;

（2）Case语句

在MySQL数据库中，可以使用CASE语句对数值型数据进行区间分组。

CASE 
WHEN 条件表达式1 THEN 结果1
...
WHEN 条件表达式n THEN 结果n
ELSE 其他结果
END

CASE语句执行顺序是从上到下，区间临界值要么从小到大（进行小于判断时），要么从大到小（进行大于判断时），否则无法得到正确的结果。

在MySQL数据库中，使用CASE语句对商品表“item”的商品价格字段“price”进行以50元、100元为区间临界值的价格段分组操作。

SELECT a.item_id, a.item_name, a.price,
       CASE 
           WHEN a.price>100 THEN '(100,+∞)'
           WHEN a.price>50 THEN '(50,100]'
           ELSE '(0,50]'
       END AS labels
FROM item AS a;

六、数据分析

选择最合适的数据分析方法即可，只要能快速、有效地分析数据、解决问题，它就是好方法。

数据分析的三大作用，分别是现状分析、原因分析以及预测分析。这三大作用分别对应对比、细分、预测三大基本方法。

数据分析作用、方法对应表

数据分析作用	基本方法	数据分析方法
现状分析	对比	对比分析 描述性分析 分组分析 结构分析 分布分析 交叉分析 矩阵分析 RFM分析 综合评价分析 留存分析 生命周期价值分析 ......
原因分析	细分	结构分解法 因素分解法 转化分析 ......
预测分析	预测	相关分析 回归分析 时间序列 ......

有了这张表，我们要解决什么问题、达到什么目的，就可以使用对应的数据分析方法实现，清晰明了。

1、对比分析

对比分析，是指将两个或者两个以上的数据进行比较，分析它们的差异，从而揭示事物发展变化情况和规律性的方法。

通过对比分析，可以非常直观地看出事物在某方面的变化或差距，并且可以准确地量化显示这种变化或差距。

做数据分析，需要依据指标从不同的维度进行对比分析，才能得出有效的结论。

指标是指用于衡量事物发展程度的一种量化工具，例如人口数、GDP、收入、用户数、利润率、留存率、覆盖率等。很多公司都有自己的关键绩效指标（Key Performance Indicator，简称KPI）体系，可以通过一批关键指标来衡量业务运营情况的好坏。

指标可以分为绝对数指标和相对数指标。绝对数指标主要用来反映规模大小，也就是我们常说的数量、规模。相对数指标是指两个或两个以上有联系的统计指标值的比较，用来反映事物的发展程度、结构、强度等，也就是我们常说的质量。

所以，分析一个事物发展的程度可以从数量（Quantity）、质量（Quality）这两个方面的指标进行对比分析，这个方法被称为QQ模型分析法，简称QQ模型。

数量衡量事物发展的规模大小，例如：收入、用户数量、渠道数、GDP、人口数量等

质量：（1）广度，群体覆盖的范围，例如：留存率、渗透率、付费率等。

           （2）深度，群体参与的程度，例如：人均消费额、人均GDP、人均收入等

例如分析业务时，先分析业务是否达到一定规模，如规模够大，再分析质量高否，如不高，就可从提升质量角度入手，量变引起质变就是这个道理。GDP与人均GDP、用户数与留存率等组合分析，都是QQ模型的经典应用。

指标用于衡量事物发展的程度，这个程度是好还是坏，需要通过不同维度进行对比才能知道。

维度是事物或现象的某种特征，也是我们常说的分析角度，如产品类型、用户类型、地区、时间、性别、年龄、收入等都是维度。

时间是一种常用、特殊的维度，在时间维度上的对比通常被称为纵比。本月数据与上月数据对比，就是环比；本月数据与去年同期数据对比，就是同比；每个月份数据与某一固定月份数据对比，就是定基比。

通过不同时间点数据的对比，就可知在时间维度上事物的发展变化是好还是坏，例如本月新增用户数环比增长10%，同比增长20%。

另一种对比是横比，如不同国家人口数、GDP的对比，不同省份收入、用户数的对比，不同公司、不同部门之间的销售额对比，不同产品之间的销量对比，不同类型用户的收入之间的对比。

根据数据类型来划分，维度可以分为定性维度、定量维度。

定性维度（字符型数据）：事物的固有特征，如产品类型、用户类型、地区都是定性维度。

定量维度（数值型数据）：如收入、消费、年龄等，一般先进行数值分组，再进行对比等分析，使结果更直观。

只有通过事物发展的数量、质量两大方面指标，从横比、纵比角度进行全方位对比分析，才能全面了解事物发展的情况与规律。

最后强调，指标的口径范围、计算方法、计量单位必须一致，即要用同一种单位或标准去衡量。例如8美元与16元人民币就无法直接对比，需根据汇率换算后才可对比。

2、描述性分析

描述性分析，也称描述性统计分析，它是对数据进行分析，得出反映客观现象的各种数量特征的一种分析方法，主要包括数据的集中趋势分析、离散程度分析、频数分布分析等，常用的统计指标有计数、求和、平均值、最小值、最大值、标准差、方差等。

MySQL数据库提供计数、求和、平均值、最大值、最小值、标准差、方差相应的统计函数。

常用统计函数

Count()	计数
Sum()	求和
Avg()	平均值
Min()	最小值
Max()	最大值
StdDev()	标准差
Variance()	方差

例如，对商品表“item”中的商品价格字段“price”进行描述性分析。

SELECT Count(price) AS Count,
       Sum(price) AS Sum,
       Avg(price) AS avg,
       Min(price) AS min,
       Max(price) AS max,
       StdDev(price) AS stdev,
       Variance(price) AS var
FROM item;

3、分组分析

分组分析，是指根据分组字段将分析对象划分成不同的部分，以对比分析各组之间的差异性的一种分析方法。

分组目的，就是将总体中不同性质的对象分开，将相同性质的对象合并，保持各组内对象性质的一致性、组与组之间性质的差异性，以便进一步进行各组之间的对比分析。

根据分组字段的数据类型划分，分组类型只要包括两种：定性分组和定量分组。

定性分组：根据事物的固有属性划分的分组，如性别、学历、地区等属性。

定量分组：数值分组，将数值型数据进行等距或非等距分组，如年龄段、收入段

在SQL语句中，需要使用GROUP BY子句和Count（计数）、Sum（求和）、Avg（平均值）等统计函数对数据进行分组统计。

（1）简单分组统计

直接使用GROUP BY子句进行分组统计

例如，根据商品表“item”中的数据，统计出不同出版社商品的平均价格，可以对出版社字段“press”使用GROUP BY子句分组，对价格字段“price”使用Avg函数统计平均值。

SELECT press, Avg(price) AS price_avg
FROM item
GROUP BY press;

再次强调，GROUP BY子句后面的所有字段，要一次出现在SELECT的后面，然后紧跟需要的统计函数。

（2）分组排序统计

分组排序统计就是在使用GROUP BY子句进行分组统计的基础上，对分组统计结果进行升序或降序排序，使分组统计结果更加清晰、直观。

例如，根据商品表“item”中的数据，统计不同出版社的商品数，并按商品数降序排序，可以对出版社字段“press”使用GROUP BY子句分组，对商品ID字段“item_id”使用Count函数统计商品数，最后按商品数降序排序。

SELECT press, Count(item_id) AS item_cnt
FROM item
GROUP BY press
ORDER BY Count(item_id) DESC;

在此统计结果基础上，我们还可以只将商品数排在前5名的结果取出来。

在MySQL数据库中需要使用关键字LIMIT和ORDER BY子句组合实现。

LIMIT用法如下：

SELECT 字段
FROM 表
ORDER BY 字段 DESC
LIMIT n

取前n名数据时，通常需要先使用ORDER BY子句对数据进行降序排序，再取出前n名数据。

SELECT press, Count(item_id) AS item_cnt
FROM item
GROUP BY press
ORDER BY Count(item_id) DESC
LIMIT 5;

（3）分组过滤统计

分组过滤统计就是在使用GROUP BY子句进行分组统计的基础上，对分组统计结果根据指定条件进行过滤，仅呈现需要的分组统计结果。

例如，需要使用商品表item中的数据，获取出版社的商品数大于3的数据，可以使用HAVING子句实现。

HAVING子句用于过滤已分组的各组数据，它常与GROUP BY子句组合使用，其过滤条件通常是使用统计函数汇总的值与某个值进行比较的结果。

HAVING子句与WHERE子句都是用于执行条件过滤的语句，它们之间有相似之处也有不同之处，具体如下。

WHERE子句与HAVING子句的区别与联系

对比内容	WHERE子句	HAVING子句
过滤对象	根据数据表中的字段过滤行数据、记录	根据GROUP BY子句查询出的字段过滤各组数据
过滤条件	不可以使用统计函数	可以使用统计函数
执行顺序	在GROUP BY子句前执行（GROUP BY子句非必须）	在GROUP BY子句后执行
操作符	均可使用比较运算符、IN、BETWEEN、LIKE等操作符

使用HAVING子句获取出版社的商品数大于3的数据。

SELECT press, Count(item_id) AS item_cnt
FROM item
GROUP BY press
HAVING Count(item_id)>3
ORDER BY Count(item_id) DESC

（4）分组去重统计

分组去重统计就是先对数据进行指定条件（字段组合）的去重处理，然后使用GROUP BY子句进行分组统计。

日常工作中经常需要统计用户数，如日登录用户数、月登录用户数、日消费用户数、月消费用户数等，这时需要考虑用户去重的问题。

例如，根据登录表“login”中得数据，统计2018年每月登录用户数。登录表“login”是根据日、用户维度汇总处理过的数据表，在日维度上，登录用户是不存在重复情况的，而在月维度上存在一个用户在同一个月内有多天登录的情况，在统计月登录用户数时，该用户只能统计一次，也就是需要统计按月去重后的用户数。

PS：再次强调，GROUP BY子句不能使用字段的别名，因为SELECT 在GROUP BY之后执行，别名还未生效。

在MySQL数据库中，可以直接在Count函数里使用关键字DISTINCT对用户进行去重统计。

SELECT Date_format(date, '%Y-%m') AS year_month,
       Count(DISTINCT uid) AS Count
FROM login
WHERE date BETWEEN '2018/1/1' AND '2018/12/31'
GROUP BY Date_format(date, '%Y-%m')

统计用户数时，一定要注意数据表中的用户是否存在重复，如果存在，就需要进行用户去重统计。

要注意的是对用户去重不是简单的直接对用户进行去重处理，还要注意数据记录重复的条件（字段组合）。

4、结构分析

结构分析，是指在分组的基础上，计算各组成部分占总体的比重，进而分析总体的内部构成特征的一种分析方法。这个分组主要是指定性分组，一般需要关注结构，其重点在于各部分占整体的比重，例如性别结构、地区结构等。

对于结构分析，一般需要在对原始数据进行分组求和或计数之后，再求每个分组统计值占整体统计值的比重，从而得到结构分析的结果。

例如，根据商品表“item”中得数据，统计各出版社上架商品的比例。

计算思路：先计算各出版社的商品数 → 然后除总商品数，总商品数用子查询方式计算 → 最后按各出版社的商品数降序排序

SELECT press,
       Count(item_id) AS Count,
       Count(item_id) / (SELECT Count(item_id) FROM item) AS p
FROM item
GROUP BY press
ORDER BY Count(item_id) DESC;

5、分布分析

分布分析，是指根据分析目的将数值型数据进行等距或不等距分组，从而研究各组分布规律的一种分析方法。

分布分析法也是以分组为基础的，这个分组主要是指定量分组，而定量分组一般关注分布，例如用户消费分布、用户收入分布、用户年龄分布等。

分布分析重点查看数据的分布情况，其分布图的横坐标项是不能改变顺序的，也就是不能按各组统计值的大小排序，否则无法研究分布规律。

例如，根据商品表“item”中得数据，统计不同价格段上架商品数

SELECT
      CASE
          WHEN a.price>100 THEN '(100,+∞)'
          WHEN a.price>50 THEN '(50,100]'
          ELSE '(0,50]'
      END AS labels,
      Count(item_id) AS Count
FROM item AS a
GROUP BY
      CASE
          WHEN a.price>100 THEN '(100,+∞]'
          WHEN a.price>50 THEN '(50,100]'
          ELSE '(0,50]'
      END;

6、交叉分析

交叉分析，通过用于分析两个或两个以上分组变量之间的关系，以交叉表的形式进行变量间关系的对比分析。

交叉分析的原理，就是从数据的不同维度综合进行细分，以进一步了解数据的结构、分布特征。

交叉分析的分组变量，可以是定量分组VS定量分组，定量分组VS定性分组，定性分组VS定性分组两两组合，只要有助于发现并解决问题即可。

交叉分析的维度，建议不超过两个，这是因为交叉维度越多，统计结果就分得越细，这样就越不能突出重点，也就越难发现问题或规律。

例如，根据商品表“item”中的数据，统计各出版社、各价格段的上架商品数，可以将出版社字段“press”作为行，价格段作为列，计数函数Count作为统计函数，对商品ID字段计数。

SELECT press,
       Count(If(price>50, item_id, null)) AS 50元以上,
       Count(If(price<=50, item_id, mull)) AS 50元以内
FROM item
GROUP BYpress;

SELECT press,
       Count(CASE WHEN price>50 THEN item_id END) AS 50元以上,
       Count(CASE WHEN price<=50 THEN item_id END) AS 50元以内
FROM item
GROUP BY press;

以上两段代码，均可得到各出版社、各价格段的上架商品数

7、矩阵分析

矩阵分析，是指将事物的两个重要属性（指标）作为依据进行关联分析，找出解决问题思路的一种分析方法，也称矩阵关联分析。

以属性A为横轴，属性B为纵轴，组成一个坐标系，在两条坐标轴上分别根据某一标准（如平均值、经验值、行业水平等）进行划分，得到4个象限，并将要分析的每个对象根据横、纵坐标对应的指标值投射到相应象限上，可以直观地将两个属性的关联性表现出来，进而分析每个对象在这两个属性上的表现。矩阵分析也被称为象限图分析。

在解决问题和分配资源时，矩阵分析可以为决策者提供参考依据，先解决主要矛盾，再解决次要矛盾，有利于决策者进行资源优化配置。

需求1：通过消费、流量两个角度分析每个省份的用户质量。

通常使用平均值来代表该对象的质量水平，所以需要统计出各省份用户的平均月消费、平均月流量两个数据。

SELECT province, Avg(cost) AS cost_avg, Avg(flow) AS flow_avg
FROM telecom
GROUP BY province;

根据得出的表格，画出横坐标人均消费、纵坐标人均流量的象限图，可以找出双高与双低的省份。双高所在为第一象限，双低为第三象限。

需求2：通过消费、流量两个角度分析每个大区的用户质量。

如何统计出华东、华南、华北各大区用户的平均月消费、平均月流量两个数据呢？

现有数据为各省份用户的月消费、月流量数据，还需要各省份与各大区的对应关系，例如上海、江苏、浙江都对应华东区，所以需要整理一张各省份与大区对应的维表。

然后就可以将通信用户消费表telecom、地区表area两张数据表，通过共同的关键字段“province”，使用内连接或左连接方式合并成一张数据表，再跟进数据表area中的“area”字段分组统计平均月消费、平均月流量两个数据。

SELECT b.area, Avg(a.cost) AS cost_avg, Avg(a.flow) AS flow_avg
FROM telecom AS a
LEFT JOIN area AS b
ON a.province = b.province
GROUP BY b.area

根据以上得出来的结果，根据各大区用户的平均月消费、平均月流量两个数据使用散点图绘制矩阵图。若集中在矩阵图中心点周围，即用户质量与全国平均水平较为接近。

8、转化分析

转化分析，指对用户使用某项业务时经过一系列关键环节之后的转化效果进行分析的一种方法，经常通过漏斗图展现转化效果，所以也称漏斗图分析。

转化分析是针对业务流程诊断的一种分析方法，通过对某些关键环节转化率的分析，可以更快地发现业务流程中存在的问题。

下面通过某电视公司的一个数据分析案例学习如何使用SQL语句进行转化分析。

“事件表”中，第1列为用户ID“uid”，第2列为事件发生的日期“date”，第3列为商品ID“item_id”，第4列为事件类型“behavior_type”（“1”代表浏览商品，“2”代表提交订单，“3”代表完成交易，这3个事件是用户购买商品的关键环节）。

该表记录了每个用户每日浏览某个商品、提交订单、完成交易等新消息，都是不重复的记录。所以，接下来使用数据表统计完成每个事件（环节）的用户数时不需要考虑用户重复的问题。

SELECT behavior_type, Count(uid) AS uid_count
FROM behavior
GROUP BY behavior_type

如果需要计算各个环节相对第一个环节的转化率，将各个环节的完成用户数除以第一个环节的完成用户数即可，而第一个环节完成用户数需要使用子查询来统计。

SELECT behavior_type, Count(uid) AS uid_count,
       Count(uid) / (SELECT Count(uid) FROM behavior WHERE behavior_type = 1) AS rate
FROM behavior
GROUP BY behavior_type

9、RFM分析

RFM分析，指根据客户活跃程度和交易金额贡献情况进行客户价值细分的一种分析方法。RFM分析主要依据三个指标，分别为R（Recency，进度）、F（Frequency，频度），M（Monetary，额度）。

RFM指标

指标	说明	意义
R	客户最近一次交易距现在的时间间隔	R越大，表示客户未交易时间越久； R越小，表示客户最近交易时间越近
F	客户在最近一段时间内交易的次数	F越大，表示客户交易越频繁； F越小，表示客户不够活跃
M	客户在最近一段时间内交易的金额	M越大，表示客户价值越高； M越小，表示客户价值越低

例如，R、F、M三个分值都很高为高价值客户，都低为潜在客户，其他类型客户如下表所示。

RFM分值对应表

R分值	F分值	M分值	RFM分值	客户类型
高	高	高	222	高价值客户
高	高	低	221	一般价值客户
高	低	高	212	重点发展客户
高	低	低	211	一般发展客户
低	高	高	122	重点保持客户
低	高	低	121	一般保持客户
低	低	高	112	重点挽留客户
低	低	低	111	潜在客户

RFM分析计算过程大致如下所示。

Step1 计算R、F、M指标数据

Step2 计算R分值、F分值、M分值

Step3 计算RFM分值

Step4 根据RFM分值细分客户

下面通过数据表“behavior”中完成交易数据进行分析，如何使用SQL语句进行RFM分析。

Step1：计算R、F、M指标数据。

R（近度）的计算，需要先取出每个用户完成交易（behavior_type=3）的最大（最近）日期，再使用DateDiff函数计算其距统计时间点“2018-12-31”的天数。

F（频度）的计算，直接统计每个用户完成交易的订单数即可。

M（额度）的计算，直接统计每个用户完成交易的总金额即可，交易金额取自相关商品的价格信息。因为本例中每个事件涉及的商品数量均为1，商品价格就相当于交易金额，所以直接对每日、每个用户完成交易事件的商品价格求和，即得到完成的交易总金额。

最后将计算结果保持至新表rfm中。

CREATE TABLE rfm AS
SELECT a.uid,
       TimestampDiff(day, Max(a.date, '2018/12/31') AS r,
       Count(a.item_id) AS f,
       Sum(b.price) AS m
FROM behavior AS a
INNER JOIN item AS b
ON a.item_id = b.item_id
WHERE a.behavior_type = 3
GROUP BY a.uid

Step2：计算R分值、F分值、M分值。

因为R越小，R分值就越高，所以当R小于平均值时，就将R分值赋值为2，否则赋值为1。

F和M越大，F分值、M分值就越高，所以当F、M大于平均值时，就将F分值、M分值赋值为2，否则都赋值为1。

最后将计算结果保存至新数据表“rfm_s”中。

CREATE TABLE rfm_s AS
SELECT uid,
       If(r < (SELECT Avg(r) FROM rfm), 2, 1) AS r_s,
       If(f < (SELECT Avg(f) FROM rfm), 2, 1) AS f_s,
       If(m < (SELECT Avg(m) FROM rfm), 2, 1) AS m_s
FROM rfm;

上面得到新表rfm_s，数据表中含有每个用户的RFM分值。

Step3：计算RFM分值。

方法一：使用字段合并的方法将“r_s”，“f_s”，“m_s”字段数据合并成新字段“rfm_score”，需要注意此时字段“rfm_score”的数据类型为字符型。

方法二：使用下面的公式将“r_s”，“f_s”，“m_s”字段数据计算生成新字段“rfm_score”，此时字段“rfm_score”的数据类型为数值型。（公式：rfm = 100 x r_s + 10 x f_s + m_s）

本例采用字段合并的方法生成新字段“rfm_score”，然后将计算结果保存至新数据表“rfm_score”中。

CREATE TABLE rfm_score AS
SELECT uid, r_s, f_s, m_s, Count(r_s, f_s, m_s) AS rfm_score
FROM rfm_s

Step4：根据RFM分值细分客户。

计算各客户细分类型的用户数据，直接根据“rfm_score”字段分组统计uid数即可。

SELECT rfm_score, Count(uid) AS cnt
FROM rfm_score
GROUP BY rfm_score;

然后，可以参照RFM分值对应表，将各RFM分值对应至相应的客户类型，就可以得到客户类型的用户数量。

为了使结果更加清晰、直观，可以整理一张RFM分值与客户类型的维表“rfm_type”，将其与数据表“rfm_score”关联匹配后再统计各客户类型用户数据。

接下来，就可以将数据表“rfm_type”“rfm_score”通过共同字段“rfm_score”使用内连接或左连接方式合并成一张表，再根据数据表“rfm_type”中的“rfm_score” “type”字段分组统计用户数。

需要注意的是，数据表“rfm_score”中的“rfm_score”字段是使用字段合并的方法生成的，其数据类型为字符型，所以需要将数据表“rfm_score”中的“rfm_score”字段数据类型转换为数值型。本例直接使用乘以1的方法转换，然后才能使用关键字ON设置两张表的“rfm_score”字段相等条件。

SELECT b.rfm_score, b.type, Count(a.uid) AS cnt
FROM rfm_score AS a
INNER JOIN rfm_type AS b
ON a.rfm_score * 1 = b.rfm_score
GROUP BY b.rfm_score, b.type

后续可以对不同的客户群体有针对性地采取相应的运营策略，开展推广、管理工作，进而提升客户价值和营收水平。

10、留存分析

在互联网行业中，如果用户开始使用产品，经过一段时间后仍然继续使用该产品，则此部分用户被定义为留存客户，其数量占当时新增用户数量的比例即留存率。

留存率反映了用户因需求在产品内得到满足而主动留下来继续使用产品的一种状态。留存率越高，说明用户对产品的依赖度越高。

常用的留存率统计指标有：新增用户次日留存率、新增用户7日留存率、新增用户15日留存率、新增用户30日留存率。

常用留存率指标

新增用户次日留存率	某日新增用户中，在新增的第2天仍然登录的用户数/该日新增用户数
新增用户7日留存率	某日新增用户中，在新增的第7天仍然登录的用户数/该日新增用户数
新增用户15日留存率	某日新增用户中，在新增的第15天仍然登录的用户数/该日新增用户数
新增用户30日留存率	某日新增用户中，在新增的第30天仍然登录的用户数/该日新增用户数

（1）单日单个留存率指标统计

单日单个留存率指标的统计方法。例如，根据登录表login中的数据，统计2018-1-1新增用户的次日留存率。

首先，抽取2018-1-1新增用户数据作为表1，抽取出2018-1-2登录用户数据作为表2，通过共同的关键字段“uid”，使用左连接方式合并成一张数据表。

然后，统计2018-1-1新增用户与2018-1-2登录用户相同的用户数，也就是2018-1-1日新增且在次日留存的用户数，再除以2018-1-1新增用户数，得到2018-1-1日新增用户的次日留存率。

SELECT Count(a.uid) AS 新增用户数,
       Count(b.uid) / Count(a.uid) AS 次留
FROM
(SELECT uid FROM login WHERE date = '2018/1/1' AND type = 1)
AS a
LEFT JOIN
(SELECT uid FROM login WHERE date = '2018-1-2') AS b
ON a.uid=b.uid

如果需要统计2018-1-1新增用户的7日留存率，那就将2018-1-1新增用户数据与2018-1-7（2018-1-1起第7日）登录用户数据进行左连接匹配，再统计新增用户在第7日的留存率。对于新增用户的N日留存率，统计思路与此相同。

（2）单日多个留存率指标统计

例如，需要同时统计2018-1-1日新增用户的次日留存率、7日留存率，这时无须分别编写代码进行统计，直接使用两个左连接语句，即可将次日留存率、7日留存率在同一段代码中完成统计。

SELECT Count(a.uid) AS 新增用户数,
       Count(b.uid) / Count(a.uid) AS 次留,
       Count(c.uid) / Count(a.uid) AS 7留
FROM
(SELECT uid FROM login WHERE date = '2018/1/1' AND type = 1)
AS a
LEFT JOIN
(SELECT uid FROM login WHERE date = '2018/1/2') AS b
ON a.uid = b.uid
LEFT JOIN
(SELECT uid FROM login WHERE date = '2018/1/7') AS c
ON a.uid = c.uid

（3）多日多个留存率指标统计

例如，需要统计2018-1-1~2018-1-6每日新增用户的次日留存率、7日留存率。

Step1：将新增用户与登录用户连接，计算用户登录日与新增日的间隔天数。

将2018-1-1~2018-1-6新增用户数据与2018-1-1~2018-1-12（2018-1-6起第7日）登录用户数据进行左连接匹配，然后计算每个用户每个登录日与其新增日的间隔天数“day”。

CREATE TABLE ret AS
SELECT a.uid, a.new_date, b.ret_date,
TimestampDiff(day, a.new_date, b.ret_date) AS day
FROM
(SELECT uid, date AS new_date FROM login WHERE type=1
        AND date BETWEEN '2018/1/1' AND '2018/1/6') AS a
LEFT JOIN 
(SELECT uid, date AS ret_date FROM login
WHERE date BETWEEN '2018/1/1' AND '2018/1/12') AS b
ON a.uid=b.uid
ORDER BY a.uid, a.new_date, b.ret_date;

Step2：计算新增用户的次日留存率、7日留存率。

计算每日的新增用户在新增后第2、第7天登录留存的用户数（登录日与新增日的间隔天数为1、6的登录用户数），再除以对应的每日新增用户数（登录日与新增日的间隔天数为0的登录用户数），即可得到2018-1-1~2018-1-6每日新增用户的次日留存率、7日留存率。

SELECT new_date,
Count(If(day=0, uid, NULL)) AS 新增用户数,
Count(If(day=1, uid, NULL)) / Count((If(day=0, uid, NULL)))
AS 次留,
Count(If(day=6, uid, NULL)) / Count(If(day=0, uid, NULL))
AS 7留
FROM ret
GROUP BY new_date;

将Step1和Step2合并写：

SELECT c.new_date,
Count(If(c.day=0, c.uid, NULL)) AS 新增用户数,
Count(If(c.day=1, c.uid, NULL)) / Count(If(c.day=0, c.uid, NULL))
AS 次留,
Count(If(c.day=6, c.uid, NULL)) / Count(If(c.day=0, c.uid, NULL))
AS 7留
FROM
(SELECT a.uid, a.new_date, b.ret_date,
        TimestampDiff(day, a.new_date, b.ret_date) AS day
FROM
(SELECT uid, date AS new_date FROM login WHERE type=1)
        AND date BETWEEN '2018/1/1' AND '2018/1/6') AS a
LEFT JOIN
(SELECT uid, date AS ret_date FROM login
        WHERE date BETWEEN '2018/1/1' AND '2018/1/12') AS b
ON a.uid=b.uid) AS c
GROUP BY c.new_date;

11、生命周期价值分析

LTV（Life Time Value）就是用户生命周期价值，指从获取用户到用户流失期间，用户为产品带来的全部收益总和。生命周期价值分析是衡量用户对产品所产生的价值的一种方法。

要确保产品能够回本，就需要确保用户在整个生命周期中给产品带来的价值大于获取用户的成本。

常用的LTV统计指标有：新增用户LTV1、新增用户LTV7、新增用户LTV15、新增用户LTV30。

新增用户LTV1	某日新增用户中，在新增的第1天内累计消费金额/该日新增用户数
新增用户LTV7	某日新增用户中，在新增的第7天内累计消费金额/该日新增用户数
新增用户LTV15	某日新增用户中，在新增的第15天内累计消费金额/该日新增用户数
新增用户LTV30	某日新增用户中，在新增的第30天内累计消费金额/该日新增用户数

例如，需要统计2018/12/1~2018/12/6每日新增用户数的LTV1、LTV7，计算过程大致如下所示。

Step1、计算每日新增用户数

先从login表中取出2018-12-1~2018-12-6的新增用户数据，然后计算每日新增用户数，最后将计算结果保存至新数据表new_uid中。

CREATE TABLE new_uid AS
SELECT date, Count(uid) AS new_cnt
FROM login
WHERE date BETWEEN '2018/12/1' AND '2018/12/6'
      AND type=1
GROUP BY date;

Step2、计算每日每个用户的消费金额

先从数据表behavior中取出2018-12-1~2018-12-6（2018-12-6起第7天）每个用户完成交易（behavior_type=3）的数据，再将其与数据表item进行左连接匹配，获取商品的价格信息。因为本例中每个事件涉及的商品数量均为1，商品价格就相当于销售金额，所以每日每个用户的消费金额的计算方法就是直接对每日每个用户完成交易事件的商品价格求和。

CREATE TABLE uid_pay_day AS
SELECT a.date AS pay_date, a.uid, Sum(b.price) AS pay
FROM behavior AS a
INNER JOIN item AS b
ON a.item_id = b.item_id
WHERE date BETWEEN '2018/12/1' AND '2018/12/6' AND a.behavior_type = 3
GROUP BY a.date, a.uid

Step3、将新增用户数据与用户消费数据连接，计算用户消费日与新增日的间隔天数

将2018-12-1~2018-12-6新增用户数据与2018-12-1~2018-12-12（2018-12-6起第7日）用户消费数据进行左连接匹配，然后计算每个用户消费日与其新增日的间隔天数“day”。

CREATE TABLE new_uid_pay_day AS
SELECT a.uid, a.new_date, b.pay_date, b.pay,
       TimestampDiff(day, a.new_date, b.pay_date) AS day
FROM
(SELECT date AS new_date, uid FROM login WHERE type=1
        AND date BETWEEN '2018/12/1' AND '2018/12/6') AS a
LEFT JOIN uid_pay_day AS b
ON a.uid=b.uid
ORDER BY a.uid, a.new_date, b.pay_date

Step4、计算新增用户的LTV1、LTV7

先使用数据表new_uid_pay_day计算每日新增用户的登录第1天消费金额、登录第1天至第7天累计消费金额，再与数据表new_uid进行左连接匹配，计算每日新增用户的LTV1、LTV7。

SELECT a.date,
       a.new_cnt,
       b.total_pay1 / a.new_cnt AS lt1,
       b.total_pay7 / a.new_cnt AS lt7
FROM new_uid AS a
LEFT JOIN
       (SELECT new_date,
        Sum(If(day=1,pay,0)) AS total_pay1,
        Sum(If(day BETWEEN 0 AND 6,pay,0)) AS total_pay7
        FROM new_uid_pay_day
GROUP BY new_date) AS b
ON a.date = b.new_date