Real-time index data query method, device and readable storage medium for database

Abstract

The present invention discloses a real-time indicator data query method, device and readable storage medium, including: receiving a real-time indicator data query request for a database input from an external source, wherein the database maintains a fact table and a dimension table. According to the required data indicator, the dependent target fact table and target dimension table are determined, and then according to the filtering conditions, these tables are filtered to obtain pending real-time data indicators. Finally, multiple pending real-time data indicators are integrated into a target real-time data indicator. With such a design, real-time data queries can be efficiently processed through an automated process, providing effective support for real-time business decisions.

Description

Technical Field

[0001] The present invention relates to the technical field of database management, and in particular to a real-time indicator data query method, device and readable storage medium for a database. Background Art

[0002] With the development of big data technology, business systems need to query a large amount of data in real time to obtain key business indicators. In the existing technology, data query usually generates data in advance and then exposes this data to external applications. In addition, the existing methods cannot filter the existing fact tables and dimension tables, which makes it impossible for business systems to accurately query specific conditions. Existing data queries can usually only achieve "pseudo real-time", that is, the data is not generated in real time, but updated regularly. Therefore, when the business system needs to obtain the latest data, it may encounter data delay problems. This is not only complicated to operate, but also inefficient to query.

[0003] Therefore, how to improve query efficiency, reduce operational complexity, and achieve true real-time data query by optimizing query methods is an important issue facing the current database technology field. Summary of the invention

[0004] The object of the present invention is to provide a method, device and readable storage medium for real-time index data query of a database.

[0005] In a first aspect, an embodiment of the present invention provides a real-time indicator data query method for a database, comprising:

[0006] Receiving an externally input real-time indicator data query request for a database, wherein the database maintains a fact table and a dimension table, and the real-time indicator data query includes filtering conditions for the fact table and the dimension table and a required data indicator;

[0007] According to the demand data indicator, determining from the database a target fact table and a target dimension table on which the demand data indicator depends;

[0008] According to the filtering condition, the target fact table and the target dimension table are filtered to obtain a plurality of pending real-time data indicators;

[0009] The multiple pending real-time data indicators are integrated to obtain a target real-time data indicator.

[0010] In a possible implementation, the target fact table and the target dimension table are filtered according to the filtering condition to obtain a plurality of pending real-time data indicators, including:

[0011] Obtaining the filter field of the filter condition;

[0012] Extracting related information from the target fact table and the target dimension table according to the filter field;

[0013] Filtering is performed according to the associated information to obtain the multiple pending real-time data indicators from the target fact table and the target dimension table.

[0014] In a possible implementation manner, the integrating the multiple pending real-time data indicators to obtain the target real-time data indicator includes:

[0015] Determining whether the types of the multiple pending real-time data indicators are all common indicators;

[0016] If yes, directly integrating the multiple pending real-time data indicators to obtain the target real-time data indicator;

[0017] If not, based on the corresponding type of each of the pending real-time data indicators, integration is performed according to preset rules to obtain the target real-time data indicator.

[0018] In a possible implementation manner, directly integrating the multiple pending real-time data indicators to obtain the target real-time data indicator includes:

[0019] Based on the dimension attribute information corresponding to each pending real-time data indicator, the association relationship between each pending real-time data indicator is determined through the metadata database;

[0020] According to the association relationship, the target real-time data indicator is integrated to obtain.

[0021] In a possible implementation, the step of integrating the corresponding types of each of the pending real-time data indicators according to a preset rule to obtain the target real-time data indicator includes:

[0022] In the case where the type of the current real-time data indicator is a derived indicator or a summary indicator, obtaining a common indicator associated with the current real-time data indicator, wherein the current real-time data indicator is any real-time data indicator among the multiple pending real-time data indicators, the derived indicator is derived from the common indicator, and the summary indicator is obtained by summarizing the common indicators;

[0023] According to the common indicator associated with the current real-time data indicator, the real-time data indicator in the same dimension as the current real-time data indicator is obtained and integrated to obtain the target real-time data indicator.

[0024] In a possible implementation, the step of integrating the corresponding types of each of the pending real-time data indicators according to a preset rule to obtain the target real-time data indicator includes:

[0025] In the case where the type of the current real-time data indicator is an SQL indicator, analyzing the SQL syntax tree corresponding to the SQL indicator to obtain a target field, and the current real-time data indicator is any real-time data indicator among the multiple pending real-time data indicators;

[0026] According to the target field, a real-time data indicator in the same dimension as the current real-time data indicator is determined from metadata and integrated to obtain the target real-time data indicator.

