poyee-data-warehouse/manual/backfill/20260510_dws_usr_user_trade_1d_init.sql

-- 作者:tianyu.chu
-- 日期:2026-05-10
-- 工单:(无)
-- 目的:dws_usr_user_trade_1d 初始化(kb/29 §2):
--      扫 dwd_trd_order_pay_apd_d 全量历史分区 +
--      过滤 category IS NOT NULL(脏数据 other 已在 DIM 清洗为 NULL,聚合时排除,kb/28 §3.2)+
--      GROUP BY (user_id, category, dwd.dt) 聚合 +
--      动态分区写入 dws dt(承袭 dwd 业务时间分区)
-- 状态:[待执行]
-- 备注:跑一次后由 jobs/dws/usr/dws_usr_user_trade_1d.sql 接管日常增量;
--      dt 取 dwd.dt 直接传(dwd 已按 DATE(payment_success_time) 落分区,dws 不再 DATE_FORMAT);
--      历史业务时间跨 2021-10 ~ ${dt},动态分区上限 SET 提到 2000;
--      前置:dwd_trd_order_pay_apd_d_init 已跑完(全量历史已落 dwd)

set hive.exec.max.dynamic.partitions=2000;
set hive.exec.max.dynamic.partitions.pernode=200;

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 category IS NOT NULL
GROUP BY user_id, category, dt;