09261

最新推荐文章于 2024-11-02 11:13:42 发布

MYH516

最新推荐文章于 2024-11-02 11:13:42 发布

阅读量207

点赞数

文章标签： java 数据库大数据

本文链接：https://blog.csdn.net/mayaohao/article/details/133309513

版权

select * from dw_ads.adsordsa;




-- dwd model 8
drop table if exists dw_dwd.dwd_ord_model_order_detail_timeline_fl_d;
create table dw_dwd.dwd_ord_model_order_detail_timeline_fl_d
(
    sub_order_no                string comment '子订单号',
    order_no                    string comment '订单号',
    platform_order_no           string comment '源平台订单号：tmall/douyin/appoms订单号',
    platform_sub_order_no       string comment '源平台订单号：tmall/douyin/appoms订单号',
    sku_code                    string comment '',
    order_channel               string comment '订单渠道TMALL/DOUYIN/APP/WEB/H5/MINIPROGRAM/OFFLINE',
    order_create_time           timestamp comment '下单时间',
    order_prepay_time           timestamp comment '预售订金支付时间',
    order_pay_time              timestamp comment '支付时间',
    push_ynap_order_time        timestamp comment 'omni推送给ynap的时间',
    ynap_confirm_delivery_time  timestamp comment 'omniYNAP订单确认时间',
    ynap_delivery_time          timestamp comment 'omniYNAP发货时间',
    ynap_enter_warehouse_time   timestamp comment 'omni入本地仓时间',
    ynap_cancel_time            timestamp comment 'omniYNAP取消时间',
    order_apply_delivery_time   timestamp comment '申请国内发货时间',
    order_delivery_time         timestamp comment '国内仓库发货时间',
    order_consign_time          timestamp comment '客户签收时间',
    order_success_time          timestamp comment '订单交易成功时间',
    order_end_time              timestamp comment '订单完成时间（售后期结束）',
    order_cancel_time           timestamp comment '订单取消时间',
    order_last_update_time      timestamp comment '订单最后更新时间',
    refund_apply_time           timestamp comment '退款申请时间',
    return_apply_time           timestamp comment '退货申请时间',
    return_send_time            timestamp comment '退货商品寄出时间（APP上门揽件时间）',
    return_enter_warehouse_time timestamp comment '退货入库时间',
    refund_backmoney_time       timestamp comment '退款确认时间',
    refund_success_time         timestamp comment '退款成功时间',
    refund_close_time           timestamp comment '退款关闭时间',
    refund_last_update_time     timestamp comment '退款最后更新时间',
    dc_system_time              timestamp comment '系统更新时间',
    create_time                 timestamp comment '创建时间',
    update_time                 timestamp comment '更新时间'
)
comment '订单时间表'
stored as orc;




--10
drop table if exists dw_dwd.dwd_ord_model_order_label_fl_d;
create table dw_dwd.dwd_ord_model_order_label_fl_d
(
    order_no           string comment '订单号',
    platform_order_no  string comment '源平台订单号',
    is_use_prepay_card string comment '是否使用预付卡（APP礼品卡/TMALL兑换卡）',
    is_buy_prepay_card string comment '是否购买预付卡',
    is_installment     string comment '是否分期',
    is_interest_free   string comment '是否免息',
    is_special_refund  string comment '是否特殊退款',
    payment_modes      string comment '支付方式（拼接）',
    is_88vip           string comment '88VIP标签',
    terminal_type      string comment '终端来源类型：web/ios/android',
    is_staff_order     string comment '员工订单标记',
    is_mobile_order    string comment '是否移动端订单',
    is_preorder        string comment '是否预售订单',
    is_member_order    string comment '下单时是否会员订单',
    is_first_order     string comment '是否购买首单',
    is_second_retreat  string comment '是否非秒退首单',
    th_buy             string comment '订单第几次购买',
    is_gift_order      string comment '赠品订单标记',
    is_free_order      string comment '0元购订单',
    is_test            string comment '是否测试订单',
    app_order_type     string comment '订单类型：销售订单sale,线下订单offline,Pre-OrderpreOrder',
    app_channel_code   string comment '渠道：APP，WEBSITE,EIP',
    is_affiliate_user  string comment '是否是Affiliate用户',
    city_level         string comment '地域等级',
    create_time        timestamp comment '创建时间',
    update_time        timestamp comment '更新时间',
    dc_system_time     timestamp comment '数据中台系统处理时间'
)
comment '.订单标签表'
stored as orc;


--11
drop table if exists dw_dwd.dwd_ord_model_order_detail_label_fl_d;
create table dw_dwd.dwd_ord_model_order_detail_label_fl_d
(
    sub_order_no                  string comment '子订单号',
    order_no                      string comment '订单号',
    order_channel                 string comment '订单渠道:TMALL/DOUYIN/APP/WEB/H5/MINIPROGRAM/OFFLINE',
    day                           date comment '数据日期',
    memberid                      string comment '会员id',
    sku_code                      string comment '商品标识',
    order_amount                  decimal(10,2) comment '下单金额',
    pay_amount                    decimal(10,2) comment '付款金额',
    ship_amount                   decimal(10,2) comment '发货金额',
    refund_apply_amount           decimal(10,2) comment '申退金额',
    refund_apply_success_amount   decimal(10,2) comment '退款成功金额',
    refund_cancel_amount          decimal(10,2) comment '发货前退款金额',
    refund_cancel_success_amount  decimal(10,2) comment '发货前退款成功金额',
    order_num                     bigint comment '下单件数',
    pay_num                       bigint comment '付款件数',
    ship_num                      bigint comment '发货件数',
    refund_apply_num              bigint comment '申退件数',
    refund_apply_success_num      bigint comment '退款成功件数',
    refund_cancel_num             bigint comment '发货前退款件数',
    refund_cancel_success_num     bigint comment '发货前退款成功件数',
    exchange_rate_cny_to_eur      decimal(10,2) comment '人名币兑换欧元汇率',
    exchange_rate_eur_to_cny      decimal(10,2) comment '欧元兑换人名币汇率',
    tax_rate                      decimal(10,2) comment '税率',
    cash_pay_amunt                decimal(10,2) comment '实付现金金额',
    prepay_card_amount            decimal(10,2) comment '购买预付卡金额',
    origin_prepay_card_amount     decimal(10,2) comment '购买预付卡本金金额',
    extra_prepay_card_amount      decimal(10,2) comment '购买预付卡赠送金额',
    use_prepay_card_amount        decimal(10,2) comment '使用预付卡总金额',
    use_origin_prepay_card_amount decimal(10,2) comment '使用预付卡本金金额',
    use_extra_prepay_card_amount  decimal(10,2) comment '使用预付卡赠送金额',
    order_prepay_deposit_amount   decimal(10,2) comment '预售支付定金',
    create_time                   timestamp comment '创建时间',
    update_time                   timestamp comment '更新时间',
    dc_system_time                timestamp comment '数据中台系统处理时间'
)
comment '订单详情标签表'
stored as orc;


