CN111241799A - Iterative calculation method for massive formulas of online server - Google Patents

Abstract

The invention relates to a method for iteratively calculating a large number of formulas of an online server, which comprises the following steps: the method comprises the steps of identifying a data formula on line, verifying data compliance, dynamically analyzing the formula, supporting a special formula in Excel, defining the formula, standardizing and dynamically replacing the formula. By defining, standardizing and checking the formula and analyzing and calculating the expression by using jep, various complex mathematical calculations including power operation, exponential operation, conditional operation, column operation, Excel formula calculation, loop iteration calculation and receiving and classifying and analyzing calculation of a large amount of data can be supported. The invention can calculate mass data, so that a company can flexibly formulate a formula according to self service requirements, improve the working efficiency for service operators, save the development cost for a development team, and greatly improve the development efficiency of a calculation function, the operation efficiency of a page and the response speed.

Description

Iterative calculation method for massive formulas of online server

Technical Field

The invention relates to a method for iteratively calculating a large number of formulas of an online server.

Background

In recent years, large data is frequently used online, which is a service accessing mass data, however, since the online platform table data calculation is too single mechanical, the data calculation is not fast and efficient enough, the calculation accuracy requirement of the data is easy to ignore, and there is no clear formula definition and specification for hundred million-level calculation. The computing precision of the web side is often not accurate enough, the computing efficiency is not high, and the mental concentration of operators is needed for the frequently encountered calculation of mass data and the computation and verification of the data. From past experience, the method is strong in data repetition and mechanicalness, and has no online instant storage, accurate and rapid target range calculation and global efficient storage. The design meets the actual operation requirements of customers, the calculated numerical value is accurate, and the logic is clear; the method has the advantages of concise and clear program design, easy maintenance, high operating efficiency, high degree of structurization and clear layering. Therefore, the traditional online data calculation is too dependent on the business regulation category, and lacks of normalization, simplicity, rapidness, high efficiency, scientificity and flexibility.

Disclosure of Invention

Aiming at the problems, the invention provides an iterative calculation method for a large number of formulas of an online server, which comprises the following steps:

designing a database of a management formula;

positioning and coordinate definition of formulas and data;

a single computational logic;

acquiring the position and the value of a user cell and performing separate calculation;

calculating according to data and formulas input by a user and calculating and storing values related to the affected areas and the business relations;

calculating and storing the data copied and pasted by the user;

dynamically modifying a formula through service analysis and calculating stored data;

triggering through a page calculation button, and performing cyclic calculation solving;

distributed storage is realized;

realizing diffusion calculation;

and realizing cooperative operation.

The method comprises the steps that a business relation hidden in a formula is designed in a database, and the database is designed in a tree data structure, so that iterative calculation of massive formulas is carried out;

the characteristics of the formula and the massive formula are designed according to the flexibility of the formula definition, so that the formula definition is more flexible;

obtaining formulas and assignments, and then quickly calculating to obtain a result;

adopting redis and rabbitMQ to perform distributed storage;

the coordinate of the data is flexibly matched by adopting a regular method, and the data format of the key value pair is as follows: bond: a value of a formula A5 is formula data corresponding to the cell, the formula data is matched with a formula to be executed to replace variables and dynamic formulas in the formula, and a calculation expression is executed by JEP to obtain a result;

the formula is defined as an EXCEL expression mode, wherein a dynamically modified formula needs to be specially named and identified by [ C ];

and importing the excel worksheet formula of the original service into the database for cleaning, and then storing the excel worksheet formula in a persistent mode for standby.

Further, a single logic and formula design are combined and matched into a circular formula, wherein the method comprises the following steps:

and defining an initial formula, assigning values to the initial cells by loop calculation, starting iteration through designed tree-shaped calculation logic, stopping iteration when the final result value is correct, assigning values to all formulas of the loop calculation, and obtaining the final results of all values in each iteration formula for persistent storage.

Further, the matrix data batch calculation includes:

receiving the execution of pasting operation of the data, and acquiring all coordinates and numerical values of all data of the current operation;

performing cyclic execution according to all coordinates and the set of numerical values, then distributing the data to an MQ queue for persistence, and sending the result to a large UI end for web page rendering;

