前言

很多同学在面试过程中会被问到：1、有做过模型设计相关的优化吗？2、如何优化长周期累计计算？

如果你还不知道如何回答此类问题，那么这个案例将会为你提供一个非常好的思路！！！

需求背景

一句话描述：计算X平台用户最近90天活跃天数

输入：用户活跃天表dwd_tb_user_active_dd（user_id、is_active）

最初方案：

INSERT OVERWRITE TABLE dwd_tb_user_active_days_di PARTITION(dt = '${date}')
SELECT
  user_id,
  size(COLLECT_SET(dt)) AS active_days
FROM dwd_tb_user_active_dd
WHERE dt <= '${date}'
  AND dt >= DATE_FORMAT(DATE_SUB(TO_DATE('${date}'), 89), 'yyyyMMdd')
  AND is_active = 1
GROUP BY uid;

问题分析

dwd_tb_user_active_dd 是一张用户全量活跃信息表，读取90天的分区，可知数据量是非常大的，那么就会出现计算效率低、浪费计算资源等问题

解决方案

1、创建滚存表dwd_tb_user_active_arr_dd（user_id、last_90d_active_dates），其中last_90d_active_dates存储最近90天用户活跃的日期

2、上线日第一天，初始化近90天活跃的用户日期列表

3、从第二天起，利用昨日分区的数据，然后加上当天是否活跃，同时删除活跃日期列表中在90天前的数据

4、根据用户活跃日期列表，求和即可计算活跃天数

WITH previous_day_data AS (
  SELECT 
    user_id,
    last_90d_active_dates -- 用户历史活跃日期数组
  FROM dwd_tb_user_active_arr_dd 
  WHERE dt = '${date-1}'
),
current_day_active AS (
  SELECT 
    user_id
  FROM dwd_tb_user_active_dd
  WHERE dt = '${date}' AND is_active = 1
),
processed_data AS (
  SELECT 
    p.user_id,
    CASE 
      WHEN c.user_id IS NOT NULL THEN ARRAY_UNION(p.last_90d_active_dates, ARRAY('${date}'))
      ELSE p.last_90d_active_dates
    END AS all_active_dates,
    date_sub('${date}', 89) AS cutoff_date
  FROM previous_day_data p
  LEFT JOIN current_day_active c ON p.user_id = c.user_id
),
final_result AS (
  SELECT 
    user_id,
    FILTER(
      all_active_dates,
      date >= cutoff_date
    ) AS last_90d_active_dates
  FROM processed_data
)
INSERT OVERWRITE TABLE dwd_tb_user_active_arr_dd PARTITION(dt = '${date}')
SELECT 
    user_id, 
    last_90d_active_dates
FROM final_result
;

INSERT OVERWRITE TABLE dwd_tb_user_active_days_di PARTITION(dt = '${date}')
SELECT 
    user_id, 
    size(last_90d_active_dates) as active_days
FROM dwd_tb_user_active_arr_dd
where dt = '${date}'
;

思考

上述方案需要一张中间表来存储用户近90天活跃的日期，是不是就更加耗费存储了？

是的！！！下一篇来跟大家聊聊 如何使用BitMap进一步优化滚存表