本ページでは、Realtime Compute を使用して、クーポンベースのマーケティングポリシーでマーケティングクーポンを発行する条件を満たすユーザーをフィルタリングする方法について説明します。

背景

販売業者は、独身の日に「払い戻しクーポン」のマーケティングポリシーを使用しています。 ユーザーの消費量が指定された値に達した後、販売業者は、より多くの消費を促進するために、ユーザーに一定量の払い戻しクーポンを発行します。 Realtime Compute は、ユーザーの消費量をリアルタイムでモニターし、払い戻しクーポンの発行条件を満たすユーザーをフィルターします。

ソリューション

  • SQL 構造
  • ソーステーブル
    • E コマースシステムによって生成された注文を格納するソーステーブルを作成します。
      コアロジックに焦点を当てるため、注文データ形式を単純化して、ユースケースに関連する属性のみを保持します。
      CREATE TABLE dwd_tb_trd_pay_ri(
              biz_order_id varchar, // The ID of the order. 
              auction_id varchar, // The ID of the product.
              auction_title varchar, // The title of the product. 
              buyer_id varchar, // The ID of the buyer.
              buyer_nick varchar, // The nickname of the buyer.
              pay_time varchar, // The payment time of the order.
              gmt_create varchar, // The time when the order was created.
              gmt_modified varchar, // The time when the order was modified.
              biz_type VARCHAR, // The type of the transaction.
              pay_status VARCHAR, // The payment status. 
              `attributes` VARCHAR, // The flag of the order.
              from_group VARCHAR, // The source of the order.
              div_idx_actual_total_fee DOUBLE // The deal amount.        
      ) WITH (
          type='datahub',
          endPoint='http://dh-cn-hangzhou.aliyun-inc.com',
          project='yourProjectName', // Your project name.
          topic='yourTopicName', // Your topic name.
          roleArn='yourRoleArn', // Your role ARN.
          batchReadSize='500'
      );
    • 返品のソーステーブルを作成します。
      コアロジックに焦点を当てるため、注文データ形式を単純化して、ユースケースに関連する属性のみを保持します。
      CREATE TABLE dwd_tb_trd_rfd_ri(
              biz_order_id varchar, // The ID of the order. 
              auction_id varchar, // The ID of the product.
              auction_title varchar, // The title of the product. 
              buyer_id varchar, // The ID of the buyer.
              buyer_nick varchar, // The nickname of the buyer.
              pay_time varchar, // The payment time of the order.
              gmt_create varchar, // The time when the order was created.
              gmt_modified varchar, // The time when the order was modified.
              biz_type VARCHAR, // The type of the transaction.
              pay_status VARCHAR, // The payment status. 
              `attributes` VARCHAR, // The flag of the order.
              from_group VARCHAR, // The source of the order. 
              refund_fee DOUBLE // The refund amount.     
      ) WITH (    
          type='datahub',
          endPoint='http://dh-cn-hangzhou.aliyun-inc.com',
          project='yourProjectName', // Your project name.
          topic='yourTopicName', // Your topic name.
          roleArn='yourRoleArn', // Your role ARN.
          batchReadSize='500'
      );
  • 結果テーブル

    次のステートメントを実行して、RDS 結果テーブルを作成します。

    CREATE TABLE tddl_output(
        id BIGINT, // The ID, which auto-increments.
        gmt_create TIMESTAMP, // The time when the order was created.
        gmt_modified TIMESTAMP, // The time when the order was modified.
        buyer_id BIGINT, // The ID of the buyer.
        cumulate_amount BIGINT, // The order amount.
        effect_time BIGINT, // The payment time of the order.
        primary key(buyer_id,effect_time)
    ) WITH ( 
            type= 'rds', 
            url = 'yourDatabaseURL', // Your database URL. 
            tableName = 'yourTableName', // Your table name. 
            userName = 'yourUserName', // Your username. 
            password = 'yourDatabasePassword' // Your password. 
        ); 
  • ビジネスロジック

    UNION ALL 操作を実行して注文テーブルと返品テーブルを結合し、購入したすべてのアイテムに関する情報を取得して、実際の消費量とユーザーの詳細を収集します。

    CREATE VIEW dwd_tb_trd_and_rfd_pay_ri
    AS
    SELECT
    *
    FROM(
        (SELECT 
            `a`.biz_order_id biz_order_id, 
            `a`.auction_id auction_id, 
            `a`.auction_title auction_title, 
            `a`.buyer_id buyer_id, 
            `a`.buyer_nick buyer_nick, 
            `a`.pay_time pay_time, 
            `a`.gmt_create gmt_create, 
            `a`.gmt_modified gmt_modified, 
            `a`.biz_type biz_type, 
            `a`.pay_status pay_status, 
            `a`.`attributes` `attributes`, 
            `a`.from_group,        
            `a`.div_idx_actual_total_fee div_idx_actual_total_fee
        FROM 
            dwd_tb_trd_pay_ri `a` 
        )
        UNION ALL
        (
        SELECT 
            `b`.biz_order_id biz_order_id, 
            `b`.auction_id auction_id, 
            `b`.auction_title auction_title, 
            `b`.buyer_id buyer_id, 
            `b`.buyer_nick buyer_nick, 
            `b`.pay_time pay_time, 
            `b`.gmt_create gmt_create, 
            `b`.gmt_modified gmt_modified, 
            `b`.biz_type biz_type, 
            `b`.pay_status pay_status, 
            `b`.`attributes` `attributes`, 
            `b`.from_group,     
            `b`.refund_fee div_idx_actual_total_fee // The refund amount, which is a negative value.
        FROM 
            dwd_tb_trd_rfd_ri `b` 
        )
    );
    					
  • 重複排除

    注文テーブルと返品テーブルには、製品 ID や製品名などの大量の重複データが含まれている場合があります。 MIN 関数を使用して、各注文の最初に生成されたレコードでのみパラメーター値を取得し、他のデータをフィルターで除外します。 注文 ID と支払い状況によってデータをグループ化し、必要な製品 ID と製品名を取得します。

    
    CREATE VIEW filter_hxhb_dwd_tb_trd_and_rfd_pay_ri_distinct AS 
    SELECT 
       biz_order_id biz_order_id, 
       pay_status pay_status,
       MIN(auction_id) auction_id, 
       MIN(auction_title) auction_title, 
       MIN(buyer_id) buyer_id, 
       MIN(buyer_nick) buyer_nick, 
       MIN(pay_time) pay_time, 
       MIN(gmt_create) gmt_create, 
       MIN(gmt_modified) gmt_modified, 
       MIN(biz_type) biz_type, 
       MIN(attributes) attributes, 
       MIN(div_idx_actual_total_fee) div_idx_actual_total_fee
    FROM 
        dwd_tb_trd_and_rfd_pay_ri
    GROUP BY biz_order_id ,pay_status;                
  • データ集計
    CREATE VIEW ads_tb_trd_and_rfd_pay_ri AS
    SELECT 
       MIN(gmt_create) gmt_create, // The time when the order was created.
       MAX(gmt_modified) gmt_modified, // The time when the order was last modified.
       CAST (buyer_id AS BIGINT) buyer_id, // The ID of the buyer.
       CAST (date_format(pay_time , 'yyyy-MM-dd HH:mm:ss' , 'yyyyMMdd') AS BIGINT) as effect_time, // The payment time.
       SUM(CAST(div_idx_actual_total_fee/100 AS BIGINT)) cumulate_amount // The total amount.
    FROM 
       filter_hxhb_dwd_tb_trd_and_rfd_pay_ri_distinct 
    GROUP BY 
       buyer_id,date_format(pay_time , 'yyyy-MM-dd HH:mm:ss' , 'yyyyMMdd');
    Q: MAX 関数と MIN 関数が使用されるのはなぜですか。
     MIN(gmt_create) gmt_create, // The time when the order was created.
     MAX(gmt_modified) gmt_modified, // The time when the order was last modified.

    A: MIN (gmt_create) は、注文が初めて記録されたときに取得します。 MAX (gmt_modified) は、注文が最後に変更されたときに取得します。 MAX および MIN を使用して、注文のビジネスロジックに基づいて対応する時間値を取得することができます。

    時間フィールドが BIGINT タイプではない場合は、対応する組み込み関数を使用してデータタイプを変換します。 詳細については、「組み込み関数"」をご参照ください。
  • RDS へのデータ挿入

    統計データを RDS 結果テーブルに挿入します。 適切なプッシュソフトウェアを使用して、指定された条件を満たすユーザーに正しい払い戻し額のクーポンを発行します。

    INSERT INTO tddl_output
    SELECT
        gmt_create,
        gmt_modified,
        buyer_id,
        cumulate_amount,
        effect_time
    from ads_tb_trd_and_rfd_pay_ri
    where cumulate_amount>0;

    Q: 「where cumulate_amount>0」句が使用されるのはなぜですか。

    cumulate_amount>0

    A: この句は、前の UNION ALL 操作に含まれる返品された製品の金額を除外します。

まとめ

Q: 注文と返品注文に関する多数のレコードから必要なデータを取得するにはどうすればよいですか。

A: 実際のトランザクション状況では、多数の注文レコードと返品レコードが生成されます。 UNION ALL 操作を実行して、2 つ以上のテーブルを 1 つの大きなテーブルとして結合することができます。 特定のビジネスロジックに基づいて、レコードの重複排除と集計を行います。 最後に、各ユーザーのすべての注文の実際のトランザクション金額を結果テーブルに書き込んで、クーポン発行の準備をします。