进阶：商品订单畅销类目分析

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. 在节点工具栏单击保存，保存节点。