大数据中full_dim应用
作为小白,真的是第一次听说full_dim,在懵逼状态中学习,跟着大佬的脚步,一步一个脚印,努力前行。
今天分享一下我对full_dim的认识
所谓full_dim,表面之意就是全部的维度,实际上full_dim就是维度的全部组合,那么full_dim到底有什么作用,下面我就用一个例子来讲解一下。
with detail as (
select '2020-01-01' as dt , 1 as dept , 1 as sale_amt
union all select '2020-01-02' as dt , 1 as dept , 2 as sale_amt
union all select '2020-01-03' as dt , 2 as dept , 3 as sale_amt
union all select '2020-01-05' as dt , 2 as dept , 5 as sale_amt
)
select a.dt,a.dept,sum(coalesce(a.sale_amt,0))over(partition by a.dept order by a.dt desc) as agg_amt from detail a;
执行此段sql,得到如下结果:
分析:从上面的图片中可以看出,因为原始数据中没有2020-01-04的数据,因此上图中缺少2020-01-04的数据,但是也有可能出现另外一种情况就是,2020-01-04有数据,如果通过某些字段进行group by和partition by 的恰好该字段为null,那么很有可能会丢失数据。
上面这种问题可以通过full_dim的方式解决,请看以下sql。
with detail as (
select '2020-01-01' as dt , 1 as dept , 1 as sale_amt
union all select '2020-01-02' as dt , 1 as dept , 2 as sale_amt
union all select '2020-01-03' as dt , 2 as dept , 3 as sale_amt
union all select '2020-01-05' as dt , 2 as dept , 5 as sale_amt
),full_dim as (
select '2020-01-01' as dt,1 as dept
union all select '2020-01-01' as dt,2 as dept
union all select '2020-01-02' as dt,1 as dept
union all select '2020-01-02' as dt,2 as dept
union all select '2020-01-03' as dt,1 as dept
union all select '2020-01-03' as dt,2 as dept
union all select '2020-01-04' as dt,1 as dept
union all select '2020-01-04' as dt,2 as dept
union all select '2020-01-05' as dt,1 as dept
union all select '2020-01-05' as dt,2 as dept
)
select a.dt
, a.dept
,coalesce( b.sale_amt ,0) as sale_amt
,sum(coalesce(b.sale_amt ,0)) over( partition by a.dept order by a.dt ) as agg_amt
from full_dim as a
left join detail as b
on a.dt = b.dt
and a.dept = b.dept
执行上面的sql,得到如下结果:
这段sql实现的是以dept分组,sale_amt进行累计求和的功能。从上面的结果可以看的出,虽然没有2020-01-04的数据,但是通过关联full_dim,在最终的结果展示中可以有2020-01-04的记录,但是对应的字段值为0,完美解决上面的问题。