DataWorks：進階：商品訂單暢銷類目分析

1. 在工作流程編排頁面中，滑鼠移至上方至workshop節點上，單擊開啟節點。

2. 在workshop節點編輯頁面右側單擊調度配置，配置相關參數。以下為本教程所需配置的關鍵參數，未說明參數保持預設即可。

   調度配置參數	說明
   調度類型	本教程配置為空跑調度。
   調度資源群組	本教程配置為建立資源群組並綁定工作空間時建立的Serverless資源群組。
   節點依賴配置	由於workshop為初始節點，無上遊依賴，此時可以單擊使用工作空間根節點，由工作空間根節點觸發工作流程執行。 工作空間根節點命名為：工作空間名_root。

3. 在節點工具列單擊儲存，儲存節點。

1. 在工作流程編排頁面中，滑鼠移至上方至ods_item_info節點上，單擊開啟節點。

2. 配置同步鏈路網路與資源。

   參數	描述
   資料來源	資料來源：MySQL。 資料來源名稱：MySQL_Source。
   我的資源群組	選擇建立資源群組並綁定工作空間時購買的Serverless資源群組。
   資料去向	資料去向：MaxCompute(ODPS)。 資料來源名稱：選擇建立並綁定MaxCompute計算資源時綁定的MaxCompute計算資源，此處樣本為MaxCompute_Source。

3. 單擊下一步，配置同步任務。

   1. 配置資料來源與去向

      說明

      表中未說明的參數保持預設值即可。

      配置地區	參數	配置說明
      資料來源	表	item_info。
      資料去向	表	單擊一鍵產生目標表結構快速建立MaxCompute表。將如下建表語句粘貼至建表語句地區，單擊建立表。該表用於接收資料來源端的商品資訊。 建表SQL CREATE TABLE IF NOT EXISTS ods_item_info( `id` BIGINT COMMENT '', `cate_id` BIGINT COMMENT '', `cate_name` STRING COMMENT '', `commodity_id` BIGINT COMMENT '', `commodity_name` STRING COMMENT '', `desc_path` STRING COMMENT '', `duration` BIGINT COMMENT '', `features` STRING COMMENT '', `gmt_create` DATETIME COMMENT '', `gmt_modified` DATETIME COMMENT '', `is_deleted` BIGINT COMMENT '', `is_virtual` STRING COMMENT '', `item_id` BIGINT COMMENT '', `item_status` BIGINT COMMENT '', `last_offline_time` DATETIME COMMENT '', `last_online_quantity` BIGINT COMMENT '', `last_online_time` DATETIME COMMENT '', `pict_url` STRING COMMENT '', `reserve_price` DECIMAL(38,18) COMMENT '', `secure_trade_ems_post_fee` DECIMAL(38,18) COMMENT '', `secure_trade_fast_post_fee` DECIMAL(38,18) COMMENT '', `secure_trade_ordinary_post_fee` DECIMAL(38,18) COMMENT '', `shop_id` BIGINT COMMENT '', `shop_nick` STRING COMMENT '', `sub_title` STRING COMMENT '', `title` STRING COMMENT '' ) COMMENT '' PARTITIONED BY (pt STRING) lifecycle 36500;
      	分區資訊	本教程填入${bizdate}，用於在後續測試階段為bizdate參數賦值常量，在調度執行時為bizdate參數動態賦值。Data Studio支援的變數格式及配置方法，請參見調度參數。

   2. 確認欄位對應及通道控制。

      DataWorks通過配置源端與目標端欄位對應關係，實現源端指定欄位資料寫入目標端指定欄位，同時支援設定任務並發數、髒資料策略等。本教程髒資料策略配置為不容忍髒資料，其他配置保持預設。更多資訊，請參見嚮導模式配置。

4. 在節點工具列單擊儲存，儲存節點。

1. 在工作流程編排頁面中，滑鼠移至上方至ods_trade_order節點上，單擊開啟節點。

2. 配置同步鏈路網路與資源。

   參數	描述
   資料來源	資料來源：MySQL。 資料來源名稱：MySQL_Source。
   我的資源群組	選擇建立資源群組並綁定工作空間時購買的Serverless資源群組。
   資料去向	資料去向：MaxCompute(ODPS)。 資料來源名稱：選擇建立並綁定MaxCompute計算資源時綁定的MaxCompute計算資源，此處樣本為MaxCompute_Source。