--12
drop table if exists dw_dwd.dwd_ord_model_order_detail_basic_index_fl_d;
create table dw_dwd.dwd_ord_model_order_detail_basic_index_fl_d
(
sub_order_no                   string comment '子订单号',
order_no                       string comment '订单号',
order_channel                  string comment '订单渠道:TMALL/DOUYIN/APP/WEB/H5/MINIPROGRAM/OFFLINE',
day                            date comment '数据日期',
memberid                       string comment '会员id',
sku_code                       string comment '商品标识',
order_amount                   decimal(10,2) comment '下单金额',
pay_amount                     decimal(10,2) comment '付款金额',
ship_amount                    decimal(10,2) comment '发货金额',
refund_apply_amount            decimal(10,2) comment '申退金额',
refund_apply_success_amount    decimal(10,2) comment '退款成功金额',
refund_cancel_amount           decimal(10,2) comment '发货前退款金额',
refund_cancel_success_amount   decimal(10,2) comment '发货前退款成功金额',
order_num                      bigint comment '下单件数',
pay_num                        bigint comment '付款件数',
ship_num                       bigint comment '发货件数',
refund_apply_num               bigint comment '申退件数',
refund_apply_success_num       bigint comment '退款成功件数',
refund_cancel_num              bigint comment '发货前退款件数',
refund_cancel_success_num      bigint comment '发货前退款成功件数',
exchange_rate_cny_to_eur       decimal(10,2) comment '人名币兑换欧元汇率',
exchange_rate_eur_to_cny       decimal(10,2) comment '欧元兑换人名币汇率',
tax_rate                       decimal(10,2) comment '税率',
cash_pay_amunt                 decimal(10,2) comment '实付现金金额',
prepay_card_amount             decimal(10,2) comment '购买预付卡金额',
origin_prepay_card_amount      decimal(10,2) comment '购买预付卡本金金额',
extra_prepay_card_amount       decimal(10,2) comment '购买预付卡赠送金额',
use_prepay_card_amount         decimal(10,2) comment '使用预付卡总金额',
use_origin_prepay_card_amount  decimal(10,2) comment '使用预付卡本金金额',
use_extra_prepay_card_amount   decimal(10,2) comment '使用预付卡赠送金额',
order_prepay_deposit_amount    decimal(10,2) comment '预售支付定金',
create_time                    timestamp comment '创建时间',
update_time                    timestamp comment '更新时间',
dc_system_time                 timestamp comment '数据中台系统处理时间'
)
comment '子订单级原子指标'
stored as orc;

--ads 宽表

drop table if exists dw_ads.ads_ord_model_order_detail_fl_d;
create table dw_ads.ads_ord_model_order_detail_fl_d
(
--
    sub_order_no                     string comment '子订单号，oms：order_item_no，tmall：oid',
    order_no                         string comment '订单号，全渠道唯一',
    platform_sub_order_no            string comment '源平台子订单号：tmall/douyin/appoms订单号',
    platform_order_no                string comment '平台订单号：tmall/douyin/appoms订单号',
    refund_no                        string comment '退单号',
    platform_refund_no               string comment '源平台退款单号：tmall/douyin/appoms编号',
    order_category                   string comment '订单种类:销售订单/互动订单/礼品卡充值订单',
    order_channel                    string comment '订单渠道:TMALL/DOUYIN/APP/WEB/H5/MINIPROGRAM/OFFLINE',
    sale_platform                    string comment '销售渠道',
    sub_sale_platform                string comment '子销售渠道',
    jv_user_id                       string comment '中台用户id',
    member_id                        string comment '会员id',
    customer_no                      string comment '平台用户id：tmall：buyer_open_uid/douyin：openid/app：app_user_id',
--用户属性、标签，
    earlist_paydate                  date comment '用户最早的支付日期（剔除秒退）(天猫)',
    store_paydate                    date comment '用户最早的支付日期（剔除秒退）',
    staff_flag                       string comment '员工订单标签',
--商品维度，字段
    ynapchannel                      string comment '渠道。NAP，MRP，TPP',
    gift_order_flag                  string comment '赠品标签',
    brand_code                       string comment '品牌编号',
    brand_name                       string comment '品牌名称',
    size_schema                      string comment '尺寸',
    category_name                    string comment 'YNAP一级品类',
    subcategory_name                 string comment 'YNAP二级品类',
    sub_subcategory_name             string comment 'YNAP三级品类',
    model_id                         string comment 'Modelid',
    variant_id                       string comment 'Variantid',
    gtin                             string comment '商品条码',
    brand_size                       string comment '品牌尺码',
    sub_channel                      string comment '二级渠道',
    channel                          string comment '一级渠道',
    ftitle                           string comment '天猫商品名称',
    sale_season                      string comment 'YNAPSeason',
    fouteriid                        string comment 'Model级别Glscode',
    fpicpath                         string comment '商品图片',
    price                            decimal(10, 2) comment '商品天猫价格',
    fouterskuid                      string comment 'GTIN级别的glscode',
    hard_or_soft_luxury              string comment '软硬奢分类。HardLuxury：硬奢，SoftLuxury：软奢',
    numiid                           string comment '宝贝ID',
    gender                           string comment '性别',
    collocationinfo                  string comment '巴黎世家商品系列名称',
    merchant_gender                  string comment '采购部门定义的性别',
    product_url                      string comment '宝尊的商品照片',
    brand_colour                     string comment '品牌颜色',
    hard_luxury                      string comment '是否是硬奢',
    category_id_new                  string comment '处理后的一级品类编号',
    ynaprichemonttppchannel          string comment '渠道。YNAP，历峰和TPP',
    retail_price                     decimal(10, 2) comment '零售价',
    brand_simple_name                string comment '品牌简称',
    category_name_manual             string comment '采购部门定义的一级品类',
    subcategory_name_manual          string comment '采购部门定义的二级品类',
    sub_subcategory_name_manual      string comment '采购部门定义的三级品类',
    ynap_full_price                  decimal(10, 2) comment 'YNAP原价',
    merchant_season                  string comment '采购部门定义的商品季节',
    pid_style_clr                    string comment 'ProductID',
    model_fabric_colour              string comment 'Model级别的商品颜色',
    model_fabric                     string comment '面料',
    subchannel_hl_sl_alia_buccellati string comment '三级渠道',
    napmrprichemontchannel           string comment '渠道。NAP,MRP,Richemont,TTP'
)
comment '订单宽表'
stored as orc;

