SQL语句+语法I数据分析面试必备！

 ↑ 关注 &＃43; 置顶 ~ 别错过小z的干货内容

| 作者&＃xff1a;无眠

| 来源&＃xff1a;知乎

前些天在网上冲浪的时候看到一个案例咨询&＃xff0c;问说世界500强的数据分析要不要去&＃xff0c;评论区一片爆炸&＃xff1a;“楼主能分享一下文科生怎么转行做数据分析吗&＃xff1f;&＃xff1f;”、“SQL、python这些学起来好痛苦&＃xff01;”我看着屏幕苦笑&＃xff0c;数据分析岗位现在的热门程度如果要形容的话&＃xff0c;基本就是随便抓一个微博网友都知道这个岗位了。

Anyway&＃xff0c;言归正传&＃xff0c;数据分析师的招聘JD你们一定不陌生&＃xff1a;

可以说&＃xff0c;每个数据分析岗都需要会SQL。

我本人曾在滴滴、美团、平安科技的数据分析类岗位实习过&＃xff0c;实习期间会大量运用sql进行取数。也参与了2018年的秋招&＃xff0c;做过网易、拼多多、新浪等等公司的数据分析笔试题&＃xff0c;还是比较了解SQL常考的题目类型的。

写这篇文章是希望帮助还没有实战过SQL的小伙伴、或者了解一些SQL语句&＃xff0c;但是担心自己了解的太片面的小伙伴。这篇文章主要介绍的是&＃xff1a;如果想要面试数据分析岗位&＃xff0c;最优先需要掌握的SQL技能是哪些呢&＃xff1f;

读完本文&＃xff0c;你能快速知道&＃xff1a;

&＃xff08;1&＃xff09;除了select 这种基本的语句&＃xff0c;我最应该马上掌握的SQL语句和知识是什么&＃xff1f;

&＃xff08;2&＃xff09;面试中SQL题80%都在考察的语法是什么&＃xff1f;

&＃xff08;3&＃xff09;这些语法应该怎么使用&＃xff1f;

本文将从三大块介绍入门SQL需要掌握的语法和知识&＃xff0c;分别是

• 最基础的选择&＃xff08;select&＃xff09;和连接&＃xff08;join/union&＃xff09;

• 最常用的函数&＃xff08;distinct/group by/order by等&＃xff09;

• 一些小小的进阶技巧&＃xff08;组内排序、取前百分之多少的值、时间函数&＃xff09;

从一个实习和秋招过来人的角度看&＃xff0c;这些知识基本够面试的时候用了&＃xff0c;如果本身也在数据分析岗位实习或者实习过&＃xff0c;可以在评论区讨论或者补充一些也常常用到的SQL知识&＃xff0c;大家一起交流进步~ ps. 本文主要做知识点快速突破&＃xff0c;具体的实战练习大家必不可少还是要做&＃xff01;

介绍完了三大块知识后&＃xff0c;后续会有常见的SQL面试/笔试题&＃xff0c;可以练习和交流~

1. 最基本&＃xff08;选数据&＃xff09;

• 怎么把数据从表里选出来&＃xff1f;

-- 从table_1中选择a这一列
select a from table_1


• 想要的数据在多张表里&＃xff0c;想取多个字段&＃xff0c;该怎么办&＃xff1f;—— 表连接

-- table_1中有id,age; table_2中有id&＃xff0c;sex。想取出id,age,sex 三列信息
-- 将table_1,table_2 根据主键id连接起来
select a.id,a.age,b.sex from
(select id,age from table_1) a --将select之后的内容存为临时表a
join
(select id, sex from table_2) b --将select之后的内容存为临时表b
on a.id &＃61;b.id


在这里先介绍一下几种join: &＃xff08;敲重点&＃xff0c;很容易问的哦&＃xff09;

join : hive的join默认是inner join&＃xff0c;找出左右都可匹配的记录&＃xff1b;

left join: 左连接&＃xff0c;以左表为准&＃xff0c;逐条去右表找可匹配字段&＃xff0c;如果有多条会逐次列出&＃xff0c;如果没有找到则是NULL&＃xff1b;

right join&＃xff1a;右连接&＃xff0c;以右表为准&＃xff0c;逐条去左表找可匹配字段&＃xff0c;如果有多条会逐次列出&＃xff0c;如果没有找到则是NULL&＃xff1b;

full outer join: 全连接&＃xff0c;包含两个表的连接结果&＃xff0c;如果左表缺失或者右表缺失的数据会填充NULL。

每种join 都有on ,>join 之前要确保关联键是否去重&＃xff0c;是不是刻意保留非去重结果。

