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

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 的不同分区中
注意分区字段为
odt （20190905）
ocatlvel (1、2、3)
参考代码：
– 3级分类

INSERT OVERWRITE TABLE itcast_dw.dim_goods_cats PARTITION (dt=‘20190908’,catlevel=‘3’)
SELECT
catid,
catname,
parentid,
isshow,
isfloor,
createtime
FROM itcast_ods.itcast_goods_cats WHERE cat_level=3 and dt=‘20190908’;

– 2级分类

INSERT OVERWRITE TABLE itcast_dw.dim_goods_cats PARTITION (dt=‘20190908’,catlevel=‘2’)
SELECT
catid,
catname,
parentid,
isshow,
isfloor,
createtime
FROM itcast_ods.itcast_goods_cats WHERE cat_level=2 and dt=‘20190908’;

– 1 级分类

INSERT OVERWRITE TABLE itcast_dw.dim_goods_cats PARTITION (dt=‘20190908’,catlevel=‘1’)
SELECT
catid,
catname,
parentid,
isshow,
isfloor,
createtime
FROM itcast_ods.itcast_goods_cats WHERE cat_level=1 and dt=‘20190908’;

– 测试

select * from itcast_ods.itcast_goods_cats WHERE cat_level=1 and dt=‘20190908’ limit 5;

select * from itcast_ods.itcast_goods_cats WHERE cat_level=2 and dt=‘20190908’ limit 5;

select * from itcast_ods.itcast_goods_cats WHERE cat_level=3 and dt=‘20190908’ limit 5;

dim_shops
将 ods层的 itcast_shops 数据导入到 dim_shops 的 20190908分区中
参考代码：

INSERT OVERWRITE TABLE itcast_dw.dim_shops PARTITION (dt=‘20190908’)
SELECT
shopid,
areaid,
shopname,
servicestarttime,
serviceendtime,
shopstatus,
bdcode
FROM itcast_ods.itcast_shops
where dt=‘20190908’;

– 测试

select * from itcast_dw.dim_shops where dt=‘20190908’ limit 5;

dim_org
将 ods 层 itcast_org 导入到 dw层中

INSERT OVERWRITE TABLE itcast_dw.dim_org PARTITION (dt=‘20190908’)
SELECT
orgid,
orgname,
parentid,
orglevel,
managercode,
createtime,
updatetime,
orgtype
FROM itcast_ods.itcast_org
WHERE dt = ‘20190908’;

– 测试

select * from itcast_dw.dim_org where dt = ‘20190908’ limit 5;

2.5 dw层数据维度拉宽
数据表拉宽
1、店铺维度数据拉宽
店铺表(itcast_shops)与组织机构表(itcast_org)进行关联
在这里插入图片描述
dim_shop

– 创建dw层商铺维度表
DROP TABLE IF EXISTS itcast_dw.dim_shop;
– 1. 店铺维度表
create table if not exists itcast_dw.dim_shop(
shop_id string, – 店铺id,
shop_name string, – 店铺名称
city_id string, --城市组织机构id
city_name string, --城市组织机构名称
region_id string, --区域组织机构id
region_name string --区域组织机构名称
)
partitioned by (dt string)

参考代码：

– 2. 加载订单组织机构维度表数据
insert overwrite table itcast_dw.dim_shop partition(dt=‘20190908’)
select
t1.shopid as shop_id, – 店铺id
t1.shopname as shop_name, – 店铺名称
t2.orgid as city_id, – 城市组织机构id
t2.orgname as city_name, – 城市组织机构名称
t3.orgid as region_id, – 区域组织机构id
t3.orgname as region_name – 区域组织机构名称
from
(select shopid, shopname, areaid from itcast_ods.itcast_shops where dt=‘20190908’) t1 – 店铺数据
left join
(select orgid, parentid, orgname, orglevel from itcast_ods.itcast_org where orglevel=2 and dt=‘20190908’) t2 – 城市组织机构数据
on t1.areaid = t2.orgid
left join
(select orgid, parentid, orgname, orglevel from itcast_ods.itcast_org where orglevel=1 and dt=‘20190908’) t3 – 省份组织机构数据
on t2.parentid = t3.orgid;

– 测试数据

select * from itcast_dw.dim_shop limit 10;

2、商品分类维度数据拉宽
dim_goods_cat
商品表维度数据拉宽
参考代码：
在这里插入图片描述

