官方网站怎么推广,免流服务器,高端商城网站建设,wordpress添加媒体库1. 指标分类
通过沟通调研#xff0c;把需求进行分析、抽象和总结#xff0c;整理成指标列表。指标有原子指标、派生指标、 衍生指标三种类型。
原子指标基于某一业务过程的度量值#xff0c;是业务定义中不可再拆解的指标#xff0c;原子指标的核心功能就是对指标…1. 指标分类
通过沟通调研把需求进行分析、抽象和总结整理成指标列表。指标有原子指标、派生指标、 衍生指标三种类型。
原子指标基于某一业务过程的度量值是业务定义中不可再拆解的指标原子指标的核心功能就是对指标的聚合逻辑进行了定义。可以得出结论原子指标包含三要素分别是业务过程、度量值和聚合逻辑。例如订单总额就是一个典型的原子指标其中的业务过程为用户下单、度量值为订单金额聚合逻辑为sum()求和。
派生指标基于原子指标在原子指标的基础上加了各种限定。与原子指标不同派生指标通常会对应实际的统计需求。 衍生指标是在一个或多个派生指标的基础上通过各种逻辑运算复合而成的。例如比率、比例等类型的指标。衍生指标也会对应实际的统计需求。
2. 四大业务需求
2.1 销售业务需求
业务流程
线下销售/退款流程 线上销售/退款流程 主要区别:
线上流程相对更加复杂线上订单退款与原订单有关联关系线下订单是没有关联关系线上订单涉及配送订单完成时间和下单时间间隔会比较长
核销和售卖
业务部门需要全面分析线上线下的销售情况包括销售、取消、退款的金额、成本、单量、SKU以及活动的情况。
注意点:
成本指标, 只能在库存处理之后才能获得 商品下单, 仅为预占库存, 只有当订单完成时, 才会真正扣减商品进货的每批次的成本价格均有所不同, 为保证成本准确性,只有在扣减库存时才能拿到对应批次的成本, 所以要想获取商品成本只能在库存处理之后才能获得对于线上订单从下单到完成的数据间隔可能会比较长, 如果只从核销维度来统计数据, 延迟性会非常高, 所以, 还需要从售卖维度来统计数据
故: 销售主题按照时间维度分为核销(扣减库存)和售卖(订单下单)两个需求
原子指标:
核销主题: 售卖主题
衍生指标: 2.2 供应链业务需求
业务流程
要货到货流程 商品调拨流程: **为精细化运营业务部门严格管控供应链要求计算库存的数量、金额、SKU、周转、动销、损耗数量和金额、盘点差异以及要货、收货、配送、退货、退配、调入、调出、系统调整的数量和金额。**具体指标如下。
原子指标:
库存 订单 衍生指标: 2.3 会员需求
因为黑马甄选是生鲜新零售业务包括线上和线下所以会员也分为线上会员和线下会员。线下会员类似理发店的VIP卡可以在线下购物时享受积分和折扣还可以进行充值所以线下会员也叫实体卡会员。线上会员即在平台上注册的会员。因为线上业务是门店大力推广的方向也是资本看好的板块并且可以通过线上渠道进行运营和迭代所以这里重点分析线上会员。
主要统计会员的注册、消费、充值、余额情况。注意线上会员也可以在线下消费使用相同的手机号即可。具体指标如下:
原子指标: 衍生指标: 2.4 商城需求
在这里商城需求指的是对商城的访问日志进行分析主要是流量数据和交易数据。如何评价线上平台的好坏UV/PV/新访客数量/跳出数/浏览时长等都是非常重要的指标。如何提高平台的运营效果就是要从各环节进行优化比如首页展示、商品详情、商品加购、商品结算等通过绘制转换漏斗找到薄弱环节进行专项提高和突破所以需要计算各环节的具体指标。具体指标如下。
原子指标: 衍生指标: 3. 核销主题需求 3.1 商品销售情况(已核销)分析
说明
了解不同城市各个门店以及各个品类商品每天的销售情况已核销
指标
销售单量、销售数量、销售金额、折扣金额、销售成本、余额支付金额、取消商品销售金额、退款商品销售金额、线上单量、线下单量、线上销售金额、线下销售金额、线上销售成本、线下销售成本、损耗金额、收货金额收货-退货-退配调入-调出、要货金额
维度
时间、区域、类别
粒度
时间维度天、下钻至刻、区域维度城市、门店、类别维度商品、第一品类、第二品类、第三品类
涉及库sale、member、order、stock
涉及表 事实表sale: store_sale_dtl、store_sale_info、store_sale_pay、shop_order、shop_order_item、shop_sale_pay、shop_refund、shop_refund_itemmember: member_unionstock: store_stock_adjorder: store_receive、store_return_to_vendor、store_return_to_dc、store_alloc_in、store_alloc_out、store_require 维度表 dim: dwd_dim_date_f、dwd_dim_source_type_map_f、dwd_dim_store_f、dwd_dim_goods_f、dwd_dim_store_goods_f3.2 经营情况(已核销)分析
说明
了解不同城市各个门店每天的销售情况已核销
指标
销售单量、销售数量、销售金额、折扣金额、销售成本、余额支付金额、取消商品销售金额、退款商品销售金额、线上单量、线下单量、线上销售金额、线下销售金额、线上销售成本、线下销售成本、损耗金额、收货金额收货-退货-退配调入-调出、要货金额、线上会员单量、实体卡会员单量、线上会员销售金额、实体卡会员销售金额、线上会员销售成本、实体卡会员销售成本、线上会员下单人数、实体卡会员下单人数、使用余额销售金额、使用余额单量、使用余额的销售成本、使用余额的下单人数维度
时间、区域
粒度
时间维度天下钻至刻、区域维度城市、门店涉及库sale、member、order、stock
涉及表 事实表a) salestore_sale_dtl、store_sale_info、store_sale_pay、shop_order、shop_order_item、shop_sale_pay、shop_refund、shop_refund_item b) membermember_union c) stockstore_stock_adj d) orderstore_receive、store_return_to_vendor、store_return_to_dc、store_alloc_in、store_alloc_out、store_require 维度表dwd_dim_date_f、dwd_dim_source_type_map_f、dwd_dim_store_f、dwd_dim_goods_f、dwd_dim_store_goods_f
3.3 门店营销情况(以核销)分析
说明了解各个门店营销情况包括不同销售渠道已核销的销售情况以及日清活动的效果。【日清活动指的是为了保证果蔬的新鲜度一些商品当日出清为了能出清商品会采用打折的方式出售并且随着时间的推移越晚折扣越低。】
销售渠道需求
指标订单量、退款订单量、取消订单量、商品销售金额、商品销售成本、商品折扣金额
维度订单渠道
日清活动需求
指标销售SKU数、销售单量、销售数量、销售金额、折扣金额、销售成本、销售利润、线上单量、线下单量、线上销售金额、线下销售金额、损耗金额、收货金额收货-退货-退配调入-调出、要货金额、
维度区域、品类
粒度区域维度门店、品类维度第一品类
涉及库sale、member、order、stock
涉及表 事实表a) salestore_sale_dtl、store_sale_info、store_sale_pay、shop_order、shop_order_item、shop_sale_pay、shop_refund、shop_refund_item b) membermember_union c) stockstore_stock_adj d) orderstore_receive、store_return_to_vendor、store_return_to_dc、store_alloc_in、store_alloc_out、store_require 维度表dwd_dim_date_f、dwd_dim_source_type_map_f、dwd_dim_store_f、dwd_dim_goods_f、dwd_dim_store_goods_f
4. 核销主题数仓建设
ADS
商品销售情况 经营情况 门店营销情况 ADS建表 黑马甄选离线数仓项目_完整资料\07_脚本\03_数仓各层建表脚本\核销主题ADS建表.sql 商品销售情况门店商品销售天表: ads_goods_store_goods_statistics_day_i城市商品销售天表: ads_goods_city_goods_statistics_day_i门店第三品类销售天表: ads_category_store_third_category_statistics_day_i门店第二品类销售天表: ads_category_store_second_category_statistics_day_i门店第一品类销售天表: ads_category_store_first_category_statistics_day_i经营情况门店经营分析天表: ads_store_manage_statistics_day_i城市经营分析天表: ads_store_city_manage_statistics_day_i门店营销情况门店销售渠道分析天表: ads_marketing_store_source_type_day_i门店品类日清商品分析天表: ads_marketing_store_category_clean_data_day_i门店日清商品分析天表: ads_marketing_store_clean_data_day_i
DWS
商品销售情况 经营情况 DWS层建表 黑马甄选离线数仓项目_完整资料\07_脚本\03_数仓各层建表脚本\核销主题DWS建表.sql 门店商品分析刻表: dws_goods_store_goods_statistics_quarter_i
门店经营分析刻表: dws_store_manage_statistics_quarter_iDWM
商品销售情况 经营情况 注意: 相比商品销售情况多了会员部分,多了余额部分 此处可以在门店商品销售明细刻表基础上添加会员,余额部分创建一个新表 但是本次项目,我们直接把会员,余额部分已经添加到上述的门店商品销售明细刻表中 DWM层建表 黑马甄选离线数仓项目_完整资料\07_脚本\03_数仓各层建表脚本\核销主题DWM建表.sql 门店商品销售明细刻表: dwm_sold_goods_sold_dtl_i
门店商品损耗刻表: dwm_stock_store_goods_loss_quarter_i
门店商品要货刻表: dwm_order_store_goods_require_quarter_i
门店商品收货刻表: dwm_order_store_goods_receipt_quarter_iDWD
表汇总分析 DWD层建表 黑马甄选离线数仓项目_完整资料\07_脚本\03_数仓各层建表脚本\核销主题DWD建表.sql 门店销售明细宽表(8张表降维拉宽): dwd_sale_store_sale_dtl_i线上余额支付明细表: dwd_sale_shop_sale_balance_pay_i
商城订单表(核销表): dwd_sold_shop_order_i
商城订单明细表(核销表): dwd_sold_shop_order_item_i商城订单核销明细宽表(10张表降维拉宽): dwd_sold_shop_order_dtl_i门店库调表(3张表降维拉宽): dwd_stock_store_stock_adj_i门店收货表(3张表降维拉宽):dwd_order_store_receive_i
门店退货表(3张表降维拉宽): dwd_order_store_return_to_vendor_i
门店退配表(3张表降维拉宽): dwd_order_store_return_to_dc_i门店调入表(3张表降维拉宽):dwd_order_store_alloc_in_i
门店调出表(3张表降维拉宽):dwd_order_store_alloc_out_i门店要货表(3张表降维拉宽):dwd_order_store_require_i5. 核销主题的开发工作
5.1 DWD层开发
建库:
CREATE DATABASE IF NOT EXISTS dwd;注意具体计算只计算增量全量计算直接去掉where条件即可。或者在调度时使用补数的方法将历史分区一天一天跑出来即可
在DWD层中, 主要是将事实表数据和维度表数据进行关联, 对事实表数据实施降维工作, 同时针对部分表进行清洗转换处理工作
本次DWD层,共设计为12张事实表, 分别为门店销售明细表、线上余额支付明细、商城订单表(核销表)、订单明细表(核销表)、商城核销明细表、门店库调单、门店收货单、门店退货单、门店退配单、门店调入单、门店调出单、门店要货单
5.1.1 门店销售明细宽表
此表为常见的明细打宽的开发任务, 整个业务过程是门店下单, 粒度为订单明细, 维度包含时间、门店、商品、品类等, 事实包括销售数量、金额、成本、余额支付金额等
设计拉宽表的思考点:
1原始表中重要信息不好判断的时候尽量保留所有字段即可2一些必要类型字段比如说is_day_clearis_balance_consume3维度拉宽比如对日期/时间进行处理、门店信息拉宽、商品信息拉宽4还要注意保持数仓中字段名的统一类型名、指标、维度字段等
本次, 我们主要将门店销售的核心三张表进行拉宽操作, 包括门店销售信息表、门店销售明细表、门店销售支付表进行合并拉宽, 同时还需要带上相关的维度表
涉及事实表:
ods_sale_store_sale_info_i(门店销售信息表): 主要描述了门店线下的的销售明细包括单号、时间、金额、渠道、会员等信息。ods_sale_store_sale_dtl_i(门店销售明细表): 相对于销售信息表, 多了具体的商品信息, 库存信息等注意:1因为组合商品和组合成分都是会记录到明细表的这里只需要分析组合商品所以需要排除掉组合成分。比如牙膏和牙刷一起组合销售则牙膏和牙刷一起为组合商品牙膏、牙刷为组合成分。combination_flag int comment 1-组合商品2-组合成分2) 有时候库存调整要做一些冲减单这些单据不是正常的销售单所以需要排除。过滤条件为offset_flag 0ods_sale_store_sale_pay_i(门店销售支付表): 主要描述了每一单的支付类别、支付金额等信息。这个表只是为了取余额销售的信息所以这里让 pay_type_id’201’三张表的关联条件是order_no相同。订单表和明细表进行inner join支付表要跟订单表做左关联因为不是每一单都是余额支付涉及维度表: 五张dwd_dim_date_f(时间维度表):记录了一个日期对应的不同的日期如周一日期、周末日期、月一日期、月末日期等。
dwd_dim_source_type_map_i(交易类型映射表):记录了原始交易渠道对应的归类情况。
dwd_dim_store_i(门店表):记录了门店的基本信息包括门店销售类型、分店类型、城市、区域、人数、状态等信息。
dwd_dim_goods_i(商品表):记录商品的基本信息包括ID编码名称分类售卖信息订货信息等。
dwd_dim_store_goods_i(门店商品表):跟商品表不同的是商品表记录的是商品通用的信息而门店商品表里会记录一些门店独有的信息比如日清信息采购信息柜组信息等。建表操作:
CREATE TABLE IF NOT EXISTS dwd.dwd_sale_store_sale_dtl_i(-- 门店销售信息表trade_date_time STRING COMMENT 销售时间,trade_date STRING COMMENT 交易日期,-- 时间维度表week_trade_date STRING COMMENT 周一日期,month_trade_date STRING COMMENT 月一日期,-- 门店销售信息表 hourly BIGINT COMMENT 交易小时(0-23),quarter BIGINT COMMENT 刻钟:1.0-15,2.15-30,3.30-45,4.45-60,quarters BIGINT COMMENT 刻钟数:hourly*4quarters,parent_store_no STRING COMMENT 母店编码,store_no STRING COMMENT 店铺编码,-- 门店表store_name STRING COMMENT 店铺名称,store_sale_type BIGINT COMMENT 店铺销售类型,store_type_code BIGINT COMMENT 分店类型,worker_num BIGINT COMMENT 员工人数,store_area DECIMAL(27, 2) COMMENT 门店面积,city_id BIGINT COMMENT 城市ID,city_name STRING COMMENT 城市名称,region_code STRING COMMENT 区域编码,region_name STRING COMMENT 区域名称,is_day_clear BIGINT COMMENT 是否日清:0否,1是,-- 门店销售信息表trade_type BIGINT COMMENT 结算类型(0.正常交易,1.赠品发放,2.退货,4.培训,5.取消交易),-- 交易类型映射表source_type BIGINT COMMENT 交易来源1:线下POS;2:三方平台;3:传智鲜商城;4:黑马优选团;5:传智大客户;6:传智其他;7:黑马优选;8:优选海淘;9:优选大客户;10:优选POS;11:优选APP;12:优选H5;13:店长工具线下;14:店长工具线上;15:黑马其他,source_type_name STRING COMMENT 交易来源名称,-- 门店销售信息表sale_type BIGINT COMMENT 销售类型 1.实物,2.代客,3.优选小程序,4.离店,5.传智鲜小程序,6.第三方平台,7.其他,8.大客户,member_type BIGINT COMMENT 会员类型:0非会员,1线上会员,2实体卡会员,is_balance_consume BIGINT COMMENT 是否有余额支付:0否,1是,parent_order_no STRING COMMENT 母订单编号,order_no STRING COMMENT 订单编号,pos_no STRING COMMENT Pos机号,ser_id STRING COMMENT POS机当天序号从1开始递增,item BIGINT COMMENT 商品在小票的位置,sort BIGINT COMMENT 组合商品分割商品拆出位置,pay_time STRING COMMENT 支付时间,last_update_time STRING COMMENT 最后更新时间,cashier_no STRING COMMENT 收银员编码,cashier_name STRING COMMENT 收银员名称,share_user_id STRING COMMENT 分享人用户ID,commission_amount DECIMAL(27, 2) COMMENT 佣金,zt_id BIGINT COMMENT 中台ID,member_id BIGINT COMMENT 会员ID,card_no STRING COMMENT 卡号,-- 商品表first_category_no STRING COMMENT 一级分类编码,first_category_name STRING COMMENT 一级分类名称,second_category_no STRING COMMENT 二级分类编码,second_category_name STRING COMMENT 二级分类名称,third_category_no STRING COMMENT 三级分类编码,third_category_name STRING COMMENT 三级分类名称,-- 门店销售明细表goods_no STRING COMMENT 商品编码,-- 商品表goods_name STRING COMMENT 商品名称,spec STRING COMMENT 单位,-- 门店销售明细表is_component BIGINT COMMENT 是否为组合商品:0否,1是,-- 门店商品表supply_team BIGINT COMMENT 供应链团队 1.平台商品,2.优选标品,3.传智鲜标品,4.传智鲜生鲜,5优选POS商品,dc_no STRING COMMENT 采购仓库编号,dc_name STRING COMMENT 采购仓库名称,group_no STRING COMMENT 采购柜组编号,group_name STRING COMMENT 采购柜组名称,-- 门店销售明细表trade_mode_id BIGINT COMMENT 结算方式:1购销,2联营,vendor_id BIGINT COMMENT 供应商ID,contract_no STRING COMMENT 合同编号,-- 门店商品表is_clean BIGINT COMMENT 商品是否日清:0否,1是,-- 门店销售明细表is_daily_clear BIGINT COMMENT 商品是否参加日清活动:0否,1是,sale_qty DECIMAL(27, 3) COMMENT 商品销售数量,sale_amount DECIMAL(27, 2) COMMENT 商品销售金额,dis_amount DECIMAL(27, 2) COMMENT 商品折扣金额,sale_cost DECIMAL(27, 2) COMMENT 商品销售成本,-- 门店销售支付表balance_amount DECIMAL(27, 2) COMMENT 余额支付,-- 当前写入时间: current_timestampwrite_time TIMESTAMP COMMENT 写入时间
)
COMMENT 门店销售明细表
partitioned by(dt STRING COMMENT 核销日期)
row format delimited fields terminated by ,
stored as orc
tblproperties (orc.compressSNAPPY);需求梳理:
先将三个核心事实表进行合并操作, 形成结果数据表
1- 销售日期基于门店销售信息表中的库存日期处理
2- 母店编码和门店编码: 母店编码为门店编码, 门店编码使用销售门店编码如果没有, 使用门店编码
3- 结算类型: 判断trade_id,如果为1 返回0(正常交易), 为2返回2, 如果为3,4返回5
4- 销售渠道如果为null, 返回1
5- 会员类型: 如果以OL- / SF- 开头的为线上会员, 否则为线下会员, 为空为null的为非会员
6- 判断是否为余额消费, 基于是否可以关联上余额支付的表
7- 母订单编号: 判断商品销售金额是否小于0, 如果小于采用source_order_sn(退款单据ID),如果大于采用parent_order_sn. 如果为NULL, 选择order_no
8- 针对item(商品序号),sort(序号),cashier_no,cashier_name,trade_mode_id,share_user_id,commission_amount,zt_id,member_id 如果为null, 设置为0
9- 支付时间选择pay_date
10- 最后修改时间, 选择库存时间
11- 余额金额(平摊金额)计算: 首先判断单据金额是否等于0 , 则结果为0, 如果不是, 判断是否有余额支付金额, 如果有, 使用余额金额 * 商品销售金额 / 单据金额三个表关联需要产出以下几个字段:
trade_date_time,trade_date,hourly,minute,parent_store_no,store_no,source_type,member_type,is_balance_consume,trade_type,parent_order_no,order_no,pos_no,ser_id,item,sort,pay_time,last_update_time,cashier_no,cashier_name,zt_id,member_id,card_no,goods_no,is_component,trade_mode_id,vendor_id,contract_no,is_daily_clear,share_user_id,commission_amount,sale_qty,sale_amount,dis_amount,sale_cost,balance_amount代码实现
selectdate_format(t2.deal_date,yyyy-MM-dd HH:mm:ss) as trade_date_time,date_format(t2.trade_date,yyyy-MM-dd HH:mm:ss) as trade_date ,hour(t2.deal_date) as hourly,minute(t2.deal_date) as minute,t2.store_no as parent_store_no,coalesce(t2.sale_store_no,t2.store_no) as store_no,coalesce(t2.source_type,1) as source_type,if(t2.member_type like OL-% OR t2.member_type like SF-%,1,if(t2.member_type OR t2.member_type is null,0,2)) as member_type,if(t3.order_no is null,0,1) as is_balance_consume,if(t2.trade_id 1,0,if(t2.trade_id 2,2,5)) as trade_type,if(t1.sale_amount 0,t2.source_order_sn,if(t1.sale_amount 0,t2.parent_order_sn,t1.order_no)) as parent_order_no,t2.order_no,t2.pos_no,t2.ser_id,coalesce(t1.item,0) as item,coalesce(t1.sort,0) as sort,date_format(t2.pay_date,yyyy-MM-dd HH:mm:ss) as pay_time,date_format(t2.deal_date,yyyy-MM-dd HH:mm:ss) as last_update_time,coalesce(t2.cashier_no,0) as cashier_no,coalesce(t2.cashier_name,0) as cashier_name,coalesce(t2.member_center_sn,0) as zt_id,coalesce(t2.member_id,0) as member_id,t2.card_no,t1.goods_no,if(t1.combination_flag 1,1,0) as is_component,coalesce(t1.trade_mode_id,0) as trade_mode_id,t1.vendor_id,t1.contract_no,t1.is_daily_clear,coalesce(t1.share_user_id,0) as share_user_id,coalesce(t1.commission_amount,0) as commission_amount,t1.sale_qty,t1.sale_amount,t1.dis_amount,t1.sale_cost,if(t2.total_pay_amount 0,0,if(t3.pay_amount is null,0,cast(t3.pay_amount * t1.sale_amount / t2.total_pay_amount as decimal(27,2))))as balance_amountfrom (select * from ods.ods_sale_store_sale_dtl_i where combination_flag ! 2 and offset_flag 0) t1join ods.ods_sale_store_sale_info_i t2 on t2.order_no t1.order_noleft join (select * from ods.ods_sale_store_sale_pay_i where pay_type_id 201) t3 on t2.order_no t3.order_no ;
接着基于结果和其他五个维度表关联, 生成最终维度表
1- sale_type(销售类型): 基于上述结果表的source_type判断, 当为1返回1, 当为9返回5 , 当为 4,5,6,7,8返回6. 当为11返回8 ,否则返回7
2- tag商品标识为null, 返回4
3- dc_no(采购仓库编码) 为null 返回-1
4- dc_name(采购仓库名称为null) 返回其他仓
5- group_no(采购柜组编码)为null, 返回-1
6- group_name(采购柜组名称)为null, 返回其他柜组
7- 当 vendor_id、is_clear 为null, 返回0代码实现:
with t4 as(selectdate_format(t2.deal_date,yyyy-MM-dd HH:mm:ss) as trade_date_time,date_format(t2.trade_date,yyyy-MM-dd) as trade_date ,hour(t2.deal_date) as hourly,minute(t2.deal_date) as minute,t2.store_no as parent_store_no,coalesce(t2.sale_store_no,t2.store_no) as store_no,coalesce(t2.source_type,1) as source_type,if(t2.member_type like OL-% OR t2.member_type like SF-%,1,if(t2.member_type OR t2.member_type is null,0,2)) as member_type,if(t3.order_no is null,0,1) as is_balance_consume,if(t2.trade_id 1,0,if(t2.trade_id 2,2,5)) as trade_type,if(t1.sale_amount 0,t2.source_order_sn,if(t1.sale_amount 0,t2.parent_order_sn,t1.order_no)) as parent_order_no,t2.order_no,t2.pos_no,t2.ser_id,coalesce(t1.item,0) as item,coalesce(t1.sort,0) as sort,date_format(t2.pay_date,yyyy-MM-dd HH:mm:ss) as pay_time,date_format(t2.deal_date,yyyy-MM-dd HH:mm:ss) as last_update_time,coalesce(t2.cashier_no,0) as cashier_no,coalesce(t2.cashier_name,0) as cashier_name,coalesce(t2.member_center_sn,0) as zt_id,coalesce(t2.member_id,0) as member_id,t2.card_no,t1.goods_no,if(t1.combination_flag 1,1,0) as is_component,coalesce(t1.trade_mode_id,0) as trade_mode_id,t1.vendor_id,t1.contract_no,t1.is_daily_clear,coalesce(t1.share_user_id,0) as share_user_id,coalesce(t1.commission_amount,0) as commission_amount,t1.sale_qty,t1.sale_amount,t1.dis_amount,t1.sale_cost,if(t2.total_pay_amount 0,0,if(t3.pay_amount is null,0,cast(t3.pay_amount * t1.sale_amount / t2.total_pay_amount as decimal(27,2))))as balance_amountfrom (select * from ods.ods_sale_store_sale_dtl_i where combination_flag ! 2 and offset_flag 0) t1join ods.ods_sale_store_sale_info_i t2 on t2.order_no t1.order_noleft join (select * from ods.ods_sale_store_sale_pay_i where pay_type_id 201) t3 on t2.order_no t3.order_no
)insert overwrite table dwd.dwd_sale_store_sale_dtl_i partition(dt)
selectt4.trade_date_time,t4.trade_date,t5.week_trade_date,t5.month_trade_date,t4.hourly,casewhen t4.minute between 0 and 14 then 1when t4.minute between 15 and 29 then 2when t4.minute between 30 and 44 then 3when t4.minute between 45 and 59 then 4end as quarter,(t4.hourly * 4casewhen t4.minute between 0 and 14 then 1when t4.minute between 15 and 29 then 2when t4.minute between 30 and 44 then 3when t4.minute between 45 and 59 then 4end) as quarters,t4.parent_store_no,t4.store_no,t7.store_name,t7.store_sale_type,t7.store_type_code,t7.worker_num,t7.store_area,t7.city_id,t7.city_name,t7.region_code,t7.region_name,t7.is_day_clear,t4.trade_type,t4.source_type,t6.source_type_name,casewhen t4.source_type 1 then 1when t4.source_type 9 then 5when t4.source_type in (4,5,6,7,8) then 6when t4.source_type 11 then 8else 7end as sale_type,t4.member_type,t4.is_balance_consume,t4.parent_order_no,t4.order_no,t4.pos_no,t4.ser_id,t4.item,t4.sort,t4.pay_time,t4.last_update_time,t4.cashier_no,t4.cashier_name,t4.share_user_id,t4.commission_amount,t4.zt_id,t4.member_id,t4.card_no,t8.first_category_no,t8.first_category_name,t8.second_category_no,t8.second_category_name,t8.third_category_no,t8.third_category_name,t4.goods_no,t8.goods_name,t8.spec,t4.is_component,coalesce(t9.tag,4) as supply_team,coalesce(t9.dc_no,-1) as dc_no,coalesce(t9.dc_name,其他仓) as dc_name,coalesce(t9.group_no,-1) as group_no,coalesce(t9.group_name,其他柜组) as group_name ,t4.trade_mode_id,coalesce(t4.vendor_id,0) as vendor_id,t4.contract_no,coalesce(t9.is_clear,0) as is_clean,t4.is_daily_clear,t4.sale_qty,t4.sale_amount,t4.dis_amount,t4.sale_cost,t4.balance_amount,date_sub(current_date(),1) as write_time,t4.trade_date as dt
from t4left join dim.dwd_dim_date_f t5 on t4.trade_date t5.trade_dateleft join dim.dwd_dim_source_type_map_i t6 on t6.dt 2023-09-24 and t4.source_type t6.original_source_typeleft join dim.dwd_dim_store_i t7 on t7.dt 2023-09-24 and t4.store_no t7.store_noleft join dim.dwd_dim_goods_i t8 on t8.dt 2023-09-24 and t4.goods_no t8.goods_noleft join dim.dwd_dim_store_goods_i t9 on t9.dt 2023-09-24 and t9.goods_no t4.goods_no;5.1.2 线上余额支付明细
这个表的目的是为了计算dwd_sold_shop_order_dtl_i(商城核销明细表)因为是核销维度有可能有的订单从下单到签收经历了很长时间在判断是否是余额销售的时候就需要取到当时下单对应的支付单的情况因为支付单是按下单时间分区的不可能把全量数据都拿过来做关联所以这里就需要一个表来只记录余额支付的明细这样就极大的降低了数据量。
处理方案: 在门店销售支付表在where条件中设置pay_channel_name ‘余额支付’只保留余额支付的记录。
建表操作:
CREATE TABLE IF NOT EXISTS dwd.dwd_sale_shop_sale_balance_pay_i(store_no STRING COMMENT 门店编码,store_name STRING COMMENT 门店名称,trade_date TIMESTAMP COMMENT 交易日期,member_id BIGINT COMMENT 会员ID,zt_id BIGINT COMMENT 中台会员ID,trade_order_id STRING COMMENT 关联的交易单ID,pay_order_id STRING COMMENT 支付单id,order_no STRING COMMENT 订单号,pay_channel STRING COMMENT 支付渠道,pay_channel_name STRING COMMENT 支付渠道名称,trade_order_type BIGINT COMMENT 交易单类型1消费,2充值,3提现,4退货退款,trade_order_type_name STRING COMMENT 交易单类型名称,pay_amount DECIMAL(27, 2) COMMENT 支付对等RMB的金额比如是积分支付那这里就是积分所对应的RMB的金额,trade_merchant STRING COMMENT 交易商家
)
COMMENT 线上余额支付明细
partitioned by (dt STRING COMMENT 交易日期)
row format delimited fields terminated by ,
stored as orc
tblproperties (orc.compressSNAPPY);计算操作: 5.1.3 商城订单表(核销表)
订单下单完成后会经历各种状态变化只要更新就会同步过来更新后的数据。因为项目有核销主题、售卖主题在做具体的分析的时候希望核销主题的表是按照核销时间完成时间进行分区的售卖主题的表是按照售卖时间下单时间进行分区的。所以这里需要对shop_order表进行处理根据complete_time和create_time进行分别分区用于核销主题和售卖主题使用。
建表操作:
CREATE TABLE IF NOT EXISTS dwd.dwd_sold_shop_order_i(id BIGINT COMMENT 编号,parent_order_no STRING COMMENT 父单订单号,order_id STRING COMMENT 订单编号,is_split BIGINT COMMENT 是否拆单0-不需要拆单1-待拆单2-已拆单,platform_id BIGINT COMMENT 平台id1-有赞2-京东到家3-美团外卖4-饿了么,tid STRING COMMENT 平台订单号,source_type BIGINT COMMENT 订单来源10,20,30,40,41,50,60,70,source_name STRING COMMENT 订单来源名称10-有赞20-京东到家30-美团外卖40-饿了么41-百度外卖50-传智鲜精选60-黑马优选70-抖店,store_no STRING COMMENT 门店编码,city_id BIGINT COMMENT 城市编号,city_name STRING COMMENT 城市名称,region_code STRING COMMENT 区域编码,order_status BIGINT,order_status_desc STRING COMMENT 主订单状态描述0-新建; 1-待出票2-待备货3-待揽件4-待自提; 5-配送中6-已完成7-已取消,pay_type BIGINT COMMENT 支付类型1-线下支付2-线上支付,trade_type STRING COMMENT 交易类型。取值范围fixed(一口价) gift(送礼bulk_purchase来自分销商的采购present 赠品领取group 拼团订单 pifa 批发订单 cod 货到付款 peer 代付 qrcode扫码商家二维码直接支付的交易qrcode_3rd线下收银台二维码交易),is_deleted BIGINT COMMENT 是否有效1已删除0正常,order_create_time TIMESTAMP COMMENT 平台订单创建时间,order_pay_time TIMESTAMP COMMENT 订单支付时间,create_time TIMESTAMP COMMENT 创建时间,print_status BIGINT COMMENT 打印状态0-未打票;1-已打票,print_time TIMESTAMP COMMENT 出票时间,stock_up_status BIGINT COMMENT 门店处理状态0-待备货/1-已备货,stock_up_time TIMESTAMP COMMENT 备货完成时间,order_type BIGINT COMMENT 配送类型真正的订单类型由业务类型来决定1-及时送2-隔日送3-自提单,express_type BIGINT COMMENT 配送方式0-三方平台配送1-自配送2-快递3-自提,receive_time TIMESTAMP COMMENT 要求送达/自提时间,express_code STRING COMMENT 配送单号,delivery_status BIGINT COMMENT 配送状态0-待配送1-配送中2-已送达,delivery_time TIMESTAMP COMMENT 配送时间,pick_up_status BIGINT COMMENT 自提状态0-待自提1-已自提,qr_code STRING COMMENT qr提货码,pick_up_time TIMESTAMP COMMENT 自提时间,complete_time TIMESTAMP COMMENT 订单完结时间,is_cancel BIGINT COMMENT 是否取消,cancel_time TIMESTAMP COMMENT 取消时间,cancel_reason STRING COMMENT 取消原因,refund_status BIGINT COMMENT 退款状态0未退款1部分退款2已全额退款,refund_time TIMESTAMP COMMENT 已退款时间,last_update_time TIMESTAMP COMMENT 最新更新时间,order_total_amount DECIMAL(27, 2) COMMENT 订单总金额,product_total_amount DECIMAL(27, 2) COMMENT 商品总金额原价,pack_amount DECIMAL(27, 2) COMMENT 餐盒费/打包费,delivery_amount DECIMAL(27, 2) COMMENT 配送费,discount_amount DECIMAL(27, 2) COMMENT 订单优惠金额商家承担优惠金额平台补贴金额,seller_discount_amount DECIMAL(27, 2) COMMENT 商家承担优惠金额,platform_allowance_amount DECIMAL(27, 2) COMMENT 平台补贴金额,real_paid_amount DECIMAL(27, 2) COMMENT 实付金额,product_discount DECIMAL(27, 2) COMMENT 商品优惠金额,real_product_amount DECIMAL(27, 2) COMMENT 商品实际金额,buyer_id BIGINT COMMENT 买家id,buyer_phone STRING COMMENT 买家电话,buyer_remark STRING COMMENT 买家备注,r_name STRING COMMENT 收货人姓名,r_tel STRING COMMENT 收货人电话,r_province STRING COMMENT 收货人省份,r_city STRING COMMENT 收货人城市,r_district STRING COMMENT 收货人区域,r_address STRING COMMENT 收货人地址,r_zipcode STRING COMMENT 收货人邮编,is_tuan_head BIGINT COMMENT 是否为团长订单,store_leader_id BIGINT COMMENT 团长id,order_group_no STRING COMMENT 团单号,commision_amount DECIMAL(27, 2) COMMENT 抽佣金额,settle_amount DECIMAL(27, 2) COMMENT 结算金额,points_amount DECIMAL(27, 2) COMMENT 积分抵扣金额,pay_point BIGINT COMMENT 消费积分数,balance_amount DECIMAL(27, 2) COMMENT 余额扣除金额,pay_channel_amount DECIMAL(27, 2) COMMENT 通过支付渠道支付的金额,point_amount DECIMAL(27, 2) COMMENT 消费赠送积分,sync_erp_status BIGINT COMMENT 同步erp状态,sync_erp_msg STRING COMMENT 同步erp失败消息
)
COMMENT 商城订单表(核销表)
partitioned by (dt STRING COMMENT 完成日期)
row format delimited fields terminated by ,
stored as orc
tblproperties (orc.compressSNAPPY);ods_sale_shop_order_i 是按照最后更新时间进行分区的所以取出当天更新的数据并且只取完成时间是当天的数据插入dwd表时按照complete_time分区即可。
计算: 5.1.4 订单明细表(核销表)
与商城订单表一样, 只获取核销后的订单数据, 以及是最终完结的数据, 在售卖主题中, 获取下单时的信息数据即可
建表操作:
CREATE TABLE IF NOT EXISTS dwd.dwd_sold_shop_order_item_i(id BIGINT COMMENT 自增id,order_id STRING COMMENT 订单编号,goods_no STRING COMMENT 商品编码,goods_name STRING COMMENT 商品名称,weight DECIMAL(27, 3) COMMENT 重量单位kg,quantity BIGINT COMMENT 数量,unit STRING COMMENT 单位,sale_qty DECIMAL(27, 3) COMMENT 销售数量,disp_price DECIMAL(27, 2) COMMENT sku展示价格,pay_price DECIMAL(27, 2) COMMENT 价格,sale_amount DECIMAL(27, 2) COMMENT 单品销售金额,dis_amount DECIMAL(27, 2) COMMENT 单品总折扣金额,sale_cost DECIMAL(27, 2) COMMENT 销售成本,sale_type BIGINT COMMENT 类型1-常规2-赠品,create_time TIMESTAMP COMMENT 创建时间,complete_time TIMESTAMP COMMENT 完成时间,last_update_time TIMESTAMP COMMENT 更新时间,activity_plat_city_goods_id BIGINT COMMENT 活动商品区域id,activity_type BIGINT COMMENT 活动类型(11:拼团 21:秒杀),item_goods_key STRING COMMENT 虚拟字段itemgoodskey,is_deleted BIGINT COMMENT 是否删除0-否1-删除,transfer_paper_no STRING COMMENT 要货单号,serial_no BIGINT COMMENT 商品序号每个订单下起始都为1,is_delivery BIGINT COMMENT 仓发是否配送1:配送,goods_source_type BIGINT COMMENT 商品来源类型1-生鲜品2-标品,trade_mode_id BIGINT COMMENT 结算方式:1购销,2联营,vendor_id BIGINT COMMENT 供应商ID,contract_no STRING COMMENT 合同编号
)
COMMENT 订单明细表核销表
partitioned by (dt STRING COMMENT 完成日期)
row format delimited fields terminated by ,
stored as orc
tblproperties (orc.compressSNAPPY);处理方式与商城订单表也是类似的
计算: 5.1.5 商城核销明细表
这个表是将商城的订单表、订单明细表、退款表、退款明细表、支付表合起来形成的核销明细表。
需要5张事实表:dwd_sold_shop_order_i、dwd_sold_shop_order_item_i、dwd_sale_shop_sale_balance_pay_i: dwd_sale_shop_sale_balance_pay_i用order_no与订单表和订单明细表的order_id关联。ods_sale_shop_refund_i 退款表主要记录退款单号、时间、原因、金额等信息。ods_sale_shop_refund_item_i 退款明细表主要记录退款的商品信息以及活动信息等。因为退款单要获取原单的记录所以退款表还要与订单表进行关联来取一些字段。比较庆幸的一点是在退款的时候订单表也会进行更新这样在关联订单的时候只要在对应的那个分区里就可以找到对应的记录不用再去遍历多个分区来找。退款表、退款明细表、线上支付明细表四个表的关联关系如下使用退款表作为主表需要五张维表dwd_dim_date_fdwd_dim_store_idwd_dim_goods_idwd_dim_store_goods_iods_mem_member_union_i 会员基础信息表记录了会员的ID联系方式归属门店来源状态等信息。建表操作:
CREATE TABLE IF NOT EXISTS dwd.dwd_sold_shop_order_dtl_i(complete_time STRING COMMENT 订单完成时间,trade_date STRING COMMENT 交易日期,-- 日期表week_trade_date STRING COMMENT 周一日期,month_trade_date STRING COMMENT 月一日期,hourly BIGINT COMMENT 交易小时(0-23),quarter BIGINT COMMENT 刻钟:1.0-15,2.15-30,3.30-45,4.45-60,quarters BIGINT COMMENT 刻钟数:hourly*4quarters,parent_order_no STRING COMMENT 父单订单号/源单号,order_id STRING COMMENT 订单编号,trade_type BIGINT COMMENT 结算类型(0.正常交易,1.赠品发放,2.退货,4.培训,5.取消交易),is_split BIGINT COMMENT 是否拆单0-不需要拆单1-待拆单2-已拆单,platform_id BIGINT COMMENT 平台id1-有赞2-京东到家3-美团外卖4-饿了么,tid STRING COMMENT 平台订单号,source_type BIGINT COMMENT 订单来源10,20,30,40,41,50,60,70,source_name STRING COMMENT 订单来源名称10-有赞20-京东到家30-美团外卖40-饿了么41-百度外卖50-传智鲜精选60-黑马优选70-抖店,order_type BIGINT COMMENT 配送类型真正的订单类型由业务类型来决定1-及时送2-隔日送3-自提单,express_type BIGINT COMMENT 配送方式0-三方平台配送1-自配送2-快递3-自提,order_status BIGINT,order_status_desc STRING COMMENT 主订单状态描述0-新建; 1-待出票2-待备货3-待揽件4-待自提; 5-配送中6-已完成7-已取消,pay_type BIGINT COMMENT 支付类型1-线下支付2-线上支付,is_balance_consume BIGINT COMMENT 是否余额支付1是0否,store_no STRING COMMENT 店铺编码,-- 分店信息表store_name STRING COMMENT 店铺名称,store_sale_type BIGINT COMMENT 店铺销售类型,store_type_code BIGINT COMMENT 分店类型,worker_num BIGINT COMMENT 员工人数,store_area DECIMAL(27, 2) COMMENT 门店面积,city_id BIGINT COMMENT 城市ID,city_name STRING COMMENT 城市名称,region_code STRING COMMENT 区域编码,region_name STRING COMMENT 区域名称,is_day_clear BIGINT COMMENT 是否日清:0否,1是,order_create_time TIMESTAMP COMMENT 平台订单创建时间,order_pay_time TIMESTAMP COMMENT 订单支付时间,create_time TIMESTAMP COMMENT 创建时间,is_cancel BIGINT COMMENT 是否取消,cancel_time TIMESTAMP COMMENT 取消时间,cancel_reason STRING COMMENT 取消原因,last_update_time TIMESTAMP COMMENT 最新更新时间,-- 会员基础信息表zt_id BIGINT COMMENT 中台ID,buyer_id BIGINT COMMENT 买家id,buyer_phone STRING COMMENT 买家电话,buyer_remark STRING COMMENT 买家备注,r_name STRING COMMENT 收货人姓名,r_tel STRING COMMENT 收货人电话,r_province STRING COMMENT 收货人省份,r_city STRING COMMENT 收货人城市,r_district STRING COMMENT 收货人区域,r_address STRING COMMENT 收货人地址,r_zipcode STRING COMMENT 收货人邮编,is_tuan_head BIGINT COMMENT 是否为团长订单,store_leader_id BIGINT COMMENT 团长id,order_group_no STRING COMMENT 团单号,commission_amount DECIMAL(27, 2) COMMENT 抽佣金额,settle_amount DECIMAL(27, 2) COMMENT 结算金额,-- 商品表 first_category_no STRING COMMENT 一级分类编码,first_category_name STRING COMMENT 一级分类名称,second_category_no STRING COMMENT 二级分类编码,second_category_name STRING COMMENT 二级分类名称,third_category_no STRING COMMENT 三级分类编码,third_category_name STRING COMMENT 三级分类名称,goods_no STRING COMMENT 商品编码,goods_name STRING COMMENT 商品名称,weight DECIMAL(27, 3) COMMENT 重量单位kg,quantity DECIMAL(27, 3) COMMENT 数量,unit STRING COMMENT 单位,sale_qty DECIMAL(27, 3) COMMENT 销售数量,disp_price DECIMAL(27, 2) COMMENT sku展示价格,pay_price DECIMAL(27, 2) COMMENT 价格,sale_amount DECIMAL(27, 2) COMMENT 单品销售金额,dis_amount DECIMAL(27, 2) COMMENT 单品总折扣金额,sale_cost DECIMAL(27, 2) COMMENT 销售成本,sale_type BIGINT COMMENT 类型1-常规2-赠品,activity_plat_city_goods_id BIGINT COMMENT 活动商品区域id,activity_type BIGINT COMMENT 活动类型(11:拼团 21:秒杀),order_total_amount DECIMAL(27, 2) COMMENT 订单总金额(平摊),order_discount_amount DECIMAL(27, 2) COMMENT 订单优惠金额商家承担优惠金额平台补贴金额(平摊),order_paid_amount DECIMAL(27, 2) COMMENT 实付金额(平摊),balance_amount DECIMAL(27, 2) COMMENT 余额支付,-- 门店商品信息表supply_team BIGINT COMMENT 供应链团队 1.平台商品,2.优选标品,3.传智鲜标品,4.传智鲜生鲜,5优选POS商品,dc_no STRING COMMENT 采购仓库编号,dc_name STRING COMMENT 采购仓库名称,group_no STRING COMMENT 采购柜组编号,group_name STRING COMMENT 采购柜组名称,trade_mode_id BIGINT COMMENT 结算方式:1购销,2联营,vendor_id BIGINT COMMENT 供应商ID,contract_no STRING COMMENT 合同编号
)
COMMENT 商城核销明细表
partitioned by (dt STRING COMMENT 核销日期)
row format delimited fields terminated by ,
stored as orc
tblproperties (orc.compressSNAPPY);分析操作:
1- 先将 商城订单表、订单明细表、线上余额支付明细 三个表进行关联: 其处理操作与门店销售明细宽表类似1- 当is_cancel 为1是, 返回5 否则返回02- 是否为余额支付, 判断支付表即可3- commission_amount、settle_amount order_total_amount order_discount_amount order_paid_amount balance_amount 需要计算2- 接着将退款表、退款明细表、商城订单表以及线上余额支付明细表进行进行关联: 其处理操作与门店销售明细宽表类似1- 日期使用退款的创建时间即可2- trade_type(结算类型), 如果cancel_time不为null, 返回5, 否则返回23- 需要将计算后的commission_amount(抽佣金额), settle_amount(结算金额)调整为负数5- quantity,qty,amount,cost调整为负数(做对冲(冲减单))6- weight,unit,dis_amount,order_discount_amount调整为07- sale_type类型只有18- unit设置为空即可3- 将1和2的结果 基于union all 关联在一起, 然后和五张维度表进行关联: 其处理操作与门店销售明细宽表类似SQL实现: 5.1.6 门店库调表
库调即库存调整类型有日清活动、盘点更正、报损/溢等。对于这些情况都是广义上的损耗。为了计算损耗需要对库调表进行加宽处理。
需求说明: 对源表进行加宽处理处理时间、添加门店信息等。
建表操作:
CREATE TABLE IF NOT EXISTS dwd.dwd_stock_store_stock_adj_i(trade_date STRING COMMENT 库存处理日期,week_trade_date STRING COMMENT 周一日期,month_trade_date STRING COMMENT 月一日期,hourly BIGINT COMMENT 交易小时(0-23),quarter BIGINT COMMENT 刻钟:1.0-15,2.15-30,3.30-45,4.45-60,quarters BIGINT COMMENT 刻钟数:hourly*4quarters,id BIGINT COMMENT 主键,uid STRING COMMENT 唯一标识,order_id STRING COMMENT 库调单号,order_source BIGINT COMMENT 下单来源小程序、pc等, store_no STRING COMMENT 店铺编码,store_name STRING COMMENT 店铺名称,store_sale_type BIGINT COMMENT 店铺销售类型,store_type_code BIGINT COMMENT 分店类型,worker_num BIGINT COMMENT 员工人数,store_area DECIMAL(27, 2) COMMENT 门店面积,city_id BIGINT COMMENT 城市ID,city_name STRING COMMENT 城市名称,region_code STRING COMMENT 区域编码,region_name STRING COMMENT 区域名称,is_day_clear BIGINT COMMENT 是否日清:0否,1是,goods_no STRING COMMENT 商品编码,goods_name STRING COMMENT 商品名称,adj_type_big STRING COMMENT 库调类型大类,adj_type_small STRING COMMENT 库调类型小类,adj_reason_big STRING COMMENT 库调原因大类,adj_reason_small STRING COMMENT 库调原因小类,adj_qty DECIMAL(27, 3) COMMENT 库调数量,adj_price DECIMAL(27, 2) COMMENT 库调单价,adj_amount DECIMAL(27, 2) COMMENT 库调金额,create_time TIMESTAMP COMMENT 创建时间,stock_deal_time TIMESTAMP COMMENT 库存处理时间,sync_time TIMESTAMP COMMENT 数据同步时间,vendor_no STRING COMMENT 供应商编码,vendor_name STRING COMMENT 供应商名称
)
COMMENT 门店库调单
partitioned by(dt STRING COMMENT 库存处理时间)
row format delimited fields terminated by ,
stored as orc
tblproperties (orc.compressSNAPPY);涉及表: 门店库调表 和 日期表以及分店信息表 进行关联
分析处理:
时间采用库存处理时间计算处理: 5.1.7 门店收货单
门店的收货情况包括单号时间商品信息数量和金额等。
涉及表: ods_order_store_receive_i(门店收货单)、时间表、分店信息表关联
建表操作:
CREATE TABLE IF NOT EXISTS dwd.dwd_order_store_receive_i(trade_date STRING COMMENT 库存处理日期,week_trade_date STRING COMMENT 周一日期,month_trade_date STRING COMMENT 月一日期,hourly BIGINT COMMENT 交易小时(0-23),quarter BIGINT COMMENT 刻钟:1.0-15,2.15-30,3.30-45,4.45-60,quarters BIGINT COMMENT 刻钟数:hourly*4quarters,id BIGINT COMMENT 主键,uid STRING COMMENT 唯一标识,order_id STRING COMMENT 收货单号,order_source BIGINT COMMENT 下单来源小程序、pc等,store_no STRING COMMENT 店铺编码,store_name STRING COMMENT 店铺名称,store_sale_type BIGINT COMMENT 店铺销售类型,store_type_code BIGINT COMMENT 分店类型,worker_num BIGINT COMMENT 员工人数,store_area DECIMAL(27, 2) COMMENT 门店面积,city_id BIGINT COMMENT 城市ID,city_name STRING COMMENT 城市名称,region_code STRING COMMENT 区域编码,region_name STRING COMMENT 区域名称,is_day_clear BIGINT COMMENT 是否日清:0否,1是,goods_no STRING COMMENT 商品编码,goods_name STRING COMMENT 商品名称,dc_no STRING COMMENT 配送中心编码,dc_name STRING COMMENT 配送中心名称,vendor_no STRING COMMENT 供应商编码,vendor_name STRING COMMENT 供应商名称,order_type BIGINT COMMENT 订单类型1-直送2-配送3-代发,receive_price DECIMAL(27, 2) COMMENT 收货价,receive_qty DECIMAL(27, 3) COMMENT 收货数量,git_qty DECIMAL(27, 3) COMMENT 赠品数量,create_time TIMESTAMP COMMENT 创建时间,stock_deal_time TIMESTAMP COMMENT 库存处理时间,dc_send_order_id STRING COMMENT 仓库发货单号,red_order_id STRING COMMENT 被红冲单号,contract_no STRING COMMENT 合同编号,contract_name STRING COMMENT 合同名称,trade_mode BIGINT COMMENT 1-直营2-联营,order_source_type BIGINT COMMENT 订货标识0-门店订货1-采购配货,sync_time TIMESTAMP COMMENT 数据同步时间
)
COMMENT 门店收货单
partitioned by (dt STRING COMMENT 库存处理时间)
row format delimited fields terminated by ,
stored as orc
tblproperties (orc.compressSNAPPY);计算处理: 日期字段 stock_deal_time 5.1.8 门店退货单
门店退给供应商的商品情况包括单号、门店、时间、商品信息、数量、金额、原因等。
涉及表: ods_order_store_return_to_vendor_i(门店退货单)、时间表、门店信息表
建表操作:
CREATE TABLE IF NOT EXISTS dwd.dwd_order_store_return_to_vendor_i(trade_date STRING COMMENT 库存处理日期,week_trade_date STRING COMMENT 周一日期,month_trade_date STRING COMMENT 月一日期,hourly BIGINT COMMENT 交易小时(0-23),quarter BIGINT COMMENT 刻钟:1.0-15,2.15-30,3.30-45,4.45-60,quarters BIGINT COMMENT 刻钟数:hourly*4quarters,id BIGINT COMMENT 主键,uid STRING COMMENT 唯一标识,order_id STRING COMMENT 退配单号,order_source BIGINT COMMENT 下单来源小程序、pc、智能补货、系统等,store_no STRING COMMENT 门店编码,store_name STRING COMMENT 门店名称,store_sale_type BIGINT COMMENT 店铺销售类型,store_type_code BIGINT COMMENT 分店类型,worker_num BIGINT COMMENT 员工人数,store_area DECIMAL(27, 2) COMMENT 门店面积,city_id BIGINT COMMENT 城市ID,city_name STRING COMMENT 城市名称,region_code STRING COMMENT 区域编码,region_name STRING COMMENT 区域名称,is_day_clear BIGINT COMMENT 是否日清:0否,1是,goods_no STRING COMMENT 商品编码,goods_name STRING COMMENT 商品名称,dc_no STRING COMMENT 配送中心编码,dc_name STRING COMMENT 配送中心名称,vendor_no STRING COMMENT 供应商编码,vendor_name STRING COMMENT 供应商名称,return_price DECIMAL(27, 2) COMMENT 退配价,return_qty DECIMAL(27, 3) COMMENT 退配数量,create_time TIMESTAMP COMMENT 创建时间,stock_deal_time TIMESTAMP COMMENT 库存处理时间,original_order_id STRING COMMENT 退配原单号,is_fresh BIGINT COMMENT 是否为生鲜店0-否1-是,is_entity BIGINT COMMENT 是否实物退回0-否1-是,responsible_person STRING COMMENT 责任归属方,return_reason_big STRING COMMENT 退配原因大类,return_desc_big STRING COMMENT 退配说明大类,return_reason_small STRING COMMENT 退配原因小类,return_desc_small STRING COMMENT 退配说明小类,sync_time TIMESTAMP COMMENT 数据同步时间
)
COMMENT 门店退货单
partitioned by (dt STRING COMMENT 库存处理时间)
row format delimited fields terminated by ,
stored as orc
tblproperties (orc.compressSNAPPY);计算:时间字段 stock_deal_time 5.1.9 门店退配单
门店退给大仓的商品情况包括单号、门店、时间、商品信息、数量、金额、原因等。
涉及表: ods_order_store_return_to_dc_i(门店退配单)、时间表、门店信息表
建表操作:
CREATE TABLE IF NOT EXISTS dwd.dwd_order_store_return_to_dc_i(trade_date STRING COMMENT 库存处理日期,week_trade_date STRING COMMENT 周一日期,month_trade_date STRING COMMENT 月一日期,hourly BIGINT COMMENT 交易小时(0-23),quarter BIGINT COMMENT 刻钟:1.0-15,2.15-30,3.30-45,4.45-60,quarters BIGINT COMMENT 刻钟数:hourly*4quarters,id BIGINT COMMENT 主键,uid STRING COMMENT 唯一标识,order_id STRING COMMENT 退配单号,order_source BIGINT COMMENT 下单来源小程序、pc、智能补货、系统等,store_no STRING COMMENT 门店编码,store_name STRING COMMENT 门店名称,store_sale_type BIGINT COMMENT 店铺销售类型,store_type_code BIGINT COMMENT 分店类型,worker_num BIGINT COMMENT 员工人数,store_area DECIMAL(27, 2) COMMENT 门店面积,city_id BIGINT COMMENT 城市ID,city_name STRING COMMENT 城市名称,region_code STRING COMMENT 区域编码,region_name STRING COMMENT 区域名称,is_day_clear BIGINT COMMENT 是否日清:0否,1是,goods_no STRING COMMENT 商品编码,goods_name STRING COMMENT 商品名称,dc_no STRING COMMENT 配送中心编码,dc_name STRING COMMENT 配送中心名称,vendor_no STRING COMMENT 供应商编码,vendor_name STRING COMMENT 供应商名称,return_price DECIMAL(27, 2) COMMENT 退配价,return_qty DECIMAL(27, 3) COMMENT 退配数量,create_time TIMESTAMP COMMENT 创建时间,stock_deal_time TIMESTAMP COMMENT 库存处理时间,original_order_id STRING COMMENT 退配原单号,is_fresh BIGINT COMMENT 是否为生鲜店0-否1-是,is_entity BIGINT COMMENT 是否实物退回0-否1-是,responsible_person STRING COMMENT 责任归属方,return_reason_big STRING COMMENT 退配原因大类,return_desc_big STRING COMMENT 退配说明大类,return_reason_small STRING COMMENT 退配原因小类,return_desc_small STRING COMMENT 退配说明小类,sync_time TIMESTAMP COMMENT 数据同步时间,batch_type_id STRING COMMENT 批次类型id
)
COMMENT 门店退配单
partitioned by (dt STRING COMMENT 库存处理时间)
row format delimited fields terminated by ,
stored as orc
tblproperties (orc.compressSNAPPY);计算:时间字段 stock_deal_time 5.1.10 门店调入单
从其他门店调入本店的单据信息包括时间、单号、商品、门店、商品信息、数量和金额等。
涉及表: ods_order_store_alloc_in_i(门店调入单) 、时间表、门店表
建表操作:
CREATE TABLE IF NOT EXISTS dwd.dwd_order_store_alloc_in_i(trade_date STRING COMMENT 库存处理日期,week_trade_date STRING COMMENT 周一日期,month_trade_date STRING COMMENT 月一日期,hourly BIGINT COMMENT 交易小时(0-23),quarter BIGINT COMMENT 刻钟:1.0-15,2.15-30,3.30-45,4.45-60,quarters BIGINT COMMENT 刻钟数:hourly*4quarters,id BIGINT COMMENT 主键,uid STRING COMMENT 唯一标识,order_id STRING COMMENT 调拨单号,order_source BIGINT COMMENT 下单来源小程序、PC等,goods_no STRING COMMENT 商品编码,goods_name STRING COMMENT 商品名称,alloc_in_store_no STRING COMMENT 调入门店编码,alloc_in_store_name STRING COMMENT 调入门店名称,alloc_in_store_sale_type BIGINT COMMENT 调入店铺销售类型,alloc_in_store_type_code BIGINT COMMENT 调入分店类型,alloc_in_worker_num BIGINT COMMENT 调入门店员工人数,alloc_in_store_area DECIMAL(27, 2) COMMENT 调入门店面积,alloc_in_city_id BIGINT COMMENT 调入门店城市ID,alloc_in_city_name STRING COMMENT 调入门店城市名称,alloc_in_region_code STRING COMMENT 调入门店区域编码,alloc_in_region_name STRING COMMENT 调入门店区域名称,alloc_in_is_day_clear BIGINT COMMENT 调入门店是否日清:0否,1是,alloc_out_store_no STRING COMMENT 调出门店编码,alloc_out_store_name STRING COMMENT 调出门店名称,alloc_price DECIMAL(27, 2) COMMENT 调拨单价,alloc_qty DECIMAL(27, 3) COMMENT 调拨数量,alloc_reason STRING COMMENT 调拨原因,alloc_amount DECIMAL(27, 2) COMMENT 调拨金额,create_time TIMESTAMP COMMENT 创建时间,stock_deal_time TIMESTAMP COMMENT 库存处理时间,sync_time TIMESTAMP COMMENT 数据同步时间,vendor_no STRING COMMENT 供应商编码,vendor_name STRING COMMENT 供应商名称
)
COMMENT 门店调入单
partitioned by (dt STRING COMMENT 库存处理时间)
row format delimited fields terminated by ,
stored as orc
tblproperties (orc.compressSNAPPY);计算:时间字段为stock_deal_time 5.1.11 门店调出单
从本店调入其他门店的单据信息包括时间、单号、商品、门店、商品信息、数量和金额等。
涉及表: ods_order_store_alloc_out_i(门店调出单)、时间表、门店信息表
建表操作:
CREATE TABLE IF NOT EXISTS dwd.dwd_order_store_alloc_out_i(trade_date STRING COMMENT 库存处理日期,week_trade_date STRING COMMENT 周一日期,month_trade_date STRING COMMENT 月一日期,hourly BIGINT COMMENT 交易小时(0-23),quarter BIGINT COMMENT 刻钟:1.0-15,2.15-30,3.30-45,4.45-60,quarters BIGINT COMMENT 刻钟数:hourly*4quarters,id BIGINT COMMENT 主键,uid STRING COMMENT 唯一标识,order_id STRING COMMENT 调拨单号,order_source BIGINT COMMENT 下单来源小程序、PC等,goods_no STRING COMMENT 商品编码,goods_name STRING COMMENT 商品名称,alloc_in_store_no STRING COMMENT 调入门店编码,alloc_in_store_name STRING COMMENT 调入门店名称,alloc_out_store_no STRING COMMENT 调出门店编码,alloc_out_store_name STRING COMMENT 调出门店名称,alloc_out_store_sale_type BIGINT COMMENT 调出店铺销售类型,alloc_out_store_type_code BIGINT COMMENT 调出分店类型,alloc_out_worker_num BIGINT COMMENT 调出门店员工人数,alloc_out_store_area DECIMAL(27, 2) COMMENT 调出门店面积,alloc_out_city_id BIGINT COMMENT 调出门店城市ID,alloc_out_city_name STRING COMMENT 调出门店城市名称,alloc_out_region_code STRING COMMENT 调出门店区域编码,alloc_out_region_name STRING COMMENT 调出门店区域名称,alloc_out_is_day_clear BIGINT COMMENT 调出门店是否日清:0否,1是,alloc_price DECIMAL(27, 2) COMMENT 调拨单价,alloc_qty DECIMAL(27, 3) COMMENT 调拨数量,alloc_reason STRING COMMENT 调拨原因,alloc_amount DECIMAL(27, 2) COMMENT 调拨金额,create_time TIMESTAMP COMMENT 创建时间,stock_deal_time TIMESTAMP COMMENT 库存处理时间,sync_time TIMESTAMP COMMENT 数据同步时间,vendor_no STRING COMMENT 供应商编码,vendor_name STRING COMMENT 供应商名称
)
COMMENT 门店调出单
partitioned by (dt STRING COMMENT 库存处理时间)
row format delimited fields terminated by ,
stored as orc
tblproperties (orc.compressSNAPPY);计算SQL: 时间字段为stock_deal_time 5.1.12 门店要货单
门店的要货信息包括时间、单号、门店、商品信息、数量和金额、要货方式、状态等。
涉及表: ods_order_store_require_i(门店要货单)和日期表和门店表
建表操作:
CREATE TABLE IF NOT EXISTS dwd.dwd_order_store_require_i(trade_date STRING COMMENT 确认日期,week_trade_date STRING COMMENT 周一日期,month_trade_date STRING COMMENT 月一日期,hourly BIGINT COMMENT 交易小时(0-23),quarter BIGINT COMMENT 刻钟:1.0-15,2.15-30,3.30-45,4.45-60,quarters BIGINT COMMENT 刻钟数:hourly*4quarters,id BIGINT COMMENT 主键,uid STRING COMMENT 唯一标识,order_id STRING COMMENT 要货单号,order_source BIGINT COMMENT 下单来源小程序、pc、智能补货、系统等,store_no STRING COMMENT 门店编码,store_name STRING COMMENT 门店名称,store_sale_type BIGINT COMMENT 店铺销售类型,store_type_code BIGINT COMMENT 分店类型,worker_num BIGINT COMMENT 员工人数,store_area DECIMAL(27, 2) COMMENT 门店面积,city_id BIGINT COMMENT 城市ID,city_name STRING COMMENT 城市名称,region_code STRING COMMENT 区域编码,region_name STRING COMMENT 区域名称,is_day_clear BIGINT COMMENT 是否日清:0否,1是,goods_no STRING COMMENT 商品编码,goods_name STRING COMMENT 商品名称,dc_no STRING COMMENT 配送中心编码,dc_name STRING COMMENT 配送中心名称,vendor_no STRING COMMENT 供应商编码,vendor_name STRING COMMENT 供应商名称,group_no STRING COMMENT 采购柜组编号,require_price DECIMAL(27, 2) COMMENT 要货价格,require_qty DECIMAL(27, 3) COMMENT 要货数量,create_time TIMESTAMP COMMENT 创建时间,send_time TIMESTAMP COMMENT 预计送货时间,collect_require_order_id STRING COMMENT 要货汇总单号,require_type_code BIGINT COMMENT 要货类型1-直送2-配送3-代发,is_online BIGINT COMMENT 1-线上0-线下,confirm_time TIMESTAMP COMMENT 审核时间,is_canceled BIGINT COMMENT 1-取消0-正常,sync_time TIMESTAMP COMMENT 数据同步时间,is_urgent BIGINT COMMENT 是否加急 0,否 1是,original_order_price DECIMAL(27, 2) COMMENT 原单价
)
COMMENT 门店要货单
partitioned by (dt STRING COMMENT confirm_time时间)
row format delimited fields terminated by ,
stored as orc
tblproperties (orc.compressSNAPPY);
