「数据分析工具」玩转数据分析中常用的SQL函数

01写在前面

SQL作为数据分析师必备技能之一，无论是初级分析师还是高级分析师，SQL已经是各大公司招聘条件里的必选项，为什么SQL对于数据分析师来说如此重要呢？在回答这个问题之前，我们先搞懂以下几个问题。

第一个问题，SQL是啥？

SQL是Structured Query Language的缩写，意思是结构化查询语言，是一种在数据库管理系统（RelationalDatabase Management System, RDBMS）中查询数据，或通过RDBMS对数据库中的数据进行更改的语言。

看不懂。。。能不能说人话？好嘞，SQL就是一种对数据库中的数据表或者数据进行增、删、改、查等操作的语言。

什么是数据库？“数据库是“按照数据结构来组织、存储和管理数据的仓库”。是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合。说人话就是按照一定的组织结构存储数据的仓库。我们常见的Oracle，MySQL，SQL Server都是数据库，只是有一些是商业的数据库，一些是开源免费的而已。

第二个问题，数据分析为啥要学SQL?

其实理解了数据库和SQL的关系之后，这个问题就是句废话了。巧妇难为无米之炊，数据分析的第一步肯定是要有数据，数据哪里来？肯定要从数据库中取出来，SQL就是这样一个方便、普适的取数工具，因为几乎所有的数据库的SQL语法都是相似的，甚至现在我们做大数据分析用到的Hive SQL，其语法99%也是和SQL一样的，所以学会SQL基本上就掌握了所有数据库的取数方法。

第三个问题，到底怎么学习SQL?

你以为SQL只是一个简单的取数工具，把数据从数据库中拉出来就完事了？

1、SQL不仅能取数据，还提供了丰富的函数，可以做数据的清洗、转换等数据处理，而且SQL还能像Excel的透视表那样，可以方便地在不同的维度上对数据进行求和、计数、去重计数、求平均等操作，进而对数据进行分析，而这一切，也只需要短短的几行SQL代码就能实现。

