十分钟搞懂 SQL 数据分析

type

status

date

slug

summary

category

icon

password

是否阅读

Origin

URL

本文重点在于快速让读者对 “SQL 数据分析” 有全面认识。其次，详解 SQL 部分关键词或函数，并用实操后的结果向大家展示 SQL 数据分析。

下面的框架图展示了本文的行文顺序，第（一）部分介绍 SQL 基本概念，第（二）到（十二）部分，讲解 SQL 关键词和业务场景。文章末尾处展现了完整的 “SQL 数据分析” 框架图。

（一）基本介绍

1.1 SQL 是啥?

一门查询数据库的编程语言。

1.2 SQL 能操作谁？

SQL 可操作关系型数据库，但不能操作非关系型数据库。关系型数据库可简单理解为由行和列组成的二维表。关系型数据库分为开源和收费两类，开源的数据库系统有 MySQL、PostgreSQL, 收费有 Oracle、SQL Server, DB2。以上系统尽管有不同的 SQL 语法，但大同小异，学会一种即可，本文选用 MySQL。

1.3 SQL 语句

通过 SQL 语句，可在数据库中查到想要的数据。SQL 语句由三部分组成，关键字、表名、列名。

(二) SELECT 语句

2.1 “*” 号代表所有列

“” 号能选出表中所有的列。例如代码：SELECT FROM Table，此代码会选出 Table 表中所有的列。

2.2 DISTINCT 去重

DISTINCT 关键词，能对表中一列或多列信息进行去重。以下图为例，DISTINCT 关键字对列名 “product_type” 去重，列中两个 “办公” 会变成去重后的一个 “办公”，“服装” 和“厨房”也有同样去重效果。

2.3 ORDER BY 排序

ORDER BY 的作用是排序，可对一列或多列排序。下图展示了对两列同时进行排序的例子。

2.3.1 两列排序

代码：“ORDER BY department,salary”，作用是先对 department 的各个部门排序，其次，基于已排序部门的基础上再对 salary 进行排序（默认升序）。

首先，对部门 finance ，marketing，technology 按照 26 个英文字母的升序进行排序，部门依次被排为 finance 第一，marketing 第二，technology 第三。其次，当同一部门出现多个员工时，再按照 salary 大小进行升序排序，比如，部门的 Joe、Sam、Jason 被分别排为第一，第二，第三。详情见下图。

2.3.2 业务场景

高铁买票时，会用到价格排序，出发时间、耗时长短；淘宝血拼时，会用到价格排序；大众点评找美食时，会用到距离优先、好评优先等。顾客在使用这些功能时，后台数据库用到的就是简单但强大的 “ORDER BY” 语句。

2.4LIMIT 限制行数

LIMIT 关键词可限制查询行数。LIMIT 用法有二。用法一，返回表的前 X 行；用法二，返回表的特定行。

2.4.1 返回前 X 行

举例， LIMIT 5, 返回表的前 5 行。

2.4.2 返回特定行

举例， LIMIT 5，10，从第 6 行开始，返回 10 行。

2.5 语句执行顺序

数据库执行 SQL 代码的顺序不是按照抒写的顺序来执行，而是按照特点关键字的顺序来执行，执行顺序为：FROM->WHERE->GROUP BY ->HAVING ->SELECT ->ORDER

(三) 过滤数据

3.1 WHERE 过滤

3.1.1 大于小于

WHERE 语句能选出特定范围的数据，分为三类：①大于，②小于，③大于且小于。以字段 “price” 为例对此三类进行解释。

WHERE price > 1000 能选出价格大于 1000 的数据；

WHERE price < 200 能选出价格小于 200 的数据；

WHERE 700 < price< 1000 能选出价格大于 700 且小于 1000 的数据。

实操效果见下图。

3.1.2 等于

WHERE 语句能选出等于特定数值或文字的数据，比如，WHERE price = 388, 可选出价格为 388 的数据；再比如，WHERE hotel =‘希尔顿’能选出酒店为 “希尔顿” 的数据。见下图。

3.1.3 业务场景

京东、淘宝、天猫、携程的官网和 APP，均提供 “价格区间” 和“品牌筛选”的功能，背后的代码就是 WHERE 语句。

3.2 BETWEEN 过滤

WHERE BETWEEN 也能筛出特定区间的数值，例如， WHERE price BETWEEN700 AND 1000, 能筛出价格从 700 到 1000 的数据, 功能类似于 “WHERE 大于且小于”。

3.3 IN 或 NOT IN 过滤

WHERE IN 也可筛选数据，例如字段 “capital_flows”（资金流向的意思）的数值可以是各个行业，WHERE IN 语句能把“capital_flows” 的行业限定在‘房地产’和‘证券’行业，代码见下图。

业务场景：近些年 “房地产” 和“证券”成为银保监会重点关注的行业，在银保监会大量的 EAST 报送数据中（EAST 系统可简单理解为检查各家银行数据的系统），如何迅速找到资金流到 “房地产” 和“证券”的数据呢？当属 WHERE IN 语句。

3.4 NULL 过滤

WHERE IS NULL 语句能从数据中快速筛选出数据为空值的记录，代码和实操效果见下图。