[0027] In a possible implementation manner, the integrating the multiple pending real-time data indicators to obtain the target real-time data indicator includes:

[0028] In the case where the dimensions of the multiple pending real-time data indicators are the same, extracting the measurement content of each of the pending real-time data indicators;

[0029] Integrate the SQL statements corresponding to each of the measurement contents to obtain a target SQL statement;

[0030] Execute the target SQL statement to obtain the target real-time data indicator.

[0031] In a second aspect, an embodiment of the present invention provides a real-time index data query device for a database, comprising:

[0032] An acquisition module is used to receive an externally input real-time indicator data query request for a database, wherein the database maintains a fact table and a dimension table, and the real-time indicator data query includes filtering conditions for the fact table and the dimension table and a required data indicator;

[0033] A query module is used to determine, from the database, a target fact table and a target dimension table on which the demand data indicator depends according to the demand data indicator; filter the target fact table and the target dimension table according to the filtering condition to obtain a plurality of pending real-time data indicators; and integrate the plurality of pending real-time data indicators to obtain a target real-time data indicator.

[0034] In a third aspect, an embodiment of the present invention provides a computer device, comprising a processor and a non-volatile memory storing computer instructions, wherein when the computer instructions are executed by the processor, the computer device executes the real-time indicator data query method for a database described in at least one possible implementation of the first aspect.

[0035] In a fourth aspect, an embodiment of the present invention provides a readable storage medium, wherein the readable storage medium includes a computer program, and when the computer program is running, the computer device where the readable storage medium is located is controlled to execute the real-time indicator data query method for a database described in at least one possible implementation manner of the first aspect.

[0036] Compared with the prior art, the beneficial effects provided by the present invention include: using a real-time indicator data query method, device and readable storage medium for a database disclosed in the present invention, including: receiving an external input real-time indicator data query request for a database, and the database maintains a fact table and a dimension table. According to the required data indicators, the dependent target fact table and target dimension table are determined, and then according to the filtering conditions, these tables are filtered to obtain pending real-time data indicators. Finally, multiple pending real-time data indicators are integrated into target real-time data indicators. Such a design can efficiently process real-time data queries through an automated process, providing effective support for real-time business decisions. BRIEF DESCRIPTION OF THE DRAWINGS

[0037] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the accompanying drawings required for use in the embodiments are briefly introduced below. It should be understood that the following drawings only illustrate certain embodiments of the present invention and should not be regarded as limiting the scope. For those of ordinary skill in the art, other related drawings can also be obtained based on these drawings without creative work.

[0038] Figure 1 A schematic diagram of the steps of a method for querying real-time index data in a database provided by an embodiment of the present invention;

[0039] Figure 2 A schematic block diagram of the structure of a real-time index data query device for a database provided by an embodiment of the present invention;

[0040] Figure 3 A schematic block diagram of the structure of a computer device provided in an embodiment of the present invention. DETAILED DESCRIPTION

[0041] In order to make the purpose, technical solutions and advantages of the embodiments of the present invention clearer, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, not all of the embodiments. Generally, the components of the embodiments of the present invention described and shown in the drawings here can be arranged and designed in various different configurations.

[0042] The specific implementation modes of the present invention are described in detail below in conjunction with the accompanying drawings.

[0043] In order to solve the technical problems in the aforementioned background technology, Figure 1 A flowchart of a real-time indicator data query method for a database provided in an embodiment of the present disclosure is provided. The real-time indicator data query method for a database is introduced in detail below.

[0044] Step S201, receiving a real-time indicator data query request for a database input from an external source, wherein the database maintains a fact table and a dimension table, and the real-time indicator data query includes filtering conditions for the fact table and the dimension table and a required data indicator;

[0045] Step S202, according to the demand data indicator, determining from the database a target fact table and a target dimension table on which the demand data indicator depends;

[0046] Step S203, filtering the target fact table and the target dimension table according to the filtering condition to obtain a plurality of pending real-time data indicators;

[0047] Step S204: Integrate the multiple pending real-time data indicators to obtain a target real-time data indicator.

[0048] In the embodiment of the present invention, for example, assume that there is a data warehouse of an e-commerce platform, which contains fact tables and dimension tables. The fact table records the data of each order transaction, and the dimension table contains various information from which to analyze the fact table, such as region, product category, etc. Assume that the external system needs to query the product category with the highest sales on a certain day, and the request parameters are as follows:

[0049]

