poyee-data-warehouse/jobs/dws/usr/dws_usr_user_trade_1d.sql

-- 作者:tianyu.chu
-- 日期:2026-05-10
-- 工单:(无)
-- 目的:dws_usr_user_trade_1d 日常增量(kb/29 §1.4 + §2):
--      回算近 2 日(与 dwd N=2 对齐,漂移连锁补偿,kb/93 ADR-09):扫 dwd.dt IN (${dt}, ${pdt}) +
--      过滤 category IS NOT NULL(脏数据 other 已在 DIM 清洗为 NULL,kb/28 §3.2)+
--      GROUP BY (user_id, category, dt) 聚合 +
--      动态分区写入 dws.dt IN (${dt}, ${pdt})
-- 状态:[草案]
-- 备注:sched=T,${dt}=T-1,${pdt}=T-2;
--      回算 N=2 兜底跨零点漂移连锁补偿(dwd 同步,参 kb/93 ADR-09);
--      INSERT OVERWRITE 动态分区(kb/26 §8 项目默认 DYNAMIC mode):只覆盖 SELECT 出现的 dt 分区,不动其他历史分区;
--      前置 DS DEPENDENT:dwd_trd_order_pay_apd_d.${dt}

INSERT OVERWRITE TABLE dws.dws_usr_user_trade_1d PARTITION (dt)
SELECT
    user_id                                                  AS user_id,
    category                                                 AS category,
    COUNT(DISTINCT order_id)                                 AS pay_order_cnt,
    SUM(purchase_cnt)                                        AS purchase_cnt,
    SUM(payable_amt_cny)                                     AS payable_amt_cny,
    SUM(pay_amt_cny)                                         AS pay_amt_cny,
    SUM(trade_amt_cny)                                       AS trade_amt_cny,
    SUM(settle_amt_cny)                                      AS settle_amt_cny,
    SUM(merchant_discount_amt_cny)                           AS merchant_discount_amt_cny,
    SUM(platform_discount_amt_cny)                           AS platform_discount_amt_cny,
    SUM(member_discount_amt_cny)                             AS member_discount_amt_cny,
    SUM(act_discount_amt_cny)                                AS act_discount_amt_cny,
    SUM(point_deduct_amt_cny)                                AS point_deduct_amt_cny,
    SUM(shipping_amt_cny)                                    AS shipping_amt_cny,
    SUM(point)                                               AS point,
    CURRENT_TIMESTAMP()                                      AS etl_time,
    dt                                                       AS dt
FROM dwd.dwd_trd_order_pay_apd_d
WHERE dt IN ('${dt}', '${pdt}')
  AND category IS NOT NULL
GROUP BY user_id, category, dt;