poyee-data-warehouse/jobs/ods/trd/ods_trd_card_group_info_ful_d.sql

-- 作者：tianyu.chu
-- 日期：2026-07-17
-- 工单：(无)
-- 目的：拼团全量最新态日常 merge —— full dt=${dt} ← full dt=${pdt} ⨝ inc dt=${dt}，按 id 取最新一版
-- 状态：[草案]
-- 备注：sched=T，${dt}=业务日 T-1、${pdt}=T-2（即前一天的 full）。
--       **静态分区写入**（PARTITION(dt='${dt}')）是硬要求：本 SQL 读 full dt=${pdt} 又写 full，
--       若用动态分区（PARTITION(dt)），输出路径=表根目录、与读的分区路径重叠，Spark 会报
--       "Cannot overwrite a path that is also being read from"。静态分区的输出是具体分区目录，不重叠。
--       起点由 manual/backfill/20260717_..._ful_d_seed.sql 一次性 seed；断链需重新 seed。
--       历史多版本在 inc 表（拉链底座），本表只保当前态。lock 是保留字，SELECT 里必须带反引号。
--       **分区保留 2 天**（只留 ${dt} 与 ${pdt}）：文末 ALTER 精确 drop ${rdt}（=${dt}-2，即 T-3），
--       每天正好掉一个。用精确分区值而非 dt < 'x'——Spark SQL 的 DROP PARTITION 只接受 col=value，
--       比较式是 Hive 语法、Spark 解析不了。DS 需额外传参 rdt=$[yyyyMMdd-3]；手动跑加 -p rdt=<T-3>。
--       代价：漏跑一天则那天本该掉的分区不会被补 drop，需手工清；断链重新 seed 即可。

INSERT OVERWRITE TABLE ods.ods_trd_card_group_info_ful_d PARTITION (dt='${dt}')
SELECT
    id, merchant_id, appid, name, code,
    status, specs, type, random_type, total_price,
    copies, unit_price, sold_copies, release_time, cycle,
    show_applet, title, msg, remark, create_time,
    update_by, update_time, order_quota_min, order_quota_max, user_quota_max,
    start_time, marketing_info, reviewmsg, `lock`, commission_rate,
    year, sport, manufacturer, sets, act,
    config, info_config, total_num, banner_end_time, add_banner,
    finished_time, display_name, group_sets_no, close_payment_time, confirm_send_time,
    close_payment_status, open_card, close_payment_record, group_full_time, live_create_time,
    live_start_time, live_end_time, report_start_time, report_end_time, report_review_num,
    report_review_first_time, report_review_end_time, review_hold_time, review_approval_time, review_num,
    config_json, free_flag, mer_name, change_type, act_price,
    act_config_json, real_sold_num, weight, hot_type, team_first,
    prop1, prop2, prop3, point_rate, point_max,
    point_min, list_id, list_code, mix_copies, sub_type,
    act_point_type, payment_method, payment_total_price, payment_commission, payment_finished_price,
    payment_remain_price, payment_online_price, exclusive, has_bg, merchant_sort,
    del_flg, del_time, review_account, act_id, sold_end_time,
    panini_list_id, hot_type_config, goods_type, report_flag, use_coupon,
    user_level, custom, gift_card_id, group_show_name, min_card_num,
    act_type, waring_type, compensation_status, point_type, first_act_config,
    gift_config, version, extra_prop, use_member_discount, merchant_open,
    is_deleted
FROM (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY COALESCE(update_time, create_time) DESC) AS rn
    FROM (
        SELECT * FROM ods.ods_trd_card_group_info_ful_d WHERE dt = '${pdt}'
        UNION ALL
        SELECT * FROM ods.ods_trd_card_group_info_inc_d WHERE dt = '${dt}'
    ) u
) t
WHERE t.rn = 1;

-- 保留 2 天：drop 掉 ${dt}-2（T-3）这一个分区
ALTER TABLE ods.ods_trd_card_group_info_ful_d DROP IF EXISTS PARTITION (dt='${rdt}');