第1-2篇¶

日期：2022.08.27
链接：http://www.yufan.site/study/cda/cda1-2.html
内容：数据分析

要点说明¶

数据结构（占比15%）
a. 表格结构数据特征（占比2%）
b. 表格结构数据获取、引用、查询与计算（占比3%）
c. 表结构数据特征（占比5%）
d. 表结构数据获取、加工与使用（占比5%）

1、表格结构数据特征
【领会】
表格结构数据概念
表格结构数据处理工具
【熟知】
表格结构数据特征

2、表格结构数据获取、引用、查询与计算
【领会】
表格结构数据获取方法
【熟知】
单元格区域的特征
【应用】
表格结构数据的引用方法
表格结构数据的查询方法
表格结构数据的常用函数

3、表结构数据特征
【熟知】
理解主键的意义
理解维度及度量的意义
理解缺失值
表结构数据特征
表结构数据与表格结构数据差异

4、表结构数据获取、加工与使用
【领会】
表结构数据获取渠道及方法
【熟知】
表结构数据连接逻辑

表结构数据汇总逻辑
ETL 作用、特征
【应用】
应用E-R 图
计算两表连接汇总值

表格结构数据¶

使用电子表格工具，如Excel、WPS、Numbers等等，来进行数据的处理，加工及分析的，以单元格作为数据基本的加工处理分析单位的数据结构就叫做表格结构数据。

表格结构数据特征¶

表格结构数据层级¶

单元格，单元格的集合->单元格区域，单元格区域的集合->工作表，工作表的集合->工作簿，一个工作簿是一个独立的电子表格文件。
对象间的父子级关系：
父级->子级：一个父级对象下包含多个不同子集对象
子级->父级：一个子级对象只能属于某一个特定的父级对象
例子：省份与城市、月份与日期

表格结构数据类型¶

数值型：数值型中包含整数值与小数值的信息，主要用来进行数学计算。
文本型：文本值中包含文字、符号、数字等信息，主要用来对文本内容进行描述时使用。
逻辑型：逻辑值中只包含真（true）与假值（false）两种信息，主要用来进行逻辑判断。

表格结构数据获取方法¶

从企业后台数据库系统获取，从前端操作平台获取数据，从企业外部渠道获取数据。

表格结构数据使用方法¶

单元格值的引用方法¶

引用同一工作表内单元格值：通过“=列号+行号”定位单元格,例：=A1
引用不同工作表内单元格值：通过“=表名！+列号+行号”定位单元格,例：=Sheet2!A1

单元格区域值的引用方法¶

单元格区域：   a.单元格区域需要由连续的单元格构成。
  b.单元格区域需要是一个方形区域。

引用相同工作表内的单元格区域：“=左上单元格：右下单元格”，例：求和 =SUM(A1:C2)
引用不同工作表内的单元格区域：“=表名！左上单元格：右下单元格”,例：求和 =SUM(Sheet2!A1:C3)
引用多行：“=上边行号：下边行号”，例：求和 =SUM(1:2)
引用多列：“=左边列号：右边列号”，例：求和 =SUM(A:B)

表格结构数据查询方法¶

查询方法：
  a.使用表格工具搜索功能进行查询。
  b.使用查询函数进行查询。

用查询函数进行查找¶

举例拆解VLOOKUP函数的各个参数：
D2：查询条件，以“D2”单元格值作为查询条件。
A:B：查找范围，在“A:B”的单元格区域内进行查询，查询范围中的第一列“A”列为查询匹配列，在“A”列中查找与“D2”查询条件值相同的第一个单元格值。
2：查找范围内的顺序号，在“A:B”两列中取第二列上的单元格值。
FALSE：查找方式，“FALSE”代表精确匹配，只在查询范围内匹配与条件值“D2”单元格值“李四”完全相同的单元格值。
image

表结构数据¶

使用诸如数据库查询语言、ETL工具、可视化工具等等，来进行处理分析加工的，以字段或记录作为最基本的处理分析加工单位的数据结构。

字段：整列数
记录：整行数
维度：业务角度
度量：业务行为结果
维度字段：文本型
度量字段：数值型

维度表：只包含维度信息的表
事实表：既包含维度信息又包含度量信息的表

表结构数据特征¶

  1. 以字段或记录作为数据的引用、操作及计算的基本单位的数据。
第一行为标题行，第二行以后称为记录，字段名不能重名，一个字段只能有一种数据类型。
  2. 所有字段记录行数相同
方形结构，记录行数相同，存在空值，处理缺失值。
  3. 一个表中有且只有一个主键。

物理意义：
单字段主键：由一个字段构成的主键。
多字段联合主键：由多个字段构成的主键。
非空不重复。
定位记录行、字段名+主键值定位具体数值。
多以“xxID”，“xxNo”，“xx编号”等名称命名。
业务意义：
表的业务记录单位。在一个数据表中的所有非主键字段都要围绕主键展开。

确定主键的方法¶

