CN111325008A

CN111325008A - Formula calculation method based on distribution

Info

Publication number: CN111325008A
Application number: CN202010019355.0A
Authority: CN
Inventors: 李博; 赵成龙; 马海明
Original assignee: Zoomlion Technology Co ltd
Current assignee: Zoomlion Technology Co ltd
Priority date: 2020-01-08
Filing date: 2020-01-08
Publication date: 2020-06-23

Abstract

The invention discloses a formula calculation method based on distribution, which comprises the following steps: defining a general formula library and persisting; initializing a formula cross reference relationship; the system automatically evaluates the calculated amount and sends the calculation task to the node server; analyzing a formula calculation result; inquiring the affected formula and analyzing the formula calculation result; after each node server processes the calculation task, the calculation result is actively returned to the main server, and all the calculation results are aggregated and then are uniformly sent to the client. Compared with the traditional hard coding formula calculation, the distributed calculation method can obtain a dynamic adjustment formula without modifying codes; the method solves the problems of complicated and fussy formula reference and modification of evaluators, the convergence of error rate, responsibility and mass calculation tasks of the evaluators and the collapse of traditional calculation.

Description

Formula calculation method based on distribution

Technical Field

The invention particularly relates to a formula calculation method based on distribution.

Background

When using the evaluation method to price, the property evaluator needs to use a large number of complicated and tedious mathematical formulas to enter the formulas into the traditional EXCLE. The evaluators respectively make Excel templates by different pricing methods, pricing rules and calculation models, mutual reference among formulas, even sheet page reference and recursive calculation formulas are troublesome, and the evaluators are required to consume huge workload, and the method comprises the following steps:

1. when data of different Sheet pages in the same template are referenced, an evaluator is required to manually search a corresponding reference formula, and the data need to be modified one by one during modification, so that omission is avoided;

2. data between different templates is more troublesome to refer to, and corresponding data needs to be copied to another file manually, so that errors are easy to occur.

3. The pricing results need to be extracted and aggregated in a single file, the summary cells need to be copied to new files one by one, and complex aggregation functions need to be written.

The traditional evaluators generally summarize the comparison index information used by the pricing method, and the way is to summarize the final evaluation value by a summarization method such as an average method or a median method. In the process, an evaluator needs to find out corresponding summary values in different Excel templates as input parameters for summary, wherein the summary contents mainly comprise: row data summarization, column data summarization, specific cell data summarization, cross-file reference data summarization, and the like.

Based on the above manual pricing, ordering and summarizing processes, it can be known that the form needs to be repeatedly checked for data accuracy and data needs to be repeatedly modified in the evaluation process, and the process is easy to miss and error, and is undoubtedly a disaster for evaluators and data auditors. The evaluation line has complex business, many association parties, and the requirement change and formula change are a normal state, and when the business change needs to modify the formula, the traditional method is that an evaluator needs to modify a plurality of formula files corresponding to the formula, so that the formula is easy to be filled in by mistake or omitted in operation, and the evaluation result is greatly influenced.

Disclosure of Invention

The invention aims to provide a distributed formula calculation method, which adopts the architecture design of distributed micro-services to fully decouple systems, improve the fault tolerance rate of the systems, strengthen the flexibility of the systems, and meet increasingly huge calculation tasks by increasing nodes and smoothly upgrading interfaces when the system calculation amount reaches the bottleneck.

A formula calculation method based on distribution comprises three stages,

when in definition:

1) defining a general formula library and persisting; defining variables used in a formula, wherein the variables are divided into two types, one type is a common variable and is expressed in a coordinate form, and corresponding values are accurately positioned through the design of a cross point principle; the other is a special variable which represents a special reference relation design and is characterized by being globally unique and completely transparent to programming, and the variable can be specially processed according to a rule engine during system analysis;

