[数据仓库]电商核心业务知识之订单商品模块

深藏阁楼爱情的钟 2022-05-10 06:58 322阅读 0赞

电商核心业务知识  
  
  
订单商品模块（9张表）  
\--订单主要信息表  
drop table if exists itqsc.ods\_b2c\_orders;  
create external table itqsc.ods\_b2c\_orders  
(  
order\_id  bigint, -订单ID（主键）  
order\_no string, -订单号  
order\_date timestamp, -订单日期  
user\_id bigint, -用户ID  
user\_name string, -登陆名  
order\_money double, -订单金额  
order\_type string, -订单类型  
order\_status string, -订单状态  
pay\_type string, -支付类型  
pay\_status string, -支付状态  
order\_source string, -订单来源  
last\_update\_time timestamp, -订单最后修改时间  
dw\_date timestamp  
)  
partitioned by  
(dt string)  
LOCATION 'hdfs://hadoop0:9000/user/hadoop/dev/itqsc/ods\_b2c\_orders';  
  
  
\--订单商品信息表  
drop table if exists itqsc.ods\_b2c\_orders\_goods;  
create external table itqsc.ods\_b2c\_orders\_goods  
(  
order\_id  bigint, -订单ID  
goods\_id bignit, -商品ID  
cat\_id bigint, -类目ID  
size\_id bigint, -条码ID(联合主键)  
cat\_name string, -类目名称  
goods\_price double, -商品价格  
goods\_amout bigint, -数量  
last\_update\_time timestamp, -最后修改时间  
dw\_date timestamp  
)  
partitioned by  
(dt string)  
LOCATION 'hdfs://hadoop0:9000/user/hadoop/dev/itqsc/ods\_b2c\_orders\_goods';  
  
  
  
  
\--订单详细信息表  
drop table if exists itqsc.ods\_b2c\_orders\_desc;  
create external table itqsc.ods\_b2c\_orders\_desc  
(  
order\_id  bigint, -订单ID（主键）  
order\_no string, -订单号  
consignee string, -收货人姓名  
area\_id bigint, -收货人地址ID  
area\_name string, -收货人ID对应地址段  
address string, -收货人街道地址  
mobilephone string, -收货人手机号  
telphone string, -收货人电话  
coupon\_id bigint, -使用代金券ID  
coupon\_money double, -使用代金券金额  
carriage\_money double, -运费  
last\_update\_time timestamp, -最后修改时间  
dw\_date timestamp  
)  
partitioned by  
(dt string)  
LOCATION 'hdfs://hadoop0:9000/user/hadoop/dev/itqsc/ods\_b2c\_orders\_desc';  
  
  
\----订单与商品宽表(核心地位，又叫SKU表)，以上三表的关联，粒度非常细  
drop table if exists itqsc.dm\_b2c\_orders\_goods;  
create external table itqsc.dm\_b2c\_orders\_goods  
(  
order\_id  bigint, -订单ID  
goods\_id bignit, -商品ID  
cat\_id bigint, -类目ID  
size\_id bigint, -条码ID  
cat\_name string, -类目名称  
goods\_price double, -商品价格  
goods\_amount bigint, -数量  
order\_no string, -订单号  
order\_date timestamp, -订单日期  
user\_id bigint, -用户ID  
user\_name string, -登陆名  
order\_money double, -订单金额  
order\_type string, -订单类型  
order\_status string, -订单状态  
pay\_type string, -支付类型  
pay\_status string, -支付状态  
order\_source string, -订单来源  
coupon\_id bigint, -使用代金券ID  
coupon\_money double, -使用代金券金额  
carriage\_money double, -运费  
dw\_date timestamp  
)  
  
  
\----订单宽表(核心地位)  
drop table if exists itqsc.dm\_b2c\_orders;  
create external table itqsc.dm\_b2c\_orders  
(  
order\_id  bigint, -订单ID  
order\_no string, -订单号  
order\_date timestamp, -订单日期  
user\_id bigint, -用户ID  
user\_name string, -登陆名  
order\_money double, -订单金额  
order\_type string, -订单类型  
order\_status string, -订单状态  
pay\_type string, -支付类型  
pay\_status string, -支付状态  
order\_source string, -订单来源  
consignee string, -收货人姓名  
area\_id bigint, -收货人地址ID  
area\_name string, -收货人ID对应地址段  
address string, -收货人街道地址  
mobilephone string, -收货人手机号  
telphone string, -收货人电话  
coupon\_id bigint, -使用代金券ID  
coupon\_money double, -使用代金券金额  
carriage\_money double, -运费  
create\_time timestamp, -创建时间  
last\_update\_time, timestamp, -最后修改时间  
dw\_date timestamp  
)  
  
  
\--购物车表  
drop table if exists itqsc.ods\_b2c\_cart;  
create external table itqsc.ods\_b2c\_cart  
(  
id bigint, -ID  
session\_id string, --session\_id  
user\_id  bigint, -用户  
goods\_id bigint, -商品ID  
number bigint, - 商品数量  
add\_time timestamp, -商品加入时间  
cancel\_time timestamp, -商品取消时间  
submit\_time timestamp, -商品提交时间  
dw\_date timestamp  
)  
partitioned by  
(dt string)  
LOCATION 'hdfs://hadoop0:9000/user/hadoop/dev/itqsc/ods\_b2c\_cart';  
  
  
\-订单指标表  
drop table if exists itqsc.dm\_user\_order\_tag;  
create external table itqsc.dm\_user\_order\_tag  
(  
user\_id  bigint, -用户  
first\_order\_time timestamp, -第一次消费时间  
last\_order\_time timestamp, -最近一次消费时间  
first\_order\_ago bigint, -首单距今时间  
last\_order\_ago bigint, -尾单距今时间  
month1\_order\_cnt bigint, -近30天购买次数  
month1\_order\_amt bouble, -近30天购买金额  
month2\_order\_cnt bigint, -近60天购买次数  
month2\_order\_amt bouble, -近60天购买金额  
month3\_order\_cnt bigint, -近90天购买次数  
month3\_order\_amt double, -近90天购买金额  
max\_order\_amt double, -最大消费金额  
min\_order\_amt double, -最小消费金额  
total\_order\_cnt bigint, -累计消费次数（不汗退拒）  
total\_order\_amt double,-累计消费金额（不汗退拒）  
total\_coupon\_amt double,-累计使用代金券金额  
user\_avg\_amt double, -客单价（含退拒）  
month3\_user\_avg\_amt double, -近90天客单位（含退拒）  
common\_address string, -常用收货地区  
common\_paytype string, -常用支付方式  
month1\_cart\_cnt bigint, -近30天购物车次数  
month1\_cart\_goods\_cnt bigint, -近30天购物车商品件数  
month1\_cart\_submit\_cnt bigint, -近30天购物车提交商品件数  
month1\_cart\_rate double, -近30天购物车成功率  
month1\_cart\_cancel\_cnt bigint, -近30天购物车放弃件数  
dw\_date timestamp  
)  
partitioned by  
(dt string)  
LOCATION 'hdfs://hadoop0:9000/user/hadoop/dev/itqsc/dm\_user\_order\_tag';  
  
  
\-商品信息表  
drop table if exists itqsc.ods\_b2c\_goods;  
create external table itqsc.ods\_b2c\_goods(  
sku\_id bigint, -sku编号  
sku\_name string, -SKU名称  
goods\_id bigint, -商品编号  
goods\_no string, -商品货号  
goods\_sn string, -商品条码  
goods\_name string, -商品名称  
size\_id bigint, -尺码编号  
size\_name string, -尺码名称  
colour\_id bigint, -颜色ID  
shop\_id bigint, -店铺编号  
shop\_name bigint, -店铺名称  
curr\_price double, -售卖价格  
market\_price double, -市场价格  
discount double, -折扣比例  
cost\_price double, -成本价格  
cost\_type string, -成本类型  
warehouse string, -所在仓库  
stock\_cnt bigint, -进货数量   
stock\_amt double, -进货货值  
first\_cat bignit, -一级分类ID  
first\_cat\_name string, -一级分类名称  
second\_cat bigint, -二级分类ID  
second\_cat\_name string, -二级分类名称  
third\_cat bignit, -三级分类ID  
third\_cat\_name string, -三级分类名称  
dw\_date timestamp  
)  
  
  
\-商品指标计算  
drop table if exists itqsc.dm\_user\_goods\_amt;  
create external table itqsc.dm\_user\_goods\_amt  
(  
user\_id  bigint, -用户  
p\_sales\_cnt bigint, -排除退拒销售数量  
p\_sales\_amt double, -排除退拒销售金额  
p\_sales\_cnt\_amt double, -排除退拒销售金额（扣促销减免）  
h\_sales\_cnt bigint, -含退拒销售数量  
h\_sales\_amt double, -含退拒销售金额  
h\_sales\_cnt\_amt double, -含退拒销售金额（扣促销减免）  
return\_cnt bigint, -退货商品数量  
return\_amt double, -退货商品金额  
reject\_cnt bigint, -拒收商品数量  
reject\_amt double, -拒收商品金额  
common\_first\_cat string, -最常购买商品一级类目名称  
common\_second\_cat string, -最常购买商品二级类目名称  
common\_third\_cat string, -最常购买商品三级类目名称  
dw\_date timestamp  
)  
partitioned by  
(dt string)  
LOCATION 'hdfs://hadoop0:9000/user/hadoop/dev/itqsc/dm\_user\_goods\_amt'; --------------------- 本文来自 houxiaoqin 的CSDN 博客 ，全文地址请点击：https://blog.csdn.net/houxiaoqin/article/details/47612175?utm\_source=copy