业务场景：银保监会要求各家银行提供的 EAST 报送数据不能为空值。例如，客户的 “社会信用代码”，“贷款流向行业” 不能为空。WHERE IS NULL 语句能快速找出数据为空值的记录。

3.4 通配符过滤

通配符（“%” 或 “_”）能搭配数据中的任何字符，达到筛选数据的效果。

3.4.1 % 百分号

% 百分号代表 0 到多个任意字符。举例，代码 LIKE “天安门 %” 会找到只有 “天安门” 这三个字和 “天安门” 后面有其他文字的数据，详情见 3.4.2 图。

3.4.2 _下划线

_下划线能代表 1 个任意字符。LIKE ‘天安门_’ 会找到 “天安门” 后面有一个文字的数据，比如“天安门东”。详情见下图。

3.5 正则表达式

正则表达式是由各种符号组成的搜索模式，相比 “通配符” 正则表达式更加精确。下列符号能组成各种正则表达式，但种类繁多且抽象，大家感受一下即可。

3.6 多条件过滤

WHERE 语句中，AND 和 OR 能把两个或多个条件结合起来。AND: 如果第一个条件和第二个条件都成立，则显示一条记录；OR 如果第一个条件和第二个条件只要有一个成立，则显示一条记录。

（四）处理字段

4.1 拼接字段

CONCAT 函数能把多个字段拼接起来，例如下图，concat 函数能把 company 和 country 两个字段拼接在一起，具体代码见文章末尾处的框架图。

4.2 计算字段

字段之间可做加减乘除运算，例如下图展示了，price 字段与 quantity 字段相乘得出新的字段，计算结果如下图。

（五）处理数据

处理数据分为三类：处理文本，处理时间，处理数值。对应的三类函数分别为 “文本处理函数”，“时间处理函数 “，“数值处理函数”。

5.1 文本处理函数

以下是对各个文本处理函数的总结，第一列和第四列是分别是函数名称和相应的解释；第二列和第三列分别是例子和返回的结果。

5.2 时间处理函数

下图是对时间处理函数的总结和解释。

5.3 数值处理函数

下图是对数值处理函数的总结和说明。

（六）聚合函数

常用的聚合函数有以下五种，详情见下图。

“计数函数”, 例如 COUNT(price)，对 price 列的行数进行计数。

“平均值函数”，例如 AVG(price), 对 price 列求平均值。

“求和函数”，例如 SUM(price), 对 price 列中所有值进行求和。

“最大值函数”，例如 MAX(price)，找出 price 列中的最大值。

“最小值函数”，例如 MIN（price），找出 price 列中的最小值。

（七）分组数据

GROUP BY 关键词可对数据进行分组，HAVING 可对分组后的数据进行过滤。举例，GROUP BY 将列 “bedrooms” 中数量为 2、3、4 的数据各分为一组；HAVING 又能把数量大于 2 的分组后数据筛选出来。若觉得抽象，请参考下图。

（八）CASE WHEN 搜索

CASE WHEN 搜索语句是一种条件表达式，可实现对定量数据的定性描述。举例，可将两个卧室描述为 “小”，三个卧室描述为 “适中”，四个卧室描述为 “大”, 请参考下图。

（九）窗口函数

窗口函数可分为 “聚合函数的窗口函数” 和“专用窗口函数”。基本语法：<窗口函数> OVER ( [PARTITION BY <>]ORDER BY <>)

9.1 聚合函数的窗口函数

实操效果和关键词详见下图。

9.2 专用窗口函数

RANK 函数会出现相同排序，和 “跳序”

DENSE_RANK 函数，会出现相同排序，但无 “跳序”

ROW_NUMBER 函数，不存在重复的排序。

（十）子查询

子查询是嵌套在主查询中的查询，可以简单理解为在 SELECT 语句中又嵌套一个 SELECT 语句。理论上，子查询可嵌套在各个位置，但在实际工作中，主要嵌套在 SELECT、WHERE、HAVING 等关键词后面。

（十一）多表连接

一张表往往难以查询到需要的所有字段，多表连接可以同时查询多个表的字段，把不同表中的字段进行横向拼接，以满足在多表中各个字段的查询。多表连接主要分为内连接、左连接、右连接、全连接、交叉连接。

以左连接为例，Product 表为左表，连接 Price 表，关联字段为 product_id。返回结果：保留左表 product 表的所有数据，并保留 Price 表中 product_id 为 1 和 3 的数据，详情见下图。

（十二）组合查询

组合查询可实现多个 SELECT 语句的同时查询，并将查询结果作为单个结果集返回。关键词 “UNION” 可对结果集进行去重，“UNION ALL”不能对结果集进行去重。

（十三）结束语及完整框架图

本文已对 “SQL 数据分析” 进行梳理，并对某些关键词和语句进行了详解。笔者梳理出 “SQL 数据分析” 的完整框架图送给大家，希望对你们有帮助。

团队介绍：我们是毕马威旗下的专业数据挖掘团队，微信公众号（kpmgbigdata）每周六晚 8 点准时推送一篇原创数据科学文章。我们的作品都由项目经验丰富的博士或资深顾问精心准备，分享结合实际业务的理论应用和心得体会。