Data Management:Use the task orchestration feature for e-commerce tagging

Open SQL console

1. Log on to the DMS console V5.0.

2. Move the pointer over the icon in the upper-left corner and choose All functions > SQL Console > SQL Console.

   In normal mode, choose SQL Console > SQL Console from the top navigation bar.

3. In the Please select the database first dialog box, search for your database and select it from the results, then click Confirm.

Create the tables

Run each of the following SQL statements in the SQL editor. Copy and paste each statement, then click Execute.

activity_setting (promotion configuration table):

CREATE TABLE `activity_setting` (
    `has_promotion` tinyint(1) NOT NULL COMMENT 'Whether discounts are provided',
    `consumption_limit` int(11) NOT NULL COMMENT 'Limit on purchase amount',
    `quota` int(11) NOT NULL COMMENT 'Coupon discount',
    PRIMARY KEY (`has_promotion`)
) ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
ROW_FORMAT=COMPACT
AVG_ROW_LENGTH=16384;

consumption_records (purchase transaction table):

CREATE TABLE `consumption_records` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Record ID',
    `time` datetime NOT NULL COMMENT 'Purchase time',
    `count` int(11) NOT NULL COMMENT 'Purchase amount',
    `user_id` bigint(20) NOT NULL COMMENT 'User ID',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_general_ci
COMMENT='Purchase record table'
AUTO_INCREMENT=8001
ROW_FORMAT=COMPACT
AVG_ROW_LENGTH=54;

voucher_send_list (coupon issuance table):

CREATE TABLE `voucher_send_list` (
    `Id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Record ID',
    `user_id` int(11) NULL COMMENT 'User ID',
    `consumption_sum` int(11) NULL COMMENT 'Purchase amount',
    `quota` int(11) NULL COMMENT 'Coupon discount',
    PRIMARY KEY (`Id`)
) ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=1
ROW_FORMAT=COMPACT
AVG_ROW_LENGTH=0;

Load data

Insert the promotion configuration. This example sets has_promotion = 1 (active), a purchase threshold of 100, and a coupon discount of 30:

INSERT INTO activity_setting(has_promotion, consumption_limit, quota)
VALUES('1','100','30');

Generate test purchase records for consumption_records using the DMS test data generation feature. For details, see Generate test data.

The following figure shows an example configuration for generating data in the consumption_records table.

SQL Assignment for Single Instance node

This node reads the promotion settings from activity_setting and stores each column value in an output variable. Downstream nodes reference these variables using the ${variable_name} syntax.

1. Double-click the SQL Assignment for Single Instance node.

2. Search for and select your database from the drop-down list.

3. In the SQL editor, enter:

   select * from activity_setting limit 1

4. In the right-side navigation pane, click the Variable Setting tab, then select the Output Variables tab.

5. Click Increase Variable and add the following output variables: Quota, HasPromotion, and ConsumptionLimit.

After the node runs, the query result is mapped to these three variables. The Conditional Branch and Single Instance SQL nodes reference them as ${HasPromotion}, ${Quota}, and ${ConsumptionLimit}.

For configuration details, see Configure an SQL assignment node.

Conditional Branch node

1. Double-click the Conditional Branch node.

2. Configure the conditional expression to evaluate whether HasPromotion is active.

The following figure shows the configured conditional expression.

For configuration details, see Configure a conditional branch node.

Single Instance SQL node

1. Double-click the Single Instance SQL node.

2. In the SQL editor, enter the following statement. It uses the ${Quota}, ${ConsumptionLimit}, and ${bizdate} variables to insert qualifying users into voucher_send_list:

   INSERT INTO `voucher_send_list`(`user_id`,`consumption_sum`,`quota`)
   SELECT * FROM
   (SELECT `user_id`, sum(count) AS consumption_sum,${Quota} FROM `consumption_records`
    WHERE `time` > '${bizdate}'  GROUP BY `user_id`) `consumption_records`
   where `consumption_sum`>${ConsumptionLimit}