– 表创建
DROP TABLE IF EXISTS itcast_dw.dim_goods_cat;
create table if not exists itcast_dw.dim_goods_cat(
cat_3d_id string, – 三级商品分类id
cat_3d_name string, – 三级商品分类名称
cat_2d_id string, – 二级商品分类Id
cat_2d_name string, – 二级商品分类名称
cat_1t_id string, – 一级商品分类id
cat_1t_name string – 一级商品分类名称
)
partitioned by (dt string)
STORED AS PARQUET;

insert overwrite table itcast_dw.dim_goods_cat partition(dt=‘20190908’)
select
t3.catid as cat_3d_id, – 三级分类id
t3.catname as cat_3d_name, – 三级分类名称
t2.catid as cat_2d_id, – 二级分类id
t2.catname as cat_2d_name, – 二级分类名称
t1.catid as cat_1t_id, – 一级分类id
t1.catname as cat_1t_name – 一级分类名称
from
(select catid, catname, parentid from itcast_ods.itcast_goods_cats where cat_level=3 and dt=‘20190908’) t3 – 商品三级分类数据
left join
(select catid, catname, parentid from itcast_ods.itcast_goods_cats where cat_level=2 and dt=‘20190908’) t2 – 商品二级分类数据
on t3.parentid = t2.catid
left join
(select catid, catname, parentid from itcast_ods.itcast_goods_cats where cat_level=1 and dt=‘20190908’) t1 – 商品一级分类数据
on t2.parentid = t1.catid;

– 测试数据

select * from itcast_dw.dim_goods_cat limit 5;

3、支付方式维度表拉宽
dim_payment
在这里插入图片描述
0—其他
1—支付宝
2—微信
3—现金
41—额支付
42—货到付款

– 三、支付方式维度数据拉宽

– 1. 创建支付方式维度表
create table if not exists itcast_dw.dim_payment(
payment_id string, – 支付方式id
payment_name string – 支付方式名称
)
partitioned by (dt string)
STORED AS PARQUET;

– 2. 加载支付方式维度数据
– 需要额外添加一行数据 0 -> 其他
insert overwrite table itcast_dw.dim_payment partition(dt=‘20190908’)
select
t1.id as payment_id, – 支付方式id
t1.payName as payment_name – 支付方式名称
from
(select id, payName from itcast_ods.itcast_payments where dt=‘20190908’) t1
union
select
‘0’ as payment_id,
‘其他’ as payment_name;

– 测试查询支付方式维度数据

select * from itcast_dw.dim_payment limit 5;

4、事实表拉宽
fact_order_goods1

– 1. 创建订单明细事实表
create table if not exists itcast_dw.fact_order_goods1(
order_id string, – 订单id
goods_cat_3d_id string, – 商品三级分类id
shop_id string, – 店铺id
payment_id string, – 订单支付方式
goods_num bigint, – 商品数量
pay_money double, – 订单明细金额
paytime string – 订单时间
)
partitioned by (dt string)
STORED AS PARQUET;

–支付方式,0:未知;1:支付宝，2：微信;3、现金；4、其他

– 2. 拉宽订单明细事实表

insert overwrite table itcast_dw.fact_order_goods1 partition(dt=‘20190908’)
select
t1.orderid as order_id,
t3.goodscatid as goods_cat_3d_id,
t3.shopid as shop_id,
t1.paytype as payment_id,
t2.goodsnum as goods_num,
t2.payprice as pay_money,
t1.paytime as paytime
from
(select orderid, paytype, paytime from itcast_ods.itcast_orders where dt=‘20190908’) t1 – 订单表数据
left join
(select orderid, goodsid, goodsnum, payprice from itcast_ods.itcast_order_goods where dt=‘20190908’) t2 – 订单明细数
on t1.orderid = t2.orderid
left join
(select goodsid, shopid, goodscatid from itcast_dw.dim_goods where dw_end_date = ‘9999-12-31’) t3 – 商品数量
on t2.goodsid = t3.goodsid;

– 测试数据

select * from itcast_dw.fact_order_goods1 limit 5;

指标计算

