谁说菜鸟不会数据分析（工具篇） 学习笔记 第一章数据处理

 

第一章   数据处理

1.数据分析三大作用：现状分析，原因分析，预测分析

大数据四大特点（4V）:数据量大（volume）数据类型多样（variety）

                                          数据处理实时性强（velocity）数据真实性（veracity）

数据库：按照数据结构来组织，存储，管理数据的仓库。

基本功能：

• 添加新数据记录
• 编辑现有数据
• 删除信息记录
• 以不同方式组织查看数据，如查询，处理，分析

常用数据库：

关系型数据库Oracle, Microsoft SQL server, MySQL, Microsoft Access

分布式数据库 基于NoSQL技术的Hbase, MongoDB, Redis等

2.Microsoft access

优势不足

优：易操作，查询处理可直接生成相应的SQL语句

缺：小型数据库，数据库过大（文件百兆以上），记录数过多（千万条以上）性能会变差；每个数据库文件上限2GB

SQL 结构化查询语言 structured query language

数据挖掘：通过对历史数据进行建模预测，生成一定的规则；将此规则写成SQL语句，并编写成数据库的存储过程，定期执行他们得到数据模型结果。

处理大数据的Hadoop ,使用Hive语言（HQL），与SQL基本一致

数据查询是数据库核心操作

SQL注意事项：

1. 英文大小写都行
2. 关键字空格分隔
3. 字段或参数之间逗号分隔
4. 字符型，单引号
5. 语句结束加分号
6. 特殊字符加[]
7. * 所有
8. 日期时间类型两端加#
9. 符号均为英文状态下
10. 避免全表扫描，先考虑用where筛选，where子句避免“<>““or“等，避免对字段进行函数操作（否则全表扫描）

导入数据

1. 直接导入法    与数据源脱离关系，数据更改不影响源文件

外部数据  文本文件 分隔符 让access添加主键（唯一ID自动填充） 保存导入步骤

1. 建立链接法   显示源文件中的数据，数据并不存在数据库中，源文件更改自动反映到链接表中，数据随数据源变化自动更新

数据处理主要包括数据合并，计算，分组，去重

数据合并:横向，纵向

横向 多个表中，根据共有，字段，各表记录一一对应，相当于excel 的VLOOKUP精确匹配（关联匹配查询）

1. 菜单操作法

步骤：关系；选中两表添加；关系管理器中，A表ID字段拖到B表ID字段处松开鼠标；编辑关系对话框，选三种联接中的一个；出现连接线；保存；简单查询向导，分别选择两个表需要的字段，得到联合查询的结果。

note：双击左侧对象栏查询对象，自动更新，数据量大时需谨慎。

查询设计比查询向导更快捷。

1. SQL 查询法

步骤：设计，SQL视图；输入语句

SELECT A.用户ID，A,产品，B.性别

FROM A INNER JOIN B ON A.[用户ID] = B.[用户ID];

或 SELECT 订购.用户ID，订购,产品，用户.性别

FROM 订购 （AS） A,用户 （AS） B

WHERE A.用户ID= B.用户ID;

NOTE:AS改表名，可以省略

表名.字段

字段名可用同样方式重命名

纵向  数据记录的合并

1. 菜单操作法

步骤：查询设计，设计视图中先添加一个表A；追加，选择目标表B；依次双击选择表A中所有字段,被选择的字段将在下面的查询设计网格中显示；运行，确定

Note：可以新建一张空白表把各个表追加进去

1. SQL 查询法

UNION ALL 和UNION合并，但所有查询的列数和列的顺序必须相同，数据类型必须兼容。

UNION 会删除重复记录，并且自动排序，尽可能使用UNION ALL

追加

INSERT INTO A(订单编号，订购日期，用户ID,产品，【单价（元）】，数量，金额)

SELECT 订单编号，订购日期，用户ID,产品，【单价（元）】，数量，金额

FROM B;

Step1 新建空白表，要求与表B结构，字段数据类型一致

SELECT * INTO A

FROM B

WHERE 1=2;

Step2 将四个表插入上述空白表

子查询语句，结果将生成一张数据表，将作为插入语句的源表，“嵌套查询“

INSERT INTO A

SELECT * FROM

（SELECT * FROM C

UNION ALL

SELECT * FROM D

UNION ALL

SELECT * FROM E

UNION ALL

SELECT * FROM F

）;

 

直接查询并将数据添加到新表

SELECT * INTO B

FROM

（SELECT * FROM C

UNION ALL

SELECT * FROM D

UNION ALL

SELECT * FROM E

UNION ALL

SELECT * FROM F

）;

索引与主键

索引：对指定列进行排序，好比目录，快速查询用

主键：确定数据中每一条记录的唯一标识，是一个特殊索引

具体区别：

1. 主键用于标识记录唯一性，不允许记录重复，键值不为空，主键是（特殊）索引，索引不一定是主键
2. 索引提高查询速度，不需全表扫描
3. 使用主键，数据库自动创建主索引，也可以在非主键上创建索引
4. 只允许有一个主键（可以是一列或多列（字段）），但可以有多个索引
5. 主键不是必须的，但最好有

数据库连接关系：

内连接 inner join

左连接 left join

右连接 right join

 

数据计算

1. 简单计算  加减乘除等

1. 菜单操作法

步骤：查询设计，添加表进设计视图；字段中输入订单金额：[单价（元）]*[数量]；运行

1. SQL查询法

Select 编号，日期，ID，产品，[单价（元）]，[数量] ，[单价（元）]*[数量] AS 金额 FROM 订购明细；

1. 函数计算  软件内置函数，求和平均值，最大最小值

