数据分析SQL常考题型及大厂例题

2021/6/17 2:22:23

编程Tag： 函数 table select SQL distinct 成绩 例题 常考

本文主要是介绍数据分析SQL常考题型及大厂例题，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

文章目录

        • 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函数

  1. 题型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.分数排名】

  2. 题型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 大厂面试原题讲解

参考：数据分析面试通关



这篇关于数据分析SQL常考题型及大厂例题的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！


相关编程文章

更多>
本站官方QQ
官方QQ:
23002807

沪ICP备2023033072号-1

     

扫一扫关注最新编程教程