举例详解数据分析会用到哪些SQL技能？

本文首发个人知乎https://zhuanlan.zhihu.com/p/137328389和个人微信公众号 呆呆玩数据

【背景介绍】

在一家知名电商企业的BI部门实习四个多月，岗位为数据分析。日常工作中打交道最多的就是SQL和EXCEL，在实习之前SQL技能只会简单的增删改查语句，第一周实习经理甩了一份业务常见绩效取数的SQL代码给我，三四百行的代码看得头昏眼乱，惊讶到简简单单的SQL也能写出这么长的代码。历经四个多月的取数训练，对SQL的使用技能噌噌噌上了好几个台阶。本文整理一份数据分析中常用的SQL技能，学会这些技能，就能轻松在企业庞大的数据库中随心所欲地取数并计算指标了。

SQL是一种操纵数据库的语言，主要包括增删改查，对于数据分析师来说，一般只要查找数据的，不能对数据库进行增删改，毕竟公司怕你删库跑路，哈哈哈！查找数据代码的语法格式如下：

SELECT columns_name       --查找一列或多列，多列之间用逗号隔开
FROM Table                --目标表
WHERE condition           --过滤条件
GROUP BY columns_name     --按列值分组，可以1个或多个列
HAVING condition          --分组后的筛选条件，HAVING与WHERE区别在于前者表达式中可包含函数
ORDER BY columns_name     --按列排序
LIMIT start, row_count    --对结果进行限定，start表示从哪行开始，row_count表示结果行数

【基础用法】

【举个栗子_1】有一张学生表student，包括学生id，姓名，年龄，班级，分数字段，下面用SQL来查找数据吧。

student表

--查找id,姓名，成绩列
SELECT stu_id,stu_name,grade
FROM student

--查找成绩大于等于70分的学生id,姓名，成绩列
SELECT stu_id,stu_name,grade
FROM student
WHERE grade >= 70

--计算班级人数，平均成绩，最高成绩，最低成绩
SELECT class, avg(grade), max(grade), min(grade) as min_grade, count(stu_id)
FROM student
GROUP BY class

计算班级平均成绩用到了GROUP BY语句，同样的class被分为一组，当你使用分组语句时，SELECT语句后面只能出现分组字段和聚合函数，比如这里SELECT语句后面要是有stu_id就会报错。还有这里用到了as 重命名功能，一般使用函数后会重新命名，不然得到的列默认为函数表达式。

--Having语句删选班级平均分大于60分的班级
SELECT class, avg(grade), max(grade), min(grade) as min_grade, count(stu_id)
FROM student
GROUP BY class
HAVING avg(grade) > 60

--like进行模糊匹配
where name like '陈%'    --找出姓陈的人
where name like '%铭%'    --找出名字中有铭字的人
where name like '陈_'    --找出姓陈且名字为两个字的人

like进行模糊匹配时，%可以匹配任意多个任意字符，_匹配任意一个字符。

【表连接】

表连接是指两张表可以通过相同的关键字段进行连接，包括内连接和外连接，而外连接又包括左外连接、右外连接和全外连接。1）内连接指结果表只包含同时存在两张表中的连接字段；2）左外连接是指既包含内连接的连接字段还包含左表未连接的字段；3）右外连接是指既包含内连接的连接字段还包含右表未连接的字段；4）全外连接是包含两张表的所有连接字段。下面用韦恩图表示这几种连接的不同，非常直观。

从左到右，依次是内连接，左外连接，右外连接，全外连接

【举个栗子2】表连接，两张表student，student2，stu_id为连接字段

--内连接,*是通配符,取出表所有的变量
SELECT student.*,student2.*         
FROM student 
INNER JOIN student2 ON (student.stu_id = student2.stu_id)

内连接

--左外连接
SELECT student.*,student2.*         
FROM student 
LEFT OUTER JOIN student2 ON (student.stu_id = student2.stu_id)

左外连接

--右外连接
SELECT student.*,student2.*         
FROM student 
RIGHT OUTER JOIN student2 ON (student.stu_id = student2.stu_id)

右外连接

--全外连接
SELECT student.*,student2.*         
FROM student 
FULL OUTER JOIN student2 ON (student.stu_id = student2.stu_id)

全外连接

【子查询】

子查询是指把一个查询的结果放到另一个查询里面使用，功能十分丰富，既可以放在FROM语句中作为临时表供另一个查询使用，也可以放在WHERE子句后面进行过滤。放在WHERE子句后面的子查询的返回结果可以是单行也可以是多行，单行子查询一般与>,<,=,<=,>=等比较符一起使用，而多行子查询可与IN（等于子查询返回结果的任意一个），any（比较符满足子查询中的任意一个情况）, all（比较符满足子查询中的所有情况）等配套使用，下面举例说明。

