数据分析

[TOC]

相关链接：

戴师兄_数据分析_课程视频

戴师兄_数据分析_课程笔记

一、 数据

1. 观测

1.及时发现异常。

2.找数据之间的因果关系。

2. 实验

提出假设，然后验证假设。

所有未经事实数据验证的想法都是假设。

设计A/B测试获取数据。

3. 应用数据创造价值

1.基于数据反馈不断迭代产品和业务策略。

拆解方法：

  • 流程拆解法
  • 二分法
  • 象限拆解法
  • 杜邦分析法

拆解只要符合MECC法则即可：

  • AARRR
  • PEST
  • RFM
  • SWOT
  • 5W1H

2.基于数据训练算法，让机器自动化地完成工作。

二、 Excel

1. 使用方法

1. 引用

可以跨工作表进行引用，非本工作表引用时会出现 表名+! 。

2. 窗口分屏

视图-新建窗口

默认当前的工作表被分到新窗口。

win+方向键。

3. 冻结窗格

视图-冻结窗格

方便固定某行或列查看数据。

选定单元格是冻结它的上一行和上一列。

4. 数字和日期转化

数字 1 默认为 1900-1-1 .

5. 同比和环比

  • 同比

    • = （本期数 - 同期数） / 同期数
    • = 本期数 / 同期数 - 1

  • 环比

    • = （本期数 - 上期数） / 上期数
    • = 本期数 / 上期数 - 1

年：

  • 2020年环比
    • = (2020年数据-2019年数据) / 2019年数据
    • = (2020年数据 / 2019年数据) - (2019年数据 / 2019年数据)
    • = 2020年数据 / 2019年数据 - 1

月：

  • 2020年7月环比

    • = 2020年7月数据/2020年6月数据 - 1

  • 2020年7月同比

    • = 2020年7月数据 / 2019年数据 - 1

日：

  • 2020年7月1日环比

    • = 2020年7月1日数据 / 2020年6月30日数据 - 1

  • 2020年7月1日的月同比

    • = 2020年7月1日数据 / 2020年6月1日数据 - 1

  • 2020年7月1日的周同比

    • = 2020年7月1日数据 / 2020年6月24日数据 - 1

6. 数据透视

1.点击单元格-插入-数据透视表。

2.设置筛选、列、行、值。

3.自行添加字段：点击 字段、项目和、集-设置名称和公式-修改 。

4.插入切片器（用于筛选）——可以在透视表外的工资表进行筛选。

注：点击单元格选择与切片器相同的字段——只能在透视表内进行筛选。

5.图表制作:

“点击单元格-数据透视图”

6.聚合（分类汇总）

数据透视表会把多行进行聚合运算。

7. 数据验证

数据-数据验证-设置参数

可以实现不同变量的表格切换

可用于周报开发

8. 求和

选定区域-开始-求和符号 Σ ;

Alt + = ;

SUM函数

数值类的单元格向右拉填充，公式计算类的单元格从上往下再拖一次。

9. 图表

选中区域-插入-选择图表类型

10. 条件规则

选定区域-开始-条件格式-新建规则

修改

选定区域-开始-条件格式-管理规则

多次应用

选定单元格-开始-格式刷-目标单元格

11. 复制格式

选定单元格-开始-格式刷-目标单元格

单击只能刷一个单元格。

双击可以一直使用。

2. 函数

1. 注意事项

1.输入函数需要用 = ，选择函数用 Tab 识别。

2.函数中写入中文时，必须加上英文的双引号 “ “ .

例如：”美团”

3.函数中写入条件判断（如>=）时，必须加上英文的双引号 “ “ ，同时用 & 链接条件语句。

例如：”>=” & DATE(YEAR(B39),MONTH(B39),1)

4.多次使用同一函数

复制函数所在单元格，粘贴到目标单元格。

从函数所在单元格右下角下拉，双击右下角是自动填充到表格的最后一行。

拖拽时需要锁定单元格

快捷键 F4

我们直接引用B15单元格，输入 =B15 ，然后向右拖拽。会变成C15，向下拖拽会变成B16。

a：=$B15，在列号B前加 $ ，那么列就不会变了。

  • 拖动句柄向右，列号不变，为$B15.
  • 拖动句柄向下，行号会变，为$B16.

b：=B$15，在行号15面前加 $ ，那么行就不会变了。

  • 拖动句柄向下，行号不变，为B$15.
  • 拖动句柄向右，列号会变，为C$15.

2. SUM

1
2
SUM(number1,number2,...)SUM(列名)SUM(行名)
SUM(单个或多个单元格)SUM(列的名称，如A:A)SUM(行的名称，如1:1)

SUM可以用逗号’,’对多个区域进行求和。

3. SUMIF

1
2
SUMIF(range,criteria,[sum_range])
SUMIF(条件判断所在区域，条件，[用来求和的数值区域])

4. SUMIFS

