新零售資料中台：如何将SKU和SPU粒度資料表融合到一張表

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粒度的資料表做融合。具體采用哪種方案，需要讀者根據實際業務情況、項目需求決定。