SQL

https://mp.weixin.qq.com/s/M8EIkNTQl3Sc-5bWPSqC0Q

JOIN连接

INNER JOIN

（取交集）只有进行连接的两个表中都存在与连接标准相匹配的数据才会被保留下来。

LEFT JOIN

（保留左表）左连接操作符左边表中符合 WHERE 子句的所有记录将会被返回，右表中如果没有符合 ON 后面连接条件时，那么从右边表指定选择的列的值将会是 NULL。

RIGHT JOIN

（保留右表）右连接会返回右边表所有符合 WHERE 语句的记录。左表 中匹配不上的宇段值用 NULL 代替。

FULL JOIN

（取并集）最后介绍的完全外连接将会返回所有表中符合 WHERE 语句条 件的所有记录。如果任一表的指定宇段没有符合条件的值的话，那么就使用 NULL 替代。

ON 和 WHERE

https://www.runoob.com/w3cnote/sql-join-the-different-of-on-and-where.html
1、 on 条件是在生成临时表时使用的条件，它不管 on 中的条件是否为真，都会返回左边表中的记录。
2、where 条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有 left join 的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。
原因就是 left join、right join、full join 的特殊性，不管 on 上的条件是否为真都会返回 left 或 right 表中的记录，full 则具有 left 和 right 的特性的并集。 而 inner join 没这个特殊性，则条件放在 on 中和 where 中，返回的结果集是相同的。

例题

编写一个 SQL 查询，查找所有至少连续出现三次的数字，表名为：test，表结构如下：

select DISTINCT a.id
from test a
         inner join test b on a.id + 1 = b.id
         inner join test c on a.id + 2 = c.id
where a.num = b.num
  and a.num = c.num;

聚合函数

聚合函数是必考知识点，考察应聘者对分组的理解及聚合函数的掌握情况。
GROUP BY 语句通常会和聚合函数一起使用，按照一个或者多个列对结果进行分组， 然后对每个组执行聚合操作。
HAVING子句允许用户通过一个简单的语法完成原本需要通过子查询才能对GROUP BY语句产生的分组进行条件过滤的任务。
常用聚合函数：

WHERE和GROUP BY的区别

where 和having之后都是筛选条件，但是有区别的：
1.where在group by前， having在group by 之后
2.聚合函数（avg、sum、max、min、count），不能作为条件放在where之后，但可以放在having之后

例题

找出num出现次数大于等于2次的num

select num, count(num) as cnt
from test
group by num
having count(num) >= 2;

窗口函数

窗口函数是数据分析师常用函数之一，故也成为各大厂必考知识点。
窗口函数也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理。窗口函数的基本语法如下：

<窗口函数>()
over (partition by <用于分组的列名> 
order by <用于排序的列名>)

<窗口函数>的位置，可以放以下两种函数：
专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。
聚合函数，如sum. avg, count, max, min等

例题

Employee 表包含所有员工信息，每个员工有其对应的工号 Id，姓名 Name , 工资 Salary 和部门编号 DepartmentId 。

Department 表包含公司所有部门的信息。

编写一个SQL查询，找出每个部门获得前三高工资的所有员工。例如，根据上述给定的表，查询结果应返回：

▼ 解题思路：
我们借助窗口函数中的partition by置顶窗口的分类方式，然后通过RANK()函数进行排序，最后输出排名前三的员工信息。

列转行作为SQL高级应用函数，是各大厂高频考点，可以测试应聘者对SQL的掌握程度。下面我们主要讲解Lateral View的原理及用法。

• Lateral View 用于和UDTF函数【explode,split】结合来使用。
• 首先通过UDTF函数将数据拆分成多行，再将多行结果组合成一个支持别名的虚拟表。
• 主要解决在select使用UDTF做查询的过程中查询只能包含单个UDTF，不能包含其它字段以及多个UDTF的情况。
• 语法：LATERAL VIEW udtf(expression) tableAlias AS coAlias (‘,’ colAlias)

列转行

Movie表记录了各大电影的类型情况，数据如下：

需要转换成以下格式：

▼ 解题思路：本题只需要借助Lateral View进行拆解重组即可。