– 指标统计
– 创建 ads 层结果表
create table if not exists itcast_ads.ads_trade_order(
area_type string, – 区域范围：区域类型（全国、大区、城市）
region_name string, – 区域名称
city_name string, – 城市名称
category_type string, --分类类型（一级、二级）
category_1st_name string, – 一级分类名称
category_2d_name string, – 二级分类名称
payment_name string, – 支付方式（所有、微信、支付宝、…）
total_count bigint, – 订单数量
total_goods_num bigint, – 商品数量
total_money double – 支付金额
)
partitioned by (dt string)
row format delimited fields terminated by ‘\001’ stored as TEXTFILE;

5.1、新交易详情表(tmp_order_goods)
订单表(fact_orders)与订单明细表详情(fact_order_goods)进行关联，组成新交易详情表(tmp_order_goods)，在原交易详情表基础上增加交易类型
在这里插入图片描述
参考代码：

DROP TABLE IF EXISTS itcast_dw.tmp_order_goods;
CREATE TABLE itcast_dw.tmp_order_goods(
ogid bigint,
orderid bigint,
goodsnum bigint,
goodsprice double,
goodsid bigint,
paytype tinyint)
PARTITIONED BY (dt string)
STORED AS PARQUET;

INSERT OVERWRITE TABLE itcast_dw.tmp_order_goods PARTITION (dt=‘20190908’)
SELECT
og.ogid, --订单明细表id
og.orderid,–订单id
og.goodsnum,–商品数量
og.goodsprice,–商品单价
og.goodsid,–商品id
orders.paytype --支付方式
FROM
(SELECT orderid,paytype FROM itcast_dw.fact_orders WHERE dt=‘20190908’ ) orders
LEFT JOIN
(SELECT ogid,orderid,goodsnum,goodsprice,goodsid FROM itcast_dw.fact_order_goods WHERE dt=‘20190908’ ) og
ON orders.orderid=og.orderid;

– 验证结果

SELECT * FROM itcast_dw.tmp_order_goods WHERE dt=‘20190908’ LIMIT 10;

5.2、新交易门店表(tmp_order_goods_shop)
新订单明细详情(tmp_order_goods)与商品表(dim_goods）、店铺表（dim_shops）进行关联，组成新交易门店表(tmp_order_goods_shop),在新订单交易详情基础上，增加门店相关信息与商品信息。
2.1 创建 tmp_order_goods_shop表
2.2 导入数据
关联 dim_goods 表
关联 dim_shops 表
参考代码：
在这里插入图片描述
– 表创建

DROP TABLE IF EXISTS itcast_dw.tmp_order_goods_shop;
CREATE TABLE itcast_dw.tmp_order_goods_shop(
ogid bigint,
orderid bigint,
shopid bigint,
goodsCatId bigint,
cityid bigint,
goodsnum bigint,
goodsprice double,
goodsid bigint,
paytype tinyint)
PARTITIONED BY (dt string)
STORED AS PARQUET;

– 插入数据

INSERT OVERWRITE TABLE itcast_dw.tmp_order_goods_shop PARTITION (dt=‘20190908’)
SELECT
tmp.ogid,
tmp.orderid,
goods.shopid,
goods.goodscatId,
shops.areaid,
tmp.goodsnum,
tmp.goodsprice,
tmp.goodsid,
tmp.paytype
FROM
(SELECT ogid,orderid,goodsnum,goodsprice,goodsid,paytype FROM itcast_dw.tmp_order_goods WHERE dt=‘20190908’) tmp
LEFT JOIN
(SELECT goodsid,shopid,goodsCatId FROM itcast_dw.dim_goods WHERE dw_end_date = ‘9999-12-31’ ) goods ON goods.goodsid=tmp.goodsid
LEFT JOIN
(SELECT shopid,areaid FROM itcast_dw.dim_shops WHERE dt=‘20190908’) shops ON shops.shopid=goods.shopid;

– 数据验证

SELECT * FROM itcast_dw.tmp_order_goods_shop LIMIT 10;

5.3、新交易信息宽表（tmp_order_goods_cat_org)(依赖2.4)
新交易门店表(tmp_order_goods_shop)与商品分类表和组织架构表进行关联，组成新交易信息宽表（tmp_order_goods_cat_org)，在新交易门店表基础上增加
3.1 创建新交易门店表tmp_order_goods_cat_org
3.2 编写SQL连接 dim_goods_cats 的一级分类
3.2 编写SQL连接 dim_goods_cats 的二级分类
3.3 编写SQL连接 dim_goods_cats 的三级分类
3.4 编写SQL连接dim_org的销售部门（orgType=2）
商品分类信息与区域维度信息
在这里插入图片描述
– 创建表