2、如果数据很多很复杂，像Excel那样存放在不同的Sheet里，要汇总在一起进行分析，要怎么处理呢？SQL的强大之处就在于可以非常方便地将不同的数据按照一定的关联连接起来，这个关联可以是内连接inner join (找两个表的交集）、左连接left join (交集并且左表所有）、右连接（right join 交集并且右表所有）、全连接outer join（找两个表的并集），可以通过各种不同的关联条件可以实现各种不同的数据连接，最终对连接后的数据进行分析。

3、通过以上两点来看，SQL好像和Excel功能上没啥区别啊？Excel也能做数据清洗，透视表也能做求和、计数等聚合操作，Excel的Power Pivot也能实现多个表之间的连接。实际上，SQL除了以上这些功能之外，还提供了一个非常强大的功能：窗口函数，窗口函数有什么用呢？如果我们要计算每个人在特定分组下的排名、每月销售额的同比、环比、截至每天的累计销售额，这些数据分析中经常遇到的、且基础的SQL语句无法很好解决的问题，窗口函数就显示出它的威力了。所以窗口函数也是判断你是SQL基础玩家和高阶玩家的重要标准，也是数据分析面试中最喜欢考查的内容之一。

那么想入行数据分析的同学来说，怎么快速高效地掌握SQL这个数据分析的利器呢？根据前面的介绍，提升SQL水平可以按照这样的学习路径：

1、SQL基础语法：首先熟悉SQL的基础语法，对于数据分析而言，重点掌握数据查询SELECT，包括：如何使用WHERE进行数据筛选，熟练使用算数运算符（+-*/）、逻辑运算符(AND /OR/NOT)进行字段计算和条件过滤，使用SUM 、COUNT、AVG等聚合函数结合GROUP BY进行不同维度下的汇总分析，如何用HAVING子句对聚合的结果进行过滤，并使用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系列文章的压轴，会在第四篇文章中重点讲解。

另外，前面已经总结了一些数据分析中常用的Excel使用技巧，有兴趣的可以翻看之前的文章，包括：

02常用SQL函数分类

我们在使用SQL进行数据提取的过程中，由于分析需要，我们还需要对数据表中的原始数据进行特定的处理，如需要对字符串进行截取、对日期字段进行格式转换、对值字段进行算术运算等，处理成我们需要的格式。熟练地掌握常见的SQL函数可以帮助我们高效地进行数据的处理，提高分析的效率。

数据分析中常见的SQL函数大致可以分为以下几类：

1、日期时间函数（用来进行日期操作的函数）

2、字符串函数（用来进行字符串操作的函数）

3、算术函数（用来进行数值计算的函数）

4、其他重要函数（cast/coalesce/…等）

MySQL、Oracle、SQL Server和Hive SQL等不同数据库的的SQL函数略有差异，但大部分函数是通用的，为方便讲解，我们以MySQL语法为例进行讲解，部分函数涉及到Hive SQL语法，使用的时候请注意。

03日期时间函数

日期和时间类型的字段是数据表中最常见的字段，一般情况下，我们在分析的时候需要对日期、时间转换为我们需要的时间格式，下面这些函数可以方便地完成这些操作。

日期和时间类的函数很多，我们不需要全部记住，我们只需要记住以下几种常见场景下的函数即可。

1、日期和时间戳相互转换

如果需要返回系统当前的日期和时间，可以使用以下函数：

CURRENT_DATE：返回当前日期

CURRENT_TIME：返回当前时间

Now()：返回当前日期时间

当数据表中存储的是日期，需要转化为时间戳时，可以使用unix_timestamp函数，函数格式：unix_timestamp(date, format) ，表示将format格式的日期转化为时间戳。
当数据表中存储的是时间戳，需要转化为日期时，可以使用from_unixtime函数，函数格式：from_unixtime(unixtime, format)，表示将时间戳转化为format格式的日期、

-- 获取当前日期、时间 --
select current_date();-- 结果:2021-07-17
select current_time(); -- 结果:16:36:00
select now(); -- 结果:'2021-07-17 16:36:00'
-- 日期转化为时间戳 --
select unix_timestamp('2020-07-17'); -- 结果:1594915200
select unix_timestamp('2020-07-17 16:36:00'); -- 结果:1594974960
-- 时间戳转化为日期 --
select from_unixtime (1594974960); -- 结果:2020-07-17 16:36:00
select from_unixtime (1594974960,'%Y-%m-%d'); -- 结果:20200717
select from_unixtime (1594974960,'%Y%m%d');-- 结果:2020-07-17
-- 日期格式如要需要转换，也可以借助时间戳来进行转换 --
select from_unixtime(unix_timestamp('20200717'),'%Y-%m-%d');-- 结果:2020-07-17
select from_unixtime(unix_timestamp('2020-07-17'),'%Y%m%d');-- 结果:20200717

2、日期之间相互转换

如果需要把带有时间信息的日期字段转换为只有日期的格式，我们可以使用date_format()函数，函数格式date_format(str,format)，通过format将str转化为需要的日期格式；

或者使用类似的str_to_date(str,format)，将字符转换成日期需要的日期格式；

当然也可以使用cast()函数进行转化，函数格式cast(str as date)；

如果需要把日期转换成年/月/周等，可以使用year()/month()/weekofyear()等函数；

如果需要把日期转换成时/分/秒等，可以使用hour()/minute()/second()等函数；

-- 日期格式相互转换 --
select date_format('2020-07-17 16:36:00','%Y-%m-%d'); -- 结果: 2020-07-17
select str_to_date('2020-07-17 16:36:00','%Y-%m-%d'); -- 结果: 2020-07-17
select cast('2020-07-17 16:36:00' as date); -- 结果: 2020-07-17
-- 日期转换为年、月、周、时、分、秒 --
select year('2020-07-17 16:36:00'); -- 结果: 2020
select month('2020-07-17 16:36:00'); -- 结果: 7
select weekofyear('2020-07-17 16:36:00'); -- 结果: 17
select hour('2020-07-17 16:36:00'); -- 结果: 16
select minute('2020-07-17 16:36:00'); -- 结果: 36
select second('2020-07-17 16:36:00'); -- 结果: 0

3、日期之间的加减操作

通常我们需要选取某段时间的数据进行分析，这个时候就可以通过日期的加减操作来实现，常用的函数主要是datesub/date_add/datediff函数，主要语法及功能如下：

date_sub (str, INTERVAL N DAY/WEEK/MONTH)：返回开始日期strN天/周/月前的日期

date_add(str, INTERVAL N DAY/WEEK/MONTH)：返回开始日期str增加N天/周/月后的日期

datediff(enddate, startdate)：返回结束日期减去开始日期的天数

需要注意的是：

1、这里展示的date_add/date_sub的语法是MySQL的函数语法，INTERVAL不仅可以指定N天的时间间隔，还可以指定N周/月的时间间隔，如下例中所示。在其他数据库中，这2个函数的语法略有差异，需要留意。

2、datediff函数只能处理'yyyy-MM-dd'这种格式的日期，如果日期形式是'yyyyMMdd'的话，需要用上面提到的日期和日期格式之间的转换方法来做处理~

select date_add('2021-07-17', INTERVAL 7 DAY);-- 7天后的日期，结果:2021-07-24
select date_add('2021-07-17', INTERVAL 1 WEEK);-- 一周后的日期，结果:2021-07-24
select date_sub('2021-07-17', INTERVAL 30 DAY); -- 30天前的日期，结果:2021-06-17
select date_sub('2021-07-17', INTERVAL 1 MONTH); -- 1月前的日期，结果:2021-06-17
select datediff('2021-07-17','2021-07-01'); -- 结果:16

04字符串函数

除日期时间函数以外，对于数据库中的字符串字段我们也需要进行拼接、截取、替换、解析等操作，数据分析中常用的字符串函数主要如下：

concat (str1, str2, str3)：将多个字符串首尾连接后返回

concat_ws(separator,str1,str2,…)：将多个字符串按照separator首尾连接

substr(str,start,length)：截取字符串str,从start开始的length个字符

left(str,length)：截取字符串str,从最左边开始的length个字符

right(str,length)：截取字符串str,从最右边开始的length个字符

replace (str，str_before，str_after)：将字符串str中所有的str_before用str_after替换

ltrim(str)/ rtrim(str)/ trim(str) 把字符串头部/尾部/全部的空格去掉。

len(str)：返回某个字段长短

lower(str)：将字符全部小写

upper(str)：将字符全部大写

需要注意的是：

另外两个在Hive SQL中经常使用但MySQL目前还不支持的函数：

get_json_object(param1,"$.param2")：解析json字符串param1中key为param2的字符串

split(str,separator)：按照pat字符串分割str，会返回分割后的字符串数组

示例如下：

select concat('My','S','QL'); -- 结果：MySQL
select concat_ws('-','This','is','MySQL'); -- 结果：This-is-MySQL
select length('This is MySQL'); -- 结果：13
select substr('This is MySQL',1,4); -- 结果：This
select left('This is MySQL',4),right('This is MySQL',5); -- 结果：This,MySQL
select trim('  bar  '),ltrim('   barbar   ') rs1, rtrim('   barbar   ') rs2; -- 结果：bar,barbar   ,   barbar
select replace('www.mysql.com','sql','SQL'); -- 结果：www.mySQL.com
select lower('MySQL Function'); -- 结果：mysql function
select upper('MYsql Function'); -- 结果：MYSQL FUNCTION
select 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

cast（data as datatype）:将数据data转换为datatype类型

-- 将字符串转换为数字
select product_id,cast(product_id as signed) as product_id_int from product;

结果如下：

2) coalesce

coalesce(data1,data2,data3……):按照由前到后的顺序返回第一个非空值

-- 取第一个非空值
select coalesce(NULL,NULL,'first not null');

结果如下：

3) between