1
2
SUMIFS(sum_range,[criteria_range1],[criteria1],[criteria_range2],[criteria2]...)
SUMIFS(用来求和的数值区域，条件1判断所在的区域1，条件1，条件2判断所在的区域2，条件2...)

5. DATE

注：

不要用excel的日期格式存储日期的数据，要用字符串存储

日期变量进行运算是最好用括号括起来。

年

1
2
YEAR(serial_number)
YEAR(日期)

月

1
2
MONTH(serial_number)
MONTH(日期)

日

1
2
DAY(serial_number)
DAY(日期)

日期组合

1
2
DATE(year,month,day)
DATE(代表年份的数值，代表月份的数值，代表日份的数值)

每个月第一天

1
DATE(year(日期),month(日期),1)

每个月最后一天

1
2
DATE(year(日期),month(日期)+1,1)-1
or DATE(year(日期),month(日期)+1,0)

6. SUBTOTAL

SUBTOTAL会自动根据原数据的筛选进行计算，因此优于SUM函数。

1
2
SUBTOTAL(function_num,ref1,[ref2],...)
SUBTOTAL(指定函数，指定区域1，[选择区域2],...)

7. IF

1
2
IF(logical_test,value_if_true,[value_if_false])
IF(逻辑比较条件，结果成立时返回的值，[结果不成立时返回的值])

注：

[value_if_false]：该参数选填，没有该参数时，返回值False.

例如：（嵌套）

1
=IF(I80=0,IF(J80=0,"AB都等于0","A等于0，但B不等于0"),IF(J80=0,"A不等于0，但B等于0","AB都不等于0"))
A B 判断
0 0 AB都等于0
1 0 A不等于0，但B等于0
1 1 AB都不等于0
0 1 A等于0，但B不等于0

8. VLOOKUP

1
2
VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
VLOOKUP(要查找的数据，要查找的位置和要返回的数据的区域，要返回的数据在该区域的列号，返回近似匹配或精确匹配-指示为1/TRUE或0/FALSE)

注：

1.要查找的数据必须在 table_array 的第一列，否则会报错。

2.通常使用精确匹配， [range_lookup] 的值输入 0 .

3.VLOOKUP只会返回它查找到的第一个值。

模糊查询

在 lookup_value 后面加上通配符 &”通配符”

通配符

  • *：代替不定数量的字符。
  • ?：（英文输入状态下）代替下一个字符。

例如：

1
2
3
=VLOOKUP(I96&"*",F96:G103,2,0)

=VLOOKUP(I99&"???",F96:G103,2,0)

9. MATCH*

在选定的行or列里找出目标数据的位置。

1
2
3
MATCH(lookup_value,lookup_array,[match_type])

MATCH(查找项，查找区域，0)

10. INDEX*

1
2
INDEX(array,row_num,column_num)
INDEX(区域，行号，列号)

行号 为 0 时返回整列，列号 为 0 时返回整行。

11. 函数组合使用

index和match组合使用

1
index(数据区域，match(行查找项，index A数据区域的相对区域，0)，match(列查找项，index B数据区域的相对区域，0)

例如：

1
=INDEX('拌客源数据1-8月'!$A:$X,MATCH($B112,'拌客源数据1-8月'!$I:$I,0),MATCH(D$111,'拌客源数据1-8月'!$1:$1,0))

注意区域是否用 $ 锁定。

sumifs、index和match组合使用

例如：

1
=SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(G$111,'拌客源数据1-8月'!$1:$1,0)),'拌客源数据1-8月'!$I:$I,$B112)

3. 周报开发

1.框架

  • 标题
  • 小看板
  • 结果指标
  • 过程指标

2.计算

客单价 = GMV/有效订单

周环比 = 本周数据 / 上周数据 -1

到手率 = 商家实收 / GMV

到手率周环比 = 本周到手率 / （上周商家实收/上周GMV） -1

进店转化率 = 进店人数 / 曝光人数

下单转化率 = 下单人数 / 进店人数

营销占比 = cpc总费用 / GMV

总营销占比 = 当周的cpc总费用 / 当周的GMV

业务进度 = 截止目前的GMV / 目标

3.优化

1.迷你图：选定区域-插入-折线图-选择单元格-勾选标记

2.进度条：选定区域-开始-条件格式-新建规则-基于各自值设置所以单元格的格式-进度条-数值-0~1

3.字体颜色：选定区域-开始-条件格式-新建规则-只包含以下内容的单元格设置格式-单元格值-大于-0-格式-颜色

4.图标：选定区域-开始-条件格式-新建规则-基于各自值设置所以单元格的格式-图标集-图标-值-类型

5.标记数据：选定区域-开始-条件格式-新建规则-使用公式确定要设置的单元格-公式-格式

例如：=$C13<AVERAGE($C$13:$C$19)

6.美化：

视图-功能区-取消网格线

选定标题单元格-合并后居中-放大字体-加粗

更改小标题的字体颜色并加粗