如果直接对数据库中的数据表进行操作，可以通过SQL语句确认数据表的主键字段。
如果间接在其他数据分析平台使用表结构数据（数据源是数据库中的数据表，使用时将数据源数据导入到其他平台使用），可以找原数据库中的数据表的设计者咨询主键信息，或是直接查看数据表设计者留下的设计资料来对主键字段进行确认。
通过对数据表的业务意义进行分析，推测主键字段后再用物理手段确认推测的主键字段中的记录值是否能够满足“非空”、“不重复”的要求来对主键字段进行确认。

处理缺失值¶

根据数据类型以及生成信息重要程度的不同，使用不同方法处理缺失值。

文本型字段¶

影响不大：可以选择不进行处理，或者也可以用其他没有实际业务含义的文本字符对缺失值进行替换。
影响大：向业务人员进行确认后替换，或者与业务人员核实后删除。

数值型字段¶

综合考虑该数值型字段所代表的度量意义，以及针对该数值型字段进行汇总计算的方式，来最终决定对缺失值的具体处理方法。

表结构数据获取方法¶

“应用”表格结构数据
数据源数据->[将数据源数据导出到数据文件]->电子表格支持的数据文件（*.xlsx\*.csv\*.txt）->[应用电子表格工具打开数据文件]->在电子表格工具上使用表格数据结构。
“引用”表结构数据
数据源数据<-用表结构数据分析工具创建与数据源数据间的连接关系<-在表结构数据分析工具上使用表结构数据

关系型数据库管理系统¶

RDBMS：Relational Database Management System 关系型数据库
关系型数据库管理系统的主要任务是企业业务数据的存储、检索、访问与共享。
特征：多层级结构、OLTP（On-Line Transaction Processing 联机事务处理过程）、可量化结构化数据、提供大部分数据源、不善于分析

商业智能系统¶

BI：Business Intelligence 商业智能
用于为企业决策者快速提供完整、准确、深入的数据分析结果，帮助企业决策者实现商业洞察
特征：强于分析、多功能模块构成、两种主要类型（企业级商业智能系统[大型企业各相关部门]、敏捷型商业智能系统[中小企业或某个大企业的业务部门]）、多维数据集（多维数据模型）、所见即所得（数据可视化）

ETL功能¶

将数据从数据源端经过抽取（Extract），清洗转换（Transform），之后加载（Load）到数据仓库。
E-抽取：
创建与不同数据源间的连接关系，对这些数据源中的数据进行“引用”
T-清洗转换：
清洗的主要任务是筛选过滤不完整、错误及重复的数据记录
对“粒度”不一致的数据进行转换。
对业务规则不一致的数据进行转换。
L-加载：
将抽取出来的数据经过清洗与转换后加载到数据仓库中进行存储与使用。

数据仓库¶

DW：Data Warehouse 数据仓库
用来存储分析所需要的不同数据源上的所有相关数据信息。

OLAP¶

OLAP：on-Line Analytic Processing 联机分析处理
连接信息孤岛、创建多维数据模型。

表结构数据使用方法¶

表结构数据的横向合并¶

将不同表中的字段信息合并到同一个表中使用。
通过公共字段匹配：拥有相同记录值的字段。
左表与右表：连接命令左侧的表为左表，右侧的表为右表。
连接方向：决定表的主附关系，主要使用“左连接”（左表为主表）\“右连接”（右表为主表）\“内连接”（无主附之分）
对应关系：决定连接结果行数是对应项乘积的结果
E-R图：Entity Relationship Diagram 实体-联系图，多表连接的鸟瞰图

三种连接方式测试¶

利用Python环境下的pymysql对表的三种连接方式进行测试
In [27]:
import sys
# 导入pymysql模块
import pymysql

# 连接database
con = pymysql.connect(
    host="localhost",
    user="user",password="password",
    database="cda",
    charset="utf8")

# 得到一个可以执行SQL语句的光标对象
cursor = con.cursor()  # 执行完毕返回的结果集默认以元组显示
In [28]:
# 建立一个销售人员表待测试
# 删除已建立的表
sql = """
DROP TABLE IF EXISTS saler 
"""
cursor.execute(sql) # 执行SQL语句

# 定义要执行的SQL语句
sql = """
CREATE TABLE IF NOT EXISTS saler ( /*建立销售人员表*/
saler_id    CHAR(10)  NOT NULL ,
saler_name  CHAR(10)  NOT NULL ,
PRIMARY KEY (saler_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8; 
"""
cursor.execute(sql) # 执行SQL语句


# 向表中添加数据
sql = """
insert into saler values ('S01','赵大');
"""
cursor.execute(sql) # 执行SQL语句

sql = """
insert into saler values ('S02','王二');
"""
cursor.execute(sql) # 执行SQL语句

sql = """
insert into saler values ('S03','张三');
"""
cursor.execute(sql) # 执行SQL语句

# 获取Table
cursor.execute("SELECT * FROM saler")
print("saler")

# 打印Table头
des = cursor.description
print([item[0] for item in des])

# 打印Table数据
rows = cursor.fetchall()
for row in rows:
    print(row)

