hive里假设:dws_store_install_ds 为历史安装全量表
dws_store_install_dm为天增量表
如下逻辑是 计算上月累积,本月累积值 的逻辑
INSERT OVERWRITE TABLE dws_hispace_install_ds(pt_d='$date')
SELECT
IF(t2.app_id is null ,t1.app_id,t2.app_id) AS app_id
,NVL(install_cnt,0) AS install_cnt
,NVL(sum_install_cnt,0)+NVL(install_cnt,0) AS sum_install_cnt
,IF(dayofmonth('$date')='1',this_month_install_cnt,last_month_install_cnt) AS last_month_install_cnt
,IF(dayofmonth('$date')='1',install_cnt,this_month_install_cnt+install_cnt) AS this_month_install_cnt
FROM
(
SELECT
app_id
,sum_install_cnt
,last_month_install_cnt
,this_month_install_cnt
FROM dws_store_install_ds
WHERE pt_d='$last_date'
) t1
FULL OUT JOIN
(
SELECT
app_id
,install_cnt
FROM dws_store_install_dm
WHERE pt_d='$date'
) t2
ON t1.app_id = t2.app_id
;