2) initializing a formula cross reference relationship; the premise of initializing the formulas is to initialize the complex formulas to the database based on the definition of "1)" above, and if there is a mutual reference relationship between the formulas, the formulas need to be identified by a special field, so that when the system is in operation, the mutual reference relationship between the formulas can be clearly resolved, and further, the formulas are processed by a recursive function.

And (3) running:

1) the system automatically evaluates the calculated amount and sends the calculation task to the node server; when the system receives the calculation task, the main server distributes the task to the node servers according to an average distribution algorithm and a system resource exhaustion algorithm, the node servers receiving the task start a formula rule engine firstly,

2) and analyzing the variables in the formula, searching corresponding values among the systems through the definition rules of the variables, further bringing the values into the formula, and calculating a final result.

3) Processing other formulas influenced by the current formula and calculating the result according to the steps 1) -2). After the current formula is calculated, the system checks whether other influenced formulas exist, if a formula engine exists, the formula engine automatically recurs upwards or downwards according to conditions, all the interrelated formulas are recalculated once again, and the calculation results are respectively stored.

4) After each node server processes the calculation task, the calculation result is actively returned to the main server, and further, all the calculation results are aggregated and then are uniformly sent to the client.

The distributed formula method adopts the parallel calculation of the master-slave node servers, supports multithreading and dynamic capacity expansion and improves the calculation efficiency.

And (3) closing the resources:

after receiving the calculation completion signal, the main server releases all used resources such as formula variable names, variable values and the like, and the utilization rate of system resources is improved.

Compared with the prior art, the invention has the beneficial effects that:

1. compared with the traditional hard coding formula calculation, the distributed calculation method can obtain a dynamic adjustment formula without modifying codes;

2. the invention solves the problems of complicated formula reference and modification of evaluators;

3. the invention solves the problems of the total error rate and the responsibility of an evaluator;

4. the invention solves the problems of massive computing tasks and traditional computing collapse.

Drawings

FIG. 1 is a flow chart of a distributed formula calculation method;

FIG. 2 is a structural framework diagram of a distributed formula calculation method;

FIG. 3 is a schematic flow chart of a distributed equation computation method invocation;

Detailed Description

The technical solutions in the embodiments of the present invention will be clearly and completely described below with reference to the drawings in the embodiments of the present invention, and it is obvious that the described embodiments are only a part of the embodiments of the present invention, and not all of the embodiments. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present invention.

The embodiment of the invention provides a formula calculation method based on distribution, and the method comprises the following specific steps with reference to fig. 1-3:

s1, defining a general formula library and persisting;

s2, initializing a formula cross reference relationship;

s3, the system automatically evaluates the calculated amount and sends the calculation task to the node server.

And S4, analyzing the formula calculation result.

S5, processing the affected formula and analyzing the formula calculation result

S1.1 defines the variables used in the formula, such as: "A1" indicates column A, row 1, and so on; the special variable needs to be processed according to a specific service, such as a10001, where "10001" does not represent the 10001 line, and it represents a special variable, which is used for the special processing according to the design of the rule engine during parsing, and the specific operation is:

s1.1.1, formula definitions are defined in connection with a specific service, all fields required to be used by a formula are defined in a specific table, and the design of the table has certain characteristics, for example: the list of the table is 'A, B, C, D ….' and so on, and a basis is provided for subsequent data extraction;

s1.1.2 finding variable values from the coordinates of the rows and columns in defining the formula according to the S1.1 Table field design such as: a1 denotes column a, first row, is the only value that can be denoted as a1(a, 1);

s2.1, initializing the mutual reference relation between the formulas into the relation data when the system is initialized. The different formulas are mutually influenced, the association between the different formulas is marked by a field in the database, and the formula engine checks the field at runtime to determine whether to perform the next calculation;

and S3.1, when the system main server receives the calculation task, reasonably sending the task to the node server by adopting an average distribution method and a resource glowing method according to the task amount, and executing the next task by the node server receiving the task, wherein the state of the node server is ready at this moment. The function is as follows: readyhandles (tasks);

