access 导入 txt sql语句_SQL初级数据分析（基于Microsoft Access）

一、数据库与SQL

1、数据库介绍

数据库（Database）是按照数据接口来组织、存储和管理数据的仓库。通过数据库中的各种对象，进行记录、处理、分析各种数据。

常用数据库：

• Oracle
• SQL Server
• MySQL
• Access

2、Access数据库

2.1、Microsoft Office办公软件重要组成部分，一种关系数据管理系统软件。

存储、处理、分析数据，数据处理功能更强大，数据量较大（大于几十万）时使用。

2.2、优缺点

• 优点：操作界面熟悉且友好，容易操作；查询处理可以直接生成相应的SQL语句；适合数据库入门
• 缺点：数据库过大（Access文件大于100M）性能会变差；每个数据库文件最大上限为2G

2.3、六个对象：

• 表：存储数据
• 查询：查找数据
• 窗体：获取数据
• 报表：获取数据
• 宏：自动化操作
• 模块：自动化操作

3、SQL简介

3.1、什么是SQL

SQL（Structured Query Language）结构化查询语言，一种通用的关系型数据库操作语言，用于存取数据、查询、更新和管理数据库。

SQL的本质是让数据库根据用户要求实现查询操作，数据查询是数据库的核心操作。

SQL是各类数据库语言的基础。

3.2、SQL常用语句

3.3、SQL语法

1）SELECT语句是SQL查询语言中的核心语言，能根据指定的条件规则从数据库中查询所要的数据。

2）基本语法：

SELECT 

条件是非必须的语句，但一般情况下都会进行具有某种条件的查询。

3）SQL语句示例：

SELECT 

4）SQL语句注意事项

• SQL语句中，英文字母大写或小写均可；
• 每个SQL语句的关键字用空格符号分隔，例：SELECT 字段 FROM 表；
• 字段或参数之间用英文逗号分隔，例：SELECT 姓名,性别,年龄,身高 FROM 会员表
• SQL语句中如参数为字符型，需要使用英文单引号，数值型则不使用，例：SELECT 姓名,性别,年龄,身高,月收入 FROM 会员表 WHERE 性别='男';
• SQL语句结束时，在语句结尾处添加英文分号（虽然在Access数据库中无强制要求，但为了养成良好编程习惯建议一定添加，避免出错）
• Access数据库SQL语句中，如表名、字段名中出现空格、"/"、""等特殊字符时，需要用方括号"[]"将还有特殊字符的表名或字段名括起来，以免得到不正确的结果或SQL语句无法运行；
• SQL语句中，"*"代表选定数据表中的所有字段，并且按照其在数据库中的固定顺序来显示，例：SELECT *
  FROM 表;
• Access数据库，在函数参数中或条件查询中，若参数或查询条件为日期和时间类型数据，需要在数据值两端加上井字符号（#），以表示数据类型为日期型；
• SQL语句中使用的逗号、分号、单引号、括号等符号均为英文符号；
• 应尽量避免在数据库中进行全表扫描：首先应考虑用WHERE子句筛选出需要的数据；其次在WHERE子句中，应尽量避免使用"!="或"<>"、"OR"等；最后应尽量避免在WHERE子句中对字段进行函数操作，否则将进行全表扫描；

二、数据处理

1、数据导入

创建Access数据库

• Excel数据导入
• 文本数据导入

2、创建查询

• 查询设计：点击右下角SQL按钮可以得到SQL语句
• SQL视图

数据表表名有特殊字符需要用方括号括起来，还可以用"as"重新命名，如：

3、数据抽取

3.1、字段拆分

截取某一字段的部分信息形成一个新字段，比如提取身份证号码的不同字段。

提取方式同Excel，使用函数LEFT、RIGHT、MID，公式如下：

• LEFT(字符串,提取的位数)
• RIGHT(字符串,提取的位数)
• MID(字符串,提取的起始位置,提取的位数)

以提取身份证号码为例：

SELECT 

3.2、记录抽取

根据指定的条件对数据表中的数据记录进行筛选，抽取出符合条件的数据记录。

使用筛选功能，在SQL中主要使用WHERE子句，举例见第一部分：数据库与SQL-3.3-3。

WHERE 条件表达式

以下是相关案例：

案例1

SELECT 

案例2

SELECT 

案例3

SELECT 

案例4

SELECT 

案例5

SELECT 

案例6

SELECT 

3.3、随机抽样

按照随机的原则，即保证总体中的每个单位都有同等机会被抽中，进行抽取样本的一种方法。

应用：调查用户行为异常；数据挖掘建模

函数RND进行随机抽样，该函数只有一个参数，参数必须是数值型数据；如果参数是字符型，可以用函数LEN计算字符型长度，转换为数值型数据。