[0050] According to the required data indicators, it is determined that the dependent fact table "order transaction table" and dimension table "commodity category table" are required. The filtering condition is to filter out the order transaction data of the specified date according to the date field, and filter out the order transaction records of the day from the fact table. After filtering, the pending real-time data indicators are obtained, that is, the order transaction records of the day. According to the requirements, it is hoped to obtain the commodity category with the highest sales. Therefore, it is necessary to summarize the pending real-time data indicators according to the commodity category and calculate the sales of each commodity category. In this design, by receiving the external input indicator query request, the dependent target fact table and target dimension table are determined, the target table is filtered according to the filtering conditions, and then the data is integrated to obtain the final real-time data indicator, that is, the commodity category with the highest sales.

[0051] In the embodiment of the present invention, the aforementioned step S203 may be implemented in the following manner.

[0052] (1) Obtaining the filter field of the filter condition;

[0053] (2) extracting related information from the target fact table and the target dimension table according to the filter field;

[0054] (3) Filtering is performed according to the associated information to obtain the multiple pending real-time data indicators from the target fact table and the target dimension table.

[0055] In the embodiment of the present invention, for example, assume that there is a data warehouse of an e-commerce platform, which contains fact tables and dimension tables. The fact table records the data of each order transaction, and the dimension table contains various information from which to analyze the fact table, such as region, product category, etc. Assume that the external system request needs to be filtered based on date and product category, and the filtering conditions are as follows:

[0056]

[0057]

[0058] According to the fields in the filter conditions, the associated information is extracted from the target fact table and the target dimension table. Assuming that the target fact table is the "order transaction table" and the target dimension table is the "product category table", by analyzing the filter conditions, it can be obtained that the fields that need to be associated are the date field of the order transaction table and the product category field of the product category table. According to the extracted associated information, the target fact table and the target dimension table are filtered. In this example, the order transaction records of the day are filtered out according to the date field, and the records belonging to the "Electronics" category are filtered out according to the product category field. After filtering, the pending real-time data indicators are obtained, that is, the order transaction records belonging to the "Electronics" category of the day. In this design, by obtaining the filter fields in the filter conditions, the associated information is extracted, and then the target fact table and the target dimension table are filtered according to the associated information, so as to obtain multiple pending real-time data indicators.

[0059] In the embodiment of the present invention, the aforementioned step S204 may be implemented in the following manner.

[0060] (1) determining whether the types of the plurality of pending real-time data indicators are all common indicators;

[0061] (2) If yes, directly integrating the multiple pending real-time data indicators to obtain the target real-time data indicator;

[0062] (3) If not, based on the corresponding type of each of the pending real-time data indicators, integration is performed according to preset rules to obtain the target real-time data indicator.

[0063] In an embodiment of the present invention, for example, in the previous e-commerce platform data warehouse scenario, it is assumed that multiple pending real-time data indicators have been obtained, that is, the commodity category with the highest sales on the day. Now it is necessary to integrate these pending real-time data indicators to obtain the target real-time data indicator. Assume that two pending real-time data indicators are obtained: commodity category A with the highest sales and commodity category B with the highest sales volume. First, it is necessary to determine whether the types of these two indicators are both ordinary indicators. Assuming that commodity category A with the highest sales and commodity category B with the highest sales volume are both ordinary indicator types, the two indicators can be directly integrated. A possible integration method is to calculate the weighted average of sales and sales volume, and determine the final target real-time data indicator based on the result. Assume that in addition to commodity category A with the highest sales and commodity category B with the highest sales volume, there are other types of pending real-time data indicators, such as commodity category C with the lowest return rate. In this case, based on the type of each pending real-time data indicator, it is necessary to integrate according to the preset rules. For example, for commodity category A with the highest sales and commodity category B with the highest sales volume, the target real-time data indicator can be determined using the weighted average; while for commodity category C with the lowest return rate, a different calculation method may be required, such as taking the minimum value. With such a design, in this scenario, based on the type of the pending real-time data indicator, it is determined whether it is a common indicator. If so, it is directly integrated to obtain the target real-time data indicator; if not, based on the type of each pending real-time data indicator, it is integrated according to the preset rules to obtain the final target real-time data indicator.

[0064] In the embodiment of the present invention, the aforementioned step of directly integrating the multiple pending real-time data indicators to obtain the target real-time data indicator can be implemented through the following example.

[0065] (1) Based on the dimension attribute information corresponding to each pending real-time data indicator, determine the association relationship between each pending real-time data indicator through the metadata database;

[0066] (2) According to the association relationship, the target real-time data indicator is integrated.