s4.1, the node server receives the calculation task, and preloads variables contained in the analytical formula into a memory; and extracting values corresponding to the variables through a search algorithm according to the types of the variables, and preloading the values into a memory.

S4.2, calling JEP a calculation result and replacing the result into a corresponding formula if the function is contained in the formula; according to the execution steps of A-E, the searched values are substituted for variables in the formula, and the JEP engine is called to bring the formula into the calculated result.

And S5.1, recursively processing other formulas influenced by the current formula according to the steps.

And S5.1.1, taking the current formula as a condition, inquiring the affected formula by the relational database, and storing the result in the memory.

S5.1.2, calculating the corresponding formula result according to the steps of S1-S4, and storing the result in the memory.

S5.1.3, and recursive processing 5.1.1-5.1.2. The recursion processing is relatively time-consuming operation, and multithreading processing is used, so that the CPU resource of the server is reasonably utilized to the maximum extent. And returning the operation result to the executor.

S5.1.4, when the user updates a value expression of a formula, the result influenced by the formula is automatically calculated and stored;

s5.1.5, the system saves the result after the operation through the steps and returns the result to the calling end.

The present invention is not limited to the embodiments described above, and those skilled in the art can make various equivalent modifications or substitutions without departing from the spirit of the present invention, and these equivalent modifications or substitutions are included in the scope defined by the claims of the present application.

Claims

1. A distributed formula calculation method is characterized by comprising the following steps:

defining a general formula library and persisting;

initializing a formula cross reference relationship;

the system automatically evaluates the calculated amount and sends the calculation task to the node server;

analyzing a formula calculation result;

the affected formula is queried and the formula calculation results are parsed.

2. The distributed formula calculation method based on claim 1, wherein the defining and persisting a general formula library comprises:

the variables needed to be used in the general formula are first defined: it is the smallest factor in a formula engine and is characterized by being based on a coordinate form, such as: p (X, Y), wherein X represents a data row and Y represents a data column; the corresponding variable names are respectively represented as X1 or Y1;

secondly, common formulas are defined, each formula consisting of common variables, such as: (a1+ B1) -C2 × D3;

in addition, some special formula definitions are provided, which are characterized in that variables in the formula definitions are inconsistent with common variables, the formula definitions are not suitable for coordinate forms, but are globally unique values which represent special meanings, and special processing is performed when special variables are encountered during formula engine analysis; the formula containing the special variables is defined as a special formula, such as: (A10001+ B10006) -C1/D5, wherein "A100001" does not represent column A, row 100001;

in a scenario of actually applying formulas, a situation that another formula is included in a formula usually occurs, and for such a usage scenario, a system supports cross-reference of formulas, as long as it is satisfied that cross-reference between formulas is not an infinite loop, and a cross-reference relationship between formulas requires that the system is initialized and configured in a relational database and identifies a reference relationship between two formulas through a specific field.

3. The method of claim 2, wherein the generic formula engine parses and computes results, comprising:

A. acquiring the number of formulas to be calculated, and sending the formulas to be calculated to a designated node server according to an average distribution algorithm and a glory-run algorithm;

B. the node server receives the calculation task, and preloads variables contained in the analytical formula into a memory;

C. extracting values corresponding to the variables through a search algorithm according to the types of the variables (common variables and special variables), and preloading the values into a memory;

D. if the formula contains a function, calling JEP to calculate the result and replacing the result into the corresponding formula;

E. replacing variables in the formula with the searched values according to the execution steps of A-D, and calling JEP an engine to bring the formula into a calculation result;

F. and recursively processing other formulas influenced by the current formula according to the steps.

4. The method of claim 3, wherein processing the affected formula using a recursive algorithm comprises:

A. taking the current formula as a condition, and inquiring the affected formula by the relational database;

B. calculating a corresponding formula result according to the steps of claim 3;

C. recursion processing A-B steps;

D. when a user updates a certain value expression of a certain formula, the result influenced by the formula is automatically calculated and stored.