数仓概念
存储数据的仓库, 主要用于存储过去历史发生过的数据
面向主题, 对数据进行统计分析的操作, 从而能够对未来提供决策支持
既不生产数据, 也不消耗数据, 数据来源于各个数据源
四大特征
- 面向主题
- 集成性
- 非易失性(稳定性)
- 时变性需求说明
# 销售需求
分析线上线下的销售情况，包括销售、取消、退款的金额、成本、单量、SKU以及活动的情况
# 会员需求
主要统计会员的注册、消费、充值、余额情况。注意线上会员也可以在线下消费，使用相同的手机号即可
# 供应链需求
计算库存的数量、金额、SKU、周转、动销、损耗数量和金额、
盘点差异以及要货、收货、配送、退货、退配、调入、调出、系统调整的数量和金额
# 商品需求
对商城的访问日志进行分析，主要是流量数据和交易数据数仓架构
数据核心业务表
项目一共有有31张表，其中包含8张维度表，23张业务核心表
数仓工具-DataX
DataX 阿里推出的一个异构数据源离线同步工具
实现包括关系型数据库(MySQL、Oracle等)、HDFS、Hive、ODPS、HBase、
FTP等各种异构数据源之间稳定高效的数据同步功能数仓建模
事实表: 指的主题,要统计的主题是什么, 对应事实就是什么, 而主题所对应的表, 其实事实表
维度表: 当对事实表进行统计分析的时候, 可能需要关联一些其他表进行辅助, 这些表其实就是维度表
星型模型
只有一个事实表,周围围绕了多张维度表,维度表与维度表没有任何关联
雪花模型
只有一个事实表,周围围绕了多张维度表, 维度表可以接着关联其他的维度表
星座模型
有多个事实表,事实表周围围绕了多张维度表, 在条件吻合的情况下, 事实表之间是可以共享维度表数仓建设方案
数仓核销主题开发
DWD层开发
1.1 门店销售明细宽表
1- 销售日期基于门店销售信息表中的库存日期处理
2- 母店编码和门店编码: 母店编码为门店编码, 门店编码使用销售门店编码如果没有, 使用门店编码
3- 结算类型: 判断trade_id,如果为1 返回0(正常交易), 为2返回2, 如果为3,4返回5
4- 销售渠道如果为null, 返回1
5- 会员类型: 如果以OL- / SF- 开头的为线上会员, 否则为线下会员, 为空为null的为非会员
6- 判断是否为余额消费, 基于是否可以关联上余额支付的表
7- 母订单编号: 判断商品销售金额是否小于0, 如果小于采用source_order_sn(退款单据ID),如果大于采用parent_order_sn. 如果为NULL, 选择order_no
8- 针对item(商品序号),sort(序号),cashier_no,cashier_name,trade_mode_id,share_user_id,commission_amount,zt_id,member_id 如果为null, 设置为0
9- 支付时间选择pay_date
10- 最后修改时间, 选择库存时间
11- 余额金额(平摊金额)计算: 首先判断单据金额是否等于0 , 则结果为0, 如果不是, 判断是否有余额支付金额, 如果有, 使用余额金额 * 商品销售金额 / 单据金额
12- sale_type(销售类型): 基于上述结果表的source_type判断, 当为1返回1, 当为9返回5 , 当为 4,5,6,7,8返回6. 当为11返回8 ,否则返回7
13- tag商品标识为null, 返回4
14- dc_no(采购仓库编码) 为null 返回-1
15- dc_name(采购仓库名称为null) 返回其他仓
16- group_no(采购柜组编码)为null, 返回-1
17- group_name(采购柜组名称)为null, 返回其他柜组
18- 当 vendor_id、is_clear 为null, 返回0with t4 as(
select
date_format(t2.deal_date,'yyyy-MM-dd HH:mm:ss') as trade_date_time,
date_format(t2.trade_date,'yyyy-MM-dd') as trade_date ,
hour(t2.deal_date) as hourly,
minute(t2.deal_date) as minute,
t2.store_no as parent_store_no,
coalesce(t2.sale_store_no,t2.store_no) as store_no,
coalesce(t2.source_type,1) as source_type,
if(
t2.member_type like 'OL-%' OR t2.member_type like 'SF-%',
1,
if(
t2.member_type = '' OR t2.member_type is null,
0,
2
)
) as member_type,
if(
t3.order_no is null,
0,
1
) as is_balance_consume,
if(
t2.trade_id = 1,
0,
if(
t2.trade_id = 2,
2,
5
)
) as trade_type,
if(
t1.sale_amount < 0,
t2.source_order_sn,
if(
t1.sale_amount >= 0,
t2.parent_order_sn,
t1.order_no
)
) as parent_order_no,
t2.order_no,
t2.pos_no,
t2.ser_id,
coalesce(t1.item,0) as item,
coalesce(t1.`sort`,0) as sort,
date_format(t2.pay_date,'yyyy-MM-dd HH:mm:ss') as pay_time,
date_format(t2.deal_date,'yyyy-MM-dd HH:mm:ss') as last_update_time,
coalesce(t2.cashier_no,0) as cashier_no,
coalesce(t2.cashier_name,0) as cashier_name,
coalesce(t2.member_center_sn,0) as zt_id,
coalesce(t2.member_id,0) as member_id,
t2.card_no,
t1.goods_no,
if(
t1.combination_flag = 1,
1,
0
) as is_component,
coalesce(t1.trade_mode_id,0) as trade_mode_id,
t1.vendor_id,
t1.contract_no,
t1.is_daily_clear,
coalesce(t1.share_user_id,0) as share_user_id,
coalesce(t1.commission_amount,0) as commission_amount,
t1.sale_qty,
t1.sale_amount,
t1.dis_amount,
t1.sale_cost,
if(
t2.total_pay_amount = 0,
0,
if(
t3.pay_amount is null,
0,
cast(t3.pay_amount * t1.sale_amount / t2.total_pay_amount as decimal(27,2))
)
)as balance_amount
from (select * from ods.ods_sale_store_sale_dtl_i where combination_flag != 2 and offset_flag = 0) t1
join ods.ods_sale_store_sale_info_i t2 on t2.order_no = t1.order_no
left join (select * from ods.ods_sale_store_sale_pay_i where pay_type_id = '201') t3 on t2.order_no = t3.order_no
)
insert overwrite table dwd.dwd_sale_store_sale_dtl_i partition(dt)
select
t4.trade_date_time,
t4.trade_date,
t5.week_trade_date,
t5.month_trade_date,
t4.hourly,
case
when t4.minute between 0 and 14 then 1
when t4.minute between 15 and 29 then 2
when t4.minute between 30 and 44 then 3
when t4.minute between 45 and 59 then 4
end as quarter,
(
t4.hourly * 4
+
case
when t4.minute between 0 and 14 then 1
when t4.minute between 15 and 29 then 2
when t4.minute between 30 and 44 then 3
when t4.minute between 45 and 59 then 4
end
) as quarters,
t4.parent_store_no,
t4.store_no,
t7.store_name,
t7.store_sale_type,
t7.store_type_code,
t7.worker_num,
t7.store_area,
t7.city_id,
t7.city_name,
t7.region_code,
t7.region_name,
t7.is_day_clear,
t4.trade_type,
t4.source_type,
t6.source_type_name,
case
when t4.source_type = 1 then 1
when t4.source_type = 9 then 5
when t4.source_type in (4,5,6,7,8) then 6
when t4.source_type = 11 then 8
else 7
end as sale_type,
t4.member_type,
t4.is_balance_consume,
t4.parent_order_no,
t4.order_no,
t4.pos_no,
t4.ser_id,
t4.item,
t4.sort,
t4.pay_time,
t4.last_update_time,
t4.cashier_no,
t4.cashier_name,
t4.share_user_id,
t4.commission_amount,
t4.zt_id,
t4.member_id,
t4.card_no,
t8.first_category_no,
t8.first_category_name,
t8.second_category_no,
t8.second_category_name,
t8.third_category_no,
t8.third_category_name,
t4.goods_no,
t8.goods_name,
t8.spec,
t4.is_component,
coalesce(t9.tag,4) as supply_team,
coalesce(t9.dc_no,-1) as dc_no,
coalesce(t9.dc_name,'其他仓') as dc_name,
coalesce(t9.group_no,-1) as group_no,
coalesce(t9.group_name,'其他柜组') as group_name ,
t4.trade_mode_id,
coalesce(t4.vendor_id,0) as vendor_id,
t4.contract_no,
coalesce(t9.is_clear,0) as is_clean,
t4.is_daily_clear,
t4.sale_qty,
t4.sale_amount,
t4.dis_amount,
t4.sale_cost,
t4.balance_amount,
date_sub(current_date(),1) as write_time,
t4.trade_date as dt
from t4
left join dim.dwd_dim_date_f t5 on t4.trade_date = t5.trade_date
left join dim.dwd_dim_source_type_map_i t6 on t6.dt = '2023-09-24' and t4.source_type = t6.original_source_type
left join dim.dwd_dim_store_i t7 on t7.dt = '2023-09-24' and t4.store_no = t7.store_no
left join dim.dwd_dim_goods_i t8 on t8.dt = '2023-09-24' and t4.goods_no = t8.goods_no
left join dim.dwd_dim_store_goods_i t9 on t9.dt = '2023-09-24' and t9.goods_no = t4.goods_no;1.2线上余额支付明细
这个表的目的是为了计算dwd_sold_shop_order_dtl_i(商城核销明细表)，
因为是核销维度，有可能有的订单从下单到签收经历了很长时间，在判断是否是余额销售的时候，
就需要取到当时下单对应的支付单的情况（因为支付单是按下单时间分区的），
不可能把全量数据都拿过来做关联，所以这里就需要一个表来只记录余额支付的明细，
这样就极大的降低了数据量。
处理方案: 在门店销售支付表在where条件中设置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 = '余额支付';
注意: 在增量的SQL中, 需要添加dt字段获取上一天的数据1.3商城订单表
订单下单完成后，会经历各种状态变化，只要更新就会同步过来更新后的数据。
因为项目有核销主题、售卖主题，在做具体的分析的时候，
希望核销主题的表是按照核销时间（完成时间）进行分区的，
售卖主题的表是按照售卖时间（下单时间）进行分区的。
所以，这里需要对shop_order表进行处理，根据complete_time和create_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,
date_format(complete_time,'yyyy-MM-dd') as dt
from ods.ods_sale_shop_order_i where complete_time is not null; -- where dt = '2023-09-27' and date_format(complete_time,'yyyy-MM-dd') = '2023-09-27';1.4订单明细表
与商城订单表一样, 只获取核销后的订单数据, 以及是最终完结的数据,
在售卖主题中, 获取下单时的信息数据即可-- 商城订单明细表
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,
date_format(complete_time,'yyyy-MM-dd') as dt
from ods.ods_sale_shop_order_item_i where complete_time is not null; 1.5商城核销明细表
这个表是将商城的订单表、订单明细表、退款表、退款明细表、支付表合起来形成的核销明细表。
因为退款单要获取原单的记录，所以退款表还要与订单表进行关联来取一些字段。
比较庆幸的一点是，在退款的时候，订单表也会进行更新，这样在关联订单的时候，
只要在对应的那个分区里就可以找到对应的记录，不用再去遍历多个分区来找。-- 商城核销明细宽表:
with t5 as (
select
t1.complete_time,
date_format(t1.complete_time,'yyyy-MM-dd') as trade_date,
hour(t1.complete_time) as hourly,
minute(t1.complete_time) as minute,
t1.parent_order_no,
t1.order_id,
t1.trade_type,
t1.is_split,
t1.platform_id,
t1.tid,
t1.source_type,
t1.source_name,
t1.order_type,
t1.express_type,
t1.order_status,
t1.order_status_desc,
t1.pay_type,
if(
t3.order_no is not null,
1,
0
) as is_balance_consume,
t1.store_no,
t1.order_create_time,
t1.order_pay_time,
t1.create_time,
if(
t1.is_cancel = 1,
5,
0
) as is_cancel,
t1.cancel_time,
t1.cancel_reason,
t1.last_update_time,
t1.buyer_id,
t1.buyer_phone,
t1.buyer_remark,
t1.r_name,
t1.r_tel,
t1.r_province,
t1.r_city,
t1.r_district,
t1.r_address,
t1.r_zipcode,
t1.is_tuan_head,
t1.store_leader_id,
t1.order_group_no,
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, -- 结算金额
t2.goods_no,
t2.weight,
t2.quantity,
t2.unit,
t2.sale_qty,
t2.disp_price,
t2.pay_price,
t2.sale_amount,
t2.dis_amount,
t2.sale_cost,
t2.sale_type,
t2.activity_plat_city_goods_id,
t2.activity_type,
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(
t1.real_paid_amount = 0,
0,
t1.real_paid_amount * t2.sale_amount / t1.real_product_amount
) as order_paid_amount, -- 实付金额(平摊)
if(
t1.real_paid_amount = 0,
0,
if(
t3.pay_amount is null,
0,
t3.pay_amount * t2.sale_amount / t1.real_product_amount
)
) as balance_amount, -- 余额支付金额 (平摊) 首先判断单据金额是否等于0 , 则结果为0, 如果不是, 判断是否有余额支付金额, 如果有, 使用余额金额 * 商品销售金额 / 单据金额
t2.trade_mode_id,
t2.vendor_id,
t2.contract_no,
t1.dt
from dwd.dwd_sold_shop_order_i t1
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 t1.order_id = t3.order_no
union all
-- 步骤二:
select
t1.create_time as complete_time,
date_format(t1.create_time,'yyyy-MM-dd') as trade_date,
hour(t1.create_time) as hourly,
minute(t1.create_time) as minute,
t3.parent_order_no,
t3.order_id,
if(
t1.cancel_time is not null,
'5',
'2'
) as trade_type,
t3.is_split,
t3.platform_id,
t3.tid,
t3.source_type,
t3.source_name,
t3.order_type,
t3.express_type,
t3.order_status,
t3.order_status_desc,
t3.pay_type,
if(
t1.order_no is not null,
1,
0
) as is_balance_consume,
t1.store_no,
t3.order_create_time,
t3.order_pay_time,
t3.create_time,
if(
t3.is_cancel = 1,
5,
0
) as is_cancel,
t1.cancel_time,
t3.cancel_reason,
t3.last_update_time,
t3.buyer_id,
t3.buyer_phone,
t3.buyer_remark,
t3.r_name,
t3.r_tel,
t3.r_province,
t3.r_city,
t3.r_district,
t3.r_address,
t3.r_zipcode,
t3.is_tuan_head,
t1.store_leader_id,
t3.order_group_no,
-(t3.commision_amount * t2.amount / t3.real_product_amount) as commission_amount, -- 抽佣金额
-(t3.settle_amount * t2.amount / t3.real_product_amount) as settle_amount, -- 结算金额
t2.goods_no,
0 as weight,
-t2.quantity,
'' as unit,
-t2.qty,
t2.amount / t2.quantity as disp_price,
t2.amount / t2.quantity as pay_price,
-t2.amount ,
0 as dis_amount,
-t2.cost as sale_cost,
1 as sale_type,
t2.activity_plat_city_goods_id,
t2.activity_type,
-t2.amount as order_total_amount, -- 订单总金额(平摊)
0 as order_discount_amount, -- 订单优惠金额 = 商家承担优惠金额 + 平台补贴金额 (平摊)
-t2.amount as order_paid_amount, -- 实付金额(平摊)
- if(
t3.real_paid_amount = 0,
0,
if(
t4.pay_amount is null,
0,
t4.pay_amount * t2.amount / t3.real_product_amount
)
) as balance_amount, -- 余额支付金额 (平摊) 首先判断单据金额是否等于0 , 则结果为0, 如果不是, 判断是否有余额支付金额, 如果有, 使用余额金额 * 商品销售金额 / 单据金额
t2.trade_mode_id,
t2.vendor_id,
t2.contract_no,
t1.dt
from ods.ods_sale_shop_refund_i t1
join ods.ods_sale_shop_refund_item_i t2 on t1.refund_no = t2.refund_no
left join dwd.dwd_sold_shop_order_i t3 on t1.order_no = t3.order_id
left join dwd.dwd_sale_shop_sale_balance_pay_i t4 on t4.order_no = t1.order_no
)
insert overwrite table dwd.dwd_sold_shop_order_dtl_i partition (dt)
select
t5.complete_time,
t5.trade_date,
t6.week_trade_date,
t6.month_trade_date,
t5.hourly,
case
when t5.minute between 0 and 14 then 1
when t5.minute between 15 and 29 then 2
when t5.minute between 30 and 44 then 3
when t5.minute between 45 and 59 then 4
end as quarter,
(
t5.hourly * 4
+
case
when t5.minute between 0 and 14 then 1
when t5.minute between 15 and 29 then 2
when t5.minute between 30 and 44 then 3
when t5.minute between 45 and 59 then 4
end
) as quarters,
t5.parent_order_no,
t5.order_id,
t5.trade_type,
t5.is_split,
t5.platform_id,
t5.tid,
t5.source_type,
t5.source_name,
t5.order_type,
t5.express_type,
t5.order_status,
t5.order_status_desc,
t5.pay_type,
t5.is_balance_consume,
t5.store_no,
t7.store_name,
t7.store_sale_type,
t7.store_type_code,
t7.worker_num,
t7.store_area,
t7.city_id,
t7.city_name,
t7.region_code,
t7.region_name,
t7.is_day_clear,
t5.order_create_time,
t5.order_pay_time,
t5.create_time,
t5.is_cancel,
t5.cancel_time,
t5.cancel_reason,
t5.last_update_time,
t10.zt_id,
t5.buyer_id,
t5.buyer_phone,
t5.buyer_remark,
t5.r_name,
t5.r_tel,
t5.r_province,
t5.r_city,
t5.r_district,
t5.r_address,
t5.r_zipcode,
t5.is_tuan_head,
t5.store_leader_id,
t5.order_group_no,
t5.commission_amount,
t5.settle_amount,
t8.first_category_no,
t8.first_category_name,
t8.second_category_no,
t8.second_category_name,
t8.third_category_no,
t8.third_category_name,
t5.goods_no,
t8.goods_name,
t5.weight,
t5.quantity,
t5.unit,
t5.sale_qty,
t5.disp_price,
t5.pay_price,
t5.sale_amount,
t5.dis_amount,
t5.sale_cost,
t5.sale_type,
t5.activity_plat_city_goods_id,
t5.activity_type,
t5.order_total_amount,
t5.order_discount_amount,
t5.order_paid_amount,
t5.balance_amount,
coalesce(t9.tag,4) as supply_team,
coalesce(t9.dc_no,-1) as dc_no,
coalesce(t9.dc_name,'其他仓') as dc_name,
coalesce(t9.group_no,-1) as group_no,
coalesce(t9.group_name,'其他柜组') as group_name ,
t5.trade_mode_id,
coalesce(t5.vendor_id,0) as vendor_id,
t5.contract_no,
t5.dt
from t5
left join dim.dwd_dim_date_f t6 on t5.trade_date = t6.trade_date
left join dim.dwd_dim_store_i t7 on t5.store_no = t7.store_no and t7.dt = '2023-09-24'
left join dim.dwd_dim_goods_i t8 on t5.goods_no = t8.goods_no and t8.dt = '2023-09-24'
left join dim.dwd_dim_store_goods_i t9 on t8.goods_no = t9.goods_no and t7.store_no = t9.store_no and t9.dt = '2023-09-24'
left join ods.ods_mem_member_union_i t10 on t5.buyer_id = t10.member_id;1.6门店库调表
库调即库存调整，类型有日清活动、盘点更正、报损/溢等。对于这些情况，
都是广义上的损耗。为了计算损耗，需要对库调表进行加宽处理insert overwrite table dwd.dwd_stock_store_stock_adj_i partition (dt)
select
t2.trade_date,
t2.week_trade_date,
t2.month_trade_date,
hour(t1.stock_deal_time) as hourly,
case
when minute(t1.stock_deal_time) between 0 and 14 then 1
when minute(t1.stock_deal_time) between 15 and 29 then 2
when minute(t1.stock_deal_time) between 30 and 44 then 3
when minute(t1.stock_deal_time) between 45 and 59 then 4
end as quarter,
(
hour(t1.stock_deal_time) * 4
+
case
when minute(t1.stock_deal_time) between 0 and 14 then 1
when minute(t1.stock_deal_time) between 15 and 29 then 2
when minute(t1.stock_deal_time) between 30 and 44 then 3
when minute(t1.stock_deal_time) between 45 and 59 then 4
end
) as quarters,
t1.id,
t1.uid,
t1.order_id,
t1.order_source,
t1.store_no,
t1.store_name,
t3.store_sale_type,
t3.store_type_code,
t3.worker_num,
t3.store_area,
t3.city_id,
t3.city_name,
t3.region_code,
t3.region_name,
t3.is_day_clear,
t1.goods_no,
t1.goods_name,
t1.adj_type_big,
t1.adj_type_small,
t1.adj_reason_big,
t1.adj_reason_small,
t1.adj_qty,
t1.adj_price,
t1.adj_amount,
t1.create_time,
t1.stock_deal_time,
t1.sync_time,
t1.vendor_no,
t1.vendor_name,
t2.trade_date as dt
from ods.ods_stock_store_stock_adj_i t1
left join dim.dwd_dim_date_f t2 on
date_format(t1.stock_deal_time,'yyyy-MM-dd') = t2.trade_date
left join dim.dwd_dim_store_i t3 on t3.dt = '2023-09-24'
and t1.store_no = t3.store_no;1.7门店收货单
门店的收货情况，包括单号，时间，商品信息，数量和金额等。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 and bs.dt= '2023-09-24';1.8门店退货单
门店退给供应商的商品情况，包括单号、门店、时间、商品信息、数量、金额、原因等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 and bs.dt = '2023-09-24'
;1.9门店退配单
门店退给大仓的商品情况，包括单号、门店、时间、商品信息、数量、金额、原因等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 and bs.dt = '2023-09-24'
;1.10门店调入单
从其他门店调入本店的单据信息，包括时间、单号、商品、门店、商品信息、数量和金额等。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-09-24'
;1.11门店调出单
从本店调入其他门店的单据信息，包括时间、单号、商品、门店、商品信息、数量和金额等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-09-24'
;1.12门店要货单
门店的要货信息，包括时间、单号、门店、商品信息、数量和金额、要货方式、状态等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-09-24'
;DWM开发
2.1商品销售明细表
作为核销主题的小宽表，要能涵盖线上线下各渠道的销售数据。这个逻辑比较简单，
将dwd_sale_store_sale_dtl_i与dwd_sold_shop_order_dtl_i两表进行合并即可-- DWM层: 销售明细宽表(将线上和线下的两部分数据进行union all 合并)
insert overwrite table dwm.dwm_sold_goods_sold_dtl_i partition(dt)
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,
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_sale_store_sale_dtl_i -- where dt = '2023-09-27'
union all
select
complete_time as trade_date_time,
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,
date_format(create_time,'yyyy-MM-dd HH:mm:ss') as 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;2.2门店商品损耗刻表
门店商品损耗刻表，粒度为门店商品刻，包含时间、门店、商品、品类等信息，便于dws/ads层使用-- 计算损耗
with t1 as (
select
trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
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,
goods_no,
sum(adj_qty) as loss_qty,
sum(adj_amount) as loss_amount
from dwd.dwd_stock_store_stock_adj_i where adj_type_big in ('日清','报损/溢','人工盘点','盘点更正','周清')
group by
trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
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,
goods_no
)
insert overwrite table dwm.dwm_stock_store_goods_loss_quarter_i partition (dt)
select
t1.trade_date,
t1.week_trade_date,
t1.month_trade_date,
t1.hourly,
t1.quarter,
t1.quarters,
t1.store_no,
t1.store_name,
t1.store_sale_type,
t1.store_type_code,
t1.worker_num,
t1.store_area,
t1.city_id,
t1.city_name,
t1.region_code,
t1.region_name,
t1.is_day_clear,
t2.first_category_no,
t2.first_category_name,
t2.second_category_no,
t2.second_category_name,
t2.third_category_no,
t2.third_category_name,
t1.goods_no,
t2.goods_name,
t2.is_clear as is_clean,
t1.loss_qty,
t1.loss_amount,
trade_date as dt
from t1 left join dim.dwd_dim_store_goods_i t2
on t1.store_no = t2.store_no and t1.goods_no = t2.goods_no and t2.dt = '2023-09-24';2.3门店商品收货刻表
净收货=收货-退货-退配+调入-调出，所以需要综合五张dwd的order主题的表。
这里为了便于其他分析，不仅存净收货指标，也把收货、退货、退配、调入、调出这几个指标分别存上。with t1 as (
select
trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
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,
goods_no,
receive_qty,
receive_price as receive_amount,
0 as return_vendor_qty,
0 as return_vendor_amount,
0 as return_dc_qty,
0 as return_dc_amount,
0 as allocation_in_qty,
0 as allocation_in_amount,
0 as allocation_out_qty,
0 as allocation_out_amount
from dwd.dwd_order_store_receive_i
union all
select
trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
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,
goods_no,
0 as receive_qty,
0 as receive_amount,
return_qty as return_vendor_qty,
return_price as return_vendor_amount,
0 as return_dc_qty,
0 as return_dc_amount,
0 as allocation_in_qty,
0 as allocation_in_amount,
0 as allocation_out_qty,
0 as allocation_out_amount
from dwd.dwd_order_store_return_to_vendor_i
union all
select
trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
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,
goods_no,
0 as receive_qty,
0 as receive_amount,
0 as return_vendor_qty,
0 as return_vendor_amount,
return_qty as return_dc_qty,
return_price as return_dc_amount,
0 as allocation_in_qty,
0 as allocation_in_amount,
0 as allocation_out_qty,
0 as allocation_out_amount
from dwd.dwd_order_store_return_to_dc_i
union all
select
trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
alloc_in_store_no as store_no,
alloc_in_store_name as store_name,
alloc_in_store_sale_type as store_sale_type,
alloc_in_store_type_code as store_type_code,
alloc_in_worker_num as worker_num,
alloc_in_store_area as store_area,
alloc_in_city_id as city_id,
alloc_in_city_name as city_name,
alloc_in_region_code as region_code,
alloc_in_region_name as region_name,
alloc_in_is_day_clear as is_day_clear,
goods_no,
0 as receive_qty,
0 as receive_amount,
0 as return_vendor_qty,
0 as return_vendor_amount,
0 as return_dc_qty,
0 as return_dc_amount,
alloc_qty as allocation_in_qty,
alloc_amount as allocation_in_amount,
0 as allocation_out_qty,
0 as allocation_out_amount
from dwd.dwd_order_store_alloc_in_i
union all
select
trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
alloc_out_store_no as store_no,
alloc_out_store_name as store_name,
alloc_out_store_sale_type as store_sale_type,
alloc_out_store_type_code as store_type_code,
alloc_out_worker_num as worker_num,
alloc_out_store_area as store_area,
alloc_out_city_id as city_id,
alloc_out_city_name as city_name,
alloc_out_region_code as region_code,
alloc_out_region_name as region_name,
alloc_out_is_day_clear as is_day_clear,
goods_no,
0 as receive_qty,
0 as receive_amount,
0 as return_vendor_qty,
0 as return_vendor_amount,
0 as return_dc_qty,
0 as return_dc_amount,
0 as allocation_in_qty,
0 as allocation_in_amount,
alloc_qty as allocation_out_qty,
alloc_amount as allocation_out_amount
from dwd.dwd_order_store_alloc_out_i
),
t2 as (
select
trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
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,
goods_no,
sum(receive_qty) as receive_qty,
sum(receive_qty * receive_amount) as receive_amount,
sum(return_vendor_qty) as return_vendor_qty,
sum(return_vendor_qty*return_vendor_amount) as return_vendor_amount,
sum(return_dc_qty) as return_dc_qty,
sum(return_dc_qty * return_dc_amount) as return_dc_amount,
sum(allocation_in_qty) as allocation_in_qty,
sum(allocation_in_amount) as allocation_in_amount,
sum(allocation_out_qty) as allocation_out_qty,
sum(allocation_out_amount) as allocation_out_amount
from t1
group by
trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
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,
goods_no
)
insert overwrite table dwm.dwm_order_store_goods_receipt_quarter_i partition (dt)
select
t2.trade_date,
t2.week_trade_date,
t2.month_trade_date,
t2.hourly,
t2.quarter,
t2.quarters,
t2.store_no,
t2.store_name,
t2.store_sale_type,
t2.store_type_code,
t2.worker_num,
t2.store_area,
t2.city_id,
t2.city_name,
t2.region_code,
t2.region_name,
t2.is_day_clear,
t3.first_category_no,
t3.first_category_name,
t3.second_category_no,
t3.second_category_name,
t3.third_category_no,
t3.third_category_name,
t2.goods_no,
t3.goods_name,
t3.is_clear as is_clean,
t2.receive_qty,
t2.receive_amount,
t2.return_vendor_qty,
t2.return_vendor_amount,
t2.return_dc_qty,
t2.return_dc_amount,
t2.allocation_in_qty,
t2.allocation_in_amount,
t2.allocation_out_qty,
t2.allocation_out_amount,
t2.receive_qty + return_vendor_qty + return_dc_qty + allocation_in_qty + allocation_out_qty as receipt_qty,
t2.receive_amount + t2.return_vendor_amount + t2.return_dc_amount + t2.allocation_in_amount + t2.allocation_out_amount as receipt_amount,
(t2.receive_amount + t2.return_vendor_amount + t2.return_dc_amount + t2.allocation_in_amount + t2.allocation_out_amount) * 0.95 as receipt_cost,
t2.trade_date as dt
from t2 left join dim.dwd_dim_store_goods_i t3 on t3.dt = '2023-09-24' and t2.store_no = t3.store_no and t2.goods_no = t3.goods_no;
2.4门店商品要货刻表
要货的小宽表，粒度也是门店商品刻，基于这个粒度进行轻量聚合。-- 门店商品要货刻表
with t1 as (
select
trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
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,
goods_no,
sum(require_qty) as require_qty,
sum(require_price * require_qty ) as require_amount
from dwd.dwd_order_store_require_i
where require_type_code = 2 and is_canceled = 0 and collect_require_order_id is not null and collect_require_order_id != ''
group by
trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
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,
goods_no
)
insert overwrite table dwm.dwm_order_store_goods_require_quarter_i partition (dt)
select
t1.trade_date,
t1.week_trade_date,
t1.month_trade_date,
t1.hourly,
t1.quarter,
t1.quarters,
t1.store_no,
t1.store_name,
t1.store_sale_type,
t1.store_type_code,
t1.worker_num,
t1.store_area,
t1.city_id,
t1.city_name,
t1.region_code,
t1.region_name,
t1.is_day_clear,
t2.first_category_no,
t2.first_category_name,
t2.second_category_no,
t2.second_category_name,
t2.third_category_no,
t2.third_category_name,
t1.goods_no,
t2.goods_name,
t2.is_clear as is_clean,
t1.require_qty,
t1.require_amount,
t1.trade_date as dt
from t1 left join dim.dwd_dim_store_goods_i t2
on t1.store_no = t2.store_no and t1.goods_no = t2.goods_no and t2.dt = '2023-09-24'
DWS开发
3.1门店商品分析刻表
构建一张DWS层的大宽表，包含销售、损耗、收货、要货等信息。
维度字段，包含4张DWM表中的共有字段；指标字段，包含需求中所要求的字段以及拓展的字段。
将四张dwm层的表进行合并即可。需要注意的是，要先进行初步聚合，再去union all，可以提高计算效率-- DWS层: 门店 商品的分析刻表
-- 第一步: 先计算销售相关的数据
select
-- 时间维度
trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
-- 门店维度
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,
-- 商品维度
first_category_no,
first_category_name,
second_category_no,
second_category_name,
third_category_no,
third_category_name,
goods_no,
goods_name,
is_clean,
-- 指标
-- 销售单量/数量/金额
count( if(trade_type = 0,parent_order_no,null)) - count(if(trade_type = 5,parent_order_no,null)) as order_num,
sum(sale_qty) as sale_qty,
sum(sale_amount) as sale_amount,
-- 折扣金额
sum(dis_amount) as dis_amount,
-- 销售成本
sum(sale_cost) as sale_cost,
-- 余额支付金额
sum(if(is_balance_consume = 1,balance_amount,0)) as balance_amount,
-- 取消商品销售金额
sum(if(trade_type = 5,sale_amount,0)) as cancel_sale_amount,
-- -- 退款商品销售金额
sum(if(trade_type = 2,sale_amount,0)) as refund_sale_amount,
-- -- 线上线下单量
count( if(trade_type = 0 and is_online_order = 1,parent_order_no,null)) - count(if(trade_type = 5 and is_online_order = 1,parent_order_no,null)) as online_order_num,
count( if(trade_type = 0 and is_online_order = 0,parent_order_no,null)) - count(if(trade_type = 5 and is_online_order = 0,parent_order_no,null)) as offline_order_num,
-- -- 线上线下销售数量
sum(if(is_online_order = 1,sale_qty,0)) as online_sale_qty,
sum(if(is_online_order = 0,sale_qty,0)) as offline_sale_qty,
-- -- 线上线下销售金额
sum(if(is_online_order = 1,sale_amount,0)) as online_sale_amount,
sum(if(is_online_order = 0,sale_amount,0)) as offline_sale_amount,
-- 线上线下销售成本
sum(if(is_online_order = 1,sale_cost,0)) as online_sale_cost,
sum(if(is_online_order = 0,sale_cost,0)) as offline_sale_cost
from dwm.dwm_sold_goods_sold_dtl_i
group by
-- 时间维度
trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
-- 门店维度
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,
-- 商品维度
first_category_no,
first_category_name,
second_category_no,
second_category_name,
third_category_no,
third_category_name,
goods_no,
goods_name,
is_clean;
-- 第二步 计算 损耗数量 和 损耗金额
select
-- 时间维度
trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
-- 门店维度
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,
-- 商品维度
first_category_no,
first_category_name,
second_category_no,
second_category_name,
third_category_no,
third_category_name,
goods_no,
goods_name,
is_clean,
sum(loss_qty) as loss_qty,
sum(loss_amount) as loss_amount
from dwm.dwm_stock_store_goods_loss_quarter_i
group by
-- 时间维度
trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
-- 门店维度
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,
-- 商品维度
first_category_no,
first_category_name,
second_category_no,
second_category_name,
third_category_no,
third_category_name,
goods_no,
goods_name,
is_clean;
-- 第三步: 收货数量 和 收货金额
select
-- 时间维度
trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
-- 门店维度
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,
-- 商品维度
first_category_no,
first_category_name,
second_category_no,
second_category_name,
third_category_no,
third_category_name,
goods_no,
goods_name,
is_clean,
sum(receipt_qty) as receipt_qty,
sum(receipt_amount) as receipt_amount
from dwm.dwm_order_store_goods_receipt_quarter_i
group by
-- 时间维度
trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
-- 门店维度
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,
-- 商品维度
first_category_no,
first_category_name,
second_category_no,
second_category_name,
third_category_no,
third_category_name,
goods_no,
goods_name,
is_clean;
-- 第四步: 要货数量 和 要货金额
select
-- 时间维度
trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
-- 门店维度
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,
-- 商品维度
first_category_no,
first_category_name,
second_category_no,
second_category_name,
third_category_no,
third_category_name,
goods_no,
goods_name,
is_clean,
sum(require_qty) as require_qty,
sum(require_amount) as require_amount
from dwm.dwm_order_store_goods_require_quarter_i
group by
-- 时间维度
trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
-- 门店维度
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,
-- 商品维度
first_category_no,
first_category_name,
second_category_no,
second_category_name,
third_category_no,
third_category_name,
goods_no,
goods_name,
is_clean;
-- 第五步: 进行合并 FULL JOIN / Union all 均可以
with t1 as (
-- 第一步: 先计算销售相关的数据
select
-- 时间维度
trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
-- 门店维度
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,
-- 商品维度
first_category_no,
first_category_name,
second_category_no,
second_category_name,
third_category_no,
third_category_name,
goods_no,
goods_name,
is_clean,
-- 指标
-- 销售单量/数量/金额
count( if(trade_type = 0,parent_order_no,null)) - count(if(trade_type = 5,parent_order_no,null)) as order_num,
sum(sale_qty) as sale_qty,
sum(sale_amount) as sale_amount,
-- 折扣金额
sum(dis_amount) as dis_amount,
-- 销售成本
sum(sale_cost) as sale_cost,
-- 余额支付金额
sum(if(is_balance_consume = 1,balance_amount,0)) as balance_amount,
-- 取消商品销售金额
sum(if(trade_type = 5,sale_amount,0)) as cancel_sale_amount,
-- -- 退款商品销售金额
sum(if(trade_type = 2,sale_amount,0)) as refund_sale_amount,
-- -- 线上线下单量
count( if(trade_type = 0 and is_online_order = 1,parent_order_no,null)) - count(if(trade_type = 5 and is_online_order = 1,parent_order_no,null)) as online_order_num,
count( if(trade_type = 0 and is_online_order = 0,parent_order_no,null)) - count(if(trade_type = 5 and is_online_order = 0,parent_order_no,null)) as offline_order_num,
-- -- 线上线下销售数量
sum(if(is_online_order = 1,sale_qty,0)) as online_sale_qty,
sum(if(is_online_order = 0,sale_qty,0)) as offline_sale_qty,
-- -- 线上线下销售金额
sum(if(is_online_order = 1,sale_amount,0)) as online_sale_amount,
sum(if(is_online_order = 0,sale_amount,0)) as offline_sale_amount,
-- 线上线下销售成本
sum(if(is_online_order = 1,sale_cost,0)) as online_sale_cost,
sum(if(is_online_order = 0,sale_cost,0)) as offline_sale_cost,
0 as loss_qty,
0 as loss_amount,
0 as receipt_qty,
0 as receipt_amount,
0 as require_qty,
0 as require_amount
from dwm.dwm_sold_goods_sold_dtl_i
group by
-- 时间维度
trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
-- 门店维度
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,
-- 商品维度
first_category_no,
first_category_name,
second_category_no,
second_category_name,
third_category_no,
third_category_name,
goods_no,
goods_name,
is_clean
union all
-- 第二步 计算 损耗数量 和 损耗金额
select
-- 时间维度
trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
-- 门店维度
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,
-- 商品维度
first_category_no,
first_category_name,
second_category_no,
second_category_name,
third_category_no,
third_category_name,
goods_no,
goods_name,
is_clean,
0 as order_num,
0 as sale_qty,
0 as sale_amount,
0 as dis_amount,
0 as sale_cost,
0 as balance_amount,
0 as cancel_sale_amount,
0 as refund_sale_amount,
0 as online_order_num,
0 as offline_order_num,
0 as online_sale_qty,
0 as offline_sale_qty,
0 as online_sale_amount,
0 as offline_sale_amount,
0 as online_sale_cost,
0 as offline_sale_cost,
sum(loss_qty) as loss_qty,
sum(loss_amount) as loss_amount,
0 as receipt_qty,
0 as receipt_amount,
0 as require_qty,
0 as require_amount
from dwm.dwm_stock_store_goods_loss_quarter_i
group by
-- 时间维度
trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
-- 门店维度
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,
-- 商品维度
first_category_no,
first_category_name,
second_category_no,
second_category_name,
third_category_no,
third_category_name,
goods_no,
goods_name,
is_clean
union all
-- 第三步: 收货数量 和 收货金额
select
-- 时间维度
trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
-- 门店维度
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,
-- 商品维度
first_category_no,
first_category_name,
second_category_no,
second_category_name,
third_category_no,
third_category_name,
goods_no,
goods_name,
is_clean,
0 as order_num,
0 as sale_qty,
0 as sale_amount,
0 as dis_amount,
0 as sale_cost,
0 as balance_amount,
0 as cancel_sale_amount,
0 as refund_sale_amount,
0 as online_order_num,
0 as offline_order_num,
0 as online_sale_qty,
0 as offline_sale_qty,
0 as online_sale_amount,
0 as offline_sale_amount,
0 as online_sale_cost,
0 as offline_sale_cost,
0 as loss_qty,
0 as loss_amount,
sum(receipt_qty) as receipt_qty,
sum(receipt_amount) as receipt_amount,
0 as require_qty,
0 as require_amount
from dwm.dwm_order_store_goods_receipt_quarter_i
group by
-- 时间维度
trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
-- 门店维度
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,
-- 商品维度
first_category_no,
first_category_name,
second_category_no,
second_category_name,
third_category_no,
third_category_name,
goods_no,
goods_name,
is_clean
union all
-- 第四步: 要货数量 和 要货金额
select
-- 时间维度
trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
-- 门店维度
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,
-- 商品维度
first_category_no,
first_category_name,
second_category_no,
second_category_name,
third_category_no,
third_category_name,
goods_no,
goods_name,
is_clean,
0 as order_num,
0 as sale_qty,
0 as sale_amount,
0 as dis_amount,
0 as sale_cost,
0 as balance_amount,
0 as cancel_sale_amount,
0 as refund_sale_amount,
0 as online_order_num,
0 as offline_order_num,
0 as online_sale_qty,
0 as offline_sale_qty,
0 as online_sale_amount,
0 as offline_sale_amount,
0 as online_sale_cost,
0 as offline_sale_cost,
0 as loss_qty,
0 as loss_amount,
0 as receipt_qty,
0 as receipt_amount,
sum(require_qty) as require_qty,
sum(require_amount) as require_amount
from dwm.dwm_order_store_goods_require_quarter_i
group by
-- 时间维度
trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
-- 门店维度
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,
-- 商品维度
first_category_no,
first_category_name,
second_category_no,
second_category_name,
third_category_no,
third_category_name,
goods_no,
goods_name,
is_clean
)
insert overwrite table dws.dws_goods_store_goods_statistics_quarter_i partition (dt)
select
-- 时间维度
trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
-- 门店维度
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,
-- 商品维度
first_category_no,
first_category_name,
second_category_no,
second_category_name,
third_category_no,
third_category_name,
goods_no,
goods_name,
is_clean,
sum(order_num) as order_num ,
cast(sum(sale_qty) as decimal(27,3)) as sale_qty ,
cast(sum(sale_amount) as decimal(27,2)) as sale_amount ,
cast(sum(dis_amount) as decimal(27,2)) as dis_amount ,
cast(sum(sale_cost) as decimal(27,2)) as sale_cost ,
cast(sum(balance_amount) as decimal(27,2)) as balance_amount ,
cast(sum(cancel_sale_amount) as decimal(27,2)) as cancel_sale_amount ,
cast(sum(refund_sale_amount) as decimal(27,2)) as refund_sale_amount ,
sum(online_order_num)as online_order_num ,
sum(offline_order_num) as offline_order_num ,
cast(sum(online_sale_qty) as decimal(27,3)) as online_sale_qty ,
cast(sum(offline_sale_qty) as decimal(27,3)) as offline_sale_qty ,
cast(sum(online_sale_amount) as decimal(27,2)) as online_sale_amount ,
cast(sum(offline_sale_amount) as decimal(27,2)) as offline_sale_amount ,
cast(sum(online_sale_cost) as decimal(27,2)) as online_sale_cost ,
cast(sum(offline_sale_cost) as decimal(27,2)) as offline_sale_cost ,
cast(sum(loss_qty) as decimal(27,3)) as loss_qty ,
cast(sum(loss_amount) as decimal(27,2)) as loss_amount ,
cast(sum(receipt_qty) as decimal(27,3)) as receipt_qty ,
cast(sum(receipt_amount) as decimal(27,2)) as receipt_amount ,
cast(sum(require_qty) as decimal(27,3)) as require_qty ,
cast(sum(require_amount)as decimal(27,2)) as require_amount,
trade_date as dt
from t1
group by
-- 时间维度
trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
-- 门店维度
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,
-- 商品维度
first_category_no,
first_category_name,
second_category_no,
second_category_name,
third_category_no,
third_category_name,
goods_no,
goods_name,
is_clean;3.2门店经营分析刻表
构建一张DWS层的大宽表，包含维度字段，以及销售、损耗、收货、要货、会员、支付等全部指标-- DWS 门店经营分析刻表
-- 第一步: 先将之前已经算过的指标进行上卷统计得到新的结果
select
-- 时间维度
trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
-- 门店维度
store_no,
store_name,
store_sale_type,
store_type_code,
worker_num,
store_area,
city_id,
region_code,
city_name,
region_name,
is_day_clear,
sum(order_num) as order_num,
sum(sale_qty) as sale_qty,
sum(sale_amount) as sale_amount,
sum(dis_amount) as dis_amount,
sum(sale_cost) as sale_cost,
sum(balance_amount) as balance_amount,
sum(cancel_sale_amount) as cancel_sale_amount,
sum(refund_sale_amount) as refund_sale_amount,
sum(online_order_num) as online_order_num,
sum(offline_order_num) as offline_order_num,
sum(online_sale_amount) as online_sale_amount,
sum(offline_sale_amount) as offline_sale_amount,
sum(online_sale_cost) as online_sale_cost,
sum(offline_sale_cost) as offline_sale_cost,
sum(loss_amount) as loss_amount,
sum(receipt_amount) as receipt_amount,
sum(require_amount) as require_amount
from dws.dws_goods_store_goods_statistics_quarter_i
group by
-- 时间维度
trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
-- 门店维度
store_no,
store_name,
store_sale_type,
store_type_code,
worker_num,
store_area,
city_id,
region_code,
city_name,
region_name,
is_day_clear;
-- 第二步: 计算会员以及其他的指标
select
-- 时间维度
trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
-- 门店维度
store_no,
store_name,
store_sale_type,
store_type_code,
worker_num,
store_area,
city_id,
region_code,
city_name,
region_name,
is_day_clear,
count( if( trade_type = 0 and member_type = 1,parent_order_no,null)) - count( if( trade_type = 5 and member_type = 1,parent_order_no,null)) as ol_mem_order_num,
count( if( trade_type = 0 and member_type = 2,parent_order_no,null)) - count( if( trade_type = 5 and member_type = 2,parent_order_no,null)) as vip_mem_order_num,
sum( if( member_type = 1,sale_amount,0)) as ol_mem_sale_amount,
sum( if( member_type = 2,sale_amount,0)) as vip_mem_sale_amount,
sum( if( member_type = 1,sale_cost,0)) as ol_mem_sale_cost,
sum( if( member_type = 2,sale_cost,0)) as vip_mem_sale_cost,
count(distinct if(member_type = 1 and trade_type = 0, member_id,null)) as ol_mem_trade_num,
count(distinct if(member_type = 2 and trade_type = 0, member_id,null)) as vip_mem_trade_num,
sum(if(is_balance_consume = 1,balance_amount,0) ) as balance_sale_amount,
count(if(is_balance_consume = 1 and trade_type = 0,parent_order_no,null)) - count(if(is_balance_consume = 1 and trade_type = 5,parent_order_no,null)) as balance_order_num,
sum(if(is_balance_consume = 1,sale_cost,0) ) as balance_sale_cost,
count(distinct if(is_balance_consume = 1 and trade_type = 0,member_id,null)) as balance_people_num
from dwm.dwm_sold_goods_sold_dtl_i
group by
-- 时间维度
trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
-- 门店维度
store_no,
store_name,
store_sale_type,
store_type_code,
worker_num,
store_area,
city_id,
region_code,
city_name,
region_name,
is_day_clear;
-- 第三步: 用第一张表 left join 第二张表即可
with t1 as (
select
-- 时间维度
trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
-- 门店维度
store_no,
store_name,
store_sale_type,
store_type_code,
worker_num,
store_area,
city_id,
region_code,
city_name,
region_name,
is_day_clear,
sum(order_num) as order_num,
sum(sale_qty) as sale_qty,
sum(sale_amount) as sale_amount,
sum(dis_amount) as dis_amount,
sum(sale_cost) as sale_cost,
sum(balance_amount) as balance_amount,
sum(cancel_sale_amount) as cancel_sale_amount,
sum(refund_sale_amount) as refund_sale_amount,
sum(online_order_num) as online_order_num,
sum(offline_order_num) as offline_order_num,
sum(online_sale_amount) as online_sale_amount,
sum(offline_sale_amount) as offline_sale_amount,
sum(online_sale_cost) as online_sale_cost,
sum(offline_sale_cost) as offline_sale_cost,
sum(loss_amount) as loss_amount,
sum(receipt_amount) as receipt_amount,
sum(require_amount) as require_amount
from dws.dws_goods_store_goods_statistics_quarter_i
group by
-- 时间维度
trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
-- 门店维度
store_no,
store_name,
store_sale_type,
store_type_code,
worker_num,
store_area,
city_id,
region_code,
city_name,
region_name,
is_day_clear
),
t2 as (
select
-- 时间维度
trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
-- 门店维度
store_no,
store_name,
store_sale_type,
store_type_code,
worker_num,
store_area,
city_id,
region_code,
city_name,
region_name,
is_day_clear,
count( if( trade_type = 0 and member_type = 1,parent_order_no,null)) - count( if( trade_type = 5 and member_type = 1,parent_order_no,null)) as ol_mem_order_num,
count( if( trade_type = 0 and member_type = 2,parent_order_no,null)) - count( if( trade_type = 5 and member_type = 2,parent_order_no,null)) as vip_mem_order_num,
sum( if( member_type = 1,sale_amount,0)) as ol_mem_sale_amount,
sum( if( member_type = 2,sale_amount,0)) as vip_mem_sale_amount,
sum( if( member_type = 1,sale_cost,0)) as ol_mem_sale_cost,
sum( if( member_type = 2,sale_cost,0)) as vip_mem_sale_cost,
count(distinct if(member_type = 1 and trade_type = 0, member_id,null)) as ol_mem_trade_num,
count(distinct if(member_type = 2 and trade_type = 0, member_id,null)) as vip_mem_trade_num,
sum(if(is_balance_consume = 1,balance_amount,0) ) as balance_sale_amount,
count(if(is_balance_consume = 1 and trade_type = 0,parent_order_no,null)) - count(if(is_balance_consume = 1 and trade_type = 5,parent_order_no,null)) as balance_order_num,
sum(if(is_balance_consume = 1,sale_cost,0) ) as balance_sale_cost,
count(distinct if(is_balance_consume = 1 and trade_type = 0,member_id,null)) as balance_people_num
from dwm.dwm_sold_goods_sold_dtl_i
group by
-- 时间维度
trade_date,
week_trade_date,
month_trade_date,
hourly,
quarter,
quarters,
-- 门店维度
store_no,
store_name,
store_sale_type,
store_type_code,
worker_num,
store_area,
city_id,
region_code,
city_name,
region_name,
is_day_clear
)
insert overwrite table dws.dws_store_manage_statistics_quarter_i partition (dt)
select
-- 时间维度
t1.trade_date,
t1.week_trade_date,
t1.month_trade_date,
t1.hourly,
t1.quarter,
t1.quarters,
-- 门店维度
t1.store_no,
t1.store_name,
t1.store_sale_type,
t1.store_type_code,
t1.worker_num,
t1.store_area,
t1.city_id,
t1.region_code,
t1.city_name,
t1.region_name,
t1.is_day_clear,
t1.order_num,
t1.sale_qty,
t1.sale_amount,
t1.dis_amount,
t1.sale_cost,
t1.balance_amount,
t1.cancel_sale_amount,
t1.refund_sale_amount,
t1.online_order_num,
t1.offline_order_num,
t1.online_sale_amount,
t1.offline_sale_amount,
t1.online_sale_cost,
t1.offline_sale_cost,
t1.loss_amount,
t1.receipt_amount,
t1.require_amount,
t2.ol_mem_order_num,
t2.vip_mem_order_num,
t2.ol_mem_sale_amount,
t2.vip_mem_sale_amount,
t2.ol_mem_sale_cost,
t2.vip_mem_sale_cost,
t2.ol_mem_trade_num,
t2.vip_mem_trade_num,
t2.balance_sale_amount,
t2.balance_order_num,
t2.balance_sale_cost,
t2.balance_people_num,
t1.trade_date as dt
from t1 left join t2 on t1.trade_date = t2.trade_date and t1.quarters = t2.quarters and t1.store_no = t2.store_noADS层开发
根据需求分析进行上卷统计即可
略
至此,离线数仓的核销主题开发工作结束,其它主题亦如此,剩余就是优化问题
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