[0067] In an embodiment of the present invention, for example, in the previous e-commerce platform data warehouse scenario, it is assumed that multiple pending real-time data indicators have been obtained, that is, the commodity category with the highest sales on the day. Now it is necessary to integrate these pending real-time data indicators to obtain the target real-time data indicator. Assume that the pending real-time data indicators include commodity category A with the highest sales and commodity category B with the highest sales volume. First, it is necessary to determine the association relationship between them through the metadata database based on the dimensional attribute information corresponding to each pending real-time data indicator. For example, the association relationship between commodity category A and sales, and the association relationship between commodity category B and sales volume can be determined through the association fields of the commodity category dimension table and the sales fact table. After obtaining the association relationship between the pending real-time data indicators, these association relationships can be integrated to obtain the target real-time data indicator. For example, the commodity category A with the highest sales volume can be compared with the commodity category B with the highest sales volume to find out the association relationship between the two. It may be that a commodity category has both high sales and high sales volume, or there are other association relationships. By integrating the association relationships, a more comprehensive target real-time data indicator can be obtained. In this way, based on the dimensional attribute information corresponding to the pending real-time data indicators, the association relationship between each pending real-time data indicator is determined through the metadata database. Then, these associations are integrated to obtain the final target real-time data indicators. This can help better understand and analyze the relationship between different pending real-time data indicators and obtain more comprehensive analysis results.

[0068] In an embodiment of the present invention, the aforementioned step of integrating the corresponding types of each of the pending real-time data indicators according to preset rules to obtain the target real-time data indicator can be implemented through the following examples.

[0069] (1) when the type of the current real-time data indicator is a derived indicator or a summary indicator, obtaining a common indicator associated with the current real-time data indicator, wherein the current real-time data indicator is any real-time data indicator among the multiple pending real-time data indicators, the derived indicator is derived from the common indicator, and the summary indicator is obtained by summarizing the common indicators;

[0070] (2) According to the common indicator associated with the current real-time data indicator, real-time data indicators in the same dimension as the current real-time data indicator are obtained and integrated to obtain the target real-time data indicator.

[0071] In an embodiment of the present invention, for example, in the previous e-commerce platform data warehouse scenario, it is assumed that multiple pending real-time data indicators have been obtained, that is, the commodity category with the highest sales on the day. Now it is necessary to integrate these pending real-time data indicators to obtain the target real-time data indicator. Assume that one of the pending real-time data indicators is the commodity category A with the highest sales, which belongs to the derived indicator type, not the ordinary indicator type. According to the foregoing content, it is necessary to first obtain the ordinary indicator associated with the current real-time data indicator. In the scenario, the ordinary indicator associated with the commodity category A with the highest sales can be obtained through the metadata database or other data sources. The possible association relationship is the sales and sales volume of the commodity category A. Assuming that the ordinary indicator associated with the commodity category A has been obtained, then according to the foregoing content, it is necessary to obtain other real-time data indicators in the same dimension as the current real-time data indicator (commodity category A). In this scenario, it is possible to consider obtaining the sales and sales volume of other commodity categories in the same dimension as commodity category A. After obtaining the real-time data indicators in the same dimension as the current real-time data indicator, these data indicators can be integrated according to preset rules. Possible integration methods include summing, averaging, and other operations to obtain the final target real-time data indicator. With this design, when the current real-time data indicator type is a derived indicator or a summary indicator, it is necessary to first obtain the common indicator associated with the current real-time data indicator. Then, based on the dimension of the associated common indicator, obtain other real-time data indicators in the same dimension as the current real-time data indicator, and integrate them to obtain the final target real-time data indicator. Doing so can help better understand and analyze the relationship between different real-time data indicators and obtain more comprehensive analysis results.

[0072] In an embodiment of the present invention, the aforementioned step of integrating the corresponding types of each of the pending real-time data indicators according to preset rules to obtain the target real-time data indicator can be implemented through the following examples.

[0073] (1) when the type of the current real-time data indicator is an SQL indicator, analyzing the SQL syntax tree corresponding to the SQL indicator to obtain a target field, and the current real-time data indicator is any real-time data indicator among the multiple pending real-time data indicators;

[0074] (2) According to the target field, a real-time data indicator in the same dimension as the current real-time data indicator is determined from metadata and integrated to obtain the target real-time data indicator.

