2订单分析地域、分类维度分析业务开发
总结需求1:
ads数据都来源于dw, DW层数据都来源于ods。 ads一张表存储了18个需求的所有数据
2.1 需求分析
集团总公司分为很多的分公司(销售事业部)
为了公司的经营需要,公司需要定期检查各个分公司的经营指标完成情况,运营部门提出数据分析需求:
交易金额
交易笔数
微信交易笔数
支付宝交易笔数
维度如下:
商品分类
行政区域
要求:最终可以根据不同大区、不同城市、不能分类级别查询交易数据,也就是要求支持不同维度的组合查询。
需求:
获取全国、无商品分类维度的分交易类型数据
获取全国、无商品分类维度的不分交易类型的数据
获取全国、一级商品分类维度的分交易类型数据
获取全国、一级商品分类维度的不分交易类型数据
获取全国、二级商品分类维度的分交易类型数据
获取全国、二级商品不分类维度的分交易类型数据
获取大区、无商品分类维度的分交易类型数据
获取大区、无商品不分类维度的不分交易类型数据
获取大区、一级商品分类维度的分交易类型数据
获取大区、一级商品分类维度的不分交易类型数据
获取大区、二级商品分类维度的分交易类型数据
获取大区、二级商品分类维度的不分交易类型数据
获取城市、无商品分类维度的分交易类型数据
获取城市、无商品分类维度的不分交易类型数据
获取城市、一级商品分类维度的不分交易类型数据
获取城市、一级商品分类维度的分交易类型数据
获取城市、二级商品分类维度的不分交易类型数据
获取城市、二级商品分类维度的分交易类型数据
根据需求整理出用到的表
最终的流程图:
2.2 创建 ads 层数据表
– 创建ads(数据集市层)订单分析表
DROP TABLE IF EXISTSitcast_ads
.ads_trade_order
;
CREATE TABLEitcast_ads
.ads_trade_order
(
orgtype
bigint,
orgid
bigint,
cattype
bigint,
catid
bigint,
paytype
bigint,
ordercount
bigint,
goodsprice
double)
PARTITIONED BY (dt
string)
STORED AS PARQUET;
orgtype
区域类型
orgid
区域类型ID
cattype
品类
catid
品类ID
paytype 支付方式
ordercount订单总数
goodsprice` 订单金额
2.3 创建 dw 层数据表
该层主要创建维度表与事实表。为了便于识别,维度表增加 dim_ 前缀。事实表增加 fact_ 前缀
fact_orders
– 创建dw层订单事实表
DROP TABLE IF EXISTSitcast_dw
.fact_orders
;
CREATE TABLEitcast_dw
.fact_orders
(
orderid
bigint,
orderno
string,
userid
bigint,
orderstatus
bigint,
goodsmoney
double,
delivertype
bigint,
delivermoney
double,
totalmoney
double,
realtotalmoney
double,
paytype
bigint,
ispay
bigint,
areaid
bigint,
areaidpath
string,
orderscore
bigint,
isinvoice
bigint,
invoiceclient
string,
orderremarks
string,
ordersrc
bigint,
needpay
double,
isrefund
bigint,
isclosed
bigint,
receivetime
string,
deliverytime
string,
tradeno
string,
createtime
string,
commissionfee
double,
scoremoney
double,
usescore
bigint,
noticedeliver
bigint,
lockcashmoney
double,
paytime
string,
isbatch
bigint,
totalpayfee
bigint)
partitioned by (dt string)
STORED AS PARQUET;
dim_goods
– dw层商品维度表,通过拉链表已经创建
dim_goods_cats
– 创建dw层商品分类维度表
DROP TABLE IF EXISTSitcast_dw
.dim_goods_cats
;
CREATE TABLEitcast_dw
.dim_goods_cats
(
catid
bigint,
catname
string,
parentid
bigint,
isshow
bigint,
isfloor
bigint,
createtime
bigint)
partitioned by (dt string,catLevel bigint)
STORED AS PARQUET;
dim_org
DROP TABLE IF EXISTS
itcast_dw
.dim_org
;
CREATE TABLEitcast_dw
.dim_org
(
orgId
bigint,
orgname
string,
parentId
bigint,
orglevele
bigint,
managercode
string,
createtime
string,
updatetime
string,
orgtype
bigint)
PARTITIONED BY (dt
string)
STORED AS PARQUET;
dim_shops
– 创建dw层商铺维度表
DROP TABLE IF EXISTSitcast_dw
.dim_shops
;
CREATE TABLEitcast_dw
.dim_shops
(
shopid
bigint,
areaid
bigint,
shopname
string,
servicestarttime
bigint,
serviceendtime
bigint,
shopstatus
bigint,
bdcode
string)
partitioned by (dt string)
STORED AS PARQUET;
2.4 ods层数据至dw层
fact_orders
导入ods层 2019年09月09日的订单数据到 20190908 分区
导入数据后使用 hive/beeline确认数据是否正确映射
INSERT OVERWRITE TABLE
itcast_dw
.fact_orders
PARTITION (dt=‘20190908’)
SELECT
orderid,
orderno,
userid,
orderstatus,
goodsmoney,
delivertype,
delivermoney,
totalmoney,
realtotalmoney,
paytype,
ispay,
areaid,
areaidpath,
orderscore,
isinvoice,
invoiceclient,
orderremarks,
ordersrc,
needpay,
isrefund,
isclosed,
receivetime,
deliverytime,
tradeno,
createtime,
commissionfee,
scoremoney,
usescore,
noticedeliver,
lockcashmoney,
paytime,
isbatch,
totalpayfee
FROMitcast_ods
.itcast_orders
WHERE dt=‘20190908’;
测试:
select * from itcast_dw.fact_orders limit 5;
dim_goods
将商品表的所有数据加载到 商品维度表
参考代码:
– 测试
select * from
itcast_dw
.dim_goods
where dw_end_date = ‘9999-12-31’ limit 5;
dim_goods_cats
分别将 ods 层的商品分类数据导入到 dw层的 dim_goods_cats