• 两张表数据的字段一样&＃xff0c;想合并起来&＃xff0c;怎么办&＃xff1f;

-- 不去重&＃xff0c;合并两张表的数据
select * from
(
select id from table_1
UNION ALL
select id from table_2
)t;


union和union all 均基于列合并多张表的数据&＃xff0c;所合并的列格式必须完全一致。union的过程中会去重并降低效率&＃xff0c;union all 直接追加数据。union 前后是两段select 语句而非结果集。

2. 最常用&＃xff08;更有多重组合&＃xff09;

为方便大家理解每个函数的作用&＃xff0c;先建一个表&＃xff0c;后面以这个为示例。

• 如果有千万用户数据&＃xff0c;想知道有多少去重的用户数&＃xff1f;—— 去重 distinct

-- 罗列不同的id
select distinct id from table_1-- 统计不同的id的个数
select count(distinct id) from table_1-- 优化版本的count distinct
select count(*) from
(select distinct id from table_1) tb


distinct 会对结果集去重&＃xff0c;对全部选择字段进行去重&＃xff0c;并不能针对其中部分字段进行去重。使用count distinct进行去重统计会将reducer数量强制限定为1&＃xff0c;而影响效率&＃xff0c;因此适合改写为子查询。

• 想分性别进行统计&＃xff0c;看看男女各多少&＃xff1f;—— 聚合函数和group by

-- 统计不同性别&＃xff08;F、M&＃xff09;中&＃xff0c;不同的id个数
select count(distinct id) from table_1
group by sex
-- 其它的聚合函数例如&＃xff1a;max/min/avg/sum-- 统计最大/最小/平均年龄
select max(age), min(age),avg(age) from
table_1
group by id


聚合函数帮助我们进行基本的数据统计&＃xff0c;例如计算最大值、最小值、平均值、总数、求和

• 只想查看A公司的男女人数数据&＃xff1f;—— 筛选 where/having

-- 统计A公司的男女人数
select count(distinct id) from table_1
where company &＃61; &＃39;A&＃39;
group by sex-- 统计各公司的男性平均年龄&＃xff0c;并且仅保留平均年龄30岁以上的公司
select company, avg(age) from table_1
where sex &＃61; &＃39;M&＃39;
group by company
having avg(age)>30;


• 希望查询结果从高到低/从低到高排序&＃xff1f;—— 排序 order by

-- 按年龄全局倒序排序取最年迈的10个人
select id,age from table_1 order by age DESC
limit 10


• 将数值型的变量转化为分类型的变量&＃xff1f;—— case when 条件函数

-- 收入区间分组
select id,
(case when CAST(salary as float)<50000 Then &＃39;0-5万&＃39;
when CAST(salary as float)>&＃61;50000 and CAST(salary as float)<100000 then &＃39;5-10万&＃39;
when CAST(salary as float) >&＃61;100000 and CAST(salary as float)<200000 then &＃39;10-20万&＃39;
when CAST(salary as float)>200000 then &＃39;20万以上&＃39;
else NULL end
from table_1;


• case 函数的格式为&＃xff08;case when 条件1 then value1 else null end&＃xff09;, 其中else 可以省&＃xff0c;但是end不可以省。

在这个例子里也穿插了一个CAST的用法&＃xff0c;它常用于string/int/double型的转换。

• 字符串

1. concat( A, B...)返回将A和B按顺序连接在一起的字符串&＃xff0c;如&＃xff1a;concat(&＃39;foo&＃39;, &＃39;bar&＃39;) 返回&＃39;foobar&＃39;

select concat(&＃39;www&＃39;,&＃39;.iteblog&＃39;,&＃39;.com&＃39;) from
iteblog;
--得到 www.iteblog.com


2. split(str, regex)用于将string类型数据按regex提取&＃xff0c;分隔后转换为array。

-- 以","为分隔符分割字符串&＃xff0c;并转化为array
Select split("1,2,3",",")as value_array from table_1;
-- 结合array index,将原始字符串分割为3列
select value_array[0],value_array[1],value_array[2] from
(select split("1,2,3",",")as value_array from table_1 )t


3. substr&＃xff08;str,0,len) 截取字符串从0位开始的长度为len个字符。

select substr(&＃39;abcde&＃39;,3,2) from
iteblog;-- 得到cd


3. 基础进阶