[0075] In an embodiment of the present invention, for example, in the previous e-commerce platform data warehouse scenario, it is assumed that multiple pending real-time data indicators have been obtained, that is, the commodity category with the highest sales on the day. Now it is necessary to integrate these pending real-time data indicators to obtain the target real-time data indicator. Assume that one of the pending real-time data indicators is the commodity category A with the highest sales, and its type is determined to be an SQL indicator. It is necessary to analyze the SQL syntax tree corresponding to the SQL indicator to obtain the target field. In the scenario, the target field can be extracted by parsing and analyzing the SQL syntax tree of the SQL indicator. For example, a section of code may be included in the SQL statement for calculating sales and associating it with the commodity category. This section of code needs to be analyzed and the commodity category field is obtained as the target field. After obtaining the target field, the metadata information can be used to find other real-time data indicators in the same dimension as the current real-time data indicator (commodity category A). For example, an association query can be performed in the dimension table based on the commodity category field to obtain other real-time data indicators in the same dimension as the commodity category, such as sales and sales volume. After obtaining the real-time data indicators in the same dimension as the current real-time data indicator, these data indicators can be integrated according to the preset rules to obtain the final target real-time data indicator. Possible integration methods include summing, averaging, and other operations. With this design, when the current real-time data indicator type is an SQL indicator, it is necessary to analyze the SQL syntax tree corresponding to the SQL indicator to obtain the target field. Then, based on the target field, other real-time data indicators in the same dimension as the current real-time data indicator are determined from the metadata and integrated to obtain the final target real-time data indicator. Doing so can help better understand and analyze the data logic of the SQL indicator and obtain more comprehensive analysis results.

[0076] In the embodiment of the present invention, the aforementioned step of integrating the multiple pending real-time data indicators to obtain the target real-time data indicator can be implemented through the following example.

[0077] (1) when the dimensions of the multiple pending real-time data indicators are the same, extracting the measurement content of each of the pending real-time data indicators;

[0078] (2) integrating the SQL statements corresponding to each of the measurement contents to obtain a target SQL statement;

[0079] (3) Execute the target SQL statement to obtain the target real-time data indicator.

[0080] In an embodiment of the present invention, for example, in the previous e-commerce platform data warehouse scenario, it is assumed that multiple pending real-time data indicators have been obtained, that is, the commodity category with the highest sales on the day. Now it is necessary to integrate these pending real-time data indicators to obtain the target real-time data indicator. In the scenario, it is assumed that there are two pending real-time data indicators: commodity category A with the highest sales and commodity category B with the highest sales volume. These two indicators have the same dimension, that is, commodity category. It is necessary to extract the measurement content of each pending real-time data indicator. For commodity category A with the highest sales, the measurement content is the value of sales; for commodity category B with the highest sales volume, the measurement content is the value of sales volume. In the scenario, a corresponding SQL statement can be constructed according to each measurement content. For example, for commodity category A with the highest sales, an SQL statement can be constructed to query the commodity category with the highest sales and return its sales value; for commodity category B with the highest sales volume, another SQL statement can be constructed to query the commodity category with the highest sales volume and return its sales value. Then, these SQL statements are integrated to obtain the target SQL statement. After obtaining the target SQL statement, the statement can be executed to query the database or data source to obtain the result. Executing the target SQL statement will return the required target real-time data indicators, such as the product category with the highest sales and the product category with the highest sales volume, as well as the corresponding measurement content. In this design, the measurement content of each pending real-time data indicator is extracted, and the corresponding SQL statement is constructed based on the measurement content. Then, these SQL statements are integrated to obtain the target SQL statement. Finally, the target SQL statement is executed to obtain the results of the target real-time data indicator from the database or data source. Doing so can help integrate multiple pending real-time data indicators to obtain the final target real-time data indicator.

[0081] An overall implementation process of an embodiment of the present invention is provided below.

[0082] First, define the terms in the embodiments of the present invention:

[0083] 1. Fact table: A data record that records the occurrence of a certain business fact, which can be likened to a running account record. For example, if each piece of data is a sales record, then this fact includes: product name, product price, sales area, secondary distributor, sales time, whether there is a discount, and other information.

[0084] 2. Dimension table: records the values ​​of the fact table from different perspectives. For example, in the region dimension table, each row records a city name.

[0085] 3. Indicator: A business-meaningful value or a batch of values ​​calculated through fact tables, dimension tables, and some limiting conditions, such as the sales summary of each province and city in 2022.

[0086] 4. Ordinary indicator: A type of indicator, also called an atomic indicator, which is the most common indivisible indicator. An indicator data table is calculated through a fact table and 0 or more dimension tables, combined with indicator caliber and business-limited logic.

[0087] 5. Derived indicator: A type of indicator, also called a derived indicator, which is composed of multiple indicators of the same dimension. At the same time, the calculation formula of the metric column can also be customized as needed.

[0088] 6. SQL indicator: A type of indicator. When you want to implement some more complex indicators, you can generate indicator data by writing native SQL statements or stored procedures.

[0089] 7. Summary indicator: A type of indicator that aggregates multiple indicators of the same dimension to provide support for subsequent data services.

