这篇文章主要是对数仓工作的一个总结。数仓工作中,除了数据架构另外就是数据模型实施了。今天总结一下工作中遇到的大部分实现场景。
今天总结的也是遇到最常见的,用得最多的,也是需要掌握的。话不多说,上菜。
基础的sql语法以及一些时间的加减处理这里就不说了。放张我整理的图就行。然后挑重要的说。
第一个要说的就是UDF、UDAF、UDTF函数。
udf全称是User Defined Function,即用户自定义函数。通常是为了满足我们特定场景的一些需求。当然Hive也提供了很多内置的函数包,如上图整理的。当不能满足我们要求的时候就需要自己去写了。
这里简单说些udf的开发,后面我们再单独写一篇文章来具体实现UDF包。首先就是需要添加hive-exec依赖包就可以了。继承UDF方法类。然后打成jar引入就可以了。
接下来要说的是空值填充处理。
以前遇到同时代码里面有写if的也有写case when的。nvl函数能够直接对空值进行赋值。nvl(val,def_val)。当val为空时,默认为def_val的值。当然还有coalesce(val1,val2,val3,..),它满足的条件是当val1是空则选择val2,若val2继续为空则选择val3。。。
不得不说的窗口函数
在业务实现中,开窗函数可谓是用得最多的了,特别是用于值的分组统计,用得好的话,可能帮助我们少写很多的子查询。
它和聚合函数不同的是,它能够每一行都返回。窗口的统计值。
基础语法是:分析函数() over(partition by field1 order by field2 )
partition by是我们的聚合字段,也就是分组字段,order by 是分组内的数据按照该字段进行排序。
一些 sum、avg、min、max还可以在窗口统计中进行边界处理,就是between and。格式:ROWS BETWEEN xx and xx
current row:当前所在行
preceding n:当前行再向前n行统计
following n:再向后n行统计
unbounded:所有的行
unbounded preceding:第一行
unbounded following:最后一行。
接下来用一些实例来说窗口函数,模拟一些数据
select * from windows_tmp_tab order by other_field;
sum窗口函数,排序后是按照排序进行累计显示,不排序则是整个窗口的sum。min,max,avg类似
Select
group_field,
rank_field,
sum(rank_field) over(partition by group_field order by rank_field) sum_rank
from windows_tmp_tab
sum的窗口内加上between and
Select
group_field,
rank_field,
sum(rank_field) over(partition by group_field order by rank_field rows between 3 PRECEDING AND CURRENT ROW) tmp_rank
from windows_tmp_tab;
AVG的窗口使用,和sum一致
Select
group_field,
rank_field,
avg(rank_field) over(partition by group_field order by rank_field) sum_rank
from windows_tmp_tab
Max窗口,同样也是排序后是按照当前排序字段进行最大,不排序是整体最大
Select
group_field,
rank_field,
max(rank_field) over(partition by group_field order by rank_field) sum_rank
from windows_tmp_tab;
Select
group_field,
rank_field,
max(rank_field) over(partition by group_field) sum_rank
from windows_tmp_tab;
count分组计数统计
Select
group_field,
rank_field,
count(rank_field) over(partition by group_field) sum_rank
from windows_tmp_tab;
Row number分组排序,取Top,最常见的场景就是取某一个top n数据,遇到同样大小的也会进行index+1
Select
group_field,
rank_field,
row_number() over(partition by group_field order by rank_field) tmp_rank
from windows_tmp_tab;
rank() over 也是分组排序,但是遇到相同大小的会共用相同的index,但是下一次的index会进行index+n,n取决于有多少个相同的数。
Select
group_field,
rank_field,
rank() over(partition by group_field order by rank_field) tmp_rank
from windows_tmp_tab;
dense_rank() over 同样是分组排序,但是遇到相同大小的会共用相同的index,但是下一次的index会进行index+1,进行自然数排序。
ntile,将分组进行切片。然后按照排序返回对应切片的值。可以用于这样的场景,取某分组top1/2的数据
Select
group_field,
rank_field,
ntile(2) over(partition by group_field order by rank_field) tmp_rank
from windows_tmp_tab;
cume_dist 返回分组内小于当前值的比例,可以用于取每个部门工资小于1w的比例。
Select
group_field,
rank_field,
cume_dist() over(partition by group_field order by rank_field) tmp_rank
from windows_tmp_tab;
lag(col,n,def_val)取统计窗口的值排序后的上n行的值,类似于python中的diff函数。如果前n行为null则显示def_val。一般以下场景,排序后的前一个值,前n个值的场景。
Select
group_field,
rank_field,
lag(rank_field,1,null) over(partition by group_field order by rank_field) tmp_rank
from windows_tmp_tab;
lead(col,n,def_val)同lag。只不过它取得是后n行的值。
Select
group_field,
rank_field,
lead(rank_field,1,-999) over(partition by group_field order by rank_field) tmp_rank
from windows_tmp_tab;
first_value分组排序后,取当前窗口的第一个值。
Select
group_field,
rank_field,
first_value(rank_field) over(partition by group_field order by rank_field) tmp_rank
from windows_tmp_tab;
last_value分组排序后,取当前窗口的最后一个值。
Select
group_field,
rank_field,
last_value(rank_field) over(partition by group_field order by rank_field) tmp_rank
from windows_tmp_tab;
行转列:group by + case when
select
group_field,
max(case when other_field='语文' then rank_field else null end) as yuwen,
max(case when other_field='数学' then rank_field else null end) as shuxue,
max(case when other_field='英语' then rank_field else null end) as yingyu
from tmp_tab group by group_field
还有一些with,cache table的用法用于创建临时表这里就不再介绍了。
今天的分享到此结束了,欢迎补充。
更多文章请关注公众号: