Hive 实战之累计计算(51)

累计计算

对于数仓而言，我们一般都是增量计算，增量数据同步、增量数据计算，然后我们产出每天的指标，大多数情况下都是这样的，但是我们也有很多的指标需要把过去的数据和当前的数据进行累计，从而产出计算结果。

接下来我们分析一下常用的累计计算，然后关注一下如何去从中抽出一个通用的计算模型

数据准备/需求分析

day	open_cnt	click_cnt
+----+---------+--------+
2021-06-10	100	50
2021-06-11	120	60
2021-06-12	150	50
复制代码

这里我们就模拟了两个指标 open_cnt 打开次数，click_cnt 点击次数，这里的指标是按照天计算的，也就是说我们每天都计算打开次数和点击次数,下面是模拟数据的SQL

with daily_index as(
select
'2021-06-10' as day,100 as open_cnt,50 as click_cnt
union all
select
'2021-06-11' as day,120 as open_cnt,60 as click_cnt
union all
select
'2021-06-12' as day,150 as open_cnt,50 as click_cnt
)
select * from daily_index;
复制代码

现在我们的需求变了，我们还需要计算出截止到当前的累计值,累计打开次数和累计打开人数

需求很明确，接下来我们分析一下怎么做

首先今天的open_cnt和click_cnt 计算出来之后，我们计算今天的total_open_cnt和total_click_cnt，我们需要将昨天的total_open_cnt和total_click_cnt与今天的open_cnt和click_cnt分别相加即可，当然这里的相加可以是另外一种方法就是将过去全部的open_cnt相加也可以，我们将这两种方式称之为增量累加和全量累加

全量累加

首先我们还是将我们的数据存入到以前的表中daily_index,也就是说我们这个表还是存的是每天的数据，不包含累加数据，然后等我们今天的数据入库之后，我们开始计算累加指标，存储到另外一个表中，然后使用的时候我们可以将这两个表关联起来使用，我们也可以将这累加指标和非累加指标同时查出来放到一个新表

select
  *,
  sum(open_cnt)  over (partition by 1 order by day) as total_open_cnt,
  sum(click_cnt)  over (partition by 1 order by day) as total_click_cnt
from daily_index;
复制代码

day	open_cnt	click_cnt	total_open_cnt	total_click_cnt
+----+---------+----------+---------------+----------------+
2021-06-10	100	50	100	50
2021-06-11	120	60	220	110
2021-06-12	150	50	370	160
复制代码

可以和我们上面分析的时候进行一下对比，可以看到我们这个逻辑是正确的，但是这样有没有问题呢？有那就是资源浪费，每天都需要进行全部累加，但其实这个问题不太严重，因为指标是按天计算的，所以数据量肯定不大。其实全量累加我们除了使用窗口函数之外我们还可以使用自关联

select
  a.day,max(a.open_cnt),max(a.click_cnt),
  sum(b.open_cnt) as total_open_cnt,
  sum(b.click_cnt) as total_click_cnt
from
  daily_index a
inner join
  daily_index b
on
  a.day>=b.day
group by
  a.day
;
复制代码

day	_c2	_c3	total_open_cnt	total_click_cnt
+----+----+----+---------------+----------------+
2021-06-10	100	50	100	50
2021-06-11	120	60	220	110
2021-06-12	150	50	370	160
复制代码

增量累加

增量累加的实现思路我们分析过了，现在我们就看一下怎么实现，其实实现也非常简单，我们也可以先计算每天的数据，然后再计算累加的数据

创建结果表

create table daily_index(
  ds string,
  open_cnt bigint ,
  click_cnt bigint ,
  total_open_cnt bigint ,
  total_click_cnt bigint
)
;
复制代码

计算当日指标数据

需要注意的是我们的逻辑kennel需要多次运行所以我们要保证幂等

select
  if(b.ds is not null,b.ds,a.ds) as ds,
  if(b.ds is not null,b.open_cnt,a.open_cnt) as open_cnt,
  if(b.ds is not null,b.click_cnt,a.click_cnt) as click_cnt
from
daily_index a
full join
(
  select
  '2021-06-10' as ds,100 as open_cnt,50 as click_cnt
)b
on
  a.ds=b.ds
;
复制代码

我们采用了full join，可以将历史数据和今日新增数据进行合并，形成截止到当前时间的新的数据集

计算累计指标数据

我们可以在上面的基础上，计算累计指标

select
    a.ds,
    a.open_cnt,
    a.click_cnt,
    if(b.total_open_cnt is not null ,b.total_open_cnt+a.open_cnt,a.open_cnt) as total_open_cnt,
    if(b.total_click_cnt is not null ,b.total_click_cnt+a.click_cnt,a.click_cnt) as total_click_cnt
