CN112434018A - Report generation method and device, computer equipment and storage medium - Google Patents
Report generation method and device, computer equipment and storage medium Download PDFInfo
- Publication number
- CN112434018A CN112434018A CN202011137858.4A CN202011137858A CN112434018A CN 112434018 A CN112434018 A CN 112434018A CN 202011137858 A CN202011137858 A CN 202011137858A CN 112434018 A CN112434018 A CN 112434018A
- Authority
- CN
- China
- Prior art keywords
- data
- query
- access
- access formula
- memory database
- 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.)
- Granted
Links
- 238000000034 method Methods 0.000 title claims abstract description 43
- 238000004364 calculation method Methods 0.000 claims abstract description 75
- 230000014509 gene expression Effects 0.000 claims description 35
- 238000004590 computer program Methods 0.000 claims description 16
- 238000001914 filtration Methods 0.000 claims description 15
- 238000000605 extraction Methods 0.000 claims description 3
- 230000008569 process Effects 0.000 abstract description 5
- 238000004891 communication Methods 0.000 description 6
- 238000010586 diagram Methods 0.000 description 5
- 210000001072 colon Anatomy 0.000 description 4
- 238000005516 engineering process Methods 0.000 description 4
- 230000008676 import Effects 0.000 description 3
- 230000003993 interaction Effects 0.000 description 3
- 238000000926 separation method Methods 0.000 description 2
- 238000010276 construction Methods 0.000 description 1
- 238000011161 development Methods 0.000 description 1
- 230000006870 function Effects 0.000 description 1
- 239000004973 liquid crystal related substance Substances 0.000 description 1
- 238000012986 modification Methods 0.000 description 1
- 230000004048 modification Effects 0.000 description 1
- 230000003287 optical effect Effects 0.000 description 1
- 238000012545 processing Methods 0.000 description 1
- 230000003068 static effect Effects 0.000 description 1
- 239000002699 waste material Substances 0.000 description 1
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/21—Design, administration or maintenance of databases
- G06F16/215—Improving data quality; Data cleansing, e.g. de-duplication, removing invalid entries or correcting typographical errors
-
- 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/22—Indexing; Data structures therefor; Storage structures
- G06F16/2282—Tablespace storage structures; Management thereof
-
- 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/242—Query formulation
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Databases & Information Systems (AREA)
- Physics & Mathematics (AREA)
- Data Mining & Analysis (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Quality & Reliability (AREA)
- Mathematical Physics (AREA)
- Computational Linguistics (AREA)
- Software Systems (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The application relates to a report generation method, a report generation device, computer equipment and a storage medium. The method comprises the following steps: extracting a figure taking formula from the initial report control; inquiring data from a background database according to the access formula; constructing a memory database according to a data structure of the inquired data, and importing the inquired data into the memory database; creating an index for the memory database according to the query parameters in the access formula; taking out data corresponding to the query parameters in the access formula from the memory database through the index, and calculating based on the taken out data; and assigning the calculation result corresponding to the access formula to the report control to generate a report. By adopting the method, the calculation performance in the report generation process can be improved.
Description
Technical Field
The present application relates to the field of computer technologies, and in particular, to a report generation method and apparatus, a computer device, and a storage medium.
Background
With the development of computer technology and the arrival of big data era, data acquisition from a database and data result display to a report form after memory calculation are involved in many scenes. For example, in the aspect of financial management, data is acquired from a database, and the data is filtered by a grid virtual table technology (DataTable) for temporarily storing the data in a memory, so that the data acquisition of the report data is completed.
However, after the data acquired from the database is put into the DataTable, each report formula calculates the result from the DataTable, however, the performance is poor when the DataTable is used for calculation, and the actual working requirements cannot be met usually.
Disclosure of Invention
In view of the above, it is necessary to provide a report generation method, apparatus, computer device and storage medium capable of improving the calculation performance.
A report generation method, the method comprising:
extracting a figure taking formula from the initial report control;
inquiring data from a background database according to the access formula;
constructing a memory database according to a data structure of the inquired data, and importing the inquired data into the memory database;
creating an index for the memory database according to the query parameters in the access formula;
taking out data corresponding to the query parameters in the access formula from the memory database through the index, and calculating based on the taken out data;
and assigning the calculation result corresponding to the access formula to the report control to generate a report.
In one embodiment, the querying data from the background database according to the access formula includes:
when the extracted access formula is multiple, grouping the multiple formulas;
merging the query parameters in the same group of access formulas to obtain query parameter sets corresponding to the access formulas of each group;
and respectively querying data from a background database according to each query parameter set.
In one embodiment, the querying data from a background database according to each query parameter set includes:
generating a query statement according to the query parameters in the same query parameter set;
and querying data from a background database according to the query statement.
In one embodiment, the merging the query parameters in the same group of access formulas to obtain query parameter sets corresponding to the groups of access formulas respectively includes:
aiming at a plurality of query parameters of the same type in the same group of access formulas, when the query parameters meet the proximity condition, generating a corresponding range access condition according to the minimum query parameter and the maximum query parameter in the query parameters.
In one embodiment, the fetching, by the index, data corresponding to the query parameter in the access formula from the in-memory database, and performing calculation based on the fetched data includes:
when a plurality of extracted access formulas are provided, respectively splicing the query parameters in each access formula to generate a filtering condition;
according to the index and the filtering condition corresponding to each access formula, taking out the data corresponding to each access formula from the memory database;
and calculating based on the extracted data to obtain a calculation result corresponding to each access formula.
In one embodiment, the performing the calculation based on the retrieved data includes:
calculating based on the taken data to generate a data dictionary; the key in the data dictionary is a character string consisting of the name of the access formula and the query parameter in the access formula, and the value in the data dictionary is the calculation result of the access formula;
assigning the calculation result corresponding to the access formula to the report control to generate a report, comprising:
and determining a corresponding access formula in the report control according to the keys in the data dictionary, and assigning the calculation result to the access formula determined in the report control according to the values in the data dictionary.
In one embodiment, the retrieving, by the index, data corresponding to the query parameter in the access formula from the in-memory database includes:
constructing a query expression according to the query parameters in the access formula;
when the execution plan matched with the query expression is cached in the memory database, then
And according to the cached matched execution plan and the index, taking out data corresponding to the query parameters in the access formula from the memory database.
In one embodiment, the retrieving, by the index, data corresponding to the query parameter in the access formula from the in-memory database further includes:
when the execution plan matched with the query expression is not cached in the memory database, the execution plan is executed
Compiling and analyzing the query expression through the memory database to generate an execution plan, and caching the execution plan;
and according to the index and the generated execution plan, taking out data corresponding to the query parameters in the access formula from the memory database.
A report generation apparatus, the apparatus comprising:
the extraction module is used for extracting the access formula from the initial report control;
the query module is used for querying data from a background database according to the access formula;
the building module is used for building a memory database according to the data structure of the inquired data and importing the inquired data into the memory database;
the creating module is used for creating an index for the memory database according to the query parameters in the access formula;
the calculation module is used for taking out the data corresponding to the query parameters in the access formula from the memory database through the index and calculating based on the taken out data;
and the generating module is used for assigning the calculation result corresponding to the access formula to the report control to generate a report.
A computer device comprising a memory and a processor, the memory storing a computer program, the processor implementing the following steps when executing the computer program:
extracting a figure taking formula from the initial report control;
inquiring data from a background database according to the access formula;
constructing a memory database according to a data structure of the inquired data, and importing the inquired data into the memory database;
creating an index for the memory database according to the query parameters in the access formula;
taking out data corresponding to the query parameters in the access formula from the memory database through the index, and calculating based on the taken out data;
and assigning the calculation result corresponding to the access formula to the report control to generate a report.
A computer-readable storage medium, on which a computer program is stored which, when executed by a processor, carries out the steps of:
extracting a figure taking formula from the initial report control;
inquiring data from a background database according to the access formula;
constructing a memory database according to a data structure of the inquired data, and importing the inquired data into the memory database;
creating an index for the memory database according to the query parameters in the access formula;
taking out data corresponding to the query parameters in the access formula from the memory database through the index, and calculating based on the taken out data; and assigning the calculation result corresponding to the access formula to the report control to generate a report.
According to the report generation method, the report generation device, the computer equipment and the storage medium, the access formula is extracted from the initial report control, data is inquired from the background database according to the access formula, the memory database is constructed according to the data structure of the inquired data, and the inquired data is imported into the memory database. By establishing the memory database in the cache and storing the data to be used by the access formula inquired by the background database in the memory database, the access formula can be directly used for accessing the local memory database, so that the interaction frequency with the background database is reduced, and the data access speed is improved. And creating an index for the memory database according to the query parameters in the access formula, taking out the data corresponding to the query parameters in the access formula from the memory database through the index, calculating based on the taken-out data, assigning the calculation result corresponding to the access formula to the report control, and generating a report. The index corresponding to the query parameter in the access formula is created in the memory database, so that the access formula can quickly take out corresponding data in the memory database according to the index, and the access time is saved. And finally, calculating the acquired data to obtain a calculation result, assigning the calculation result to the report control, and generating a report. Therefore, the report generation method can effectively improve the calculation performance of the terminal required by report generation.
Drawings
FIG. 1 is a diagram of an application environment of a report generation method in one embodiment;
FIG. 2 is a flowchart illustrating a report generation method according to an embodiment;
FIG. 3 is a flowchart illustrating the report generation step in one embodiment;
FIG. 4 is a block diagram showing the structure of a report generation apparatus according to an embodiment;
FIG. 5 is a diagram illustrating an internal structure of a computer device according to an embodiment;
fig. 6 is an internal structural view of a computer device in another embodiment.
Detailed Description
In order to make the objects, technical solutions and advantages of the present application more apparent, the present application is described in further detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative of the present application and are not intended to limit the present application.
The report generation method provided by the application can be applied to the application environment shown in fig. 1. Wherein the terminal 102 communicates with the server 104 via a network. The terminal 102 may be, but not limited to, various personal computers, notebook computers, smart phones, tablet computers, and portable wearable devices, and the server 104 may be implemented by an independent server or a server cluster formed by a plurality of servers.
The server 104 is provided with a background database, and the terminal 102 can inquire data from the background database according to an access formula in the report control through communication with the server 104. The terminal 102 may construct a memory database in the memory according to the data structure of the queried data, and import the queried data into the memory database. The terminal 102 may create an index for the memory database according to the query parameter in the access formula; and taking out the data corresponding to the query parameters in the access formula from the memory database through the index, and calculating based on the taken-out data. The terminal 102 may assign the calculation result corresponding to the access formula to a report control, thereby generating a report.
In an embodiment, as shown in fig. 2, a report generating method is provided, which is described by taking the example that the method is applied to the terminal in fig. 1, and includes the following steps:
The initial report control is a report blank cell which does not display a calculation result according to the contained access formula. That is, the initial report control only includes the access formula, and does not obtain the corresponding calculation result. And the data acquisition formula is a formula for acquiring data contained in the report control.
Specifically, when a report needs to be generated, the terminal may extract the access formula contained in the report control from the initial report control of the report to be generated.
It is understood that the number formula in one report control can be at least one, namely one or more.
And step 204, inquiring data from the background database according to the access formula.
The background database is a database which is installed in the server and managed through a background program.
Specifically, the terminal generates a data query request according to the extracted access formula and sends the generated data query request to the server. And after receiving the data query request sent by the terminal, the server queries data in a background database according to the data query request.
In one embodiment, the terminal may analyze the access formula to obtain a query expression, generate a query statement according to the query expression, and query data from the background database according to the query statement. The query statement is a query instruction composed of one or more query expressions.
In an embodiment, when the number of the access formulas is multiple, the multiple access formulas may be merged to generate a total data query request, and the data corresponding to all the access formulas are queried from the background database. Therefore, resource waste caused by the fact that each access formula independently generates a data query request for querying is avoided.
The data structure is the data structure of the data inquired by the access formula.
The memory database is a database which is directly operated by putting the data inquired by the background database in the memory of the terminal.
Specifically, the data queried from the background database is placed in a temporary table, and the terminal may construct a table in the in-memory database in the local memory according to a table structure of the temporary table (i.e., a data structure of the queried data), so as to construct the in-memory database. The terminal can import the data queried in the background database into the in-memory database.
And step 208, creating an index for the memory database according to the query parameters in the access formula.
The query parameter is a parameter used for querying according to an access formula.
An index is a logical pointer to data of a memory database.
It can be understood that the index functions as a directory of the book, and the required content can be quickly found according to the page number in the directory. The index provides a pointer to the data of the in-memory database, along which the corresponding data of the in-memory database can be quickly accessed.
Specifically, the access formula may include one or more query parameters, and the terminal creates an index from fields related to the query parameters in the access formula. For example, the access formula may be calculated by building an index of the query parameters such as account book, subject, access type, currency, year, duration, etc.
Specifically, the terminal may find an index pointer corresponding to the query parameter of the access formula in the memory database, extract data corresponding to the query parameter in the access formula along the index, and perform calculation in the memory based on the extracted data.
In an embodiment, when the access formula is multiple, each access formula may fetch corresponding data from the in-memory database according to its query parameter, and calculate a calculation result of each access formula based on the fetched data.
And step 212, assigning the calculation result corresponding to the access formula to the report control to generate a report.
Specifically, the terminal assigns a calculation result corresponding to the access formula, which is obtained by calculation in the memory, to the report control, and displays the calculation result through the report control, so that a report is generated. It can be understood that the calculation results shown in the generated report, that is, the specific report data is shown.
In an embodiment, when there are multiple access formulas, the terminal may position each access formula in the report control according to the calculation result of each access formula that can be calculated in step 210, and assign each calculation result to a corresponding access formula in the report control, so as to generate the report.
According to the report generation method, the report generation device, the computer equipment and the storage medium, the access formula is extracted from the initial report control, data is inquired from the background database according to the access formula, the memory database is constructed according to the data structure of the inquired data, and the inquired data is imported into the memory database. By establishing the memory database in the cache and storing the data to be used by the access formula inquired by the background database in the memory database, the access formula can be directly used for accessing the local memory database, so that the interaction frequency with the background database is reduced, and the data access speed is improved. And creating an index for the memory database according to the query parameters in the access formula, taking out the data corresponding to the query parameters in the access formula from the memory database through the index, calculating based on the taken-out data, assigning the calculation result corresponding to the access formula to the report control, and generating a report. The index corresponding to the query parameter in the access formula is created in the memory database, so that the access formula can quickly take out corresponding data in the memory database according to the index, and the access time is saved. And finally, calculating the acquired data to obtain a calculation result, assigning the calculation result to the report control, and generating a report. Therefore, the report generation method can effectively improve the calculation performance of the terminal required by report generation.
In one embodiment, querying data from the background database according to an access formula comprises: when the extracted access formulas are multiple, grouping the multiple formulas; merging the query parameters in the same group of access formulas to obtain query parameter sets corresponding to the access formulas of each group; and respectively querying data from the background database according to the query parameter sets.
The query parameter set is a complete set of query parameters obtained after the query parameters are deduplicated. It is understood that the query parameter set includes individual parameters and parameter ranges. For example, the parameter of the query parameter set may be a parameter range from 1 month 2020 to 5 months 2020, or, for example, the currency code may be a single parameter value of 01.
Specifically, when the terminal detects that a plurality of access formulas extracted from the initial report control are multiple, the extracted formulas are grouped according to a preset grouping rule. Further, the terminal may combine the query parameters in the same group of access formulas to obtain a set of query parameters corresponding to each group of access formulas. And the terminal queries data from the background database according to the query parameter set.
In a specific embodiment, the manner of grouping the formulas may be grouping according to the types of the access formulas in the initial report control, or grouping a plurality of access formulas according to the types of the parameters of the access formulas in the initial report control, which depends on the report type and the service calculation logic and is not described herein again.
In a specific embodiment, referring to the flowchart of the report generation step in fig. 3, the access formula is divided into two groups according to the type of the access formula, taking the type "ACCT" and the type "AcctCash" as examples. Taking a set of mathematical equations of type "ACCT" as an example,
ACCT("001","1401:1408","Y","01",2020,1,1)
ACCT("001","5001:5201","Y","01",2020,2,2)
ACCT("001","1471","Y","01",2020,3,3)
ACCT("001","2203","C","01",2020,3,3)
ACCT("002","1122","C","01",2020,4,4)
the set of numerical equations of type "ACCT" has 7 parameters, which are: account book coding, subject coding (which may be single or a range), access type (e.g., Y-term end number, C-term occurrence number … …), year, beginning period, and ending period. The terminal group of access formula with type of 'ACCT' involves 7 query parameters to be merged to form a query parameter set.
In one embodiment, when merging the query parameters, all the query parameters may be collected and separated by commas, so that each query parameter is expressed in the form of a hash value. For example, three query parameters, "001", "002", and "003" may be separated based on commas and combined to (001,002,003), and then hash values according to the respective query parameters based on the combined result.
In another embodiment, a minimum value and a maximum value can be taken from the query parameters to be merged, and separated by a colon, so as to be expressed in a form of continuous values. For example, for the three query parameters, "001", "002", and "003", the minimum value "001" and the maximum value "003" may be separated based on a colon and combined into a range value condition of (001:003), and then, continuous value taking may be performed based on the range value condition.
In this embodiment, query parameters in the access formulas of the same group are combined after the access formulas are grouped to obtain corresponding query parameter sets, and compared with directly combining the query parameter sets of all the access formulas to a background database for access, the method saves a repeated query process of query parameters, and can obtain all corresponding query parameters only by querying once.
In one embodiment, querying data from a background database according to each query parameter set respectively includes: generating a query statement according to the query parameters in the same query parameter set; and querying data from the background database according to the query statement.
Specifically, the terminal generates a query statement according to the query parameters in the same query parameter set, and then sends the query statement to the server. And the server queries data from the background database through a query statement.
In a specific embodiment, referring to the flowchart of the report generation step in fig. 3, the terminal generates a Query statement, that is, "generate access SQL" (Structured Query Language, SQL is Structured Query Language) according to the Query parameters in the same Query parameter set, and sends the Query statement to the server. The server queries data from the background database through the 'generate access SQL'.
In the embodiment, the query statements are generated for the query parameters in the query parameter set of the same access formula and are sent to the server for access, and compared with the method that the query statements are generated according to the query parameters of each access formula and are sent to the server for access, the interaction times with the server are reduced, and the time overhead is saved.
In one embodiment, the merging query parameters in the same group of access formulas to obtain query parameter sets corresponding to the access formulas of each group respectively includes: and aiming at a plurality of query parameters of the same type in the same group of access formulas, when the plurality of query parameters meet the approaching condition, generating a corresponding range access condition according to the minimum query parameter and the maximum query parameter in the query parameters.
The term "range access conditions" refers to the ranges used for accessing the data.
In one embodiment, satisfying the proximity condition means that the numerical span between the query parameter and the query parameter is relatively small. The range fetching condition is a condition for fetching a number within a range in a background database. It can be understood that the range access condition can cover most of the query parameters, so that the data corresponding to most of the query parameters can be queried by using the range access condition, and the efficiency can be improved compared with the case of querying according to a single query parameter.
Specifically, for a plurality of query parameters of the same type in the same group of access formulas, when the plurality of query parameters satisfy the proximity condition, the terminal may use the minimum query parameter and the maximum query parameter in the query parameters as end points for the query parameters satisfying the proximity condition, and separately generate the corresponding range access conditions.
In one embodiment, the terminal may separate the minimum query parameter and the maximum query parameter by a colon to generate the range access condition. In other embodiments, the terminal may express the range access condition in other manners, and is not limited to colon separation.
In other embodiments, when the number of the plurality of query parameters is small and easy to enumerate, even if the proximity condition is satisfied, the query parameters may be fetched under a single-value fetch condition instead of the generation range fetch condition.
In a specific embodiment, when query parameters which do not satisfy the proximity condition exist simultaneously, the terminal collects the parameters as single-value access conditions for separation when merging. The query parameters that do not satisfy the proximity condition refer to query parameters with a large span. And the single-value access condition is used for accessing with a single query parameter.
It will be appreciated that generating a scoped query with query parameters that do not satisfy the resolution criteria results in the query parameters covered in the scoped query being largely unnecessary, and thus results in the querying of a large amount of additional excess data, and therefore, the scoped query is not appropriate in this situation. For example, if the years are 2020-01 and 2020-10, the actual years are less, but the span is larger, so that the query with the range is not suitable, and the number-taking condition in this case is a single value number-taking condition that the years are equal to 2020-01 or 2020-10.
It should be noted that, for some numerical query parameters, the range query condition cannot be generated, but the single-value access condition is used for accessing.
Further, the terminal may combine the range query condition and the single-value query condition corresponding to the same set of access formulas to obtain the final corresponding access condition of the set of access formulas.
In a specific embodiment, again taking the set of mathematical equations of type "ACCT" as an example,
ACCT("001","1401:1408","Y","01",2020,1,1)
ACCT("001","5001:5201","Y","01",2020,2,2)
ACCT("001","1471","Y","01",2020,3,3)
ACCT("001","2203","C","01",2020,3,3)
ACCT("002","1122","C","01",2020,4,4)
for example, the calculation involves 001,002 two account book codes, the minimum subject code is 1122, the maximum subject code is 5201, the number categories include Y-term end number and C-term occurrence number, the currency code is 01-RMB, and the annual period ranges from 1 month in 2020 to 4 months in 2020.
And merging the query parameters in the set of access formulas. When the query parameters satisfy the proximity condition, the terminal generates a corresponding range access condition according to the minimum query parameter and the maximum query parameter in the query parameters, for example, the minimum subject code is 1122, the maximum subject code is 5201, that is, the range access condition of "1122:5201" is generated. It can be understood that the Y-term end number and the C-term occurrence number are not numerical query parameters, so that the two cannot be combined into a range access condition, and access is performed by using a single-value access condition. Therefore, the terminal can encode 001,002 two accounts, the number category has Y-term end number and C-term occurrence number, the currency code is 01-RMB, and the two accounts are combined into '001,002', 'Y, C' and '01'. It is understood that the age range is 1 month 2020 to 4 months 2020, i.e. may be combined into 2020,1, 4. Finally, the query parameter sets "ACCT ("001,002","1122:5201"," Y, C ","01",2020,1, 4)" respectively corresponding to the set of access formulas are obtained.
In this embodiment, the corresponding range access condition is generated according to the minimum query parameter and the maximum query parameter in the query parameters, and through such processing, the data size of the query parameter set of the query statement that is finally sent to the server can be reduced.
In one embodiment, the retrieving, by indexing, data corresponding to query parameters in an access formula from an in-memory database, and performing calculation based on the retrieved data includes: when a plurality of extracted access formulas are provided, respectively splicing the query parameters in each access formula to generate a filtering condition; according to the index and the filtering condition corresponding to each access formula, taking out the corresponding data in each access formula from the memory database; and calculating based on the extracted data to obtain a calculation result corresponding to each access formula.
The filter condition is one or more command statements composed of query parameters and in-memory database language.
It can be understood that the filtering condition is used for filtering and querying data corresponding to a single access formula from data of all access formulas stored in the in-memory database.
Specifically, when the access formulas extracted from the initial report control are multiple, the terminal respectively splices the query parameters in each access formula with the query language of the in-memory database to generate the filtering conditions. And after the terminal acquires the filtering conditions, the terminal acquires the corresponding data in each access formula from the memory database according to the index and the filtering conditions corresponding to each access formula. And after the terminal acquires the corresponding data in the access formulas, calculating based on the acquired data to obtain a calculation result corresponding to each access formula.
In one particular embodiment, the in-memory database query language to which the filter criteria need to be applied may be set as a template. When the access formulas are required to be inquired in the memory database, the terminal automatically fills the inquiry parameters of each access formula in the set template to generate a complete filtering condition. When the query is required for the access formula in the memory database, the query parameters of each access formula can be manually filled in the set template to generate a complete filtering condition.
IN a specific embodiment, referring to the flowchart of the report generation step IN fig. 3, still taking the set of access formulas with the type "ACCT" as an example, "generate access SQL", respectively concatenate the query parameters IN each access formula to generate a filter condition, for example, concatenating 001,002 two book codes together with the IN-memory database query language to generate a filter condition, "WHERE fbook (user) IN ('001,002')".
In this embodiment, the query parameters in each access formula are spliced to generate the filtering condition, and the data corresponding to each access formula is fetched from the memory database, because the fetching of the data in the memory database is faster than the fetching and calculating speed of the data of the disk or the background database, the data corresponding to each access formula can be fetched more quickly, and the calculation result corresponding to each access formula can be obtained more quickly.
In one embodiment, performing the calculation based on the retrieved data comprises: calculating based on the taken data to generate a data dictionary; assigning the calculation result corresponding to the access formula to a report control to generate a report, comprising: and determining a corresponding access formula in the report control according to the keys in the data dictionary, and assigning the calculation result to the access formula determined in the report control according to the values in the data dictionary.
Where a data dictionary is a collection of definitions for all data elements used in a memory database. The key in the data dictionary is a character string composed of the name of the access formula and the query parameter in the access formula. And the values in the data dictionary are the calculation results of the access formula.
Specifically, for each access formula, the terminal may perform calculation according to data extracted from the memory database for the access formula, form a character string with a name of the access formula and a query parameter in the access formula, and use the character string as a key of the data dictionary, and use a calculation result of the access formula obtained through calculation as a value of the data dictionary, thereby generating the data dictionary. The terminal can determine a corresponding access formula from the report control according to the keys in the data dictionary, assign the calculation result to the access formula determined in the report control according to the values in the data dictionary, and further generate a report including the calculation result.
In a specific embodiment, taking the access formula ACCT ("001", "1401:1408", "Y", "01",2020,1,1) as an example, the corresponding data Dictionary is Dictionary [ (ACCT _001_1401:1408_ Y _01_2020_1_1) ] -1000.00. Wherein, (ACCT _001_1401:1408_ Y _01_2020_1_1) is a key of the data dictionary, and represents the name of the access formula and the query parameter in the access formula. 1000 is the value of the data dictionary, which represents the settlement result calculated according to the data corresponding to the access formula, i.e. the report control corresponding to the access formula ACCT ("001", "1401:1408", "Y", "01",2020,1,1) needs to be assigned with a value.
In the embodiment, through the data dictionary, the system for operating the report form of the terminal can conveniently inquire the corresponding calculation result and quickly and accurately assign the corresponding report form control.
In one embodiment, the retrieving, by indexing, data corresponding to the query parameter in the access formula from the in-memory database includes: constructing a query expression according to the query parameters in the access formula; and when the execution plan matched with the query expression is cached in the memory database, taking out the data corresponding to the query parameters in the access formula from the memory database according to the cached matched execution plan and the index.
The query expression is an expression constructed by an operator and an operation object according to query parameters in the access formula, or an expression constructed by a single operation object according to the query parameters in the access formula.
The execution plan is a description of an execution process or an access path of a query statement for searching data in the memory database.
Specifically, when the terminal wants to query data from the memory database, a query expression may be constructed according to the query parameters in the access formula. When the execution plan matched with the query expression is cached in the memory database, the terminal directly takes out the data corresponding to the query parameters in the access formula from the memory database according to the cached matched execution plan and the index.
In a specific embodiment, the terminal may perform matching according to the constructed query expression, and when a repeated query expression occurs in the matching, the terminal directly fetches data corresponding to the query parameter in the access formula from the memory database according to the execution plan of the cache for the repeated query expression.
In this embodiment, for the executed execution plan, the memory database may use the existing execution plan before, and does not need to re-edit and analyze the query expression, so that the use of the memory database can improve the calculation performance of the terminal in calculating the data corresponding to the query parameter in the access formula.
In one embodiment, the retrieving, by indexing, data corresponding to the query parameter in the access formula in the in-memory database further includes: when the execution plan matched with the query expression is not cached in the memory database, compiling and analyzing the query expression through the memory database to generate an execution plan, and caching the execution plan; and according to the index and the generated execution plan, taking out data corresponding to the query parameters in the access formula from the memory database.
Specifically, when the execution plan matched with the query expression is not cached in the memory database, the terminal compiles and analyzes the query expression through the memory database to generate an execution plan, and caches the execution plan. And the terminal executes the generated execution plan, and takes out the data corresponding to the query parameters in the access formula from the memory database along the pointer in the index.
In a specific embodiment, the execution plan is generated and cached by the in-memory database after the query expression is compiled and analyzed. The user can also check the execution plan generated by compiling and analyzing the memory database aiming at the query expression by sending a corresponding instruction.
In this embodiment, the memory database only compiles the query expressions of the execution plan which is not cached and matched with the query expressions, and does not need to compile all the query expressions, so that the calculation performance of the terminal for calculating the data corresponding to the query parameters in the access formula is improved.
It should be understood that, although the steps in the flowchart of fig. 2 are shown in order as indicated by the arrows, the steps are not necessarily performed in order as indicated by the arrows. The steps are not performed in the exact order shown and described, and may be performed in other orders, unless explicitly stated otherwise. Moreover, at least a portion of the steps in fig. 2 may include multiple steps or multiple stages, which are not necessarily performed at the same time, but may be performed at different times, which are not necessarily performed in sequence, but may be performed in turn or alternately with other steps or at least a portion of the other steps or stages.
In one embodiment, the report generation method further includes that the in-memory database instance is packaged at the terminal.
In this embodiment, a new memory database instance is constructed for each calculation of the report, and is automatically released after the report is used up. After the memory database instance is packaged, all operations (connection character strings, connection opening, connection closing and the like) do not need to be reconstructed during calling, and the operations can be conveniently used as local objects.
In one embodiment, as shown in fig. 4, there is provided a report generating apparatus 400, including: an extraction module 402, a query module 404, a construction module 406, a creation module 408, a calculation module 410, and a generation module 412, wherein:
and an extracting module 402, configured to extract the access formula from the initial report control.
And the query module 404 is configured to query data from the background database according to the access formula.
The building module 406 is configured to build the memory database according to the data structure of the queried data, and import the queried data into the memory database.
And the creating module 408 is configured to create an index for the memory database according to the query parameter in the access formula.
The calculating module 410 is configured to extract, through the index, data corresponding to the query parameter in the access formula from the in-memory database, and perform calculation based on the extracted data.
And the generating module 412 is configured to assign the calculation result corresponding to the access formula to the report control, so as to generate a report.
In one embodiment, the query module 404 is further configured to group a plurality of formulas when the extracted access formula is multiple; merging the query parameters in the same group of access formulas to obtain query parameter sets corresponding to the access formulas of each group; and respectively querying data from the background database according to the query parameter sets.
In one embodiment, the query module 404 is further configured to generate a query statement according to the query parameters in the same query parameter set; and querying data from the background database according to the query statement.
In one embodiment, the query module 404 is further configured to generate a corresponding range access condition according to a minimum query parameter and a maximum query parameter of the query parameters when the plurality of query parameters satisfy the proximity condition for a plurality of query parameters of the same type in the same set of access formulas.
In one embodiment, the calculation module 410 is further configured to, when the extracted access formulas are multiple, respectively concatenate the query parameters in each access formula to generate a filter condition; according to the index and the filtering condition corresponding to each access formula, taking out the data corresponding to each access formula from the memory database; and calculating based on the extracted data to obtain a calculation result corresponding to each access formula.
In one embodiment, the calculation module 410 is further configured to perform calculation based on the retrieved data to generate a data dictionary; the key in the data dictionary is a character string consisting of the name of the access formula and the query parameter in the access formula, and the value in the data dictionary is the calculation result of the access formula; and determining a corresponding access formula in the report control according to the keys in the data dictionary, and assigning the calculation result to the access formula determined in the report control according to the values in the data dictionary.
In one embodiment, the calculation module 410 is further configured to construct a query expression according to the query parameter in the access formula; and when the execution plan matched with the query expression is cached in the memory database, taking out the data corresponding to the query parameters in the access formula from the memory database according to the cached matched execution plan and the index.
In an embodiment, the calculation module 410 is further configured to, when an execution plan matched with the query expression is not cached in the memory database, compile and analyze the query expression through the memory database, generate an execution plan, and cache the execution plan; and according to the index and the generated execution plan, taking out data corresponding to the query parameters in the access formula from the memory database.
For the specific definition of the report generation apparatus, reference may be made to the above definition of the report generation method, which is not described herein again. All or part of the modules in the report generation device can be realized by software, hardware and a combination thereof. The modules can be embedded in a hardware form or independent from a processor in the computer device, and can also be stored in a memory in the computer device in a software form, so that the processor can call and execute operations corresponding to the modules.
In one embodiment, a computer device is provided, which may be a server, the internal structure of which may be as shown in fig. 5. The computer device includes a processor, a memory, and a network interface connected by a system bus. Wherein the processor of the computer device is configured to provide computing and control capabilities. The memory of the computer device comprises a nonvolatile storage medium and an internal memory. The non-volatile storage medium stores an operating system, a computer program, and a database. The internal memory provides an environment for the operation of an operating system and computer programs in the non-volatile storage medium. The database of the computer equipment is used for storing data required by the report form access formula. The network interface of the computer device is used for communicating with an external terminal through a network connection. The computer program is executed by a processor to implement a report generating method.
In one embodiment, a computer device is provided, which may be a terminal, and its internal structure diagram may be as shown in fig. 6. The computer device includes a processor, a memory, a communication interface, a display screen, and an input device connected by a system bus. Wherein the processor of the computer device is configured to provide computing and control capabilities. The memory of the computer device comprises a nonvolatile storage medium and an internal memory. The non-volatile storage medium stores an operating system and a computer program. The internal memory provides an environment for the operation of an operating system and computer programs in the non-volatile storage medium. The communication interface of the computer device is used for carrying out wired or wireless communication with an external terminal, and the wireless communication can be realized through WIFI, an operator network, NFC (near field communication) or other technologies. The computer program is executed by a processor to implement a report generating method. The display screen of the computer equipment can be a liquid crystal display screen or an electronic ink display screen, and the input device of the computer equipment can be a touch layer covered on the display screen, a key, a track ball or a touch pad arranged on the shell of the computer equipment, an external keyboard, a touch pad or a mouse and the like.
Those skilled in the art will appreciate that the architecture shown in fig. 6 is merely a block diagram of some of the structures associated with the disclosed aspects and is not intended to limit the computing devices to which the disclosed aspects apply, as particular computing devices may include more or less components than those shown, or may combine certain components, or have a different arrangement of components.
In one embodiment, a computer device is provided, which includes a memory and a processor, wherein the memory stores a computer program, and the processor implements the report generation method of the above-mentioned various embodiments when executing the computer program.
In one embodiment, a computer-readable storage medium is provided, on which a computer program is stored, which, when executed by a processor, implements the report generating method of the various embodiments described above.
It will be understood by those skilled in the art that all or part of the processes of the methods of the embodiments described above can be implemented by hardware instructions of a computer program, which can be stored in a non-volatile computer-readable storage medium, and when executed, can include the processes of the embodiments of the methods described above. Any reference to memory, storage, database or other medium used in the embodiments provided herein can include at least one of non-volatile and volatile memory. Non-volatile Memory may include Read-Only Memory (ROM), magnetic tape, floppy disk, flash Memory, optical storage, or the like. Volatile Memory can include Random Access Memory (RAM) or external cache Memory. By way of illustration and not limitation, RAM can take many forms, such as Static Random Access Memory (SRAM) or Dynamic Random Access Memory (DRAM), among others.
The technical features of the above embodiments can be arbitrarily combined, and for the sake of brevity, all possible combinations of the technical features in the above embodiments are not described, but should be considered as the scope of the present specification as long as there is no contradiction between the combinations of the technical features.
The above-mentioned embodiments only express several embodiments of the present application, and the description thereof is more specific and detailed, but not construed as limiting the scope of the invention. It should be noted that, for a person skilled in the art, several variations and modifications can be made without departing from the concept of the present application, which falls within the scope of protection of the present application. Therefore, the protection scope of the present patent shall be subject to the appended claims.
Claims (11)
1. A report generation method is characterized by comprising the following steps:
extracting a figure taking formula from the initial report control;
inquiring data from a background database according to the access formula;
constructing a memory database according to a data structure of the inquired data, and importing the inquired data into the memory database;
creating an index for the memory database according to the query parameters in the access formula;
taking out data corresponding to the query parameters in the access formula from the memory database through the index, and calculating based on the taken out data;
and assigning the calculation result corresponding to the access formula to the report control to generate a report.
2. The method of claim 1, wherein querying data from a background database according to the access formula comprises:
when the extracted access formula is multiple, grouping the multiple formulas;
merging the query parameters in the same group of access formulas to obtain query parameter sets corresponding to the access formulas of each group;
and respectively querying data from a background database according to each query parameter set.
3. The method of claim 2, wherein querying data from a background database according to each of the query parameter sets comprises:
generating a query statement according to the query parameters in the same query parameter set;
and querying data from a background database according to the query statement.
4. The method of claim 2, wherein the merging the query parameters in the same set of access formulas to obtain the query parameter sets corresponding to the access formulas respectively comprises:
aiming at a plurality of query parameters of the same type in the same group of access formulas, when the query parameters meet the proximity condition, generating a corresponding range access condition according to the minimum query parameter and the maximum query parameter in the query parameters.
5. The method according to claim 1, wherein the retrieving data corresponding to the query parameter in the access formula from the in-memory database through the index and performing calculation based on the retrieved data comprises:
when a plurality of extracted access formulas are provided, respectively splicing the query parameters in each access formula to generate a filtering condition;
according to the index and the filtering condition corresponding to each access formula, taking out the data corresponding to each access formula from the memory database;
and calculating based on the extracted data to obtain a calculation result corresponding to each access formula.
6. The method of claim 1, wherein said performing a calculation based on said retrieved data comprises:
calculating based on the taken data to generate a data dictionary; the key in the data dictionary is a character string consisting of the name of the access formula and the query parameter in the access formula, and the value in the data dictionary is the calculation result of the access formula;
assigning the calculation result corresponding to the access formula to the report control to generate a report, comprising:
and determining a corresponding access formula in the report control according to the keys in the data dictionary, and assigning the calculation result to the access formula determined in the report control according to the values in the data dictionary.
7. The method according to claim 1, wherein the retrieving data corresponding to the query parameter in the access formula from the in-memory database through the index comprises:
constructing a query expression according to the query parameters in the access formula;
when the execution plan matched with the query expression is cached in the memory database, then
And according to the matched execution plan and the index, taking out the data corresponding to the query parameters in the access formula from the memory database.
8. The method of claim 7, wherein the retrieving, from the in-memory database through the index, data corresponding to query parameters in the access formula further comprises:
when the execution plan matched with the query expression is not cached in the memory database, the execution plan is executed
Compiling and analyzing the query expression through the memory database to generate an execution plan, and caching the execution plan;
and according to the index and the generated execution plan, taking out data corresponding to the query parameters in the access formula from the memory database.
9. A report generation apparatus, characterized in that the apparatus comprises:
the extraction module is used for extracting the access formula from the initial report control;
the query module is used for querying data from a background database according to the access formula;
the building module is used for building a memory database according to the data structure of the inquired data and importing the inquired data into the memory database;
the creating module is used for creating an index for the memory database according to the query parameters in the access formula;
the calculation module is used for taking out the data corresponding to the query parameters in the access formula from the memory database through the index and calculating based on the taken out data;
and the generating module is used for assigning the calculation result corresponding to the access formula to the report control to generate a report.
10. A computer device comprising a memory and a processor, the memory storing a computer program, characterized in that the processor, when executing the computer program, implements the steps of the method of any of claims 1 to 8.
11. A computer-readable storage medium, on which a computer program is stored, which, when being executed by a processor, carries out the steps of the method of any one of claims 1 to 8.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202011137858.4A CN112434018B (en) | 2020-10-22 | 2020-10-22 | Report generation method, report generation device, computer equipment and storage medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202011137858.4A CN112434018B (en) | 2020-10-22 | 2020-10-22 | Report generation method, report generation device, computer equipment and storage medium |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN112434018A true CN112434018A (en) | 2021-03-02 |
| CN112434018B CN112434018B (en) | 2024-06-04 |
Family
ID=74695817
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202011137858.4A Active CN112434018B (en) | 2020-10-22 | 2020-10-22 | Report generation method, report generation device, computer equipment and storage medium |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN112434018B (en) |
Citations (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101866350A (en) * | 2010-05-11 | 2010-10-20 | 中兴通讯股份有限公司 | Report generation method and device as well as WEB server |
| CN102521416A (en) * | 2011-12-28 | 2012-06-27 | 用友软件股份有限公司 | Data correlation query method and data correlation query device |
| US20180004782A1 (en) * | 2014-07-31 | 2018-01-04 | Quickbase, Inc. | Dynamic modeling of data in relational databases |
| CN108182630A (en) * | 2018-02-02 | 2018-06-19 | 金蝶软件（中国）有限公司 | Counteracting method and device in a kind of group financial report |
| CN110909014A (en) * | 2018-09-14 | 2020-03-24 | 阿里巴巴集团控股有限公司 | Optimization suggestion generation and database query method, device, equipment and storage medium |
| US20200134077A1 (en) * | 2018-10-30 | 2020-04-30 | Elasticsearch B.V. | Systems and Methods for Reducing Data Storage Overhead |
| CN111506606A (en) * | 2020-04-14 | 2020-08-07 | 深圳市金蝶天燕云计算股份有限公司 | Report query method and related equipment |
-
2020
- 2020-10-22 CN CN202011137858.4A patent/CN112434018B/en active Active
Patent Citations (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101866350A (en) * | 2010-05-11 | 2010-10-20 | 中兴通讯股份有限公司 | Report generation method and device as well as WEB server |
| CN102521416A (en) * | 2011-12-28 | 2012-06-27 | 用友软件股份有限公司 | Data correlation query method and data correlation query device |
| US20180004782A1 (en) * | 2014-07-31 | 2018-01-04 | Quickbase, Inc. | Dynamic modeling of data in relational databases |
| CN108182630A (en) * | 2018-02-02 | 2018-06-19 | 金蝶软件（中国）有限公司 | Counteracting method and device in a kind of group financial report |
| CN110909014A (en) * | 2018-09-14 | 2020-03-24 | 阿里巴巴集团控股有限公司 | Optimization suggestion generation and database query method, device, equipment and storage medium |
| US20200134077A1 (en) * | 2018-10-30 | 2020-04-30 | Elasticsearch B.V. | Systems and Methods for Reducing Data Storage Overhead |
| CN111506606A (en) * | 2020-04-14 | 2020-08-07 | 深圳市金蝶天燕云计算股份有限公司 | Report query method and related equipment |
Non-Patent Citations (1)
| Title |
|---|
| 巫乔顺 等: ""一种自定义动态报表系统的设计与应用"", 《机械设计与制造工程》, vol. 42, no. 1, 31 January 2013 (2013-01-31), pages 80 - 82 * |
Also Published As
| Publication number | Publication date |
|---|---|
| CN112434018B (en) | 2024-06-04 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN110023923A (en) | It generates data and converts workflow | |
| CN110795455A (en) | Dependency relationship analysis method, electronic device, computer device and readable storage medium | |
| CN105550206B (en) | The edition control method and device of structured query sentence | |
| CN106293891B (en) | Multidimensional investment index monitoring method | |
| CN111581271A (en) | Interface parameter determination method and device, computer equipment and storage medium | |
| CN112860727B (en) | Data query method, device, equipment and medium based on big data query engine | |
| US11663288B2 (en) | Just-in-time front end template generation using logical document object models | |
| CN112930529A (en) | Generating software artifacts from conceptual data models | |
| CN111858608A (en) | Data management method, device, server and storage medium | |
| CA3149710A1 (en) | Data collecting method, device, computer equipment and storage medium | |
| CN110519263A (en) | Anti- brush amount method, apparatus, equipment and computer readable storage medium | |
| CN115335821A (en) | Offloading statistics collection | |
| CN113918149A (en) | Interface development method and device, computer equipment and storage medium | |
| CN114356968A (en) | Query statement generation method and device, computer equipment and storage medium | |
| CN110765152A (en) | SQL extraction method and device, computer equipment and storage medium | |
| CN114372102A (en) | Data analysis method and device, storage medium and electronic equipment | |
| CN113642301A (en) | Report generation method, device and system | |
| CN116860311A (en) | Script analysis method, script analysis device, computer equipment and storage medium | |
| CN103809915A (en) | Read-write method and device of magnetic disk files | |
| CN112434018A (en) | Report generation method and device, computer equipment and storage medium | |
| CN113010550A (en) | Batch processing object generation and batch processing method and device for structured data | |
| Yang et al. | Acquisition of ERA models from data intensive code | |
| CN110851517A (en) | Source data extraction method, device and equipment and computer storage medium | |
| CN112527880B (en) | Method, device, equipment and medium for collecting metadata information of big data cluster | |
| CN114626062B (en) | Website application user interaction point discovery method and system based on dynamic and static combination |
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 |