drop table if exists dw_ads.ads_ord_model_order_detail_fl_d;
CREATE TABLE dw_ads.ads_ord_model_order_detail_fl_d
(
--     用户相关字段
    jv_user_id                       string COMMENT '用户唯一标识',

--     商品相关字段
    brand_code                       string COMMENT '品牌编号',
    brand_name                       string COMMENT '品牌名称',
    size_schema                      string COMMENT '尺寸',
    category_name                    string COMMENT 'YNAP一级品类',
    subcategory_name                 string COMMENT 'YNAP二级品类',
    sub_subcategory_name             string COMMENT 'YNAP三级品类',
    model_id                         string COMMENT 'Model id',
    variant_id                       string COMMENT 'Variant id',
    sale_season                      string COMMENT 'YNAP Season',
    gtin                             string COMMENT '商品条码',
    fouterskuid                      string COMMENT 'GTIN级别的glscode',
    batch_no                         string COMMENT '批次号',
    brand_size                       string COMMENT '品牌尺码',
    sub_channel                      string COMMENT '二级渠道',
    channel                          string COMMENT '一级渠道',
    ftitle                           string COMMENT '天猫商品名称',
    fouteriid                        string COMMENT 'Model级别Glscode',
    ynapchannel                      string COMMENT '渠道。NAP，MRP，TPP',
    napmrprichemontchannel           string COMMENT '渠道。NAP,MRP,Richemont,TTP',
    model_fabric_colour              string COMMENT 'Model级别的商品颜色',
    merchant_season                  string COMMENT '采购部门定义的商品季节',
    pid_style_clr                    string COMMENT 'Product ID',
    ynap_full_price                  double COMMENT 'YNAP原价',
    category_name_manual             string COMMENT '采购部门定义的一级品类',
    subcategory_name_manual          string COMMENT '采购部门定义的二级品类',
    sub_subcategory_name_manual      string COMMENT '采购部门定义的三级品类',
    brand_simple_name                string COMMENT '品牌简称',
    subchannel_hl_sl_alia_buccellati string COMMENT '三级渠道',
    retail_price                     double COMMENT '零售价',
    ynaprichemonttppchannel          string COMMENT '渠道。YNAP，历峰和TPP',
    category_id_new                  string COMMENT '处理后的一级品类编号',
    hard_luxury                      string COMMENT '是否是硬奢',
    hard_or_soft_luxury              string COMMENT '软硬奢分类。Hard Luxury：硬奢，Soft Luxury：软奢',
    numiid                           string COMMENT '宝贝ID',
    gender                           string COMMENT '性别',
    product_url                      string COMMENT '宝尊的商品照片',
    brand_colour                     string COMMENT '品牌颜色',
    merchant_gender                  string COMMENT '采购部门定义的性别',

--  订单相关字段

--  收货地址类
    freceivercity                    string COMMENT '收货城市',                 --主
    freceiverdistrict                string COMMENT '收货行政区',               --主
    freceiverstate                   string COMMENT '收货省份',                 --主
    freceiveraddress                 string COMMENT '收货地址',                 --主
    freceivermobile                  string COMMENT '收货人手机号',              --主
    freceivername                    string COMMENT '收货人姓名',                --主

--  时间类
    createdate                       date COMMENT '创建日期',
    fconsigntime                     timestamp COMMENT '发货时间',
    fpaytime                         timestamp COMMENT '支付时间',
    refundcreatetime                 string COMMENT '退款创建时间',
    paydate                          date COMMENT '支付日期',                   --timeline
    shipdate                         date COMMENT '发货时间',                   --timeline
    enddate                          date COMMENT '交易结束时间',
    refundapplydate                  date COMMENT '退款申请日期',
    confirm_time                     timestamp COMMENT '订单确认时间',
    storage_confirm_time             timestamp COMMENT '退货入库时间',
    collect_parcel_start_time        timestamp COMMENT '客人退货时间',
    child_order_receive_time         timestamp COMMENT '客人收货时间',
    fendtime                         timestamp COMMENT '订单结束时间',
    fcreated                         timestamp COMMENT '订单创建时间',
-- 状态类
    fstatus                          string COMMENT '订单状态',
    frefundstatus                    string COMMENT '退款状态：WAIT_SELLER_AGREE，买家已经申请退款，等待卖家同意；WAIT_BUYER_RETURN_GOODS，卖家已经同意退款，等待买家退货；WAIT_SELLER_CONFIRM_GOO',
    fgoodstatus                      string COMMENT '货品状态：BUYER_NOT_RECEIVED，买家未收到货；BUYER_RECEIVED，买家收到货；BUYER_RETURNED_GOODS，买家退货',
    refund_status                    string COMMENT '自建退款状态',
    order_status                     string COMMENT '自建订单状态',

-- 数量类
    fnum                             int COMMENT '商品数量',
    refundnum                        int COMMENT '退货数量',
    return_applied_units             int COMMENT '申请退货商品数量',
    return_units                     int COMMENT '退货件数。有商品退回',
    gross_sales_units                int COMMENT '日销售数量（发货）',
    cancelled_successed_units        int COMMENT '成功取消订单件数',
    cancelled_applied_units          int COMMENT '申请取消订单件数',
    updated_dgso_units               int COMMENT '日销售量（付款且在发货前之后一直未取消订单）',
--     金额类
    discountfee                      double COMMENT '折扣金额',
    payment                          double COMMENT '支付金额',
    totalfee                         double COMMENT '订单总金额',
    price                            double COMMENT '商品天猫价格',
    updated_dgso                     double COMMENT '日销售金额（付款且在发货前之后一直未取消订单）',
    return_applied_sales             double COMMENT '申请退货商品金额',
    gross_sales                      double COMMENT '日销售额（发货）',
    return_sales                     double COMMENT '退款金额。不区分仅退款和退货退款',
    cancelled_successed_sales        double COMMENT '成功取消订单金额',
    cancelled_applied_sales          double COMMENT '申请取消订单金额',
    frefundfee                       double COMMENT '退款金额',
    markdown_price                   double COMMENT '天猫划线价',
    basic_price                      double COMMENT '本金',
    expand_price                     double COMMENT '膨胀金',
    basic_price_refund               double COMMENT '商品行退本金不含运费=(订单本金/商品行实付金额*退款金额)*(商品行实付金额/订单实付金额)',
    expand_price_refund              double COMMENT '商品行退膨胀金不含运费=(订单膨胀金/商品行实付金额*退款金额)*(商品行实付金额/订单实付金额)',
    ynap_cost                        string COMMENT 'ynap回传的采购价',

-- 标签类
    fhasgoodreturn                   string COMMENT '是否有退货。true，退货；false，退款',
    is_member                        int COMMENT '是否是会员',
    88vip_flag                       int COMMENT '88VIP标签',
    free_order                       string COMMENT '区分0元订单标记 0元订单则为1 其他为0',
    memberid                         string comment 'memberid 会员id',
    check_jit                        int COMMENT '是否是jit商品 1是 2否',
    jit_stcok                        int COMMENT '是否现货 1是 2否',
    updated_cancel_flag              int COMMENT '订单发货前一直未取消标签。1：取消，2：未取消',

-- 编号类
    staff_flag                       string COMMENT '员工订单标签',
    frefundid                        string COMMENT '退货编号',
    ftid                             string COMMENT '母订单号',
    foid                             string COMMENT '子订单号',
    batch_code                       string COMMENT '库存批次号 PO',
    delivery_no                      string COMMENT '出库单号',
    virtual_warehouse_code           string COMMENT '仓存编号',
    nu                               string COMMENT '国内物流单号',
    logi_no                          string COMMENT '国际物流单号',


--     订单其他
    sale_platform                    string COMMENT '销售平台,TMALL/APP',
    sub_sale_platform                string COMMENT '销售子渠道',
    buyer_nick                       string COMMENT '不加密的买家名',
    freason                          string COMMENT '申请退款原因',
    ftype                            string COMMENT '是否预售。step：是，fixed：否',
    fpicpath                         string COMMENT '商品图片',
    buyer_customer_no                string COMMENT '买家客户号，TMALL是buyer_nickname_encry，APP是login_code',
    paydate_val_rate                 double COMMENT '按照支付日期匹配的汇率',
    order_type                       int COMMENT '订单类型',
    level                            int COMMENT '会员等级',
    content                          string COMMENT '评价内容',
    reply                            string COMMENT '客服答复',
    payment_mode                     string COMMENT '支付方式 微信-wechat,支付宝-alipay,银联-uniepay,积分-points'
) stored as orc;


select * from dw_ads.ads_ord_sales_data_fl_d;

desc dw_ads.ads_ord_model_order_detail_fl_d;

--单独比较数据的脚本模型  tbgoods

select
a.numiid,a.flisttime,b.flisttime,a.fmodified,b.fmodified
from
dw_dwd.dwd_jv_api_t_ec_tbgoods_fl_d a
join
 dw_dwd.dwd_jv_ecc_t_ec_tbgoods_fl_d b
on a.numiid=b.numiid and b.dt='20230323'
where nvl(a.flisttime,'')!= nvl(b.flisttime,'') limit 100;

select fdelisttime,dt,fmodified
from dw_tmp.tmp_dwd_jv_ecc_t_ec_tbgoods_fl_d_02
where fnumiid='616060443059'
order by dt;


select fdelisttime from dw_ods.ods_jv_ecc_t_ec_tbgoods_fl_d where dt='20230323' and fnumiid='602939545441'
union all
select fdelisttime from dw_ods.ods_jv_ecc_t_ec_tbgoods_fl_d where dt='20230322' and fnumiid='602939545441';





select fdelisttime,dt from dw_tmp.tmp_dwd_jv_ecc_t_ec_tbgoods_fl_d_02 where fnumiid='666047990790';






--单独比较数据的脚本模型  tbsku

select
b.fnumiid,b.fskuid,a.fquantity,b.fquantity,a.fmodified,b.fmodified
from
dw_dwd.dwd_jv_api_t_ec_tbsku_fl_d a  -- api
join
 dw_tmp.dwd_jv_api_t_ec_tbsku_fl_d b  --ecc源
