财务表关系-new

app.finance_order_extend_detail_df 

dwd.live_order_basic_di t2

app.finance_ship_detail_df

dw.live_order_commission_df

dim.live_zhubo_user_info_df

dept_id

sync.app_finance_goods_stat_1_df  

dw.live_erp_order_goods_df

dim.zh_multiple_enum_df  tB4

1159  财务发货成本_一件代发

ods.goods_terminal_category_df t4

自营订单表

output表结果为增量，同步到ES五个索引中

dim.live_platform_goods_info_df t3

ods.live_goods_category_df t1

dw.live_order_goods_extend_df  t2

dim.zh_multiple_enum_df  tB3

ods.live_zhubo_user_info_df t1

goods_id + from_source

dw.live_order_goods_extend_df t11

dim.goods_supplier_cps_item_df t8

dw.live_order_basic_extend_df 

982  财务订单_订单明细 

app.finance_output_warehouse_goods_stat_ma t3

dwd.live_erp_order_new_di

969  财务订单_统计概览_维度统计_主播

user_id+dept_id 定位dim唯一记录(一个员工可能会有多个部门[领导])t1.ding_user_id = t2.dingding_user_idt2.dingding_user_id = t3.dingding_user_idt3.dept_id = t4.dept_idt4.dept_id = t5.org_id

974   财务订单_统计概览_维度统计_店铺

商品中心

buyer_order_no

dim.zh_multiple_enum_df t2

dw.live_goods_zhubo_detail_df t1

ods.goods_spu_df t3

platform_order_goods_no

order_no

dw.live_order_goods_fin_df    t1

ods.ywcloud_user_df t1

user_id+dept_id

替代线上的：dw.live_erp_order_di表该表按照天分区

dwd.live_order_goods_di  t1

t1.order_created_at>11-26t1.platform_order_no = t2.ponunion all t1.order_created_at <11-26t1.pk_id = t2.pk_id 两部分都和 t3 joint1.order_no = t3.buyer_order_no在上述结果集上计算相关佣金=>tAtA 与 t4 四个部分union all

dw.live_order_basic_extend_df  t1

dwd.live_order_logistics_di t9

dwd.live_order_basic_di  

pk_id+platform_order_no 11-26前 pk_id为null platform_order_no唯一11-26后，pk_id唯一

sync.app_finance_goods_zhubo_stat_1_df

CPS商品表

dw.live_order_basic_extend_df

dingding_user_id

dim.live_zhubo_manual_dept_df t2

gp(t3.platform_order_no)t2.platform_order_no = t3.pon 对结果集gp(pon) =>tAgp(t4.pon)=>tBt5.state = t6.enum_id对结果集gp(order_no)=>tCt1.pon=tA.pon;t1.pon=tB.pon;t1.pon = tC.order_no最后，where t1.business_no = '3'

1156  财务发货成本_发货明细 

ods.live_erp_warehouse_df t7

dim.goods_supplier_cps_item_df

1条pon多条数据，1商品1佣金

dwd.live_order_logistics_di t5

org_id

app.finance_goods_stat_df 

dim.goods_supplier_cps_item_df t4

dim.live_zhubo_user_info_df t6

init_sku_code + supplier_no

sku_no

1020     财务订单_统计明细_商品

t1.platform_order_goods_no = t2.platform_order_goods_not1.sku_id = t3.init_sku_code and t1.supplier_id = t3.supplier_not1.platform_order_no = t4.platform_order_not5.raw_so_id = t1.outer_order_not1.platform_order_no = t6.platform_order_no [t6 r去重之后]t4.warehouse_code = t7.warehouses_codet2.detail_platform_item_id=t8.platform_goods_no and[平台商品id] = [平台商品编号] t5.order_from_source = t8.order_from_source 

968 财务订单-统计概览_天维度折线图

sync.dim_live_ship_select_df

sync.app_finance_order_extend_detail_output_df_idxx

订单商品扩展表

ods.goods_sell_submit_spu_df t2

ods.live_erp_after_sale_order_df t8

每月发货订单明细

app.live_warehouse_compare_df

platform_order_no+ goods_id + combo_sku_id (>2020-11-26)

dwd.live_erp_order_goods_new_di t1

platform_order_no

sync.app_finance_zhubo_stat_df

ods.ywcloud_org_df t5

dw.live_order_goods_extend_df t2

ding_user_id

1158  财务发货成本_自营总成本

dim.live_show_select_df

970  财务订单_统计概览_维度统计_部门

unique_no

ods.live_platform_rate_config_df t5

ods.live_supplier_base_df t6

dw.live_order_commission_fin_df

dwd.live_activity_data_export_detail_di t12

1161  财务发货成本_下拉框

ods.goods_supplier_cps_item_df t1

981  财务订单_下拉框

dw.live_erp_order_goods_df 

t1.sku_id    =    t2.shangpinbianma

