01
写在前面
SQL是数据分析师必备技能之一。 无论是初级分析师还是高级分析师，SQL都已经是各大公司的招聘条件中的必备。 为什么 SQL 对于数据分析师如此重要？ 在回答这个问题之前，我们先来了解以下几个问题。
第一个问题，什么是SQL？
SQL是Query的缩写，意思是结构化查询语言。 它是一种用于查询数据库管理系统 (RDBMS) 中的数据或通过 RDBMS 更改数据库中的数据的语言。
无法阅读。 。 。 你会说人类语言吗？ 那么，SQL是一种用于对数据库中的数据表或数据进行增、删、改、查的语言。
什么是数据库？ 数据库是“按照数据结构组织、存储和管理数据的仓库”，是计算机中长期存储的、有组织的、可共享的、统一管理的大量数据的集合从人的角度来说，它是基于一定的仓库来存储数据的一种组织结构，常用的，MySQL、SQL都是数据库，但有些是商业数据库，有些是开源免费的。
第二个问题，为什么数据分析需要学习SQL？
其实了解了数据库和SQL的关系后，这个问题就是废话了。 巧妇难为无米之炊。 数据分析的第一步必须是有数据。 数据从哪里来？ 必须从数据库中检索它。 SQL之所以是这样一种方便且通用的访问工具，是因为几乎所有数据库的SQL语法都是相似的。 即使现在，我们用于大数据分析的 Hive SQL 语法也已经达到了 99%。 %也和SQL一样，所以学习SQL基本上就掌握了所有的数据库访问方法。
第三个问题，如何学习SQL？
你以为SQL只是一个简单的访问工具，只要把数据从数据库中拉出来就可以了？
1. SQL不仅可以获取数据，还提供了丰富的函数，可以用于数据清洗、转换等数据处理，并且SQL还可以像Excel的数据透视表一样方便地对不同维度的数据进行求和、计数、去重计数、平均等操作，然后对数据进行分析，这一切只需几行SQL代码即可实现。
2、如果有很多复杂的数据，像Excel一样存储在不同的工作表中，需要聚合分析，我们应该如何处理？ SQL的强大之处在于可以非常方便地将不同的数据按照一定的关联性连接起来。 这种关联可以是内连接（求两个表的交集）、左连接左连接（与左表所有的交集）、右连接（右连接交集与右表所有）、全连接外连接（求两个表的并集） ），通过各种关联条件可以实现各种数据连接，最后将连接后的数据进行分析。
3、从以上两点来看，SQL似乎和Excel在功能上没有什么区别？ Excel还可以做数据清洗，数据透视表还可以进行求和、计数等聚合操作，Excel的Power Pivot还可以实现多个表之间的连接。 其实除了上述功能之外，SQL还提供了一个非常强大的功能：窗口函数，窗口函数有什么用呢？ 如果我们要计算每个人在特定群体下的排名，月销售额的同比、环比、累计到每一天的销售额，这些问题是数据分析中经常遇到的问题，无法解决。通过基本的SQL语句就可以很好的解决。 窗口函数显示了它的强大功能。 因此，窗口函数也是判断你是基础SQL玩家还是高级玩家的重要标准，也是数据分析面试中最喜欢考察的东西之一。
那么对于想要进入数据分析领域的同学来说，如何快速高效的掌握SQL这个数据分析的利器呢？ 根据前面的介绍，提高SQL水平可以遵循以下学习路径：
1、SQL基本语法：首先熟悉SQL基本语法。 对于数据分析，重点掌握数据查询，包括：如何使用WHERE过滤数据、熟练使用算术运算符（+-*/）、逻辑运算符（AND/OR/NOT）进行字段计算和条件过滤、使用SUM 、COUNT、AVG等聚合函数结合GROUP BY进行不同维度的汇总分析，如何使用子句来过滤聚合结果，并使用ORDER BY最终对查询结果进行排序。 这部分最重要的一点是：明确SQL语句的执行顺序和书写顺序的区别，这对于理解SQL的执行过程很有帮助。 作为SQL系列的第一篇内容，我们将在本文中重点介绍它。
2、SQL常用函数：在掌握SQL基本语法的基础上，下一步是掌握数据分析中的一些常用函数，包括但不限于：日期函数、字符串函数、数值运算函数等。熟练使用这些函数可以帮助我们高效地进行数据清洗、转换等数据处理。 我们将在 SQL 系列的第二篇文章中重点讨论这一部分。
3、SQL子查询与表连接：在前面的基础上，我们还需要掌握子查询与多个表之间的连接。 在数据分析工作中，经常需要对多个相关表进行分析。 对于简单的查询，我们可以使用嵌套子查询来解决。 但是，如果涉及的表较多，并且表之间的关系比较复杂，我们就需要使用表连接的方式将表按照一定的关系连接在一起。 常见的连接类型有内连接：INNER JOIN、左连接：LEFT JOIN、右连接：RIGHT JOIN、全连接：FULL JOIN。 我们将在 SQL 系列的第三篇文章中重点讨论这一部分。
4、SQL窗口函数：前三部分已经基本涵盖了数据分析对于SQL的基本要求，但是在实际工作中，我们有一些复杂而常见的场景是用基本语法无法很好解决的，比如：每一年- 每月销售额的同比/环比、每天的累计销售额、每个产品在所属品类下的销售额排名，窗口函数就是为这些场景而生的。 如果能熟练掌握窗口函数，数据分析基本上不会遇到什么SQL问题。 这部分内容是SQL系列文章的结局，将在第四篇文章中进行讲解。
02
常用SQL函数分类
在使用SQL进行数据提取的过程中，由于分析的需要，我们还需要对数据表中的原始数据进行特定的处理，比如截取字符串、对日期字段进行格式转换、对值字段进行算术操作等等，处理成我们需要的格式。 熟练掌握常用SQL函数可以帮助我们高效处理数据，提高分析效率。
数据分析中常见的SQL函数大致可以分为以下几类：
1.日期和时间函数（日期运算的函数）
2.字符串函数（字符串操作的函数）
3.算术函数（用于进行数值计算的函数）
4.其他重要函数（cast//...等）
MySQL、SQL、Hive SQL等不同数据库的SQL函数略有不同，但大部分函数是通用的。 为了方便说明，我们以MySQL语法为例。 某些函数涉及 Hive SQL 语法。 使用时请注意。
03
日期时间函数
日期和时间类型的字段是数据表中最常见的字段。 一般我们在分析时需要将日期和时间转换为我们需要的时间格式。 下面的函数可以轻松完成这些操作。
日期和时间的函数有很多，我们不需要全部记住，只需要记住以下常见场景中的函数即可。
1. 日期和时间戳相互转换
如果需要返回系统当前的日期和时间，可以使用以下函数：
：返回当前日期
：返回当前时间
Now()：返回当前日期和时间
当日期存储在数据表中，需要转换为时间戳时，可以使用函数。 函数:(date,)表示将中的日期转换为时间戳。
当时间戳存储在数据表中，需要转换为日期时，可以使用函数。 函数格式：(,)表示将时间戳转换为格式为日期，
-- 获取当前日期、时间 --select current_date();-- 结果:2021-07-17select current_time(); -- 结果:16:36:00select now(); -- 结果:'2021-07-17 16:36:00'-- 日期转化为时间戳 --select unix_timestamp('2020-07-17'); -- 结果:1594915200select unix_timestamp('2020-07-17 16:36:00'); -- 结果:1594974960-- 时间戳转化为日期 --select from_unixtime (1594974960); -- 结果:2020-07-17 16:36:00select from_unixtime (1594974960,'%Y-%m-%d'); -- 结果:20200717select from_unixtime (1594974960,'%Y%m%d');-- 结果:2020-07-17-- 日期格式如要需要转换，也可以借助时间戳来进行转换 --select from_unixtime(unix_timestamp('20200717'),'%Y-%m-%d');-- 结果:2020-07-17select from_unixtime(unix_timestamp('2020-07-17'),'%Y%m%d');-- 结果:20200717
2. 日期之间的转换
如果需要将带有时间信息的日期字段转换为仅日期格式，我们可以使用()函数，函数(str,)，通过将str转换为需要的日期格式；
或者使用类似的(str,)将字符转换为日期所需的日期格式；
当然，也可以使用cast()函数进行转换，函数格式为cast(str as date);
如果需要将日期转换为年/月/周等，可以使用year()/month()/()等函数；
如果需要将日期转换为小时/分钟/秒等，可以使用hour()/()/()等函数；
-- 日期格式相互转换 --select date_format('2020-07-17 16:36:00','%Y-%m-%d'); -- 结果: 2020-07-17select str_to_date('2020-07-17 16:36:00','%Y-%m-%d'); -- 结果: 2020-07-17select cast('2020-07-17 16:36:00' as date); -- 结果: 2020-07-17-- 日期转换为年、月、周、时、分、秒 --select year('2020-07-17 16:36:00'); -- 结果: 2020select month('2020-07-17 16:36:00'); -- 结果: 7select weekofyear('2020-07-17 16:36:00'); -- 结果: 17select hour('2020-07-17 16:36:00'); -- 结果: 16select minute('2020-07-17 16:36:00'); -- 结果: 36select second('2020-07-17 16:36:00'); -- 结果: 0
3. 日期之间的加减法
通常我们需要选取某一段时间的数据进行分析。 这时可以通过日期的加减来实现。 常用的函数主要是 // 函数。 主要语法和功能如下：
(str, N DAY/WEEK/MONTH)：返回开始日期strN天/周/月之前的日期
(str, N DAY/WEEK/MONTH)：返回开始日期str增加N天/周/月后的日期
(, )：返回结束日期减去开始日期的天数
必须要注意的是：
1.这里显示的/的语法是MySQL的函数语法。 您不仅可以指定N天的时间间隔，还可以指定N周/月的时间间隔，如下例所示。 在其他数据库中，这两个函数的语法略有不同，需要注意。
2. 该函数只能处理'yyyy-MM-dd'格式的日期。 如果日期格式为''，则需要使用上面提到的日期与日期格式之间的转换方法来处理~
select date_add('2021-07-17', INTERVAL 7 DAY);-- 7天后的日期，结果:2021-07-24select date_add('2021-07-17', INTERVAL 1 WEEK);-- 一周后的日期，结果:2021-07-24select date_sub('2021-07-17', INTERVAL 30 DAY); -- 30天前的日期，结果:2021-06-17select date_sub('2021-07-17', INTERVAL 1 MONTH); -- 1月前的日期，结果:2021-06-17select datediff('2021-07-17','2021-07-01'); -- 结果:16
04
字符串函数
除了日期和时间函数之外，我们还需要对数据库中的字符串字段进行拼接、截取、替换、解析等操作。 数据分析中常用的字符串函数主要有以下几种：
(str1, str2, str3)：将多个字符串首尾相连后返回
(,str1,str2,…)：按照首尾连接多个字符串
(str, start,)：截取字符串str，从start开始的字符
left(str,)：截取字符串str，从最左边开始的字符
right(str,)：截取字符串str，从最右边开始的字符
(str,,)：将字符串str中的所有内容替换为
ltrim(str)/ rtrim(str)/ trim(str) 删除字符串开头/结尾/所有的空格。
len(str)：返回字段的长度
lower(str)：小写所有字符
upper(str)：将所有字符大写
必须要注意的是：
Hive SQL 中经常使用但 MySQL 目前不支持的另外两个函数：
(,"$.")：解析json字符串中的key的字符串
split(str,)：根据pat字符串分割str，返回分割后的字符串数组
示例如下：
select concat('My','S','QL'); -- 结果：MySQLselect concat_ws('-','This','is','MySQL'); -- 结果：This-is-MySQLselect length('This is MySQL'); -- 结果：13select substr('This is MySQL',1,4); -- 结果：Thisselect left('This is MySQL',4),right('This is MySQL',5); -- 结果：This,MySQLselect trim(' bar '),ltrim(' barbar ') rs1, rtrim(' barbar ') rs2; -- 结果：bar,barbar , barbarselect replace('www.mysql.com','sql','SQL'); -- 结果：www.mySQL.comselect lower('MySQL Function'); -- 结果：mysql functionselect upper('MYsql Function'); -- 结果：MYSQL FUNCTIONselect get_json_object({"name":"李小虎","gender":"男","age":"25"},"$.name"); -- 结果：李小虎select split('This-is-MySQL','-'); -- 结果：[This,is,MySQL]
05
算术函数
在数据分析中，有时需要对数据表中的数值字段进行一定的算术处理。 常见的算术函数有以下三种：
ABS(num)：取绝对值
MOD(num1,num2)：取余数，接受两个参数，第一个是值，第二个是除数
ROUND(num,N)：保留小数位数，接受两个参数，第一个是值，第二个是保留的小数位数。
一般来说，数值计算函数用得不多，但ROUND在数据分析中还是很实用的。
示例如下：
SELECT ABS(-7.21); -- 取绝对值，结果：7.21;SELECT MOD(23,12); -- 23除以12的余数，结果：11;SELECT ROUND(99.865,1); -- 保留一位小数 结果：99.9;
06
其他重要语法
在实际的数据分析中，除了上面用到的函数外，还需要一些比较常用的函数和谓词，但有些在Hive SQL中是可用的，但在MySQL中不支持。 主要有以下几种：
1) 演员
cast(data)：将数据data转换为类型
-- 将字符串转换为数字select product_id,cast(product_id as signed) as product_id_int from product;
结果如下：
2）
(data1, data2, data3...)：按从前到后的顺序返回第一个非空值
-- 取第一个非空值select coalesce(NULL,NULL,'first not null');
结果如下：
3）
A和B：过滤A和B之间的数据，包括A和B
-- 筛选出售价在5000-10000的销售记录select * from product where sale_price between 5000 and 10000 ;
结果如下：
4）在/不在
in/not in ('cond1','cond2','cond3')：过滤某个字段满足任意候选条件的记录
-- 筛选出手机、电脑的销售记录select * from product where product_category in ('手机','电脑');
结果如下：
5) 为空/不为空
col is null/is not null：过滤字段col为空/不空的记录
-- 筛选出商品名称不为空的用户select * from product where product_name is not null;
结果如下：
6) 喜欢
like 根据模糊匹配，过滤某个字段满足某种匹配规则的记录
-- 筛选出包含"电视"的销售记录,%代表多个字符，更多用法参考正则表达式select * from product where product_name like '%电视%';
结果如下：
7) 情况
n1 then end：判断数据，满足条件则返回，否则返回
-- 判断售价是否高于5000selectsale_price,casewhen sale_price between 0 and 5000 then '<=5000'< span="">else '>5000'end as is_above_5000from product;
结果如下：
8) 一行/多行转换
将多行合并为一行
对于表中的销售记录，由于每天都可能有一个产品被售出，因此一个产品会有多条销售记录，如下图所示：
如果我们想将一种产品的销售价格聚合到一条记录中，并将多行转换为一行，我们可以执行以下操作：
-- 多行合并一行，一个商品所有的售价合并成一行，HiveSQL 语法create table col_row_convert as -- 供后面一行转多行使用select product_id,product_name,concat_ws(',',collect_list(sale_price)) as sale_price_allfrom productgroup by product_id,product_name;select * from col_row_convert;
结果如下：
需要注意的是，代码中有两个重要的函数：and是Hive SQL中的语法，目前MySQL还不支持。 前者负责将相同的 , 汇总成一个列表，后者负责将列表中的每个元素通过“,”连接起来，最后一个产品形成一条记录，也有类似的功能。 两者的功能基本相同，只不过后者会对汇总后的数据进行去重处理。
将一行拆分为多行
相反，如果我们已经有某个产品的汇总价格记录，则需要将一行记录拆分为多行，即执行上述步骤的逆操作，代码如下：
-- 一行拆成多行，上面步骤的逆操作，HiveSQL 语法select product_id, product_name, sale_pricefrom col_row_convert alateral view explode(split(sale_price_all,',')) b AS sale_price;
结果如下：
还需要注意的是，这里的两个重要函数：split和view也是Hive SQL中的函数，目前MySQL还不支持。 前者将上一步形成的摘要字符按照“,”进行拆分，后者将一行拆分为多行。
