Updating Daily Incremental Order Data After Summary

  • Last update: January 16, 2025
  • Overview

    Application Scenario

    Fields in the order table of your business system include orderid, money, order transaction date, and order creation date. Under normal circumstances, order transaction date and order creation date have the same values. However, date discrepancies may occur due to supplementary entry of order data. For example, business personnel may submit data of orders that happened on 05-01 to the system on 05-10, in which case the order creation date is 05-10, and the order transaction date is 05-01.

    Data on the ODS layer of a data warehouse is incrementally updated based on the order creation date and can be updated normally in case of supplementary entry.

    On the DM layer, the order amount is summarized daily by order transaction date and data from five days before the order transaction date is scheduled to be incrementally updated daily. However, because the time difference between the order creation date and the order transaction date may exceed five days, the incremental update plan where old data is deleted first does not apply to the supplemented data. For example, on 05-16, data of orders created on 05-01 and 05-02 is supplemented, but the system only updates data from 05-12 to 05-16.

    iconNote:
    The historical transaction data supplemented on 05-16 is highlighted in yellow.

    1.png

    Implementation Method

    • First, perform an incremental update on data whose order transaction date is within the past five days of the current date.

    • Filter orders whose creation date is later than its transaction date and fetch the transaction date. Data with the fetched transaction date contains supplemented data, requiring a re-summary of the daily order amount.

    Procedure

    Example: An incremental update on order table data whose order transaction date is within the past five days of the current date is executed regularly. Additionally, for supplemented data whose order transaction date is beyond the time range of incremental updates, the order amount on that date is re-summarized and updated.

    For example, if data of orders created on 05-01 and 05-02 is supplemented on 05-16, the system performs incremental updates on both data from 05-12 to 05-16 and the supplemented data.

    Data in the ZISJ_order table before the update is shown in the following figure.

    iconNote:
    The historical transaction data supplemented on 05-16 is highlighted in yellow.

    1.png

    The ZISJ_order_sum table containing the summarized daily order amount is shown in the following figure.

    1737018985991869.png

    You want to synchronize the transaction data increments within the past five days and the supplemented data to the data table. The expected effect is shown in the following figure.

    4.png

    Updating Data Increments of the Past Five Days

    Create a scheduled task and drag a SQL Script node onto the page. Configure the script to delete the summarized daily transaction amount of the past five days from the summary table.

    The following is the MySQL statement.

    DELETE FROM ZJSJ_order_sum
    WHERE date >= CURDATE() - INTERVAL 5 DAY;

    5.png

    Add a Data Synchronization node to re-summarize the daily order amount of the past five days and write it into the summary table, as shown in the following figure.

    The following is the MySQL statement.

    SELECT DATE (order transaction date) AS date, SUM (money) AS total_money
    FROM ZJSJ_order
    WHERE order transaction date >= CURDATE () - INTERVAL 5 DAY -- Select the records within the past five days.
    GROUP BY DATE(order transaction date)

    6.png

    Click Data Preview to see the summarized daily sales amount of the past five days of the order transaction date, as shown in the following figure.

    7.png

    Write the data into the summary table, set Write Method to Write Data into Target Table Directly, select date as the logical primary key, and set Strategy for Primary Key Conflict to Overwrite Data in Target Table If Same Primary Key Value Exists, as shown in the following figure.

    8.png

    Fetching the Order Transaction Date of Supplemented Data and Re-summarizing the Daily Amount

    Add a Parameter Assignment node to fetch the order transaction date of data whose order creation date is later than its order transaction date from the ZISJ_order table, as shown in the following figure.

    The following is the MySQL statement.

    SELECT DATE(order transaction date) AS jy_date_list 
    FROM ZJSJ_order 
    WHERE DATE(order creation date) > DATE(order transaction date) 
    GROUP BY DATE(order transaction date)

    9.png

    Click Data Preview, as shown in the following figure.

    10.png

    You can set this return value as a parameter, as shown in the following figure.

    iconNote:
    Enable Closure Character to pass multiple parameter values.

    11.png

    Add a Data Synchronization node, pass in the order transaction date of supplemented data to filter data with the order transaction date, and summarize the order amount of the filtered transaction date, as shown in the following figure.

    The following is the MySQL statement.

    SELECT DATE (order transaction date) AS date, SUM (money) AS total_money
    FROM ZJSJ_order
    WHERE DATE (order transaction date) IN (${new_date}) -- Specify the date.
    GROUP BY DATE (order transaction date)

    12.png

    Click Data Preview, and you can see the data, as shown in the following figure.

    13.png

    Write the summarized data to the summary table, as shown in the following figure.

    14.png

    Running the Task

    Run the task. After successful execution, the data increments of the past five days has been synchronized, and the supplemented data has been summarized and synchronized, as shown in the following figure.

    4.png

    附件列表


    主题: Data Development - Scheduled Task
    • Helpful
    • Not helpful
    • Only read

    滑鼠選中內容,快速回饋問題

    滑鼠選中存在疑惑的內容,即可快速回饋問題,我們將會跟進處理。

    不再提示

    9s后關閉

    Get
    Help
    Online Support
    Professional technical support is provided to quickly help you solve problems.
    Online support is available from 9:00-12:00 and 13:30-17:30 on weekdays.
    Page Feedback
    You can provide suggestions and feedback for the current web page.
    Pre-Sales Consultation
    Business Consultation
    Business: international@fanruan.com
    Support: support@fanruan.com
    Page Feedback
    *Problem Type
    Cannot be empty
    Problem Description
    0/1000
    Cannot be empty

    Submitted successfully

    Network busy