Method, system and storage medium for processing custom formula based on directed graph
By using directed graph processing methods in custom formulas, detecting and simplifying circular dependencies, the problem of computational complexity of custom formulas is solved, and the formulas are quickly simplified and flexible logical processing is achieved, ensuring the accuracy of calculations and Satisfied business needs.
Patent Information
- Application Number
- CN202111119570.9
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2021-09-24
- Publication Date
- 2024-12-10
- Estimated Expiration
- 2041-09-24
AI Technical Summary
Custom formulas are prone to vicious loops and multi-layer nesting during the calculation process, resulting in complex calculations and it is difficult for the existing technology to effectively detect and simplify the circular dependency problem.
Using directed graph-based processing method, the circular dependence in the formula is detected through the loop detection algorithm, and the reverse dependence processing is performed, the custom formula is converted into a simple indicator representation, avoiding the dead loop, and simplifying the formula.
It effectively avoids the problem of dead loop, simplifies the calculation process of custom formulas, realizes rapid simplification of formulas, and supports customized logical processing, such as unit conversion and proportional conversion, ensuring the accuracy and flexibility of calculations.
Smart Images
Figure CN113886459B_ABST
Abstract
Description
Technical Field
[0001] The present invention belongs to the technical field of data processing, and in particular relates to a method and system for processing a custom formula based on a directed graph. Background Art
[0002] With the development of big data technology, data processing technology is being used more and more widely in various scenarios. When calculating the results of complex indicators, custom formulas composed of simple indicator calculations are widely used in various data processing and calculation processes because of their flexible assembly and clear business calculation rules.
[0003] Due to its flexible nature, custom calculation formulas can be composed of simple indicators through logical superposition, four arithmetic operations and other rules, or they can be composed of other custom calculation formulas nested in each other. Therefore, there is a natural possibility of multi-layer nesting in custom formulas.
[0004] Multiple nestings are prone to infinite loops, which in turn lead to complex calculations. Before calculating custom formulas, you need to simplify the custom formulas into simple indicator representations. At this time, if there is a circular dependency in the formula, that is, formula B is used in formula A, and formula A is used in formula B, an infinite loop will occur. In order to avoid infinite loop problems in formulas with circular dependencies during calculations, it is necessary to detect circular dependency problems in the formulas before simplifying custom formulas into simple indicator representations. In addition, when custom formulas are nested in multiple layers, simple indicators that can be replaced with data cannot be used directly when calculating the formula results. You need to simplify the formula first, convert the custom formula representation into a simple indicator representation, and then directly replace the simple indicator with specific data to calculate the final formula result.
[0005] Therefore, how to propose a method to solve the multiple dependencies of custom formulas is a technical problem that needs to be solved at present. Summary of the invention
[0006] The purpose of the present invention is to provide a method and system for processing custom formulas based on directed graphs to solve the problem of complex calculation processing caused by circular dependencies and multi-layer nesting in the prior art.
[0007] In order to solve the above technical problems, a first aspect of the present invention provides a method for processing a custom formula based on a directed graph, the method comprising:
[0008] Load the custom formula from the storage area and find the elements that the formula depends on, and construct a directed graph forward based on the elements;
[0009] Performing dependency detection on elements in the directed graph using a loop detection algorithm;
[0010] Perform reverse dependency processing to simplify the formula;
[0011] Query the simplified custom formula based on the formula ID and substitute it into the business system to complete the calculation.
[0012] Furthermore, the custom formula is stored in the Zookeeper node and is assigned a unique ID, and is backed up in MySQL.
[0013] Furthermore, the method also includes: automatically monitoring data changes of each Zookeeper node, and when there is a custom formula update in the Zookeeper node, reloading the updated custom formula from the storage area and performing subsequent processing.
[0014] Furthermore, in the process of executing reverse dependency processing, the custom processing logic is abstracted into a functional interface, and the business system performs custom logic processing of the formula according to actual needs.
[0015] Further, the performing dependency detection on the elements in the directed graph using a ring detection algorithm includes:
[0016] Construct a vertex in-degree array, where the array subscript corresponds to the vertex index and the array stores the in-degree of the corresponding vertex;
[0017] Construct an auxiliary queue and put the vertices with in-degree 0 into the queue;
[0018] Take out the vertex with in-degree 0 from the auxiliary queue, reduce the in-degree of the vertex it points to by 1 and update the in-degree array. If the in-degree of the vertex it points to is 0, put it into the queue. Otherwise, continue with step 2 and take the vertex from the queue.
[0019] After processing all vertices with in-degree 0, when the queue is empty, determine whether there are vertices with in-degree not 0. If so, it means there is a cycle in the directed graph, and make an exception. If not, it means there is no cycle in the directed graph, and perform subsequent reverse dependency processing.
[0020] Furthermore, the reverse dependency processing includes:
[0021] Construct a vertex out-degree array, where the array subscript corresponds to the vertex index and the array stores the out-degree of the corresponding vertex;
[0022] Construct an auxiliary queue and put the vertices with out-degree 0 into the queue;
[0023] Loop through the unvisited vertices, take out the vertices with out-degree 0 from the queue, process the data of the taken out vertices with out-degree 0 through a custom function, find the parent vertex according to the reverse edge, reduce the out-degree of the parent vertex by one, and mark the vertex as visited;
[0024] Traverse the out-degree array, and put the vertices with out-degree 0 and unvisited into the queue;
[0025] Exit the loop when all vertices have been visited.
[0026] Furthermore, when the custom processing logic is a simple formula replacement, starting from the leaf vertex represented by a simple indicator, the custom formula is replaced in the directed graph in sequence upward, and finally the custom formulas in all vertices in the entire directed graph are represented by simple indicators and calculated directly; when the custom logic needs to increase business logic according to the business scenario, the calculated logic data is added to the formula corresponding to the leaf vertex during the simplification process, wherein the business logic includes: unit conversion and ratio conversion.
[0027] Another aspect of the present invention further provides a system for processing a custom formula based on a directed graph, the system comprising:
[0028] A formula loading component loads a custom formula from a storage area and searches for elements on which the formula depends, and constructs a directed graph forward based on the elements;
[0029] A dependency detection processing component performs dependency detection on elements in the directed graph using a loop detection algorithm;
[0030] A reverse dependency processing component performs reverse dependency processing and simplifies the formula;
[0031] The calculation component queries the simplified custom formula based on the formula ID and substitutes it into the business system for calculation.
[0032] Furthermore, it also includes: a monitoring component for monitoring whether the custom formula stored in each Zookeeper node has changed. When there is a custom formula update, the updated custom formula is reloaded from the storage area and subsequent processing steps are performed.
[0033] According to a third aspect of the present invention, a computer-readable storage medium is provided, on which a computer program is stored, wherein the computer program is executed by a processor to perform the steps of the method described in the first aspect of the present invention.
[0034] Compared with the prior art, the method and system for processing a custom formula based on a directed graph provided by the present invention have the following technical effects:
[0035] 1. The present invention performs a circular dependency check before simplifying the formula, thereby avoiding the occurrence of subsequent dead loop problems.
[0036] 2. The present invention simplifies the formula and converts the custom formula expression into a simple indicator expression, thereby solving the simplification problem of multi-layer nested custom formulas.
[0037] 3. The present invention can achieve various business goals by executing custom logic. Custom logic can be added flexibly, and specific business logic processing can be performed on simple dependent indicators, such as format conversion, unit conversion, etc. of the original data in the formula.
[0038] 4. The formula of the present invention is stored in the Zookeeper node. When the custom formula changes, the monitoring component automatically senses and obtains the updated formula information, rebuilds the graph and dependency processing, and makes the changes in the formula transparent to the business user. BRIEF DESCRIPTION OF THE DRAWINGS
[0039] Figure 1 It is a flowchart of a method for processing a custom formula based on a directed graph according to an embodiment of the present invention.
[0040] Figure 2 It is a structural diagram of a system for processing custom formulas based on a directed graph according to an embodiment of the present invention.
[0041] Figure 3 It is a logic flow chart of executing a reverse dependency process using a directed graph according to an embodiment of the present invention.
[0042] Figure 4 It is a topological diagram of the circular dependency of the detection formula of the embodiment of the present invention.
[0043] Figure 5 It is a topological diagram of reverse dependency processing in an embodiment of the present invention. DETAILED DESCRIPTION
[0044] The following examples are only used to more clearly illustrate the technical solutions of the present invention, and are not intended to limit the scope of protection of the present invention. For example, certain words are used in the specification and claims to refer to specific components. Those skilled in the art should understand that hardware or software manufacturers may use different nouns to refer to the same component. This specification and claims do not use the difference in name as a way to distinguish components, but use the difference in function of the components as the criterion for distinction. The subsequent description of the specification is a preferred embodiment of the present invention, but the description is for the purpose of illustrating the general principles of the present invention, and is not intended to limit the scope of the present invention. The scope of protection of the present invention shall be determined by the definition of the attached claims.
[0045] The present invention is further described in detail below with reference to the accompanying drawings and specific embodiments.
[0046] like Figure 1 As shown, an embodiment of the present invention discloses a method for processing a custom formula based on a directed graph, comprising the following steps:
[0047] Step S1, loading a custom formula from a storage area and searching for elements on which the formula depends, and constructing a directed graph forward according to the elements;
[0048] The custom formula data is stored in the Zookeeper node and assigned a unique ID, and a backup is saved in MySQL. The stored custom formula is loaded from the storage area through the formula loading component, and then all dependencies, that is, the elements that the formula depends on, are loaded according to the dependency relationship, and a directed graph is constructed forward. Each non-leaf vertex in the directed graph is a custom formula, and each leaf vertex with an out-degree of 0 is a simple indicator that can be directly calculated.
[0049] Step S2, using a loop detection algorithm to perform dependency detection on the elements in the directed graph;
[0050] The circular dependency is detected using the ring detection algorithm in the directed graph. The specific steps of the algorithm are as follows:
[0051] 1. Construct a vertex in-degree array. The array subscript corresponds to the vertex index, and the array saves the in-degree of the corresponding vertex.
[0052] 2. Construct an auxiliary queue, and add the points with in-degree 0 into the queue;
[0053] 3. Take out the vertex with in-degree 0 from the auxiliary queue and "prune" its forward dependent edge, that is, reduce the in-degree of the vertex pointed to by 1 and update the in-degree array. If the in-degree of the pointed vertex is 0, enter the queue, otherwise continue with step 2 and take the vertex from the queue;
[0054] 4. The queue is empty, that is, all vertices with in-degree 0 have been processed. Finally, it is determined whether there are vertices with in-degree not 0. If there are, it means that there is a cycle in the graph, that is, the custom formula is considered to have a circular dependency, and corresponding exception processing is performed. For example, ACDA in the figure has a cycle. If not, it means that the graph has no cycle, and the subsequent reverse dependency processing can be performed.
[0055] like Figure 4 In the figure, the arrow indicates the topological detection direction of the circular dependency, and * indicates the leaf vertex corresponding to the simple indicator, which is equivalent to the last branch of the directed graph, and there are no other adjacent nodes after it.
[0056] Step S3, perform reverse dependency processing to simplify the formula;
[0057] Before performing reverse dependency processing, add custom business logic and simplify the formula to a simple indicator representation. The custom processing logic is abstracted into a functional interface through the reverse dependency processing component. The business system can freely define the logic according to actual needs. The core of the reverse dependency processing component is to perform reverse processing based on the dependency relationship of the directed graph, that is, starting from the leaf vertex, processing its relationship with the dependent vertex until the vertex with an in-degree of 0 is processed.
[0058] Reference Figure 3 As shown, the specific steps of reverse dependency processing are as follows:
[0059] 1. Construct a vertex out-degree array, the array subscript corresponds to the vertex index, and the array saves the out-degree of the corresponding vertex;
[0060] 2. Construct an auxiliary queue and put the vertices with out-degree 0 into the queue;
[0061] 3. Loop through the unvisited vertices, take out the vertices with out-degree 0 from the queue, process the vertex data with out-degree 0 through the custom functional interface, find the parent vertex according to the reverse edge, the parent vertex corresponds to the out-degree minus one, and mark the parent vertex as visited; the parent vertex refers to the previous vertex that a vertex depends on, if A->B, then A is the parent vertex of B, and a vertex may have multiple parent vertices;
[0062] 4. Traverse the out-degree array, and put the vertices with out-degree 0 and unvisited into the queue;
[0063] 5. Exit the loop when all vertices have been visited.
[0064] The out-degree of the parent vertex is a vertex attribute. In order not to change the graph itself, the out-degree attribute will not be modified during operation. Therefore, an out-degree array record is established for each vertex. When the operation modifies the out-degree, only the out-degree value corresponding to the vertex in the array is modified. When querying, the corresponding out-degree value is also queried in the array.
[0065] like Figure 5 In the figure, the dotted arrows point to the topological direction of the reverse dependent simplified formula, and * indicates the leaf vertex corresponding to the simple index.
[0066] The result after graph reverse processing can realize the addition of custom logic:
[0067] (1) When the custom processing logic is a simple formula replacement, the leaf vertex represents a simple indicator. The custom formula can be replaced in the directed graph in sequence. Finally, the custom formulas in all vertices in the entire graph are represented by simple indicators and can be directly calculated.
[0068] (2) When business logic needs to be added to the custom logic according to different business scenarios, such as unit conversion, ratio conversion, multiplication by a certain base, division by a certain base, etc., the calculated logic data can also be added to the formula during the simplification process, and corresponding adjustments can be made according to the changed business scenarios. For example, when the unit of the amount data needs to be converted (RMB to 10,000 yuan), the amount can be multiplied or divided by the corresponding multiple when processing the leaf vertex in the custom logic. Or if a certain statistical amount needs to be uniformly deducted at a ratio of 30% in the custom logic, the amount represented by the custom formula at the leaf vertex is directly multiplied by 30%, and other nodes can be automatically changed according to the data changes of the leaf vertex, which is extremely convenient to operate.
[0069] Step S4: Query the simplified custom formula according to the formula ID and substitute it into the business system for calculation.
[0070] When the reverse dependency processing is completed, the simplified custom formula can be queried according to the formula ID (corresponding ID of the vertex in the directed graph), and the business system can directly substitute real data and calculate.
[0071] In addition, the present invention also sets up monitoring for Zookeeper nodes to automatically monitor data changes of each node. When the node data is updated, that is, when the formula is updated, it automatically senses and reloads the updated custom formula, and then repeats subsequent business processing, including building a directed graph, circular dependency detection, reverse dependency processing, simplification, etc. The business user is transparent to this process.
[0072] The steps in the embodiment of the present invention are described in detail below through a specific case.
[0073] Set f as a custom formula rule, and m as a simple indicator. There are custom formulas A = f(B, C), B = f(m), C = f(D), and D = f(m), where custom formula A is composed of formulas B and C, formula B is composed of simple indicator m, formula C is composed of formula D, and formula D is composed of simple indicator m.
[0074] The specific processing steps are as follows:
[0075] 1) The loading component loads the custom formula A from the storage area, finds the elements that A depends on, namely B and C, and searches in sequence until the simple indicator is reached, and constructs each formula element as a directed graph vertex, the simple indicator as a leaf vertex, and constructs a directed graph forward.
[0076] 2) The detection component uses the algorithm for detecting loops in directed graphs, that is, it uses vertices with in-degree 0 to "prune" the dependent edges of the vertices that depend on them in turn to detect whether there are loops in the directed graph. If there are loops, exception processing is performed. If there are no loops, the next step of reverse dependency processing is performed.
[0077] 3) Implement the reverse dependency processing algorithm for directed graphs, that is, start from the leaf vertex and follow the reverse edge to process in sequence, add custom processing logic according to business needs and perform reverse dependency processing. Replace the dependent item with the dependent item, that is, perform formula simplification operation, and add other logic according to business needs, such as adding unit conversion logic in the process of simplifying the formula.
[0078] 4) After the reverse dependency processing is completed, the simplified custom formula can be queried based on the formula ID, that is, the corresponding ID of the vertex in the directed graph, and the business system can directly substitute the real data and calculate.
[0079] During the whole process, the monitoring component monitors the Zookeeper node. When a formula is updated, it automatically senses and reloads the updated custom formula, and then repeats the subsequent simplification process.
[0080] Through the method in this embodiment, the problem of dead loop dependency in custom formulas can be automatically detected, multi-layer nested custom formulas can be quickly simplified, and corresponding processing logic can be added according to different business needs to enhance the function of the formula, such as adding logic such as unit conversion.
[0081] In addition, alternatively, the above method can be implemented by a computer program product, i.e., a computer-readable storage medium. The computer program product may include a computer-readable storage medium on which are loaded computer-readable program instructions for executing various aspects of the present invention. The computer-readable storage medium may be a tangible device that can hold and store instructions used by an instruction execution device. The computer-readable storage medium may be, for example, but not limited to, an electrical storage device, a magnetic storage device, an optical storage device, an electromagnetic storage device, a semiconductor storage device, or any suitable combination thereof. More specific examples (non-exhaustive lists) of computer-readable storage media include: a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), a static random access memory (SRAM), a portable compact disk read-only memory (CD-ROM), a digital versatile disk (DVD), a memory stick, a floppy disk, a mechanical encoding device, for example, a punch card or a protruding structure in a groove on which instructions are stored, and any suitable combination thereof. The computer-readable storage medium used herein is not to be construed as a transient signal per se, such as a radio wave or other freely propagating electromagnetic wave, an electromagnetic wave propagating through a waveguide or other transmission medium (e.g., light pulses through a fiber optic cable), or an electrical signal transmitted through wires.
[0082] Please refer to Figure 2 As shown, another embodiment of the present invention further provides a system for processing a custom formula based on a directed graph, the system comprising:
[0083] A formula loading component loads a custom formula from a storage area and searches for elements on which the formula depends, and constructs a directed graph forward based on the elements;
[0084] A dependency detection processing component performs dependency detection on elements in the directed graph using a loop detection algorithm;
[0085] A reverse dependency processing component performs reverse dependency processing to simplify the formula and simplify the formula to a simple indicator representation form;
[0086] The calculation component queries the simplified custom formula based on the formula ID and substitutes it into the business system for calculation.
[0087] The system also includes: a monitoring component for monitoring data changes of Zookeeper nodes, and when the node data is updated, reloading the updated custom formula and simplifying the processing.
[0088] Among them, the custom formula data is stored in the Zookeeper node and assigned a unique ID, and a backup is saved in MySQL. The formula loading component loads the custom formula from the storage area, and then automatically builds a directed graph based on the forward dependency of the custom formula. At this time, the custom formula is built into the vertex in the graph. The dependency detection processing component detects whether there is a loop in the graph. If there is a loop in the directed graph, an exception is processed. When there is no loop in the directed graph, the reverse dependency processing component performs custom logic processing according to the vertex dependency in the graph until all vertices with an in-degree of 0 are processed. Finally, the formula after reverse dependency processing can be retrieved according to the ID identifier. When the custom processing logic is to replace a simple indicator, the reverse dependency processing component provides a simplified formula function. When the reverse dependency processing component inputs other specific custom business logic, it can also be added in the formula simplification process.
[0089] It is worth noting that the above is only a preferred embodiment of the present invention, and does not limit the scope of patent protection of the present invention. The present invention can also improve the materials and structures of the above-mentioned various parts, or replace them with technical equivalents. Therefore, all equivalent structural changes made by using the description and illustrations of the present invention, or directly or indirectly applied to other related technical fields are also included in the scope of the present invention.
Claims
1. A method for processing a custom formula based on a directed graph, characterized in that: The method includes: Load the custom formula from the storage area and find the elements that the formula depends on, and construct a directed graph forward based on the elements; Performing dependency detection on elements in the directed graph using a loop detection algorithm; Perform reverse dependency processing to simplify the formula; Query the simplified custom formula based on the formula ID and substitute it into the business system to complete the calculation; The reverse dependency processing includes: constructing a vertex out-degree array, wherein the array subscript corresponds to the vertex index, and the array stores the out-degree of the corresponding vertex; Construct an auxiliary queue and put the vertices with out-degree 0 into the queue; Loop through the unvisited vertices, take out the vertices with out-degree 0 from the queue, process the data of the taken out vertices with out-degree 0 through a custom function, find the parent vertex according to the reverse edge, reduce the out-degree of the parent vertex by one, and mark the parent vertex as visited; Traverse the out-degree array, and put the vertices with out-degree 0 and unvisited into the queue; Exit the loop when all vertices have been visited.
2. The method according to claim 1, characterized in that The custom formula is stored in the Zookeeper node and assigned a unique ID, and is backed up in MySQL.
3. The method according to claim 2, characterized in that The method further includes: automatically monitoring data changes of each Zookeeper node, and when a custom formula update occurs in the Zookeeper node, reloading the updated custom formula from the storage area and performing subsequent processing.
4. The method according to any one of claims 1 to 3, characterized in that: In the process of executing reverse dependency processing, the custom processing logic is abstracted into a functional interface, and the business system performs custom logic processing of the formula according to actual needs.
5. The method according to claim 4, characterized in that The using a ring detection algorithm to perform dependency detection on elements in the directed graph includes: Construct a vertex in-degree array, where the array subscript corresponds to the vertex index and the array stores the in-degree of the corresponding vertex; Construct an auxiliary queue and put the vertices with in-degree 0 into the queue; Take out the vertex with in-degree 0 from the auxiliary queue, reduce the in-degree of the vertex it points to by 1 and update the in-degree array. If the in-degree of the vertex it points to is 0, put it into the queue. Otherwise, continue with step 2 and take the vertex from the queue. After processing all vertices with in-degree 0, when the queue is empty, determine whether there are vertices with in-degree not 0. If so, it means there is a cycle in the directed graph, and make an exception. If not, it means there is no cycle in the directed graph, and perform subsequent reverse dependency processing.
6. The method according to claim 4, characterized in that When the custom processing logic is a simple formula replacement, starting from the leaf vertex represented by a simple indicator, the custom formula is replaced in the directed graph in sequence upwards, and finally the custom formulas in all vertices in the entire directed graph are represented by simple indicators and calculated directly; When custom logic needs to add business logic according to business scenarios, the calculated logic data is added to the formula corresponding to the leaf vertex during the simplification process, wherein the business logic includes: unit conversion and ratio conversion.
7. A system for processing custom formulas based on directed graphs, characterized in that: The system includes: A formula loading component loads a custom formula from a storage area and searches for elements on which the formula depends, and constructs a directed graph forward based on the elements; A dependency detection processing component performs dependency detection on elements in the directed graph using a loop detection algorithm; A reverse dependency processing component performs reverse dependency processing and simplifies the formula; The calculation component queries the simplified custom formula based on the formula ID and substitutes it into the business system for calculation; The reverse dependency processing component is used to: construct a vertex out-degree array, wherein the array subscript corresponds to the vertex index, and the array stores the out-degree of the corresponding vertex; Construct an auxiliary queue and put the vertices with out-degree 0 into the queue; Loop through the unvisited vertices, take out the vertices with out-degree 0 from the queue, process the data of the taken out vertices with out-degree 0 through a custom function, find the parent vertex according to the reverse edge, reduce the out-degree of the parent vertex by one, and mark the parent vertex as visited; Traverse the out-degree array, and put the vertices with out-degree 0 and unvisited into the queue; Exit the loop when all vertices have been visited.
8. The system according to claim 7, characterized in that Also includes: The monitoring component is used to monitor whether the custom formula stored in each Zookeeper node has changed. When there is a custom formula update, the updated custom formula is reloaded from the storage area and subsequent processing is performed.