文章目录

• • • • 1. 数据分析岗位技能要求
      • • 1.1 哪一个工具是数据分析师的核心工具
        • 1.2 对于数据分析师来说，是否需要建模能力
        • 1.3 数据分析岗位对业务有什么要求
      • 2.数据分析师必须掌握的SQL核心技能
      • • 2.1 SQL万能查询框架
        • 2.2 你最应该掌握的SQL语句和知识
        • • 2.2.1 最基本（选数据）
          • 2.2.2 最常用（单个或组合使用）
          • 2.2.3 基础进阶（开窗函数）
      • 3.大厂数据分析SQL面试精讲
      • • 3.1 面试常考基本题型
        • 3.2 大厂面试原题讲解

1. 数据分析岗位技能要求

1.1 哪一个工具是数据分析师的核心工具

SQL是当之无愧的第一工具，排名第二的是BI，excel也相当重要，python所占比例不大。

1.2 对于数据分析师来说，是否需要建模能力

随着经验要求上升，岗位对数据分析建模能力的要求越来越广泛，学习算法和建模是数据分析进阶的必备路径。建模指机器学习算法和深度学习算法。

1.3 数据分析岗位对业务有什么要求

业务能力排名第一，产品、运营和项目经验等也都和业务能力挂钩。除了业务外，统计分析、建模也十分重要。

2.数据分析师必须掌握的SQL核心技能

2.1 SQL万能查询框架

• SQL操作的一般框架

select <select_list>
from <table_list>
[where <condition>]
[group by <group_by_list>]
[having <having_condition>]
[order by <order_by_list> ASC|DESC]
[limit <limit_number>]

   select-查询
   from-从
   where-哪里(满足XX条件的)
   group by-依据XX分组
   order by-依据XX排序
   limit-限制N条

   总结:从XX表中查询满足XX条件的XX列，结果依据XX分组，依据XX排序，限制返回N条。

• SQL语句的书写顺序vs执行顺序
  

1. 首先会定位到from关键词确认语句将要对哪些表进行操作
2. 然后定位到where关键词看看限定了什么样的查询条件,
3. 之后定位到group by关键词看是否有分组统计需求，
4. 前面执行结束之后执行having语句，对查询结果进行条件筛选，
5. 之后确定最终要返回哪些字段呈现给用户（也就是select关键词后面的列名)
6. 如果有distinct关键词就执行去重操作，
7. 最后根据order by关键词后面字段对返回的结果进行升序或者降序排列，
8. 如果有返回行数限制的要求，就按照limit关键词后面的数字返回相应的行数

2.2 你最应该掌握的SQL语句和知识

2.2.1 最基本（选数据）

• 怎么把数据从表中选择出来–select

• 想要的数据在多张表中，想取多个字段，该怎么办？–表连接

  

  
  
  
  
  

  注意：

  • MySQL中不支持全连接

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

  • 两张表数据的字段一样，想合并起来，怎么办？–union
    union和union all均基于列合并多张表的数据，所合并的列格式必须完全一致。union的过程中会去重并降低效率，union all直接追加数据。

2.2.2 最常用（单个或组合使用）

• 去重 distinct
  如果有千万用户数据。想知道有多少不重复的用户数?

• 罗列不同的id
  select distinct id from Table_1

• 统计不同的ld数
  select count(distinct id) from Table_1

• 优化版本的count distinct

  • 使用count distinct进行去重统计会将reducer数量强制限定为1
  • 影响查询效率
  • 可以使用子查询进行优化
    select count(*) from (select distinct id from table_1) tb

• 聚合 max/min/sum/count+group by

