DataWorks:上級編：人気商品カテゴリの分析

1. ワークフローキャンバス上で workshop ノードにマウスを合わせ、「ノードを開く」をクリックします。

2. workshop ノードエディターの右側で スケジュール設定 をクリックし、パラメーターを設定します。以下に、本チュートリアルで重要なパラメーターを示します。その他のパラメーターについてはデフォルト値のままにしてください。

   スケジュールパラメーター	説明
   スケジュールタイプ	本チュートリアルでは、ダミーラン を設定します。
   リソースグループ	本チュートリアルでは、リソースグループの作成およびワークスペースへの関連付け で作成したサーバーレスリソースグループを選択します。
   ノード依存関係の設定	workshop は初期ノードであり、上流の依存関係がないため、ワークスペースのルートノードを使用 をクリックして、ワークフローをワークスペースのルートノードによってトリガーできます。 ワークスペースのルートノードの名前は WorkspaceName_root の形式です。

3. ノードツールバーの 保存 をクリックしてノードを保存します。

1. ワークフローキャンバス上で ods_item_info ノードにマウスを合わせ、「ノードを開く」をクリックします。

2. データソースおよびリソースグループを設定します。

   パラメーター	説明
   ソース	データソース：MySQL_Source。
   送信先	データソース：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} を入力します。この変数は、テスト時には定数値を使用し、スケジュール実行時には動的値を使用します。DataStudio における変数形式および設定方法の詳細については、「スケジュールパラメーター」をご参照ください。

   2. フィールドマッピング および チャネル制御 を確認します。

      DataWorks は、ソースフィールドを対応する送信先フィールドにマッピングします。また、右側のチャネル制御パネルで、タスクの並列実行数や不正データポリシーなどの設定も行えます。本チュートリアルでは、不正データレコードのポリシー を 不正データレコードを許可しない に設定し、その他の設定はデフォルトのままにします。詳細については、「ウィザードモードでのバッチ同期ノードの設定」をご参照ください。

4. ノードツールバーの 保存 をクリックしてノードを保存します。

1. ワークフローキャンバス上で ods_trade_order ノードにマウスを合わせ、「ノードを開く」をクリックします。

2. データソースおよびリソースグループを設定します。

   パラメーター	説明
   ソース	データソース：MySQL_Source。
   送信先	データソース：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} を入力します。この変数は、テスト時には定数値を使用し、スケジュール実行時には動的値を使用します。DataStudio における変数形式および設定方法の詳細については、「スケジュールパラメーター」をご参照ください。

   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 'ショップニックネーム',
       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 計算リソースおよび対応する計算クォータを選択します。

   • リソースグループパラメーターを設定するには、リソースグループの作成およびワークスペースへの関連付け で購入したサーバーレスリソースグループを選択します。

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 '購入者ニックネーム（null の場合は空文字列）',
       item_id          BIGINT COMMENT '商品数値 ID',
       item_price       DECIMAL(38,18) COMMENT '商品価格（センチ単位）',
       buy_amount       BIGINT COMMENT '購入数量',
       biz_type         BIGINT COMMENT '取引タイプ',
       memo             STRING COMMENT 'メモ（null の場合は空文字列）',
       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
       id,
       gmt_create,
       gmt_modified,
       sub_order_id,
       parent_order_id,
       buyer_id,
       COALESCE(buyer_nick, '') AS buyer_nick, -- null の buyer_nick を処理
       item_id,
       item_price,
       buy_amount,
       biz_type,
       COALESCE(memo, '') AS memo, -- null の 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 -- 10,000 センチ以上の注文を大口注文とみなす
           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;

3. 実行時パラメーターを設定します。

   MaxCompute SQL ノードエディターの右側で 実行設定 をクリックします：

   • 計算リソース パラメーターを設定するには、MaxCompute 計算リソースの作成および関連付け で関連付けた MaxCompute 計算リソースおよび対応する計算クォータを選択します。

   • リソースグループパラメーターを設定するには、リソースグループの作成およびワークスペースへの関連付け で購入したサーバーレスリソースグループを選択します。

4. ノードツールバーの 保存 をクリックしてノードを保存します。

このノードは、dwd_trade_order および dim_item_info テーブルからクリーニングおよび標準化された詳細データを集約し、日次商品カテゴリ売上サマリーテーブル 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 計算リソースおよび対応する計算クォータを選択します。

   • リソースグループパラメーターを設定するには、リソースグループの作成およびワークスペースへの関連付け で購入したサーバーレスリソースグループを選択します。

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 計算リソースおよび対応する計算クォータを選択します。

   • リソースグループパラメーターを設定するには、リソースグループの作成およびワークスペースへの関連付け で購入したサーバーレスリソースグループを選択します。

4. ノードツールバーの 保存 をクリックしてノードを保存します。