【举个栗子_3】student表中查询grade大于平均分（73.33）的学生id，分数

--WHERE单行子查询
SELECT stu_id, grade
FROM student
WHERE grade > (select avg(grade) from student)     

WHERE子查询

【举个栗子_4】student表中查询属于班级平均分最高班级的学生信息

--WHERE 和 FROM  多行子查询IN
SELECT stu_id
FROM student as a
WHERE class IN 
(SELECT class 
FROM 
(SELECT class, avg(grade) as avg_grade
FROM student
GROUP BY class
ORDER BY avg(grade) DESC
limit 1) sub_1) 

上面这个嵌套子查询稍微有一点点复杂，我一共用了两层子查询，首选最里层的子查询计算了每个班级的平均分。然后用ORDER BY 子句按班级平均分倒序排列（最高分排第一个），然后LIMIT 1限制输出1行数据，得到了最高分的班级行，接着把这层子查询结果放到外面一层子查询的FROM语句后面，第二层子查询得到class，最后外查询的WHERE 子句判断class是不是等于第二层子查询中的某一个。

【举个栗子_5】student表中最高分

SELECT stu_id,grade
FROM student as a
WHERE grade >= all (SELECT distinct grade FROM student) 

【举个栗子_6】student表中非最低分的学生

SELECT stu_id,grade
FROM student as a
WHERE grade > any (SELECT distinct grade FROM student) 

只有最低分的学生成绩不大于任何一个人的分数，被排除在外。

【case when】

case when 语句是SQL中的一个非常重要的功能，可以完成很多复杂的计算，相当于一个表达式，可以放在任何可放表达式的地方。语法 case when 条件 then 结果 when 条件 then 结果 else end。else可不加，是缺省条件下的值，如果不加，有缺省情况则为NULL。CASE WHEN还可以和GROUP BY 语句搭配使用，用在sum,count,max等聚合函数内部。

【举个栗子_7】student表中生成一个新字段成绩等级，小于60分不及格，大于等于60并小于80及格，大于等于80并小于90良好，大于等于90优秀

SELECT a.*,
       (case when grade < 60 then '不及格' when grade >=60 and grade < 80 then '及格'
        when grade >=80 and grade <90 then '良好' when grade >=90 then '优秀' end) as grade_level
       	
FROM student a

 

【举个栗子_8】计算每个班级中，及格和不及格人数

SELECT class
       ,count(distinct case when grade < 60 then stu_id end) as failed_num
       ,count(distinct case when grade >= 60 then stu_id end) as pass_num
FROM student
GROUP BY class

【组合查询】

SQL中可用union或union all将多个查询结果拼接起来，两者的区别是union会对结果重新排序，而union all不会，所以一般为了节省内存在不需要排序的情况下用union all更好。

【举个栗子_9】组合查询

SELECT a.*
FROM student AS a
WHERE class = 1 and grade >= 60
UNION ALL 
SELECT a.*
FROM student AS a
WHERE class = 2 and grade >= 60

【常用函数】

--字符串函数
substring(string,start,length)  --截取字符串string,从start开始的length个字符,类似excel的mid
left(string,length)  --截取字符串string,从最左边开始的length个字符,类似excel的left
right(string,length)  --截取字符串string,从最右边开始的length个字符,类似excel的right
instr (string1 , string2 [,start_location ]) --MYSQL中查找string1在string2中出现的位置
insert(string1,start，length,string2)  --MYSQL中删除指定位置的的指定个数字符，并在指定位置处插入新字符

【举个栗子_10】字符串函数

SELECT 
substring('中国浙江省杭州市',3,3)  AS province
,left('中国浙江省杭州市',2)  AS country
,right('中国浙江省杭州市',3)  AS city
,instr ('中国浙江省杭州市' , '杭州市')  AS index_city
,insert('中国浙江省杭州市',6,3,'宁波市')  AS replace_city

--日期函数,不同数据库会有一些区别，此处以mysql为例
current_date()     --当前日期
date_add(date,interval expr unit)   --date加减expr的unit(年月日周)的date
datediff(expr1,expr2)   --expr1减去expr2的天数
timediff(expr1,expr2)   --expr1减去expr2的的时间
date_format(date,format)  --格式化日期
str_to_date(str,format)  --将字符转换成日期

【举个栗子_11】日期函数

SELECT 
current_date() as now, 
date_add(current_date(), interval -1 day) as yesterday, 
DATEDIFF(current_date(),date_add(current_date(), interval -1 day)) as date_cha,
DATE_FORMAT(current_date(),'%Y/%m/%d') as ym,
STR_TO_DATE('2020-05-01','%Y-%m-%d') as strdate1,
STR_TO_DATE('2020.05.01','%Y.%m.%d') as strdate2

