Operational Data Store

  • 知道ODS层的作用
  • 掌握ODS层的四种数据导入方式Master the four data import methods of ODS layer
  • 清晰流利地说出Sqoop的几个常用参数

ODS层作用

负责临时存储数据,和源数据基本保持一致,从而采用orc+zlib

四种数据导入方式

①全量同步 – 每天新增一个日期分区，同步并存储当天的全量数据,历史数据定期删除
②全量覆盖 – 不需要分区，每次同步都是先删后写，直接覆盖
③增量导入 – 每天新增一个日期分区，同步并存储当天的新增数据
④新增及更新同步 – 每天新增一个日期分区，同步并存储当天的新增和更新数据

注意2,3,4咱们使用的比较多

Demo案例

这里connect 后面是单引号,里面直接写jdbc:mysql://192.168.88.80:3306/yipin也是可以的

1
2
3
4
5
6
7
8
/user/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect 'jdbc:mysql://192.168.88.80:3306/yipin?useUnioncode=true&characterEncoding=UTF-8&autoReconnect=true' \
--username root \
--password 123456 \
--query 'select * from t_district where 1=1 and \$CONDITIONS' \
--hcatalog-database yp_ods \
--hcatalog-table t_district \
-m 1

Q: query的使用注意事项

A:query后面是单引号,必须加where条件,必须加$CONDITIONS表示SQL语句结束,因为有query了后面不需要加—table

Q: 为什么要使用hcatalog

A:是因为要导入数据的表是orc存储格式的,一般的导入方式是-cupt,hive-database

