数据分析师sql实战课(数据分析师之快速掌握SQL基础)

数据分析师sql实战课(数据分析师之快速掌握SQL基础)(1)

数据与智能 本公众号关注大数据与人工智能技术。由一批具备多年实战经验的技术极客参与运营管理,持续输出大数据、数据分析、推荐系统、机器学习、人工智能等方向的原创文章,每周至少输出5篇精品原创。同时,我们会关注和分享大数据与人工智能行业动态。欢迎持续关注。

作者:斌迪

SQL技能是数据分析师的必备技能,作者在之前的文章《你不知道的数据分析师》中也提到了,数据分析师50%的时间都在写SQL。

本文将从一道数据分析师的SQL面试题开始分析讲解,期间,会涉及到SQL的基础操作和分析函数的使用等知识点,然后为大家总结出了一份快速掌握SQL基础的指南,希望能够帮助到SQL初学者。

一道SQL面试题

这是一道来自百度数据部门的面试题,主要考察row_number的使用。

题目:SQL语句如何查询各用户最长连续登录天数?如图左边是源表User,右边是需要达到的查询结果。

数据分析师sql实战课(数据分析师之快速掌握SQL基础)(2)

数据分析师sql实战课(数据分析师之快速掌握SQL基础)(3)

Step 1 审题

数据分析师sql实战课(数据分析师之快速掌握SQL基础)(4)

各用户最长的连续登录天数,先要确定连续登录的数据特征,日期表现为每个用户的后一天和前一天的差值为1,不能为大于1的值,一旦大于1也就间断了。那么可以设置一列序号,如果是连续的话,这列序号也是会随着日期同步增长的,那么日期减去这个序号,应该都是一个确定的日期。

比如说2017年1月1号对应的序号是1,2017年1月2号对应的序号是2,2017年1月3号对应的序号是3,那么2017年1月1号-1=2016年12月31号,同理,2017年1月2号-2=2016年12月31号,都是同样的日期。

根据这个日期与序号之差和UID进行分组统计出不同UID和差值的数量,最后按照UID分组统计出数量的最大值。具体流程见下图:

数据分析师sql实战课(数据分析师之快速掌握SQL基础)(5)

数据分析师sql实战课(数据分析师之快速掌握SQL基础)(6)

Step 2 创建表

数据分析师sql实战课(数据分析师之快速掌握SQL基础)(7)

CREATE TABLE IF NOT EXISTS `loadrecord` (

`uid` int,

`loadtime` string

) ;

INSERT INTO `loadrecord` (`uid`, `loadtime`) VALUES

('201', '2017/1/1'),

('201', '2017/1/2'),

('202', '2017/1/2'),

('202', '2017/1/3'),

('203', '2017/1/3'),

('201', '2017/1/4'),

('202', '2017/1/4'),

('201', '2017/1/5'),

('202', '2017/1/5'),

('201', '2017/1/6'),

('203', '2017/1/6'),

('203', '2017/1/7');

数据分析师sql实战课(数据分析师之快速掌握SQL基础)(8)

Step 3 添加一列日期序号

数据分析师sql实战课(数据分析师之快速掌握SQL基础)(9)

select uid,loadtime,row_number() over (partition by uid order by loadtime) as row_num

from loadrecord;

结果如下图所示:

数据分析师sql实战课(数据分析师之快速掌握SQL基础)(10)

这里用到了row_number窗口分析函数,将每个用户按照登录日期升序进行编号。

数据分析师sql实战课(数据分析师之快速掌握SQL基础)(11)

Step 4 获得一个新日期

数据分析师sql实战课(数据分析师之快速掌握SQL基础)(12)

select uid,loadtime,row_number() over (partition by uid order by loadtime) asrow_num,date_sub(regexp_replace(loadtime,'/','-'),row_number() over (partition by uid order by loadtime)) as new_loadtime

from loadrecord

数据分析师sql实战课(数据分析师之快速掌握SQL基础)(13)

这里先用字符串函数regexp_replace将日期格式修改为"yyyy-MM-dd"格式,然后用date_sub函数将日期相减。

数据分析师sql实战课(数据分析师之快速掌握SQL基础)(14)

Step 5 第一次聚合

数据分析师sql实战课(数据分析师之快速掌握SQL基础)(15)

select uid,new_loadtime,count(uid) as new_loadtime_num

from

(

select uid,loadtime,row_number() over(partition by uid order by loadtime) as row_num,date_sub(regexp_replace(loadtime,'/','-'),row_number() over (partition by uid order by loadtime)) as new_loadtime

from loadrecord

) a

group by uid,new_loadtime

数据分析师sql实战课(数据分析师之快速掌握SQL基础)(16)

这里使用了count聚合函数和子查询操作,通过这一次的聚合统计出每个用户对应的所有连续登录的天数。

数据分析师sql实战课(数据分析师之快速掌握SQL基础)(17)

Step 6 审题

数据分析师sql实战课(数据分析师之快速掌握SQL基础)(18)