in Excel, cyclic calculation is performed in calculation, for example, cyclic reference formulas such as "A8 ═ a9+ a 10", a9 ═ A8+ a7 × A3 "and the like are used, and the cyclic calculation is controlled for 100 times, and initial values of" 1-10 "are given to variables at the beginning of calculation and then the calculation is performed;

the mutual reference processing between formulas and the calculation formula between the Sheet pages, for example, the mutual reference mode is adopted between the formulas of 'asset liability table A10 ═ profit table B10' and 'profit table B10 ═ cash flow B15 ═ C15', and the reference can reach dozens of formulas in the actual business logic, thereby forming chain calculation, when different users modify cells, the system adopts distributed design, and the mutual influence and the final calculation result are not influenced;

the database design is designed in a tree structure mode, the chain calculation is determined by the interaction columns among all formulas on formula identification calculation, and the mesh structure is scattered in a tail recursion mode by matching with the recursion operation on program design, for example: the A influence B > B influence C > C influence D, and the specific operation process of the calculation of the chain structure is as follows:

the front-end parameters are related parameters of A, the obtained formula, numerical value and influence column of A are stored in a memory, the used EHcache of java is a cache in a pure process, the efficiency on data acquisition is high, and the operation is simple and easy;

after the related formula of A is calculated, the information of the affected cells is stored in an array structure of a List for the next calculation, and the iterative calculation is finished until the cells which are not affected at all are finally calculated;

when in calculation, the execution of a series of sql sentences in a persistent mysql database can seriously affect the user experience, and the database and front-end page data are synchronized in a message queue mode.

Compared with the prior art, the invention has the beneficial effects that: the method has the advantages of fast calculation, high evaluation precision, use of BigDecimal in the calculation process, inclusion of common mathematical functions and operators, support of Boolean expressions, good expandability and configurability, provision of modification functions, and maintenance of flexibility and expandability of programs. When a custom field is newly added or a calculation formula is changed, the code does not need to be modified, and the calculation formula only needs to be configured again. And the multi-user simultaneous operation interface is supported, and the cooperative operation is realized.

Drawings

FIG. 1 is a flow chart of the overall calculation method provided by the present invention;

FIG. 2 is a flow diagram of single compute logic;

FIG. 3 is an illustration of a database design;

FIG. 4 is a flow chart of JEP calculation design.

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 invention discloses an iterative calculation method of a large number of formulas of an online server, which comprises the following steps: the method comprises the following specific steps:

s1, acquiring data recorded by a user at an appointed position and coordinate positioning thereof, wherein the data is data required by the user, the positioning is used for uniformly positioning the data and a formula in a three-dimensional coordinate form, for example, ". X.A 3: 89" indicates that the data in the third row of the A column on a certain page is 89, and the formula form, ". X.D 9 ═ A3 +. B5" indicates that two values are summed and the data and the formula are matched one by one in the appointed coordinate form;

s2, 1, wherein the matching coordinate uses a regular coordinate separation method;

2. the variable value is a class definition in the alternative formula including:

2.1 dynamic formula substitution is to use "[ base day ] to identify specific substitution variable content;

2.2 in the formula, "financial cost W42 ═ financial cost W36+ financial cost W39", where "financial cost W42" is the result item of the calculation, "financial cost W36" and "financial cost W39" are the known items, and replacing the location identifier after "financial cost W42 ═ with data results in" financial cost W42 ═ 18 ";

s3, storing the numerical values in redis is to deal with the pressure of business personnel sharing the database during cooperative operation, updating the redis data when the trend of the data in the redis is the initial data import, updating when a client modifies the data, and updating when the data calculation is finished and the persistence operation is finished, so as to ensure that the data in the redis of the client, the server and the database are consistent, and ensure that the result obtained during the data calculation is accurate;

s4, obtaining an executable computational expression through the variables of the regular substitution formula:

1. performing regular identification on the coordinates in the regular separation;

2. checking whether the coordinates are in compliance;

3. by identifying the business requirements of different pages, the formula is dynamically processed, such as: all formulas are dynamically generated when the total cost and the total turnover of all products are calculated in business balance, and business operation is flexible and dynamic;

4, "[ product ] identification substitution formulas need to be substituted according to specific calculation templates and serve as fixed service variables;

5. the trinocular operational expression is defined according to jep operational rules;

s5, after generating jep executable mathematical expressions, performing classified execution, processing the results into normal numerical values for display and persistence, and processing the results with 0 after calculating the abnormity;