--其他一些常用函数
round(columns, decimals)  --对某个数据列进行指定小数位四舍五入
len(str)                  --返回某个字段长短
lowner(str)               --将字符全部小写
upper(str)                --将字符全部大写
first()                   --返回第一行记录
last()                    --返回最后一个记录的值 
cast(expr as stype)       --类型转化,比如cast(str as bigint)
from_unixtime(timestamp)  --将时间戳转换为时间

【窗口函数】

窗口函数也称为OLAP函数，可以对数据库数据进行实时分析处理。语法如下：

<窗口函数> over ([partition by <分组coulums>] 
order by <排序columns> [窗口子句])

窗口函数经常用来计算排序，也可以用在一些聚合函数上，下面举例说明，一张新的学生表student_new

student_new

【举个栗子_12】窗口函数-排序

--用于排序，row_number,rank,dense_rank
SELECT a.*,
       row_number() over(partition by class order by grade desc) as row_num,
       rank() over(partition by class order by grade desc) as rank_num,
       dense_rank() over(partition by class order by grade desc) as dense_num,
FROM student_new

由上面这个例子可以清晰地看出三个窗口排序函数的区别，row_number是在每个分组窗口中给定唯一序号，而rank碰到相等值序号一样，会跳过之后的位次，而dense_rank碰到相等值序号也一致，但不跳过之后的位次。

【举个栗子_13】窗口函数-聚合函数

--聚合窗口函数
SELECT a.*,
       sum(grade) over (order by stu_id) as cum_sum,
       avg(grade) over (order by stu_id) as cum_avg
FROM student_new

cum_sum计算直到当前行的分数总和，cum_avg计算直到当前行的平均成绩。

其他窗口函数，lag()和lead()取前N行或后N行，first_values和last_values(取第一行和最后一行)。

【举个栗子_14】其他窗口函数

SELECT a.*,
       lag(grade,1,-1) over (order by grade desc) as before_grade,   --第二个参数指前1行，第三个参数为缺省情况
       lead(grade,1,-1) over (order by grade desc) as next_grade,
       first_values(grade) over (order by grade desc) as max_grade
       last_values(grade) over (order by grade desc)  as min_grade
   
FROM student_new

窗口子句：上面的窗口函数都是作用在整个数据中或partition by的分区中，还可以通过窗口子句选择函数作用范围。

【举个栗子_15】窗口子句，计算移动平均值

SELECT a.*,
       avg(grade) over (order by stu_id rows 2 preceding) as moving_avg
FROM student_new

窗口子句中的rows 2 preceding是指数据范围为当前行的前两行到当前行，也可以用following关键字替换preceding，含义是当前行到后两行，还可以用rows between n preceding and n following 表示前n行到后n行。

【WITH 子句】

with子句是一种临时表，这个临时表简直太强大了，全靠它才能轻轻轻松松写上几百上千行SQL代码，如果没有它，面对日常取数需求几十个字段而且来自不同表格不同维度的嵌套子查询简直会疯。语句格式为： with table_1 as (select……)， table_2 as (select……)，table_3 as (select……)，…… table_n as (select……)，后面子表还可以从前面子表中查找数据，比如table2中的FROM 可以接table_1

【举个栗子_16】WITH子句

--用with子句得到成年且成绩及格的学生名单
with adult as (
SELECT stu_id
FROM student
WHERE age >=18
),
pass_stu as (
SELECT stu_id
FROM student
WHERE grade >=60
)
SELECT a.stu_id, age, grade
FROM student AS a
RIGHT JOIN adult AS b on (a.stu_id = b.stu_id)
RIGHT JOIN adult AS c on (a.stu_id = c.stu_id)

上述例子的问题很简单，完全可以用WHERE子句完成，举这个例子只是为了展示一下WITH子句的用法。当数据来自很多张不同的表格时，把各个指标分在不同的WITH子表中，最后再通过关键字段连接起来是非常方便的。

以上就是整个实习期间所需的SQL技能总结了，把这些全部掌握，日常SQL取数完全没问题了。具体需求中碰到不常用的功能再网上搜索一下就好了。

【数据分析实习体验】

最后说一下整个实习过程的感受吧，其实数据分析师所需要的技术能力要求不高，SQL入门大概一周左右就能把文章中提到的技能掌握得差不多。数据分析师的最重要价值还是体现在对业务的理解上，能够实实在在地利用数据为业务赋能，监控经营风险，给出合理的经营建议，挖掘有价值的信息和规律。