3. 單擊下一步，配置同步任務。

   1. 配置資料來源與去向

      說明

      表中未說明的參數保持預設值即可。

      配置地區	參數	配置說明
      資料來源	表	trade_order
      資料去向	表	單擊一鍵產生目標表結構快速建立MaxCompute表。將如下建表語句粘貼至建表語句地區，單擊建立表。該表用於接收資料來源端的商品資訊。 建表SQL CREATE TABLE IF NOT EXISTS ods_trade_order( `id` BIGINT COMMENT '', `biz_type` BIGINT COMMENT '', `buy_amount` BIGINT COMMENT '', `buyer_id` BIGINT COMMENT '', `buyer_memo` STRING COMMENT '', `buyer_nick` STRING COMMENT '', `end_time` DATETIME COMMENT '', `gmt_create` DATETIME COMMENT '', `gmt_modified` DATETIME COMMENT '', `ip` STRING COMMENT '', `is_parent` BIGINT COMMENT '', `is_sub` BIGINT COMMENT '', `item_id` BIGINT COMMENT '', `item_price` DECIMAL(38,18) COMMENT '', `logistics_status` BIGINT COMMENT '', `memo` STRING COMMENT '', `parent_order_id` BIGINT COMMENT '', `pay_status` BIGINT COMMENT '', `pay_time` DATETIME COMMENT '', `seller_memo` STRING COMMENT '', `shop_id` BIGINT COMMENT '', `status` BIGINT COMMENT '', `sub_order_id` BIGINT COMMENT '', `total_fee` DECIMAL(38,18) COMMENT '' ) COMMENT '' PARTITIONED BY (pt STRING) lifecycle 36500;
      	分區資訊	本教程填入${bizdate}，用於在後續測試階段為bizdate參數賦值常量，在調度執行時為bizdate參數動態賦值。Data Studio支援的變數格式及配置方法，請參見調度參數。

   2. 確認欄位對應及通道控制。

      DataWorks通過配置源端與目標端欄位對應關係，實現源端指定欄位資料寫入目標端指定欄位，同時支援設定任務並發數、髒資料策略等。本教程髒資料策略配置為不容忍髒資料，其他配置保持預設。更多資訊，請參見嚮導模式配置。

4. 在節點工具列單擊儲存，儲存節點。

基於ods_item_info表，處理商品維度資料，產出商品基礎資訊維度資料表dim_item_info。

1. 在工作流程編排頁面中，滑鼠移至上方至dim_item_info節點上，單擊開啟節點。

2. 將如下代碼粘貼至節點編輯頁面。

   CREATE TABLE IF NOT EXISTS dim_item_info (
       gmt_modified                   STRING COMMENT '商品最後修改日期',
       gmt_create                     STRING COMMENT '商品建立時間',
       item_id                        BIGINT COMMENT '商品數字ID',
       title                          STRING COMMENT '商品標題',
       sub_title                      STRING COMMENT '商品子標題',
       pict_url                       STRING COMMENT '主圖URL',
       desc_path                      STRING COMMENT '商品描述的路徑',
       item_status                    BIGINT COMMENT '商品狀態1：確認通過0：未確認通過',
       last_online_time               DATETIME COMMENT '最近一次開始銷售時間，商品上架時間',
       last_offline_time              DATETIME COMMENT '銷售結束時間，表示一個銷售周期的結束，僅作用於拍賣商品',
       duration                       BIGINT COMMENT '有效期間，銷售周期，只有兩個值，7天或14天',
       reserve_price                  DOUBLE COMMENT '當前價格',
       secure_trade_ordinary_post_fee DOUBLE COMMENT '平郵費用',
       secure_trade_fast_post_fee     DOUBLE COMMENT '快遞費用',
       secure_trade_ems_post_fee      DOUBLE COMMENT 'EMS郵費',
       last_online_quantity           BIGINT COMMENT '商品最近一次上架時的庫存數量',
       features                       STRING COMMENT '商品特徵',
       cate_id                        BIGINT COMMENT '商品葉子類目ID',
       cate_name                      STRING COMMENT '商品葉子類目名稱',
       commodity_id                   BIGINT COMMENT '品類ID',
       commodity_name                 STRING COMMENT '品類名稱',
       is_virtual                     STRING COMMENT '是否虛擬商品',
       shop_id                        BIGINT COMMENT '商家ID',
       shop_nick                      STRING COMMENT '商家NICK',
       is_deleted                     BIGINT COMMENT '類目是否刪除'
   )
   COMMENT '商品基礎資訊維度資料表'
   PARTITIONED BY (pt STRING COMMENT '業務日期, yyyymmdd')
   LIFECYCLE 365;
   
   
   -- 插入資料到 dim_item_info 表
   INSERT OVERWRITE TABLE dim_item_info PARTITION(pt='${bizdate}')
   SELECT
       gmt_create,
       gmt_modified,
       item_id,
       title,
       sub_title,
       pict_url,
       desc_path,
       item_status,
       last_online_time,
       last_offline_time,
       duration,
       cast(reserve_price as DOUBLE),
       cast(secure_trade_ordinary_post_fee as DOUBLE),
       cast(secure_trade_fast_post_fee as DOUBLE),
       cast(secure_trade_ems_post_fee as DOUBLE),
       last_online_quantity,
       features,
       cate_id,
       cate_name,
       commodity_id,
       commodity_name,
       is_virtual,
       shop_id,
       shop_nick,
       is_deleted
   FROM ods_item_info
   WHERE pt = '${bizdate}';