between A and B:筛选位于A和B之间的数据，包含A和B

-- 筛选出售价在5000-10000的销售记录
select * from product where sale_price between 5000 and 10000 ;

结果如下：

4) in/not in

in/not in (‘cond1’,’cond2’,’cond3’):筛选某个字段满足候选条件中的任意一个的记录

-- 筛选出手机、电脑的销售记录
select * from product where product_category in ('手机','电脑');

结果如下：

5) is null/is not null

col is null /is not null：筛选某个字段col为空/不为空的记录

-- 筛选出商品名称不为空的用户
select * from product where product_name is not null;

结果如下：

6) like

like按照模糊匹配，筛选某个字段符合某种匹配规则的记录

-- 筛选出包含"电视"的销售记录,%代表多个字符，更多用法参考正则表达式
select * from product where product_name like '%电视%';

结果如下：

7) case when

case data when condition1 then result1 else result2 end:对data进行判断，满足条件condition1就返回result1，反之返回result2

-- 判断售价是否高于5000
select
sale_price,
case
when sale_price between 0 and 5000 then '<=5000'< span="">
else '>5000'
end as is_above_5000
from product;

结果如下：

8) 一行/多行转换

多行合并为一行

对于product表中的销售记录，由于一个商品在每天都有可能会被销售，所以一个商品会存在着多个售价的记录，如下图所示：

如果我们想一个商品的售价汇总成一条记录，将多行转换成一行，就可以进行如下操作：

-- 多行合并一行，一个商品所有的售价合并成一行，HiveSQL 语法
create table col_row_convert as  -- 供后面一行转多行使用
select product_id,product_name,
concat_ws(',',collect_list(sale_price)) as sale_price_all
from product
group by product_id,product_name;
select * from col_row_convert;

结果如下：

需要注意的是：代码中的2个重要函数：collect_list和concat_ws是Hive SQL中的语法，MySQL暂不支持，前者负责将同一个product_id,product_name的汇总为一个list，后者负责将list中的各个元素通过“，”进行连接，最终一个产品product_id,product_name形成了一条记录，与collect_list类似的还有collect_set函数，两者功能基本一致，只是后者会对汇总后的数据进行去重。

一行拆分为多行

相反地，如果我们已经有一个产品的一条汇总的售价记录，需要把一行记录拆分为多行，也就是进行上面步骤的逆操作，代码如下：

-- 一行拆成多行，上面步骤的逆操作，HiveSQL 语法
select product_id, product_name, sale_price
from col_row_convert a
lateral view explode(split(sale_price_all,',')) b AS sale_price;

结果如下：

同样需要注意的是，这里的2个重要函数：split 和lateral view explode也是Hive SQL中的函数，MySQL暂不支持，前者是将上一步形成的汇总字符按照“，”进行分割，后者是将一行分为多行。

以上就是数据分析工具—SQL常用函数部分的内容，部分数据分析工具请翻看历史文章，更多数据分析工具的文章持续更新中，敬请期待，如果觉得不错，也欢迎分享、点赞和收藏~