例如，随机抽取用户明细表中20个用户

SELECT 

4、数据合并

综合不同的原数据表中某几个字段的信息或不同记录数据，组合成一个新字段或新记录数据，包括字段合并、字段匹配、记录匹配。

4.1、字段合并

将某几个字段合并成一个新字段，比如将之前身份证号码中提取的年、月、日合并成出生日期字段。

连接符：&、+

SELECT 

以上代码生成的字段是字符型，不是日期型；可以使用函数DateSerial直接生成日期型字段，与Excel中函数Day一致。

函数DateSerial，返回包含指定的年、月、日的日期；公式：DateSerial(year,month,day)

SELECT 

4.2、字段匹配

根据各表共有的关键字段把各表所需的记录一一对应，相当于Excel中的函数VLOOKUP的精确匹配功能。

数据库连接关系

• 内连接（INNER JOIN）：选择两个表中关键字段相匹配的记录
• 左连接（LEFT JOIN）：选择第一个表中的所有记录以及第二个表中关键字段相匹配的记录
• 右连接（RIGHT JOIN）：选择第二个表中的所有记录以及第一个表中关键字段相匹配的记录

在Access的数据库工具中创建关系，找到关键字段，选择需要的联接类型。

左连接与右连接的原理一致，左连接经常用来计算用户留存率、再购买率等。

4.3、记录合并

将具有共同的数据字段、结构，但记录信息不同的数据表合并到一个新的数据表。

在SQL中使用UNION或UNION ALL合并两表或多表。

*UNION会删除各表中的重复记录，并进行排序，使用时要慎重；UNION ALL则不做重复数据处理。

SELECT 

如果需要保存新合并的数据表，将合并查询结果插入一个新建相同字段、结构的空表中。

SELECT 

*上面WHERE条件1=2永远不成立，因此插入0条记录，就相当于新建了一个相同字段、结构的空表。

将合并查询的记录插入至刚才新建的空表中。

INSERT 

5、数据去重

保留唯一的数据记录，删除其他多余的重复记录。

在SQL中常用的两种数据去重方式为：GROUP BY子句和DISTINCT

5.1、GROUP BY子句

对数据按指定的分组字段进行分组，相当于EXCEL透视表中的行标签分组功能。分组字段可以有多个，即多条件去重，同时满足才去重，用英文逗号分隔。

SELECT 

5.2、DISTINCT

忽略所选字段中包含重复数据的记录，数据去重。同理，字段可以有多个，即多条件去重，同时满足才去重，用英文逗号分隔。

SELECT 

6、数据分组

重要的数据分析方法，根据数据分析对象特征，按照一定的指标，如业务、用户属性、时间等维度，把数据分析对象划分为不同的部分和类型进行研究，以揭示其内在的联系和规律性。

常用的数据分组方式包括数值分组和日期分组。

6.1、数值分组

函数IIF，与Excel中的函数IF用法一致；在Access数据库中，函数IIF最多进行13层嵌套。

IIF(条件表达式,表达式成立返回的值,表达式不成立返回的值)

SELECT 

6.2、日期分组

6.2.1、函数YEAR、MONTH、DAY

与Excel中的同名函数用法一致，分成年、月、日三个分组，前提是字段必须为日期型数据。

SELECT 

6.2.2、函数FORMAT

公式：FORMAT(日期/时间,日期/时间格式参数)

参数表如下

举例如下：

SELECT 

7、数据计算

通过对原有字段进行相应计算后得到新的字段，以满足数据分析需求，主要包括简单计算和函数计算。

7.1、简单计算

通过“加、减、乘、除”的方法得到新的字段

SELECT 

7.2、函数计算

通过内置的函数进行计算，比如求和、平均、最大值、最小值等。

以介绍函数DATEDIFF为例

公式：DATEDIFF("参数",起始日期,结束日期)

与Excel中的函数DATEDIFF一致，但用法略有不同，不同之处：日期间隔的参数在SQL中为公式中的第一个变量。

参数表如下

举例如下：

SELECT 

*在SQL中，如果函数参数或条件查询为日期或时间类型数据，需要在数据两端加上#符号以表示该数据字段为日期型。

三、数据分析

数据处理就是给数据分析做好准备工作，数据处理的目的将采集到的数据用适当的处理方法进行整理加工，形成适合数据分析的要求样式（即一维表）。

数据分析就是通过对比与细分进行现状分析和原因分析，可通过数据分组了解其数据构成，通过不同时间维度的对比查找数据变化的原因。

数据分析通常包含简单统计、分组统计、交叉表统计等常用方法。

3.1、简单统计

指计数、求和、平均等常用简单统计分析，用于反映描述事物的整体情况。

举例，SQL代码如下：

SELECT