常用Sqoop

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- 全量覆盖(演示插入到分区表并使用Oozie自动化调度)
-- 在sh脚本中定义变量 (TD_DATE = \`date -d '1 days ago' "+%Y-%m-%d"\`  -- 这里的\是转义符
/user/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect 'jdbc:mysql://192.168.88.80:3306/yipin?useUnioncode=true&characterEncoding=UTF-8&autoReconnect=true' \
--username root \
--password 123456 \
--query 'select *,'${TD_DATE}' from t_user_login where 1=1 and \$CONDITIONS' \
--hcatalog-database yp_ods \
--hcatalog-table t_user_login \
-m 1

-- 增量导入(仅新增
/user/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect 'jdbc:mysql://192.168.88.80:3306/yipin?useUnioncode=true&characterEncoding=UTF-8&autoReconnect=true' \
--username root \
--password 123456 \
--query 'select *,'${TD_DATE}' from t_user_login where 1=1 and login_time between '${TD_DATE} 00:00:00' and '${TD_DATE} 23:59:59' and \$CONDITIONS' \
--hcatalog-database yp_ods \
--hcatalog-table t_user_login \
-m 1

-- 增量导入(更新 + 新增 
-- 注意这里login_time 和 update_time之间是或or的关系(只要有更新,或者新增都要
/user/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect 'jdbc:mysql://192.168.88.80:3306/yipin?useUnioncode=true&characterEncoding=UTF-8&autoReconnect=true' \
--username root \
--password 123456 \
--query 'select *,'${TD_DATE}' from t_user_login where 1=1 and login_time between '${TD_DATE} 00:00:00' and '${TD_DATE} 23:59:59' or update_time between '${TD_DATE} 00:00:00' and '${TD_DATE} 23:59:59' and \$CONDITIONS' \
--hcatalog-database yp_ods \
--hcatalog-table t_user_login \
-m 1

Data Warehouse Detail

  • 知道DWD层的作用
  • 掌握拉链表作用，拉链表公式，以及拉链表的实现方式(创建临时表)

DWD层作用

负责清洗转换,区分维度表dim_xxx和事实表fact_xxx

食用前必看

3种导入方式:拉链导入(增量及更新) ,全量覆盖导入, 增量导入(instance1,2,3
24张表中,有20张表是拉链表,订单评价表和登录记录表是增量表(增量导入),区域字典表、时间维度表是全量覆盖导入
这里的重点是拉链表-要有start_time 和 end_time这两个字段来维护历史数据 这就是拉链表
拉链表公式: (旧的拉链表 left join 增量信息) union all 增量信息

拉链表Pseudocode

1
2
3
4
5
6
7
8
9
10
11
12
insert overwrite table fact_shop_order_tmp partition(start_date)
select 
	...
	xxx,
	....
	if(增量信息.id=null or 旧的拉链表.end_date='9999-12-31',旧的拉链表.end_date,date_sub(增量信息.start_date-1))
	-- if后的信息判断旧的拉链表的end_date需不需要修改(两种情况不需要修改),需要修改就用增量信息的start_date - 1
from 旧的拉链表 left join 增量信息 on 旧的拉链表.id=增量信息.id
union all
select
	*
from 增量信息;

拉链导入(增量及更新)

订单事实表,循环与拉链导入(因为拉链表是dwd层,所以要用sqoop抽取到ODS层,再到DWD层

增量导入的第一步必定是全量导入

1
2
3
4
5
6
7
8
9
10
insert overwrite table yp_dwd.fact_shop_order PARTITION (start_date)  -- overwrite后必须有table
select 
	id,order_num,buyer_id,
	case order_from  -- 这里算作数据转换,数据的清洗有空值过滤,列值裁剪
		when 1 then 'Android'
		when 2 then 'IOS'.... end as order_from
	....
	'9999-99-99' as end_date,  -- 手动定义end_time
	dt as start_date  -- dt 作为start_time
from yp_ods.t_shop_order;

Step1:增量导入更新+新增,使用sqoop从业务数据库中抽取到ODS层

1
2
3
4
5
6
7
8
/usr/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect 'jdbc:mysql://192.168.88.80:3306/yipin?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true' \
--username root \
--password 123456 \
--query "select *, '2021-11-30' as dt from t_shop_order where 1=1 and (create_time between '2021-11-30 00:00:00' and '2021-11-30 23:59:59') or (update_time between '2021-11-30 00:00:00' and '2021-11-30 23:59:59') and  \$CONDITIONS" \
--hcatalog-database yp_ods \
--hcatalog-table t_shop_order \
-m 1

Step2:创建中间临时表,用于保存拉链结果

1
2
CREATE TABLE yp_dwd.fact_shop_order_tmp like yp_dwd.fact_shop_order;  -- 创建空表
CREATE TABLE fact_shop_order_tmp as select * from CREATE TABLE fact_shop_order; -- 创建有数据的表

Step3:将公式获取的新的拉链表插入到临时表中

1
2
3
4
5
6
7
8
9
10
11
12
insert overwrite table fact_shop_order_tmp partition(start_date)
select 
	...
	xxx,
	....
	if(增量信息.id=null or 旧的拉链表.end_date='9999-12-31',旧的拉链表.end_date,date_sub(增量信息.start_date-1))
	-- if后的信息判断旧的拉链表的end_date需不需要修改(两种情况不需要修改),需要修改就用增量信息的start_date - 1
from 旧的拉链表 left join 增量信息 on 旧的拉链表.id=增量信息.id
union all
select
	*
from 增量信息;

Step4,查询临时表的信息,确认无误后覆盖到旧的拉链表中

全量覆盖导入

1
2
3
insert overwrite table yp_dwd.dim_tistrict
select * from yp_ods.t_district
where code is not null and name is not null;  -- 这里说明了DWD层的数据是经过清洗转换的

增量导入(第一次肯定是全量导入

1
2
3
4
5
insert overwrite table yp_dwd.fact_goods_evaluation partition(dt)
select
	*
from yp_ods.t_goods_evaluation
where dt = '昨天的日期比如2023-06-05'

Data Warehouse Base

  • 知道DWB层的作用
  • 能够写出DWB层的Pseudocode(伪代码)
  • 知道DWB层的重难点在哪里，能够处理join错乱问题

DWB层作用

降维,形成宽表-三张(订单,店铺,商品

三张宽表各有其难点,其实也不算是难点,订单在于多表join,店铺在于自连接join,商品在于join错乱
订单明细宽表 dwb_order_detail
店铺明细宽表 dwb_shop_detail
商品明细宽表 dwb_goods_detail

DWB订单宽表(手撕Pseudocode)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
select 
	xxx
FROM yp_dwd.fact_shop_order o   -- 订单明细表(订单主表)
--订单副表
LEFT JOIN yp_dwd.fact_shop_order_address_detail od on o.id=od.id and od.end_date='9999-99-99'
--订单组
LEFT JOIN yp_dwd.fact_shop_order_group og on og.order_id = o.id and og.end_date='9999-99-99'
--and og.is_pay=1  是否支付的过滤 0未支付 1 已支付
--订单组支付信息
LEFT JOIN yp_dwd.fact_order_pay op ON op.group_id = og.group_id and op.end_date='9999-99-99'
--退款信息
LEFT JOIN yp_dwd.fact_refund_order refund on refund.order_id=o.id and refund.end_date='9999-99-99'
--and refund.refund_state=5  退款状态 5表示退款已经完成
--结算信息
LEFT JOIN yp_dwd.fact_order_settle os on os.order_id = o.id and os.end_date='9999-99-99'
--商品快照
LEFT JOIN yp_dwd.fact_shop_order_goods_details ogoods on ogoods.order_id = o.id and ogoods.end_date='9999-99-99'
--订单评价表
LEFT JOIN yp_dwd.fact_goods_evaluation e on e.order_id=o.id and e.is_valid=1
--订单配送表
LEFT JOIN yp_dwd.fact_order_delievery_item d on d.shop_order_id=o.id and d.dispatcher_order_type=1 and d.is_valid=1
where o.end_date='9999-99-99';

DWB店铺宽表(手撕Pseudocode

1
2
3
4
5
6
7
8
-- 如果这里是left join则必须最子集在最前面(左,
select
	t3.name,
	t2.name,
	t1.name
from dim_tistrict t1  -- t1做县区表
left join dim_tistrict t2 on t1.pid=t2.code  -- t2 做市表
left join dim_tistrict t3 on t2.pid=t3.code  -- t3 做省表

商品明细宽表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 商品分类表dim_goods_class的level有3个值1,2,3分别代表大中小类
-- 总结一句话,大类表中不能有中类和小类字段,即class3中不能有level=1或2
select  -- 这里只提取大中小类字段的名称,不提取id(更多信息详情见之前的blog
	case class1.level  -- 当小类表中level字段为3(小类) 则为正确字段,否则为错误字段
		when 3 then class1.name
		else null end as min_class_name,
	case  -- 当小类表中level字段为2(中类)字段也是可以的,但是大类表中不能有小类(level=1)中类(level=2)字段
		when class1.level=2 then class1.name
		when class2.level=2 then class2.name
		else null end as mid_class_name
	case  -- max_class字段大中小类表中都有
		when class1.level=1 then class1.name
		when class2.level=1 then class2.name
		when class3.level=1 then class3.name
		else null end as max_class_name
from yp_dwd.dim_goods_class class1  -- class1 做小类表
left join dim_goods_class class2 on class1.parent_id=class2.id  -- class2做中类表
left join dim_goods_class class3 on class2.parent_id=class3.id  -- class3做大类

Data Warehouse Service

  • 知道DWS层的作用
  • 掌握DWS层的重点和难点(去重的原理)
  • 掌握该层销售，商品主题使用链式CTE表达式的作用，以及达成的效果

Keys:

  • row_number( ) 窗口函数 + CTE表达式 + where字段=1 进行去重
  • grouping(多个字段) = 十进制,转二进制然后0代表有,1代表没有
  • 金额相关指标-分别按照八个维度进行计算,case 店铺维度 then sum(if(order_rn=1 and 店铺不为空,order_amt,0)),这里注意统计小程序,ios,安卓,pc成交额时 and 后面还要跟order_from=’iOS’
  • 订单量相关指标(八个维度),方法同上

DWS层作用

获取各种主题的日统计宽表,销售,商品,用户宽表

销售主题日统计宽表

去重原理:row_number() over(partition by ) rn CTE 然后where rn=1(根据什么分组就根据什么去重)

pCiZ4m9.png

Stage1:建表

1
2
3
4
5
6
7
create table yp_dws.dws_sale_daycount(
	-- 14个维度字段(城市,商圈,店铺,品牌,大类,中类,小类的id,name)
	-- 分组类型 即属于那种维度组合 group_type
	-- 16个指标字段
)partitioned by (dt string)
row format delimited fields terminated by '\t'
stored as orc tblproperties('orc.compress'='snappy');

Stage2: 向表中插入数据(Pseudocode)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
-- Step3: 用CTE表达式封装
with tmp as (
-- Step1: 梳理表关系,抽取字段
	select
-- 维度字段相关
		o.dt as create_date,  -- 维度字段
	
-- 14个维度字段,城市,商圈,店铺,品牌,大类,中类,小类的id和name

-- 订单相关指标
		order_id,
		goods_id,
	
-- 金额相关指标
		order_amount,
		plat_fee,
		total_price,
		delivery_money,

-- 和判断相关
		order_from,
		gevel_scroes,
		refund_id,
	......
-- Step2: 去重,order_id,goods_id,brand_id, max_class_id,...
		row_number() over(partition by order_id) order_rn,  -- 按照order_id进行去重(一般可以结合
		row_number() over(partition by order_id, goods_id) og_rn  -- 按照订单和商品进行去重
	from yp_dwb.dwb_order_detail o 
	left join yp_dwb.dwb_store_detail s on o.store_id=s.id
	left join yp_dwb.dwb_goods_detail g on o.goods_id=g.id
)
select 
	-- Step4: 具体的维度字段
	-- 14个维度字段,城市,商圈,店铺,品牌,大类,中类,小类 id,和name
	-- 1个分组类型,group_type字段 (使用grouping这里1代表没有,0代表有)
	case 
		grouping(city_id,tread_area_id,store_id,brand_id,max_class_id,mid_class_id,max_class_id)
	when 15 then 'store'
	when 31 then 'tread_area'
	when 63 then 'city' 
	when 119 then 'brand'  -- 64+32+16+7
	when 123 then 'max_class'  -- 64+32+16+8  -- 123+4
	when 125 then 'mid_class'
	when 126 then 'min_class'
-- Step5: 指标计算 金额相关指标(销售收入,平台收入,配送成交额,小程序成交额,安卓,pc,苹果成交额
	-- 销售收入指标 sale_amt(还是按照八个维度来分别统计销售收入)
	case
		when grouping(store_id)=0  -- 日期城市商圈店铺
			-- 这里会对订单进行去重,并且店铺id不为null才会进行统计,否则就为0
			then sum(if(order_rn=1 and store_id is not null,order_amount,0))
		when grouping(tread_area_id)=0  -- 日期城市商圈
			then sum(if(order_rn=1 and tread_area_id is not null,order_amount,0))
		.... -- 剩下的六个维度略
		else null
	end as sale_amt
	-- 安卓成交额(八个维度进行统计)-和上面的不同在于增加了order_from='ios'
	case 
		when grouping(store_id,store_name)=0
			then sum(if(order_rn=1 and store_id is not null and order_from='ios',order_amount,0))
		when 按商圈分组时
			then 对商圈id不为空,且不重复,且来自于ios的订单对订单金额求和,否则为0
		else null
	end as ios_sale_amt
-- Step6: 指标计算(订单量相关)-订单总量,参评,差评,配送,退款,小程序,安卓,ios,pc订单量
	-- 计算订单总量(八个维度进行统计
	case
		when grouping(store_id)=0
			then sum(if(order_rn=1 and store_id is not null,order_amount,0))
		else null
	end as order_cnt
from tmp 
group by
group sets(
-- 总共八种维度进行分组
	(create_date),   -- 按照日期分组
	(create_date,city_id,city_name),  -- 按照日期城市分组
	(create_date,city_id,city_name,tread_area_id,tread_area_name),  -- 日期城市商圈
	(日期,城市,商圈,店铺),
	(日期,品牌),
	(日期,大类),
	(日期,大类,中类),
	(日期,大类,中类,小类)
)

商品主题日统计宽表

Stage1: 建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create table yp_dws.dws_sku_daycount(
	-- 维度字段,日期 + 商品
	dt string,
	sku_id string,
	sku_name string,
	-- 指标字段 15个指标(被下单次数,件数,金额|被支付次数,件数,金额|被退款次数件数金额|加入购物车次数件数|被收藏次数|好中差评)
	-- 3 * 5 = 15
	order_count int, 
	order_num int, 
	order_amout decimal(38,2)  -- 订单金额(注意这里不是数量)
	.......
)comment '商品主题日统计宽表'
row format delimited fields terminated by '\t'
stored as orc tblproperties('orc.compress'='snappy');

Stage2: 往表中插入数据

Q:为什么要用CTE表达式

A:因为要去重然后需要用到row_number(),然后需要用where rn=1来筛选,因为where只能筛选表中已经存在的数据

Q: 数据来源有哪些

A:这里的数据表是来源(dwb,dwd层)

Q:full outer join和union all的区别

pCpEESs.png

Pseudocode

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
-- step1: 准备下单,支付,退款等相关指标所需的基础数据
-- 这里由于发现前面的三组数据都来源于一张表,如果分开写数据会很多?
with order_base as (
	select 
		-- 按照订单id和商品id进行去重
		dt,order_id,goods_id,商品名称,购买商品数量,商品总金额,支付状态,
		row_number() over(partition by order_id,goods_id) as rn 
	from yp_dwb.dwb_order_detail
)
-- step2: 计算 下单次数,件数, 金额指标 - 来源于dwb_order_detail
order_count as (
	select
		dt,goods_id as sku_id,goods_name as sku_name,
		count(order_id) order_count,  -- 被下单次数
		sum(buy_num) order_num,   -- 被下单件数
		sum(total_price) order_amount  -- 被下单金额
	from order_base where rn=1 	-- 对订单进行去重
	group by dt,goods_id,goods_name  -- 因为是维度字段?是因为统计的是每件商品的下单次数吗?
)
-- step3: 计算被支付次数,件数,金额指标(dwb_order_detail
payment_count as (
	select
		dt,goods_id,goods_name,
		count(),sum(),sum()  -- 被支付次数,件数,金额
	from order_base
	where rn=1
	group by dt,goods_id,goods_name
)
-- step4: 计算退款,次数,件数,金额指标(dwb_order_detail
refund_count as (
	select 
		dt,goods_id,goods_name,count(),sum(),sum() -- 退款,次数,件数,金额
	from order_base
	where rn=1
	group by dt,goods_id,goods_name
)
-- step5: 计算 购物车次数,件数(yp_dwd.fact_shop_cart
cart_count as (
	select 
		substring(create_time,1,10) dt,goods_id as sku_id,
		count(id) cart_count,
		sum(buy_num) cart_num
	from yp_dwd.fact_shop_cart  -- 这里到dwd层了,数据血缘跨度有点大啊
	where end_date='9999-99-99'  -- 筛选出最新的数据
	group by substring(create_time,1,10),goods_id  -- 根据日期商品id分组
)
-- step6: 计算 被收藏次数(yp_dwd.fact_goods_collect
favor_count as (
	select 字串提取 as dt,商品id,count(id) as favor_count
	from yp_dwd.fact_goods_collect
	where end_date='9999-99-99'
	group by substring(create_time,1,10),goods_id
)
-- step7: 计算好评中评差评次数(yp_dwd.fact_goods_evaluation_detail
-- 这题不会就去看看38道NorthWind题目
evaluation_count as (
	select 
		count(if(gevel_scroes_goods>=9,1,null)) evaluation_good_count
		....
	from yp_dwd.fact_goods_evaluation_detail
	group by substring(create_time,1,10),goods_id
)
-- step8: 把上述结果合并到一起
union_all as (
	order_count
union all
	payment_count
union all
	refund_count
)
-- step9: 把相同数据(dt, sku_id均相同)的多个指标, 合并到一行
-- 比如, max(zhangsan,null)=zhangsan, sum(0,1)=1
select 
	dt,sku_id,max(sku_name)
	sum(order_count),
	sum(order_num),
	sum(order_amount)
	....
from union_all 
group by dt,sku_id

Data Market

  • 知道DM层的作用

  • 掌握根据DWS层的数据上卷出年月周日的统计数据的方法

  • 能写出销售主题，商品主题的Pseudocode

  • 这里没有什么新的知识点,只是需要将年月周日四个维度和之前的八种维度进行一个笛卡尔积(4*8=32)
  • grouping sets 里面的值变多了,以及增加了time_type字段,用于判断是那个时间维度
  • 指标与DWS层的指标一致

销售主题宽表实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63

-- Stage1: 建表
create table yp_dm.dm_sale(
	dt string comment '统计日期',
	time_type,  -- 日期类型
	年code,年月,月份编码,一月第几天,日期,年中第几周
	group_type,  -- 分组类型
	城市,商圈,店铺,品牌,大类,中类,小类(id,name)
	-- 统计
	销售收入,平台收入,配送成交额,小程序成交额,安卓,苹果,pc成交额,-- 额度
	成交单量,参评单量,差评单量,配送单量,退款单量,小程序,安卓,苹果,pc成交单量  -- 订单量
)
-- 销售主题统计宽表(年月周日)
-- 动作一: 完成八种维度组合情况
-- step1: 梳理表关系
select
	-- step5: 和日期相关的所有维度
	dt,
	year_code,
	year_month,
	month_code,
	day_of_month,
	dim_date_id,
	year_week_name_cnt,
	case  -- 判断是按照什么日期进行的分组
		when grouping(dim_date_id)=0 then 'date'
		.......
	end as time_type,
	
	-- step4: 完成除日期维度外,其他维度的计算
	-- group_type 分组类型
	case
		when grouping(store_id,store_name)=0 then 'store'  -- 日期城市商圈店铺
		when grouping(tread_area_id,tread_area_name)=0 then 'tread_area'  -- 日期城市商圈
		......
		end as group_type
	-- Modify 2: 使用十进制取代取巧版(八个维度)
	case 
		grouping(日期,城市,商圈,店铺,大类,中类,小类)
	when 15 then ...
	when 31 then ....
	when 63 then .....
	-- 14个维度字段
	city_id,city_name,
	城市,商圈,店铺,品牌,大类中类小类(id,name)
	
	-- step3: 完成16个指标计算
	sum(sale_amt) as sale_amt,
	sum(plat_fee) as plat_fee,
	.....
from yp_dws.dws_sale_daycount dc 
left join yp_dwd.dim_date d on dc.dt=d.date_code
group by 
grouping sets(
	-- step2: 年的八种维度
	(year_code),  -- 日期
	(year_code,city_id,city_name),  -- 日期城市
	
	-- Modify1 : 完善日期相关的维度字段
	-- 月的八种维度
	(year_code,month_code,year_month,city_id,city_name)  -- 月+城市维度
	
)

商品主题统计宽表

  • 统计累计下单次数,近30天下单次数,件数,金额-(分为首次统计和循环统计)-循环统计和首次统计不同
  • 这里最重要的思想就是循环计算总累计这种思想(先求出最新一天的累计,然后新的总累计=旧的总累计+最新一天的数据)
  • 循环计算最近30天的累计就是直接计算(通过dt>=today-30即 today-30到today)
  • 这里的重难点是 dt在表中是个string类型的字段,不能和日期类型的直接比较,需要用到cast将date类型转为string类型
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
-- Stage1: 建表
create table yp_dm.dm_sku(
	sku_id,  
	-- 最近30天下单次数,件数,金额,
	-- 累计被下单次数,件数,金额,
	-- 最近30天支付次数,件数,金额
	-- 累计被下支付次数,件数,金额
	退款,加入购物车,收藏,好评,中评,差评
)
row format delimited fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='snappy');

-- Stage2: 向表中插入数据
-- step3: 把上述结果插入到 yp_dm.dm_sku表中.
insert into table yp_dm.dm_sku
-- step1: 首次计算 总累计 和 近30天累计(用CTE表达式封装)
with all_count as (
	select
		sku_id,
		sum(order_count) as order_count,  -- 下单次数,总累计
		sum(order_num) as order_num,  -- 下单件数,总累计
		sum(order_amount) as order_amount  -- 下单金额
		......
	from yp_dws.dws_sku_daycount  -- 数据来源于dws层
	group by sku_id  -- 因为是商品主题统计宽表,所以要按照商品id分类
),
last_30d as (
	select 
		sku_id,
		sum(order_count) as order_last_30d_count,  -- 下单次数,近30天累计
		sum(order_num) as order_last_30d_num,  -- 近30天下单件数累计
		sum(order_amount) as order_last_30d_amount,  -- 近30天下单金额累计
		.......
	from yp_dws.dws_sku_daycount
	where dt >= cast(date_sub('day',30, date '2020-05-08') as varchar)
	group by sku_id
)  -- 把上述结果进行汇总
select
	ac.sku_id,
	ac.order_count,
	ac.order_num,
	ac.order_amout,
	
	l30.sku_id,
	l30.order_count,
	l30.order_num,
	l30.order_amout,
from all_count ac left join last_30d l30 on ac.sku_id=l30.sku_id;

-- Stage2: 循环计算 总累计: 旧的总累计 + 新增一天的数据
-- step1: 创建yp_dm.dm_sku_tmp表, 用于存储最新的数据(总累计, 近30天累计)
create table yp_dm.dm_sku_tmp like yp_dm.dm_sku;
-- create table yp_dm.dm_sku_tmp as select * from yp_dm.dm_sku;    ctas 创建表方式

-- step4: 把下述的结果插入到 临时包中.
insert into yp_dm.dm_sku_tmp
-- step2: 循环计算 总累计 和 近30天累计
with old as(
	select * from yp_dm.dm_sku  -- 旧的总累计
)
new as (
	select 
		sku_id,
		sum(if(dt='2020-05-09',order_count,0)) as order_count_1d,  -- 统计最新一天的订单次数
		sum(if(dt='2020-05-09',order_num,0)) as order_num_1d,  -- 最新一天的订单件数
		sum(if(dt='2020-05-09',order_amount,0)) as order_amount_1d,  -- 最新一天的订单金额
		
		sum(order_count) as order_count_30,  -- 统计最近30天总累计-订单次数
		sum(order_num) as order_num_30,  -- 统计最近30天总累计-订单件数
		sum(order_amount) as order_amount_30  -- 统计最近30天总累计-订单金额
 	from 
		yp_dws.dws_sku_daycount  -- 数据来源于dws层
	where dt>=cast(date_sub('day',30,'2020-05-09') as varchar)  -- 转换类型,不转不能比较
	group by sku_id  -- 因为是商品主题统计宽表
)
-- step3: 基于上述的统计,计算出最新的结果
select 
	coalesce(old.sku_id, new.sku_id, 0) as sku_id,  -- 商品名称(取第一个不为空的值
	-- 计算最新30天的累计
	coalesce(new.order_count30,0) as order_count,
	coalesce(new.order_num,0) as order_num,
	coalesce(new.order_amount,0) as order_amount,
	
	-- 新的总累计 = 旧的总累计 + 最新一天的数据
	coalesce(old.order_count,0) + coalesce(new.order_count,0) as oreder_count,  -- 取第一个不为空的值 
	coalesce(old.order_num,0) + coalesce(new.order_num,0) as order_num,
	coalesce(old.order_amount,0) + coalesce(new.order_amount,0) as order_amount
from old full join new on new.sku_id=old.sku_id;

-- step5: 删除旧的累计数据.
delete from yp_dm.dm_sku;

-- step6: 用临时表(最新数据) 覆盖 yp_dm.dm_sku;
insert into yp_dm.dm_sku select * from yp_dm.dm_sku_tmp;

-- step7: 查询最终结果.
select * from yp_dm.dm_sku;

Report

  • 知道RPT层的作用
  • 掌握RPT层的搭建

RPT层的销售主题的难点是求各个渠道销售单量占比 - 由于销售单量是BIGINT类型,需要转为小数,cast

销售主题统计宽表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
-- 需求1: 门店月销售单量排行(按月统计，各个门店的 月销售单量
-- step1: 建表(Hive中实现)
CREATE TABLE yp_rpt.rpt_sale_store_cnt_month(
	date_time(统计日期),year_code,year_month,
	城市,商圈,店铺(id,name),
	店铺成交单量,小程序,安卓,ios,pcweb成交单量
)comment '门店月销售单量排行'
row format delimited fields terminated by '\t'
stored as orc tblproperties('orc.compress'='snappy');

-- step2: 向表中插入数据(Presto实现)
insert into hive.yp_rpt.rpt_sale_store_cnt_month
select 	
	'2023-06-05' date_time,
	year_code,year_month,
	城市,商圈,店铺(id,name)
	订单总量,小程序,安卓,ios,pcweb成交单量
from yp_dm.dm_sale
where time_type='month' and group_type = 'store' and store_id is not null;  -- 月+店铺

-- 需求2: 按天统计 总销售额 和 销售单量
select
	'2023-06-05' date_time,
	year_code,year_month,day_month_num,
	dim_date_id,sale_amt,order_cnt
from yp_dm.dm_sale
where time_type='date_time' and group_type='all'

-- 需求3: 渠道销售占比  比如每天不同渠道的订单量占比
select 
	'2023-06-05' as date_time,
	time_type,
	year_code,
	year_month,
	dim_date_id,
	order_cnt,          -- 总订单量
	miniapp_order_cnt,  -- 小程序成交单量.
	-- 求安卓,ios,小程序,pc成交单量(bigint类型)占比
	cast(
		cast(android_order_cnt as decimal(38,2))
		/cast(order_cnt as decimal(38,2)) * 100  as decimal(5,2)
		) as android_order_ratio  -- 安卓成交单量占比(ratio) 这里就涉及到cast转int为decimal类型
	.....
from yp_dm.dm_sale

商品主题统计宽表

这里的重点就是窗口函数的经典应用,求分组后的topN

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- 需求一：商品销量topN, 统计出某天销量最多的top10商品
with tmp as (
select
	dt,sku_id,payment_num,
	dense_rank() over(order by payment_num desc) dr -- 支付数量就是销量
from yp_dws.dws_sku_daycount
)
select * from tmp where dr <= 10
-- 后面的思路都是一样的,无非是将payment_num换成了cart_num,favor_count,或者用row_number()都是窗口函数
-- 需求二：商品收藏topN, 统计出某天收藏量最多的top10商品
-- 需求三：商品加入购物车topN, 统计出某天，购物车最多的top10商品

-- 需求四: 商品退款率TOPN, 统计出某天(月)，退款率最多的top10商品.
-- 第四个需求不是通过窗口函数实现的
select
	dt,
	sku_id,
	refund_id,
	cast(
		cast(refund_last_30d_count as decimal(38,2))/payment_last_30d_count * 100 
		as decimal(5,2)
		) as refund_ratio
from yp_dm.dm_sku
where payment_last_30d_count > 0  -- 支付次数 > 0 说明有买过
order by refund_ratio desc limit 10;

用户主题统计宽表

这里由于涉及到计算,可能会有重复的计算,甚至小括号过多,容易写错,最终考虑使用CTE表达式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
with temp as (
	select
		'2023-06-04' as dt,
		sum(if(最后一次登录时间='2023-06-05',1,0)) day_users,  -- 活跃会员数
		sum(if(login_date_first='2023-06-04',1,0)) day_new_users,  -- 新增会员数
		sum(if(payment_date_first='2023-06-04', 1, 0))  day_new_payment_users,    -- 新增消费会员数
		sum(if(payment_count > 0, 1, 0)) payment_users,  -- 总付费会员数
		count(*) users,  -- 总会员数
	from yp_dm.dm_user
)
select 
	*,
	-- 求会员活跃率 = 活跃会员数/总会员数 
	--这里注意一下,如果总会员数为0会报分母不能为0的错,还有需要判断分子是否为0,如果为0就用null填充
	cast(
		if(day_users=0,null,cast(day_users as decimal(38,2))/users * 100 as decimal(5,2))
		) as day_users_ratio
	-- 求总会员付费率 = 总付费会员数 / 总会员数(方法同上)
	-- 求会员新鲜度 = 新增会员数 /  活跃会员数(方法同上)
from temp;