on  a.fnumiid = b.fnumiid and a.fskuid=b.fskuid
where nvl(a.fquantity,'')!= nvl(b.fquantity,'') limit 100;
-- where nvl(cast(a.fprice as decimal(10,2)),'')!=nvl(cast(b.fprice as decimal(10,2)),'');

select fquantity,dt from dw_tmp.tmp_dwd_jv_api_t_ec_tbsku_fl_d_02
where fnumiid='658548944933' and fskuid='4756984357736';

select dt,
       content
from dw_ods.ods_app_tmall_items_seller_list_get_topic_iu_h
where dt >= '2023032221'
and  dt <= '2023032316'  and content like "%4756984357736%" and content like '%658548944933%'
order by dt;






-- sobill 比较


-- 问题统计 freceivermobile buyer_nick

select
a.ftid,a.fcreated,a.fsteppaidfee,b.fsteppaidfee
from
dw_dwd.dwd_jv_api_t_sd_tbsobill_fl_d a
join
 dw_dwd.dwd_jv_ecc_t_sd_tbsobill_fl_d b
on a.ftid=b.ftid
-- where nvl(a.fsteppaidfee,'')!= nvl(b.fsteppaidfee,'')
where nvl(cast(a.fsteppaidfee as decimal(10,2)),'')!=nvl(cast(b.fsteppaidfee as decimal(10,2)),'')
and a.fcreated<='2023-06-12'
;
1896644679893607451    2023-05-26 20:00:16    1402.25    0.00
1896706562551840950    2023-05-26 20:11:08    1047.85    0.00
1896765206429811261    2023-05-26 20:01:24    1523.82    0.00
1896765206430811261    2023-05-26 20:01:24    734.29 0.00

select fmodified,dt from dw_tmp.dwd_jv_api_t_sd_tbsobill_fl_d_02 where ftid='3244545649279337935';


--refund 比较





select
a.frefundid,a.frefundfee,b.frefundfee,a.fmodified,b.fmodified
from
dw_dwd.dwd_jv_api_t_sd_tbrefund_fl_d a
join
dw_dwd.dwd_jv_ecc_t_sd_tbrefund_fl_d b
on a.frefundid=b.frefundid
-- where nvl(a.frefundfee,'')!= nvl(b.frefundfee,'') ;
where nvl(cast(a.frefundfee as decimal(10,2)),'')!=nvl(cast(b.frefundfee as decimal(10,2)),'');




















-- where nvl(cast(a.ftotalfee as decimal(10,2)),'')!=nvl(cast(b.ftotalfee as decimal(10,2)),'');
select * from dw_dwd.dwd_app_oms_sync_time_fl_d;
select dt,content
from dw_ods.ods_app_tmall_refund_get_topic_iu_h
where  dt >= '2023030722' and  dt <= '2023030900'
and get_json_object(get_json_object(get_json_object(content,'$.refund_get_response'),'$.refund'),'$.refund_id')='207973621733465056'
order by dt;

--soentry 比较
select
a.foid,a.fpayment,b.fpayment,a.fmodified
from
dw_dwd.dwd_jv_api_t_sd_tbsoentry_fl_d a
join
dw_dwd.dwd_jv_ecc_t_sd_tbsoentry_fl_d b
on a.foid=b.foid
-- where nvl(a.ftimeoutactiontime,'')!= nvl(b.ftimeoutactiontime,'') order by a.fmodified;
where nvl(cast(a.fpayment as decimal(10,2)),'')!=nvl(cast(b.fpayment as decimal(10,2)),'');



select dt,
       content
from(
    select
         dt,
         content
    from  dw_ods.ods_app_tmall_trade_fullinfo_get_topic_iu_h
    where  dt >= '2023032922'
      and  dt <= '2023033100'
)a1
where content like '%3285359931212211734%'
order by dt;

select * from dw_dwm.dwm_jv_item_sales_data_dtl_n_fl_d_new;
show create table dw_dwd.dwd_ecc_master_order_info_fl_d;







select count(1),count(fnumiid) from dw_dwd.dwd_jv_api_t_ec_tbgoods_fl_d ;
select count(1),count(fnumiid) from dw_dwd.dwd_jv_ecc_t_ec_tbgoods_fl_d where dt='20230312' ;







select count(1) from dw_ods.ods_app_tmall_trade_fullinfo_get_topic_iu_h where dt>='2023030600' and dt<'2023030700'
union all
select count(1) from dw_ods.ods_app_tmall_items_seller_list_get_topic_iu_h where dt>='2023030600' and dt<'2023030700';


select count(1),count(distinct fnumiid),count(distinct numiid) from dw_dwd.dwd_jv_ecc_t_ec_tbgoods_fl_d where dt='20230307';
select count(1),count(distinct fnumiid),count(distinct numiid) from dw_dwd.dwd_jv_ecc_t_ec_tbgoods_fl_d where dt='20230306';
select count(1),count(distinct fnumiid),count(distinct numiid) from dw_dwd.dwd_jv_api_t_ec_tbgoods_fl_d ;




create function EXPLODE_JSON_ARRAY ;

create table dw_ods.ods_app_tmall_trade_fullinfo_get_topic_iu_h(
    content string
)
partitioned by (dt string);
create table dw_ods.ods_app_tmall_items_seller_list_get_topic_iu_h(
    content string
)
partitioned by (dt string);


create table dw_ods.ods_app_tmall_refund_get_topic_iu_h(
    content string
)
partitioned by (dt string);


CREATE TABLE dw_dwd.dwd_jv_api_t_ec_tbgoods_fl_d(
  `fid` string COMMENT '',
  `faftersaleid` bigint COMMENT '',
  `fapprovestatus` string COMMENT '',
  `fauctionpoint` bigint COMMENT '',
  `fautofill` string COMMENT '',
  `fchangeprop` string COMMENT '',
  `fcid` bigint COMMENT '',
  `fcodpostageid` bigint COMMENT '',
  `fcreated` timestamp COMMENT '',
  `fdelisttime` timestamp COMMENT '',
  `fdesc` string COMMENT '',
  `fdetailurl` string COMMENT '',
  `femsfee` string COMMENT '',
  `fexpressfee` string COMMENT '',
  `ffeatures` string COMMENT '',
  `ffreightpayer` string COMMENT '',
  `fglobalstocktype` string COMMENT '',
  `fhasdiscount` int COMMENT '',
  `fhasinvoice` int COMMENT '',
  `fhasshowcase` int COMMENT '',
  `fhaswarranty` int COMMENT '',
  `fincrement` string COMMENT '',
  `finnershopauctiontemplateid` bigint COMMENT '',
  `finputpids` string COMMENT '',
  `finputstr` string COMMENT '',
  `fis3d` int COMMENT '',
  `fisex` int COMMENT '',
  `fisfenxiao` bigint COMMENT '',
  `fislightningconsignment` int COMMENT '',
  `fisprepay` int COMMENT '',
  `fistaobao` int COMMENT '',
  `fistiming` int COMMENT '',
  `fisvirtual` int COMMENT '',
  `fisxinpin` int COMMENT '',
  `fitemsize` string COMMENT '',
  `fitemweight` string COMMENT '',
  `flisttime` timestamp COMMENT '',
  `fmodified` timestamp COMMENT '',
  `fnick` string COMMENT '',
  `fnum` bigint COMMENT '',
  `fnumiid` bigint COMMENT '',
  `fonestation` int COMMENT '',
  `fouterid` string COMMENT '',
  `foutershopauctiontemplateid` bigint COMMENT '',
  `fpicurl` string COMMENT '',
  `fpostfee` string COMMENT '',
  `fpostageid` bigint COMMENT '',
  `fprice` string COMMENT '',
  `fproductid` bigint COMMENT '',
  `fpromotedservice` string COMMENT '',
  `fpropertyalias` string COMMENT '',
  `fprops` string COMMENT '',
  `fpropsname` string COMMENT '',
  `fscore` bigint COMMENT '',
  `fsecondkill` string COMMENT '',
  `fsellpoint` string COMMENT '',
  `fsellpromise` int COMMENT '',
  `fsellercids` string COMMENT '',
  `fstuffstatus` string COMMENT '',
  `fsubstock` bigint COMMENT '',
  `ftemplateid` string COMMENT '',
  `ftitle` string COMMENT '',
  `ftype` string COMMENT '',
  `fvalidthru` bigint COMMENT '',
  `fviolation` int COMMENT '',
  `fvolume` bigint COMMENT '',
  `fwapdesc` string COMMENT '',
  `fwapdetailurl` string COMMENT '',
  `fwithholdquantity` bigint COMMENT '',
  `fwwstatus` int COMMENT '',
  `fecplatformshopid` string COMMENT '',
  `fversion` bigint COMMENT '',
  `fisdelete` int COMMENT '',
  `fautoup` int COMMENT '',
  `fautodelist` int COMMENT '',
  `fautostuff` int COMMENT '',
  `fdelistvalue` decimal(28,10) COMMENT '',
  `fstuffvalue` decimal(28,10) COMMENT '',
  `fupqty` decimal(28,10) COMMENT '',
  `fupsuccess` int COMMENT '',
  `flastuptime` timestamp COMMENT '',
  `fupfailreason` string COMMENT '',
  `flastupdatetime` timestamp COMMENT '',
  `flastupdateuser` string COMMENT '',
  `ftenantid` bigint COMMENT '租户id',
  `fsyncrate` decimal(5,2) COMMENT '同步比例',
  `fsyncmode` tinyint COMMENT '',
  `numiid` bigint COMMENT '宝贝ID格式化后字段'
);