文本字体加粗加边框

三、 Tableau

1. Tableau简介

进行可视化工作

可以在Tableau社区学习优秀的可视化方案

Tableau社区

四大流派：

艺术流：旨在探索更多的数据表达方式，相当于用数据作画。

分析流：针对某一专项的主体，用可视化充分说明分析数据，做到高效的信息传达。

实战流：为实际的业务场景服务，追求数据的全面性和逻辑，能用就行。

垃圾流：毫无意义的堆砌，无主题。

可视化只是Tableau的基础操作，数据赋能和数据探索才是关键。

数据赋能：让业务一线也可以轻松使用最新数据。

数据探索：通过统计分析和数据可视化，从数据发现问题，用数据验证假设。

  • 支持亿级数据的连接和处理
  • 自由地对字段进行计算
  • 拖拽就可以轻松制作图表
  • 数据可以随意聚合下钻
  • 图标类型可以灵活转换
  • 内置算法智能建模

其他可视化工具：

Excel：

  • 容易上手
  • 功能强大
  • 过程难以复用和自动化
  • 无法处理大量数据

SQL：

  • 功能强大,但只擅长于取数和业务查询
  • 可以重复执行和自动化

Python/R

  • 学习难度高
  • 功能强大，数据处理、描述性分析和算法建模都能做
  • 可以重复执行和自动化

Tableau

  • 容易上手
  • 美观直接
  • 功能强大，尤其在数据探索和可视化遥遥领先
  • 可以重复执行
  • 历史悠久

Tableau、PowerBI、FineBI之间

功能全面性

  • PBI内置功能最全，但生态内的拓展资源较少。不支持MAC。
  • Tableau功能完全够用，生态内有很多拓展程序可以调用。
  • FineBI属于国内刚起步的产品，很多功能的使用不太方便，等后续优化。

易学程度

  • Tableau适合0基础。

  • 熟练掌握Excel，PBI更容易上手。b站up:孙兴华zz

  • FineBI和其他BI的学习，需要有一定的BI工具使用基础。

学习顺序

  • 0基础先掌握Tableau
  • Excel熟练后学PBI
  • 根据应聘岗位需求突击学习（熟悉功能，掌握基础用法）
  • 真正的BI技能提升，还是要从实践中学习

2. 下载安装

1. 下载地址

Tableau Desktop下载

2 .Tableau 在Windows环境下安装失败的处理

Tableau 在Windows环境下安装失败的处理-Tableau喜乐君-敏捷BI布道师 (xilejun.com)

[最新的 Microsoft Visual C++ 可再发行程序包版本](最新受支持的 Visual C++ 可再发行程序包下载 | Microsoft Learn)

注：卸载之前的所有Microsoft Visual C++，然后更新window系统！！！

3. 数据连接

1. 本地文件

  • csv数据连接到文本。
  • xls/xlsx连接到Excel。
  • json直接连接到json

2. 数据库

  • 下载数据库对应的驱动后，填写地址等参数连接到对应数据库。
  • 见SQL。

4. 连接方式

  • 默认使用智能连接，只需要选择所用的数据字段即可。
  • 连接所用的字段可以通过函数进行计算和编辑。
  • 表格之间的连接原理，见SQL。

例如

先连接到shop，然后拖拽cpc，选择连接字段

门店id=门店id 日期=日期

继续拖拽orders，线与shop相连接，选择连接字段

门店id=门店id 日期=下单日期

5. 提取方式

1.实时

每次计算都会连接一次数据库进行取数运算。

注：因为Tableau的本质是Vizql查询语言，每做一个操作就提取一次。不过在数据量级较大的情况下就会比较慢。所以一般选择数据提取。

2.数据提取

数据提取的数据库文件（工作表）保存为.hyper格式。

将当前连接所涉及的数据全部都查询提取到.hyper格式的数据提取。（相当于Tableau自己的数据库和对应类型的文件)

两种工作簿的保存类型

  • 保存为**.twb**格式，里面不带数据，打开工作表需要连接数据。

  • 如果保存为**.twbx**格式，hyper会自动打包进twbx，使tableau自带数据。

缺点是每次修改完数据源都要重新提取一遍数据，如果是实时，则不需要先提取，等你作图时他才会再提取。但如果要将Tableau上传到Sever服务器，则必须使用提取的数据连接方式。

6. 提取选项

如果选择数据提取

  • 可以在右侧编辑数据提取，Tableau还支持对表格进行基于字段和函数的增量刷新，提升数据提取的性能，但百万级以下的数据，除非频繁提取，否则一般不需要这样操作。
  • 可以在提取数据后进行预筛选，只保留筛选后的数据进行视图操作。

7. 数据可视化原理

  • 图片比文字更容易识别和理解。
  • 原始数据经过数据转换成数据表，再经过视觉映射变成视觉结构，又由图形转换成图像，最后被用户感知。