from (
  select
    if(b.ds is not null,b.ds,a.ds) as ds,
    if(b.ds is not null,b.open_cnt,a.open_cnt) as open_cnt,
    if(b.ds is not null,b.click_cnt,a.click_cnt) as click_cnt
  from
    daily_index a
  full join
  (
    -- 当日新增数据
    select
      '${ds}' as ds,100 as open_cnt,50 as click_cnt
  )b
  on
  a.ds=b.ds
) a
left join (
  select
    ds,
    total_open_cnt,
    total_click_cnt
  from
    daily_index
) b
on
	-- 前一天的数据
  a.ds=date_add(b.ds,1)
;
复制代码

ds	open_cnt	click_cnt	total_open_cnt	total_click_cnt
+---+---------+----------+---------------+----------------+
2021-06-10	100	50	100	50
复制代码

需要注意的是，我们这里没次计算都依赖前面一天的数据，所以我们需要将这些数据插入到数据库里面去才能验证我们上面的分析

模拟三次插入

需要注意的是你需要将ds 和当日新增数据换成我们上面的数据，连续执行三次(模拟每天的离线调度)

insert overwrite table daily_index
select
    a.ds,
    a.open_cnt,
    a.click_cnt,
    if(b.total_open_cnt is not null ,b.total_open_cnt+a.click_cnt,a.open_cnt) as total_open_cnt,
    if(b.total_click_cnt is not null ,b.total_click_cnt+a.click_cnt,a.click_cnt) as total_click_cnt
from (
  select
    if(b.ds is not null,b.ds,a.ds) as ds,
    if(b.ds is not null,b.open_cnt,a.open_cnt) as open_cnt,
    if(b.ds is not null,b.click_cnt,a.click_cnt) as click_cnt
  from
    daily_index a
  full join
  (
    -- 当日新增数据
    select
      '${ds}' as ds,100 as open_cnt,50 as click_cnt
  )b
  on
  a.ds=b.ds
) a
left join (
  select
    ds,
    total_open_cnt,
    total_click_cnt
  from
    daily_index
) b
on
  a.ds=date_add(b.ds,1)
;
复制代码

查看结果

可以看到我们的结果和上面是完全一样的

ds	open_cnt	click_cnt	total_open_cnt	total_click_cnt
+---+---------+----------+---------------+----------------+
2021-06-10	100	50	100	50
2021-06-11	120	60	220	110
2021-06-12	150	50	370	160
复制代码

增量累加的升级

其实我们可以看到上面的计算还是存在着一定的资源浪费，就是我们在计算累计值的时候，例如我要计算12号的累计值，但是我们把12号之前的累计值也重新算了一遍，其实这样也是不完美的，所以我们可以再对代码进行以下改进。

主要思路是这样的，我们发现如果我们不是第一次计算的话,我们可以认为前面一天的数据都是有的，那么我我们查询昨天的累计指标的时候可以只查询前一天的，而不是前面的全部

select
  ds,
  total_open_cnt,
  total_click_cnt
from
  daily_index
where
	ds=date_sub('${ds}',1)
复制代码

初始化

为了方便计算我们需要先初始化一天的数据进去

insert overwrite table daily_index
select
  '2021-06-10',100,50,100,50
;
复制代码

模拟两次插入

因为前面我们已经初始化了一天的数据，这里我们只需要插入两次即可

insert overwrite table daily_index
select
  a.ds,
  a.open_cnt,
  a.click_cnt,
  if(b.ds is null,a.total_open_cnt,b.total_open_cnt+a.open_cnt) as total_open_cnt,
  if(b.ds is null,a.total_click_cnt,b.total_click_cnt+a.click_cnt) as total_click_cnt
from (
  select
    if(b.ds is not null,b.ds,a.ds) as ds,
    if(b.ds is not null,b.open_cnt,a.open_cnt) as open_cnt,
    if(b.ds is not null,b.click_cnt,a.click_cnt) as click_cnt,
    a.total_open_cnt,
    a.total_click_cnt
  from
    daily_index a
  full join
  (
  -- 当日新增数据
    select
    '${ds}' as ds,150 as open_cnt,50 as click_cnt
  )b
  on
    a.ds=b.ds
) a
left join (
  select
    ds,
    -- flag 一个标示
    1 as flag,
    total_open_cnt,
    total_click_cnt
  from
    daily_index
  where
    ds=date_sub('${ds}',1)
) b
on
a.ds=date_add(b.ds,1)
;

复制代码

查看结果

ds	open_cnt	click_cnt	total_open_cnt	total_click_cnt
+---+---------+----------+---------------+----------------+
2021-06-10	100	50	100	50
2021-06-11	120	60	220	110
2021-06-12	150	50	370	160
复制代码

总结

累计计算是我们比较常见的一种计算，主要的实现方式有以下两种

1. 全量累加
2. 增量累加

但是你如果追求更完美的解决方案可以使用增量累加的升级版本，但是就是稍微麻烦一些，需要初始化一次。