CREATE TABLE dw_dwd.dwd_ynap_oms_tmall_order_item_fl_d(
  `id` bigint COMMENT '订单行表主键',
  `order_item_no` string COMMENT '订单行编号',
  `sharding_key_order_no` string COMMENT '主单编号，用于分库分表拆分键使用',
  `product_code` string COMMENT '商品sku号',
  `spu_code` string COMMENT '商品spu号',
  `model_code` string COMMENT 'model_code',
  `product_quantity` bigint COMMENT '商品数量',
  `batch_code` string COMMENT '库存批次号 PO',
  `unit_price` decimal(10,2) COMMENT '商品单价',
  `product_name` string COMMENT '商品名称',
  `product_image` string COMMENT '商品图片',
  `order_line_type` bigint COMMENT '1：普通产品；\r\n2：购物车赠品\r\n3：商品赠品',
  `gift_flag` bigint COMMENT '是否是赠品 true 是 false 不是',
  `promotion_code` string COMMENT '促销编号',
  `promotion_name` string COMMENT '促销名称',
  `gift_source_sku_code` string COMMENT '赠品来源商品编号',
  `virtual_warehouse_code` string COMMENT '仓存编号',
  `returnable_flag` bigint COMMENT '是否可作退货，某些产品购买后不能作退货',
  `product_label` string COMMENT '商品标签',
  `order_item_status` bigint COMMENT '订单行状态 待付款 1\n交易关闭 2\n已付款 3\n待审核 4\n待发货 5\n待收货 6\n已签收 7\n延长售后 8\n交易完成(已签收未关闭售后入口) 9\n交易完成(已签收且已关闭售后入口) 10\n拦截失败-11\n退款中-12\n退款成功-13\n商品入库失败-14',
  `intercept_status` bigint COMMENT '拦截状态 -1 失败 1 成功',
  `aftersales_time` timestamp COMMENT '延长售后时间',
  `trading_closed_time` timestamp COMMENT '交易关闭时间',
  `create_user_id` string,
  `update_user_id` string,
  `version` bigint,
  `deleted` bigint COMMENT '0 删除 1 未删除',
  `create_user_name` string,
  `update_user_name` string,
  `create_time` timestamp,
  `update_time` timestamp,
  `sort_id` bigint,
  `partner_id` bigint,
  `product_markdown_price` decimal(10,2) COMMENT 'markdown价格',
  `product_eip_price` decimal(10,2) COMMENT 'EIP价格',
  `tms_service_code` string COMMENT '物流公司编号',
  `tms_service_name` string COMMENT '物流公司名称',
  `tms_number` string COMMENT '物流编号',
  `category_code` string COMMENT '商品销售一级类目',
  `brand_name` string COMMENT '品牌英文名',
  `product_size` string COMMENT '商品尺码',
  `product_colour` string COMMENT '商品颜色',
  `actually_amount` decimal(10,2) COMMENT '实付金额',
  `virtual_category` string COMMENT '虚拟类目',
  `delivery_item_no` bigint COMMENT '出库单号',
  `sku_product_label` string COMMENT 'sku标签',
  `spu_product_label` string COMMENT 'spu标签',
  `sku_tag_label` string COMMENT 'sku所有标签',
  `invoice_amount` decimal(10,2) COMMENT '开票金额',
  `refund_amount` decimal(10,2) COMMENT '退款金额',
  `reserve_stock_number` string COMMENT 'EIP预留商品预占单号',
  `freight` decimal(10,2) COMMENT '运费分摊(抖音用)',
  `sale_day` bigint COMMENT '下单时配置的售后期',
  `tms_number_flag` bigint COMMENT '下单时退货退款物流设置标识 0 不向宝尊申请物流单号 ,1 向宝尊申请物流单号',
  `check_jit` bigint COMMENT '是否是jit商品 1是 2否',
  `jit_stcok` bigint COMMENT '是否现货 1是 2否',
  `omni_stock_num` bigint COMMENT 'omni-库存分配记录数量使用',
  `asn_stock_num` bigint COMMENT 'omni-ASN记录数量使用',
  `oid` string COMMENT '天猫发货通知',
  `package_type` bigint COMMENT '包装类型');
CREATE TABLE dw_dwd.dwd_ynap_oms_tmall_order_delivery_fl_d(
  `id` bigint COMMENT '主键',
  `order_no` string COMMENT '订单编号',
  `sharding_key_order_no` string COMMENT '团单编号，用于分库分表拆分键使用',
  `delivery_no` string COMMENT '出库单编号',
  `virtual_warehouse_code` string COMMENT '虚拟仓库编号',
  `product_quantity` bigint COMMENT '商品数量',
  `delivery_status` bigint COMMENT '出库单状态\r\n待发货-1\r\n取消-2\r\n已发货-3\r\n已签收-4\r\n已拒收-5',
  `tms_service_code` string COMMENT '物流公司编号',
  `tms_service_name` string COMMENT '物流公司名称',
  `tms_number` string COMMENT '物流编号',
  `receiving_address_code` string COMMENT '收货地址id',
  `receiving_address` string COMMENT '收货地址',
  `receiver_name` string COMMENT '收货人',
  `receiver_phone_number` string COMMENT '收货人手机号',
  `shipping_address` string COMMENT '发货地址',
  `consignee_name` string COMMENT '收货人',
  `consignee_phone_number` string COMMENT '收货人电话',
  `deleted` bigint COMMENT '状态 0未删除 1已删除',
  `intercept_status` bigint COMMENT '拦截状态 -1拦截失败  1 拦截成功',
  `create_time` timestamp,
  `update_time` timestamp,
  `version` bigint COMMENT '数据版本',
  `sort_id` bigint COMMENT '显示顺序',
  `partner_id` bigint COMMENT '合作方id',
  `create_user_name` string COMMENT '创建用户名',
  `create_user_id` string COMMENT '创建uid',
  `update_user_name` string COMMENT '更新用户名',
  `update_user_id` string COMMENT '更新uid',
  `stock_number` string COMMENT '仓库编号',
  `order_remark` string COMMENT '订单备注',
  `phone` string COMMENT '会员手机号',
  `rel_order_no` string COMMENT '第三方数据订单编号',
  `channel` bigint COMMENT '渠道',
  `user_tags` string COMMENT '下单是用户命中的标签',
  `user_tag_ids` string,
  `delivery_type` bigint COMMENT '出库单类型 1.非JIT 2.JIT现货 3.JIT非现货',
  `send_ecc` bigint COMMENT '是否下发ecc 0未下发  1已下发  omni专用',
  `package_type` bigint);
