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

-- 作者：tianyu.chu
-- 日期：2026-06-21
-- 工单：(无)
-- 目的：拼团转化漏斗，单 ${dt} 快照，扫近30天一次条件聚合出 1d/7d/30d 三窗口，写 dt=${dt}
-- 口径：登录用户(user_id 非空非'0')；每窗口独立 distinct UV（聚合UV 非路径，层间为近似）；
--       成功=GroupPayOrderConfirm 且 paymentCallBack='Y'；dt=yyyyMMdd（埋点沿用文件日8位）
-- 参数：${dt} 快照日；${d7}=dt-6、${d30}=dt-29（均含两端，共 7/30 天）；
--       分区裁剪靠 WHERE 的 '${d30}' 字面量（DS 调度用 $[yyyyMMdd-6]/$[yyyyMMdd-29] 传）
INSERT OVERWRITE TABLE ads.ads_trd_group_funnel PARTITION (dt = '${dt}')
SELECT
    COUNT(DISTINCT CASE WHEN dt =  '${dt}' AND event_name = '$AppStart'            THEN user_id END) AS uv_start_1d,
    COUNT(DISTINCT CASE WHEN dt =  '${dt}' AND event_name = 'GroupShow'            THEN user_id END) AS uv_show_1d,
    COUNT(DISTINCT CASE WHEN dt =  '${dt}' AND event_name = 'ClickGroup'           THEN user_id END) AS uv_detail_1d,
    COUNT(DISTINCT CASE WHEN dt =  '${dt}' AND event_name = 'GroupPayOrderConfirm' THEN user_id END) AS uv_order_1d,
    COUNT(DISTINCT CASE WHEN dt =  '${dt}' AND event_name = 'GroupPayOrderConfirm'
                         AND get_json_object(params_json, '$.paymentCallBack') = 'Y' THEN user_id END) AS uv_paid_1d,
    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';