SQL 编写技巧总结（持续更新中）

UDF 函数整理

用处	语法
获取json数据	get_json_object(字段,'$.x.x')
截取字符串	substr(字段,从第几位开始截取)
字符串日期相减	DATEDIFF(日期1,日期2)【yyyy-MM-DD格式】
将一个复杂类型ARRAY或者MAP转换为单行记录	SELECT explode(myCol) AS myNewCol FROM myTable;
将使用的表与UTF等函数做结合关联	LATERAL VIEW udtf(expression) tableAlias AS columnAlias (‘,’ columnAlias)* fromClause: FROM baseTable (lateralView)*

参考 http://blog.csdn.net/bitcarmanlee/article/details/51926530

提升效率

• 查询必须加分区字段
• 连表查询，查询条件尽量放在子查询去
• 子查询不要出现select *

列转行

在一些情况下，我们可能需要列转行的操作
看下面一个case

表TB_A结构如下

date	homepage	uv
2018-03-05	mp	10
2018-03-05	zby	20
2018-03-06	mp	40
2018-03-07	zby	25

我们的需求是统计每天所有门票（mp）和周边游(zby)的所有uv

目标结构是这样

mp	zby
50	45

这个时候我们可以这么做

• step1:case when then实现列转行

• step2:sum聚合实现求和，去除null

select sum(mp) mp,sum(zby) zby
from    (select 
        case when homepage = 'mp' then uv end 'mp'
        case when homepage = 'zby' then uv end 'zby'
    from TB_A)

Union

将两个结果集的数据做纵向连接，可以直接作为一个新的结果集，需要两个结果集的返回字段一致

[SQL 1]
union/union all
[SQL 2]

分为Union 和 Union All，二者区别如下：

用法	区别
union	取两个结果集的唯一值
union all	直接连接不去重

Join

将两个结果集做横向连接，作为一个新的数据源，放在[from] 里面语句段里面

select [select statement]
from 
t1 left/right/inner/full/outer join t2 on [on statement]
where
[where statement]

主要分为 left/right/inner/full/outer join，区别如下

用法	区别
left join(left outer join )	关联的信息左边数据集有数据即可返回，右边没有返回null
right join(right outer join )	关联的信息右边数据集有数据即可返回，右边没有返回null
inner join	关联的信息必须左右边数据集都存在
outer/full join	关联的信息必须左右边数据集有一方存在即可

grouping sets

grouping sets 可以实现多重聚合

image.png

以上这段sql可以使用下面的代码简单的实现

image.png