CN113779161B - Method and device for generating calculation script, computer equipment and storage medium - Google Patents
Method and device for generating calculation script, computer equipment and storage medium Download PDFInfo
- Publication number
- CN113779161B CN113779161B CN202110993749.0A CN202110993749A CN113779161B CN 113779161 B CN113779161 B CN 113779161B CN 202110993749 A CN202110993749 A CN 202110993749A CN 113779161 B CN113779161 B CN 113779161B
- Authority
- CN
- China
- Prior art keywords
- cell
- range
- calculation
- generating
- script
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/28—Databases characterised by their database models, e.g. relational or object models
- G06F16/283—Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2453—Query optimisation
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2455—Query execution
- G06F16/24553—Query execution of query operations
- G06F16/24554—Unary operations; Data partitioning operations
- G06F16/24556—Aggregation; Duplicate elimination
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F8/00—Arrangements for software engineering
- G06F8/30—Creation or generation of source code
- G06F8/31—Programming languages or programming paradigms
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Databases & Information Systems (AREA)
- General Engineering & Computer Science (AREA)
- Physics & Mathematics (AREA)
- General Physics & Mathematics (AREA)
- Data Mining & Analysis (AREA)
- Software Systems (AREA)
- Computational Linguistics (AREA)
- Computing Systems (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention provides a method and a device for generating a calculation script, computer equipment and a storage medium, wherein the method comprises the following steps: acquiring a query range of a user; determining a calculation formula from the query range, wherein the calculation formula comprises a left cell on the left side of the equal sign and a right cell on the right side of the equal sign; determining the range of the right cell according to the range of the left cell; and generating a calculation script according to the range of the right cell, wherein the calculation script is used for determining the effective cells of the left cell and generating a query result aiming at the query range according to the effective cells. The method and the device solve the technical problems that when the multi-dimensional database is subjected to query calculation, the number of invalid calculations is large due to the sparse characteristic of the multi-dimensional database, and the query calculation efficiency is low.
Description
Technical Field
The present invention relates to the field of database technologies, and in particular, to a method and an apparatus for generating a computation script, a computer device, and a storage medium.
Background
In the multidimensional database calculation engine, the range of cells covered by a calculation formula is large, but relatively few cells capable of actually calculating effective values are caused by the common sparsity in a multidimensional database, and the current main processing mode is to calculate all the cells covered by the formula in the query range once, so as to obtain a query result.
It should be noted that due to the sparse characteristic of the multidimensional database, the table of the data may contain null data, and when a large amount of data is subjected to query, a large amount of invalid calculations may be generated, which results in a long return time of the query result.
Therefore, when query computation is performed on the multidimensional database, the number of invalid computations is large due to the sparse characteristic of the multidimensional database, and the query computation efficiency is low.
Disclosure of Invention
The invention provides a method and a device for generating a calculation script, computer equipment and a storage medium, which are used for solving the technical problems of more invalid calculation times and low query calculation efficiency caused by the sparse characteristic of a multi-dimensional database when query calculation is carried out on the multi-dimensional database.
According to a first aspect of the present invention, there is provided a method for generating a computation script, the method comprising: acquiring a query range of a user; determining a calculation formula from the query range, wherein the calculation formula comprises a left cell on the left side of the equal sign and a right cell on the right side of the equal sign; determining the range of the right cell according to the range of the left cell; and generating a calculation script according to the range of the right cell, wherein the calculation script is used for determining the effective cell of the left cell and generating a query result aiming at the query range according to the effective cell.
Further, generating a calculation script according to the range of the right cell comprises: determining that the right cell is covered by a first formula according to the range of the right cell; recursively generating the computation script according to the covered first formula.
Further, generating a calculation script according to the range of the right cell comprises: and under the condition that the right cell is determined to be an input value according to the range of the right cell, generating the calculation script according to the range of the right cell, wherein under the condition that the calculation script is executed, each cell existing in the range of the right cell is obtained through traversal according to the range of the cell, and effective cells in the range of the left cell are obtained through calculation according to the existing cells.
Further, generating a calculation script according to the range of the right cell comprises: determining the leaf node range of the aggregation cell under the condition that the right cell is determined to be the aggregation cell according to the range of the right cell; and recursively generating the calculation script of the leaf node range.
Further, the right cell includes a first partial cell and a second partial cell, and generating the calculation script according to the range of the right cell includes: determining a first part of cells as input values and a second part of cells covered by the formula according to the range of the right cell; generating a first sub-calculation script according to the range of the first part of cells; recursively generating a second sub-calculation script according to a formula covered by the second part of cells; and combining and generating the calculation script according to the first sub calculation script and the second sub calculation script.
Further, determining the range of the right cell from the range of the left cell comprises: determining dimension members of the right cell according to at least one dimension member of the left cell.
According to a second aspect of the present invention, there is provided an apparatus for generating a computation script, the apparatus comprising: the device comprises an acquisition unit, a query unit and a query unit, wherein the acquisition unit is used for acquiring a query range of a user; the first determination unit is used for determining a calculation formula from the query range, wherein the calculation formula comprises a left cell on the left side of the equal sign and a right cell on the right side of the equal sign; the second determining unit is used for determining the range of the right cell according to the range of the left cell; and the generating unit is used for generating a calculation script according to the range of the right cell, wherein the calculation script is used for determining the effective cell of the right cell and generating a query result aiming at the query range according to the effective cell.
Further, the generation unit includes: a determining module for determining that the right cell is covered by a first formula according to the range of the right cell; and the generating module is used for generating the calculation script according to the covered first formula in a recursion mode.
According to a third aspect of the present invention, there is provided a computer device comprising a memory and a processor, the memory having stored thereon computer instructions which, when executed by the processor, cause the above-described method of generating a computing script to be performed.
According to a fourth aspect of the present invention, there is provided a non-transitory computer-readable storage medium having stored thereon a computer program which, when executed by a processor, causes the above-described method of generating a computing script to be performed.
The invention provides a method and a device for generating a calculation script, computer equipment and a storage medium, wherein the method comprises the following steps: acquiring a query range of a user; determining a calculation formula from the query range, wherein the calculation formula comprises a left cell on the left side of the equal sign and a right cell on the right side of the equal sign; determining the range of the right cell according to the range of the left cell; and generating a calculation script according to the range of the right cell, wherein the calculation script is used for determining the effective cell of the left cell and generating a query result aiming at the query range according to the effective cell. The method solves the technical problems that when the multi-dimensional database is subjected to query calculation, the number of invalid calculations is large due to the sparse characteristic of the multi-dimensional database, and the query calculation efficiency is low.
Drawings
In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings used in the description of the embodiments or the prior art will be briefly described below, and it is obvious that the drawings in the following description are some embodiments of the present invention, and other drawings can be obtained by those skilled in the art without creative efforts.
FIG. 1 is a flowchart of a method for generating a computing script according to a first embodiment of the present invention;
FIG. 2 is a diagram illustrating a query scope according to a first embodiment of the present invention;
fig. 3 to 4 are schematic diagrams illustrating a relationship between a left dimension and a right dimension according to a first embodiment of the invention;
FIG. 5 is a flowchart of an alternative method for generating a computing script according to a first embodiment of the invention; and
fig. 6 is a schematic diagram of a calculation script generation apparatus according to a second embodiment of the present invention.
Detailed Description
In order to make the above and other features and advantages of the present invention more apparent, the present invention is further described below with reference to the accompanying drawings. It is understood that the specific embodiments described herein are for purposes of illustration only and are not intended to be limiting.
In the following description, numerous specific details are set forth in order to provide a thorough understanding of the present invention. It will be apparent, however, to one skilled in the art that the specific details need not be employed to practice the present invention. In other instances, well-known steps or operations are not described in detail to avoid obscuring the invention.
Example one
The invention provides a method for generating a calculation script, which comprises the following steps of:
in step S11, the query range of the user is obtained.
Specifically, in this scheme, a hardware device such as a server may be used as an execution subject to obtain a query range of a user, where the query range may be a range of cells that the user wants to view, and is formed by multiple cells, for example, the range of cells shown in fig. 2, C3: E8. that is, a user wants to query a value in a range of a cell selected by adding a thick line in fig. 2, it should be noted that, in the above-mentioned plurality of cells in the user query range, some cells are entry values and can be directly valued, and some cells are covered by a formula and can be valued only by formula calculation.
Step S13, determining a calculation formula from the query range, wherein the calculation formula includes a left cell on the left side of the equal sign and a right cell on the right side of the equal sign.
Specifically, in the present embodiment, the cells covered by the formula may be determined from the query range, and then the calculation formula of the cells may be determined, for example, the calculation formula of the cells covered by the formula is sales amount — unit price, in the calculation formula, the cells may be further divided into the left and right equal-numbered cells, for example, the left equal-numbered cell is the cell related to sales amount, i.e., the left cell, the right equal-numbered cell is the cell related to sales amount and unit price, i.e., the right cell, and the number of the left and right cells may be multiple.
In step S15, the range of the right cell is determined from the range of the left cell.
Specifically, in this scheme, the range of the right cell may be determined according to the range of the left cell of the calculation formula, it should be noted that the range of the right cell may be a member list that may appear in each dimension of the right cell, and each dimension member of the right cell has three conditions: directly appointing dimension members, directly using the name of a dimension member of the left cell, and calculating through one or more dimension member names of the left cell.
And step S17, generating a calculation script according to the range of the right cell, wherein the calculation script is used for determining the effective cell of the left cell and generating a query result aiming at the query range according to the effective cell.
Specifically, in this embodiment, the calculation script may be generated according to the range of the right cell, and it should be noted that, when being executed, the calculation script may be configured to determine the valid cell in the left cell, and perform calculation on the valid cell through a calculation formula to generate the value of the left cell, and further generate the query result of the query range. It should be further noted that, after the computation script is executed, an iterator function of the query range is generated, and after the iterator function is called, a valid computation value in the query range, that is, a value of a valid cell in the right cell, may be returned until values of all valid cells are returned, and then a computation is performed to generate a query result. It should be further noted that, in the present solution, the calculation script generated for each formula is internally a function, the parameter of the function is the range of the cell on the left side of the queried formula, and the return value is an iterator, and the iterator can be used to obtain the cell information and the calculated value, which have valid calculated values, one by one.
The method provided by the scheme generates the calculation script by analyzing the range of the cells, then executes the script, and the script can automatically determine the value of the effective cells in the query range and then returns to the effective cells for calculation. The scheme provided by the application aims to reduce the invalid calculation times during query calculation, thereby improving the efficiency of the query calculation and reducing the time required by query return. Therefore, the technical problems that when the multi-dimensional database is subjected to query calculation, due to the sparse characteristic of the multi-dimensional database, the number of invalid calculations is large, and the query calculation efficiency is low are solved.
Optionally, the step S17 of generating the calculation script according to the range of the right cell may include:
in step S171, it is determined that the right cell is covered by the first formula according to the range of the right cell.
Specifically, in this solution, it is determined whether the right cell is covered by a first formula according to the range of the right cell, where the first formula may be a certain rule, and it should be noted that in this solution, the cell may be covered by a formula, for example, the calculation formula is: business profit is business income-business cost, and the cell on the left of equal sign is business profit, and the cell on the right of equal sign has two, business income and business cost, then business income is still covered by first formula, for example, business income is main business income + other business income.
Step S172, recursively generating a calculation script according to the covered first formula.
Specifically, in the present solution, a calculation script of the query range may be recursively generated according to the covered first formula. It should be noted that, the calculation script generation in the present embodiment may be a calculation script for generating each cell within the query range. That is, in the present scheme, when the right cell is covered by the formula, the present scheme recursively generates a corresponding calculation script based on the range of the right cell, and the calculation script calculates the effective values of all the left cells when executed. It should be noted here that, after the calculation script is generated, when there is a case where the right cell is covered by the formula, it is only necessary to calculate the range covered by the formula, then recursively call the calculation script function of the formula to generate an iterator for obtaining effective calculated values of the part of cells, and then use each iterated cell to calculate the current formula.
Optionally, in step S172, the calculation script generated recursively according to the formula is similar to the following pseudo code:
inputting parameters: inquiring the range;
the returned iterator is generated by the following method:
the iterator iter _ right for the right cell is obtained.
Go back to iterator { get the next cell with iter _ right.
The left cell is calculated using the cell just acquired and returned.
}
Optionally, the step S17 of generating the calculation script according to the range of the right cell may include:
and step S173, generating a calculation script according to the range of the right cell when the right cell is determined as the input value according to the range of the right cell, wherein, when the calculation script is executed, each existing cell in the range of the right cell is obtained according to the range traversal of the cells, and an effective cell in the range of the left cell is obtained according to the calculation of each existing cell.
Specifically, in the case that the right cell is the entry value, the present scheme directly generates the calculation script according to the range of the right cell, and the generated calculation script traverses each cell according to the range of the right cell, and then calculates the left cell according to each cell obtained by traversal, it should be noted that the calculation script generated in the above step S173 may include the following steps: in the first step, an iterator is generated for returning the existing entry values one by one according to the right cell range. And step two, calculating a cell value on the left side according to the cell value obtained by iteration and a calculation formula on the right side of the formula. And thirdly, writing the calculation result into the query result.
It should be noted here that, in the case of recursive query, the above computation script is packaged into an iterator for the computation script of the previous layer. If the right side is not more than one type of cell and is in an AND relation with other right side cells, the other right side cells need to be searched for whether to exist according to the traversed cells, and if the other right side cells exist, the left side cells are calculated; if not, the left cell has no valid calculated value and the value of the left cell need not be calculated. If the outer layer of the right cell has the 'condition' cell, firstly calculating whether the 'condition' is met in the second step, and when the condition is true, continuing to calculate; otherwise, the current cell is skipped and the iteration continues to the next one.
Optionally, the calculation script generated in step S173 is similar to the following pseudo code:
inputting parameters: query scope
And returning a value: iterators that traverse relevant cells in the data according to the query scope.
Optionally, the step S17 of generating a calculation script according to the range of the right cell may include:
and step S174, determining the leaf node range of the aggregation cell under the condition that the right cell is determined to be the aggregation cell according to the range of the right cell.
In step S175, a calculation script of the leaf node range is recursively generated.
Optionally, the calculation script generated in the above steps S174 to S175 is similar to the following pseudo code:
inputting parameters: query scope
The returned iterator is generated by a method, the last page
An iterator iter _ leaf is obtained that aggregates the range of the leaf cells below the cell.
Back iterator
The leaf cells are obtained using iter _ leaf until enough aggregated cells have been computed.
The information and value of this aggregated cell is returned.
}
}
Optionally, the right cell includes a first partial cell and a second partial cell, and the step of generating a calculation script according to the range of the right cell in step S17 may include:
in step S176, a first portion of the cells is determined as entered values and a second portion of the cells is covered by the formula based on the range of the right cell.
And step S177, generating a first sub-calculation script according to the range of the first part of cells.
And step S178, recursively generating a second sub-calculation script according to the formula covered by the second part of cells.
And step S179, combining and generating the calculation script according to the first sub calculation script and the second sub calculation script.
Specifically, in the present scheme, if the right cell is the entry value and the formula is covered to the maximum, the right cell and the formula are processed respectively, and then the calculation scripts generated respectively are merged together.
Optionally, the calculation script generated in step S176 to step S179 is similar to the following pseudo code:
inputting parameters: query scope
The returned iterator is generated by the following method:
and acquiring an iterator iter _ input for recording the cells.
An iterator iter _ rule for the formula overlay cell is obtained.
Back iterator
When iter _ input has a value
Get the next cell with iter _ input.
The left cell is calculated using the cell just acquired and returned.
}
When iter _ rule also has a value-
The next cell is obtained with iter _ rule.
The left cell is calculated using the cell just acquired and returned.
}
}
Alternatively, the step of determining the range of the right cell according to the range of the left cell in step S15 may include:
and step S151, determining the dimension member of the right cell according to at least one dimension member of the left cell.
Specifically, the dimension members of the right cell can be determined according to the dimension members of the left cell, optionally, the dimension members of the right cell can be directly specified, and only one member, namely the dimension member identical to the specified character string, is in the query range. In connection with FIG. 3, "right dimension 4" is the dimension member name specified directly in the formula.
In an alternative embodiment, the present solution may directly use the left dimension member name to determine the dimension member of the right cell, and such member directly uses the left dimension member to indicate that the range of the corresponding dimension is the same as the member range specified by the left related dimension, in which case, no other calculation is performed, and the left dimension member name is directly used to find the corresponding right dimension member.
In another alternative embodiment, the method calculates the dimension member of the right cell by the name of the dimension member on the left side, and can be divided into the following three cases. First, each calculated right dimension member is in the process of calculation: only one left dimension is used. In connection with fig. 3, "right dimension 1" and "right dimension 2" are the same as the dimension member names of "left dimension 1" and "left dimension 2", respectively. Second, each computed right dimension member is, during computation: multiple left dimension member names are used, but the left dimension member sets used by different right dimension members do not intersect and none of the used left dimension members are used directly for right dimension members. In connection with fig. 3, "right dimension 3" is calculated from "left dimension 3" and "left dimension 4". Thirdly, there are more than one calculated right dimension members: the left dimension member sets used intersect, and in connection with fig. 4, "right dimension 3" and "right dimension 5" are both calculated from "left dimension 3" and "left dimension 4".
An alternative embodiment of the invention is described below with reference to fig. 5:
and step S51, analyzing the relation of the cells on both sides of the equal sign.
Specifically, the relationship between cells is divided into two types: the relationship between the left side cell and the right side cell, the relationship between a plurality of right side cells, and the relationship between a plurality of right side cells. The relationship between the cells on the right side is mainly: how the right cells determine whether there are valid calculated values for the left cell. There are three main relationships: one is conditional, two is and, three is or. Conditional relationships mean that the right cell appears in the if condition or that the cell is used to compute other right cell dimension members. The conditional cell is generally used to limit the range of the cell, so the condition is generally determined after traversing other cells on the right side to determine whether there is a valid calculated value in the cell on the left side. An and relationship is a relationship between two or more cells on the right, first they are not at the location of a "conditional" relationship cell, and second all cells of an and relationship must all have valid values to be able to determine that a valid calculated value exists for the left cell. An "or" relationship is a relationship between two or more right cells, first they are not at the location of the "conditional" relationship cell, and second only if there is a valid value in all cells of the "or" relationship, it can be determined that there is a valid computed value in the left cell.
Step S52, calculating the range of the right cell according to the left cell range of the query.
In step S53, it is determined whether the right cell is covered by the rule, and if yes, step S54 is performed, and if no, step S55 is performed.
And step S54, generating a corresponding calculation script according to the covered formula recursion.
In step S55, it is determined whether the right cell is the aggregation value, and if yes, step S56 is performed, and if no, step S58 is performed.
In step S56, it is determined whether the leaf node is covered by the formula, and in case of yes, step S57 is performed.
And step S57, converting the aggregation cell range into a leaf cell range, and recursively generating a calculation script.
In step S58, a script is generated and the right cell is traversed, even if the left cell has a value.
It should be noted that if the right cell is partially covered by the formula, partially is the entry cell, and partially is the aggregation value, the calculation scripts are generated separately and then merged into a large calculation script.
An alternative embodiment of the present solution is introduced below with reference to table 1, for example, in table 1, the multidimensional data analysis scenario in table 1 has the following dimensions: commodity dimensions (two members); the date dimension (three members); sales dimension (two members: sales volume, sales amount).
Table 1:
| commodity | Date | Amount of sales | Sales amount |
| |
1 month and 1 day | 10 | 20 |
| |
1 month and 2 | ||
| Notebook | |||
| 1 month and 3 |
6 | 12 | |
| |
1 month and 1 | ||
| Pencil | |||
| 1 month and 2 |
7 | 14 | |
| |
1 month and 3 days |
In this multidimensional data analysis scenario, the following formula: (ii) [ 'sales' ] [ 'sales amount' ]; when the sales is inquired according to the traditional inquiry method, although only three sales records exist, the fact that three times of calculation are invalid is known after 6 times of calculation. By adopting the method provided by the application, the query result can be obtained by only carrying out effective calculation three times. The effective calculation can be carried out for three times because the corresponding relation between the sales and the sales is obtained by analyzing the calculation formula of the sales, and the sales with effective data can be used for calculating the corresponding sales, and the effective value can be calculated only if the sales and the sales are available.
In conclusion, the technical problems that when the multi-dimensional database is subjected to query calculation, due to the sparse characteristic of the multi-dimensional database, the number of invalid calculations is large, and the query calculation efficiency is low are solved.
Example two
As shown in fig. 6, the present disclosure provides an apparatus for generating a computation script, where the apparatus may be run in a hardware device such as a server, and may also be used to execute a method according to an embodiment, and the apparatus may include: an obtaining unit 60, configured to obtain a query range of a user; a first determining unit 62, configured to determine a calculation formula from the query range, where the calculation formula includes a left cell on the left side of the equal sign and a right cell on the right side of the equal sign; a second determining unit 64 for determining the range of the right cell from the range of the left cell; and a generating unit 66, configured to generate a calculation script according to the range of the right cell, wherein the calculation script is used to determine the valid cells of the left cell and generate a query result for the query range according to the valid cells.
According to the scheme of the embodiment, the calculation script is generated by analyzing the range of the cells, then the script is executed, the script can automatically determine the value of the effective cells in the query range and then returns to the effective cells for calculation, compared with the method that each cell must be traversed in the prior art, the method has the advantages that the value of the effective cells in the query range can be automatically determined only by calling the generated script, the phenomenon that a large number of invalid cell calculations are required to be performed by traversing each cell in the prior art is avoided, and therefore the calculation efficiency in the process of querying the cells in a large range can be greatly improved. The scheme provided by the application aims to reduce the invalid calculation times during query calculation, thereby improving the efficiency of the query calculation and reducing the time required by query return. Therefore, the technical problems that when the multi-dimensional database is subjected to query calculation, due to the sparse characteristic of the multi-dimensional database, the number of invalid calculations is large, and the query calculation efficiency is low are solved.
Optionally, the generating unit includes: the determining module is used for determining that the right cell is covered by the first formula according to the range of the right cell; and the generating module is used for recursively generating the calculation script according to the covered first formula.
The present application further provides a computer device comprising a memory and a processor, the memory having stored thereon computer instructions, which when executed by the processor, cause the generation method of the calculation script of one of the embodiments to be performed.
The present application also provides a non-transitory computer-readable storage medium having stored thereon a computer program which, when executed by a processor, causes the generation method of a computation script in one of the embodiments to be performed.
It will be understood that the specific features, operations and details described herein above with respect to the method of the present invention may be similarly applied to the apparatus and system of the present invention, or vice versa. In addition, each step of the method of the present invention described above may be performed by a respective component or unit of the device or system of the present invention.
It should be understood that the various modules/units of the apparatus of the present invention may be implemented in whole or in part by software, hardware, firmware, or a combination thereof. Each module/unit may be embedded in a processor of the computer device in a hardware or firmware form or independent from the processor, or may be stored in a memory of the computer device in a software form to be called by the processor to perform the operation of each module/unit. Each module/unit may be implemented as a separate component or module, or two or more modules/units may be implemented as a single component or module.
In one embodiment, a computer device is provided that includes a memory and a processor, the memory having stored thereon computer instructions executable by the processor, the computer instructions, when executed by the processor, instructing the processor to perform the steps of the method in embodiment one of the present invention. The computer device may broadly be a server, a terminal, or any other electronic device having the necessary computing and/or processing capabilities. In one embodiment, the computer device may include a processor, memory, a network interface, a communication interface, etc., connected by a system bus. The processor of the computer device may be used to provide the necessary computing, processing and/or control capabilities. The memory of the computer device may include non-volatile storage media and internal memory. An operating system, a computer program, and the like may be stored in or on the non-volatile storage medium. The internal memory may provide an environment for the operating system and the computer programs in the non-volatile storage medium to run. The network interface and the communication interface of the computer device may be used to connect and communicate with an external device through a network. Which when executed by a processor performs the steps of the method of the invention.
The invention may be implemented as a computer-readable storage medium having stored thereon a computer program which, when executed by a processor, causes the steps of a method of an embodiment one of the invention to be performed. In one embodiment, the computer program is distributed across multiple computer devices or processors coupled by a network such that the computer program is stored, accessed, and executed by one or more computer devices or processors in a distributed fashion. A single method step/operation, or two or more method steps/operations, may be performed by a single computer device or processor, or by two or more computer devices or processors. One or more method steps/operations may be performed by one or more computer devices or processors, and one or more other method steps/operations may be performed by one or more other computer devices or processors. One or more computer devices or processors may perform a single method step/operation, or perform two or more method steps/operations.
It will be appreciated by those of ordinary skill in the art that the method steps of the present invention may be directed to associated hardware, such as a computer device or processor, for performing by a computer program that may be stored in a non-transitory computer readable storage medium and that when executed causes the steps of the present invention to be performed. Any reference herein to memory, storage, databases, or other media may include non-volatile and/or volatile memory, as appropriate. Examples of non-volatile memory include read-only memory (ROM), programmable ROM (prom), electrically programmable ROM (eprom), electrically erasable programmable ROM (eeprom), flash memory, magnetic tape, floppy disk, magneto-optical data storage device, hard disk, solid state disk, and the like. Examples of volatile memory include Random Access Memory (RAM), external cache memory, and the like.
The respective technical features described above may be arbitrarily combined. Although not all possible combinations of features are described, any combination of features should be considered to be covered by the present specification as long as such combination is not contradictory.
Finally, it should be noted that: the above embodiments are only used to illustrate the technical solution of the present invention, and not to limit the same; while the invention has been described in detail and with reference to the foregoing embodiments, it will be understood by those skilled in the art that: the technical solutions described in the foregoing embodiments may still be modified, or some or all of the technical features may be equivalently replaced; and the modifications or the substitutions do not make the essence of the corresponding technical solutions depart from the scope of the technical solutions of the embodiments of the present invention.
Claims (4)
1. A method of generating a computing script, the method comprising:
acquiring a query range of a user;
determining a calculation formula from the query range, wherein the calculation formula comprises a left cell on the left side of the equal sign and a right cell on the right side of the equal sign;
determining the range of the right cell according to the range of the left cell;
generating a calculation script according to the range of the right cell, wherein the calculation script is used for determining a valid cell of the left cell and generating a query result aiming at the query range according to the valid cell;
generating a calculation script according to the range of the right cell comprises: determining that the right cell is covered by a first formula according to the range of the right cell; recursively generating the calculation script according to the covered first formula;
generating a calculation script according to the range of the right cell comprises: under the condition that the right cell is determined to be an input value according to the range of the right cell, generating the calculation script according to the range of the right cell, wherein under the condition that the calculation script is executed, each cell existing in the range of the right cell is obtained through traversal according to the range of the cell, and effective cells in the range of the left cell are obtained through calculation according to the existing cells;
generating a calculation script according to the range of the right cell comprises: determining the leaf node range of the aggregation cell under the condition that the right cell is determined to be the aggregation cell according to the range of the right cell; recursively generating a calculation script of the leaf node range;
the right cell includes a first partial cell and a second partial cell, and generating a calculation script according to a range of the right cell includes: determining the first partial cell as an entry value and the second partial cell is covered by a formula according to the range of the right cell; generating a first sub-calculation script according to the range of the first part of cells; recursively generating a second sub-calculation script according to the formula covered by the second part of cells; combining and generating the calculation script according to the first sub-calculation script and the second sub-calculation script;
determining the range of the right cell according to the range of the left cell comprises: determining dimension members of the right cell according to at least one dimension member of the left cell;
wherein the content of the first and second substances,
the calculation script generated recursively according to the formula is the following pseudo-code:
in the case where the right cell is an entry value, the generated calculation script is the following pseudo code:
inputting parameters: query scope
And returning a value: iterating through relevant cells in the data according to the query range;
and under the condition that the right cell is determined to be the aggregation cell according to the range of the right cell, the generated calculation script is the following pseudo code:
under the condition that the right cell comprises a first part of cells and a second part of cells, the generated calculation script is a pseudo code as follows:
2. an apparatus for generating a computing script, the apparatus comprising:
the device comprises an acquisition unit, a query unit and a query unit, wherein the acquisition unit is used for acquiring a query range of a user;
the first determination unit is used for determining a calculation formula from the query range, wherein the calculation formula comprises a left cell on the left side of the equal sign and a right cell on the right side of the equal sign;
the second determining unit is used for determining the range of the right cell according to the range of the left cell;
a generating unit, configured to generate a calculation script according to the range of the right cell, where the calculation script is configured to determine a valid cell of the left cell and generate a query result for the query range according to the valid cell;
a generation unit: further for determining that the right cell is covered by a first formula according to the range of the right cell; recursively generating the calculation script according to the covered first formula;
a generation unit: the calculation script is further configured to generate the calculation script according to the range of the right cell when the right cell is determined to be an input value according to the range of the right cell, where each cell existing in the range of the right cell is obtained according to traversal of the range of the cell when the calculation script is executed, and an effective cell in the range of the left cell is obtained through calculation according to the existing cell;
a generation unit: the right cell is determined to be an aggregation cell according to the range of the right cell, and a leaf node range of the aggregation cell is determined; recursively generating a calculation script of the leaf node range;
a generation unit: the right cell is used for determining a first part of cells as input values and a second part of cells are covered by a formula according to the range of the right cell; generating a first sub-calculation script according to the range of the first part of cells; recursively generating a second sub-calculation script according to the formula covered by the second part of cells; combining and generating the calculation script according to the first sub-calculation script and the second sub-calculation script;
a second determination unit: further for determining dimension members of the right cell from at least one dimension member of the left cell;
wherein, the first and the second end of the pipe are connected with each other,
the calculation script generated recursively according to the formula is the following pseudo-code:
in the case where the right cell is an entry value, the generated calculation script is the following pseudo code:
inputting parameters: query scope
And (4) returning a value: iterators for traversing relevant cells in the data according to the query range;
and under the condition that the right cell is determined to be the aggregation cell according to the range of the right cell, the generated calculation script is the following pseudo code:
when the right cell includes a first partial cell and a second partial cell, the generated calculation script is the following pseudo code:
3. a computer device comprising a memory and a processor, the memory having stored thereon computer instructions which, when executed by the processor, cause the method of generating a computing script of claim 1 to be performed.
4. A non-transitory computer-readable storage medium on which a computer program is stored, which, when executed by a processor, causes the generation method of a calculation script of claim 1 to be performed.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110993749.0A CN113779161B (en) | 2021-08-27 | 2021-08-27 | Method and device for generating calculation script, computer equipment and storage medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110993749.0A CN113779161B (en) | 2021-08-27 | 2021-08-27 | Method and device for generating calculation script, computer equipment and storage medium |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN113779161A CN113779161A (en) | 2021-12-10 |
| CN113779161B true CN113779161B (en) | 2022-08-23 |
Family
ID=78839459
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202110993749.0A Active CN113779161B (en) | 2021-08-27 | 2021-08-27 | Method and device for generating calculation script, computer equipment and storage medium |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN113779161B (en) |
Citations (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101010672A (en) * | 2004-10-07 | 2007-08-01 | 国际商业机器公司 | Methods, systems and computer program products for processing cells in a spreadsheet |
Family Cites Families (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US7177855B2 (en) * | 2001-06-20 | 2007-02-13 | Oracle International Corporation | Compile-time optimizations of queries with SQL spreadsheet |
| US7415454B2 (en) * | 2005-11-18 | 2008-08-19 | Microsoft Corporation | Allocation performance by query optimization |
| CN111091469B (en) * | 2019-11-20 | 2023-12-19 | 泰康保险集团股份有限公司 | Information processing method, information processing device, electronic equipment and computer readable medium |
| CN111898353A (en) * | 2020-08-17 | 2020-11-06 | 腾讯科技（深圳）有限公司 | Table display method, device and medium |
-
2021
- 2021-08-27 CN CN202110993749.0A patent/CN113779161B/en active Active
Patent Citations (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101010672A (en) * | 2004-10-07 | 2007-08-01 | 国际商业机器公司 | Methods, systems and computer program products for processing cells in a spreadsheet |
Also Published As
| Publication number | Publication date |
|---|---|
| CN113779161A (en) | 2021-12-10 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US9372899B2 (en) | Method and apparatus of ordering search results | |
| CN110502516B (en) | Table data analysis method and device, computer equipment and storage medium | |
| CN106462583A (en) | Systems and methods for rapid data analysis | |
| US10452639B2 (en) | Processing joins in a database system using zero data records | |
| CN112434216A (en) | Intelligent investment project recommendation method and device, storage medium and computer equipment | |
| CN110502697B (en) | Target user identification method and device and electronic equipment | |
| Pan et al. | A note on distributed quantile regression by pilot sampling and one-step updating | |
| CN113779161B (en) | Method and device for generating calculation script, computer equipment and storage medium | |
| US8548980B2 (en) | Accelerating queries based on exact knowledge of specific rows satisfying local conditions | |
| CN116579580A (en) | Method, device, computer equipment and storage medium for processing bill | |
| Chen et al. | Inference for a mean-reverting stochastic process with multiple change points | |
| CN112613762B (en) | Group rating method and device based on knowledge graph and electronic equipment | |
| CN115879819A (en) | Enterprise credit evaluation method and device | |
| CN113779160A (en) | Method and device for acquiring data of multidimensional database | |
| Yang et al. | The survival probability of the SABR model: asymptotics and application | |
| CN114860759A (en) | Data processing method, device and equipment and readable storage medium | |
| CN111651457B (en) | Data grouping method, device, computer equipment and storage medium | |
| CN114238044A (en) | Method and device for calculating activity of open source project and computer equipment | |
| CN112232862A (en) | Method, device, equipment and storage medium for predicting advertisement business channel ROI | |
| He et al. | Edgeworth corrections for spot volatility estimator | |
| WO2019165762A1 (en) | Sampling query method and device | |
| CN110580491A (en) | Node similarity calculation method, device, equipment and computer readable storage medium | |
| CN110704548B (en) | System and method for screening efficient computing data for multidimensional databases | |
| CN116562704A (en) | Index calculation method, system, equipment and storage medium | |
| CN116205673A (en) | Account screening method, account screening device, computer equipment and storage medium |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |