长周期指标的计算优化方案

实验背景

电子商务公司在电商数据仓库和商业分析场景中，经常需要计算最近N天的访客数、购买用户数、老客数等类似的指标。这些指标需要根据一段时间内的累积数据进行计算。

SELECT item_id --商品id 
  ,COUNT(DISTINCT visitor_id) AS ipv_uv_1d_001 
FROM 用户访问商品日志明细表 
WHERE ds <= ${bdp.system.bizdate} 
AND ds >=to_char(dateadd(to_date(${bdp.system.bizdate},'yyyymmdd'),-29,'dd'),'yyyymmdd') 
GROUP BY item_id;

当每天的日志量很大时，SELECT操作需要大量的Map Instance，运行上面的代码需要的Map Instance个数太多，甚至会超过99999个Instance的限制个数，导致Map Task无法顺利执行。

实验目的

在不影响性能的情况下计算长周期的指标。

影响性能的根源是多天汇总数据量过大，建议您使用构建临时表的方式对每天的数据进行轻度汇总，这样可以去掉很多重复数据，减少数据量。

实验方案

1. 构建中间表，每天汇总一次。

   对于上述示例，构建item_id+visitior_id 粒度的日汇总表，记作A。

   INSERT OVERWRITE TABLE mds_itm_vsr_xx(ds='${bdp.system.bizdate} ')
   SELECT item_id,visitor_id,count(1) AS pv 
   FROM
     (
     SELECT item_id,visitor_id
     FROM 用户访问商品日志明细表 
     WHERE ds =${bdp.system.bizdate} 
     GROUP BY item_id,visitor_id 
   ) a;

2. 计算多天的数据，依赖中间表进行汇总。

   对A进行30天的汇总。

   SELECT item_id
           ,COUNT(DISTINCT visitor_id) AS uv
           ,SUM(pv) AS pv
     FROM mds_itm_vsr_xx
     WHERE ds <= '${bdp.system.bizdate} '
     AND ds >= to_char(dateadd(to_date('${bdp.system.bizdate} ','yyyymmdd'),-29,'dd'),'yyyymmdd')
     GROUP BY item_id;

影响及思考

上述方法对每天的访问日志明细数据进行单天去重，从而减少了数据量，提高了性能。缺点是每次计算多天数据的时候，都需要读取N个分区的数据。

您可以通过增量累计方式计算长周期指标，不需要读取N个分区的数据，而是把N个分区的数据压缩合并成一个分区的数据，让一个分区的数据包含历史数据的信息。

场景示例

计算最近1天店铺商品的老买家数。老买家是指过去一段时间购买过商品的买家（例如过去30天）。

SELECT item_id --商品id 
        ,buyer_id AS old_buyer_id
FROM 用户购买商品明细表 
WHERE ds < ${bdp.system.bizdate} 
AND ds >=to_char(dateadd(to_date(${bdp.system.bizdate},'yyyymmdd'),-29,'dd'),'yyyymmdd') 
GROUP BY item_id
        ,buyer_id;