• 不想全局排序&＃xff0c;需要分组排序&＃xff1f;—— row_number(&＃xff09;

-- 按照字段salary倒序编号
select *, row_number() over (order by salary desc) as row_num from table_1;-- 按照字段deptid分组后再按照salary倒序编号
select *, row_number() over (partition by deptid order by salary desc) as rank from table_1;

除了row_number函数之外&＃xff0c;还有两个分组排序函数&＃xff0c;分别是rank() 和dense_rank()。

• rank()排序相同时会重复&＃xff0c;总数不会变 &＃xff0c;意思是会出现1、1、3这样的排序结果&＃xff1b;

• dense_rank() 排序相同时会重复&＃xff0c;总数会减少&＃xff0c;意思是会出现1、1、2这样的排序结果。

• row_number() 则在排序相同时不重复&＃xff0c;会根据顺序排序。

• 想要获取top10%的值&＃xff1f;—— percentile 百分位函数

-- 获取income字段的top10%的阈值
select percentile(CAST (salary AS int),0.9)) as income_top10p_threshold from table_1;-- 获取income字段的10个百分位点
select percentile(CAST (salary AS int),array(0.0,0.1,0.2,0.3,0.4,0.5,0.6,0.7,0.8,0.9,1.0)) as income_percentiles
from table_1;


• 想要对时间字段进行操作&＃xff1f;—— 时间函数

-- 转换为时间数据的格式
select to_date("1970-01-01 00:00:00") as start_time from table_1;-- 计算数据到当前时间的天数差
select datediff
(&＃39;2016-12-30&＃39;,&＃39;2016-12-29&＃39;);
-- 得到 "1"


to_date函数可以把时间的字符串形式转化为时间类型&＃xff0c;再进行后续的计算&＃xff1b;

• 常用的日期提取函数包括 year()/month()/day()/hour()/minute()/second()

• 日期运算函数包括datediff(enddate,stratdate) 计算两个时间的时间差&＃xff08;day)&＃xff1b;

• date_sub(stratdate,days) 返回开始日期startdate减少days天后的日期。

• date_add(startdate,days) 返回开始日期startdate增加days天后的日期。

4. 常见笔试/面试题

例&＃xff1a;有3个表S&＃xff0c;C&＃xff0c;SC&＃xff1a;

S&＃xff08;SNO&＃xff0c;SNAME&＃xff09;代表&＃xff08;学号&＃xff0c;姓名&＃xff09;

C&＃xff08;CNO&＃xff0c;CNAME&＃xff0c;CTEACHER&＃xff09;代表&＃xff08;课号&＃xff0c;课名&＃xff0c;教师&＃xff09;

SC&＃xff08;SNO&＃xff0c;CNO&＃xff0c;SCGRADE&＃xff09;代表&＃xff08;学号&＃xff0c;课号&＃xff0c;成绩&＃xff09;

问题&＃xff1a;

1. 找出没选过“黎明”老师的所有学生姓名。

2. 列出2门以上&＃xff08;含2门&＃xff09;不及格学生姓名及平均成绩。

3. 既学过1号课程又学过2号课所有学生的姓名。

1. -- 考察条件筛选
select sname from s where sno not in
( select sno from sc where cno in (
select distinct cno from c where cteacher&＃61;&＃39;黎明&＃39; )
);2. -- 考察聚合函数&＃xff0c;条件筛选
select s.sname, avg_grade from s
join
(select sno from sc where scgrade <60 group by sno having count(*) >&＃61; 2) t1
on s.sno &＃61; t1.sno
join
(select sno, avg(scgrade) as avg_grade from sc group by sno ) t2
on s.sno &＃61; t2.sno;3. -- 考察筛选、连接
select sname from( select sno from sc where cno &＃61; 1) a
join (select sno from sc where cno &＃61; 2) b
on a.sno &＃61; b.sno


做SQL题的时候注意理解每个题目希望你用的是什么知识点&＃xff0c;这样有助于巩固。

当初我学SQL的时候&＃xff0c;盯着《SQL必知必会》翻来覆去的看&＃xff0c;但是知识点真的比较多&＃xff0c;也比较零碎。在写这篇文章之前&＃xff0c;也看过知乎上关于SQL学习的文章&＃xff0c;有的比较广泛而全面&＃xff0c;有的则很干货&＃xff0c;全部是牛客上的SQL题目的解析。

基于自己的体会&＃xff0c;我写了这篇SQL面试和笔试的入门文章&＃xff0c;主旨是快速、清晰的把握重点。希望大家都能快快入门SQL~


延伸阅读

    同同同期群分析到底是个啥&＃xff1f;    Pandas熟练&＃xff1f;进来测测这50道题吧&＃xff01;    实战解读&＃xff1a;数据分析&＃xff0c;如何更进一步&＃xff1f;
数据不吹牛读者群已经建立&＃xff0c;后台回复“入群”&＃xff0c;即可加入有趣讨论&＃xff0c;交流数据干货


“多来点干货&＃xff01;”