数据分析SQL查询:一文带你入门到掌握

核心提示【背景介绍】在一家知名电商企业的BI部门实习四个多月,岗位为数据分析。日常工作中打交道最多的就是SQL和EXCEL,在实习之前SQL技能只会简单的增删改查语句,第一周实习经理甩了一份业务常见绩效取数的SQL代码给我,三四百行的代码看得头昏眼

【背景介绍】

在一家知名电商企业的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来查找数据吧。

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

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

--Having语句筛选班级平均分大于60分的班级SELECT class, avg, max, min as min_grade, countFROM studentGROUP BY classHAVING avg > 60

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

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

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

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

--WHERE 和 FROM 多行子查询INSELECT stu_idFROM student as aWHERE class IN as avg_gradeFROM studentGROUP BY classORDER BY avg DESClimit 1) sub_1)

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

SELECT stu_id,gradeFROM student as aWHERE grade > any


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

SELECT class,count as failed_num,count as pass_numFROM studentGROUP BY class

【常用函数】

--字符串函数substring --截取字符串string,从start开始的length个字符,类似excel的midleft --截取字符串string,从最左边开始的length个字符,类似excel的leftright --截取字符串string,从最右边开始的length个字符,类似excel的rightinstr --MYSQL中查找string1在string2中出现的位置insert --MYSQL中删除指定位置的的指定个数字符,并在指定位置处插入新字符

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

SELECT substring AS province,left AS country,right AS city,instr AS index_city,insert AS replace_city

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

【窗口函数】

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

<窗口函数> over

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

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

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

--聚合窗口函数SELECT a.*,sum over as cum_sum,avg over as cum_avgFROM student_new a

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

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

SELECT a.*,avg over as moving_avgFROM student_new a

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

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

【数据分析实习体验】

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

 
友情链接
鄂ICP备19019357号-22