订单分析地域、分类维度分析业务开发

2 订单分析地域、分类维度分析业务开发

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='20190909')
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`