DROP TABLE IF EXISTS itcast_dw.tmp_order_goods_cat_org;
CREATE TABLE itcast_dw.tmp_order_goods_cat_org(
ogid bigint,
orderid bigint,
shopid bigint,
firstcat bigint,
secondcat bigint,
thirdcat bigint,
cityid bigint,
regionid bigint,
goodsnum bigint,
goodsprice double,
goodsid bigint,
paytype tinyint)
PARTITIONED BY (dt string)
STORED AS PARQUET;

– 插入数据
INSERT OVERWRITE TABLE itcast_dw.tmp_order_goods_cat_org PARTITION (dt=‘20190908’)
SELECT
tmp.ogid,
tmp.orderid,
tmp.shopid,
scats.parentId AS firstcat,
tcats.parentId AS secondcat,
tmp.goodsCatId AS thirdcat,
tmp.cityid,
org.parentid AS regionId,
tmp.goodsnum,
tmp.goodsprice,
tmp.goodsid,
tmp.paytype
FROM
(SELECT ogid,orderid,shopid,goodsCatId,cityid,goodsnum,goodsprice,goodsid,paytype FROM itcast_dw.tmp_order_goods_shop WHERE dt=‘20190908’ ) tmp
LEFT JOIN (SELECT orgid,parentid FROM itcast_dw.dim_org WHERE dt=‘20190908’ AND orgtype=2 ) org ON org.orgid=tmp.cityid
LEFT JOIN (SELECT catId,parentId FROM itcast_dw.dim_goods_cats WHERE dt=‘20190908’ AND catlevel=3 ) tcats ON tcats.catId=tmp.goodsCatId
LEFT JOIN (SELECT catId,parentId FROM itcast_dw.dim_goods_cats WHERE dt=‘20190908’ AND catlevel=2 ) scats ON scats.catId=tcats.parentId;

– 验证结果

SELECT * FROM itcast_dw.tmp_order_goods_cat_org LIMIT 10;

2.6 指标计算汇总计算
到目前为止，新交易信息宽表（tmp_order_goods_cat_org)已经包含：
区域维度数据
商品分类维度数据。
根据需求，需要依据这两个维度进行汇总。最终在业务系统中展示如下：
公司高管拥有查询所有数据的权限，省份不选择时，看到的为全国范围内的数据，如果指定省份（本次开发的系统为大区）时，看到的为指定省份数据，如果指定某个城市，则看到的是指定城市的数据，同理商品分类维度类似推算。
目前新交易信息宽表（tmp_order_goods_cat_org)输入如下：
在这里插入图片描述
由于区域维度为分级维度，分为全国、大区、城市三级。通常情况下，我们会计算出城市维度，然后对城市维度数据进行汇总计算得出大区维度和全国维度数据，实际上，由于一个订单，由于会有多个商品，这些商品属于不同的商店，这些商店又属于不同的区域，故不能简单累加计算。
在这里插入图片描述
根据此订单信息分析，orderid为532的订单，实际上仅为一个订单，该订单共计3件商品，分别属于不同的商店，这些商店分属北京、石家庄、上海城市。
在区域维度计算时。精确到城市维度数据计算应该如下：
在这里插入图片描述
精确到大区维度数据计算应该如下：

精确到全国维度数据计算时结果数据应该如下：

全国、无商品分类维度的交易信息
由于需求中需要列出支付宝支付笔数，微信交易笔数与总交易笔数，根据数据信息，支付共计分为支付宝交易、微信交易、现金交易、其他交易和未知交易类型(支付方式,0:未知;1:支付宝，2：微信;3、现金；4、其他)
故每个维度的数据计算时，除按照支付类型进行分组获取各种支付类型支付的数据外，还要获取不分任何类型的数据。
需求：
1、获取全国、无商品分类维度的分交易类型数据
2、获取全国、无商品分类维度的不分交易类型的数据
注意事项：
第一个INSERT导入数据到数据集市，使用overwrite，第二个则应该使用into
计算如下:
– 获取全国、无商品分类维度的分交易类型数据

INSERT OVERWRITE TABLE itcast_ads.ads_trade_order PARTITION (dt=‘20190908’)
SELECT
‘0’, --全国大区–x 城市—y
‘9999’,
‘0’, --无商品分类和各个级别 1 2 3
‘9999’,
paytype,
count(distinct orderid),
sum(goodsnum*goodsprice)
FROM itcast_dw.tmp_order_goods_cat_org WHERE dt=‘20190908’
GROUP BY paytype;

