Financial multi-source data processing logic SQL generation method

Abstract

The invention relates to a financial multi-source data processing logic SQL (Structured Query Language) generation method. Compared with the prior art, the defects that data logic management is complicated, the data blood relationship is not clear, and business requirements cannot be quickly responded are overcome. The method comprises the following steps: designing a data model; designing a business grammar; constructing a business rule analysis engine; and generating a processing logic SQL. The financial multi-source data SQL generation method can quickly respond to business requirements, is adaptive to a domestic database, and realizes SQL direct generation of financial multi-source data.

Description

Technical Field

[0001] The present invention relates to the technical field of database processing, and in particular to a method for generating logical SQL for financial multi-source data processing. Background Art

[0002] In the financial industry, the business logic rules for data processing in data application products are complex and change frequently. Traditional hard coding methods are difficult to respond quickly to frequent changes in business needs, and traditional hard coding methods are relatively complex to maintain and expand.

[0003] For example: traditional EXCEL design documents have poor readability and are non-standard. Different customers have different data retrieval logics, and traditional EXCEL logic file maintenance methods are difficult to manage. Traditional coding implementation methods have inconsistent coding habits among different coders, resulting in inconsistent SQL code styles, difficulty in reading, and difficulty in code review and management.

[0004] In addition, the traditional coding implementation method, based on stored procedures / SQL code segments, often requires a lot of manpower to carry out adaptability transformation in order to realize the data processing logic across database platforms. Moreover, in the process of data application, the troubleshooting of data problems has always been a pain point and difficulty in data application, and the troubleshooting of data problems often requires huge time and manpower costs.

[0005] Whether it is the business manager, IT manager, or logic processing personnel, they all hope to quickly understand the business rules and logic of data processing, but the current data processing has multiple levels and the data lineage is not clear. Therefore, how to develop a high-quality and efficient multi-source data processing logic SQL generation method has become a technical problem that needs to be solved urgently. Summary of the invention

[0006] The purpose of the present invention is to solve the defects of the prior art, such as complex data logic management, unclear data lineage relationship, and inability to quickly respond to business needs, and to provide a financial multi-source data processing logic SQL generation method to solve the above problems.

[0007] In order to achieve the above object, the technical solution of the present invention is as follows:

[0008] A method for generating logical SQL for financial multi-source data processing comprises the following steps:

[0009] Design data model: Design data model by refining model usage scenarios. The data model includes standard root words, standard fields, model master table, model dimension diagram and model side diagram.

[0010] Design business grammar: Design business rules and parameters according to business needs to form complete business grammar rules;

[0011] Build a business rule parsing engine: compile a business rule system and build a business rule parsing engine;

[0012] Generation of processing logic SQL: The business rule parsing engine parses the acquired business syntax and generates multi-data source processing logic SQL.

[0013] The design of the service grammar includes the following steps:

[0014] Configure the relationship between model-driven tables and models based on the data model;

[0015] Configure data model slices, algorithm libraries, function libraries, business rules, and logical rules.

[0016] The construction of the business rule parsing engine comprises the following steps:

[0017] Set up a standard business rules system:

[0018] The business rule system that sets the standard includes business rule columns, business algorithms, and functions;

[0019] Setting business rules includes: logical operators, keywords, logical branches, comparison operators, value expressions, and algorithm functions;

[0020] Set up a special business rule system:

[0021] Set the rules for using brackets, inequality signs, and functions;

[0022] A business rule parsing engine is constructed based on the standard business rule system and the special business rule system.

[0023] The generation of the processing logic SQL includes the following steps:

[0024] The business rule parsing engine obtains the business grammar to be parsed;

[0025] Configure the business grammar to be parsed into easy-to-understand business rules according to business requirements and business rule system;

[0026] Use business rules and business logic parsing engine to generate standard data processing logic SQL:

[0027] Generate blood relationships in reverse through business rules. According to different application scenarios and user groups, they are divided into: data blood relationship, business blood relationship, and code blood relationship.

[0028] Among them, data lineage is oriented towards IT / implementation / business, business lineage is oriented towards IT / implementation / business, and code lineage is oriented towards implementation;

