SQL常用进阶命令

前言

这里的SQL包括但不限于Hive、MySQL数据库语言。主要用于记录工作中常用但在我的文章：Mysql常用知识点整理 里面没有提及的内容。

Select 语句

left join VS left semi join

left join即左连接，在MySQL中的命令（Hive中等价命令：left outer join），返回的是左表中的所有记录，即使右表中没有与之匹配的记录。当右表中有重复记录时慎用，因为使用该命令会重复返回左边中满足条件的记录，容易造成数据膨胀。

left semi join即左半连接，是Hive中的命令，返回的是左表中的记录，右表仅仅用于过滤左表记录，不出现在结果集当中。常用与右表数据有重复记录的情况。与left join/left outer join相比较，它们的实现原理有差异，前者遇到右表重复记录，左表会跳过，而后者则会一直遍历。

例子：
表A

id	price
001	23
002	12
003	30
004	07

表B

id	color
001	白色
001	褐色
003	红色
003	黑色
003	黄色
004	白色

左连接left join

select * from 
A left join B //A left outer join B
on (A.id = B.id);

结果

id	price	color
001	23	白色
001	23	褐色
002	12	NULL
003	30	红色
003	30	黑色
003	30	黄色
004	07	白色

左半连接left semi join

select * from 
A left semi join B
on (A.id = B.id);

结果

id	price
001	23
003	30
004	07

Mapjoin

它常用于一个大表和一个或多个小表的JOIN，优化查询性能。

通常情况下，JOIN操作在Reduce阶段执行表连接。整个JOIN过程包含Map、Shuffle、Reduce三个阶段。

MAPJOIN在Map阶段执行表连接，而非等到Reduce阶段才执行表连接。这样就节省了大量数据传输的时间以及系统资源，从而起到了优化作业的作用。

例子：

select /*+ mapjoin(A) */
        A.price,
        B.price
from shop A join sale_detail B
on A.price < B.price or A.price + B.price < 200;

其他说明：

1. 使用MAPJOIN时，在引用小表或子查询时，需要引用别名。
2. MAPJOIN支持小表为子查询。
3. 在MAPJOIN中，可以使用不等值连接或者OR连接多个条件。
4. MAPJOIN中多个小表用逗号隔开，例如/*+MAPJOIN(a,b,c) */。

Lateral View

Lateral View和表生成函数（例如Split、Explode等函数）结合使用，它能够将一行数据拆成多行数据，并对拆分后的数据进行聚合。
例子：
表A

class_id	student_id
001	[12,34,25,78]
002	[07,21,53]
003	[01,49]

拆分student_id

select class_id, stdid
from A
LATERAL VIEW explode(student_id) stdTable AS stdid;

结果

class_id	stdid
001	12
001	34
001	25
001	78
002	07
002	21
002	53
003	01
003	49

聚合得到每班人数

select class_id, count(*) as num
from A
LATERAL VIEW explode(student_id) stdTable AS stdid
group by class_id;

结果

class_id	num
001	4
002	3
003	2

并集、交集、补集

并集：UNION ALL, UNION (DISTINCT)
交集：INTERSECT ALL, INTERSECT (DISTINCT)
补集：EXCEPT ALL, EXCEPT (DISTINCT)

使用说明：
带ALL表示返回所有记录，不带或者带DISTINCT表示去重后进行聚合操作
语法为select语句 UNION/INTERSECT/EXCEPT [ALL/DISTINCT] select语句

Grouping Sets

应用于多维度的聚合分析的场景，例如对a列，b列，以及a和b的组合均需聚合，此时若用多个UNION语句显得很冗余，代码不美观，这时可以用Grouping Sets。

例子：

SELECT a, b, c,COUNT(*)
FROM A
GROUP BY a, b, c GROUPING SETS((a, b), (c), ());

表示分别以a和b的组合列、c列、无指定列（等价于全表作为一类）作计数聚合运算

CUBE和ROLLUP可以认为是特殊的GROUPING SETS。CUBE会枚举指定列的所有可能组合作为GROUPING SETS，而ROLLUP会以按层级聚合的方式产生GROUPING SETS。

语法：

GROUP BY CUBE(a, b, c)  
--等价于以下语句。  
GROUPING SETS((a,b,c),(a,b),(a,c),(b,c),(a),(b),(c),())

GROUP BY ROLLUP(a, b, c)
--等价于以下语句。  
GROUPING SETS((a,b,c),(a,b),(a), ())

GROUP BY CUBE ( (a, b), (c, d) ) 
--等价于以下语句。 
GROUPING SETS (
    ( a, b, c, d ),
    ( a, b       ),
    (       c, d ),
    (            )
)

GROUP BY ROLLUP ( a, (b, c), d ) 
--等价于以下语句。
GROUPING SETS (
    ( a, b, c, d ),
    ( a, b, c    ),
    ( a          ),
    (            )
)

GROUP BY a, CUBE (b, c), GROUPING SETS ((d), (e)) 
--等价于以下语句。 
GROUP BY GROUPING SETS (
    (a, b, c, d), (a, b, c, e),
    (a, b, d),    (a, b, e),
    (a, c, d),    (a, c, e),
    (a, d),       (a, e)
)

GROUP BY grouping sets((b), (c),rollup(a,b,c)) 
--等价于以下语句。 
GROUP BY GROUPING SETS (
    (b), (c),
    (a,b,c), (a,b), (a), ()
 )

Distribute By

函数

explode函数

将一行数据转为多行的UDTF（User Defined Table Valued Function 自定义表值函数：用来解决一次函数调用输出多行数据的需求，是唯一能够返回多个字段的自定义函数）
使用说明：
explode(var)
如果参数var是ARRAY类型，则将列中存储的ARRAY转为多行。
如果参数var是MAP<K, V>类型，则将列中存储的MAP的每个Key-Value转换为包含两列的行，其中一列存储Key，令一列存储Value。