3. 配置調試參數。

   在MaxCompute SQL節點編輯頁面右側單擊調試配置：

   • 配置計算資源參數，選擇建立並綁定MaxCompute計算資源時綁定的MaxCompute計算資源以及其對應的計算配額。

   • 配置資源群組參數，選擇建立資源群組並綁定工作空間時購買的Serverless資源群組。

4. 在節點工具列單擊儲存，儲存節點。

基於ods_trade_order表，對訂單的詳細交易資料進行初步清洗、轉換和商務邏輯處理，產出交易下單明細事實表dwd_trade_order。

1. 在工作流程編排頁面中，滑鼠移至上方至dwd_trade_order節點上，單擊開啟節點。

2. 將如下代碼粘貼至節點編輯頁面。

   CREATE TABLE IF NOT EXISTS dwd_trade_order (
       id               BIGINT COMMENT '主鍵，去重後的最新id',
       gmt_create       DATETIME COMMENT '建立時間',
       gmt_modified     DATETIME COMMENT '修改時間',
       sub_order_id     BIGINT COMMENT '子訂單ID',
       parent_order_id  BIGINT COMMENT '父訂單ID',
       buyer_id         BIGINT COMMENT '買家數字id',
       buyer_nick       STRING COMMENT '買家暱稱，處理空值',
       item_id          BIGINT COMMENT '商品數字id',
       item_price       DECIMAL(38,18) COMMENT '商品價格，單位分',
       buy_amount       BIGINT COMMENT '購買數量',
       biz_type         BIGINT COMMENT '交易類型',
       memo             STRING COMMENT '備忘，處理空值',
       pay_status       BIGINT COMMENT '支付狀態',
       logistics_status BIGINT COMMENT '物流狀態',
       status           BIGINT COMMENT '狀態',
       seller_memo      STRING COMMENT '賣家的給交易的備忘',
       buyer_memo       STRING COMMENT '買家給交易的備忘',
       clean_ip         STRING COMMENT '清洗後的買家IP，過濾無效格式',
       end_time         DATETIME COMMENT '交易結束時間',
       pay_time         DATETIME COMMENT '付款的時間',
       is_sub           BIGINT COMMENT '是否是子訂單1表示子訂單',
       is_parent        BIGINT COMMENT '是否是父訂單1表示父訂單',
       shop_id          BIGINT COMMENT '商家id',
       total_fee        DECIMAL(38,18) COMMENT '去除折扣和調整後的子訂單費用',
       is_large_order_flag BOOLEAN COMMENT '是否為大額訂單標誌'
   )
   COMMENT '交易下單明細事實表，包含初步清洗和商務邏輯處理'
   PARTITIONED BY (pt STRING COMMENT '業務日期, yyyymmdd')
   LIFECYCLE 365; -- 資料生命週期設定為365天
   
   
   INSERT OVERWRITE TABLE dwd_trade_order PARTITION(pt='${bizdate}')
   SELECT
       MAX(id) AS id, -- 假設使用最新的id作為去重標準
       gmt_create,
       gmt_modified,
       sub_order_id,
       parent_order_id,
       buyer_id,
       COALESCE(buyer_nick, '') AS buyer_nick, -- 處理buyer_nick為空白的情況
       item_id,
       item_price,
       buy_amount,
       biz_type,
       COALESCE(memo, '') AS memo, -- 處理memo為空白的情況
       pay_status,
       logistics_status,
       status,
       seller_memo,
       buyer_memo,
       CASE 
           WHEN ip LIKE '__.__.__.__' THEN NULL -- 過濾無效IP格式
           ELSE ip 
       END AS clean_ip,
       end_time,
       pay_time,
       is_sub,
       is_parent,
       shop_id,
       total_fee,
       CASE 
           WHEN total_fee >= 10000 THEN TRUE -- 假設大於10000分的訂單為大額訂單
           ELSE FALSE 
       END AS is_large_order_flag -- 添加商務邏輯標誌
   FROM (
       SELECT
           *,
           ROW_NUMBER() OVER(PARTITION BY buyer_id, item_id, gmt_create ORDER BY id DESC) AS rn -- 用於去重的行號
       FROM ods_trade_order
       WHERE pt = '${bizdate}'
   ) AS sub_query
   WHERE rn = 1 -- 僅保留每個去重組的第一條記錄
   GROUP BY 
       gmt_create,
       gmt_modified,
       sub_order_id,
       parent_order_id,
       buyer_id,
       buyer_nick,
       item_id,
       item_price,
       buy_amount,
       biz_type,
       memo,
       pay_status,
       logistics_status,
       status,
       seller_memo,
       buyer_memo,
       clean_ip,
       end_time,
       pay_time,
       is_sub,
       is_parent,
       shop_id,
       total_fee,
       is_large_order_flag;

