| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139 |
- -- 作者：tianyu.chu
- -- 日期：2026-05-07
- -- 工单：(无)
- -- 目的：raw → ods 增量同步（ADR-03）；双源 union (raw dt=${dt} + raw dt=${pdt}) + 活动日 = ${dt} 过滤 + 按运行日 ${dt} 分桶（dt=活动日 GREATEST(create,update)）+ (id) dedupe + 动态分区写入
- -- 状态：[草案]
- -- 备注：sched=T，${dt}=业务日 T-1（yyyyMMdd），${pdt}=T-2；ods 跨 dt 不去重，同 pk 多 dt 并存（拉链表底层）；
- -- dt=活动日 = GREATEST(create, update)：业务会改 create、二者皆活动性时间戳、无"创建日"概念，取较晚者为最后活动日；每单每活动日一份
- INSERT OVERWRITE TABLE ods.ods_trd_card_group_info_inc_d PARTITION (dt)
- SELECT
- CAST(id AS BIGINT) AS id,
- CAST(merchant_id AS BIGINT) AS merchant_id,
- appid AS appid,
- name AS name,
- code AS code,
- CAST(status AS BIGINT) AS status,
- specs AS specs,
- type AS type,
- random_type AS random_type,
- CAST(total_price AS DECIMAL(20,4)) AS total_price,
- CAST(copies AS BIGINT) AS copies,
- CAST(unit_price AS DECIMAL(20,4)) AS unit_price,
- CAST(sold_copies AS BIGINT) AS sold_copies,
- release_time AS release_time,
- cycle AS cycle,
- show_applet AS show_applet,
- title AS title,
- msg AS msg,
- remark AS remark,
- CAST(create_time AS TIMESTAMP) AS create_time,
- update_by AS update_by,
- CAST(update_time AS TIMESTAMP) AS update_time,
- CAST(order_quota_min AS BIGINT) AS order_quota_min,
- CAST(order_quota_max AS BIGINT) AS order_quota_max,
- CAST(user_quota_max AS BIGINT) AS user_quota_max,
- CAST(start_time AS TIMESTAMP) AS start_time,
- marketing_info AS marketing_info,
- reviewmsg AS reviewmsg,
- CAST(`lock` AS BOOLEAN) AS `lock`,
- commission_rate AS commission_rate,
- year AS year,
- sport AS sport,
- manufacturer AS manufacturer,
- sets AS sets,
- act AS act,
- config AS config,
- info_config AS info_config,
- CAST(total_num AS BIGINT) AS total_num,
- CAST(banner_end_time AS TIMESTAMP) AS banner_end_time,
- add_banner AS add_banner,
- CAST(finished_time AS TIMESTAMP) AS finished_time,
- display_name AS display_name,
- CAST(group_sets_no AS BIGINT) AS group_sets_no,
- CAST(close_payment_time AS TIMESTAMP) AS close_payment_time,
- CAST(confirm_send_time AS TIMESTAMP) AS confirm_send_time,
- CAST(close_payment_status AS BIGINT) AS close_payment_status,
- CAST(open_card AS BIGINT) AS open_card,
- close_payment_record AS close_payment_record,
- CAST(group_full_time AS TIMESTAMP) AS group_full_time,
- CAST(live_create_time AS TIMESTAMP) AS live_create_time,
- CAST(live_start_time AS TIMESTAMP) AS live_start_time,
- CAST(live_end_time AS TIMESTAMP) AS live_end_time,
- CAST(report_start_time AS TIMESTAMP) AS report_start_time,
- CAST(report_end_time AS TIMESTAMP) AS report_end_time,
- CAST(report_review_num AS BIGINT) AS report_review_num,
- CAST(report_review_first_time AS TIMESTAMP) AS report_review_first_time,
- CAST(report_review_end_time AS TIMESTAMP) AS report_review_end_time,
- CAST(review_hold_time AS TIMESTAMP) AS review_hold_time,
- CAST(review_approval_time AS TIMESTAMP) AS review_approval_time,
- CAST(review_num AS BIGINT) AS review_num,
- config_json AS config_json,
- CAST(free_flag AS BIGINT) AS free_flag,
- mer_name AS mer_name,
- change_type AS change_type,
- CAST(act_price AS DECIMAL(20,4)) AS act_price,
- act_config_json AS act_config_json,
- CAST(real_sold_num AS BIGINT) AS real_sold_num,
- CAST(weight AS BIGINT) AS weight,
- hot_type AS hot_type,
- CAST(team_first AS BIGINT) AS team_first,
- prop1 AS prop1,
- prop2 AS prop2,
- prop3 AS prop3,
- CAST(point_rate AS BIGINT) AS point_rate,
- CAST(point_max AS BIGINT) AS point_max,
- CAST(point_min AS BIGINT) AS point_min,
- CAST(list_id AS BIGINT) AS list_id,
- list_code AS list_code,
- CAST(mix_copies AS BIGINT) AS mix_copies,
- sub_type AS sub_type,
- act_point_type AS act_point_type,
- CAST(payment_method AS BIGINT) AS payment_method,
- CAST(payment_total_price AS DECIMAL(20,4)) AS payment_total_price,
- CAST(payment_commission AS DECIMAL(20,4)) AS payment_commission,
- CAST(payment_finished_price AS DECIMAL(20,4)) AS payment_finished_price,
- CAST(payment_remain_price AS DECIMAL(20,4)) AS payment_remain_price,
- CAST(payment_online_price AS DECIMAL(20,4)) AS payment_online_price,
- CAST(exclusive AS BIGINT) AS exclusive,
- CAST(has_bg AS BIGINT) AS has_bg,
- CAST(merchant_sort AS BIGINT) AS merchant_sort,
- CAST(del_flg AS BIGINT) AS del_flg,
- CAST(del_time AS TIMESTAMP) AS del_time,
- review_account AS review_account,
- CAST(act_id AS BIGINT) AS act_id,
- CAST(sold_end_time AS TIMESTAMP) AS sold_end_time,
- CAST(panini_list_id AS BIGINT) AS panini_list_id,
- hot_type_config AS hot_type_config,
- CAST(goods_type AS BIGINT) AS goods_type,
- CAST(report_flag AS BIGINT) AS report_flag,
- CAST(use_coupon AS BIGINT) AS use_coupon,
- CAST(user_level AS BIGINT) AS user_level,
- CAST(custom AS BIGINT) AS custom,
- CAST(gift_card_id AS BIGINT) AS gift_card_id,
- group_show_name AS group_show_name,
- CAST(min_card_num AS BIGINT) AS min_card_num,
- act_type AS act_type,
- waring_type AS waring_type,
- CAST(compensation_status AS BIGINT) AS compensation_status,
- point_type AS point_type,
- first_act_config AS first_act_config,
- gift_config AS gift_config,
- CAST(version AS BIGINT) AS version,
- extra_prop AS extra_prop,
- CAST(use_member_discount AS BIGINT) AS use_member_discount,
- CAST(merchant_open AS BIGINT) AS merchant_open,
- CASE WHEN del_flg = '1' THEN TRUE ELSE FALSE END AS is_deleted,
- ods_dt AS dt
- FROM (
- SELECT *,
- '${dt}' AS ods_dt,
- ROW_NUMBER() OVER (
- PARTITION BY id
- ORDER BY GREATEST(create_time, NULLIF(update_time, '')) DESC
- ) AS rn
- FROM raw.raw_trd_card_group_info_inc_d
- WHERE dt IN ('${dt}', '${pdt}')
- AND DATE_FORMAT(GREATEST(create_time, NULLIF(update_time, '')), 'yyyyMMdd') = '${dt}'
- ) t
- WHERE t.rn = 1;
|