CREATE TABLE dw_dwd.dwd_ynap_oms_tmall_order_delivery_items_fl_d(
  `id` bigint,
  `sharding_key_order_no` string COMMENT '团单编号，用于分库分表拆分键使用',
  `delivery_no` string COMMENT '出库单编号',
  `delevery_item_no` string COMMENT '出库单行编号',
  `sku_code` string COMMENT '商品编号',
  `product_quantity` bigint COMMENT '商品数量',
  `product_name` string COMMENT '商品名称',
  `product_img_url` string COMMENT '商品图片url',
  `item_type` bigint COMMENT '1：普通商品；\r\n2：购物车赠品\r\n3：商品赠品',
  `stock_number` string COMMENT '仓库编号',
  `virtual_warehouse_code` string COMMENT '虚拟仓库编号',
  `delivery_line_status` bigint COMMENT '商品行状态',
  `push_status` bigint COMMENT '推送状态\r\n已推送-1\r\n无需推送-2\r\n已取消-3',
  `deleted` bigint COMMENT '状态 0未删除 1已删除',
  `order_item_state` string COMMENT '订单行的状态',
  `create_time` timestamp,
  `update_time` timestamp,
  `version` bigint COMMENT '数据版本',
  `sort_id` bigint COMMENT '显示顺序',
  `partner_id` bigint COMMENT '合作方id',
  `create_user_name` string COMMENT '创建用户名',
  `create_user_id` string COMMENT '创建uid',
  `update_user_name` string COMMENT '更新用户名',
  `update_user_id` string COMMENT '更新uid',
  `jit_stock` bigint COMMENT '是否现货 1现货 2非现货',
  `package_type` bigint);
CREATE TABLE dw_dwd.dwd_ynap_oms_tmall_order_fl_d(
  `id` bigint COMMENT '订单表主键',
  `sharding_key_order_no` string COMMENT '主单编号，用于分库分表拆分键使用',
  `channel_code` bigint COMMENT '渠道：APP，WEBSITE, EIP',
  `order_no` string COMMENT '订单编号',
  `login_code` string COMMENT '登录用户编号',
  `login_type` bigint COMMENT '会员身份:0-普通会员，1-EIP',
  `login_name` string COMMENT '用户名',
  `phone` string COMMENT '手机号',
  `order_amount` decimal(10,2) COMMENT '订单金额 （商品总金额）',
  `discount_amount` decimal(10,2) COMMENT '订单折扣金额（营销优惠）',
  `coupon_amount` decimal(10,2) COMMENT '订单优惠金额（优惠券）',
  `payable_amount` decimal(10,2) COMMENT '应支付金额',
  `refunded_amount` decimal(10,2) COMMENT '已退款金额',
  `order_status` bigint COMMENT '订单状态\r\n待付款 1\r\n交易关闭 2\r\n已付款 3\r\n待审核 4\r\n待发货 5\r\n待收货 6\r\n已签收 7\r\n延长售后 8\r\n交易完成(已签收未关闭售后入口) 9\r\n交易完成(已签收且已关闭售后入口) 10\r\n拦截失败-11\r\n退款中-12\r\n退款成功-13\r\n商品入库失败-14',
  `previewing_status` bigint COMMENT '预检状态(0-未预检,1-预检不通过,2-预检通过)',
  `previewing_reason` string COMMENT '预检失败原因',
  `payment_status` bigint COMMENT '付款状态\r\n1.未支付\r\n2.已支付\r\n3.已退款',
  `shipment_status` bigint COMMENT '出货状态 1 待发货 2 已取消 3 已发货 4已拒绝',
  `imanual_cancel_flag` bigint COMMENT '是否手动取消\r\n1-是 0-否',
  `invoice_code` string COMMENT '开票信息id，用户中心存储的开发票信息编号',
  `invoice_flag` bigint COMMENT '是否开票\r\n1-是 0-否',
  `invoice_status` bigint COMMENT '发票状态\r\n1-未开票\r\n2-已开票',
  `currency_code` string COMMENT '货币编号，默认RMB',
  `delivery_type` string COMMENT '送货方式，默认方式-快递',
  `payment_time` timestamp COMMENT '支付时间',
  `payment_balance_time` timestamp COMMENT '支付尾款时间',
  `receiving_address_code` string COMMENT '收货地址id',
  `receiver_phone_number` string COMMENT '收货人手机号',
  `receiver_name` string COMMENT '收货人',
  `receiving_address` string COMMENT '收货地址',
  `receiving_address_decrypt` string,
  `card_content` string COMMENT '贺卡内容',
  `package_type` bigint COMMENT '包装类型',
  `package_name` string COMMENT '包装类型名称',
  `order_type` bigint COMMENT '订单类型：\r\n销售订单 sale\r\n线下订单 offline\r\nPre-Order preOrder',
  `pay_times` bigint COMMENT '发起支付次数\r\n调用支付中心发起支付接口单号 = 订单号+支付次数',
  `order_remark` string COMMENT '订单备注',
  `refund_remark` string COMMENT '破例退货备注',
  `cs_remark` string COMMENT '客服备注',
  `create_user_id` string COMMENT '创建人id',
  `update_user_id` string COMMENT '修改人id',
  `create_user_name` string COMMENT '创建人姓名',
  `update_user_name` string COMMENT '修改人姓名',
  `create_time` timestamp COMMENT '创建时间',
  `update_time` timestamp COMMENT '修改时间',
  `sort_id` bigint,
  `version` bigint COMMENT '版本号',
  `deleted` bigint COMMENT '状态 0未删除  1已删除',
  `order_time` timestamp COMMENT '下单时间',
  `freight` decimal(10,2) COMMENT '运费',
  `partner_id` bigint,
  `sync_status` bigint COMMENT '同步OMS状态:0-未同步,1-同步成功,2-同步失败',
  `ps_user_id` string COMMENT 'PS客服编号',
  `shops` string COMMENT '店铺',
  `serial_number` string COMMENT 'excel导入编号',
  `sub_channel` bigint COMMENT '订单来源',
  `operator` string COMMENT '操作人',
  `freight_refund_flag` bigint COMMENT '是否已退运费',
  `system_source` bigint COMMENT '系统来源  1 IOS 2 安卓 3PC 4E EIP',
  `ps_submit_flag` bigint COMMENT '是否是PS代客下单',
  `user_grade` bigint COMMENT '会员下单等级',
  `points` decimal(10,2) COMMENT '积分',
  `buyer_name` string COMMENT '礼品卡购买人姓名',
  `id_number` string COMMENT '礼品卡购买人身份证',
  `buyer_phone_number` string COMMENT '礼品卡购买人手机',
  `refunded_card_amount` decimal(10,2) COMMENT '已退款礼品卡金额',
  `affiliate_flag` bigint COMMENT '是否是Affiliate用户',
  `user_tag_ids` string COMMENT '下单是用户命中的标签id',
  `use_card_amount` decimal(10,2) COMMENT '礼品卡使用金额',
  `residue_amount` decimal(10,2) COMMENT '剩余应付金额 （扣除礼品卡后的支付现金）',
  `rel_order_no` string COMMENT '第三方数据订单编号',
  `dy_refund_flag` bigint COMMENT '抖音订单退款标志\r\n0:未退款\r\n1.未开票订单:请在发票后台开具蓝票\r\n2.已开票订单:请在发票后台红冲后重新开蓝票\r\n3.开票金额和OMS金额不一致,冲红后开票\r\n4:发生邮件成功\r\n5:发生邮件失败\r\n',
  `user_tags` string COMMENT '下单是用户命中的标签',
  `utm_source` string,
  `utm_content` string,
  `is_douyin_notstock` bigint COMMENT '抖音未预占库存就发生退款标识',
  `good_stock` bigint COMMENT '订单是否全部现货 1是 2否',
  `right_jit` bigint COMMENT '订单是否存在JIT商品 1是 2否',
  `oaid` string COMMENT 'oaid',
  `package_time` timestamp COMMENT '',
  `pay_type` string COMMENT '支付方式1.微信 2.支付宝 3.银联',
  `buyer_memo` string COMMENT '');