[0029] The business rule parsing engine generates processing logic SQL based on business rules.

[0030] Beneficial Effects

[0031] Compared with the prior art, the method for generating SQL for processing logic of financial multi-source data of the present invention can quickly respond to business needs, adapt to the adaptation of domestic databases, and realize direct generation of SQL for financial multi-source data.

[0032] The present invention solves the problems of complex data logic management, unclear data lineage, difficult data problem analysis and troubleshooting, poor readability of data acquisition logic, non-standard data acquisition logic archives, and difficult logic code management. BRIEF DESCRIPTION OF THE DRAWINGS

[0033] Figure 1 is a method sequence diagram of the present invention;

[0034] Figure 2 A design diagram of the business rule system involved in the present invention;

[0035] Figure 3 A business operation rule diagram involved in the present invention;

[0036] Figure 4 It is a field blood relationship analysis diagram involved in the present invention;

[0037] Figure 5 This is a diagram showing the actual use of the present invention. DETAILED DESCRIPTION

[0038] In order to have a further understanding and recognition of the structural features and the effects achieved by the present invention, a preferred embodiment and accompanying drawings are used for detailed description as follows:

[0039] like Figure 1 As shown, the method for generating logical SQL for financial multi-source data processing according to the present invention comprises the following steps:

[0040] The first step is to design the data model: design the data model by refining the model usage scenarios. The data model includes standard roots, standard fields, model main table, model dimension diagram and model side diagram.

[0041] The second step is to design business grammar: Figure 2 As shown, business rules and parameters are designed according to business needs to form complete business grammar rules, which are then used when conducting business logic research.

[0042] (1) Configure the relationship between the model-driven table and the model based on the data model;

[0043] (2) Configure data model slices, algorithm library, function library, business rules, and logical rules.

[0044] The third step is to build a business rule parsing engine: compile a business rule system and build a business rule parsing engine. This will reduce the manual coding process and save the process of business requirements to R&D presentation and understanding.

[0045] (1) Setting up a standard business rules system:

[0046] The standard business rule system includes business rule columns, business algorithms, and functions.

[0047] (2) Business rules include: logical operators, keywords, logical branches, comparison operators, value expressions, and algorithm functions.

[0048] (3) Setting up a special business rules system:

[0049] Set the rules for using brackets, inequality signs, and functions.

[0050] (4) Construct a business rule parsing engine based on the standard business rule system and the special business rule system.

[0051] Business Rules Figure 3 As shown, the part can be set as follows:

[0052] Logical operators: --》

[0053] Keywords: and, or, not, contain, right equal, left equal

[0054] Logical branch: If [A] = {1}-》{1}

[0055] Comparison operators: =, >, <, >=, <=, !=

[0056] Value expression: {}, #{}, [], $function name

[0057] Algorithmic functions: max(), min(), avg().

[0058] The business rule content must be filled in according to the basic specifications, and the source field must be filled in when directly accessing.

[0059] The special business rules system is as follows:

[0060] 1. Use of brackets

[0061] When brackets are needed, you can use them directly, but make sure they are English brackets.

[0062] 2. Use of the inequality sign

[0063] The inequality sign should be uniformly used as!=. No other writing methods are allowed. Remember to use the English one.

[0064] For the expression of 3.is null, use the combination of = and {null}.

[0065] For the expression of 4.is not null, use the combination of!= and {null} (note that the exclamation mark in!= should be the English exclamation mark).

[0066] 5. Multiple dictionary items are separated by commas. The dictionary number and the dictionary item name are separated by -. To prevent parsing problems, commas, [-], and [,] in the dictionary item name should be removed. The comma must be the English one.

[0067] 6. For the in expression, use the combination of = and {}.

[0068] When there are multiple values in {}, = will be translated into in.

[0069] 7. For the not in expression, use the combination of!= and {}.

[0070] When there are multiple values in {},!= will be translated into not in.

[0071] 8. When +-* / calculations are needed, use them directly.

[0072] 9. case when nesting

