数仓|长周期去重指标的计算优化

在电商数据仓库和商业分析场景中，经常需要计算最近N天的UV、成交用户数等类似的指标，这些指标都有两个共同的特点

• 去重，在时间窗口范围内，一个用户多次访问或者购买，只统计一次

• 时间窗口，这些指标需要根据一段时间内的累积数据进行计算

通常情况下，这些指标的计算逻辑并不复杂，可以从日志明细表中查询数据进行计算。例如，运行如下SQL语句计算商品最近30天的访客数。

SELECT
        sku_code   --商品id 
        ,COUNT(DISTINCT user_id) AS ipv_uv_30d
FROM    dwd_log_vst_di   --用户访问商品日志明细表
WHERE   ds<= '${cur_date}'
AND     ds>= to_char(dateadd(to_date('${cur_date}','yyyymmdd'),-29,'dd'),'yyyymmdd')
GROUP BY item_id
;

上面的计算方式是最常见的，也是最简单的。如果数据量不是很大，这样计算也是可以的。但是当面对海量数据时，这种方式会显得非常不友好。比如每天的日志量很大时，上面代码就会出现一个严重的问题，即需要的Map个数太多，导致Map Task无法顺利执行。

尖叫提示：如果分区数据量很大，并且需要扫描的分区数有很多，比如60天、90天，这种情况需要优化代码，避免暴力扫描

优化方案一

方案描述

如何计算长周期的指标，又不影响性能？

• 首先，我们可以看到性能瓶颈主要在需要扫描的分区天数和分区数据量上，归根结底是数据量的问题，如果把数据量给降低了，就可以解决这个问题了。

• 明确了瓶颈是数据量大，那么我们该如何降低数据量呢，值得注意的是明细表的数据是不能改变的，所以减少数据量最直接的办法是把每天的数据量都给减少，因此需要构建临时表，对1天的数据进行轻度汇总，这样就能去掉很多重复数据 ，减少数据量。

实现方案

根据上面的方案描述，我们可以执行下面两个步骤：

• 构建轻度汇总表，明确汇总的粒度，就上面的例子而言，我们可以创建一张商品+用户粒度的1d表

• 使用轻度汇总的表进行长周期去重指标的计算

创建一张商品+用户粒度的1d表

--一个用户访问多次，只保存一次
INSERT OVERWRITE TABLE dwd_sku_user_1d (ds='${cur_date}')
SELECT  sku_code
        ,user_id
FROM    dwd_log_vst_di
WHERE   ds = '{cur_date}'
GROUP BY sku_code
         ,user_id

使用上面轻度汇总表进行计算

SELECT  sku_code
        ,COUNT(DISTINCT user_id) AS uv
FROM    dwd_sku_user_1d
WHERE   ds <= '${cur_date}'
AND     ds >= to_char(dateadd(to_date('${cur_date}','yyyymmdd'), - 29,'dd'),'yyyymmdd')
GROUP BY sku_code
;

优化方案二

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

所以，我们是不是可以将需要读取的这N个分区的数据存在在一个分区下面，让一个分区的数据包含历史数据的信息，这样就可以不用扫描那么多的分区了。

执行上面的思路，主要包括以下两个步骤：

• 首先获取最近30天的访问数据(假设存储在表dwd_log_vst_nd中)，只需要要一次全量计算，后面通过增量计算

• 对最新分区的数据(包括近30的数据)进行去重

假设我们有了上面的商品+用户粒度的轻度汇总表，只需要扫描一次30天分区的数据放在表dwd_log_vst_nd的一个分区下面，以后计算则通过增量的方式进行计算即可，即将dwd_log_vst_nd前天分区数据与表的dwd_sku_user_1d最近一天分区数据进行合并，放在dwd_log_vst_nd表的最近一天分区下。

INSERT OVERWRITE TABLE dwd_log_vst_nd PARTITION(ds='${cur_date}')
        COALESCE( n.sku_code, o.sku_code ) AS sku_code,
        COALESCE( n.user_id, o.user_id )   AS user_id,
FROM    (
            SELECT  *
            FROM    dwd_sku_user_1d
            WHERE   ds = '${cur_date}'
            AND     sku_code IS NOT NULL
            AND     user_id IS NOT NULL
        ) n
FULL OUTER JOIN 
        (-- 全外连接进行数据merge
            SELECT  *
            FROM    dwd_log_vst_nd
            WHERE   ds = to_char(dateadd(to_date('${cur_date}','yyyymmdd'), - 1,'dd'),'yyyymmdd')
            AND     sku_code IS NOT NULL
            AND     user_id IS NOT NULL
        ) o
ON      o.sku_code = n.sku_code
AND     o.user_id = n.user_id
;

有了上面的dwd_log_vst_nd表计算进30天的商品访客数就会变得简单了，只需要扫描一个分区的数据进行去重即可：

SELECT  sku_code
        ,COUNT(DISTINCT user_id) AS uv
FROM    dwd_sku_user_nd
WHERE   ds = '${cur_date}'
GROUP BY sku_code
;

总结

本文主要介绍了数仓开发过程中常见的长周期去重指标的计算优化方式，对于跨多天分区的去重指标计算，在数据量特别大的情况下会存在暴力扫描的问题，从而导致计算效率低。通常情况下，我们可以使用预去重的方式将原始分区的数据量降低，这样在大不部分的情况下可以缓解计算瓶颈的问题，但是仍然需要扫描大量的分区数据。本文给出的第二种方式，是基于增量去重的视角进行的，即将需要扫描的分区数据合并放置在一个分区下面，后面通过增量合并的方式来获取最新的全量数据，最终的指标计算只需要扫描一天的分区数据即可，这样计算性能会有较大的提升。

原文