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
date | homepage | uv |
|---|---|---|
2018-03-05 | mp | 10 |
2018-03-05 | zby | 20 |
2018-03-06 | mp | 40 |
2018-03-07 | zby | 25 |
表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 | 关联的信息必须左右边数据集有一方存在即可 |