CREATE TABLE dw_dwd.dwd_ynap_oms_tmall_order_item_fl_d(
  `id` bigint COMMENT '订单行表主键',
  `order_item_no` string COMMENT '订单行编号',
  `sharding_key_order_no` string COMMENT '主单编号，用于分库分表拆分键使用',
  `product_code` string COMMENT '商品sku号',
  `spu_code` string COMMENT '商品spu号',
  `model_code` string COMMENT 'model_code',
  `product_quantity` bigint COMMENT '商品数量',
  `batch_code` string COMMENT '库存批次号 PO',
  `unit_price` decimal(10,2) COMMENT '商品单价',
  `product_name` string COMMENT '商品名称',
  `product_image` string COMMENT '商品图片',
  `order_line_type` bigint COMMENT '1：普通产品；\r\n2：购物车赠品\r\n3：商品赠品',
  `gift_flag` bigint COMMENT '是否是赠品 true 是 false 不是',
  `promotion_code` string COMMENT '促销编号',
  `promotion_name` string COMMENT '促销名称',
  `gift_source_sku_code` string COMMENT '赠品来源商品编号',
  `virtual_warehouse_code` string COMMENT '仓存编号',
  `returnable_flag` bigint COMMENT '是否可作退货，某些产品购买后不能作退货',
  `product_label` string COMMENT '商品标签',
  `order_item_status` bigint COMMENT '订单行状态 待付款 1\n交易关闭 2\n已付款 3\n待审核 4\n待发货 5\n待收货 6\n已签收 7\n延长售后 8\n交易完成(已签收未关闭售后入口) 9\n交易完成(已签收且已关闭售后入口) 10\n拦截失败-11\n退款中-12\n退款成功-13\n商品入库失败-14',
  `intercept_status` bigint COMMENT '拦截状态 -1 失败 1 成功',
  `aftersales_time` timestamp COMMENT '延长售后时间',
  `trading_closed_time` timestamp COMMENT '交易关闭时间',
  `create_user_id` string,
  `update_user_id` string,
  `version` bigint,
  `deleted` bigint COMMENT '0 删除 1 未删除',
  `create_user_name` string,
  `update_user_name` string,
  `create_time` timestamp,
  `update_time` timestamp,
  `sort_id` bigint,
  `partner_id` bigint,
  `product_markdown_price` decimal(10,2) COMMENT 'markdown价格',
  `product_eip_price` decimal(10,2) COMMENT 'EIP价格',
  `tms_service_code` string COMMENT '物流公司编号',
  `tms_service_name` string COMMENT '物流公司名称',
  `tms_number` string COMMENT '物流编号',
  `category_code` string COMMENT '商品销售一级类目',
  `brand_name` string COMMENT '品牌英文名',
  `product_size` string COMMENT '商品尺码',
  `product_colour` string COMMENT '商品颜色',
  `actually_amount` decimal(10,2) COMMENT '实付金额',
  `virtual_category` string COMMENT '虚拟类目',
  `delivery_item_no` bigint COMMENT '出库单号',
  `sku_product_label` string COMMENT 'sku标签',
  `spu_product_label` string COMMENT 'spu标签',
  `sku_tag_label` string COMMENT 'sku所有标签',
  `invoice_amount` decimal(10,2) COMMENT '开票金额',
  `refund_amount` decimal(10,2) COMMENT '退款金额',
  `reserve_stock_number` string COMMENT 'EIP预留商品预占单号',
  `freight` decimal(10,2) COMMENT '运费分摊(抖音用)',
  `sale_day` bigint COMMENT '下单时配置的售后期',
  `tms_number_flag` bigint COMMENT '下单时退货退款物流设置标识 0 不向宝尊申请物流单号 ,1 向宝尊申请物流单号',
  `check_jit` bigint COMMENT '是否是jit商品 1是 2否',
  `jit_stcok` bigint COMMENT '是否现货 1是 2否',
  `omni_stock_num` bigint COMMENT 'omni-库存分配记录数量使用',
  `asn_stock_num` bigint COMMENT 'omni-ASN记录数量使用',
  `oid` string COMMENT '天猫发货通知',
  `package_type` bigint COMMENT '包装类型');
CREATE TABLE dw_dwd.dwd_ynap_oms_tmall_order_refund_fl_d(
  `id` bigint COMMENT '主键id',
  `order_no` string COMMENT '订单编号',
  `sharding_key_order_no` string COMMENT '团单编号，用于分库分表拆分键使用',
  `operate_type` bigint COMMENT '操作类型：1、退款(发货前) 2、退款（发货后） 3 退货退款',
  `refund_no` string COMMENT '退款单号',
  `total_fee` decimal(10,2) COMMENT '原订单金额',
  `login_ada` string COMMENT '操作用户',
  `login_user_name` string COMMENT '用户名称',
  `order_status` bigint COMMENT '订单的状态',
  `refund_status` bigint COMMENT '退款状态',
  `goods_status` bigint COMMENT '货物状态',
  `has_good_return` bigint COMMENT '买家是否需要退货',
  `refund_fee` decimal(10,2) COMMENT '退款金额',
  `payment` decimal(10,2) COMMENT '订单剩余为退款金额(交易总金额-退还给买家的金额)',
  `reason` string COMMENT '退款原因',
  `refund_desc` string COMMENT '退款说明',
  `tms_service_code` string COMMENT '物流公司编号',
  `tms_service_name` string COMMENT '物流公司名称',
  `tms_number` string COMMENT '物流编号',
  `refund_version` bigint COMMENT '退款版本号（时间戳）',
  `deleted` bigint COMMENT '是否删除 0 未删除 1已删除',
  `create_time` timestamp COMMENT '退款提交时间',
  `update_time` timestamp COMMENT '更新时间',
  `collect_parcel_start_time` timestamp COMMENT '上门取件开始时间',
  `collect_parcel_end_time` timestamp COMMENT '上门取件结束时间',
  `images` string,
  `receiving_address` string COMMENT '收货地址',
  `receiver_name` string COMMENT '收货人',
  `receiver_phone_number` string COMMENT '收货人手机号',
  `shipping_address` string COMMENT '发货地址',
  `consignor_name` string COMMENT '发货人',
  `consignor_phone_number` string COMMENT '发货人手机号',
  `partner_id` string COMMENT '合作方id',
  `sort_id` bigint COMMENT '排序字段',
  `version` bigint COMMENT '版本号',
  `create_user_name` string COMMENT '创建人',
  `update_user_name` string COMMENT '修改人',
  `create_user_id` string,
  `update_user_id` string,
  `batch_code` string COMMENT '批次号 PO号',
  `store_code` string COMMENT '仓库编号',
  `delivery_no` string COMMENT '出库单编号',
  `channel_code` bigint COMMENT '渠道来源 1APP',
  `refuse_reason` string COMMENT '拒绝原因',
  `freight` decimal(10,2) COMMENT '运费',
  `refund_type` bigint COMMENT '1:app 2:oms',
  `storage_cofirm_time` timestamp COMMENT '确认入库时间',
  `refuse_storage_reason` string,
  `previewing_reason` string COMMENT '预检失败原因',
  `phone` string COMMENT '会员手机号',
  `user_grade` bigint COMMENT '会员下单等级',
  `refund_card_amount` decimal(10,2) COMMENT '礼品卡退款金额',
  `refund_cash_amount` decimal(10,2) COMMENT '现金退款金额',
  `gift_card_service_charge` decimal(20,2) COMMENT '礼品卡退卡手续费',
  `rel_order_no` string COMMENT '第三方数据订单编号',
  `rel_refund_no` string COMMENT '第三方数据销退单号',
  `refund_priority` bigint COMMENT '退款优先级 1-优先退礼品卡 2-优先退现金',
  `user_tag_ids` string COMMENT '下单时用户命中的标签',
  `cs_remark` string COMMENT '客服备注',
  `user_tags` string COMMENT '下单是用户命中的标签',
  `tms_number_flag` bigint COMMENT '下单时退货退款物流设置标识 0 不向宝尊申请物流单号 1 ，向宝尊申请物流单号',
  `is_automatic` bigint COMMENT '抖音是否自动退货退款 1：是 2：不是',
  `sync_stock` bigint COMMENT '抖音发货前退款同步全量库存 0 否，1 是',
  `wms_times` bigint COMMENT '下发wms后缀');