[0090] Based on the above definition, in current enterprise data warehouses, business data is usually divided into dimension tables or fact tables according to Kimball dimensional modeling theory, and then combined into a star model or snowflake model by setting the relationship between dimensions / facts. On this basis, common indicators and derived indicators with complex nested relationships are created, indicators are run to generate data, and finally the data is provided to upstream BI or AI systems for use.

[0091] When calling a real-time service, the specific process is as follows:

[0092] 1. The third-party application (external) provides filtering conditions for the underlying dimension / fact table as parameters of the indicator data service, such as the following json:

[0093]

[0094]

[0095] 2. Based on the indicator, find the underlying fact dimension table that is dependent on

[0096] 3. Based on the existing dimensional model, analyze which dimensions / fact tables will be affected by the filtering conditions:

[0097] Find out which table the fields in the filter conditions exist on, and based on the existing associations, find all the dimension / fact tables that need to be filtered. For example, there is an association between the "Store Transaction Flow" fact table and the "Store" dimension table. When filtering the "Store" dimension table, the "Store Transaction Flow" fact table should also be filtered accordingly.

[0098] The filter conditions are in the user's request parameters, such as

[0099]

[0100] Indicates: It is necessary to perform equal value filtering on the field with id 51, and the filter value is 51. The path field indicates the relationship between the current table and the fact table. Assume that the following association relationship exists in the dimensional model: Fact-->Dim1-->Dim2.Fact-->Dim1

[0101] There may be multiple paths from Fact to Dim1, and using different paths will have different effects on the final result. So you need to specify the corresponding path

[0102] 4. There are corresponding processing procedures according to the current indicator type

[0103] If it is a common indicator without any dependencies, then the data is directly generated and returned to the third-party application based on the indicator caliber.

[0104] If there are dependent derivative indicators or summary indicators, then you need to find indicators of the same dimension in the dependent indicators. If there are indicators composed of SQL alone in the dependent indicators, you need to analyze the SQL syntax tree to find indicators of the same dimension. These indicator data of the same dimension can be generated by one SQL statement instead of multiple SQL statements. For example: when one indicator clearly indicates that the dimensions are "transaction time" and "transaction location", another indicator is directly written in the following SQL statement:

[0105] Select xxx from transaction table group by transaction time, transaction location;

[0106] The actual scenario may be a more complex SQL, so it is necessary to analyze the SQL syntax tree to obtain the dimension information and compare whether they are of the same dimension. If they are of the same dimension, the SQL behind multiple indicators can be integrated into one SQL.

[0107] The process of integrating SQL is as follows:

[0108] First, find indicators of the same dimension. Different operations are performed according to the indicator type:

[0109] If it is a common indicator, the attribute of the indicator itself will include dimension attributes, and the corresponding relationship can be found in the metadata database (postgres / mysql);

[0110] If it is a derived indicator, the derived indicator itself is derived from the ordinary indicator, and the same dimension of the derived indicator is found according to the ordinary indicator dimension on which it is based;

[0111] If it is an SQL indicator, analyze the SQL syntax tree, find the group by field in the syntax tree, and then find the corresponding relationship in the metadata. Different analysis schemes have different displays for syntax trees. Here is the result information of golang parsing the syntax tree:

