文章目录
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也是可以的
/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
-- 全量覆盖(演示插入到分区表并使用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
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层
增量导入的第一步必定是全量导入
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层
/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:创建中间临时表,用于保存拉链结果
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:将公式获取的新的拉链表插入到临时表中
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,查询临时表的信息,确认无误后覆盖到旧的拉链表中
全量覆盖导入
insert overwrite table yp_dwd.dim_tistrict
select * from yp_ods.t_district
where code is not null and name is not null; -- 这里说明了DWD层的数据是经过清洗转换的
增量导入(第一次肯定是全量导入
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)
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
-- 如果这里是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 做省表
商品明细宽表
-- 商品分类表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(
根据什么分组就根据什么去重
)
Stage1:建表
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)
-- 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: 建表
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的区别
Pseudocode
-- 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层的指标一致
销售主题宽表实现
-- 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类型
-- 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: 门店月销售单量排行(按月统计,各个门店的 月销售单量
-- 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
-- 需求一:商品销量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表达式
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;