dim.live_platform_goods_info_df  t3

自营订单商品宽表

platform_order_no+ goods_id + combo_sku_id

order_from_source: 1-淘宝 2-京东 3-拼多多 4-快手小店 5-有赞 6-微小店 7-有赞客 8-抖音 9-京东来客 10-抖音CPS 11-魔筷 12-抖音小店+抖音CPS 13-有赞分销 14-快手CPS'

dw.live_order_basic_extend_df t1

ods.goods_system_plate_form_rel_df t2

dwd.live_account_base_df t5

app.finance_order_extend_detail_output_di

dwd.live_order_basic_di  t1

sync.app_finance_ship_detail_df_idx_v1

app.finance_goods_zhubo_stat_df

dw.live_order_goods_fin_df

订单扩展宽表

t1.cooperation_mode = t2.enum_id

mid.live_order_goods_extend_df

佣金表

1157  财务发货成本_指标卡_折线图

dim.zh_multiple_enum_df t6

976 财务订单_统计明细_主播商品 

tA表所有字段做判空处理tB1.order_from_source = tB2.enum_idtB1.platform=tB3.enum_id tB1.order_status= tB4.enum_id ==> tBtA.platform_order_no = tB.platform_order_no

ods.live_delete_sku_detail_df t2

app.finance_ship_stat_df

dw.live_order_goods_df t3

app.finance_ship_detail_output_di

dw.live_erp_order_df  t4

app.finance_zhubo_stat_df

user_no

财务发货成本

dwd.live_order_basic_di  t5

on t1.platform_order_no = t2.platform_order_not1.goods_id = t3.goods_id and t2.order_from_source = t3.from_source

group by t2.user_id去重t1.zhubo_bc=t2.user_id

dw.live_order_basic_extend_df  

dw.live_erp_order_goods_df  t2

dw.live_order_goods_df  t2

dwd.live_order_commission_di t1

t2-直播中心 2； 16平台类型t3 商品中心  1；7个平台类型t1.from_source = t2.plate_form_typet2.plate_form_enum = t3.plate_form_enumt3.plate_form_type = t4.terminal_typeand t1.category_id = t4.terminal_category_no

dwd.live_erp_order_logistics_di 

ods.goods_sell_submit_sku_df  t1

ods.live_order_basic_di tB1

平台商品

sync.live_show_select_df

app.finance_order_extend_detail_df  t1

ods.goods_item_category_df

dw.live_order_goods_extend_df  

dw.live_order_basic_extend_df  t1

t5.order_no                    as order_not5.pon                            as platform_order_not1.outer_order_no          as  raw_so_id[A]t1.platform_order_no     as inner_outer_not1.platform_order_goods_no as     pogn

ods.goods_system_plate_form_rel_df t3

dw.live_erp_order_df 

订单商品宽表

sync.app_finance_ship_stat_df

<2020-11-26聚合处理  group by platform_order_no；>2020-11-26保留并剪裁掉了 1/2 的列

dwd.live_erp_order_goods_ext_di t2 

dim.live_ship_select_df

977  财务订单_统计明细_主播 

app.finance_goods_zhubo_stat_1_df

sync.app_finance_goods_stat_df 

dw.live_order_goods_extend_df 

ods.live_brand_df t4

dw.live_erp_order_goods_df t10

dim.ywcloud_user_info_df t2

dw.live_erp_order_goods_df t1

ods.goods_item_category_df  t5

dingding_user_id + dept_id

app.finance_goods_stat_1_df  

mid.live_order_goods_extend_df t1

订单表

dim.goods_sku_spu_df t3

dim.goods_sku_spu_df

ods.live_youzan_order_df t3

app.finance_ship_month_stat_df

ods.ywcloud_dingding_user_df t2

app.finance_order_extend_detail_df  t2

dwd.live_cps_user_info_di t13

dw.live_order_goods_df t4

主播部门信息

dwd.live_order_basic_di tA

raw_so_id （order_no+A）

platform_goods_no+order_from_source

dw.live_erp_order_goods_df t4

dw.live_order_goods_extend_df

遥望用户

sync.app_finance_ship_month_stat_df

dw.live_order_commission_fin_df  t3

dim.zh_multiple_enum_df  tB2

pon

972  财务订单_统计概览_维度统计_商品平台

sku_code+supplier_no

platform_goods_no+cooperation_mode

ods.live_zhubo_platform_account_df t7

dw.live_order_goods_df

975   财务订单_统计概览_维度统计_商品类目

ods.ywcloud_dingding_dept_user_rel_df t3

ods.live_erp_after_sale_order_df  t6

dw.live_order_commission_fin_df t2

sync.app_finance_goods_zhubo_stat_df

dw.live_erp_order_goods_df t4

dim.live_platform_goods_info_df

973    财务订单_统计概览_维度统计_商品品牌

ods.ywcloud_dingding_dept_df t4