• 想分性别进行统计，看看男女各多少?

  • 统计不同性别(F、M）中，不同的id个数
    select count(distinct id) from table_1 group by hender;
  • 统计最大/最小/平均年龄
    select max(age),min(age),avg(age) from table_1

• 筛选 having、where

• 只想查看A公司的男女人数数据?

  • 统计A公司的男女人数
    select count(distinct id) from table_1 where company = 'A' group by gender;
  • 统计各公司的男性平均年龄，并且仅保留平均年龄30岁以上的公司
    select company,avg(age) from table_1 where gender = "M" group by company having avg(age)>30

• 排序 order by

  • 希望查询结果从高到低/从低到高排序?
    • 按年龄全局倒序排序取最年长的10个
      select id,age from table_1 order by age DESC limit 10;

• 条件 case when

  • 将数值型的变量转换为类型的变量

  select id 
  case
  when CAST(salary as float)<50000 then "5万"
  when CAST(salary as float)>=5000 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;
  

cast是将salary转化为浮点数，防止是字符串不能计算

再举一个例子：

• group by后面为什么可以使用别名?
  • 前面说SQL语句的执行顺序是group by在select之前
  • 为什么这里可以在group by之后使用select里面设置的别名呢?
    • 原因在于MySQL对查询做了加强处理，所以允许在group by中使用别名
    • 但是在RDBMS中(比如Oracle)别名的使用都是严格遵照SQL执行顺序的——group by后面不能使用别名。

2.2.3 基础进阶（开窗函数）

• 窗口函数是什么

  • 窗口这个概念，可以简单理解为记录集合，或者分区
  • 窗口函数也就是在满足某种条件的记录集合上执行的特殊函数
    • 对于每条记录都要在此窗口内执行函数
  • 普通聚合函数vS窗口函数
    • 本质上说，窗口函数还是聚合运算。只不过它更具灵活性。它对数据的每一行，都使用与该行相关的行进行计算并返回计算结果
    • 二者区别：聚合函数是将多条记录聚合为一条；而窗口函数是每条记录都会执行，有几条记录执行完还是几条。
    • 聚合函数也可以用于窗口函数

• 窗口函数语法

  • 开窗函数名 ([]) over ([partition by ] [order by [desc]] [])
    • over是关键字，用来指定函数执行的窗口范围
    • partition子句:窗口按照那些字段进行分组，窗口函数在不同的分组上分别执行
    • order by子句:按照哪些字段进行排序，窗口函数将按照排序后的记录顺序进行编号。可以和partition子句配合使用，也可以单独使用。

• MySQL开窗函数的种类
  
  最常用的是前三个。

3.大厂数据分析SQL面试精讲

3.1 面试常考基本题型

1. 题型1【查询不在表里的数据】
   这里有两张表，一张是学生表Table_1，一张是已经选课了的学生的信息表Table_2。现在要求找出还没有选课的同学。
   
   解题思路：

   • 首先认真读题:还没有选课的学生有哪些?
     所有学生信息在Table_1中·已经选课的学生在Table_2中；那么没有选课的学生就是在Table_1中；但是不是在Table_2中
   • 判断是否需要多表连接
     数据设计两张表，需要多表连接
   • 使用哪种连接方式
     在A中不在B中，选择左连接
   • 确定连接主键
     Table_1和Table_2共有的字段为学生编号，确定学生编号字段为主键

   完整代码：
   
   同类题型——LeetCode数据库【183.从不订购的客户】

2. 题型2【查找第N高的数据】
   有一张“成绩表"Table_1，包含学生编号，选修课程的编号和成绩信息。现在需要找出某课程成绩第二高的学生成绩。这里以课程编号为“01"的同学为例。如果不存在第二高成绩的学生，那么查询应返回null。
   
   解题思路:

   • 先找出所有选修"01"课的学生成绩
     select * from table_1 where 课程编号=‘01’；
   • 查找"01"课程成绩的第二名
     考虑到成绩可能一样，所以需要使用distinct成绩进行去重。

   select distinct 成绩 from table_1 
   where 课程编号=‘01’
   order by 课程编号，成绩 desc
   limit 1 offset 1；
   

   • 考虑第二高的成绩不存在的情况
     如果不存在第二高成绩的学生，那么查询应返回null。
     使用ifnull函数可以满足要求
     – select ifnull(第二步的结果,null) as '01课第二名成绩';

   完整代码

   # 利用ORDER BY排序，再利用Limit限制offset偏移，排除只有1个值情况结合IFNULL
   SELECT
   IFNULL(
   (SELECT DISTINCT 成绩
   FROM Table_1
   ORDER BY 成绩 DESC
   LIMIT 1 OFFSET 1),
   NULL)AS "01课程第二高的成绩";
   

知识点

• limit 2,1和limit 2 offset 1的区别是什么?
  limit 2,1为跳过2条取出1条数据，limit后面是从第2条开始读，读取1条信息，即读取第3条数据
  limit 2 offset 1从第1条(不包括)数据开始取出2条数据，limit后面跟的是2条数据，offset后面是从第1条开始读取，即读取第2,3条。
• 判断是否为null–ifnull函数

3. 题型3【分组排序问题】
   下图是成绩表Table_1中的内容，记录了每个学生学生编号，课程编号和成绩。现在需要根据成绩来排名，如果两个分数相同，那么排名要是并列的。
   比如题目中的成绩从大到小排序应该是80,80,76,70,50,31。
   分数相同排名并列，那么6位同学的排序应该是1,1,3,4,5,6。
   
   解题思路:

   • 遇到分组排序问题，可以考虑使用开窗函数
   • 根据题干的要求两个分数相同，那么排名要是并列的。
   • 选择合适的开窗函数―—rank()函数

   完整代码

   select *,
   rank() over (order by 成绩 desc) as "排名"
   from table_1;
   

   知识点
   rank, dense_rank, row_number三种开窗函数的区别

   • ROW_NUMBER():顺序排序——1、2、3
   • RANK():并列排序，跳过重复序号―—1、1、3
   • DENSE_RANK():并列排序,不跳过重复序号―—1、1、2

   同类题型——LeetCode数据库【178.分数排名】

4. 题型4【连续出现N类问题】
   下面是某班级学生的某课程的成绩表(表名Sscore，列名:学号、成绩)，使月SQL查找所有至少连续出现3次的成绩。
   
   解题思路:

   • 如何理解连续出现3次?
     首先有三位同学的学号依次递增
     其次，这三位的同学的成绩相等
   • 如何解决?
     使用三张相同的表自连接可以将判断三列相等的问题转化为同一行3个值相等的问题
     

   参考代码1

   select distinct a.成绩 as 连续出现3次的成绩
   from sscore as a,
   Sscore as b,
   Sscore as c
   where a.学号=b.学号-1
   and b.学号= c.学号-1 and a.或绩=b.成绩and b.成绩= c.成绩;
   

   参考代码2

   SELECT 成绩, max(rk) AS 成绩_cnt
   FROM
   (SELECT 成绩, row_number() over(PARTITION BY成绩) AS rk --先计算出排名
   FROM Sscore) t
   GROUP BY t.成绩--根据最大的排名来知道“成绩"的出现次数
   HAVING 成绩_cnt >= 3;--用排名进行筛选
   

   知识点

   同类题型——LeetCode数据库【180.连续出现的数字】

3.2 大厂面试原题讲解

参考：数据分析面试通关