对于可视化来说，数据有【度量】和【维度】两种类型。

  • 数值型变量【度量】Measures
    • 一般是由数字组成的变量。
    • 例如：成交金额、用户数、点击量。
    • 数值变量可以进行计算，并基于计算结果的大小表示图表的面积大小、条形长短、颜色深浅等可以量化的视觉元素。
  • 类别型变量【维度】Dimensions
    • 类别变量包含有限的类别数或可区分组数。（数值变量过多时需要分组）
    • 例如：用户ID、性别、来源渠道。
    • 类别变量主要用来对数值变量的计算结果进行区分，表现为图表的颜色种类、图形位置、分类方式等难以量化的视觉元素。
    • 只能进行计数和分布等简单计算。
  • Tableau会自动区分变量类型，两者之间也可以根据可视化需求进行转换。

【度量】映射图形，【维度】负责区分。

  • 数据可映射的图形类型（按精确度排行）

    • 位置（散点图）

    • 长度（柱状图）

    • 角度（饼图）

    • 方向（折线图）

    • 形状

    • 面积和体积

    • 颜色和深度

对【度量】和【维度】进行拖拽操作，从而完成可视化图表的制作，这是Tableau可视化原理的第一概念。

拖拽至：行列、标记卡、筛选器（不要用智能推荐）

任何度量型的变量都可以切换形状。

维度对度量值进行区分，增强度量值的学习密度，这是Tableau可视化原理的第二概念。

  • 度量默认聚合。
  • 度量值会形成图形标记，图形标记可以切换。
  • 维度会对度量值进行区分，增加度量值的信息密度。
  • 图表分为有轴图表和无轴图表。
  • 离散形成标签，连续形成数轴。

常用图形类型

  • 条形图
  • 热力图（突出显示表）
  • 气泡图
  • 树地图
  • 词云
  • 饼图
  • 堆积图
  • 折线图
  • 面积图
  • 散点图
  • 直方图
  • 地图

8. 可视化原则

  • 区分用户（不同用户对于仪表盘的需求不一样）
  • 主次分明，详略得当
  • 真实准确（坐标轴从零开始）
  • 符合大众认知和审美习惯
  • 适度原则（不要用3D，颜色适度）
  • 5秒原则（5秒能看懂自己的仪表盘）
  • 恰到好处的说明
  • 少即是多（精简高效）

9. BI仪表盘

1. 仪表盘搭建原则

仪表盘搭建符合业务阅读、思考、操作的逻辑。

2. 明确仪表盘主题

观众的身份，他们感兴趣的点。

1. 普通业务人员

跟业务切实相关的数据

  • 销售：注册-激活-成交
  • 投放：消耗-转化率
  • 运维：搬车数-换电数
2. 中层管理者

整体的总结报告

  • 业务整体的绝对值、达成率、同比环比。
  • 有哪些数据优异的表现。

各个维度的数据

  • 各个业务动作的绝对值、达成率、同比环比。
  • 数据异常的具体原因。
3. 高层管理者

整体的总结报告

  • 业务整体的绝对值、达成率、同比环比。
  • 有哪些数据优异的表现。
4. 用人单位
  • 有一个明确的分析主题。
  • 分析维度越多越好。
  • 图表越高级越好。

3. 仪表盘主题拆解

怎样的数据能表达清楚你想说的主题

  • 这个主题设计到哪些数据
  • 数据的主要程度是否相同
  • 最适合数据的图表是哪种

以每日营收情况主题为例

关键数据

  • 营收数据
    • GMV
    • 商家实收
    • 用户实付
    • cpc总费用
    • 各平台数据对比
  • 流量数据
    • 曝光人数
    • 进店人数
    • 下单人数
    • 进店转化率
    • 下单转化率
    • 新客数
    • 老客数
    • 复购率

数据排序

​ 营收-投放-流量

图表选择

  • 经营情况总览：突出显示的文字
  • 经营数据详情：表格
  • 每日营收数据：多轴折线图
  • 每日流量数据：双轴组合图
  • 新老客占比：环形图
  • 平台占比：环形图
  • 门店占比：环形图
  • 投放情况：散点图
  • 订单分布：面积图
  • 配送分布：地图

4. 开发设计工作表

1. 开发操作
1. 标记-工具提示

不会出现在图表界面，而是鼠标交互时才显示。

2. 隐藏横纵轴

右键-隐藏标签。

3. 隐藏零值线（坐标轴）

右键-设置格式-线-零直线-无

4. 复制图表

双击行可以输入代码，本质是复制。

5. 重新命名值的名称

右键-设置别名。

2. 经营情况总览：突出显示的文字

选择营收数据加入文本

由于有些数据在一些日期存在空值，将日期加入筛选器，选择有效数据的日期区间，并设置为-应用于工作表-使用相关数据源的筛选项

3. 经营数据详情：表格

将总览表进行复制，加入门店名称作为区分

4. 每日营收数据：多轴折线图

