Hive面试题——最近N天uv优化策略

本文已参与「新人创作礼」活动，一起开启掘金创作之路。

对于互联网行业做数据的小伙伴来说，计算最近N天（或者从某个时间点截止到当前）的UV可以说是最常见的场景了，在数据岗位面试中也是经常会遇到的问题。面对这个问题，大部分人第一感觉，可能都觉得这个问题很简单没什么可讲的，直接过滤、去重计数就完了呗！的确，从逻辑上讲就是这么简单。但是，当数据量大了，问题就来了或者说这么简单的问题，怎么在面试中体现自己的水平呢？[/手动狗头/]

好了，言归正传，对于这个问题，常见的策略有以下几种：

1、直接过滤，去重计数。对于小数据量，简单高效的解决问题就是最优解。

SELECT  count(distinct user_id) as uv
FROM    dwd_log_vst
WHERE   
ds > to_char(dateadd(to_date('${cur_date}','yyyymmdd'), - n,'dd'),'yyyymmdd')

2、按照天和人维度去重构建轻度汇总中间表，采用天分区；再进行过滤、去重计数。这样做可以减少扫描的数据量，提升计算效率。

INSERT OVERWRITE TABLE dwd_log_vst_1d (ds='${cur_date}')
SELECT  user_id
FROM    dwd_log_vst
WHERE   ds = '{cur_date}'
GROUP BY user_id
;
SELECT  count(distinct user_id) as uv
FROM    dwd_log_vst_1d
WHERE   ds > to_char(dateadd(to_date('${cur_date}','yyyymmdd'), - n,'dd'),'yyyymmdd')

3、将最近N天的数据按照人维度去重，存储为一个分区。然后按照天去更新（用最近n-1天与最近1天，做全连接合并），这样就可以减少扫描的分区数量。

INSERT OVERWRITE TABLE dwd_log_vst_nd  PARTITION(ds='${cur_date}')
select
        COALESCE( n.user_id, o.user_id )   AS user_id,
FROM    (
            SELECT  *
            FROM    dwd_log_vst_1d 
            WHERE   ds = '${cur_date}'
            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     user_id IS NOT NULL
        ) o
ON      o.user_id = n.user_id
;

SELECT  count(distinct user_id) as uv
FROM    dwd_log_vst_nd
WHERE   ds = to_char(dateadd(to_date('${cur_date}','yyyymmdd'), - 1,'dd'),'yyyymmdd')