MaxCompute：長周期指標的計算最佳化方案

實驗背景

電子商務公司在電商資料倉儲和商務分析情境中，經常需要計算最近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;