SQL大厂面试常考知识点 | 新手速进!

挖数网精选
挖数网精选
挖数网精选
444
文章
0
评论
2020-06-0513:06:00 评论 557 4073字
摘要

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

前些天在网上冲浪的时候看到一个案例咨询,问说世界500强的数据分析要不要去,评论区一片爆炸:

"楼主能分享一下文科生怎么转行做数据分析吗?"

"SQL、python这些学起来好痛苦!"

我看着屏幕苦笑,数据分析岗位现在的热门程度如果要形容的话,基本就是随便抓一个微博网友都知道这个岗位了。

Anyway,言归正传,数据分析师的招聘JD你们一定不陌生:

SQL大厂面试常考知识点 | 新手速进!

可以说,不是每个数据分析岗都要求python,但是每个数据分析岗都需要会SQL。

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

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

读完本文,你能快速知道:

  • 除了select 这种基本的语句,我最应该马上掌握的SQL语句和知识是什么?
  • 面试中SQL题80%都在考察的语法是什么?
  • 这些语法应该怎么使用?

本文将从三大块介绍入门SQL需要掌握的语法和知识,分别是最基础的选择(select)和连接(join/union);最常用的函数(distinct/group by/order by等);一些小小的进阶技巧(组内排序、取前百分之多少的值、时间函数)。

SQL大厂面试常考知识点 | 新手速进!

一.最基本(选数据)

1. 怎么把数据从表里选出来?

-- 从table_1中选择a这一列

select a from table_1

2. 表连接

-- table_1中有id,age; table_2中有id,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 =b.id

在这里先介绍一下几种join: (敲重点,很容易问的哦)

SQL大厂面试常考知识点 | 新手速进!

join : hive的join默认是inner join,找出左右都可匹配的记录

SQL大厂面试常考知识点 | 新手速进!

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

SQL大厂面试常考知识点 | 新手速进!

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

SQL大厂面试常考知识点 | 新手速进!

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

SQL大厂面试常考知识点 | 新手速进!

每种join 都有on , on的是左表和右表中都有的字段。join 之前要确保关联键是否去重,是不是刻意保留非去重结果。

3. 两张表数据的字段一样,想合并起来,怎么办?

-- 不去重,合并两张表的数据

select * from

(

select id from table_1

UNION ALL

select id from table_2

)t;

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

二.最常用

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

SQL大厂面试常考知识点 | 新手速进!

1. 去重 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 会对结果集去重,对全部选择字段进行去重,并不能针对其中部分字段进行去重。使用count distinct进行去重统计会将reducer数量强制限定为1,而影响效率,因此适合改写为子查询。

2. 聚合函数和group by

-- 统计不同性别(F、M)中,不同的id个数

select count(distinct id) from table_1

group by sex

-- 其它的聚合函数例如:max/min/avg/sum

-- 统计最大/最小/平均年龄

select max(age), min(age),avg(age) from

table_1

group by id

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

3. 筛选 where/having

-- 统计A公司的男女人数

select count(distinct id) from table_1

where company = "A"

group by sex

-- 统计各公司的男性平均年龄,并且仅保留平均年龄30岁以上的公司

select company, avg(age) from table_1

where sex = "M"

group by company

having avg(age)>30;

4. 排序 order by

-- 按年龄全局倒序排序取最年迈的10个人

select id,age from table_1 order by age DESC

limit 10

5. case when 条件函数

-- 收入区间分组

select id,

(case when CAST(salary as float)<50000 Then "0-5万"

when CAST(salary as float)>=50000 and CAST(salary as float)<100000 then "5-10万"

when CAST(salary as float) >=100000 and CAST(salary as float)<200000 then "10-20万"

when CAST(salary as float)>200000 then "20万以上"

else NULL end

from table_1;

case 函数的格式为(case when 条件1 then value1 else null end), 其中else 可以省,但是end不可以省。

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

6. 字符串

1)concat( A, B...)返回将A和B按顺序连接在一起的字符串,如:concat("foo", "bar") 返回"foobar"。

select concat("www",".iteblog",".com") from

iteblog;

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

-- 以","为分隔符分割字符串,并转化为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(str,0,len) 截取字符串从0位开始的长度为len个字符。

select substr("abcde",3,2) from

iteblog;

-- 得到cd

三.基础进阶

1.row_number()

-- 按照字段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;

SQL大厂面试常考知识点 | 新手速进!

按照depid分组,对salary进行排序(倒序)

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

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

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

row_number() 则在排序相同时不重复,会根据顺序排序。

2.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;

3.时间函数

-- 转换为时间数据的格式

select to_date("1970-01-01 00:00:00") as start_time from table_1;

-- 计算数据到当前时间的天数差

select datediff("2016-12-30","2016-12-29");

-- 得到 "1"

to_date函数可以把时间的字符串形式转化为时间类型,再进行后续的计算。

常用的日期提取函数包括:

  • year()/month()/day()/hour()/minute()/second()
  • 日期运算函数包括datediff(enddate,stratdate) 计算两个时间的时间差(day)
  • date_sub(stratdate,days) 返回开始日期startdate减少days天后的日期
  • date_add(startdate,days) 返回开始日期startdate增加days天后的日期

End.

作者:无眠

来源:知乎专栏

本文为转载分享,如有侵权请联系后台删除

  • 我的微信公众号
  • 微信扫一扫
  • weinxin
  • 我的微信公众号
  • 微信扫一扫
  • weinxin
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: