全部产品
Search
文档中心

云原生大数据计算服务 MaxCompute:长周期指标的计算优化方案

更新时间:Dec 26, 2023

本文为您介绍如何对长周期指标的计算进行优化。

实验背景

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

通常,这些指标的计算方式为从日志明细表中查询数据进行计算。例如,运行如下SQL语句计算商品最近30天的访客数。

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;
说明

代码中的变量都是DataWorks的调度变量,仅适用于DataWorks的调度任务。下文不再重复说明。

当每天的日志量很大时,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;

改进思路:

  • 维护一张店铺商品和买家购买关系的维表A,记录买家和店铺的购买关系、第一次购买时间、最近一次购买时间、累计购买件数、累计购买金额等信息。

  • 每天使用最近1天的支付明细日志更新表A的相关数据。

  • 计算老买家数量时,判断最近一次购买时间是否在30天之内,从而最大程度上的数据关系对去重,减少计算输入数据量。