poyee-data-warehouse/jobs/ads/trd/ads_trd_group_funnel.sql

-- 作者：tianyu.chu
-- 日期：2026-06-21（2026-06-23 拆表重构）
-- 工单：(无)
-- 目的：拼团转化漏斗，单 ${dt} 快照写两表：① _daily 当日(增量分区) ② _rolling 近7/30天(覆盖整表)
-- 口径：登录用户(user_id 非空非'0')；每窗口独立 distinct UV（窗口去重无法由日 UV 加工，故各自 COUNT DISTINCT）；
--       成功=GroupPayOrderConfirm 且 paymentCallBack='Y'；dt=yyyyMMdd
-- 参数：${dt} 快照日；${d7}=dt-6、${d30}=dt-29（均含两端 7/30 天）；
--       分区裁剪靠 WHERE 字面量（DS 调度用 ${dt}/$[yyyyMMdd-6]/$[yyyyMMdd-29] 传）

-- ① 昨日逐日：增量写 dt=${dt} 单分区
INSERT OVERWRITE TABLE ads.ads_trd_group_funnel_daily PARTITION (dt = '${dt}')
SELECT
    '${dt}'                                                                         AS funnel_dt,
    COUNT(DISTINCT CASE WHEN event_name = '$AppStart'            THEN user_id END) AS uv_start,
    COUNT(DISTINCT CASE WHEN event_name = 'GroupShow'            THEN user_id END) AS uv_show,
    COUNT(DISTINCT CASE WHEN event_name = 'ClickGroup'           THEN user_id END) AS uv_detail,
    COUNT(DISTINCT CASE WHEN event_name = 'GroupPayOrderConfirm' THEN user_id END) AS uv_order,
    COUNT(DISTINCT CASE WHEN event_name = 'GroupPayOrderConfirm'
                         AND get_json_object(params_json, '$.paymentCallBack') = 'Y' THEN user_id END) AS uv_paid,
    current_timestamp() AS etl_time
FROM ods.ods_usr_traces_apd_d
WHERE dt = '${dt}'
  AND user_id IS NOT NULL AND user_id <> '0';

-- ② 近7/30天：覆盖整表，只留最新 1 行（as-of ${dt}）
INSERT OVERWRITE TABLE ads.ads_trd_group_funnel_rolling
SELECT
    '${dt}' AS dt,
    COUNT(DISTINCT CASE WHEN dt >= '${d7}' AND event_name = '$AppStart'            THEN user_id END) AS uv_start_7d,
    COUNT(DISTINCT CASE WHEN dt >= '${d7}' AND event_name = 'GroupShow'            THEN user_id END) AS uv_show_7d,
    COUNT(DISTINCT CASE WHEN dt >= '${d7}' AND event_name = 'ClickGroup'           THEN user_id END) AS uv_detail_7d,
    COUNT(DISTINCT CASE WHEN dt >= '${d7}' AND event_name = 'GroupPayOrderConfirm' THEN user_id END) AS uv_order_7d,
    COUNT(DISTINCT CASE WHEN dt >= '${d7}' AND event_name = 'GroupPayOrderConfirm'
                         AND get_json_object(params_json, '$.paymentCallBack') = 'Y' THEN user_id END) AS uv_paid_7d,
    COUNT(DISTINCT CASE WHEN event_name = '$AppStart'            THEN user_id END) AS uv_start_30d,
    COUNT(DISTINCT CASE WHEN event_name = 'GroupShow'            THEN user_id END) AS uv_show_30d,
    COUNT(DISTINCT CASE WHEN event_name = 'ClickGroup'           THEN user_id END) AS uv_detail_30d,
    COUNT(DISTINCT CASE WHEN event_name = 'GroupPayOrderConfirm' THEN user_id END) AS uv_order_30d,
    COUNT(DISTINCT CASE WHEN event_name = 'GroupPayOrderConfirm'
                         AND get_json_object(params_json, '$.paymentCallBack') = 'Y' THEN user_id END) AS uv_paid_30d,
    current_timestamp() AS etl_time
FROM ods.ods_usr_traces_apd_d
WHERE dt BETWEEN '${d30}' AND '${dt}'
  AND user_id IS NOT NULL AND user_id <> '0';