DATEDIFF(‘参数’，起始日期，结束日期)

参数：

参数	yyyy	q	m	d	w	h	n	s
说明	年	季度	月	天	周	时	分	秒

Select 用户ID,注册日期，DATEDIFF(‘D’，注册日期，#2018-12-31#) AS 注册天数 FROM 用户明细；

常用函数：

函数类别	常用函数	说明
日期	Year（）	返回给定日期的年份
	Month()	返回给定日期的月份
	Day()	返回给定日期的天
	Date()	返回系统日期
	Datediff()	计算两个给定日期的间隔
	Datepart()	返回给定日期时间的指定部分 Datepart(‘参数‘，日期)
数学	Abs()	绝对值
	Int()	整数部分
	Round()	四舍五入到指定小数位数
字符	Len()	字符串长度
	Mid()	指定位置开始指定个数的字符
	Left()	左边开始指定个数字符
	Right()	右边开始指定个数字符

 

数据分组

数据分析三大基本方法：对比，细分（数据分组），预测

常用的分组方式：数值分组，日期/时间分组

1. 数值分组

方法	语法	例子	特点
IIF函数法	IFF（表达式，表达式成立返回值，表达式不成立返回值）	Select 用户ID,年龄 IFF（年龄<=20,’20岁及其以下’， IFF（年龄<=30,’21-30岁’， IFF（年龄<=40,’31-40岁’， ‘40岁以上‘）））AS 年龄分组 FROM 用户明细；	可进行不等距分组；易出错，最多13层嵌套
CHOOSE函数法	CHOOSE（参数，结果1，结果2,…结果N） 参数可为数值或表达式，参数返回1函数返回结果1，返回2函数返回结果2.。。返回值1-254之间的数字，否则返回#VALUE!；参数为小数截尾取整，相当于int（）	SELECT 用户ID,年龄， CHOOSE(((年龄)-1)/10+1,’10岁以下’,’11-20岁’......)AS 年龄分组 FROM 用户明细；	分组可达254个；只能等距分组
SWITCH函数法	SWITCH（条件1，结果1…，条件N，结果N） 条件1为TRUE,返回结果1，条件2为TRUE,返回结果2...,所有值都不为true返回null	SELECT 用户ID，年龄， Switch（年龄<=20,’20岁及以下’， ）AS 年龄分组 FROM 用户明细；	可不等距分组；条件表达式最多14个
PARTITION函数法	PARTITION（数值参数，开始值，结束值，组距） 数值参数为整数，开始值为整数不为0，结束值整数不小于或等于开始值，组距整数，函数返回每组的“上限：下限“	SELECT 用户ID，年龄， Partition（年龄，1，100,20）AS 年龄分组 FROM 用户明细；	分组不限，语句简单清晰；只能等距

 

1. 日期/时间分组

FORMAT（日期/时间, 日期/时间格式参数）  YEAR（）MONTH()  DAY()

SELECT 订单编号，订购日期，

FORMAT(订购日期，‘yyyy‘) AS 年，

FORMAT(订购日期，‘q‘) AS 季，

FROM 订购明细；

 

数据去重

1. 重复项查询

1. 菜单操作法

步骤：查询向导，查找重复项查询向导；选字段

1. SQL查询法

SELECT First(用户ID),Count(用户ID) AS NumberOfDups

FROM 订购明细

GROUP BY 用户ID

HAVING Count(用户ID)>1;

 

COUNT 计数  GROUP BY按指定分组字段分组  HAVING与GROUP BY联合使用（相当于where）

1. 不重复项查询

SELECT First(用户ID),Count(用户ID) AS NumberOfDups

FROM 订购明细

GROUP BY 用户ID

HAVING Count(用户ID)=1;

1. 数据去重查询

1. GROUP BY子句

SELECT用户ID

FROM 订购明细

GROUP BY 用户ID;

1. DISTINCT

数据分析一步到位

日常所说的数据分析，主要指的是通过对比和细分进行现状分析和原因分析，通过数据分组了解其数据构成，甚至通过不同时间不同维度的对比，查找数据变化的原因，最后制作图表对现状进行呈现及描述。

Access数据库数据分析方法： 简单统计，分组统计，交叉统计三种

1. 简单统计

Select

Count（订单编号） as 订单总数，

Sum(订单金额) as 订单金额总额，

Avg（订购金额）as 平均订单金额

From 订购明细；

Count（） sum()  avg() max() min()  StDev() 标准差  var()

1. 分组统计

Select 产品，

Count（订单编号） as 订单总数，

Sum(订单金额) as 订单金额总额，

Avg（订购金额）as 平均订单金额

From 订购明细

GROUP BY 产品；

 

不同年龄段用户订购

Select 年龄分组，Count(用户ID) as 用户数

From

（Select distinct A.用户ID ,PARTITION(B.年龄,1,100,5) as 年龄分组

From 订购明细A,用户明细B

WHERE A.用户ID = B.用户ID）

GROUP BY 年龄分组；

 

1. 交叉统计

1. 菜单操作法

查询向导，交叉表查询向导；选择表；选行标题，列标题，交叉点统计项（是，包括各行小计）

1. SQL

Transform Count（用户ID） AS 用户ID计数

SELECT 省份，Count（用户ID）AS 总计用户ID

FROM 用户明细

GROUP BY 省份

Pivot 性别；

Transform每个行和列的交叉点统计函数及字段  Pivot要作为列标题的分组字段

Microsoft query    略

是用于将数据从外部数据源检索到其他Microsoft office（尤其是excel）应用中的一种程序

 

 

本文为谁说菜鸟不会数据分析（工具篇）  个人 学习笔记 ，侵删