文章目录
Day06-核销主题开发
一、DWD层表处理
门店销售明细表
三个事实表处理
门店销售明细表
ods_sale_store_sale_dtl_i
combination_flag:描述订单中的商品时组合商品还是组合成分，需要的是组合商品 组合商品：泡面+火腿肠 10元 商品成分 泡面 7 元 商品成分 火腿肠 3 元 组合商品：泡面+碗 15元 商品成分 泡面 7 元 商品成分 碗 5 元 sum(price) offset_flag:冲减订单
select
-- combination_flag组合商品 ，捆绑销售的商品 （泡面+火腿肠）
-- 以上组合商品会在订单中产生三个数据
-- 123123 ，泡面+火腿肠（组合商品），1个,15元
-- 123124 ，泡面（组合成分），1个，10元
-- 123124，火腿肠（null） ，1个，5元 null
-- offset_flag 冲抵订单，是为了保证库存的准确性 入库100件商品,数据库中记录，在盘点库存时，发现商品库存99个缺失1个
-- 由公司员工任务产生一个订单，冲抵掉缺失的库存-1
combination_flag,offset_flag
from ods.ods_sale_store_sale_info_i t1
join ods.ods_sale_store_sale_dtl_i t2 on t1.order_no = t2.order_no and combination_flag!=2 and offset_flag=0
门店销售支付表
ods_sale_store_sale_pay_i
筛选出使用余额支付的数据pay_type_id=201
-- combination_flag组合商品 ，捆绑销售的商品 （泡面+火腿肠）
-- 以上组合商品会在订单中产生三个数据
-- 123123 ，泡面+火腿肠（组合商品1），1个,15元
-- 123124 ，泡面（组合成分2），1个，10元
-- 123124，火腿肠（组合成分2） ，1个，5元
-- 123125,苹果，2,8元
-- offset_flag 冲抵订单，是为了保证库存的准确性 入库100件商品,数据库中记录，在盘点库存时，发现商品库存99个缺失1个
-- 由公司员工任务产生一个订单，冲抵掉缺失的库存-1
combination_flag,offset_flag,pay_type_id
from ods.ods_sale_store_sale_info_i t1
-- 筛选组合商品和冲抵订单
join ods.ods_sale_store_sale_dtl_i t2 on t1.order_no = t2.order_no and combination_flag!=2 and offset_flag=0
-- 筛选使用余额支付的订单数据 黑马甄选的支付方式有会员余额支付(id编号为201)，三方线上支付（支付宝，微信），银行卡。项目只要余额支付的订单
join ods.ods_sale_store_sale_pay_i t3 on t1.order_no = t3.order_no and pay_type_id='201';
三张门店销售表关联
| 字段 | 描述 | 来源 |
|---|---|---|
| combination_flag | 描述订单中的商品时组合商品还是组合成分，需要的是组合商品 过滤出组合商品 1-组合商品，2-组合成分 | ods_sale_store_sale_dtl_i 门店销售明细表 |
| pay_type_id | 支付方式 201 余额支付 | ods_sale_store_sale_pay_i 门店销售支付表 |
| trade_date_time | 交易时间 转化字段deal_date(使用库存处理时间) | ods_sale_store_sale_info_i 门店销售信息表 |
| trade_date | 交易日期 转化字段trade_date 截取年月日 | ods_sale_store_sale_info_i 门店销售信息表 |
| hourly | 库存处理时间–小时 转化字段deal_date (小时) | ods_sale_store_sale_info_i 门店销售信息表 |
| quarter | 库存处理时间–刻 转化字段deal_date (小时) | ods_sale_store_sale_info_i 门店销售信息表 |
| quarters | 库存处理时间–刻钟 转化字段deal_date (小时) | ods_sale_store_sale_info_i 门店销售信息表 |
| parent_store_no | 转化字段store_no 母店编码和门店编码：母店编码为门店编码 | ods_sale_store_sale_info_i 门店销售信息表 |
| store_no | 转化字段sale_store_no,store_no 门店编码使用销售门店编码如果没有，使用门店编码 | ods_sale_store_sale_info_i 门店销售信息表 |
| trade_type | 转化字段trade_id 结算类型：判断trade_id,如果为1 返回0(正常交易),为2返回2,如果为3,4返回5 | ods_sale_store_sale_info_i 门店销售信息表 |
| source_type | 转化字段source_type 销售渠道如果为null,返回1 交易来源1:线下POS;2:三方平台;3:传智鲜商城;4:黑马优选团;5:传智大客户;6:传智其他;7:黑马优选;8:优选海淘;9:优选大客户;10:优选POS;11:优选APP;12:优选H5;13:店长工具线下;14:店长工具线上;15:黑马其他 | ods_sale_store_sale_info_i 门店销售信息表 |
| sale_type | 转化字段source_type 销售类型 1.实物,2.代客,3.优选小程序,4.离店,5.传智鲜小程序,6.第三方平台,7.其他,8.大客户 基于销售渠道判断 1–1，4,5,6,7,8–6,9–5,11–8 | ods_sale_store_sale_info_i 门店销售信息表 |
| member_type | 转化字段card_no 会员类型：如果以OL-/ SF-开头的为线上会员，否则为线下会员，为空为null 的为非会员 | ods_sale_store_sale_info_i 门店销售信息表 |
| is_balance_consume | 判断是否为余额消费，关联消费支付表判断order_no | ods_sale_store_sale_info_i 门店销售信息表 |
| parent_order_no | 母订单编号：判断商品销售金额是否小于0,如果小于采用source_order_sn(退款单据ID),如果大于采用parent_order_sn.如果为NULL,选择order_no | ods_sale_store_sale_info_i 门店销售信息表 |
| is_component | 是否为组合商品:0否,1是 | |
| balance_amount | 余额金额(平摊金额)计算：首先判断单据金额(total_pay_amount)是否等于0,则结果为0,如果不是，判断是否有余额支付金额(pay_amount)，如果有，使用余额金额* 商品销售金额/单据金额 | |
| last_update_time | deal_date 最后修改时间，选择库存时间 | |
| pay_time | 支付时间选择pay_date | |
| item (商品序号) sort(序号), cashier_no, cashier_name, trade_mode_id share_user_id commission_amount, zt_id member_id | 如果为null,设置为0 |
-- 门店销售商品表过滤
with tb1 as(
select * from ods_sale_store_sale_dtl_i
where
-- 过滤组合商品
coalesce(combination_flag,0) !=2
and
-- 过滤处非冲减单
offset_flag = 0
),
tb2 as(
select * from ods_sale_store_sale_pay_i
-- 过滤支付方式余额支付的
where pay_type_id ='201'
)
select
substr(tb1.deal_date,0,19) as trade_date_time,
substr(tb1.trade_date,0,10) as trade_date,
hour(tb1.deal_date) as hourly,
case
when minute(tb1.deal_date) between 0 and 14 then 1
when minute(tb1.deal_date) between 15 and 29 then 2
when minute(tb1.deal_date) between 30 and 44 then 3
when minute(tb1.deal_date) between 45 and 59 then 4
end as quarter,
(hour(tb1.deal_date) * 4 + case
when minute(tb1.deal_date) between 0 and 14 then 1
when minute(tb1.deal_date) between 15 and 29 then 2
when minute(tb1.deal_date) between 30 and 44 then 3
when minute(tb1.deal_date) between 45 and 59 then 4
end ) as quarters,
tb1.store_no as parent_store_no,
coalesce(tb1.sale_store_no,tb1.store_no) as store_no,
tb1.store_name,
case
when tb1.trade_id = 1 then 0
when tb1.trade_id = 2 then 2
when tb1.trade_id in(3,4) then 5
end as trade_type,
coalesce(tb1.source_type,1) as source_type,
case
when coalesce(tb1.source_type,1) = 1 then 1
when coalesce(tb1.source_type,1) in (4,5,6,7,8) then 6
when coalesce(tb1.source_type,1) = 9 then 5
when coalesce(tb1.source_type,1) = 11 then 8
end as sale_type,
case
when tb1.card_no like 'CF_%' or tb1.card_no like 'SF_%' then 1
when tb1.card_no ='' or tb1.card_no is null then 0
else
2
end as member_type,
if(tb2.order_no is null,1,0) as is_balance_consume,
case
when tb1.sale_amount < 0 then tb1.source_order_sn
when tb1.sale_amount >= 0 then tb1.parent_order_sn
else tb1.order_no
end as parent_order_no,
tb1.order_no,
tb1.pos_no,
tb1.ser_id,
tb1.item,
sort,
tb1.pay_date as pay_time,
last_update_time,
tb1.cashier_no,
tb1.cashier_name,
share_user_id,
commission_amount,
tb3.member_center_sn zt_id,
tb1.member_id,
tb1.card_no,
goods_no,
goods_name,
spec,
if(tb1.combination_flag=1,1,0) is_component,
trade_mode_id,
vendor_id,
contract_no,
is_daily_clear,
sale_qty,
tb1.sale_amount,
dis_amount,
sale_cost,
case
when tb3.total_pay_amount = 0 then 0
when tb2.pay_amount is null then 0
else
tb2.pay_amount *tb1.sale_amount/ tb3.total_pay_amount
end as balance_amount,
tb1.dt
from tb1
join ods_sale_store_sale_info_i tb3 on tb1.order_no = tb3.order_no
left join tb2 on tb1.order_no=tb2.order_no
关联五个维度表
| 维度字段的处理 |
|---|
| 1-sale_type(销售类型):基于上述结果表的source_type判断，当为1返回1,当为9返回5,当为4,5,6,7,8返回6 当为11返回8,否则返回7 |
| 2-tag商品标识为null,返回4 |
| 3-dc_no(采购仓库编码)为null返回-1 |
| 4-dc_name(采购仓库名称为null)返回 其他仓 |
| 5-group_no(采购柜组编码)为null,返回-1 |
| 6-group_name(采购柜组名称)为null,返回其他柜组 |
| 7-当vendor_id、is_clear为null,返回0 |
| 8-supply_team 使用店铺商品表中的tag字段 |
| 9-is_clean 使用店铺商品表中的is_clear |
-- 门店销售商品表过滤
with tb1 as(
select * from ods_sale_store_sale_dtl_i
where
-- 过滤组合商品
coalesce(combination_flag,0) !=2
and
-- 过滤处非冲减单
offset_flag = 0
),
tb2 as(
select * from ods_sale_store_sale_pay_i
-- 过滤支付方式余额支付的
where pay_type_id ='201'
),
tb4 as (
select
substr(tb1.deal_date,0,19) as trade_date_time,
substr(tb1.trade_date,0,10) as trade_date,
hour(tb1.deal_date) as hourly,
-- week_trade_date,
-- month_trade_date,
case
when minute(tb1.deal_date) between 0 and 14 then 1
when minute(tb1.deal_date) between 15 and 29 then 2
when minute(tb1.deal_date) between 30 and 144 then 3
when minute(tb1.deal_date) between 45 and 59 then 4
end as quarter,
(hour(tb1.deal_date) * 4 + case
when minute(tb1.deal_date) between 0 and 14 then 1
when minute(tb1.deal_date) between 15 and 29 then 2
when minute(tb1.deal_date) between 30 and 144 then 3
when minute(tb1.deal_date) between 45 and 59 then 4
end ) as quarters,
tb1.store_no as parent_store_no,
coalesce(tb1.sale_store_no,tb1.store_no) as store_no,
tb1.store_name,
-- store_sale_type,
-- store_type_code
-- worker_num,
-- store_area,
-- city_id,
-- city_name,
-- region_code,
-- region_name,
-- is_day_clear,
case
when tb1.trade_id = 1 then 0
when tb1.trade_id = 2 then 2
when tb1.trade_id in(3,4) then 5
end as trade_type,
coalesce(tb1.source_type,1) as source_type,
-- source_type_name,
case
when coalesce(tb1.source_type,1) = 1 then 1
when coalesce(tb1.source_type,1) in (4,5,6,7,8) then 6
when coalesce(tb1.source_type,1) = 9 then 5
when coalesce(tb1.source_type,1) = 11 then 8
end as sale_type,
case
when tb1.card_no like 'CF_%' or tb1.card_no like 'SF_%' then 1
when tb1.card_no ='' or tb1.card_no is null then 0
else
2
end as member_type,
if(tb2.order_no is null,1,0) as is_balance_consume,
case
when tb1.sale_amount < 0 then tb1.source_order_sn
when tb1.sale_amount >= 0 then tb1.parent_order_sn
else tb1.order_no
end as parent_order_no,
tb1.order_no,
tb1.pos_no,
tb1.ser_id,
tb1.item,
sort,
tb1.pay_date as pay_time,
last_update_time,
tb1.cashier_no,
tb1.cashier_name,
share_user_id,
commission_amount,
tb3.member_center_sn zt_id,
tb1.member_id,
tb1.card_no,
-- first_category_no,
-- first_category_name,
-- second_category_no,
-- second_category_name,
-- third_category_no,
-- third_category_name,
goods_no,
goods_name,
spec,
if(tb1.combination_flag=1,1,0) is_component,
-- supply_team,
-- dc_no,
-- dc_name,
-- group_no,
-- group_name,
trade_mode_id,
vendor_id,
contract_no,
-- is_clean,
is_daily_clear,
sale_qty,
tb1.sale_amount,
dis_amount,
sale_cost,
case
when tb3.total_pay_amount = 0 then 0
when tb2.pay_amount is null then 0
else
tb2.pay_amount *tb1.sale_amount/ tb3.total_pay_amount
end as balance_amount,
tb1.dt
from tb1
join ods_sale_store_sale_info_i tb3 on tb1.order_no = tb3.order_no
left join tb2 on tb1.order_no=tb2.order_no
)
select
trade_date_time,
tb4.trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
parent_store_no,
tb4.store_no,
tb4.store_name,
store_sale_type,
store_type_code,
worker_num,
store_area,
city_id,
city_name,
region_code,
region_name,
is_day_clear,
trade_type,
tb4.source_type,
source_type_name,
sale_type,
member_type,
is_balance_consume,
parent_order_no,
order_no,
pos_no,
ser_id,
item,
sort,
pay_time,
last_update_time,
cashier_no,
cashier_name,
share_user_id,
commission_amount,
zt_id,
member_id,
card_no,
tb7.first_category_no,
tb7.first_category_name,
tb7.second_category_no,
tb7.second_category_name,
tb7.third_category_no,
tb7.third_category_name,
tb4.goods_no,
tb4.goods_name,
tb7.spec,
is_component,
coalesce(tb8.tag,4) as supply_team,
coalesce(tb8.dc_no,-1) as dc_no,
coalesce(tb8.dc_name,'其他仓') as dc_name,
coalesce(tb8.group_no,-1) as group_no,
coalesce(tb8.group_name,'其他柜组') as group_name ,
trade_mode_id,
coalesce(tb4.vendor_id,0) as vendor_id,
contract_no,
coalesce(tb8.is_clear,0) as is_clean,
is_daily_clear,
sale_qty,
sale_amount,
dis_amount,
sale_cost,
balance_amount,
date_add(current_date,-1) write_time,
tb4.trade_date as dt
from tb4
left join dim.dwd_dim_date_f tb5 on tb5.trade_date =tb4.trade_date
left join dim.dwd_dim_store_i tb6 on tb6.store_no = tb4.store_no
left join dim.dwd_dim_goods_i tb7 on tb7.goods_no = tb4.goods_no
left join dim.dwd_dim_store_goods_i tb8 on tb8.goods_no=tb4.goods_no
left join dim.dwd_dim_source_type_map_i tb9 on tb9.original_source_type = tb4.source_type;
线上余额表
线上余额支付明细 dwd_sale_shop_sale_balance_pay_i
从 线上支付明细表ods_sale_shop_sale_pay_i 筛选出使用余额支付的信息
pay_channel_name 支付渠道名称
insert overwrite table dwd.dwd_sale_shop_sale_balance_pay_i partition(dt)
select
store_no,
store_name,
trade_date,
member_id,
zt_id,
trade_order_id,
pay_order_id,
order_no,
pay_channel,
pay_channel_name,
trade_order_type,
trade_order_type_name,
pay_amount,
trade_merchant,
dt
from ods.ods_sale_shop_sale_pay_i
where pay_channel_name ='余额支付';
商城订单表
商城订单表(核销表) dwd_sold_shop_order_i
从 商城订单表ods_sale_shop_order_i 找出已经核销的订单
complete_time 订单完结时间判断
insert overwrite table dwd.dwd_sold_shop_order_i partition(dt)
select
id,
parent_order_no,
order_id,
is_split,
platform_id,
tid,
source_type,
source_name,
store_no,
city_id,
city_name,
region_code,
order_status,
order_status_desc,
pay_type,
trade_type,
is_deleted,
order_create_time,
order_pay_time,
create_time,
print_status,
print_time,
stock_up_status,
stock_up_time,
order_type,
express_type,
receive_time,
express_code,
delivery_status,
delivery_time,
pick_up_status,
qr_code,
pick_up_time,
complete_time,
is_cancel,
cancel_time,
cancel_reason,
refund_status,
refund_time,
last_update_time,
order_total_amount,
product_total_amount,
pack_amount,
delivery_amount,
discount_amount,
seller_discount_amount,
platform_allowance_amount,
real_paid_amount,
product_discount,
real_product_amount,
buyer_id,
buyer_phone,
buyer_remark,
r_name,
r_tel,
r_province,
r_city,
r_district,
r_address,
r_zipcode,
is_tuan_head,
store_leader_id,
order_group_no,
commision_amount,
settle_amount,
points_amount,
pay_point,
balance_amount,
pay_channel_amount,
point_amount,
sync_erp_status,
sync_erp_msg,
dt
from ods.ods_sale_shop_order_i
where complete_time is not null;
商城订单明细表
商城订单明细表(核销表) dwd_sold_shop_order_item_i
从 商城订单明细表 ods_sale_shop_order_item_i 中找出已经核销的表
complete_time 订单完结时间判断
insert overwrite table dwd.dwd_sold_shop_order_item_i partition(dt)
select
id,
order_id,
goods_no,
goods_name,
weight,
quantity,
unit,
sale_qty,
disp_price,
pay_price,
sale_amount,
dis_amount,
sale_cost,
sale_type,
create_time,
complete_time,
last_update_time,
activity_plat_city_goods_id,
activity_type,
item_goods_key,
is_deleted,
transfer_paper_no,
serial_no,
is_delivery,
goods_source_type,
trade_mode_id,
vendor_id,
contract_no,
dt
from ods.ods_sale_shop_order_item_i
where complete_time is not null;
商城核销明细表
1-先将 商城订单表、订单明细表、线上余额支付明细关联计算
| 字段 | 描述 | 来源字段 | 计算 |
|---|---|---|---|
| trade_date | 交易日期 | complete_time | |
| hourly | 交易小时(0-23) | complete_time | |
| quarter | 刻钟:1.0-15,2.15-30,3.30-45,4.45-60 | complete_time | |
| quarters | 刻钟数 | ||
| trade_type | 结算类型(0.正常交易,1.赠品发放,2.退货,4.培训,5.取消交易) | 默认值0 | |
| is_balance_consume | 是否余额支付 | order_no | |
| is_cancel | 是否取消 | is_cancel | 当is_cancel为1返回5否则返回0 |
| commission_amount | 抽佣金额 | commision_amount（商城订单） * sale_amount（商城订单详情） / real_product_amount（商城订单） | 抽佣金额*商品销售金额/商品实际金额 |
| settle_amount | 结算金额 | settle_amount（商城订单） * sale_amount（商城订单详情） / real_product_amount | 结算金额*商品销售金额/商品实际金额 |
| order_total_amount | 订单总金额(平摊) | order_total_amount（商城订单） * sale_amount / real_product_amount | 订单总金额*商品销售金额/商品实际金额 |
| order_discount_amount | 订单优惠金额 | discount_amount（商城订单） * sale_amount / real_product_amount | 订单优惠金额*商品销售金额/商品实际金额 |
| order_paid_amount | 实付金额 | real_paid_amount（商城订单） * sale_amount / real_product_amount | 如果实付金额为0 则为0，否则实付金额*商品销售金额/商品实际金额 |
| balance_amount | 余额支付金额 | pay_amount（余额支付明细） * sale_amount / t1.real_product_amount | 首先判断单据金额是否等于0 , 则结果为0, 如果不是, 判断是否有余额支付金额, 如果有, 使用余额金额 * 商品销售金额 / 单据金额 |
select
substr(t1.complete_time,1,10) as trade_date,
hour( t1.complete_time) as hourly,
-- 刻钟
case when minute( t1.complete_time) between 0 and 15 then 1
when minute( t1.complete_time) between 16 and 30 then 2
when minute( t1.complete_time) between 31 and 45 then 3
when minute( t1.complete_time) between 46 and 59 then 4
end as quarter,
-- 刻钟数 当前刻钟是一天中的第几个刻钟 12:35
-- 12*4 = 48 + 3 = 51
hour( t1.complete_time) * 4 + case
when minute( t1.complete_time) between 0 and 15 then 1
when minute( t1.complete_time) between 16 and 30 then 2
when minute( t1.complete_time) between 31 and 45 then 3
when minute( t1.complete_time) between 46 and 59 then 4
end as quarters,
0 as trade_type,
if(t3.order_no is not null,1,0) as is_balance_consume,
if(t1.is_cancel = 1,5,0) as is_cancel,
-- 计算
(t1.commision_amount * t2.sale_amount / t1.real_product_amount) as commision_amount,
(t1.settle_amount * t2.sale_amount / t1.real_product_amount) as settle_amount,
(t1.order_total_amount * t2.sale_amount / t1.real_product_amount) as order_total_amount,
(t1.discount_amount * t2.sale_amount / t1.real_product_amount) as discount_amount,
if(real_paid_amount = 0 , 0 , t1.real_paid_amount* t2.sale_amount / t1.real_product_amount) as order_paid_amount,
if(
t1.real_product_amount =0 ,0 ,
if(pay_amount is not null, pay_amount * sale_amount / real_product_amount,0)
) as balance_amount
from dwd.dwd_sold_shop_order_i t1
left join dwd.dwd_sold_shop_order_item_i t2 on t1.order_id=t2.order_id
left join dwd.dwd_sale_shop_sale_balance_pay_i t3 on t3.order_no=t1.order_id;
2- 接着将退款表、退款明细表、商城订单表以及线上余额支付明细表进行进行关联
退款表和退款明细表关联后的字段较少，为了将来和商城订单表合并，需要对字段进行扩充
| 字段 | 描述 | 来源字段 | 计算 |
|---|---|---|---|
| trade_date | 交易日期 | create_time | |
| hourly | 交易小时(0-23) | create_time | |
| quarter | 刻钟:1.0-15,2.15-30,3.30-45,4.45-60 | create_time | |
| quarters | |||
| trade_type | 结算类型(0.正常交易,1.赠品发放,2.退货,4.培训,5.取消交易) | cancel_time | cancel_time不为空返回5否则2 |
| is_balance_consume | 是否余额支付 | order_no | |
| is_cancel | 是否取消 | is_cancel | 当is_cancel为1返回5否则返回0 |
| commission_amount | 抽佣金额 | commision_amount（商城订单） * samount（商城退款订单详情） / real_product_amount（商城订单） | 抽佣金额*商品销售金额/商品实际金额，结果为负数 |
| settle_amount | 结算金额 | settle_amount（商城订单） * amount（商城退款订单详情） / real_product_amount | 结算金额*商品销售金额/商品实际金额，结果为负数 |
| order_total_amount | 订单总金额(平摊) | amount商城退款订单详情 | 负数 |
| order_discount_amount | 订单优惠金额 | order_discount_amount | 默认值 0 |
| order_paid_amount | 实付金额 | amount | 结果为负数 |
| balance_amount | 余额支付金额 | pay_amount（余额支付明细） * amount / t1.real_product_amount | 首先判断单据金额是否等于0 , 则结果为0, 如果不是, 判断是否有余额支付金额, 如果有, 使用余额金额 * 商品销售金额 / 单据金额 |
| weight | 重量 | 默认 0 | |
| quantity | 数量 | 负数 | |
| unit | 单位 | 默认 ‘’ | |
| qty | 销售数量 | 负数 | |
| disp_price | sku展示价格 | amount / quantity | |
| pay_price | 价格 | amount / quantity | |
| dis_amount | 单品总折扣金额 | 默认 0 | |
| sale_cost | 销售成本 | cost | 负数 |
| sale_type | 类型：1-常规；2-赠品 | 默认1 |
select
substr(t4.create_time,1,10) as trade_date,
hour( t4.create_time) as hourly,
-- 刻钟
case when minute( t4.create_time) between 0 and 15 then 1
when minute( t4.create_time) between 16 and 30 then 2
when minute( t4.create_time) between 31 and 45 then 3
when minute( t4.create_time) between 46 and 59 then 4
end as quarter,
-- 刻钟数 当前刻钟是一天中的第几个刻钟 12:35
-- 12*4 = 48 + 3 = 51
hour( t4.create_time) * 4 + case
when minute( t4.create_time) between 0 and 15 then 1
when minute( t4.create_time) between 16 and 30 then 2
when minute( t4.create_time) between 31 and 45 then 3
when minute(t4.create_time) between 46 and 59 then 4
end as quarters,
if(t4.cancel_time is null,2,5) as trade_type,
if(t7.order_no is not null,1,0) as is_balance_consume,
if(is_cancel = 1,5,0) as is_cancel,
-- 计算
(commision_amount * t5.amount / real_product_amount) as commision_amount,
(settle_amount * t5.amount / real_product_amount) as settle_amount,
-amount as order_total_amount,
0 as discount_amount,
-amount as order_paid_amount,
if(
real_product_amount =0 ,0 ,
if(pay_amount is not null, pay_amount * t5.amount / real_product_amount,0)
) as balance_amount,
0 as weight,
-quantity as quantity,
'' as unit,
-qty as qty,
amount/quantity as disp_price,
amount/quantity as pay_price,
0 as dis_amount,
-cost as sale_cost,
1 as sale_type
from ods.ods_sale_shop_refund_i t4
left join ods.ods_sale_shop_refund_item_i t5 on t4.refund_no = t5.refund_no
left join ods.ods_sale_shop_order_i t6 on t4.order_no=t6.order_id
left join ods.ods_sale_shop_sale_pay_i t7 on t4.order_no = t7.order_no;
3- 将1和2的结果 基于union all 关联在一起,
-- 使用join
select
substr(t1.complete_time,1,10) as trade_date,
hour( t1.complete_time) as hourly,
-- 刻钟
case when minute( t1.complete_time) between 0 and 15 then 1
when minute( t1.complete_time) between 16 and 30 then 2
when minute( t1.complete_time) between 31 and 45 then 3
when minute( t1.complete_time) between 46 and 59 then 4
end as quarter,
-- 刻钟数 当前刻钟是一天中的第几个刻钟 12:35
-- 12*4 = 48 + 3 = 51
hour( t1.complete_time) * 4 + case
when minute( t1.complete_time) between 0 and 15 then 1
when minute( t1.complete_time) between 16 and 30 then 2
when minute( t1.complete_time) between 31 and 45 then 3
when minute( t1.complete_time) between 46 and 59 then 4
end as quarters,
0 as trade_type,
if(t3.order_no is not null,1,0) as is_balance_consume,
if(t1.is_cancel = 1,5,0) as is_cancel,
-- 计算
(t1.commision_amount * t2.sale_amount / t1.real_product_amount) as commision_amount,
(t1.settle_amount * t2.sale_amount / t1.real_product_amount) as settle_amount,
(t1.order_total_amount * t2.sale_amount / t1.real_product_amount) as order_total_amount,
(t1.discount_amount * t2.sale_amount / t1.real_product_amount) as order_discount_amount,
if(real_paid_amount = 0 , 0 , t1.real_paid_amount* t2.sale_amount / t1.real_product_amount) as order_paid_amount,
if(
t1.real_product_amount =0 ,0 ,
if(pay_amount is not null, pay_amount * sale_amount / real_product_amount,0)
) as balance_amount,
weight,
quantity,
unit,
0 as qty,
disp_price,
pay_price,
dis_amount,
sale_cost,
sale_type
from dwd.dwd_sold_shop_order_i t1
left join dwd.dwd_sold_shop_order_item_i t2 on t1.order_id=t2.order_id
left join dwd.dwd_sale_shop_sale_balance_pay_i t3 on t3.order_no=t1.order_id
union all
-- 退款表关联
select
substr(t4.create_time,1,10) as trade_date,
hour( t4.create_time) as hourly,
-- 刻钟
case when minute( t4.create_time) between 0 and 15 then 1
when minute( t4.create_time) between 16 and 30 then 2
when minute( t4.create_time) between 31 and 45 then 3
when minute( t4.create_time) between 46 and 59 then 4
end as quarter,
-- 刻钟数 当前刻钟是一天中的第几个刻钟 12:35
-- 12*4 = 48 + 3 = 51
hour( t4.create_time) * 4 + case
when minute( t4.create_time) between 0 and 15 then 1
when minute( t4.create_time) between 16 and 30 then 2
when minute( t4.create_time) between 31 and 45 then 3
when minute(t4.create_time) between 46 and 59 then 4
end as quarters,
if(t4.cancel_time is null,2,5) as trade_type,
if(t7.order_no is not null,1,0) as is_balance_consume,
if(is_cancel = 1,5,0) as is_cancel,
-- 计算
(commision_amount * t5.amount / real_product_amount) as commision_amount,
(settle_amount * t5.amount / real_product_amount) as settle_amount,
-amount as order_total_amount,
0 as order_discount_amount,
-amount as order_paid_amount,
if(
real_product_amount =0 ,0 ,
if(pay_amount is not null, pay_amount * t5.amount / real_product_amount,0)
) as balance_amount,
0 as weight,
-quantity as quantity,
'' as unit,
-qty as qty,
amount/quantity as disp_price,
amount/quantity as pay_price,
0 as dis_amount,
-cost as sale_cost,
1 as sale_type
from ods.ods_sale_shop_refund_i t4
left join ods.ods_sale_shop_refund_item_i t5 on t4.refund_no = t5.refund_no
left join ods.ods_sale_shop_order_i t6 on t4.order_no=t6.order_id
left join ods.ods_sale_shop_sale_pay_i t7 on t4.order_no = t7.order_no;
4-维度表进行关联: 处理操作与门店销售明细宽表类似
| 1-supply_team 使用店铺商品表中的tag字段 tag商品标识为null,返回4 |
| 2-dc_no(采购仓库编码)为null返回-1 |
| 3-dc_name(采购仓库名称为null)返回其他仓 |
| 4-group_no(采购柜组编码)为null,返回-1 |
| 5-group_name(采购柜组名称)为null,返回其他柜组 |
| 6-当vendor_id、 为null,返回0 |
| 8-is_clean 使用店铺商品表中的is_clear is_clear为null,返回0 |
with tb_fact as(
select
substr(t1.complete_time,1,10) as trade_date,
hour( t1.complete_time) as hourly,
-- 刻钟
case when minute( t1.complete_time) between 0 and 15 then 1
when minute( t1.complete_time) between 16 and 30 then 2
when minute( t1.complete_time) between 31 and 45 then 3
when minute( t1.complete_time) between 46 and 59 then 4
end as quarter,
-- 刻钟数 当前刻钟是一天中的第几个刻钟 12:35
-- 12*4 = 48 + 3 = 51
hour( t1.complete_time) * 4 + case
when minute( t1.complete_time) between 0 and 15 then 1
when minute( t1.complete_time) between 16 and 30 then 2
when minute( t1.complete_time) between 31 and 45 then 3
when minute( t1.complete_time) between 46 and 59 then 4
end as quarters,
0 as trade_type,
if(t3.order_no is not null,1,0) as is_balance_consume,
if(t1.is_cancel = 1,5,0) as is_cancel,
-- 计算
(t1.commision_amount * t2.sale_amount / t1.real_product_amount) as commission_amount,
(t1.settle_amount * t2.sale_amount / t1.real_product_amount) as settle_amount,
(t1.order_total_amount * t2.sale_amount / t1.real_product_amount) as order_total_amount,
(t1.discount_amount * t2.sale_amount / t1.real_product_amount) as order_discount_amount,
if(real_paid_amount = 0 , 0 , t1.real_paid_amount* t2.sale_amount / t1.real_product_amount) as order_paid_amount,
if(
t1.real_product_amount =0 ,0 ,
if(pay_amount is not null, pay_amount * sale_amount / real_product_amount,0)
) as balance_amount,
weight,
quantity,
unit,
0 as qty,
sale_qty,
disp_price,
pay_price,
dis_amount,
sale_cost,
sale_type,
cast(goods_no as string) as goods_no,
goods_name,
t1.store_no,
store_name,
t1.complete_time,
parent_order_no,
t1.order_id,
is_split,
platform_id,
tid,
source_type,
source_name,
order_type,
express_type,
order_status,
order_status_desc,
pay_type,
order_create_time,
order_pay_time,
cancel_time,
cancel_reason,
t1.last_update_time,
zt_id,
buyer_id,
buyer_phone,
buyer_remark,
r_name,
r_tel,
r_province,
r_city,
r_district,
r_address,
r_zipcode,
is_tuan_head,
store_leader_id,
order_group_no,
sale_amount,
activity_plat_city_goods_id,
activity_type,
trade_mode_id,
vendor_id,
contract_no,
t1.create_time
from dwd.dwd_sold_shop_order_i t1
left join dwd.dwd_sold_shop_order_item_i t2 on t1.order_id=t2.order_id
left join dwd.dwd_sale_shop_sale_balance_pay_i t3 on t3.order_no=t1.order_id
union all
-- 退款表关联
select
substr(t4.create_time,1,10) as trade_date,
hour( t4.create_time) as hourly,
-- 刻钟
case when minute( t4.create_time) between 0 and 15 then 1
when minute( t4.create_time) between 16 and 30 then 2
when minute( t4.create_time) between 31 and 45 then 3
when minute( t4.create_time) between 46 and 59 then 4
end as quarter,
-- 刻钟数 当前刻钟是一天中的第几个刻钟 12:35
-- 12*4 = 48 + 3 = 51
hour( t4.create_time) * 4 + case
when minute( t4.create_time) between 0 and 15 then 1
when minute( t4.create_time) between 16 and 30 then 2
when minute( t4.create_time) between 31 and 45 then 3
when minute(t4.create_time) between 46 and 59 then 4
end as quarters,
if(t4.cancel_time is null,2,5) as trade_type,
if(t7.order_no is not null,1,0) as is_balance_consume,
if(is_cancel = 1,5,0) as is_cancel,
-- 计算
(commision_amount * t5.amount / real_product_amount) as commision_amount,
(settle_amount * t5.amount / real_product_amount) as settle_amount,
-amount as order_total_amount,
0 as order_discount_amount,
-amount as order_paid_amount,
if(
real_product_amount =0 ,0 ,
if(pay_amount is not null, pay_amount * t5.amount / real_product_amount,0)
) as balance_amount,
0 as weight,
-quantity as quantity,
'' as unit,
-qty as qty,
-qty as sale_qty,
amount/quantity as disp_price,
amount/quantity as pay_price,
0 as dis_amount,
-cost as sale_cost,
1 as sale_type,
cast(goods_no as string) as goods_no,
goods_name,
t4.store_no,
store_name,
complete_time,
parent_order_no,
order_id,
is_split,
platform_id,
tid,
source_type,
source_name,
order_type,
express_type,
order_status,
order_status_desc,
pay_type,
order_create_time,
order_pay_time,
t4.cancel_time,
cancel_reason,
last_update_time,
zt_id,
buyer_id,
buyer_phone,
buyer_remark,
r_name,
r_tel,
r_province,
r_city,
r_district,
r_address,
r_zipcode,
is_tuan_head,
t4.store_leader_id,
order_group_no,
amount as sale_amount,
activity_plat_city_goods_id,
activity_type,
trade_mode_id,
vendor_id,
contract_no,
t4.create_time
from ods.ods_sale_shop_refund_i t4
left join ods.ods_sale_shop_refund_item_i t5 on t4.refund_no = t5.refund_no
left join ods.ods_sale_shop_order_i t6 on t4.order_no=t6.order_id
left join ods.ods_sale_shop_sale_pay_i t7 on t4.order_no = t7.order_no)
insert overwrite table dwd.dwd_sold_shop_order_dtl_i partition(dt)
select
complete_time,
t8.trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
parent_order_no,
order_id,
trade_type,
is_split,
platform_id,
tid,
source_type,
source_name,
order_type,
express_type,
order_status,
order_status_desc,
pay_type,
is_balance_consume,
t8.store_no,
t8.store_name,
store_sale_type,
store_type_code,
worker_num,
store_area,
city_id,
city_name,
region_code,
region_name,
is_day_clear,
order_create_time,
order_pay_time,
t8.create_time,
is_cancel,
cancel_time,
cancel_reason,
last_update_time,
zt_id,
buyer_id,
buyer_phone,
buyer_remark,
r_name,
r_tel,
r_province,
r_city,
r_district,
r_address,
r_zipcode,
is_tuan_head,
store_leader_id,
order_group_no,
commission_amount,
settle_amount,
t10.first_category_no,
t10.first_category_name,
t10.second_category_no,
t10.second_category_name,
t10.third_category_no,
t10.third_category_name,
t10.goods_no,
t10.goods_name,
weight,
quantity,
unit,
sale_qty,
disp_price,
pay_price,
sale_amount,
dis_amount,
sale_cost,
sale_type,
activity_plat_city_goods_id,
activity_type,
order_total_amount,
order_discount_amount,
order_paid_amount,
balance_amount,
coalesce(tag,4) as supply_team,
coalesce(dc_no,-1) as dc_no,
coalesce(dc_name,'其他仓') as dc_name,
coalesce(group_no,-1) as group_no,
coalesce(group_name,'其他柜组') as group_no,
trade_mode_id,
coalesce(vendor_id,0) as vendor_id,
contract_no,
substr(complete_time,1,10) as dt
from tb_fact t8
left join dim.dwd_dim_date_f t9 on t8.trade_date = t9.trade_date
left join dim.dwd_dim_goods_i t10 on t8.goods_no= t10.goods_no
left join dim.dwd_dim_store_goods_i t11 on t8.goods_no= t11.goods_no
left join dim.dwd_dim_store_i t12 on t8.store_no= t12.store_no
门店库调表
| 字段 | 描述 | 来源字段 | 计算 |
|---|---|---|---|
| trade_date | 交易日期 | stock_deal_time | |
| hourly | 交易小时(0-23) | stock_deal_time | |
| quarter | 刻钟:1.0-15,2.15-30,3.30-45,4.45-60 | stock_deal_time | |
| quarters | 刻钟数 |
insert overwrite table dwd.dwd_stock_store_stock_adj_i partition(dt)
select
trade_date,
week_trade_date,
month_trade_date,
hour( stock_deal_time) as hourly,
-- 刻钟
case when minute( stock_deal_time) between 0 and 15 then 1
when minute( stock_deal_time) between 16 and 30 then 2
when minute( stock_deal_time) between 31 and 45 then 3
when minute( stock_deal_time) between 46 and 59 then 4
end as quarter,
-- 刻钟数 当前刻钟是一天中的第几个刻钟 12:35
-- 12*4 = 48 + 3 = 51
hour( stock_deal_time) * 4 + case
when minute( stock_deal_time) between 0 and 15 then 1
when minute( stock_deal_time) between 16 and 30 then 2
when minute( stock_deal_time) between 31 and 45 then 3
when minute(stock_deal_time) between 46 and 59 then 4
end as quarters,
t1.id,
uid,
order_id,
order_source,
t1.store_no,
t1.store_name,
store_sale_type,
store_type_code,
worker_num,
store_area,
city_id,
city_name,
region_code,
region_name,
is_day_clear,
goods_no,
goods_name,
adj_type_big,
adj_type_small,
adj_reason_big,
adj_reason_small,
adj_qty,
adj_price,
adj_amount,
t1.create_time,
stock_deal_time,
sync_time,
vendor_no,
vendor_name,
substr(stock_deal_time,1,10) as dt
from ods.ods_stock_store_stock_adj_i t1
left join dim.dwd_dim_date_f t2 on substr(stock_deal_time,1,10) = t2.trade_date
left join dim.dwd_dim_store_i t3 on t1.store_no = t3.store_no;
其他表
参考门店库调表自己实现
-- 7.门店收货表(3张表降维拉宽):dwd_order_store_receive_i
insert overwrite table dwd.dwd_order_store_receive_i partition(dt)
select
date_format(t.stock_deal_time,'yyyy-MM-dd') as trade_date
,dd.week_trade_date
,dd.month_trade_date
,hour(t.stock_deal_time) as hourly
,case when minute(t.stock_deal_time)>=0 and minute(t.stock_deal_time)<15 then 1
when minute(t.stock_deal_time)>=15 and minute(t.stock_deal_time)<30 then 2
when minute(t.stock_deal_time)>=30 and minute(t.stock_deal_time)<45 then 3
when minute(t.stock_deal_time)>=45 and minute(t.stock_deal_time)<60 then 4
end as `quarter`
,hour(t.stock_deal_time)*4 + case when minute(t.stock_deal_time)>=0 and minute(t.stock_deal_time)<15 then 1
when minute(t.stock_deal_time)>=15 and minute(t.stock_deal_time)<30 then 2
when minute(t.stock_deal_time)>=30 and minute(t.stock_deal_time)<45 then 3
when minute(t.stock_deal_time)>=45 and minute(t.stock_deal_time)<60 then 4
end as quarters
,t.id
,t.uid
,t.order_id
,t.order_source
,t.store_no
,bs.store_name
,bs.store_sale_type
,bs.store_type_code
,bs.worker_num
,bs.store_area
,bs.city_id
,bs.city_name
,bs.region_code
,bs.region_name
,bs.is_day_clear
,t.goods_no
,t.goods_name
,t.dc_no
,t.dc_name
,t.vendor_no
,t.vendor_name
,t.order_type
,t.receive_price
,t.receive_qty
,t.git_qty
,t.create_time
,t.stock_deal_time
,t.dc_send_order_id
,t.red_order_id
,t.contract_no
,t.contract_name
,t.trade_mode
,t.order_source_type
,t.sync_time
,date_format(t.stock_deal_time,'yyyy-MM-dd') as dt
from ods.ods_order_store_receive_i t
inner join dim.dwd_dim_date_f as dd
on date_format(t.stock_deal_time,'yyyy-MM-dd')=dd.trade_date
inner join dim.dwd_dim_store_i as bs
on t.store_no=bs.store_no ;
-- 门店退货表(3张表降维拉宽): dwd_order_store_return_to_vendor_i
insert overwrite table dwd.dwd_order_store_return_to_vendor_i partition (dt)
select
date_format(t.stock_deal_time,'yyyy-MM-dd') as trade_date
,dd.week_trade_date
,dd.month_trade_date
,hour(t.stock_deal_time) as hourly
,case when minute(t.stock_deal_time)>=0 and minute(t.stock_deal_time)<15 then 1
when minute(t.stock_deal_time)>=15 and minute(t.stock_deal_time)<30 then 2
when minute(t.stock_deal_time)>=30 and minute(t.stock_deal_time)<45 then 3
when minute(t.stock_deal_time)>=45 and minute(t.stock_deal_time)<60 then 4
end as `quarter`
,hour(t.stock_deal_time)*4 + case when minute(t.stock_deal_time)>=0 and minute(t.stock_deal_time)<15 then 1
when minute(t.stock_deal_time)>=15 and minute(t.stock_deal_time)<30 then 2
when minute(t.stock_deal_time)>=30 and minute(t.stock_deal_time)<45 then 3
when minute(t.stock_deal_time)>=45 and minute(t.stock_deal_time)<60 then 4
end as quarters
,t.id
,t.uid
,t.order_id
,t.order_source
,t.store_no
,bs.store_name
,bs.store_sale_type
,bs.store_type_code
,bs.worker_num
,bs.store_area
,bs.city_id
,bs.city_name
,bs.region_code
,bs.region_name
,bs.is_day_clear
,t.goods_no
,t.goods_name
,t.dc_no
,t.dc_name
,t.vendor_no
,t.vendor_name
,t.return_price
,t.return_qty
,t.create_time
,t.stock_deal_time
,t.original_order_id
,t.is_fresh
,t.is_entity
,t.responsible_person
,t.return_reason_big
,t.return_desc_big
,t.return_reason_small
,t.return_desc_small
,t.sync_time
,date_format(t.stock_deal_time,'yyyy-MM-dd') as dt
from ods.ods_order_store_return_to_vendor_i t
inner join dim.dwd_dim_date_f as dd
on date_format(t.stock_deal_time,'yyyy-MM-dd') = dd.trade_date
inner join dim.dwd_dim_store_i as bs
on t.store_no=bs.store_no
;
-- 门店退配表(3张表降维拉宽): dwd_order_store_return_to_dc_i
insert overwrite table dwd.dwd_order_store_return_to_dc_i partition (dt)
select
date_format(t.stock_deal_time,'yyyy-MM-dd') as trade_date
,dd.week_trade_date
,dd.month_trade_date
,hour(t.stock_deal_time) as hourly
,case when minute(t.stock_deal_time)>=0 and minute(t.stock_deal_time)<15 then 1
when minute(t.stock_deal_time)>=15 and minute(t.stock_deal_time)<30 then 2
when minute(t.stock_deal_time)>=30 and minute(t.stock_deal_time)<45 then 3
when minute(t.stock_deal_time)>=45 and minute(t.stock_deal_time)<60 then 4
end as `quarter`
,hour(t.stock_deal_time)*4 + case when minute(t.stock_deal_time)>=0 and minute(t.stock_deal_time)<15 then 1
when minute(t.stock_deal_time)>=15 and minute(t.stock_deal_time)<30 then 2
when minute(t.stock_deal_time)>=30 and minute(t.stock_deal_time)<45 then 3
when minute(t.stock_deal_time)>=45 and minute(t.stock_deal_time)<60 then 4
end as quarters
,t.id
,t.uid
,t.order_id
,t.order_source
,t.store_no
,bs.store_name
,bs.store_sale_type
,bs.store_type_code
,bs.worker_num
,bs.store_area
,bs.city_id
,bs.city_name
,bs.region_code
,bs.region_name
,bs.is_day_clear
,t.goods_no
,t.goods_name
,t.dc_no
,t.dc_name
,t.vendor_no
,t.vendor_name
,t.return_price
,t.return_qty
,t.create_time
,t.stock_deal_time
,t.original_order_id
,t.is_fresh
,t.is_entity
,t.responsible_person
,t.return_reason_big
,t.return_desc_big
,t.return_reason_small
,t.return_desc_small
,t.sync_time
,t.batch_type_id
,date_format(t.stock_deal_time,'yyyy-MM-dd') as dt
from ods.ods_order_store_return_to_dc_i t
inner join dim.dwd_dim_date_f as dd
on date_format(t.stock_deal_time,'yyyy-MM-dd') = dd.trade_date
inner join dim.dwd_dim_store_i as bs
on t.store_no = bs.store_no
;
-- 门店调入表(3张表降维拉宽):dwd_order_store_alloc_in_i
insert overwrite table dwd.dwd_order_store_alloc_in_i partition (dt)
select
date_format(t.stock_deal_time,'yyyy-MM-dd') as trade_date
,dd.week_trade_date
,dd.month_trade_date
,hour(t.stock_deal_time) as hourly
,case when minute(t.stock_deal_time)>=0 and minute(t.stock_deal_time)<15 then 1
when minute(t.stock_deal_time)>=15 and minute(t.stock_deal_time)<30 then 2
when minute(t.stock_deal_time)>=30 and minute(t.stock_deal_time)<45 then 3
when minute(t.stock_deal_time)>=45 and minute(t.stock_deal_time)<60 then 4
end as `quarter`
,hour(t.stock_deal_time)*4 + case when minute(t.stock_deal_time)>=0 and minute(t.stock_deal_time)<15 then 1
when minute(t.stock_deal_time)>=15 and minute(t.stock_deal_time)<30 then 2
when minute(t.stock_deal_time)>=30 and minute(t.stock_deal_time)<45 then 3
when minute(t.stock_deal_time)>=45 and minute(t.stock_deal_time)<60 then 4
end as quarters
,t.id
,t.uid
,t.order_id
,t.order_source
,t.goods_no
,t.goods_name
,t.alloc_in_store_no
,bs.store_name as alloc_in_store_name
,bs.store_sale_type as alloc_in_store_sale_type
,bs.store_type_code as alloc_in_store_type_code
,bs.worker_num as alloc_in_worker_num
,bs.store_area as alloc_in_store_area
,bs.city_id as alloc_in_city_id
,bs.city_name as alloc_in_city_name
,bs.region_code as alloc_in_region_code
,bs.region_name as alloc_in_region_name
,bs.is_day_clear as alloc_in_is_clear
,t.alloc_out_store_no
,t.alloc_out_store_name
,t.alloc_price
,t.alloc_qty
,t.alloc_reason
,t.alloc_amount
,t.create_time
,t.stock_deal_time
,t.sync_time
,t.vendor_no
,t.vendor_name
,date_format(t.stock_deal_time,'yyyy-MM-dd') as dt
from ods.ods_order_store_alloc_in_i t
inner join dim.dwd_dim_date_f as dd
on date_format(t.stock_deal_time,'yyyy-MM-dd') = dd.trade_date
inner join dim.dwd_dim_store_i as bs
on t.alloc_in_store_no=bs.store_no and bs.dt = '2023-11-22'
;
-- 门店调出表(3张表降维拉宽):dwd_order_store_alloc_out_i
insert overwrite table dwd.dwd_order_store_alloc_out_i partition (dt)
select
date_format(t.stock_deal_time,'yyyy-MM-dd') as trade_date
,dd.week_trade_date
,dd.month_trade_date
,hour(t.stock_deal_time) as hourly
,case when minute(t.stock_deal_time)>=0 and minute(t.stock_deal_time)<15 then 1
when minute(t.stock_deal_time)>=15 and minute(t.stock_deal_time)<30 then 2
when minute(t.stock_deal_time)>=30 and minute(t.stock_deal_time)<45 then 3
when minute(t.stock_deal_time)>=45 and minute(t.stock_deal_time)<60 then 4
end as `quarter`
,hour(t.stock_deal_time)*4 + case when minute(t.stock_deal_time)>=0 and minute(t.stock_deal_time)<15 then 1
when minute(t.stock_deal_time)>=15 and minute(t.stock_deal_time)<30 then 2
when minute(t.stock_deal_time)>=30 and minute(t.stock_deal_time)<45 then 3
when minute(t.stock_deal_time)>=45 and minute(t.stock_deal_time)<60 then 4
end as quarters
,t.id
,t.uid
,t.order_id
,t.order_source
,t.goods_no
,t.goods_name
,t.alloc_in_store_no
,t.alloc_in_store_name
,t.alloc_out_store_no
,bs.store_name as alloc_out_store_name
,bs.store_sale_type as alloc_out_store_sale_type
,bs.store_type_code as alloc_out_store_type_code
,bs.worker_num as alloc_out_worker_num
,bs.store_area as alloc_out_store_area
,bs.city_id as alloc_out_city_id
,bs.city_name as alloc_out_city_name
,bs.region_code as alloc_out_region_code
,bs.region_name as alloc_out_region_name
,bs.is_day_clear as alloc_out_is_clear
,t.alloc_price
,t.alloc_qty
,t.alloc_reason
,t.alloc_amount
,t.create_time
,t.stock_deal_time
,t.sync_time
,t.vendor_no
,t.vendor_name
,date_format(t.stock_deal_time,'yyyy-MM-dd') as dt
from ods.ods_order_store_alloc_out_i t
inner join dim.dwd_dim_date_f as dd
on date_format(t.stock_deal_time,'yyyy-MM-dd') = dd.trade_date
inner join dim.dwd_dim_store_i as bs
on t.alloc_out_store_no=bs.store_no and bs.dt = '2023-11-22'
;
-- 门店要货表(3张表降维拉宽):dwd_order_store_require_i
insert overwrite table dwd.dwd_order_store_require_i partition (dt)
select
date_format(t.confirm_time,'yyyy-MM-dd') as trade_date
,dd.week_trade_date
,dd.month_trade_date
,hour(t.confirm_time) as hourly
,case when minute(t.confirm_time)>=0 and minute(t.confirm_time)<15 then 1
when minute(t.confirm_time)>=15 and minute(t.confirm_time)<30 then 2
when minute(t.confirm_time)>=30 and minute(t.confirm_time)<45 then 3
when minute(t.confirm_time)>=45 and minute(t.confirm_time)<60 then 4
end as `quarter`
,hour(t.confirm_time)*4 + case when minute(t.confirm_time)>=0 and minute(t.confirm_time)<15 then 1
when minute(t.confirm_time)>=15 and minute(t.confirm_time)<30 then 2
when minute(t.confirm_time)>=30 and minute(t.confirm_time)<45 then 3
when minute(t.confirm_time)>=45 and minute(t.confirm_time)<60 then 4
end as quarters
,t.id
,t.uid
,t.order_id
,t.order_source
,t.store_no
,bs.store_name
,bs.store_sale_type
,bs.store_type_code
,bs.worker_num
,bs.store_area
,bs.city_id
,bs.city_name
,bs.region_code
,bs.region_name
,bs.is_day_clear
,t.goods_no
,t.goods_name
,t.dc_no
,t.dc_name
,t.vendor_no
,t.vendor_name
,t.group_no
,t.require_price
,t.require_qty
,t.create_time
,t.send_time
,t.collect_require_order_id
,t.require_type_code
,t.is_online
,t.confirm_time
,t.is_canceled
,t.sync_time
,t.is_urgent
,t.original_order_price
,date_format(t.confirm_time,'yyyy-MM-dd') as dt
from ods.ods_order_store_require_i t
inner join dim.dwd_dim_date_f as dd
on date_format(t.confirm_time,'yyyy-MM-dd') = dd.trade_date
inner join dim.dwd_dim_store_i as bs
on t.store_no=bs.store_no and bs.dt = '2023-11-22'
;
二、DWM层开发
2-1 DWM层建表
2-2 DWM层表处理
商品销售明细表
合并线下和线上数据
使用union进行行关联
-- DWM层商品销售明细表
-- 线下门店销售数据
select
trade_date_time,
trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
parent_store_no,
store_no,
store_name,
store_sale_type,
store_type_code,
worker_num,
store_area,
city_id,
city_name,
region_code,
region_name,
is_day_clear,
trade_type,
source_type,
source_type_name,
sale_type,
is_online_order,
member_type,
is_balance_consume,
order_type,
express_type,
parent_order_no,
order_no,
create_time,
is_cancel,
cancel_time,
last_update_time,
zt_id,
member_id,
card_no,
share_user_id,
commission_amount,
is_tuan_head,
store_leader_id,
order_group_no,
first_category_no,
first_category_name,
second_category_no,
second_category_name,
third_category_no,
third_category_name,
goods_no,
goods_name,
supply_team,
dc_no,
dc_name,
group_no,
group_name,
trade_mode_id,
vendor_id,
contract_no,
is_clean,
is_daily_clear,
sale_qty,
sale_amount,
dis_amount,
sale_cost,
balance_amount,
order_total_amount,
order_discount_amount,
order_paid_amount,
dt
from dwd.dwd_sale_store_sale_dtl_i
union all
-- 线上商城数据
select
trade_date_time,
trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
parent_store_no,
store_no,
store_name,
store_sale_type,
store_type_code,
worker_num,
store_area,
city_id,
city_name,
region_code,
region_name,
is_day_clear,
trade_type,
source_type,
source_type_name,
sale_type,
is_online_order,
member_type,
is_balance_consume,
order_type,
express_type,
parent_order_no,
order_no,
create_time,
is_cancel,
cancel_time,
last_update_time,
zt_id,
member_id,
card_no,
share_user_id,
commission_amount,
is_tuan_head,
store_leader_id,
order_group_no,
first_category_no,
first_category_name,
second_category_no,
second_category_name,
third_category_no,
third_category_name,
goods_no,
goods_name,
supply_team,
dc_no,
dc_name,
group_no,
group_name,
trade_mode_id,
vendor_id,
contract_no,
is_clean,
is_daily_clear,
sale_qty,
sale_amount,
dis_amount,
sale_cost,
balance_amount,
order_total_amount,
order_discount_amount,
order_paid_amount,
dt
from dwd.dwd_sold_shop_order_dtl_i;
门店销售
| 字段 | 描述 | 计算 |
|---|---|---|
| is_online_order | 线上订单 | 默认 0 |
| order_type | 配送类型（真正的订单类型由业务类型来决定）：1-及时送；2-隔日送；3-自提单；4-线下单 | 默认 4 |
| express_type | 配送方式：0-三方平台配送；1-自配送；2-快递；3-自提；4-线下 | 默认 4 |
| create_time | 创建时间 | trade_date_time |
| is_cancel | 是否取消 1或0 | 使用trade_type 判断，结算类型(0.正常交易,1.赠品发放,2.退货,4.培训,5.取消交易) |
| cancel_time | 取消时间 | trade_type判断, 类型为5使用last_update_time，其他为 ‘’ |
| is_tuan_head | 是否为团长订单 | 默认 0 |
| store_leader_id | 团长id | 默认 0 |
| order_group_no | 团单号 | 默认 0 |
| order_total_amount | 订单总金额(平摊) | 使用 sale_amount |
| order_discount_amount | 订单优惠金额 | dis_amount |
| order_paid_amount | 实付金额(平摊) | sale_amount |
insert overwrite table dwm.dwm_sold_goods_sold_dtl_i partition(dt)
select
trade_date_time,
date(trade_date),
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
parent_store_no,
store_no,
store_name,
store_sale_type,
store_type_code,
worker_num,
store_area,
city_id,
city_name,
region_code,
region_name,
is_day_clear,
trade_type,
source_type,
source_type_name,
sale_type,
0 as is_online_order,
member_type,
is_balance_consume,
4 as order_type,
4 as express_type,
parent_order_no,
order_no,
trade_date_time as create_time,
if(
trade_type = 5,
1,
0
) as is_cancel,
if(
trade_type = 5,
last_update_time,
''
) as cancel_time,
last_update_time,
zt_id,
member_id,
card_no,
cast(share_user_id as int) as share_user_id,
commission_amount,
0 as is_tuan_head,
0 as store_leader_id,
'0' as order_group_no,
first_category_no,
first_category_name,
second_category_no,
second_category_name,
third_category_no,
third_category_name,
goods_no,
goods_name,
supply_team,
dc_no,
dc_name,
group_no,
group_name,
trade_mode_id,
vendor_id,
contract_no,
is_clean,
is_daily_clear,
sale_qty,
sale_amount,
dis_amount,
sale_cost,
balance_amount,
sale_amount as order_total_amount,
dis_amount as order_discount_amount,
sale_amount as order_paid_amount,
dt
from dwd.dwd_sale_store_sale_dtl_i
商城销售
| 字段 | 描述 | 计算 |
|---|---|---|
| trade_date_time | 使用 complete_time | |
| parent_store_no | store_no | |
| source_type | 交易类型 | 如果是10,20,30,40,41,70修改为2，判断是否是50，如果是50修改 为3否则为7 |
| source_type_name | 交易来源名称 | 判断source_type字段，如果是10,20,30,40,41,70是’三方平台’，判断是否是50，如果是50修改 为传智鲜商城否则为黑马优选 |
| sale_type | 销售类型 1.实物,2.代客,3.优选小程序,4.离店,5.传智鲜小程序,6.第三方平台,7.其他,8.大客户 | 判断source_type字段，如果是10,20,30,40,41,70修改为6，判断是否是50，如果是50修改 为5否则为3 |
| is_online_order | 是否为线上单:0否,1是 | 默认 1 |
| member_type | 会员类型:0非会员,1线上会员,2实体卡会员 | 默认 1 |
| order_no | 订单编号 | order_id |
| member_id | 会员ID | buyer_id |
| cancel_time | 取消时间 | 时间戳转化 yyyy-MM-dd HH:mm:ss |
| last_update_time | pos_sale表最后一次更新时间 | 时间戳转化 |
| create_time | 创建时间 | 时间戳转化 |
| card_no | 卡号 | 默认 ‘’ |
| share_user_id | 分享人用户ID | 默认 0 |
| is_clean | 商品是否日清:0否,1是 | 默认 0 |
| is_daily_clear | 商品是否参加日清活动:0否,1是 | 默认 0 |
| order_group_no | 团单号 | 修改类型 int |
select
cast(complete_time as timestamp) as trade_date_time,
date(trade_date) as trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
store_no as parent_store_no,
store_no,
store_name,
store_sale_type,
store_type_code,
worker_num,
store_area,
city_id,
city_name,
region_code,
region_name,
is_day_clear,
trade_type,
if(
source_type in(10,20,30,40,41,70),
2,
if(
source_type = 50,
3,
7
)
) as source_type,
if(
source_type in(10,20,30,40,41,70),
'三方平台',
if(
source_type = 50,
'传智鲜商城',
'黑马优选'
)
) as source_type_name,
if(
source_type in(10,20,30,40,41,70),
6,
if(
source_type = 50,
5,
3
)
) as sale_type,
1 as is_online_order,
1 as member_type,
is_balance_consume,
order_type,
express_type,
parent_order_no,
order_id as order_no,
create_time,
is_cancel,
date_format(cancel_time,'yyyy-MM-dd HH:mm:ss') as cancel_time,
date_format(last_update_time,'yyyy-MM-dd HH:mm:ss') as last_update_time,
zt_id,
buyer_id as member_id,
'' as card_no,
0 as share_user_id,
commission_amount,
is_tuan_head,
store_leader_id,
order_group_no,
first_category_no,
first_category_name,
second_category_no,
second_category_name,
third_category_no,
third_category_name,
goods_no,
goods_name,
supply_team,
dc_no,
dc_name,
group_no,
group_name,
trade_mode_id,
vendor_id,
contract_no,
0 as is_clean,
0 as is_daily_clear,
sale_qty,
sale_amount,
dis_amount,
sale_cost,
balance_amount,
order_total_amount,
order_discount_amount,
order_paid_amount,
dt
from dwd.dwd_sold_shop_order_dtl_i;