将营收数据放在行，日期放在列

为了优化以后有关日期（天）相同操作，右键-日期-创建-自定义日期-详细信息-天，并把日期加入筛选器。

5. 每日流量数据：双轴组合图

将曝光人数作为行，日期作为列并且进去筛选器。

创建计算字段

  • 进店率

    1
    SUM(进店人数)/SUM(曝光人数)

  • 成交率

    1
    SUM(下单人数)/SUM(进店人数)

将进店率和成交率放入数轴，右键度量值，选择双轴图

6. 新老客占比：环形图

创建计算字段

新老客

1
2
3
IF IFNULL([Order 90D],0) = 0 then '新客'
ELSE '老客'
END

将新老客拖入颜色和标签，orders.csv计数拖入大小、标签和工具提示。并把orders.csv的标签项-右键-快速表计算-合计百分比-右键-设置格式-设置小数点位数。

平台占比、门店占比同理。

7. 投放情况

列为cpc总费用，行为GMV，日期转为字符串放入详细信息，品牌名称放入颜色，添加趋势线。

8. 订单分布

（天）下单日期为列，计数为行，再把（小时）下单日期时间添加到颜色，选择区域。

9. 配送分布

双击经纬度，将主键放入详细信息，右键距离-创建组-按不同距离段创建组-放入颜色。

地图按住 A 选区，按住 F 拖拽。

5. 构思仪表盘布局

故事=ppt

拖拽图表进行布局，可以选择平铺和浮动。

一般字体用微软雅黑。

仪表盘大小，用通用桌面1366*768，高度调到2000。

布局-背景：加底色。

筛选器右键-更多选项-浮动

边框：点击图表-布局-边界

简化：

选择图表所在工作表-右键-设置格式-边界-行分隔符-无

最上面一排-地图-背景地图-普通

地图-背景层-冲蚀（透明度）

颜色-更多颜色-pick screen color

四、 SQL

1. 简介

学习SQLZoo，主要适用于MySQL数据库。

SQL的语句的查询部分已经够用了。

使用资源：SQLZoo，必须使用英文界面练习代码，并切换至MySQL引擎（右上角齿轮）。

课程代码并非最优，答案以课程为准。

本课程的运行顺序是为了有助于学习，实际应用场景则复杂很多。

数据表：

、、

、

、、

  • 语法结构

select - from - where - group by - having - order by -limit

  • 运行顺序

from - where - group by - having - order by - limit - select

![Run order](D:\user\desktop\study\数据分析\SQL\Run order.jpeg)

2. 基础语法和运行原理

1. select&from

1
2
select 字段名
from 表名

多个字段名之间用英文逗号（,）隔开。

每个sql语句字段之间加空格。

查询结果的顺序根据select后面字段的顺序显示。

只写单段代码不加分号，多段代码加分号。

  • *：查出所有字段，并按表格原有顺序显示。
1
2
select * 
from world
1. 别名

as可以省略。

1
2
select as 别名
字段名 别名
2. distinct:去重
  • 查询表中所含的洲名称。
1
2
3
4
select distinct continent 洲 
from world;
select distinct name,continent 
from world

distinct必须紧跟在select后面，会对后面的所有字段去重。

3 .计算

四则运算都可以使用。

  • 查询国家名、gdp和人均gdp。
1
2
select name,gdp,gdp/population 人均gdp 
from world

2. where

1
2
3
select 字段名
from 表名
[where 字段名 运算符 值]

[ ]表示where是可选项

通过表达式筛选出符合查询条件的行数据。

1. 运算符
运算符
= 等于
> , >= 大于，大于等于
< , <= 小于，小于等于
<> or != 不等于
between…and… 在指定的两个值之间，闭区间
in,not in 在（不在）该条件范围筛选
is null , is not null 为（不为）空值
and 逻辑运算符：与
or 逻辑运算符：或
not 逻辑运算符：非（一般与其他连用）

  • null为查询空值，表现为,与0和null字符串无关。null字符串为=’null’。

  • 运算符后面跟文本需要加上 ‘’ .

  • in 后面用(,,)表示范围。

  • between…and…前面写小范围，后面写大范围。

  • 查询人口大于2亿的国家信息。

1
2
3
select name,population,gdp/population 人均gdp
from world
where population >=200000000
  • 查询 France, Germany, Italy的国家名和人口。
1
2
3
select name,population 
from world
where name in ('France','Germany','Italy')
  • 查询面积在250000和300000之间的国家名和面积。
1
2
3
select name area
from world
where area between 250000 and 300000
2. like模糊查询和通配符
1
2
3
select 字段名
from 表名
where 字段名 like '通配符+字段'
通配符
% 表示任何字符出现任意次数
_ 表示任何字符出现一次
  • 查询国家名以C开头ia结尾的国家。
1
2
3
select name
from world
where name like 'C%ia'
  • 查询国家名中第二个字符为’t’的国家。