select uid,max(new_loadtime_num) as max_new_loadtime_num

from

(

select uid,new_loadtime,count(uid) asnew_loadtime_num

from

(

select uid,loadtime,row_number() over(partition by uid order by loadtime) as row_num,date_sub(regexp_replace(loadtime,'/','-'),row_number() over (partition by uid order by loadtime)) as new_loadtime

from loadrecord

) a

group by uid,new_loadtime

) b

group by uid

数据分析师sql实战课(数据分析师之快速掌握SQL基础)(19)

这里使用了max聚合函数和子查询操作,通过这一次的聚合统计出每个用户对应的最大的登录时长。

到此,以上为这道SQL题目的完整解答过程,整个过程涉及SQL的基础操作(建表、查询、限定、排序)的同时,也加入了聚合函数、子查询和窗户分析函数相对进阶的操作。

各用户最长的连续登录天数-这样短短的12个字,翻译成SQL语句居然用到了两层嵌套查询、两个聚合操作、一个日期操作和一个窗口分析函数。其实在数据分析师的日常工作中,比这道SQL题目复杂的需求也是常见的,所以学好SQL对于数据分析师工作的重要性也就不言而喻了吧。

本文的后半部分将用思维导图的方式给初学者总结出一份快速学习SQL的指南,主要是一些常用的知识点,根据二八定律,只需掌握最重要的20%核心知识点,就足以胜任80%的常见工作,这里总结的应该超过了20%,足够用了。

SQL学习指南

数据分析师sql实战课(数据分析师之快速掌握SQL基础)(20)

Stage 1 基础入门

数据分析师sql实战课(数据分析师之快速掌握SQL基础)(21)

本阶段是基础入门,了解SQL的基本语法,主要涉及表的操作。

数据分析师sql实战课(数据分析师之快速掌握SQL基础)(22)

数据分析师sql实战课(数据分析师之快速掌握SQL基础)(23)

Stape 2 基础查询

数据分析师sql实战课(数据分析师之快速掌握SQL基础)(24)

查询操作是最常用的最重要的,下图是基础查询用到的列的操作、运算符、结果限定的语法。

数据分析师sql实战课(数据分析师之快速掌握SQL基础)(25)

数据分析师sql实战课(数据分析师之快速掌握SQL基础)(26)

Stape 3 复杂查询

数据分析师sql实战课(数据分析师之快速掌握SQL基础)(27)

复杂查询包括子查询、关联子查询和视图,这一部分的内容如果掌握了,可以实际工作中的很多问题。

数据分析师sql实战课(数据分析师之快速掌握SQL基础)(28)

数据分析师sql实战课(数据分析师之快速掌握SQL基础)(29)

Stape 4 数据更新

数据分析师sql实战课(数据分析师之快速掌握SQL基础)(30)

本阶段学习数据更新的基本操作,包括插入、删除和更新。

数据分析师sql实战课(数据分析师之快速掌握SQL基础)(31)

数据分析师sql实战课(数据分析师之快速掌握SQL基础)(32)

Stape 5 常用函数

数据分析师sql实战课(数据分析师之快速掌握SQL基础)(33)

本阶段学习常用函数,此处按照熟悉函数、字符串函数、日期函数、转换函数列举了较常用的函数,不同的数据库对应的函数名称可能会不一样,大家在使用的时候可以查阅相应数据库的函数文档。

数据分析师sql实战课(数据分析师之快速掌握SQL基础)(34)

数据分析师sql实战课(数据分析师之快速掌握SQL基础)(35)

Stape 6 聚合排序

数据分析师sql实战课(数据分析师之快速掌握SQL基础)(36)

本阶段学习聚合和排序,主要介绍聚合查询、分组、分组后筛选、分组后排序的语法和注意事项。一般在实际工作中使用的时候,书写顺序是:select->from->where->group by->having->order by,但是实际的执行顺序是:from->where->group by->having->select->order by(选表->筛选记录->分组->分组后筛选->选列->排序)。

数据分析师sql实战课(数据分析师之快速掌握SQL基础)(37)

数据分析师sql实战课(数据分析师之快速掌握SQL基础)(38)

Stape 7 集合操作

数据分析师sql实战课(数据分析师之快速掌握SQL基础)(39)

本阶段学习集合操作,包括表的加减、表的联结。实际工作用多表的联结是很常见的,这里的思维导图列出来的知识点相对比较基础,大家可以在此基础上查阅相应的资料进行补充学习。

数据分析师sql实战课(数据分析师之快速掌握SQL基础)(40)

SQL的掌握重在实践,多在实际操作中使用,不必死记硬背语法和函数,把它当做一个工具箱,遇到问题的时候打开工具箱取出相应的工具来解决具体的问题,而打开工具箱的方式多种多样-记忆力超群的你各种函数了然于胸、借助平台提示、搜索引擎搜索等等。

,

免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:anhduc.ph@yahoo.com

    分享
    投诉
    首页