– 获取全国、无商品分类维度的不分交易类型的数据

INSERT into TABLE itcast_ads.ads_trade_order PARTITION (dt=‘20190908’)
SELECT
‘0’,
‘9999’,
‘0’,
‘9999’,
‘9’, – 不分交易类型
count(distinct orderid),
sum(goodsprice*goodsprice)
FROM itcast_dw.tmp_order_goods_cat_org WHERE dt=‘20190908’;

– 测试

select * from itcast_ads.ads_trade_order limit 5;

全国、一级商品分类维度交易信息
需求：
1、获取全国、一级商品分类维度的分交易类型数据
2、获取全国、一级商品分类维度的不分交易类型数据

– 获取全国、一级商品分类维度的分交易类型数据
INSERT into TABLE itcast_ads.ads_trade_order PARTITION (dt=‘20190908’)
SELECT
‘0’,
‘9999’,
‘1’,
firstcat,
paytype,
count(distinct orderid),
sum(goodsprice*goodsprice)
FROM itcast_dw.tmp_order_goods_cat_org WHERE dt=‘20190908’
GROUP BY paytype,firstcat;

– 获取全国、一级商品分类维度的不分交易类型数据

INSERT into TABLE itcast_ads.ads_trade_order PARTITION (dt=‘20190908’)
SELECT
‘0’,
‘9999’,
‘1’,
firstcat,
‘9’,
count(distinct orderid),
sum(goodsprice*goodsprice)
FROM itcast_dw.tmp_order_goods_cat_org WHERE dt=‘20190908’
GROUP BY firstcat;

– 测试

select * from itcast_ads.ads_trade_orderlimit 5;

全国、二级商品分类维度交易信息
需求：
1、获取全国、二级商品分类维度的分交易类型数据
2、获取全国、二级商品不分类维度的分交易类型数据
– 获取全国、二级商品分类维度的分交易类型数据

INSERT into TABLE itcast_ads.ads_trade_order PARTITION (dt=‘20190908’)
SELECT
‘0’,
‘9999’,
‘2’,
secondcat,
paytype,
count(distinct orderid),
sum(goodsprice*goodsprice)
FROM itcast_dw.tmp_order_goods_cat_org WHERE dt=‘20190908’
GROUP BY paytype,secondcat;

– 获取全国、二级商品不分类维度的不分交易类型数据

INSERT into TABLE itcast_ads.ads_trade_order PARTITION (dt=‘20190908’)
SELECT
‘0’,
‘9999’,
‘2’,
secondcat,
‘9’,
count(distinct orderid),
sum(goodsprice*goodsprice)
FROM itcast_dw.tmp_order_goods_cat_org WHERE dt=‘20190908’
GROUP BY secondcat;

大区、无商品分类维度的交易信息
需求：
1、获取大区、无商品分类维度的分交易类型数据
2、获取大区、不分商品不分类维度的不分交易类型数据
参考代码：
– 获取大区、无商品分类维度的分交易类型数据

INSERT into TABLE itcast_ads.ads_trade_order PARTITION (dt=‘20190908’)
SELECT
‘1’, – 1 大区
regionid,
‘0’,
‘9999’,
paytype,
count(distinct orderid),
sum(goodsprice*goodsprice)
FROM itcast_dw.tmp_order_goods_cat_org WHERE dt=‘20190908’
GROUP BY paytype,regionid;

– 获取大区、不分商品不分类维度的不分交易类型数据

INSERT into TABLE itcast_ads.ads_trade_order PARTITION (dt=‘20190908’)
SELECT
‘0’,
‘9999’,
‘1’,
regionid,
‘9’,
count(distinct orderid),
sum(goodsprice*goodsprice)
FROM itcast_dw.tmp_order_goods_cat_org WHERE dt=‘20190908’
GROUP BY regionid;

大区、一级商品分类维度交易信息
1、获取大区、一级商品分类维度的分交易类型数据
2、获取大区、一级商品分类维度的不分交易类型数据
参考代码：
– 获取大区、一级商品分类维度的分交易类型数据

INSERT into TABLE itcast_ads.ads_trade_order PARTITION (dt=‘20190908’)
SELECT
‘1’,
regionid,
‘1’,
firstcat,
paytype,
count(distinct orderid),
sum(goodsprice*goodsprice)
FROM itcast_dw.tmp_order_goods_cat_org WHERE dt=‘20190908’
GROUP BY paytype,firstcat,regionid;

