数仓必须会的函数总结

    这篇文章主要是对数仓工作的一个总结。数仓工作中，除了数据架构另外就是数据模型实施了。今天总结一下工作中遇到的大部分实现场景。

    今天总结的也是遇到最常见的，用得最多的，也是需要掌握的。话不多说，上菜。

    基础的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_rankfrom  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的用法用于创建临时表这里就不再介绍了。

今天的分享到此结束了，欢迎补充。

更多文章请关注公众号：