Category: 4 SPL Big Data Computing

One essential step in quantitative trading is backtesting. Any strategy needs to be validated with historical data before it can be used for paper trading or even real trading. This article will introduce how to conduct backtesting in SPL. Without further ado, let’s get straight to the point. Suppose we have the following trade data generated according to a certain strategy: In this table, ‘ts_code’ refers to the stock code; ‘trade_date’ refers to the trade date; ‘shares’ refers to the number of shares traded, and ‘flag’ refers to the trade flag (1 denotes buy, -1 denotes sell). Now we need

Overview Composite table is an important file storage format of SPL. To ensure high performance, composite table often requires data to be stored in order. However, the order in which the data are generated is usually different from the order required by composite table, so it needs to adjust the order of data when maintaining composite table data. In addition, the composite table cannot be read when writing data to it, yet some query computation tasks cannot be stopped during data maintenance, which also needs to be ensured using corresponding means. This routine will give solutions to these problems. Related

Problem description In order to monitor the operation status of power generation equipment, sensors (DCS) will be placed in the equipment to collect data. A power monitoring and statistical system designed by a company needs to collect the data measured on multiple sensors (referred to as monitoring point below) in real time at a fixed frequency and then store, and provide the query and statistical functionalities for the data of specified monitoring point within any time interval. The statistics include maximum, minimum, mean, variance, median, etc. Data structure and data scale Field name Field meaning Field type Sample data id

Problem description Definition of funnel analysis In e-commerce business, funnel analysis is a common statistical requirement. When users use a smart device to shop online, the system will establish a connection to form a session. Each session contains multiple operation events such as visit-type event (visit), view-type event (view), detail-page-type event (detail). From the perspective of the occurrence time of events (event time for short), each user’s operation events are in a certain order. The later the event order, the fewer the number of users involved in the event, just like a funnel. For the funnel conversion analysis, it first

Problem description Data structure and data scale Customer – dimension table (cust_dim): Field name Field type Field meaning Sample data id Number Customer number 18765 d1 Number Dimension 1 12 … … … … d10 Number Dimension 10 3 … … … … This table contains a total of 100 million customers, each of which has a unique id. Each customer has 10 dimensions. For example, d2 is the age group dimension, which corresponds to 8 enumeration values, indicating the value range of d2 is 1 to 8. d4 is the educational background dimension, which corresponds to 12 enumeration values,

Problem description System architecture The frontend application is a BI system that is mainly to implement query based on some filter conditions, and the backend is a certain MPP database. The frontend generates the SQL suitable for wide table ‘orders’ (order table) based on user’s selection and operation on the page, and then submit the SQL to the backend, and finally display the result returned from backend. The generated SQL is roughly as follows: In the where condition, the date is a required field (it means to only query one day’s data each time); other fields and filter conditions can

Problem description Data structure and data scale Deposit details table (‘deposit’ for short): Field name Field type Field meaning Sample data dt Date Date 2023-11-23 curr String Currency type 1: RMB, 0: others cust_no String Customer number 2889412 dept Number Department number 1-500 code11 Number Code 11 1, 0 code12 Number Code 12 1, 0 amt Number Amount 32499.90 … This table contains a dozen fields, and stores 20 million records per day, totaling 600 million records for 30 days. Customer table (‘cust’ for short): Field name Field type Field meaning Sample data dt Date Date 2023-11-23 cust_no String Customer

Problem description Data structure and data scale Data structure of account details table ‘T’: Field name Field type Field meaning Sample data Id String Account number 28-digit number Tdate Date Date 2023-10-22 Tamt Number Amount 23345.70 Ttype Number Type 1, 0 Tcorp String Branch A210172 … Data structure of branch table ‘C’: Field name Field type Field meaning Sample data cid String Branch number A210172 cname String Branch name X Bank New York Branch caddress String Branch address 226 East 56th Street, New York, NY 10022, U.S.A. … Table T contains 300 million records. The user account id consists of

Data comparison between databases of different types means comparing data between two tables having same logical structures in different types of databases to find the differences. Problem statement The difficulty of data comparison needs to overcome is the differences of data types and the data processing approaches between different database products. The problem is that different results will be obtained after the same data is imported to and exported from different databases. Common data types mainly include numeric, string and date. Now we use Oracle and MySQL to respectively create data tables that both contain all the three commonly seen

Problem description Definition of space-time collision Dataset A contains the time and space information of n source objects A1, …, An, and each piece of information includes three attributes: ID (iA), location (lA) and time (tA). It can be assumed that the same Ai will not appear twice in A at the same time, or in other words, no two pieces of information have the same iA and tA. Dataset B, which has the same structure as A, contains m target objects B1, …, Bm (with the similar attributes iB, lB, tB) that are to be confirmed whether they collided