CREATE TABLE dw_dwd.dwd_ynap_oms_tmall_order_refund_items_fl_d(
  `id` bigint COMMENT '主键id',
  `order_no` string COMMENT '订单编号',
  `sharding_key_order_no` string COMMENT '团单编号，用于分库分表拆分键使用',
  `refund_no` string COMMENT '退款单号',
  `sku_code` string COMMENT '商品编号',
  `product_name` string COMMENT '商品名称',
  `product_img_url` string COMMENT '商品图片url',
  `quantity` bigint COMMENT '数量',
  `store_code` string COMMENT '仓储编号',
  `batch_code` string COMMENT '批次号，PO',
  `original_price` decimal(10,2) COMMENT '商品原价格',
  `deleted` bigint COMMENT '是否删除 0 未删除 1已删除',
  `refund_item_status` bigint COMMENT '销退单行状态',
  `create_time` timestamp COMMENT '退款提交时间',
  `update_time` timestamp COMMENT '更新时间',
  `actually_amount` decimal(10,2) COMMENT '商品实付金额',
  `gift_source_sku_code` string COMMENT '赠品来源商品编号',
  `gift_flag` bigint COMMENT '是否是赠品',
  `sku_type` bigint COMMENT '1 普通商品  2 订单级别 3 商品级别',
  `promotion_code` string COMMENT '促销编号',
  `promotion_name` string COMMENT '促销名称',
  `brand_name` string COMMENT '品牌英文名',
  `product_attr` string COMMENT '商品颜色尺码',
  `product_markdown_price` decimal(10,2) COMMENT 'markdown价格',
  `product_eip_price` decimal(10,2) COMMENT 'ELP价格',
  `partner_id` string COMMENT '合作方id',
  `sort_id` bigint COMMENT '排序号',
  `version` bigint COMMENT '版本号',
  `create_user_name` string COMMENT '创建人',
  `update_user_name` string COMMENT '修改人',
  `create_user_id` string COMMENT '',
  `update_user_id` string,
  `delivery_item_no` string COMMENT '出库单号',
  `order_item_status` bigint COMMENT '原订单行状态',
  `refund_fee` decimal(10,2) COMMENT '退款金额',
  `spu_code` string COMMENT 'spu_code');
CREATE TABLE dw_dwd.dwd_ynap_oms_tmall_payment_items_fl_d(
  `id` bigint COMMENT '交易流水表主键',
  `order_no` string COMMENT '订单号',
  `sharding_key_order_no` string COMMENT '订单编号，用于分库分表拆分键使用',
  `payment_code` string COMMENT '支付方式小类别(分期数)，注意，此时记录的是发起支付时选择的分期数，由于PC端支付时用户可以改分期数，因此分期期数可能和实际用户选择的分期数可能不一样',
  `payment_mode` bigint COMMENT '支付方式 微信-wechat,支付宝-alipay,银联-uniepay,积分-points',
  `payment_amount` decimal(10,2) COMMENT '支付金额',
  `payment_total_amount` decimal(10,2) COMMENT '应支付总金额',
  `points` decimal(10,2) COMMENT '积分',
  `transaction_no` string COMMENT '交易流水号',
  `transaction_status` bigint COMMENT '交易状态',
  `transaction_type` bigint COMMENT '交易类型，1-支付 2-退款',
  `trade_order_no` string COMMENT '支付中心返回的订单编号',
  `bank_trade_no` string COMMENT '支付中心返回的银行流水号',
  `deleted` bigint COMMENT '是否删除 1删除 0 未删除',
  `create_user_id` string,
  `update_user_id` string,
  `create_user_name` string,
  `update_user_name` string,
  `sort_id` bigint COMMENT '排序值',
  `version` bigint COMMENT '版本号',
  `create_time` timestamp COMMENT '创建时间',
  `update_time` timestamp COMMENT '修改时间',
  `partner_id` string COMMENT '合作方id',
  `channel_code` bigint COMMENT '渠道来源 1 安卓 2 IOS',
  `merch_no` string COMMENT '商户号',
  `terminal_no` string COMMENT '终端号',
  `qr_code_id` string COMMENT '二维码ID',
  `refund_no` string COMMENT '销退单号',
  `txn_time` string COMMENT '银联直连商户发送交易时间',
  `direct_flag` bigint COMMENT '银联支付流水标识 1 直连 , 2 间连 ');
CREATE TABLE dw_dwd.dwd_order_oms_tmall_sync_time_fl_d(
  `id` bigint COMMENT '表主键',
  `order_no` string COMMENT '订单编号',
  `sku` string COMMENT '商品sku',
  `virtual_warehouse_code` string COMMENT '仓存编号',
  `child_order_delivery_time` timestamp COMMENT '子订单发货时间',
  `child_order_receive_time` timestamp COMMENT '子订单签收时间',
  `child_order_success_time` timestamp COMMENT '子订单交易成功时间',
  `child_order_cancel_time` timestamp COMMENT '子订单交易关闭时间',
  `child_order_refund_time` timestamp COMMENT '子订单退货发起时间',
  `child_order_refund_parcel_time` timestamp COMMENT '子订单退货上门揽件时间',
  `child_order_refund_backmoney_time` timestamp COMMENT '子订单退货确认退款时间',
  `refund_type` int COMMENT '退货类型(1、仅退款 2、退货退款)',
  `create_time` timestamp COMMENT '创建时间',
  `update_time` timestamp COMMENT '修改时间',
  `version` bigint COMMENT '版本号',
  `partner_id` string COMMENT '合作方id',
  `sort_id` bigint COMMENT '排序值',
  `deleted` int COMMENT '状态 0未删除  1已删除',
  `create_user_name` string COMMENT '创建人',
  `update_user_name` string COMMENT '修改人',
  `create_user_id` string COMMENT '创建人id',
  `update_user_id` string COMMENT '修改人id',
  `order_item_id` bigint COMMENT '子订单d');

-- 迁移数据 商品

drop table if exists dw_dim.dim_pdt_sku_fl_d_test;
create table dw_dim.dim_pdt_sku_fl_d_test
like dw_dim.dim_pdt_sku_fl_d
stored as orc
location 'oss://data-zhongtai/dev/hive/warehouse/dw_dim.db/ttt';


insert overwrite table dw_dim.dim_pdt_sku_fl_d
select * from dw_dim.dim_pdt_sku_fl_d_test;


select count(1) from dw_dim.dim_pdt_sku_fl_d;

--dw_dwd.dwd_usr_action_fl_d

drop table if exists dw_dwd.dwd_usr_action_fl_d_test;
create table dw_dwd.dwd_usr_action_fl_d_test
like dw_dwd.dwd_usr_action_fl_d
stored as orc
location 'oss://data-zhongtai/dev/hive/warehouse/dw_dwd.db/t';


insert overwrite table dw_dwd.dwd_usr_action_fl_d
select * from dw_dwd.dwd_usr_action_fl_d_test;