1
2
3
select name
from world
where name like '_t%'

‘%t%’ 查询国家名含t的。

  • 查询国家名中含有两个o且被两个字符隔开的国家名。
1
2
3
select name
from world
where name like '%o__o%'
3 多条件查询
  • 查询国家名中含有三个a且面积大于60万的国家及面积。
1
2
3
4
select name,area
from world
where name like '%a%a%a%' 
and area >= 600000
  • 查询国家名中含有三个a且面积大于60万的国家及面积，或者人口大于13亿且面积大于500万的国家及其面积。
1
2
3
4
5
6
select name,area
from world
where name like '%a%a%a%'
and area >= 600000
or population > 1300000000
and area > 5000000

and的运行优先级高于or。

  • 查询Sweden、Norway、Denmark的国家名和人口。
1
2
3
4
5
select name,population
from world
where name = 'Sweden'
or name = 'Norway'
or name = 'Denmark'

in与or在理论上是一致的，in更方便。

  • 查询面积在大于等于25万且小于30万的国家名和面积。
1
2
3
4
select name,area
from world
where area between 250000 and 300000
and area != 300000

between…and…不想要范围内的值可以用 != 来去除。

4. 练习
  • 查询南美洲所有国家的名称以及它们以百万为单位的人口数量。
1
2
3
select name,population/1000000 population_in_million
from world
where continent = 'South America'
  • 查询1980年除诺贝尔化学奖和诺贝尔医学奖外其余奖项获奖者的所有信息。
1
2
3
4
select	*
from nobel
where yr = '1980'
and subject not in ('chemistry','medicine')
  • 查询既包含有所有元音字母（a,e,i,o,u），同时国家名中没有空格的国家，最后显示他们的名字。
1
2
3
4
5
6
7
8
select name
from world
where name like '%a%'
and name like '%e%'
and name like '%i%'
and name like '%o%'
and name like '%u%'
and name not like '% %'

简化

1

  • 查询1910年以前（不含1910）诺贝尔医学奖获得者和2004年及以后诺贝尔文学奖获得者的所有信息。
1
2
3
4
5
6
select *
from nobel
where yr < 1910
and	subject = 'medicine'
or yr >= 2004
and subject = 'literature'

简化

1
2
3
4
select *
from nobel
where (subject = 'medicine' and yr < 1910)
or (subject = 'literature' and yr >=2004)

3. order by

1
2
3
4
select 字段名
from 表名
[where 表达式]
[order by 字段名 asc|desc]
排序
asc 升序
desc 降序
  • 查询姓名以Sir开头的获奖者（winner），获奖年份（yr）和科目（subject），查询结果按按照年份从近到远排序，再按照科目顺序升序排序。
1
2
3
4
select winner,yr,subject
from nobel
where winner like 'Sir%'
order by yr desc,subject asc

默认为asc排序。

  • 查询1984年所有获奖者的姓名和奖项科目。结果将诺贝尔化学奖和物理学奖排在最后，然后按照科目排序，再按照获奖者姓名排序。
1
2
3
4
select winner,subject
from nobel
where yr = 1984
order by subject in ('Chemistry','Physics'),subject,winner

subject in()，在里面的值记为1，其余为0，又因为默认为asc升序，所以1排在0后面。

4. limit

1
2
3
4
5
select 字段名
from 表名
[where 表达式]
[order by 字段名 asc|desc]
[limit [位置偏移量,] 行数]
  • 查询面积排名第三的国家。
1
2
3
4
select name
from world
order by area desc
limit 3

limit n：返回查询结果的前n行。

  • 查询人口第4到第7的国家和人口。
1
2
3
4
select name,population
from world
order by population desc
limit 3,4

limit x,n：从x+1行开始，返回n行。

limit子句在语句最后一行。

  • 查询nobel表中第100行到120行的数据。
1
2
3
select *
from nobel
limit 99,21

5. group by

1
2
3
4
5
6
select 字段名
from 表名
[where 表达式]
[group by 字段名1]
[order by 字段名 asc|desc]
[limit [位置偏移量,] 行数]

聚合函数

函数
AVG() 返回某列的均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列的和

group by 字段名规定依据哪个字段分组聚合

1. 单独使用聚合函数
  • 查询非洲的总人口数。
1
2
3
select sum(population) 人口总数
from world
where continent = 'Africa'

sum()改成avg()、max()、min()，依次计算平均人口数、最大人口数、最小人口数。

  • 计算表格的行数。
1
2
select count(*)
from world
1
2
3
4
5
6
7
select count(name),
count(continent),
count(area),
count(population),
count(gdp),
count(*)
from world

当字段出现空值时，计数会被忽略，所以count(*)最快最准确。所有函数都会忽略空值。

1
2
3
4
select sum(gdp)/195,
sum(gdp)/192,
avg(gdp)
from world

在不使用group by子句时使用聚合函数，select子句中只能写聚合函数或者包含了聚合函数的算式，否则会报错。