[0073] When there are multiple levels of case when nesting, since it is necessary to know where each case when ends, and at the same time taking readability and writability into account, [level] is added before the keyword if.

[0074] 10. The use of functions

[0075] 10.1. Since there may be multiple parameters for the function input, it needs to be written according to the function format.

[0076] 10.2. If a function needs to be added, please contact us to add the corresponding mapping. 11. The alternative use of nvl()

[0077] 11. For the tree-shaped dictionary, the equal sign cannot be used, and only the keyword belonging node can be used.

[0078] Fourth step, the generation of the processed logical SQL: The business rule parsing engine parses the obtained business grammar to generate the multi-data source processed logical SQL, making the logical SQL have clear logic, strong readability, and the SQL code is clear and standardized.

[0079] (1) The business rule parsing engine obtains the business grammar to be parsed.

[0080] (2) The business grammar to be parsed is configured into easy-to-understand business rules according to business requirements and business rule system;

[0081] (3) Use business rules and business logic parsing engine to generate standard data processing logic SQL:

[0082] like Figure 4 As shown in the figure, the blood relationship is generated reversely through business rules. According to different application scenarios and user groups, it is divided into: data blood relationship, business blood relationship, and code blood relationship.

[0083] Among them, data lineage is oriented towards IT / implementation / business, business lineage is oriented towards IT / implementation / business, and code lineage is oriented towards implementation;

[0084] The business rule parsing engine generates processing logic SQL based on business rules.

[0085] like Figure 5 As shown, the present invention designs a standard data model and business syntax, builds business rules and a business rule parsing engine based on the data model and business logic, and finally generates data clear processing logic.

[0086] The above shows and describes the basic principles, main features and advantages of the present invention. Those skilled in the art should understand that the present invention is not limited to the above embodiments. The above embodiments and descriptions only describe the principles of the present invention. The present invention may be subject to various changes and improvements without departing from the spirit and scope of the present invention. These changes and improvements fall within the scope of the present invention. The scope of protection claimed by the present invention is defined by the attached claims and their equivalents.

Claims

1. A method for generating logical SQL for financial multi-source data processing, characterized in that: The following steps are involved: 11) Design data model: Design data model by refining model usage scenarios. The data model includes standard root words, standard fields, model main table, model dimension diagram and model side diagram; 12) Design business grammar: Design business rules and parameters according to business requirements to form complete business grammar rules; 13) Build a business rule parsing engine: compile a business rule system and build a business rule parsing engine; 14) Generation of processing logic SQL: The business rule parsing engine parses the acquired business syntax and generates multi-data source processing logic SQL.

2. A method for generating logical SQL for financial multi-source data processing according to claim 1, characterized in that: The design of the service grammar includes the following steps: 21) Configure the relationship between model-driven tables and models based on the data model; 22) Configure data model slices, algorithm library, function library, business rules and logical rules.

3. A method for generating logical SQL for financial multi-source data processing according to claim 1, characterized in that: The construction of the business rule parsing engine comprises the following steps: 31) Set up a standard business rules system: The business rule system that sets the standard includes business rule columns, business algorithms, and functions; 32) Setting business rules includes: logical operators, keywords, logical branches, comparison operators, value expressions, and algorithm functions; 33) Set up a special business rule system: Set the rules for using brackets, inequality signs, and functions; 34) Build a business rule parsing engine based on the standard business rule system and the special business rule system.

4. A method for generating logical SQL for financial multi-source data processing according to claim 1, characterized in that: The generation of the processing logic SQL includes the following steps: 41) The business rule parsing engine obtains the business grammar to be parsed; 42) The business grammar to be parsed is configured into business rules according to business requirements and business rule system; 43) Use business rules and business logic parsing engine to generate standard data processing logic SQL: The blood relationship is generated reversely through business rules, and is divided into the following according to different application scenarios and user groups: Data kinship, business kinship, code kinship, Among them, data lineage is oriented towards IT / implementation / business, business lineage is oriented towards IT / implementation / business, and code lineage is oriented towards implementation; The business rule parsing engine generates processing logic SQL based on business rules.

Images