3.2、分组分析

根据分析对象的某种特征，把分析对象划分为不同的部分以进行对比分析研究，揭示其内在的联系和规律性。

分组的目的是进行各组之间对比分析，研究数据分析对象的结构构成和分布特征。

分组类型主要有定量和定性两大类。定量分组就是数据分组（数值分组和日期分组）；定性分组就是按照事物的已有类别、属性划分，如性别、学历、地区等。

前面数据去重小节中的GROUP BY子句的作用就是对数据按指定的分组字段进行分组，相当于Excel透视表中的行标签。编写SQL语句时将GROUP BY放置在FROM语句之后，添加分组字段。

定性分组举例：

SELECT 

定量分组-数值分组举例：

SELECT 

定量分组-日期分组举例：

SELECT 

3.3、排序分析

根据分析对象，按数值大小（分组统计的基础上）进行升序、降序排列。排序的目的是更方便的进行对比分析，以便重点突出前几名和后几名。虽然简单基础，但非常实用，且容易被忽视。主要用于用户偏好分析，如销售排行榜、下载排行榜。

*注：如果数据没有数值分布顺序、时间顺序，建议按数值大小排序。

二八分析法就是排序分析的进一步扩展，以帕累托图的方式呈现。帕累托图是按照问题发生频率的高低顺序绘制的直方图，可用来分析质量问题，寻找影响质量问题的主要因素。

举例：

SELECT 

3.4、结构分析

结构分析法是在分组的基础上，计算各组成部分所占比重，进而分析总体的内部结构特征的分析方法。该方法应用广泛，比如市场占有率。分组主要为定性分组，定性分组一般看结构，重点在于占比。

举例：

SELECT 

其中SELECT COUNT(订单编号) FROM 订购明细为嵌套子查询，计算总销量数，用于分母。

以上代码得出的结果为小数而非百分比，如果让结果为百分比，需使用FORMAT函数转换，代码如下

SELECT 

3.5、分布分析

在定量分组的基础上查看数据的分布情况，其横坐标轴不能改变顺序，即不能按数值的大小排序，否则无法分析研究分布规律。应用广泛，用户消费分布、收入分布、年龄分布等。

下面对用户年龄进行分布分析的例子中还综合运用了SQL的分组、嵌套、计数、内连接（字段匹配）、去重等五个功能，代码如下：

SELECT 

因为订购明细表中存在大量同一用户ID重复购买的情况，所以将订购明细表重命名为A表之前对用户ID数据去重；将用户明细重命名为B表，并与A表通过字段用户ID建立内连接；最后根据用户年龄进行分组分析。

*注：涉及到用户、产品、渠道等相关统计时，要特别注意考虑数据重复的问题；掌握好SQL基础功能语句，灵活地组合运用。

3.6、交叉分析

用于两个或两个以上分组变量之间的关系，以交叉表形式对比分析变量间关系。交叉分析的原理是从数据的不同维度综合分组细分，以进一步了解数据的构成分布特征。

• 定量、定量分组交叉
• 定量、定性分组交叉
• 定性、定性分组交叉

建议最多两个维度，维度越多越没有重点，越难发现规律。在Access中，可先选择交叉表查询向导创建交叉表，再点击SQL视图查看SQL语句：

TRANSFORM 

其中TRANSFORM COUNT(用户ID) AS 用户数这行代码是增加交叉表每个行和列的交叉点统计函数及字段，GROUP BY 年龄设置行标签，PIVOT 性别设置列标签。

3.7、留存分析

以留存率衡量分析用户的质量，留存率可理解为留存下来的老用户与总用户访问量之间的比率。主要应用于网站分析、电商分析、游戏分析等，一般分为次日、3日、7日、14日、30日留存率，次日留存率最常用。与留存率类似的指标还有再付费率、再充值率等。

次日留存率的示意图如下

在Access中，首先以字段用户ID创建两个表的关系，在选择两个用户ID字段，运行后查询SQL语句，最后添加COUNT计数函数修改SQL，并进行留存率计算。

3.8、矩阵分析

根据事物的两个重要属性作为分析依据进行关联分析，找出解决问题办法的分析方法。

举例，根据年龄、消费两个维度，分析各省份购买用户质量。

首先，统计各省份购买用户平均订购金额

SELECT 

运行后得到的数据是为省份编码，导入省份编码表后（导入时数据类型选择短文本，需要与LEFT函数得到的省份编码数据类型一致），与另外两个表进行关联，修改SQL语句，运行后可以得到省份名称。

SELECT 

接下来，统计各省份购买用户平均年龄，注意考虑数据重复的问题

SELECT 

最后根据得到的各省份购买用户平均订购金额、各省份购买用户平均年龄两个数据表，使用Excel便可制作矩阵图。