3. 配置調試參數。

   在MaxCompute SQL節點編輯頁面右側單擊調試配置：

   • 配置計算資源參數，選擇建立並綁定MaxCompute計算資源時綁定的MaxCompute計算資源以及其對應的計算配額。

   • 配置資源群組參數，選擇建立資源群組並綁定工作空間時購買的Serverless資源群組。

4. 在節點工具列單擊儲存，儲存節點。

基於dwd_trade_order表和dim_item_info表，對DWD層經過清洗和標準化的詳細資料進行匯總，產出每日商品類目銷售匯總表dws_daily_category_sales。

1. 在工作流程編排頁面中，滑鼠移至上方至dws_daily_category_sales節點上，單擊開啟節點。

2. 將如下代碼粘貼至節點編輯頁面。

   CREATE TABLE IF NOT EXISTS dws_daily_category_sales (
       cate_id             BIGINT COMMENT '商品葉子類目ID',
       cate_name           STRING COMMENT '商品葉子類目名稱',
       total_sales_amount  DECIMAL(38,18) COMMENT '商品類目總銷售額，單位分',
       order_count         BIGINT COMMENT '訂單數量'
   )
   COMMENT '每日商品類目銷售匯總表'
   PARTITIONED BY (pt STRING COMMENT '業務日期, yyyymmdd')
   LIFECYCLE 365;
   
   
   INSERT OVERWRITE TABLE dws_daily_category_sales PARTITION(pt='${bizdate}')
   SELECT
       i.cate_id,
       i.cate_name,
       SUM(t.total_fee) AS total_sales_amount,
       COUNT(DISTINCT t.id) AS order_count
   FROM dwd_trade_order t
   JOIN dim_item_info i ON t.item_id = i.item_id AND t.pt = i.pt
   WHERE t.pt = '${bizdate}'
   GROUP BY t.pt, i.cate_id, i.cate_name;

3. 配置調試參數。

   在MaxCompute SQL節點編輯頁面右側單擊調試配置：

   • 配置計算資源參數，選擇建立並綁定MaxCompute計算資源時綁定的MaxCompute計算資源以及其對應的計算配額。

   • 配置資源群組參數，選擇建立資源群組並綁定工作空間時購買的Serverless資源群組。

4. 在節點工具列單擊儲存，儲存節點。

基於dws_daily_category_sales表，產出每日最暢銷商品類目排名表ads_top_selling_categories。

1. 在工作流程編排頁面中，滑鼠移至上方至ads_top_selling_categories節點上，單擊開啟節點。

2. 將如下代碼粘貼至節點編輯頁面。

   CREATE TABLE IF NOT EXISTS ads_top_selling_categories (
       rank                BIGINT COMMENT '銷量排名',
       cate_id             BIGINT COMMENT '商品葉子類目ID',
       cate_name           STRING COMMENT '商品葉子類目名稱',
       total_sales_amount  DECIMAL(38,18) COMMENT '商品類目總銷售額，單位分',
       order_count         BIGINT COMMENT '訂單數量'
   )
   COMMENT '每日最暢銷商品類目排名表'
   PARTITIONED BY (pt STRING COMMENT '業務日期, yyyymmdd');
   
   
   INSERT OVERWRITE TABLE ads_top_selling_categories PARTITION(pt='${bizdate}')
   SELECT
       rank,
       cate_id,
       cate_name,
       total_sales_amount,
       order_count
   FROM (
       SELECT
           DENSE_RANK() OVER(ORDER BY total_sales_amount DESC) AS rank,
           cate_id,
           cate_name,
           total_sales_amount,
           order_count
       FROM (
           SELECT
               cate_id,
               cate_name,
               SUM(total_sales_amount) AS total_sales_amount,
               SUM(order_count) AS order_count
           FROM dws_daily_category_sales
           WHERE pt = '${bizdate}'
           GROUP BY cate_id, cate_name
       ) agg_sub
   ) agg_outer
   WHERE rank <= 10;

3. 配置調試參數。

   在MaxCompute SQL節點編輯頁面右側單擊調試配置：

   • 配置計算資源參數，選擇建立並綁定MaxCompute計算資源時綁定的MaxCompute計算資源以及其對應的計算配額。

   • 配置資源群組參數，選擇建立資源群組並綁定工作空間時購買的Serverless資源群組。

4. 在節點工具列單擊儲存，儲存節點。