admin管理员组文章数量:1595913
- 基本的数据类型:文本 整数 小数 布尔 日期 其他类型
变量类型 名义(性别、血型) 有序(好中差) 连续型(身高体重等)
数值前加单引号 将数值转化为文本
单元格格式的设置
- excel的基本操作
表格转表:表是数据库的概念
开始-套用格式 选择某一个格式
单元格操作:shift连续多行
复制粘贴-选转置 行和列分开
隔行插入 逆序排列
查找和替换: *表示任意多个字符 ?表示一个字符
选择粘贴:选择性粘贴 数据验证:东,南,西,北
粘贴运算
粘贴图片链接
- 函数公式
Offset函数,定位新的引用区域
Offset(起点,上下位移,向右移动一列,得到新的基准点,向下取几行,向右取几行)
使用在名称定义里面,
嵌套函数
If(iserror(a1),“error”,“ok”)
4、条件格式:
一单元格的值来设置条件格式
二公式返回的格式来设置格式
一、格式-自定义-只包含以下内容的单元格设置格式
二、使用公式确定要设置格式的单元格
美元符号¥$是锁定单元格用的,要去掉
图标集:套用条件格式
数据条和色阶:
数据条,一组数据中的最大值为最长条,色阶也是
迷你图:折线图、柱形图、盈亏图
插入—迷你图
5、
数据透视图 :筛选框
在表内排序 ,图也会变
插入——切片器(如果要关联,那么取的底层数据要是同一个)
选项-切片器样式
切片器 和数据透视图表关联
6、基本图表功能
3.1 power query 导入多源数据方法
Power query 整合多数据源数据(数据库,txt,csv,web,hdfs)
突破行数限制
通过M函数灵活创建自定义数据处理计算规则
创建流程可以多次使用
Excel13版以上可以用
数据-获取和转换
仅创建链接
数据-新建查询 导入accdb文件
选择多项√ 加载到 仅创建连接 在表格界面没有变化
数据-显示查询 双击打开
关闭并上载(过程才会保存)
Csv是文本文件
加载到 表
Txt文件导入-从文本导入
3.2 横向 纵向合并多源数据
字段(字段名和记录) 、表、数据库 (层层包含的关系)
横向合并:主表和副表中 字段名有对应的
先选主表-开始-合并查询(即横向合并)-再选副表-选择关键字段-选择id-连接种类-左外部
选择内部(主表有,副表也有)
上面的是左边,下面的是右表
选择展开字段
使用原始列名作为前缀 打勾 显示原来的字段名
连接演示
合并学员信息表1和2 ---
合并查询
关闭并上载
纵向合并---
追加查询
一个字段只能有一个字段类型
点击 关闭并上载
3.3菜单栏基本功能
开始----解决错误信息 :路径下文件不存在,退回到源里面
复制路径,点击回车
填充功能:向下填充—用空值null最上面临近的值填充
向上填充 同理
透视列:透视 将数据按照某种规则进行汇总
转换-透视列-
逆透视功能:转换-逆透视列-逆透视其他列-插入
添加自定义列:添加列-自定义列-选择可用列-输入公式
视图----
3.4 M函数精讲
会改m函数 ,会写m函数
编辑栏、添加自定义列、高级编辑器
在编辑器中输入:=#shared 查看m函数列表
Eg:把身份证号变成一个新的表
开始-新建源-空查询-输入=#shared
转换-到表中
文本数据text 表数据tab
添加列-索引列-
Text.range([身份证号码],16,1)
转换-信息-奇偶
开始-替换值 和ctrl f 一样 ,替换
Let:处理的步骤
In:处理的结果
自动化运行重复性工作
写好逻辑-点击数据刷新
3.5 基本数据处理方法
识别重复行方法: 使用公式countif 计算出现的频率、使用关键字段后使用公式 if 、利用数据透视表(凡是大于一的就是重复的)
去重方法:1 数据-排序和筛选-高级 -选择不重复
识别重复行后删除:
Q中有 开始-删除重复项
查找缺失值:ctrl+f ctrl+g 利用排序筛选
替换缺失值:1使用0填充 2使用平均数填充 3删除含有缺失值的记录 4暂时保留缺失值行
处理异常值:
数值or 文本
处理格式错误:
设置格式
使用分列功能
使用公式补全信息后转换
数据分组:
数据标准化和权重:
Min-max标准化:新数据=(原数据-极小值)/(极大值-极小值)
直接对比一线和二线城市的房价是不合理的,要进行标准
标准分=(原始分-平均分)/标准差
加权平均:
1 横向和纵向对比,横向重要就为1,纵向为0
2 横向加总
3 每个阶段合计值/合计总值*100%
加权平均值=变量1*1的权重+…+变量n*变量n的权重
多条件数据抽取:
(了解一下就可以了)
3.6 power query数据处理案例
处理各大城市3-12月的大气质量数据和城市属性数据 结合
城市属性+城市大气质量=带城市属性的大气数据
三个表合一
将第一行做标题
选择月份-日期-提取年or 月
删除市 –用替换
开始-合并查询-左外部
不勾选2个表里面相同的字段
处理null:替换
4.0 数据透视表的高级方法
对数值的汇总观测方法:透视
1百分比显示
总计的百分比和列总计的百分比
行汇总的百分比
父集中的百分比
添加3%的销售提成的字段(数据透视表中-分析-字段项目于求和)
删除字段:同上-删除
修改字段:同上
添加计算项:同上——字段项目-计算项
2不同维度及层级的汇总显示
3添加计算字段
4删除及修改计算字段
5添加及删除计算项
4.1power pivot导数:加强版的数据透视工具
导入文本 (access文件)accdb csv文件等
企业版的excel才有pivot 家庭版没有
数据-数据管理模型-进入pivot功能
4.2搭建多维数据透视分析模型
多维数据集、立方体 关联在一起不同类别的数据集合
表和表之间匹配的关键字段
关系图视图下选莫哥表的关键字段拖拽到另一个表的关键字段
主页-关系图视图
主页-数据透视表:生成povit的数据透视表
只有当副表的行列标签做行列的时候,才能用副表的行列标签做值
4.3 pivot创建层次结构
选择父集字段-选择创建层次结构-
-选择子集字段-拖拽到父集下面
4.4 pviot DAX表达式:数据分析表达式 pivot特有函数集
和excel的基本函数很相近或相同
Switch (表达式1,结果1…..,表达式n,结果n)
Related(合并字段) 类似于vlookup 创建了链接关系才可以用
Filter(表,筛选条件)在筛选条件下把表提取出来
Calculate(计算规则,条件1,条件2)
4.5 pivot使用kpi
三色图标
5.1 数据可视化分析方法论
对比分析——纵向对比 柱状图和折线图
进度分析-仪表盘 温度图 游标图
横向对比-饼图 条形图 复段折线图
标准值 平均值 计划值 对比
子弹图 :平均对比分析
实际和计划值的对比:对比柱形图
同环比对比:
预警分析:kpi分析 关键指标的达成进度
透视分析:
结构分析:杜邦分析 树形结构分析
变化分析
分组分析
增维分析 : 折线+环形图
5.2描述性统计图表
频数 频率
直方图: 输入区域 接受区域
分类间距改为0
盒须图:用来体现数据分散情况
四分位数:将数据由小到达排列分成4等份,Q1 Q2 Q3 ,其中Q3-Q1就是盒子 占整个数据的50%。出于三个分割点位置的数值就是四分位数
5.3专业自定义图表制作
迷你图表 五星图表 :(if(大于,“*”,:“*”))
公式、公式加条件格式、
嵌套图表:仪表盘:内圈 外圈 环形图 下半圈改成白色
插入文本框
多维折线图:先做折线图 ,再做环形图(小的)
基本图表再创新:
瀑布图:堆积柱形图,底下设为无填充
分段折线图:列的数据 表格分段显示
5.4交互式图表制作
选择数据透视表图,插入 切片器,选择关键字段
右键-报表连接 选择数据透视表
Offset 引用
开发工具——插入-选项按钮-改名字-设置控件格式
5.5power map数据地图
必须要联网下使用-
插入-三维地图-
选择时间,有播放按钮,
无聚合
选择-新场景
5.6 power view 交互式仪表板创建
文件-选项-加载项-com加载项-勾选power view选项
新建一个空的选项卡-然后把原来的拖拽到新的选项卡下面
计数-不重复
6.0杜邦分析仪
6.1业务背景介绍
销售漏斗分析:潜在,接触,意向,明确,投入,谈判,成交、
销售管理分析;客户维度,产品维度,地域维度,销售维度,渠道维度,商机维度
多维度分析
6.2分析仪制作方法
Getpivotdata函数
7.1 MySQL安装及调试
先关杀毒软件
Custom
Mysql 5.7版本
Workbench 6.3
Connector 5.3
记住账号密码
测试 select 1+1;
7.2数据库概述
关系型数据库
非关系型数据库
数据库-表(多个字段的集合)-字段(字段名和记录组成)
Sql可以对应多种的关系型数据库
7.3数据库操作
创建数据库 creat datebase test;
查看创建好的数据库 show creat datebase test;
查看所有的数据库列表 show datebases;
使用数据库: 使用创建好的test数据库 use test;
删除数据库 drop database test;
用sys下的
Code,sql 文件 拖动到软件里面
注释 -- #
点击闪电or ctrl 加回车 执行代码
左侧要点刷新按钮
--创建数据库
Creat database test;
--查看创建好的数据库
Show creat database test;
--查看所有数据库列表
Show databases;
--使用数据库
Use test;
--删除数据库
Drop database test;
7.4 数据表操作
1 使用test数据库
use test;
2 创建员工信息表
creat table emp(
Depid char(3),固定长为3的文本型
Depname varchar(20),最大长为20的可变长的文本型
Peoplecount int 整数型
);
3 查看表是否创建成功
Show tables;
4 删除数据表
Drop table emp;
7.5数据类型
注册时间 :日期型
用户名: 文本型
充值金额 :小数型
位:bit 0或1
字节:8个字节组成的 byte
数据类型:具体不同类型的直接长度
一个int整数型占4个byte 长度
Int
Tinyint
Smallint
Mediuint
Bigint
在指定字段数据类型时如果在数据类型后加上unsigned可以禁止负数
Int(11)显示宽度为11
小数型:
Float 4字节
Double 8字节
Decimal
日期型:
Datetime:yyyy-mm-dd hh:mm:ss显示年月日和时分秒
Date:只显示年月日
Timestamp
Year(2|4)
字符串:
Char 固定长度
Varchar 可变长度字符串
防止数据溢出
Blob或text
Tinyblob和tinytext
7.66约束条件
约束是在表上强制执行的数据检验规则,用来保证创建的表的数据完整和正确
#主键约束 primary key 唯一的标识 是非空和唯一的
可以加快数据库的查询速度,
#非空约束 not null
写在要限定字段的后面
#唯一约束 Unique 不能重复
写在后面
#自增字段 auto increment
写在后面
字段名 数据类型 default默认值
#默认值 default 默认情况下从1开始自增
写在后面
Use test;
Creat table emp(
Depid char(3)primary key,
Depname varchar(20)not null,
Peoplecount int unique default 0
);
8.1填充数据
Insert into 表名(字段1,字段2……)values…..
实际应用场景中,下面应用的更多
为Monthly_indicator表导入外部txt文件:
Load date local infile’文件路径.txt’
Into table Monthly_indicator
Fields terminated by ‘\t’ 导入的是txt文件,所以要用分割符\t
Ignore 1 lines; 第一行一般是字段名 所以忽略
检查导入内容 select*from xxx;
检查导入数据总行数 select count(*)from xxx;
检查表结构 desc xxx;
8.2修改数据表
使用alter table语句修改数据表结构
将数据表xxx改名为xxxdep
Alter table xxx rename xxxdep;
讲数据表xxx中aaa字段数据的数据类型由varchar(20)修改为varchar(30)
Alter table xxx modify depname varchar(30);
将数据表xxx中aaa字段的字段名改为 bbb
Alter table xxx change aaa bbb varchar(30);
将数据表xxx中aaa字段的字段名改回卫 bbb,并将该字段的数据类型改回为varchar(20)
Alter table xxx change aaa bbb carchar(20);
为数据表xxx添加新字段 ddd 新字段的数据类型为varchar(10),约束条件为非空
Alter table xxx add ddd varchar(10)not null;
Change可以改表结构和数据类型,modify只能修改数据类型
将数据表xxx中的name字段的排列顺序改为第一位
Alter table xxx modify name varchar(10) first;
将数据表xxx中name字段的排列顺序改到 bbb字段之后
Alter table xxx modify name varchar(10)after bbb;
删除name字段
Alter table xxx drop name;
查字段类型
Desc xxx
9.1数据整合 函数
主要用于处理数字值
函数 说明
ABS(x) 返回x的绝对值
LOG(X,Y) 返回x的二进制
MOD(X,Y) 返回x/y的模(余数)
RAND() 返回0到1内的随机值,可以通过提高一个参数(种子)使RAND()随机数生成器生成一个指定的值
CEILING(X) 返回大于x的最小整数值(进一取整)
ROUND(x,y) 返回参数x的四舍五入的有y位小数的值
SQRT(x) 返回一个数的平方根
Concat(s1,s2…….,sn) 将s1sn连接成字符串
Now() 返回当前的日期和时间
Date(datetime) 返回datetime的日期值
Time(datetime) 返回datetime的时间值
Group_concat(col)
使用group_contact 函数查询不同sid下对应的所有f_name信息
Select sid,group_concat (f_name) from fruits
Group by Sid
Cast将一个值转化为指定的
Dpdate set 为字段赋值
Delete
删除fid为b5的数据记录
Delete from fruits where fid=’b5’
9.2 sql查询
加减乘除 + - * /
!=不等于 !<不小于
大于> 大于等于>= 小于类似
聚合类函数
Avg
Sum
Max
Min
Count 计数(和excel一样)
对大气质量表进行有选择的查询
Select cityname ,avg(pm25),avg(pm10)
From monthly_indicator数据源
Where pm25>50 元组选择条件
Group by cityname,monthkey having cityname<>’北京’ 分组条件
Order by avg(pm25)desc;
安装pm2.5是降序的排序排列
Desc是降序排序
Asc是升序排序
例子:
查询大气质量表中的全部内容
Select*
From monthly_indicator
查询北京的大气质量数据
Select*
From monthly_indicator
Where city_name=’北京’
查询不同月份PM2.5的最大值
Select month_key ,max(pm 25)
From monthly_indicator
Group by month_key
Group by不好理解的时候就理解为数据透视表的值的标签
降序查询不同城市pm10的平均值
Select city_name ,avg(pm 10)
From monthly_indicator
Group by city_name
Order by avg(pm10)desc;
有排序就需要使用order by
多表查询:
将2个以上的数据表通过关键字段连接在一起,并从不同表中取不同字段进行查询的方法
关键字段:用来链接2表的内容信息能够匹配的上的字段
1相连的两表中都需要有关键字段
2关键字段中的记录信息能够匹配的上
3 最理想的链接转态是2表中的关键字段都是主键,而且2个主键的值能够一一匹配的上
Select list from 表1 xx join 表2 on 表1.key=表2.key
- xx代表链接的方向,可以说inner left right等关键字
- 在连接语句前边的表是“左表”,在链接语句后的表是右表
Select 学员信息表.*,学员成绩表.*from 学员信息表 left join 学员成绩表 on 学员信息表.学号=学员成绩表.学号;
Left join以左边的表为主要的参考
内连接查询inner join
内连接:按照连接条件合并两个表,返回满足条件的行。
Select list from a
Inner join b on a.key =b.key
就是取交集
左连接 left join
取交集和左表的所有行
Select list from a
left join b on a.key =b.key
取交集和右表的所有行
Select list from a
right join b on a.key =b.key
联合查询
Union 用于合并2个或多个select语句的结果集,并消去表中任何重复行。
用union合并t1和t2表
Select t1.*from t1
Union
Select t2.*fromt2;
Union all:用于合并2个或多个select语句的结果集,保留重复行
用union 合并t1与t2表
Select t1.*from t1
Union all
Select t2.* from t2;
9.3查询操作符与子查询
Creat table fruits(
F_id char(10) not null,
S_id int not null,
F_name varchar (255) not null,
F_price decimal (8,2) not nul,
Primary key(f_id)
);
字段说明:
F_id 水果id
S_id 品类id
F_name 水果名称
F_price 水果价格
And 用来联合多个条件进行查询,条件与条件间是“和”的意思
Select*from fruits where sid=101 and fid=a1
Or 用来联合多个条件进行查询,条件与条件间是“或”的意思
Select* from fruits where fname=’apple’ or fname=’orange’
In 判断某个字段的值是否在制定的集合中,如果在则满足查询条件,如果不在则不满足查询条件
用in查询苹果和橘子的相关记录
Select*from fruits where fname in ('apple’,’orange’)
用not in 操作查询苹果和橘子之外的水果的相关记录
Select*from fruits where fname not in ('apple’,’orange’);
Between 判断某个字段的值是否在指定的范围内
Eg:用between…and操作符查询fprice 在10元到20元之间的水果记录
Select*from fruits where fprice between 10 and 20;
Like 用来匹配字符串是否相等,如果字段的值与指定的字符串相匹配,则满足查询条件
查询所有fid由b开头切字符长度为2位的水果记录
Select*from fruits where fid like’b_’;
B%k表示b开头,k结尾的任意长度的字符串,如果bak book break 都可以
B_k 表示b开头,k结尾的3个字符串,只有bak可以, book break都不可以
Is null 用来判断字段的值是否为空值null
查询所有fname为空的水果记录
Select*from fruits where f_name is null;
Distinct:用来消除重复记录
查询fruits表中所有不重复的s_id
Select distinct s_id from fruits;
子查询:写在()中,把内层查询结果当做外层查询参照的数据表来用
Any 只要瞒住内层查询语句返回的结果中的任何一个,就可以咨询外层查询语句
查询fid对应的fprice在10元到20元之间的水果记录
Select*from fruits where fid=any
(select fid from fruits where fprice between 10 and 20);
All 只有满足内层查询语句返回的所有结果,才可以咨询外层查询语句
查询所有fprice大于20元的水果记录
Select*from fruits where fprice>all
(select fprice from fruits where fprice<=20);
Exists 不返回查询的记录,而是返回一个真假值。如果为真,则返回所有为真的记录
查询是否存在fprice 大于30元的水果记录
Select*from fruits where exists
(select*from fruits where fprice>30);
As 将表或者字段重命名
讲fruits表名重命名为f后使用
Select f.*from fruits as f ;
Limit 查询只显示limit指定数字行数结果
显示fprice金额最大的前三名水果记录
Select *from fruits
Order by fprice desc
Limit 3 ;
10.1 使用SQL语句处理及查询电商业务数据1
E-R图
不同的表之间 字段的链接关系
1在这个表里面没有重复值
*表示多 这个字段在这个表里面没有重复值
多对多的结果是字段相乘的结果 ,比如2x2 等于4 最终当我们对表格进行相加或者相乘的时候会非常不方便,所以尽量不要用多对多
倒序查询卖的金额最多的产品
查询不同尺码下的产品销售数量
查询不同颜色下的产品销售金额
查询不同尺码下的不同颜色的产品销售金额
10.2 使用SQL语句处理及查询电商业务数据2
求出购买产品金额最多的前十名顾客
求出购买产品金额最多的前十名顾客的所在城市
求出购买力最强的前十个城市
求出购买力最强的前十个城市以及他们所在的省份
先建一个过渡表
多表连接基础还不牢
版权声明:本文标题:完结数据分析路线(带图) 内容由热心网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:https://www.elefans.com/dongtai/1728243900a1150846.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
发表评论