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

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,