s6, splicing the persistent statements in a coordinate definition mode in the calculation process, sending the persistent statements to a message queue for persistent operation, and avoiding too much operation data and too long page response time;

s7, the detailed calculation process is carried out in a chain structure, a recursive programming is adopted, and one cell influences a plurality of cells and gradually diffuses outwards.

And S8, returning the cell values affected by the calculation and the coordinates thereof as keys to the web end to be processed by the personnel at the web 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 (3)

1. A method for iteratively calculating a large number of formulas of an online server comprises the following steps:

designing a database of a management formula;

positioning and coordinate definition of formulas and data;

a single computational logic;

acquiring the position and the value of a user cell and performing separate calculation;

calculating the relation between the affected area and the service according to the data and the formula input by the user;

calculating and storing the associated value;

calculating and storing the data copied and pasted by the user;

dynamically modifying a formula through service analysis and calculating stored data;

triggering through a page calculation button, and performing cyclic calculation solving;

distributed storage is realized;

realizing diffusion calculation;

realizing cooperative operation;

the method comprises the steps that a business relation hidden in a formula is designed in a database, and the database is designed in a tree data structure, so that iterative calculation of massive formulas is carried out;

the characteristics of the formula and the massive formula are designed according to the flexibility of the formula definition, so that the formula definition is more flexible;

obtaining formulas and assignments, and then quickly calculating to obtain a result;

adopting redis and rabbitMQ to perform distributed storage;

the coordinate of the data is flexibly matched by adopting a regular method, and the data format of the key value pair is as follows: bond: a value of a formula A5 is formula data corresponding to the cell, the formula data is matched with a formula to be executed to replace variables and dynamic formulas in the formula, and a calculation expression is executed by JEP to obtain a result;

the formula is defined as an EXCEL expression mode, wherein a dynamically modified formula needs to be specially named and identified by [ C ];

and importing the excel worksheet formula of the original service into the database for cleaning, and then storing the excel worksheet formula in a persistent mode for standby.

2. The method of claim 1, wherein the employing of a single logic in combination with formulation design matches into a cyclic formulation wherein the method comprises:

and defining an initial formula, assigning values to the initial cells by loop calculation, starting iteration through designed tree-shaped calculation logic, stopping iteration when the final result value is correct, assigning values to all formulas of the loop calculation, and obtaining the final results of all values in each iteration formula for persistent storage.

3. The method of claim 1, wherein the matrix data batch computation comprises:

receiving the execution of pasting operation of the data, and acquiring all coordinates and numerical values of all data of the current operation;

performing cyclic execution according to all coordinates and the set of numerical values, then distributing the data to an MQ queue for persistence, and sending the result to a large UI end for web page rendering;

in Excel, cyclic calculation is performed in calculation, for example, cyclic reference formulas such as "A8 ═ a9+ a 10", a9 ═ A8+ a7 × A3 "and the like are used, and the cyclic calculation is controlled for 100 times, and initial values of" 1-10 "are given to variables at the beginning of calculation and then the calculation is performed;

the mutual reference processing between formulas and the calculation formula between the Sheet pages, for example, the mutual reference mode is adopted between the formulas of 'asset liability table A10 ═ profit table B10' and 'profit table B10 ═ cash flow B15 ═ C15', and the reference can reach dozens of formulas in the actual business logic, thereby forming chain calculation, when different users modify cells, the system adopts distributed design, and the mutual influence and the final calculation result are not influenced;

the database design is designed in a tree structure mode, the chain calculation is determined by the interaction columns among all formulas on formula identification calculation, and the mesh structure is scattered in a tail recursion mode by matching with the recursion operation on program design, for example: the A influence B > B influence C > C influence D, and the specific operation process of the calculation of the chain structure is as follows:

the front-end parameters are related parameters of A, the obtained formula, numerical value and influence column of A are stored in a memory, the used EHcache of java is a cache in a pure process, the efficiency on data acquisition is high, and the operation is simple and easy;

after the related formula of A is calculated, the information of the affected cells is stored in an array structure of a List for the next calculation, and the iterative calculation is finished until the cells which are not affected at all are finally calculated;

when in calculation, the execution of a series of sql sentences in a persistent mysql database can seriously affect the user experience, and the database and front-end page data are synchronized in a message queue mode.

Images