[0112] {"Cache":null,"Distinct":false,"StraightJoinHint":false,"SQLCalcFoundRo ws":false,"From":[{"Expr":{"Name":"student","Qualifier":""},"Partitions":null,"As":"","Hints":null}],"Comments":null,"SelectExprs":[{"Expr":{"Metadata":nul l,"Name":"name","Qualifier":{"Name":"","Qualifier":""}},"As":""}],"Where":null,"GroupBy":[{"Metadata":null,"Name":"name","Qualifier":{"Name":"","Qual ifier":""}}],"Having":null,"OrderBy":null,"Li

[0113] mit":null,"Lock":0,"Into":null}

[0114] Filter out the indicators to be used by the current service from all indicators of the same dimension.

[0115] In the case of the same dimension, the SQL statements of the measurement parts of various indicators can be put together and thus integrated into one SQL statement.

[0116] 5. Run SQL to obtain and return indicator data.

[0117] With this design, the underlying dimension table / fact table can be filtered, and multiple indicators of the same dimension can be merged into one SQL statement to optimize the execution speed.

[0118] Please refer to Figure 2 , Figure 2 A real-time index data query device 110 for a database is also provided in an embodiment of the present invention, comprising:

[0119] The acquisition module 1101 is used to receive a real-time indicator data query request for a database input from an external source, wherein the database maintains a fact table and a dimension table, and the real-time indicator data query includes filtering conditions for the fact table and the dimension table and a required data indicator;

[0120] Query module 1102 is used to determine, from the database, the target fact table and target dimension table on which the demand data indicator depends according to the demand data indicator; filter the target fact table and the target dimension table according to the filtering conditions to obtain multiple pending real-time data indicators; and integrate the multiple pending real-time data indicators to obtain the target real-time data indicator.

[0121] It should be noted that the implementation principle of the aforementioned real-time indicator data query device 110 for databases can refer to the implementation principle of the aforementioned real-time indicator data query method for databases, which will not be repeated here. It should be understood that the division of the various modules of the above device is only a division of logical functions. In actual implementation, they can be fully or partially integrated into a physical entity, or physically separated. And these modules can all be implemented in the form of software called by processing elements; they can also be implemented in the form of hardware; some modules can also be implemented in the form of software called by processing elements, and some modules can be implemented in the form of hardware. For example, the real-time indicator data query device 110 for databases can be a separately established processing element, or it can be integrated in a chip of the above device. In addition, it can also be stored in the memory of the above device in the form of program code, and called and executed by a processing element of the above device. The function of the real-time indicator data query device 110 for databases. The implementation of other modules is similar. In addition, all or part of these modules can be integrated together, or they can be implemented independently. The processing element described here can be an integrated circuit with signal processing capabilities. In the implementation process, each step of the above method or each module above can be completed by an integrated logic circuit of hardware in a processor element or an instruction in software form.

[0122] For example, the above modules may be one or more integrated circuits configured to implement the above methods, such as one or more application specific integrated circuits (ASIC), or one or more microprocessors (digital signal processors, DSP), or one or more field programmable gate arrays (FPGA), etc. For another example, when a module above is implemented in the form of a processing element scheduling program code, the processing element may be a general-purpose processor, such as a central processing unit (CPU) or other processor that can call program code. For another example, these modules may be integrated together and implemented in the form of a system-on-a-chip (SOC).

[0123] The embodiment of the present invention provides a computer device 100, which includes a processor and a non-volatile memory storing computer instructions. When the computer instructions are executed by the processor, the computer device 100 executes the aforementioned real-time indicator data query device 110 for a database. Figure 3 As shown, Figure 3 The computer device 100 provided in the embodiment of the present invention is a structural block diagram. The computer device 100 includes a real-time index data query device 110 for a database, a memory 111, a processor 112 and a communication unit 113.

[0124] To achieve data transmission or interaction, the memory 111, the processor 112 and the communication unit 113 are electrically connected to each other directly or indirectly. For example, these components can be electrically connected to each other through one or more communication buses or signal lines. The real-time indicator data query device 110 for a database includes at least one software function module that can be stored in the memory 111 in the form of software or firmware or solidified in the operating system (OS) of the computer device 100. The processor 112 is used to execute the real-time indicator data query device 110 for a database stored in the memory 111, such as the software function modules and computer programs included in the real-time indicator data query device 110 for a database.

[0125] An embodiment of the present invention provides a readable storage medium, which includes a computer program. When the computer program is running, the computer device where the readable storage medium is located is controlled to execute the aforementioned real-time indicator data query method for a database.

[0126] For illustrative purposes, the foregoing description is made with reference to specific embodiments. However, the above illustrative discussion is not intended to be exhaustive or to limit the present disclosure to the precise form disclosed. Numerous modifications and variations are possible in accordance with the above teachings. These embodiments are selected and described in order to best illustrate the principles of the present disclosure and its practical application, so that those skilled in the art can best utilize the present disclosure and utilize various embodiments with different modifications to suit the intended specific application.

Claims

1. A real-time index data query method for a database, characterized in that: include: Receiving an externally input real-time indicator data query request for a database, wherein the database maintains a fact table and a dimension table, and the real-time indicator data query includes filtering conditions for the fact table and the dimension table and a required data indicator; According to the demand data indicator, determining from the database a target fact table and a target dimension table on which the demand data indicator depends; According to the filtering condition, the target fact table and the target dimension table are filtered to obtain a plurality of pending real-time data indicators; Integrate the multiple pending real-time data indicators to obtain a target real-time data indicator; The target fact table and the target dimension table are filtered according to the filtering condition to obtain a plurality of pending real-time data indicators, including: Obtaining the filter field of the filter condition; Extracting associated information from the target fact table and the target dimension table according to the filter field; Filtering according to the associated information, obtaining the plurality of pending real-time data indicators from the target fact table and the target dimension table; The step of integrating the plurality of pending real-time data indicators to obtain a target real-time data indicator includes: Determining whether the types of the multiple pending real-time data indicators are all common indicators; If yes, directly integrating the multiple pending real-time data indicators to obtain the target real-time data indicator; If not, based on the corresponding type of each of the pending real-time data indicators, integration is performed according to preset rules to obtain the target real-time data indicator; The step of directly integrating the plurality of pending real-time data indicators to obtain the target real-time data indicator includes: Based on the dimension attribute information corresponding to each pending real-time data indicator, the association relationship between each pending real-time data indicator is determined through the metadata database; According to the association relationship, the target real-time data indicator is integrated to obtain; The corresponding type of each of the pending real-time data indicators is integrated according to a preset rule to obtain the target real-time data indicator, including: In the case where the type of the current real-time data indicator is a derived indicator or a summary indicator, obtaining a common indicator associated with the current real-time data indicator, wherein the current real-time data indicator is any real-time data indicator among the multiple pending real-time data indicators, the derived indicator is derived from the common indicator, and the summary indicator is obtained by summarizing the common indicators; According to the common indicator associated with the current real-time data indicator, real-time data indicators in the same dimension as the current real-time data indicator are acquired and integrated to obtain the target real-time data indicator; The step of integrating the plurality of pending real-time data indicators to obtain a target real-time data indicator includes: In the case where the dimensions of the multiple pending real-time data indicators are the same, extracting the measurement content of each of the pending real-time data indicators; Integrate the SQL statements corresponding to each of the measurement contents to obtain a target SQL statement; Execute the target SQL statement to obtain the target real-time data indicator.

2. A real-time index data query device for a database, characterized in that: include: An acquisition module is used to receive an externally input real-time indicator data query request for a database, wherein the database maintains a fact table and a dimension table, and the real-time indicator data query includes filtering conditions for the fact table and the dimension table and a required data indicator; A query module is used to determine, from the database, a target fact table and a target dimension table on which the demand data indicator depends according to the demand data indicator; filter the target fact table and the target dimension table according to the filtering condition to obtain a plurality of pending real-time data indicators; integrate the plurality of pending real-time data indicators to obtain a target real-time data indicator ； The query module is specifically used for: Acquire a filter field of the filter condition; extract associated information from the target fact table and the target dimension table according to the filter field; perform filtering according to the associated information, and obtain the plurality of pending real-time data indicators from the target fact table and the target dimension table; The query module is specifically used for: Determining whether the types of the multiple pending real-time data indicators are all common indicators; If yes, directly integrate the multiple pending real-time data indicators to obtain the target real-time data indicator; if no, integrate the multiple pending real-time data indicators according to the preset rules based on the corresponding type of each pending real-time data indicator to obtain the target real-time data indicator; The query module is also specifically used for: Based on the dimension attribute information corresponding to each pending real-time data indicator, determining the association relationship between each pending real-time data indicator through a metadata database; and integrating and obtaining the target real-time data indicator according to the association relationship; The query module is also specifically used for: In the case where the type of the current real-time data indicator is a derived indicator or a summary indicator, obtaining a common indicator associated with the current real-time data indicator, wherein the current real-time data indicator is any real-time data indicator among the multiple pending real-time data indicators, the derived indicator is derived from the common indicator, and the summary indicator is obtained by summarizing the common indicators; According to the common indicator associated with the current real-time data indicator, real-time data indicators in the same dimension as the current real-time data indicator are acquired and integrated to obtain the target real-time data indicator; The query module is also specifically used for: In the case where the type of the current real-time data indicator is an SQL indicator, analyzing the SQL syntax tree corresponding to the SQL indicator to obtain a target field, and the current real-time data indicator is any real-time data indicator among the multiple pending real-time data indicators; Determine, from metadata according to the target field, a real-time data indicator in the same dimension as the current real-time data indicator and integrate them to obtain the target real-time data indicator; The query module is specifically used for: When the dimensions of the multiple pending real-time data indicators are the same, the measurement content of each of the pending real-time data indicators is extracted; the SQL statements corresponding to each of the measurement contents are integrated to obtain the target SQL statement; and the target SQL statement is executed to obtain the target real-time data indicator.

3. A computer device, characterized in that: The computer device includes a processor and a non-volatile memory storing computer instructions. When the computer instructions are executed by the processor, the computer device executes the real-time indicator data query method for a database described in claim 1.

4. A readable storage medium, characterized in that: The readable storage medium includes a computer program, and when the computer program is executed, the computer device where the readable storage medium is located is controlled to execute the real-time indicator data query method for a database as described in claim 1.

Images