– 获取大区、一级商品分类维度的不分交易类型数据

INSERT into TABLE itcast_ads.ads_trade_order PARTITION (dt=‘20190908’)
SELECT
‘1’,
regionid,
‘1’,
firstcat,
‘9’,
count(distinct orderid),
sum(goodsprice*goodsprice)
FROM itcast_dw.tmp_order_goods_cat_org WHERE dt=‘20190908’
GROUP BY firstcat,regionid;

大区、二级商品分类维度交易信息
1、获取大区、二级商品分类维度的分交易类型数据
2、获取大区、二级商品分类维度的不分交易类型数据
参考代码：
– 获取大区、二级商品分类维度的分交易类型数据

INSERT into TABLE itcast_ads.ads_trade_order PARTITION (dt=‘20190908’)
SELECT
‘1’,
regionid,
‘2’,
secondcat,
paytype,
count(distinct orderid),
sum(goodsprice*goodsprice)
FROM itcast_dw.tmp_order_goods_cat_org WHERE dt=‘20190908’
GROUP BY paytype,secondcat,regionid;

– 获取大区、二级商品分类维度的不分交易类型数据

INSERT into TABLE itcast_ads.ads_trade_order PARTITION (dt=‘20190908’)
SELECT
‘1’,
regionid,
‘2’,
secondcat,
‘9’,
count(distinct orderid),
sum(goodsprice*goodsprice)
FROM itcast_dw.tmp_order_goods_cat_org WHERE dt=‘20190908’
GROUP BY secondcat,regionid;

城市、无商品分类维度交易信息
1、获取城市、无商品分类维度的分交易类型数据
2、获取城市、无商品分类维度的不分交易类型数据
参考代码：
– 获取城市、无商品分类维度的分交易类型数据

INSERT into TABLE itcast_ads.ads_trade_order PARTITION (dt=‘20190908’)
SELECT
‘2’,
cityid,
‘0’,
‘9999’,
paytype,
count(distinct orderid),
sum(goodsprice*goodsprice)
FROM itcast_dw.tmp_order_goods_cat_org WHERE dt=‘20190908’
GROUP BY paytype,cityid;

– 获取城市、无商品分类维度的不分交易类型数据

INSERT into TABLE itcast_ads.ads_trade_order PARTITION (dt=‘20190908’)
SELECT
‘2’,
cityid,
‘0’,
‘9999’,
‘9’,
count(distinct orderid),
sum(goodsprice*goodsprice)
FROM itcast_dw.tmp_order_goods_cat_org WHERE dt=‘20190908’
GROUP BY cityid;

城市、一级商品分类维度交易信息
1、获取城市、一级商品分类维度的不分交易类型数据
2、获取城市、一级商品分类维度的分交易类型数据
参考代码：
– 获取城市、一级商品分类维度的分交易类型数据

INSERT into TABLE itcast_ads.ads_trade_order PARTITION (dt=‘20190908’)
SELECT
‘2’,
cityid,
‘1’,
firstcat,
paytype,
count(distinct orderid),
sum(goodsprice*goodsprice)
FROM itcast_dw.tmp_order_goods_cat_org WHERE dt=‘20190908’
GROUP BY paytype,firstcat,cityid;

– 获取城市、一级商品分类维度的不分交易类型数据

INSERT into TABLE itcast_ads.ads_trade_order PARTITION (dt=‘20190908’)
SELECT
‘2’,
cityid,
‘1’,
firstcat,
‘9’,
count(distinct orderid),
sum(goodsprice*goodsprice)
FROM itcast_dw.tmp_order_goods_cat_org WHERE dt=‘20190908’
GROUP BY firstcat,cityid;

城市、二级商品分类维度交易信息
1、获取城市、二级商品分类维度的不分交易类型数据
2、获取城市、二级商品分类维度的分交易类型数据
参考代码：
– 获取城市、二级商品分类维度的分交易类型数据

INSERT into TABLE itcast_ads.ads_trade_order PARTITION (dt=‘20190908’)
SELECT
‘2’, – 2 代表城市
cityid,
‘2’,
secondcat,
paytype,
count(distinct orderid),
sum(goodsprice*goodsprice)
FROM itcast_dw.tmp_order_goods_cat_org WHERE dt=‘20190908’
GROUP BY paytype, secondcat,cityid;

