大数据中full_dim应用

大数据中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，完美解决上面的问题。