数据分析 | 实习、秋招中SQL常考知识点

实习到秋招，经历了50+面试，特别是一面很喜欢考点sql知识点，下面是我面试数据分析岗位，在美团、快手、猿辅导等互联网公司，以及银行，美的等实业公司中遇到的常考SQL知识点

1.表连接，几种join的区别？

• INNER JOIN（内连接）：

结果是左表和右表关联字段相同的交集。即查询结果为下图重合部分的数据；

• LEFT JOIN（左连接）：

结果是左表的所有数据+右表中和左表重合部分的数据，即以左表为准，左表的数据全部查询，而右表的数据则只显示匹配的数据，不匹配的数据为null；

• RIGHT JOIN（右连接）：

结果右表的所有值，左表中有匹配的则有值，不匹配的则null表示，和left join 相反；

• FULL OUTER JOIN（全连接）：

结合了LEFT JOIN和RIGHT JOIN的结果，并使用NULL值作为两侧缺失匹配结果。

这道题目基本在面快手、美团等的实习，还有渤海银行、猿辅导等公司的数据分析秋招中都考察过。

2. 排序窗口函数有什么，之间的区别？

• ROW_NUMBER、RANK、DENSE_RANK 专用排序的窗口函数、

• 语法

函数（） over(partition by "要分组的列"

order by "要排序的列" desc/asc)

• 区别—排序中用相同数据出现时（如数据是5，5，3，降序排列）

○ ROW_NUMBER

相同数据,先查出的排名在前,没有重复值，排名为1，2，3；

○ RANK

跳跃排序,相同数据排名相同,比如并列第1,则两行数据都标为1,下一位将是第3名.中间的2被跳过。排名存在重复值113；

○ DENSE_RANK

连续排序的,比如两条并列第1,则两行数据都标为1,下一个排名将是第2名，排名为1，1，2。

3. sql语句的执行顺序是什么?

• 执行顺序

from →join →on →where →group by→avg/sum/…→having→select→distinct→order by→limit

• 具体含义

①from：需要从哪个数据表检索数据，如果有join则对 FROM 子句中的前两个表执行笛卡尔积(交叉联接)

生成临时表(n×m行)；

② on ：对以上临时表进行条件筛选；

③ join：补充左表或右表保持完整。如果有关联多个表，则中间表对下个表继续以上两步的操作；

④where:过滤表中数据的条件；

⑤group by：如何将上面过滤出的数据分组；

⑥avg/sum/…：聚合函数；

⑦having：对上面已经分组的数据进行过滤的条件；

⑧select：查看结果集中的哪个列，或列的计算结果；

⑨distinct：去重

⑩order by：按照什么样的顺序来查看返回的数据

⑪limit：限制查询结果返回的数量

4. 去重方法

• distinct

对单字段、多字段去重，必须放在第一个查询字段前，效率比较低，一般与 count 配合用来计算条数；

（注意多字段的话，即字段的不同组合作为一个整体去重，而不是依次对每个字段去重）

• group by

将去重的单/多字段放在groupby后，select查询出去重结果，也是整体去重；

• 窗口函数

对要去重的字段进行分组后排序，并且将排名设置为1

<窗口函数> over (partition by <去重字段> order by <用于排序的列名>)

5. 如何确认数据是否取对？---这也是实际工作中常见问题

• 有报错

逗号，字段名，groupby没放在select中，分区等原因；具体看报错提示

• 无报错

i. 平时积累，常见的数据量级，比如DAU

ii. 与已有数据指标做对比，判断是否合理

无报错的错误通常是逻辑原因导致的，需要逐步排查出逻辑问题。

• 出错后的做法：

i. 简化

有约束条件的去掉约束条件，有多个表连接/嵌套的去掉连接/嵌套，只保留最简单的表格，

从最简单的情况开始测试，再不断往上添加条件，直到出错，从而定位到错误。

举个例子，table_a left join table_b出错，先分别检查table_a和table_b取出的数据是否正确,再检查join之后的结果

ii. 输出信息

输出信息是为了帮助我们更好地识别错误。

由于工作中使用的数据量通常比较大，可以采用输出行数，输出几条数据的方法更方便地帮助检查错误。

举个例子，table_a left join table_b出错，先看table_a有多少行，left join之后行数有没有变化

7. sql如何进行性能优化

• EXPLAIN

在select 语句前加上explain，并不会真正执行sql语句，而是可查看sql的性能瓶颈信息，

针对性地进行优化；

• 区分in和exists

如果是exists，那么以外层表为驱动表，先被访问；

如果是IN，那么先执行子查询。

所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

• 避免在where子句中对字段进行null值判断

对于null的判断会导致引擎放弃使用索引而进行全表扫描。

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

• in 和 not in 也要慎用，否则会导致全表扫描

• 避免在where子句中对字段进行表达式操作 / !=或<>操作符 / 对字段进行函数操作

将导致引擎放弃使用索引而进行全表扫描

• 尽量用union all代替union

7. 日期函数有哪些？——根据知乎喵宁一的文章整理