– 获取城市、二级商品分类维度的不分交易类型数据

INSERT into TABLE itcast_ads.ads_trade_order PARTITION (dt=‘20190908’)
SELECT
‘2’,
cityid,
‘2’,
secondcat,
‘9’,
count(distinct orderid),
sum(goodsprice*goodsprice)
FROM itcast_dw.tmp_order_goods_cat_org WHERE dt=‘20190908’
GROUP BY secondcat,cityid;

查看全文
如若内容造成侵权/违法违规/事实不符，请联系编程学习网邮箱：809451989@qq.com进行投诉反馈，一经查实，立即删除！

Python Web 框架：Django、Flask 与 Tornado 的性能对比
本文的数据涉及到我面试时遇到过的问题，大概一次 http 请求到收到响应需要多少时间。这个问题在实际工作中与框架有比较大的关系，因此特别就框架的性能做了一次分析。这里使用 2016 年 6 月 9 日的报告数据： Pythons Web Framework Benchmarks。本文仅关注目前最常用的三大 …...
2024/4/24 10:57:07
idea从git上克隆项目之后pom文件无效问题
idea从git上克隆项目之后pom文件无效问题在自己的idea从git上拉区项目的时候，经常第一次会克隆下来的项目会报pom文件显示不是maven形式：如下图这是因为从git上下下来项目之后，maven没设置。依次打开File—>setting—>Buid,Exception,Deployment—>Build Tools—&…...
2024/4/24 10:57:06
java去掉json字符串中双引号
1.去掉字符串中的空格2.去掉字符双引号字符串{"fBNR":"<p style="text-align: center;"><span style="color: rgb(85, 85, 85); font-family: 仿宋; text-align: center; background-color: rgb(255, 255, 255);">——市水务系…...
2024/4/24 10:57:06
SpringBoot初体验之idea新建项目
目录新建项目为啥没有官网下载安装重启 idea出现了新建项目点击新建，怎么没有Spring Initializr，那我咋新建？我的图片教程图片为啥没有查了一下资料，原来这玩意得先去下载一个插件。实际上我觉得这种工具类的东西，让人更加方便了，但是也更加愚笨了，按照道理，就算用…...
2024/4/24 10:57:03
由防火墙导致的数据库空闲连接断开问题
目录一、问题描述二、问题逐步分析1、java.net.SocketException: Broken pipe异常是怎么产生的？有什么影响？2、为什么数据库TCP连接会被断开？3、防火墙切断数据库连接会造成的影响三、解决方法1、调大防火墙的连接切断时长2、tcp keepalive功能3、Oracle数据库的DCD4、程序不…...
2024/4/24 10:57:06
机器视觉-相机镜头光源介绍及选型-照明方式
照明方式 ===================================================================================== 1.照明分类：打光方式1.1.直接照明：高角度打光(明视场)定义：光直接射向物体，得到清楚的影像。高角度打光,光线方向与检测面相对垂直,表面平整部位反光相对容易进入镜头之…...
2024/4/24 10:57:04
机器视觉-相机镜头光源介绍及选型-光源分类
光源分类 ===================================================================================== 1.1.颜色白色光源(W)白色光源适用性广,亮度高,特别是拍摄彩色图像时使用更多.蓝色光源(B) 波光425-480适用产品：银色背景产品(如钣金,车加工件等),薄膜上金属印刷…...
2024/5/8 18:37:58
安翰科技助力消化内镜医师适宜技术与科学方案让内镜医生安心诊疗
中国工程院院士陈香美在2020年两会上提出“护医”建议，在新冠肺炎疫情这场没有硝烟的战役中，医护人员起到决定性作用。疫情之后，我们要把感动变为尊重，把对医护人员的尊崇扎下根来。如何提高医生群体的获得感，不仅需要制度改革，更需要创新技术来减轻医生群体的心理压力与…...
2024/5/3 6:30:00
HR：我好不容易招来的人，你们一个Redis就问回去了，有那么难吗？
概念缓存穿透在高并发下，查询一个不存在的值时，缓存不会被命中，导致大量请求直接落到数据库上，如活动系统里面查询一个不存在的活动。缓存击穿在高并发下，对一个特定的值进行查询，但是这个时候缓存正好过期了，缓存没有命中，导致大量请求直接落到数据库上，如活动系统里…...
2024/5/8 20:59:22
C#WPF项目使用图片
1、Xaml文件<Image x:Name="isLineImage" Grid.Column="2" HorizontalAlignment="Left" Height="30" Margin="123,23,0,0" VerticalAlignment="Top" Width="34"/>2、CS文件：try{string projpath…...
2024/4/25 21:19:01
使用post方式代替window.location.href
window.location.href可以简单粗暴的实现文件下载，Excel导出等。但是该方式传参数有着太多不安全因素，例如：将一些重要信息暴露给地址栏亦或者是由于参数过长导致无法访问等等。以下是使用post代替window.location.href的实现，大体思路是将参数封装成一个隐藏的Form，然后f…...
2024/4/24 10:56:56
SRfeat论文理解
问题：这种基于gan的方法的结果往往包含与输入图像无关的、没有意义的高频噪声。解决：判别器鼓励发生器产生结构高频特征；生成器使用跳过连接。两种判别器：一个图像判别器、一个特征判别器。基于结构分量。我们提出了一种新的具有远程跳跃连接的SISR发生器。两个亚像素卷积…...
2024/4/15 5:24:57
Markdown学习
Markdown学习标题: #＋空格＋标题名称（一级标题） ##＋空格+标题名称（二级标题） #的数量代表几级标题，最多支持六级标题字体 hello world（字体两边加两个**变成黑体） hello world（字体两边加一个*变成斜体） hello world（字体两边加三个*变成斜体加黑体） hello worl…...
2024/4/15 5:24:56
MYSQL 问题之修改密码 Unknown column password in field list
出现Unknown column password in field list这个问题是由于新版本的mysql采用了authentication_string来替代password字段将语句修改为 update mysql.user set authentication_string=password(root) where user=root ; 即可。...
2024/5/6 3:02:51
LeetCode1.两数之和：给定一个整数数组 nums 和一个目标值 target，请你在该数组中找出和为目标值的那两个整数，返回数组下标。假设每种输入只对应一个答案。但数组中同一个元素不能使用两遍
c++ 实现LeetCode 1.两数之和（难度：简单）新的改变今天第一次尝试写博客，第一次在leetcode上做题，希望能坚持下去：要求：给定 nums = [2, 7, 11, 15], target = 9 因为 nums[0] + nums[1] = 2 + 7 = 9 所以返回 [0, 1] 来源：力扣（LeetCode）链接：https://leetcode-…...
2024/4/15 5:24:54
美化绿植问题
【题目描述】计信学院门前摆了一排盆栽的绿植，高度各不相同，共有n（0<n<100）盆。五一节到了，为了美化，现在要搬走其中的n-m盆，使得剩下的m盆绿植相对位置不变，但高度是递增的，而且留下的绿植盆数最多。【输入】每一行一个整数，表示n的值；第二行是n个整数，分…...
2024/4/24 10:56:55
前端预加载gif
内容描述待补充~ <div id="Header":style="`height:${H};`"class="Header"><img id="headersBgGif"data-up="https://f2.kkmh.com/20200417tm.gif"src="https://f2.kkmh.com/2…...
2024/4/24 10:57:01
Spark环境配置与安装
目前，CSDN博文搜索有时候看不到博文发表时间，如果以后能像百度学术那样有搜索的时间范围选项不知道会不会更好一点。虽说百度也收录了CSDN博文，可以搜到。前提，官网说明要先安装配置好java8或者java11。此处，博主安装在已经配置好Hadoop伪分布的虚拟机Linux上，Hadoop2.…...
2024/4/24 2:45:52
剑指Offer面试题06从头到尾打印链表
面试题06：从头到尾打印链表题目思路一思路二题目输入一个链表的头节点，从尾到头反过来返回每个节点的值（用数组返回）。示例 1：输入：head = [1,3,2] 输出：[2,3,1]限制：0 <= 链表长度 <= 10000思路一利用栈先进后出的特点，依次push()链表的值，然后再依次从栈…...
2024/4/24 10:56:55
git 本地仓库、远程仓库的创建、上传
备注：gittest为虚拟项目，xiaoming为虚拟账户远程仓库地址：https://gitee.com/xiaoming/gittest.git Git全局设置如果第一次使用git，则需要对git进行配置 git config --global user.name "xiaoming" git config --global user.email "xiaoming@163.com&quo…...
2024/4/24 10:56:52

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

相关文章

最新文章