saler
['saler_id', 'saler_name']
('S01', '赵大')
('S02', '王二')
('S03', '张三')
In [29]:
# 建立一个订单表待测试
# 删除已建立的表
cursor.execute("DROP TABLE IF EXISTS indent") # 执行SQL语句

# 建立表
cursor.execute(
"""
CREATE TABLE IF NOT EXISTS indent ( /*建立订单人员表*/
order_id         CHAR(10)  NOT NULL ,
saler_id_num     CHAR(10)  NOT NULL ,
order_money      INT       NOT NULL ,
PRIMARY KEY (order_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8; 
"""
) 

# 向表中添加数据
cursor.execute("insert into indent values ('A01','S01',10000)") # 执行SQL语句
cursor.execute("insert into indent values ('A02','S02',15000)") # 执行SQL语句

# 获取Table
cursor.execute("SELECT * FROM indent")
print("indent")

# 打印Table头
des = cursor.description
print([item[0] for item in des])

# 打印Table数据
rows = cursor.fetchall()
for row in rows:
    print(row)
    

indent
['order_id', 'saler_id_num', 'order_money']
('A01', 'S01', 10000)
('A02', 'S02', 15000)
In [30]:
# 内连接测试
cursor.execute("DROP TABLE IF EXISTS inner_join_test")
cursor.execute(
"""
CREATE TABLE IF NOT EXISTS inner_join_test(
SELECT saler.*, indent.*
FROM saler INNER JOIN indent
ON saler.saler_id = indent.saler_id_num
);
"""
)
# 获取Table
cursor.execute("SELECT * FROM inner_join_test")
print("inner_join_test")

# 打印Table头
des = cursor.description
print([item[0] for item in des])

# 打印Table数据
rows = cursor.fetchall()
for row in rows:
    print(row)

inner_join_test
['saler_id', 'saler_name', 'order_id', 'saler_id_num', 'order_money']
('S01', '赵大', 'A01', 'S01', 10000)
('S02', '王二', 'A02', 'S02', 15000)
In [31]:
# 左连接测试
cursor.execute("DROP TABLE IF EXISTS left_join_test")
cursor.execute(
"""
CREATE TABLE IF NOT EXISTS left_join_test(
SELECT saler.*, indent.*
FROM saler LEFT JOIN indent
ON saler.saler_id = indent.saler_id_num
)
"""
)
# 获取Table
cursor.execute("SELECT * FROM left_join_test")
print("left_join_test")

# 打印Table头
des = cursor.description
print([item[0] for item in des])

# 打印Table数据
rows = cursor.fetchall()
for row in rows:
    print(row)

left_join_test
['saler_id', 'saler_name', 'order_id', 'saler_id_num', 'order_money']
('S01', '赵大', 'A01', 'S01', 10000)
('S02', '王二', 'A02', 'S02', 15000)
('S03', '张三', None, None, None)
In [32]:
# 右连接测试
cursor.execute("DROP TABLE IF EXISTS right_join_test")
cursor.execute(
"""
CREATE TABLE IF NOT EXISTS right_join_test(
SELECT saler.*, indent.*
FROM saler RIGHT JOIN indent
ON saler.saler_id = indent.saler_id_num
)
"""
)
# 获取Table
cursor.execute("SELECT * FROM right_join_test")
print("right_join_test")

# 打印Table头
des = cursor.description
print([item[0] for item in des])

# 打印Table数据
rows = cursor.fetchall()
for row in rows:
    print(row)

right_join_test
['saler_id', 'saler_name', 'order_id', 'saler_id_num', 'order_money']
('S01', '赵大', 'A01', 'S01', 10000)
('S02', '王二', 'A02', 'S02', 15000)
In [33]:
# 断开连接
# 增/删/改均需要进行commit提交,进行保存
con.commit()

# 关闭游标
cursor.close()

# 关闭连接
con.close()

表结构数据的纵向合并¶

多表中记录信息合并到同一个表中进行使用的合并方式称为纵向合并。
需要的两个条件：字段个数相同，相同位置字段的数据类型相同
两种方式：去重合并与全合并。

表结构数据的汇总¶

数据透视：对零散数据进行汇总分析。
维度->业务观测角度。
度量->业务行为结果。
汇总计算规则->衡量业务行为结果好坏的测量仪。
维度筛选度量、度量被维度筛选。

汇总计算规则¶

合计规则：
将相同维度值下对应的多个度量值相加在一起、一般用SUM函数代表合计规则。
计数规则：
对相同维度值下的度量个数进行计数、COUNT非空计数、DISTINCTCOUNT去重计数。
平均规则：
用合计规则的结果除以计数规则的结果（平均=合计/计数）、一般用AVERAGE函数表示。
最大值规则：
求相同维度之下最大的度量值、一半用MAX函数表示。
最小值规则：
求相同维度之下最小的度量值、一半用MIN函数表示。

数据分析的业务意义¶

数据分析是连接零散数据与人类认知间的桥梁。
零散数据->数据分析（数据透视分析，数据挖掘分析）->人类认知