2. 单独使用group by
  • 对大洲进行分组。
1
2
3
select continent
from world
group by continent

结果跟distinct是一样的，但是逻辑完全不同。

group by先分区，再去重分组。

  • 查询每个大洲和大洲内的国家的数量。
1
2
3
select continent,count(name)
from world
group by continent
  • 查询2013至2015年每年每个科目获奖人数，结果按年份从大到小，人数从大到小排列。
1
2
3
4
5
select yr,subject,count(winner)
from nobel
where yr between 2013 and 2015
group by yr,subject
order by yr desc,subject,count(winner) desc

使用group by子句时，select只能使用聚合函数和group by引用过的字段，否则会报错。

3. 练习
  • 计算Estonia、Latvia、Lithuania这三个国家的总人口数
1
2
3
select sum(population)
from world
where name in ('Estonia','Latvia','Lithuania')
  • 查询每个大洲和该大洲里人口超过1千万的国家的数量
1
2
3
4
select continent,count(name)
from world
where population >= 10000000
group by continent

6. having

1
2
3
4
5
6
7
select 字段名
from 表名
[where 表达式]
[group by 字段名1]
[having 表达式]
[order by 字段名 asc|desc]
[limit [位置偏移量,] 行数]

having限定分组聚合后查询必须满足的条件

  • 查询总人口数量至少为1亿的大洲
1
2
3
4
select continent
from world
group by continent
having sum(population) >= 100000000

having子句中只能使用聚合函数和group by作为分组依据的字段。

having的表达式可以用聚合函数，where不可以。

对非聚合字段（聚合前）进行筛选用where，对聚合字段（聚合后）进行筛选用having，因为运行顺序不一样，这样效率更高。

  • 查询总人口数至少为3亿的大洲和其平均gdp，其中只有gdp高于200亿且人口数大于6000万或者gdp低于80亿且首都中含有三个a的国家的计入计算，最后按国家数从大到小排序，只显示第一行
1
2
3
4
5
6
7
8
select continent,avg(gdp)
from world
where (gdp > 20000000000 and population > 60000000)
or (gdp < 8000000000 and capital like '%a%a%a%')
group by continent
having sum(population) >= 300000000
order by count(name) desc
limit 1
  • 查询人均gdp大于3000的大洲及其人口数，仅在gdp在200亿和300亿之间的国家计入计算。
1
2
3
4
5
select continent,sum(population),sum(gdp)/sum(population)
from world
where gdp between 20000000000 and 30000000000
group by continent
having sum(gdp)/sum(population) > 3000

7. 常见函数

1. 数学函数
round

四舍五入函数

1
round(x,y)

round函数对x进行四舍五入，精确到小数点后y位。

y为负值时，保留小数点左边相应的位数为0，且不进行四舍五入。

例如

1
2
round(3.15，1)#3.2
round(14.15，-1)#10
2. 字符串函数
1. concat

连接字符串函数

1
concat(s1,s2,...)

任一参数为null时，则返回null。

例如

1
2
concat('My','SQL')#MySQL
concat('My',null,'SQL')#null
2. replace

替换函数

1
replace(s,s1,s2)

使用字符串s2代替s中所有的s1。

例如

1
replace('MySQLMySQL','SQL','sql')#MysqlMysql
3. left,right,substring

截取字符串一部分的函数

1
2
3
left(s,n)
right(s,n)
substring(s,n,len)

left函数返回字符串s最左边n个字符。

right函数返回字符串s最右边n个字符。

substring函数返回字符串s从第n个字符起，取长度为len的子字符串。

n也可以为负值，则从倒数第n个字符起取长度为len的子字符串。

没有len值，则取从第n个字符起到最后一位。

例如

1
2
3
4
5
left('abcdefg',3)#abc
right('abcdefg',3)#efg
substring('abcdefg',2,3)#bcd
substring('abcdefg',-2,3)#fg
substring('abcdefg',2)#bcdefg
3. 数据类型转换函数
cast

转换数据类型的函数

1
cast(x as type)

将一个类型的x值转换为另一个类型的值。

type参数可以填写：

char(n),date,time,datetime,decimal等转换为对应的数据类型。

4. 日期时间函数
1. year,month,day

获取年月日的函数

1
2
3
year(date)
month(date)
day(date)

date可以是年月日，也可以是年月日时分秒。

1
2
3
year('2021-08-03')#2021
month('2021-08-03')#8
day('2021-08-03')#3
2. date_add,date_sub

对指定起始时间进行加减操作

1
2
date_add(date,interval expr type)
date_sub(date,interval expr type)

date用来指定起始时间，可以是年月日，也可以是年月日时分秒。

expr用来指定从起始时间添加或减去的时间间隔。

type指示expr被解释的方式，type可以是以下值：

主要是红款内的

date_add_sub

date_add加，date_sub减。

例如

