1、新零售行业数据分析背景介绍
作为新零售行业从业者，最常见的问题就是以何种粒度在数据仓库存储订单交易数据表。常见的粒度有三类：（1）以商品SKU为粒度存储订单数；（2）以商品SPU为粒度存储订单数据；（3）以交易订单为粒度存储订单数据。其中，第3种方式以交易订单为粒度存储订单数据，更加适合交易明细数据表，对于数据仓库存储方式不是很合适。因此，本文重点阐述如何将SKU粒度数据表与SPU粒度数据表进行融合。
为什么会出现两种不同粒度的数据表存储方式呢？ 通常有两种决定因素：第1种因素与零售行业数据分析业务特性有关系；第2种因素与数据量大小有关系。
零售行业数据分析业务特性
如果需要分析近期的商品销售情况，决策者往往会需要看到商品最细粒度的销量，商品最细粒度就是SKU。对于零售行业，SKU = 货号(SPU) + 规格/颜色。哪款货号商品最畅销？ 具体某款货号的哪种规格/颜色最畅销？ 今年流行什么款式、什么装饰、什么颜色？ 这些商品销售数据分析都需要细化到商品SKU粒度。
如果需要分析最近五年、十年甚至历史累计商品销售情况，决策者往往不需要细化到SKU粒度，通常SPU(货号)粒度足够满足分析需求。
数据量大小
分析近期的商品销售情况，比如最近15天、最近一个月、最近三个月等情况，汇总得到的SKU粒度销售数据量往往小于百万条，利用常规BI工具即可快速分析得到结果。
分析最近几年、或者历史累计销售情况，如果还是采用SKU粒度存储数据，数据量往往过亿、甚至数十亿，常规BI工具很难快速得到分析结果。
以上从行业特性、数据量大小两个角度分析为什么同一家公司往往存在SKU、SPU两种粒度的商品销售数据表。除此之外，往往还存在库存采用SPU粒度、销售采用SKU粒度；统配采用SPU粒度、发货采用SKU粒度等诸多情况。
但是，在数据仓库分析领域，很多时候需要将不同粒度的数据表进行融合，进而得到统一的分析结果。比如：将本年商品销售数据 与 历史累计商品销售数据 进行融合分析； 将本年商品销售数据 与 本年库存数据进行关联分析。 这些情况往往都需要将SKU粒度数据表 与 SPU粒度数据表做融合分析，或者做关联分析。
本文介绍一种简单的数据预处理方法，将SKU粒度数据表与SPU粒度数据表融合到一张表，希望思路可以提供给大家参考。
2、 SKU和SPU粒度数据融合方法
假设有两张数据表： sku_table 和 spu_table，分别按照sku粒度和spu粒度存储商品销售数据。
2.1 查看表结构
--建表语句
create table if not exists test_project.sku_table (
shopno string comment '门店代码',
prodno string comment '产品代码',
sizeno string comment '规格代码',
rtam_lastyy Double comment '某款sku商品的本年销售金额'
) comment 'sku粒度的数据表' PARTITIONED BY (ds string comment '分区日期') lifecycle 200;
create table if not exists test_project.spu_table (
shopno string comment '门店代码',
prodno string comment '产品代码',
rtam_lishileiji Double comment '某款spu粒度商品的历史累计销售金额'
) comment ' spu粒度的数据表' PARTITIONED BY (ds string comment '分区日期') lifecycle 200;注意：上述数据表中，prodno就是货号，也就是常说的SPU， prodno与sizeno组合就成为SKU。
对于sku_table，以 shopno + prodno + sizeno为联合主键表示1条数据。1条商品销售数据，以 “门店 + 商品货号代码 + 规格/颜色”记录1条销售数据，这种情况常见于新零售行业的数据仓库。
对于spu_table，以shopno + prodno 为联合主键表示1条数据。1条商品销售数据，以 “门店 + 商品货号代码”记录1条销售数据，这种情况常见于新零售行业的数据仓库。
2.2 查看数据
select * from test_project.sku_table where ds = '20200417';
select * from test_project.spu_table where ds = '20200417';
2.3 将sku_table 与 spu_table融合到一张表
首先建立一张数据表，用于存储融合之后的数据：
create table if not exists test_project.sku_spu_merge_table (
shopno string comment '门店代码',
prodno string comment '产品代码',
sizeno string comment '规格代码',
rtam_lastyy Double comment '某款sku商品的本年销售金额',
rtam_lishileiji Double comment '某款spu粒度商品的历史累计销售金额'
) comment '将sku粒度数据(本年销售表)与spu粒度数据(历史累计销售表)整合到一张表' PARTITIONED BY (ds string comment '分区日期') lifecycle 200;然后将两张表导入到test_project.sku_spu_merge_table数据表：
insert overwrite table
test_project.sku_spu_merge_table partition (ds = '20200417')
select
COALESCE (v1.shopno, v2.shopno) as shopno,
COALESCE (v1.prodno, v2.prodno) as prodno,
v1.sizeno,
v1.rtam_lastyy,
v2.rtam_lishileiji
from
(
select
shopno,
prodno,
sizeno,
rtam_lastyy,
row_number() over ( partition by shopno,prodno order by sizeno) size_order
from
test_project.sku_table
where ds = '20200417'
) v1
full outer join
(
select
shopno,
prodno,
rtam_lishileiji,
1 join_order
from
test_project.spu_table
where ds = '20200417'
) v2
on (v1.shopno = v2.shopno) and (v1.prodno = v2.prodno) and (v1.size_order = v2.join_order);上述SQL的原理稍作解释：
1) 首先利用row_number() over ( partition by shopno,prodno order by sizeno) 函数对sku_table 按照shopno,prodno（也就是 门店+货号）进行分组，然后按照sizeno进行排序，将排序结果赋值给size_order；
2) 然后将sku_table新增最后一列join_order，赋予1这个固定值；
3) 最后将sku_table 和 spu_table 做full outer join，join的条件是 (v1.shopno = v2.shopno) and (v1.prodno = v2.prodno) and (v1.size_order = v2.join_order)。特别是条件：v1.size_order = v2.join_order，也就是将sku_table按照shopno,prodno分组排序之后的第1行(也就是size_order=1) 与 spu_table 进行对应(也就是join_order=1)进行关联。
最终得到的效果如下所示：
最终将sku_table 和 spu_table 融合到一张数据表。
对于原来SKU粒度的sku_table，保持原样；
对于SPU粒度的spu_table，当(shopno + prodno) 与sku_table相同时，也就是 （同一个门店 + 同一个货号商品），第1个sizeno放真实的历史累计销售数据；后面的历史累计销售数据被置为空。
上述做法的好处在于：对数据做融合分析(比如求和、求平均值)，不会因为spu的数据重复计算而出现计算错误。
3、 进一步分析
本文给出的sku粒度和spu粒度的数据融合方法只是一个参考，读者可以利用本文的思路继续拓展其他方法。比如：将SPU粒度的销售数据按照该SPU下面有多少个SKU，先做平均值，然后赋值给融合之后的数据表。另外一个思路，可以先将SPU粒度的数据表与订单明细表做关联分析，得到SPU下面每一个SKU的销售数据，然后再和SK粒度的数据表做融合。具体采用哪种方案，需要读者根据实际业务情况、项目需求决定。
