千亿数仓第四章(指标计算_订单分析地域、分类维度分析业务开发)

2订单分析地域、分类维度分析业务开发
总结需求1：
ads数据都来源于dw, DW层数据都来源于ods。 ads一张表存储了18个需求的所有数据
2.1 需求分析
集团总公司分为很多的分公司（销售事业部）

为了公司的经营需要，公司需要定期检查各个分公司的经营指标完成情况，运营部门提出数据分析需求：
交易金额
交易笔数
微信交易笔数
支付宝交易笔数

维度如下：
商品分类
行政区域
要求：最终可以根据不同大区、不同城市、不能分类级别查询交易数据，也就是要求支持不同维度的组合查询。
需求：
获取全国、无商品分类维度的分交易类型数据
获取全国、无商品分类维度的不分交易类型的数据
获取全国、一级商品分类维度的分交易类型数据
获取全国、一级商品分类维度的不分交易类型数据
获取全国、二级商品分类维度的分交易类型数据
获取全国、二级商品不分类维度的分交易类型数据
获取大区、无商品分类维度的分交易类型数据
获取大区、无商品不分类维度的不分交易类型数据
获取大区、一级商品分类维度的分交易类型数据
获取大区、一级商品分类维度的不分交易类型数据
获取大区、二级商品分类维度的分交易类型数据
获取大区、二级商品分类维度的不分交易类型数据
获取城市、无商品分类维度的分交易类型数据
获取城市、无商品分类维度的不分交易类型数据
获取城市、一级商品分类维度的不分交易类型数据
获取城市、一级商品分类维度的分交易类型数据
获取城市、二级商品分类维度的不分交易类型数据
获取城市、二级商品分类维度的分交易类型数据

根据需求整理出用到的表
最终的流程图：

2.2 创建 ads 层数据表

– 创建ads（数据集市层）订单分析表
DROP TABLE IF EXISTS itcast_ads.ads_trade_order;
CREATE TABLE itcast_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 EXISTS itcast_dw.fact_orders;
CREATE TABLE itcast_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 EXISTS itcast_dw.dim_goods_cats;
CREATE TABLE itcast_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 TABLE itcast_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 EXISTS itcast_dw.dim_shops;
CREATE TABLE itcast_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
FROM itcast_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