1
2
date_add('2021-08-03 23:59:59',interval 1 second)#2021-08-04 00:00:00
date_sub('2021-08-03 23:59:59',interval 2 month)#2021-06-03 23:59:59
3. datediff

计算两个日期之间间隔的天数

1
datediff(date1,date2)

例如

1
2
3
datediff('2021-06-08','2021-06-01')#7
datediff('2021-06-08 23:59:59','2021-06-01 21:00:00')#7
datediff('2021-06-01','2021-06-08')#7
4. date_format

将日期和时间格式化

1
date_format(date,fomat)

根据format指定的格式显示date值。

format格式有：

date_format

例如

1
2
date_format('2018-06-01 16:23:12','%b %d %Y %h %i %p')#Jun 01 2018 04:23PM
date_format('2018-06-01 16:23:12','%Y/%d/%m')#2018/01/06
5. 条件判断函数
1. if
1
if(expr,v1,v2)

如果表达式expr是true，返回值1，否则返回2。

例如

1
2
if(1<2,'Y','N')#Y
if(1>2,'Y','N')#N
2. case when

a.

1
case expr when v1 then r1[when v2 then r2]...[else rn]end

例如

1
case 2 when 1 then 'one'when 2 then 'two' else 'more']end#two

b.

1
case when v1 then r1 [when v2 then r2]...[else rn]end

例如

1
case when 1<0 then 'T' else 'F' end#F
6. 例题
case when
1
2
3
4
select recovered 累计治愈人数
,case when recovered = 1 then 'one' when recovered > 1 then 'more' else '0' end
from covid
where recovered >0
year,month,day
1
2
3
4
5
6
select whn 更新时间
,year(whn) 年
,month(whn) 月
,day(whn) 日
from covid
where recovered >0
date_add
1
2
3
4
select whn 更新时间
,date_add(whn,interval 2 day) 加2天
from covid
where recovered >0
round,concat
1
2
3
4
5
6
7
select confirmed
,deaths
,recovered
,recovered/confirmed
,concat(round((recovered/confirmed)*100,2),'%') 治愈率
from covid
where recovered/confirmed >0.3
replace
1
2
3
select distinct name
,replace(name,'a','替换') 替换
from covid
substring
1
2
3
4
5
select distinct name
,substring(name,2,3)
,substring(name,2)
from covid
where recovered/confirmed > 0.3
7. 练习题
  • 查询国家名称及其首都名称都以相同的字母开头的国家名及其首都，且不能包括国家名称和首都名称完全相同的情况。
1
2
3
4
select name,capital
from world
where left(name,1) = left(capital,1)
and name != capital
  • 查询首都和名称，其中首都需是国家名称的扩展
1
2
3
4
select capital,name
from world
where capital like concat('%',name,'%')
and capital != name

3. 高级语句

1. 窗口函数

1
over([partition by 字段名][order by 字段名 asc|desc])

partition by指定分区依据，order by指定排序依据。

1. 排序窗口函数
1
2
3
rank()over()
dense_rank()over()
row_number()over()
  • 查询每一年S14000021选区中所有候选人所在的团体和得票数，并对每一年中的所有候选人根据选票数的高低赋予就名次，选票数最高则为1，第二名则为2，后续以此类推，最后根据团体和年份排序。
1
2
3
4
5
select yr,party,votes
,rank()over(partition by yr order by votes desc) as posn
from ge
where constituency = 'S14000021'
order by party,yr

先按yr进行分区，再在yr内部对votes进行降序排序，rank()作用是给它们加上序号。

窗口函数不受order by和limit的影响，在having后跳出。

partition by只分区不去重。

排序窗口函数中order by是必选项

  • rank()：跳跃式排序
    • 比如数值为99，99，90，89，结果为1，1，3，4
  • dense_rank()：并列式连续型排序
    • 比如数值为99，99，90，89，结果为1，1，2，3
  • row_number()：连续型排序
    • 比如数值为99，99，90，89，结果为1，2，3，4

当数值不重复时，三种排序函数可以通用。

2. 偏移分析函数
1
2
lag(字段名,偏移量[,默认值])over([partition by 字段名] order by 字段名 asc|desc)
lead(字段名,偏移量[,默认值])over([partition by 字段名] order by 字段名 asc|desc)
  • 查询法国和德国1月每天新增确诊人数，最后显示国家名、标准日期（2020-01-27）、当天截止时间累计确诊人数、昨天截止时间累计确诊人数、每天新增确诊人数、按照截止时间排序。
1
2
3
4
5
6
7
select name 国家名,date_format(whn,'%Y-%m-%d') 截止时间,confirmed 当天截止时间累计确诊人数
,lag(confirmed,1)over(partition by name order by whn) 昨天截止时间累计确诊人数
,(confirmed-lag(confirmed,1)over(partition by name order by whn)) 每天新增确诊人数
from covid
where name in ('France','Germany')
and month(whn)=1
order by whn