Database query method, terminal equipment and storage medium

Abstract

The invention is suitable for the technical field of information query, and provides a database query method, terminal equipment and a storage medium, the method comprises the following steps: obtaining a query index needing to be queried by a user in a user query request; generating an aggregation expression of the query indexes according to the aggregation function of the query indexes; if the query indexes capable of being merged and queried exist, combining the aggregation expressions of the query indexes capable of being merged and queried to obtain a combined expression; and generating a query script of the query index according to the combined expression. When each query index is processed, if the data source structures corresponding to the plurality of query indexes are the same, the data sources needing to be queried by the plurality of query indexes are the same, and the query indexes with the same data source structures can be combined into one query script; and the query results of the plurality of query indexes can be obtained only by traversing the data source once, so that the query efficiency is improved.

Description

Technical Field

[0001] This application belongs to the field of information retrieval technology, and in particular relates to a database query method, terminal device and storage medium. Background Technology

[0002] A database is a computer software system that stores and manages data according to a data source structure. Databases have a large storage capacity, capable of holding millions, tens of millions, or even hundreds of millions of data entries.

[0003] Because databases contain large amounts of data, query scripts need to be written based on query metrics to perform queries. The role of the query script is to "communicate" with the database, instructing it to perform operations with specific commands. Its core purpose is to "retrieve," "manage," and "resource" data from the database. Therefore, writing appropriate query scripts to improve data query efficiency is crucial. Summary of the Invention

[0004] This application provides a database query method, terminal device, and storage medium, which can be used to write suitable query scripts and improve data query efficiency.

[0005] In a first aspect, embodiments of this application provide a database query method, including: Retrieve the query metrics that the user needs to query from the user's query request; Based on the data source structure corresponding to the query indicator and the aggregation function of the query indicator, an aggregation expression for the query indicator is generated, wherein the data source structure is used to characterize the data source features of the query data required by the query indicator; If a first indicator exists among the query indicators, the aggregation expressions of the first indicator with the same data source structure are combined to obtain a combined expression, wherein the first indicator is a query indicator with the same data source structure as other query indicators. Based on the combined expression, a first query script with the same data source structure for the first indicator is generated; The query results are obtained by querying the query indicators in the database according to the first query script.

[0006] In this application, when querying the query indicators required by the user, it is necessary to write a query script for each query indicator. Currently, for each query indicator, one query script corresponds to one query indicator. When querying each query indicator, it is necessary to traverse all the data in the database according to the query script. If two query indicators need to query the same data source, the same data source needs to be traversed twice to obtain the query results of the two query indicators, resulting in low query efficiency.

[0007] When processing various query metrics, if multiple query metrics have the same data source structure, it means that multiple query metrics need to query the same data source. In this case, query metrics with the same data source structure can be merged to generate a single query script. This allows the database to obtain the query results of multiple query metrics by executing only one query script. In other words, it only needs to traverse the data source once to obtain the query results of multiple query metrics, thereby improving query efficiency.

[0008] In one possible implementation of the first aspect, if a first indicator exists among the query indicators, the aggregation expressions of the first indicators having the same data source structure are combined to obtain a combined expression, including: If a first indicator exists among the query indicators, and the first indicator is a first type of indicator, the aggregation expressions of the first indicators with the same data source structure are combined to obtain a combined expression, wherein the first type of indicator is a query indicator that can be determined without relying on other query indicators.

[0009] In this application, on the one hand, the indicators that can be merged are merged for querying, and on the other hand, only the first type of indicators that do not depend on other query indicators are queried. This reduces the number of data scans and calculations, significantly reducing the pressure on the underlying database. In addition, it can also reduce the number of tasks that the computing engine needs to manage and schedule, thus optimizing resource utilization.

[0010] In one possible implementation of the first aspect, after generating the aggregate expression of the query indicator based on the data source structure corresponding to the query indicator and the aggregate function of the query indicator, the method further includes: If a second indicator exists among the query indicators, and the second indicator is a first type of indicator, a second query script for the second indicator is generated based on the aggregation expression of the second indicator. Here, the second indicator is a query indicator that does not have the same data source structure as other query indicators, and the first type of indicator is a query indicator that can be determined without relying on other query indicators.

[0011] In one possible implementation of the first aspect, after generating the aggregate expression of the query indicator based on the data source structure corresponding to the query indicator and the aggregate function of the query indicator, the method further includes: If a third indicator exists among the query indicators, and the third indicator is a second type of indicator, where the second type of indicator is a query indicator that needs to be determined by other query indicators, then no query script for the third indicator will be generated. After obtaining the first and second indicators from the database query, the third indicator is calculated based on the first and / or the second indicators.

[0012] In this application, the third indicator is a derived indicator that needs to be calculated based on other query indicators. When a query is initiated to the database, no query script is generated for this type of derived indicator. The database does not need to process this type of derived indicator, and the database does not need to process more complex query indicators, thus ensuring the speed of database queries.

[0013] In one possible implementation of the first aspect, the method further includes: Based on the data query conditions in the user query request, the dimensions of the data tables in the database that need to be queried are combined to obtain various combined dimensions; Obtain the attribute features of each of the combined dimensions, wherein the attribute features include the historical zero value ratio and / or the estimated number of rows of query data, and the historical zero value ratio is used to characterize the proportion of zero values ​​in the historical data of the combined dimension; If a target combined dimension exists in the combined dimensions, a skip instruction program for the target combined dimension is generated. The target combined dimension is the combined dimension whose historical zero value ratio is greater than a first preset value and / or whose estimated number of query data rows is less than a second preset value. The skip instruction program is used to instruct the database not to access the data in the target combined dimension. Add the skip instruction program to the first query script to obtain the optimized first query script; The skip instruction program is added to the second query script to obtain the optimized second query script.

[0014] In this application, by filtering the combined dimensions, we obtain the combined dimensions that do not contribute much to the query results, and then remove these combined dimensions so that the database does not query these combined dimensions, thereby reducing the amount of data queried by the database and improving the data query speed.

[0015] In one possible implementation of the first aspect, the method further includes: Obtain the configuration file of the database, wherein the configuration file stores the distribution of data and indexing rules in the database; Based on the configuration file and the data query conditions in the user query request, the optimal query path for the query metric is generated; The optimized target query script is obtained by optimizing the data source information in the target query script based on the optimal query path. The target query script is the query script corresponding to the query indicators in the first query script and the second query script. In this application, the optimal query path for data query is found to obtain the optimal information of the data source to be queried. The information of the data source in the target query script is updated according to the optimal information of the data source, so that the target query script can be more accurate, so that the database query can obtain more accurate query results based on the more accurate target query script.

[0016] In one possible implementation of the first aspect, the method further includes: Retrieve the sorting rules of the database indexes; The sorting rules of the index are added to the third query script, wherein the third query script is the query script that needs to be sent to the database from the first query script and the second query script.

[0017] In one possible implementation of the first aspect, the method further includes: Based on the dimensional information of the database and the query metrics to be queried, generate the data index range of the database; The data index range is added to the fourth query script, wherein the fourth query script is the query script that needs to be sent to the database from the first query script and the second query script.

[0018] Secondly, embodiments of this application provide a database query apparatus, including: The indicator determination module is used to obtain the query indicators that the user needs to query in the user's query request; An aggregation expression generation module is used to generate an aggregation expression for the query indicator based on the data source structure corresponding to the query indicator and the aggregation function of the query indicator, wherein the data source structure is used to characterize the data source features of the query data required by the query indicator. The indicator merging module is used to combine the aggregation expressions of the first indicator with the same data source structure to obtain a combined expression if the first indicator exists among the query indicators. The first indicator is a query indicator with the same data source structure as other query indicators. The script generation module is used to generate a first query script for the first indicator with the same data source structure based on the combined expression. The result generation module is used to query the query indicators in the database according to the first query script and obtain the query results.

[0019] Thirdly, embodiments of this application provide a terminal device, including: a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to implement the database query method described in any one of the first aspects above.

[0020] Fourthly, embodiments of this application provide a computer-readable storage medium storing a computer program that, when executed by a processor, implements the database query method described in any one of the first aspects above.

[0021] Fifthly, embodiments of this application provide a computer program product that, when run on a terminal device, causes the terminal device to execute the database query method described in any one of the first aspects. Attached Figure Description

[0022] To more clearly illustrate the technical solutions in the embodiments of this application, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0023] Figure 1 This is a schematic flowchart of a database query method provided in an embodiment of this application; Figure 2 This is a flowchart illustrating a method for optimizing a query script based on a data query path, according to an embodiment of this application. Figure 3 This is a flowchart illustrating a method for optimizing a query script by filtering combined dimensions, according to an embodiment of this application. Figure 4 This is a schematic diagram of the structure of a database query device provided in an embodiment of this application; Figure 5 This is a schematic diagram of the structure of a terminal device provided in an embodiment of this application. Detailed Implementation

[0024] It should be understood that, when used in this application specification and the appended claims, the term "comprising" indicates the presence of the described features, integrals, steps, operations, elements and / or components, but does not exclude the presence or addition of one or more other features, integrals, steps, operations, elements, components and / or a collection thereof.

[0025] It should also be understood that the term “and / or” as used in this application specification and the appended claims means any combination of one or more of the associated listed items and all possible combinations, and includes such combinations.

[0026] Furthermore, in the description of this application and the appended claims, the terms "first," "second," "third," etc., are used only to distinguish descriptions and should not be construed as indicating or implying relative importance.

[0027] In daily work, data storage and data retrieval are involved in various fields.

[0028] For example, in financial data analysis scenarios, multi-field aggregation is a common requirement, such as combining and summarizing multiple indicators like revenue, cost, and profit by department, region, time, and other dimensions. Because the amount of data to be analyzed is relatively large and multi-dimensional, columnar storage databases are often used to handle such business scenarios for ease of storage and analysis.

[0029] After storing the data in the database, if you need to query certain indicators, you can iterate through the data in the database to obtain the query results for the indicators.

[0030] When performing indicator queries, existing technologies suffer from redundant data scanning and calculation. For different query indicators, the same data source needs to be scanned repeatedly to obtain query results for multiple indicators. For example, a query script A is written for indicator A, and query script A is used to traverse data table 1 to obtain indicator A; a query script B is written for indicator B, and query script B is used to traverse data table 1 to obtain indicator B. The same data table 1 is traversed twice, resulting in redundant data scanning.

[0031] Furthermore, performing aggregation calculations on meaningless combinations of dimensions leads to a waste of resources. For example, if the sales revenue of product category B in area A is 0, this combination of dimensions is meaningless in calculating the total sales revenue, and aggregating the data from this combination results in a waste of resources.

[0032] Based on the above problems, this application proposes a database query method. By merging the aggregate expressions of query indicators with the same data source characteristics, a query script is generated. This query script is then used to query multiple query indicators, allowing the database to obtain the query results of multiple query indicators by traversing the data source only once. This reduces redundant data scanning and calculation and improves the efficiency of indicator query.

[0033] In addition, this application also prunes the dimension combinations, removing meaningless dimension combinations so that meaningless dimension combinations are not needed when querying indicators, thereby reducing resource waste and improving query efficiency.

[0034] The following combination Figure 1 The database query method of this application embodiment will be described in detail. This method can be applied to a database in a server.

[0035] Figure 1 A schematic flowchart of the database query method provided in this application is shown, with reference to... Figure 1 The method is described in detail below: S101, Obtain the query metrics that the user needs to query in the user's query request.

[0036] In this embodiment, user query requests are received through a query interface.

[0037] The system extracts key information from user query requests and breaks down the metrics to be queried, obtaining the query metrics, data query conditions, and data source identifiers that the user needs to query.

[0038] For example, a user query request might be: Summarize the sales revenue, return amount, net sales revenue, cost, gross profit, and gross profit margin from the regional sales profit contribution table for August 2025, categorized by region and type. Analyzing the user query request and breaking down the query indicators yields the indicator information shown in the table below.

[0039]

[0040] Among them, the atomic type index is the first type of index, which is a query index that does not need to rely on other query indexes for calculation.

[0041] The calculated metrics are of the second type, meaning they are query metrics that require other query metrics to be calculated.

[0042] In MySQL, `sum(o.amount)` calculates the sum of all values ​​in the `amount` field of the `orders` table in a MySQL database. `Amount` is the name of a field representing a monetary amount.

[0043] In MySQL, `sum(r.refund)` calculates the sum of all values ​​in the `refund` field of the `r` table in a MySQL database.

[0044] Sales-refund means sales revenue minus the amount of returns.

[0045] Oracle:sum(c.cost) calculates the sum of all values ​​in the cost field of table c in an Oracle database.

[0046] net_sales – cost represents net sales minus costs.

[0047] profit / nullif(net_sales,0) represents the ratio of gross profit to net sales.

[0048] Based on the user's query request, the final query metrics include: sales revenue, return amount, net sales revenue, cost, gross profit, and gross profit margin. The data query criteria are partitioning and categorization summaries. The data source is the regional sales profit contribution table for August 2025.

[0049] In practical use, to better understand the dependencies between various query metrics, a directed acyclic graph (DAG) can be generated based on the metric information. The nodes in the DAG are atomic or computed metrics, and the edges in the DAG represent the dependencies between query metrics.

[0050] S102, Based on the data source structure corresponding to the query indicator and the aggregation function of the query indicator, generate the aggregation expression of the query indicator, wherein the data source structure is used to characterize the data source features of the query data required by the query indicator.

[0051] In this embodiment, the data source structure corresponding to the query indicator is determined based on the distribution of data in the database. The data source structure may include the database name, data table name, and dimensions. The data distribution includes the data storage location. For example, to calculate the total sales of region A over a period of time, it is necessary to query the monthly or daily sales of region A, and the monthly or daily sales of region A are stored in data table 5 of the MySQL database.

[0052] An aggregate expression is a coded expression that evaluates a set of data and returns a single, summary result. Aggregate expressions typically consist of aggregate functions, table names, and conditional expressions.

[0053] S103, if a first indicator exists among the query indicators, the aggregation expressions of the first indicator with the same data source structure are combined to obtain a combined expression, wherein the first indicator is a query indicator with the same data source structure as other query indicators.

[0054] In this embodiment, if query indicator A and query indicator B have the same data source structure, then query indicator A and query indicator B are both recorded as the first indicator, and the aggregate expression of query indicator A and the aggregate expression of query indicator B are combined to obtain a combined expression that includes the aggregate expression of query indicator A and the aggregate expression of query indicator B.

[0055] In another approach, to save on database computation, only the first type of indicators are calculated in the database layer, while the second type of indicators can be excluded from the database layer calculation, thereby improving computational efficiency.

[0056] Specifically, if the query metrics include a first metric and the first metric is a first type of metric, the aggregation expressions of the first metric with the same data source structure are combined to obtain a combined expression, wherein the first type of metric is a query metric that can be determined without relying on other query metrics.

[0057] S104, Based on the combined expression, generate a first query script for the first indicator that has the same data source structure.

[0058] In this embodiment, a query script is written based on the data source (table name, table structure, etc.) of the first indicator, data query conditions (statistics by region, category, etc.), and combined expressions. Specifically, the data source (table name, table structure, etc.) of the first indicator, data query conditions (statistics by region, category, etc.), and combined expressions are input into the program to write the model, thus obtaining the first query script.

[0059] As an example, let's look at the sales and return amounts for each region and product category in August 2025. The aggregate expressions for sales and return amounts can be combined, resulting in the following first query script: SELECT region, category (grouping dimension, indicating that the results will be summarized by "region" and "category"); SUM(o.amount) AS sales (Calculates the total amount of all orders in each group and names it as sales). SUM(r.refund)AS refund (calculates the total refund amount for all refund records in each group) and names it as the refund amount; FROM order_tab o LEFT JOIN refund_tab r (The main table is the order table, and the refund table is joined to the order table). ON o.order_id=r.order_idWHERE o.dt BETWEEN '2025-08-01' AND '2025-08-31'GROUPBY region, category. (Only orders created between August 1, 2025 and August 31, 2025 will be processed).

[0060] S105, query the query indicators in the database according to the first query script to obtain the query results.

[0061] In this application, when processing various query indicators, if multiple query indicators have the same data source structure, it means that multiple query indicators need to query the same data source. In this case, query indicators with the same data source structure can be merged into a single query script, so that the database only needs to execute one query script to obtain the query results of multiple query indicators. That is, it only needs to traverse the data source once to obtain the query results of multiple query indicators, thereby improving query efficiency.

[0062] The above describes how to merge query metrics that can be merged. For query metrics that cannot be merged, a separate query script can be generated for individual querying.

[0063] Specifically, after step S102 above, the method of this application may further include: If a second indicator exists among the query indicators, a second query script for the second indicator is generated based on the aggregation expression of the second indicator. The second indicator is a query indicator that does not have the same data source structure as other query indicators, and the first type of indicator is a query indicator that can be determined without relying on other query indicators.

[0064] Furthermore, if a second indicator exists among the query indicators, and the second indicator is a first type of indicator, a second query script for the second indicator is generated based on the aggregation expression of the second indicator. Here, the second indicator is a query indicator that does not have the same data source structure as other query indicators, and the first type of indicator is a query indicator that can be determined without relying on other query indicators.

[0065] As an example, statistics were compiled in August 2025 to determine the cost for each region and each product category.

[0066] SELECT region, category; SUM(cost) AS cost; (Calculate the cost of each group and name it cost); FROM cost_tab WHERE acc_month='2025-08'GROUPBY region, category. (Data source: cost details table, accounting records as of August 2025).

[0067] For query metrics that require other query metrics to be calculated, i.e., third metrics, a query script for that metric may not be generated. This allows the database layer to avoid processing the third metric, and the third metric can be calculated directly after the other query metrics are determined.

[0068] Specifically, after step S102 above, the method of this application may further include: If a third indicator exists among the query indicators, and the third indicator is a second type of indicator, where the second type of indicator is a query indicator that needs to be determined by other query indicators, then no query script for the third indicator is generated; after obtaining the first indicator and the second indicator from the database query, the third indicator is calculated based on the first indicator and / or the second indicator.

[0069] In this embodiment, both the first query script and the second query script can be sent to the database layer for processing. After obtaining the query results of the first and second indicators, the database layer returns the query results of the first and second indicators to the upper layer (e.g., the application layer). Upon receiving the query results of the first and second indicators, the upper layer calculates a third indicator based on the first and / or second indicators to reduce the data processing load on the database layer and improve the speed of indicator queries. For example, the third indicator could be net sales revenue, gross profit, or gross profit margin, where net sales revenue is calculated based on sales revenue and return amount; gross profit is calculated based on net sales revenue and cost; and gross profit margin is calculated based on gross profit and net sales revenue.

[0070] After generating the first query script for the first indicator and the second query script for the second indicator based on the aggregation expression, the first and second query scripts can be optimized to improve their accuracy. This allows for a more accurate and faster retrieval of the first indicator based on the more accurate first query script, and a more accurate and faster retrieval of the second indicator based on the more accurate second query script. Specifically, this application can optimize the query scripts (first and second query scripts) based on the data query path, optimize the index of the query scripts, and optimize the query scripts through filtering dimension combinations.

[0071] Below, as Figure 2 As shown, the process of optimizing the query script based on the data query path will be introduced first.

[0072] S201, Obtain the configuration file of the database, wherein the configuration file stores the distribution of data and indexing rules in the database.

[0073] In this embodiment, the data distribution describes how the data is segmented, placed, and organized at the physical storage level of the database; it determines where the system finds and stores the data.

[0074] For example, the order table is stored by region: "East":{"min_dt": "2025-01-01", "max_dt": "2025-08-31", "rows":800M}; (The East area stores 800 million rows of data from January 1, 2025 to August 31, 2025).

[0075] "North": {"min_dt": "2025-01-01", "max_dt": "2025-08-31", "rows":600M}. (The North area stores 600 million rows of data from January 1, 2025 to August 31, 2025).

[0076] In this embodiment, indexing rules define how to create and use indexes. An index is like a table of contents in a book; it doesn't contain the main text, but it allows you to quickly find the page number of specific content, avoiding having to flip through the entire book page by page (a full table scan). For example, the indexing rule could be monthly indexing.

[0077] S202, Based on the configuration file and the data query conditions in the user query request, generate the optimal query path for the query indicator.

[0078] In this embodiment, the data query conditions are set by the user, specifying what data the user wants to query and what content the user wants to query. For example, the data query conditions could be: to partition and summarize the sales amount, return amount, and gross profit of the regional sales table for August.

[0079] In this embodiment, the system determines whether to perform a global scan of the data table or a skip scan using the index, based on the configuration file and data query conditions; and determines the parallelism of the data scan based on the data storage volume of each region.

[0080] For example, if a user needs to aggregate data for August, and this data is stored in the East and North partitions while other partitions do not contain August data, then it's determined that using partition pruning to query the August data will be faster. Therefore, partition pruning can be used to query the August data. If the table to be queried has an index, then using an index-skipped scan to scan the table is faster than a full table scan. Therefore, an index-skipped scan can be used to scan the data in the table.

[0081] In addition, determining the optimal query path can also help determine the more accurate storage location of the data source to be scanned in the database.

[0082] S203, optimize the data source information in the target query script according to the optimal query path to obtain the optimized target query script, wherein the target query script is the query script corresponding to the query indicators in the first query script and the second query script. In this embodiment, the data source information may include the storage location of the data source to be scanned, the query method of the data source, etc.

[0083] For example, if the sales amount requires querying the order table, the order table in the first query script generated based on the aggregate expression is located in area A. After determining the optimal query path, and confirming that the order table is stored in table 1 in area A, the position of the order table in the first query script can be updated to table 1 in area A.

[0084] The above describes the process of optimizing the query script based on the optimal query path. The following describes the process of optimizing the query script using indexes.

[0085] Method 1: Obtain the sorting rules of the database index; add the sorting rules of the index to the third query script, wherein the third query script is the query script that needs to be sent to the database in the first query script and the second query script.

[0086] Method 2: Generate a data index range for the database based on the dimension information of the database and the query indicators to be queried; add the data index range to the fourth query script, wherein the fourth query script is the query script that needs to be sent to the database from the first query script and the second query script.

[0087] For example: For a MySQL database: Obtain the collation rules of the MySQL database index; Add the collation rules of the index to the third query script, wherein the third query script is the query script that needs to be sent to the MySQL database in the first query script and the second query script.

[0088] In this embodiment, the index sorting rules are defined by the MySQL database itself, following its own index order to facilitate fast data retrieval. For example, the index is first sorted by ID, and then within the same ID, it is sorted by job title. Adding the database index sorting rules to the query script improves the index's orderliness, allowing for faster retrieval of the required data based on the index sorting rules when searching for data using the query script.

[0089] For Oracle databases: Based on the dimension information of the Oracle database and the query metrics to be queried, generate the data index range of the Oracle database; add the data index range to the fourth query script, wherein the fourth query script is the query script that needs to be sent to the Oracle database in the first query script and the second query script.

[0090] In this embodiment, the dimension information of the Oracle database represents the dimensions of the data stored in the Oracle database. For example, personnel information is stored according to gender and age, with gender and age being the dimensions of the data.

[0091] The data index range represents the range of data sources to be retrieved. Setting the data retrieval range in the query script forces the Oracle database to execute a specific plan, preventing the selection of an undesirable index when querying data, which could lead to inaccurate statistics.

[0092] For example, if you need to query cost data for August 2025, set the data index range to data table 1, which stores cost data.

[0093] SELECT / *+ INDEX(c COST_TAB_IDX1) * / (forces the query to use the index named COST_TAB_IDX1); region, category, SUM(cost) AS cost FROM cost_tab_c WHEREacc_month='2025-08'GROUP BY region, category ORDER BY region, category.

[0094] Below, as Figure 3 As shown, we will continue to explain the method of optimizing the query script by filtering through combination of dimensions.

[0095] S301, based on the data query conditions in the user query request, combine the dimensions of the data tables in the database that need to be queried to obtain various combined dimensions.

[0096] In this embodiment, the dimensions corresponding to the data query conditions are combined to obtain combined dimensions.

[0097] For example, if you need to query the sales volume and return amount of each type of product in each region, you need to combine the region and product category to get combined dimensions such as (East Region, Digital), (South Region, Books) and (East Region, Books).

[0098] S302, obtain the attribute features of each of the combined dimensions, wherein the attribute features include the historical zero value ratio and / or the estimated number of query data rows, and the historical zero value ratio is used to characterize the proportion of zero values ​​in the historical data of the combined dimension.

[0099] In this embodiment, a correspondence table between dimensions and attributes can be generated for the attribute features corresponding to each combined dimension, so as to find the attribute features of each combined dimension more clearly.

[0100] As an example, the table below shows the correspondence between combined dimensions and attribute features.

[0101]

[0102] The higher the proportion of historical zero values, the more zero values ​​there are in that combined dimension.

[0103] S303, if a target combined dimension exists in the combined dimensions, a skip instruction program for the target combined dimension is generated, wherein the target combined dimension is the combined dimension in which the proportion of historical zero values ​​is greater than a first preset value and / or the estimated number of query data rows is less than a second preset value, and the skip instruction program is used to instruct the database not to access the data in the target combined dimension.

[0104] In this embodiment, both the first preset value and the second preset value can be set as needed. For example, the first preset value can be set to 95%, and the second preset value can be set to 50K, etc.

[0105] If the proportion of historical zero values ​​is greater than the first preset value, it indicates that there are a relatively large number of zero values ​​in this combined dimension. The data in this combined data has a relatively small impact on the final result. Therefore, it is not necessary to query the data in this combined dimension.

[0106] If the estimated number of rows to be queried is less than the second preset value, it means that there is relatively little data in this combined dimension, and the data in this combined dimension has little impact on the final result. Therefore, it is not necessary to query the data in this combined dimension.

[0107] S304, add the skip instruction program to the first query script to obtain the optimized first query script; add the skip instruction program to the second query script to obtain the optimized second query script.

[0108] It should be noted that the database layer performs data queries according to the first query script and the second query script to obtain the query results corresponding to the query indicators.

[0109] For example, the query results for the financial field can be shown in the table below.

[0110]

[0111] It should be understood that the sequence number of each step in the above embodiments does not imply the order of execution. The execution order of each process should be determined by its function and internal logic, and should not constitute any limitation on the implementation process of the embodiments of this application.

[0112] Corresponding to the database query method described in the above embodiments, Figure 4 A structural block diagram of a database query device provided in an embodiment of this application is shown. For ease of explanation, only the parts related to the embodiment of this application are shown.

[0113] Reference Figure 4The device 400 may include: an index determination module 410, an aggregation expression generation module 420, an index merging module 430, a script generation module 440, and a result generation module 450.

[0114] Among them, the indicator determination module 410 is used to obtain the query indicators that the user needs to query in the user query request; The aggregation expression generation module 420 is used to generate an aggregation expression for the query indicator based on the data source structure corresponding to the query indicator and the aggregation function of the query indicator, wherein the data source structure is used to characterize the data source features of the query data required by the query indicator. The indicator merging module 430 is used to combine the aggregation expressions of the first indicator with the same data source structure to obtain a combined expression if there is a first indicator among the query indicators. The first indicator is a query indicator with the same data source structure as other query indicators. The script generation module 440 is used to generate a first query script for the first indicator with the same data source structure based on the combined expression. The result generation module 450 is used to query the query indicators in the database according to the first query script and obtain the query results.

[0115] In one possible implementation, the indicator merging module 430 can specifically be used for: If a first indicator exists among the query indicators, and the first indicator is a first type of indicator, the aggregation expressions of the first indicators with the same data source structure are combined to obtain a combined expression, wherein the first type of indicator is a query indicator that can be determined without relying on other query indicators.

[0116] In one possible implementation, the script generation module 440 can specifically be used for: If a second indicator exists among the query indicators, and the second indicator is a first type of indicator, a second query script for the second indicator is generated based on the aggregation expression of the second indicator. Here, the second indicator is a query indicator that does not have the same data source structure as other query indicators, and the first type of indicator is a query indicator that can be determined without relying on other query indicators.

[0117] In one possible implementation, the device 400 may further include: The indicator calculation module is used to calculate the third indicator if there is a third indicator among the query indicators and the third indicator is a second type of indicator, where the second type of indicator is a query indicator that needs to be determined by other query indicators; after obtaining the first indicator and the second indicator from the database query, the module calculates the third indicator based on the first indicator and / or the second indicator.

[0118] In one possible implementation, the device 400 may further include: The dimension combination module is used to combine the dimensions of the data tables in the database to be queried according to the data query conditions in the user query request, so as to obtain various combined dimensions. The attribute feature extraction module is used to obtain the attribute features of each of the combined dimensions, wherein the attribute features include the historical zero value ratio and / or the estimated number of query data rows, and the historical zero value ratio is used to characterize the proportion of zero values ​​in the historical data of the combined dimension; The program generation module is used to generate a skip instruction program for the target combined dimension if a target combined dimension exists in the combined dimensions. The target combined dimension is a combined dimension whose historical zero value ratio is greater than a first preset value and / or whose estimated number of query data rows is less than a second preset value. The skip instruction program is used to instruct the database not to access the data in the target combined dimension. The first script update module is used to add the skip instruction program to the first query script to obtain an optimized first query script; The second script update module is used to add the skip instruction program to the second query script to obtain an optimized second query script.

[0119] In one possible implementation, the device 400 may further include: The file acquisition module is used to acquire the configuration file of the database, wherein the configuration file stores the distribution and indexing rules of the data in the database; The path determination module is used to generate the optimal query path for the query metric based on the configuration file and the data query conditions in the user query request. The script update module is used to optimize the data source information in the target query script according to the optimal query path to obtain the optimized target query script, wherein the target query script is the query script corresponding to the query indicators in the first query script and the second query script. In one possible implementation, the device 400 may further include: The rule retrieval module is used to obtain the sorting rules of the indexes in the MySQL database; The first script optimization module is used to add the sorting rules of the index to the third query script, wherein the third query script is the query script that needs to be sent to the MySQL database in the first query script and the second query script.

[0120] In one possible implementation, the device 400 may further include: The index range determination module is used to generate the data index range of the Oracle database based on the dimension information of the Oracle database and the query metrics to be queried; The second script optimization module is used to add the data index range to the fourth query script, wherein the fourth query script is the query script that needs to be sent to the Oracle database in the first query script and the second query script.

[0121] It should be noted that the information interaction and execution process between the above-mentioned devices / units are based on the same concept as the method embodiments of this application. For details on their specific functions and technical effects, please refer to the method embodiments section, and they will not be repeated here.

[0122] Those skilled in the art will clearly understand that, for the sake of convenience and brevity, the above-described division of functional units and modules is merely an example. In practical applications, the above functions can be assigned to different functional units and modules as needed, that is, the internal structure of the device can be divided into different functional units or modules to complete all or part of the functions described above. The functional units and modules in the embodiments can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit. The integrated unit can be implemented in hardware or as a software functional unit. Furthermore, the specific names of the functional units and modules are only for easy differentiation and are not intended to limit the scope of protection of this application. The specific working process of the units and modules in the above system can be referred to the corresponding process in the foregoing method embodiments, and will not be repeated here.

[0123] This application also provides a terminal device, see [link to relevant documentation] Figure 5 The terminal device 500 may include: at least one processor 510, a memory 520, and a computer program stored in the memory 520 and executable on the at least one processor 510. When the processor 510 executes the computer program, it implements the steps in any of the above method embodiments, for example... Figure 1 Steps S101 to S104 in the illustrated embodiment. Alternatively, when the processor 510 executes the computer program, it implements the functions of each module / unit in the above-described device embodiments, for example... Figure 4 The functions of the indicator determination module 410 to the script generation module 440 are shown.

[0124] For example, a computer program may be divided into one or more modules / units, one or more of which are stored in memory 520 and executed by processor 510 to complete this application. The one or more modules / units may be a series of computer program segments capable of performing specific functions, which describe the execution process of the computer program in terminal device 500.

[0125] Those skilled in the art will understand that Figure 5 This is merely an example of a terminal device and does not constitute a limitation on the terminal device. It may include more or fewer components than shown, or combine certain components, or different components, such as input / output devices, network access devices, buses, etc.

[0126] The processor 510 can be a Central Processing Unit (CPU), or other general-purpose processors, digital signal processors (DSPs), application-specific integrated circuits (ASICs), field-programmable gate arrays (FPGAs), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. The general-purpose processor can be a microprocessor or any conventional processor.

[0127] The memory 520 can be an internal storage unit of the terminal device or an external storage device, such as a plug-in hard drive, a smart media card (SMC), a secure digital (SD) card, or a flash card. The memory 520 is used to store the computer program and other programs and data required by the terminal device. The memory 520 can also be used to temporarily store data that has been output or will be output.

[0128] The bus can be an Industry Standard Architecture (ISA) bus, a Peripheral Component Interconnect (PCI) bus, or an Extended Industry Standard Architecture (EISA) bus, etc. Buses can be categorized as address buses, data buses, control buses, etc. For ease of illustration, the buses shown in the accompanying drawings are not limited to a single bus or a single type of bus.

[0129] The database query method provided in this application can be applied to terminal devices such as computers, tablets, laptops, netbooks, and personal digital assistants (PDAs). This application does not impose any restrictions on the specific type of terminal device.

[0130] In the above embodiments, the descriptions of each embodiment have different focuses. For parts that are not described in detail or recorded in a certain embodiment, please refer to the relevant descriptions of other embodiments.

[0131] Those skilled in the art will recognize that the units and algorithm steps of the various examples described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, or a combination of computer software and electronic hardware. Whether these functions are implemented in hardware or software depends on the specific application and design constraints of the technical solution. Those skilled in the art can use different methods to implement the described functions for each specific application, but such implementation should not be considered beyond the scope of this application.

[0132] In the embodiments provided in this application, it should be understood that the disclosed terminal devices, apparatuses, and methods can be implemented in other ways. For example, the terminal device embodiments described above are merely illustrative. For instance, the division of modules or units is only a logical functional division, and in actual implementation, there may be other division methods. For example, multiple units or components may be combined or integrated into another system, or some features may be ignored or not executed. Furthermore, the coupling or direct coupling or communication connection shown or discussed may be an indirect coupling or communication connection through some interfaces, apparatuses, or units, and may be electrical, mechanical, or other forms.

[0133] The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the units can be selected to achieve the purpose of this embodiment according to actual needs.

[0134] Furthermore, the functional units in the various embodiments of this application can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit. The integrated unit can be implemented in hardware or as a software functional unit.

[0135] If the integrated unit is implemented as a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, all or part of the processes in the methods of the above embodiments can also be implemented by a computer program instructing related hardware. The computer program can be stored in a computer-readable storage medium, and when executed by one or more processors, it can implement the steps of the various method embodiments described above.

[0136] If the integrated unit is implemented as a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, all or part of the processes in the methods of the above embodiments can also be implemented by a computer program instructing related hardware. The computer program can be stored in a computer-readable storage medium, and when executed by one or more processors, it can implement the steps of the various method embodiments described above.

[0137] Similarly, as a computer program product, when the computer program product is run on a terminal device, it enables the terminal device to implement the steps in the above-described method embodiments.

[0138] The computer program includes computer program code, which can be in the form of source code, object code, executable file, or some intermediate form. The computer-readable medium can include: any entity or device capable of carrying the computer program code, recording media, USB flash drive, portable hard drive, magnetic disk, optical disk, computer memory, read-only memory (ROM), random access memory (RAM), electrical carrier signals, telecommunication signals, and software distribution media, etc. It should be noted that the content included in the computer-readable medium can be appropriately added or removed according to the requirements of legislation and patent practice in the jurisdiction. For example, in some jurisdictions, according to legislation and patent practice, computer-readable media may not include electrical carrier signals and telecommunication signals.

[0139] The above-described embodiments are only used to illustrate the technical solutions of this application, and are not intended to limit them. Although this application has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features. Such modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of this application, and should all be included within the protection scope of this application.

Claims

1. A database query method, characterized in that, include: Retrieve the query metrics that the user needs to query from the user's query request; Based on the data source structure corresponding to the query indicator and the aggregation function of the query indicator, an aggregation expression for the query indicator is generated, wherein the data source structure is used to characterize the data source features of the query data required by the query indicator; If a first indicator exists among the query indicators, the aggregation expressions of the first indicator with the same data source structure are combined to obtain a combined expression, wherein the first indicator is a query indicator with the same data source structure as other query indicators. Based on the combined expression, a first query script with the same data source structure for the first indicator is generated; The query results are obtained by querying the query indicators in the database according to the first query script.

2. The database query method as described in claim 1, characterized in that, If a first indicator exists among the query indicators, the aggregation expressions of the first indicators with the same data source structure are combined to obtain a combined expression, including: If a first indicator exists among the query indicators, and the first indicator is a first type of indicator, the aggregation expressions of the first indicators with the same data source structure are combined to obtain a combined expression, wherein the first type of indicator is a query indicator that can be determined without relying on other query indicators.

3. The database query method as described in claim 1, characterized in that, After generating the aggregate expression for the query indicator based on the data source structure corresponding to the query indicator and the aggregate function of the query indicator, the method further includes: If a second indicator exists among the query indicators, and the second indicator is a first type of indicator, a second query script for the second indicator is generated based on the aggregation expression of the second indicator. Here, the second indicator is a query indicator that does not have the same data source structure as other query indicators, and the first type of indicator is a query indicator that can be determined without relying on other query indicators.

4. The database query method as described in claim 1, characterized in that, After generating the aggregate expression for the query indicator based on the data source structure corresponding to the query indicator and the aggregate function of the query indicator, the method further includes: If a third indicator exists among the query indicators, and the third indicator is a second type of indicator, where the second type of indicator is a query indicator that needs to be determined by other query indicators, then no query script for the third indicator will be generated. After obtaining the first and second indicators from the database query, the third indicator is calculated based on the first and / or the second indicators.

5. The database query method as described in claim 3, characterized in that, The method further includes: Based on the data query conditions in the user query request, the dimensions of the data tables in the database that need to be queried are combined to obtain various combined dimensions; Obtain the attribute features of each of the combined dimensions, wherein the attribute features include the historical zero value ratio and / or the estimated number of rows of query data, and the historical zero value ratio is used to characterize the proportion of zero values ​​in the historical data of the combined dimension; If a target combined dimension exists in the combined dimensions, a skip instruction program for the target combined dimension is generated. The target combined dimension is the combined dimension whose historical zero value ratio is greater than a first preset value and / or whose estimated number of query data rows is less than a second preset value. The skip instruction program is used to instruct the database not to access the data in the target combined dimension. Add the skip instruction program to the first query script to obtain the optimized first query script; The skip instruction program is added to the second query script to obtain the optimized second query script.

6. The database query method as described in claim 3, characterized in that, The method further includes: Obtain the configuration file of the database, wherein the configuration file stores the distribution of data and indexing rules in the database; Based on the configuration file and the data query conditions in the user query request, the optimal query path for the query metric is generated; The optimized target query script is obtained by optimizing the data source information in the target query script based on the optimal query path. The target query script is the query script corresponding to the query indicators in the first query script and the second query script.

7. The database query method as described in claim 3, characterized in that, The method further includes: Retrieve the sorting rules of the database indexes; The sorting rules of the index are added to the third query script, wherein the third query script is the query script that needs to be sent to the database from the first query script and the second query script.

8. The database query method as described in claim 3, characterized in that, The method further includes: Based on the dimensional information of the database and the query metrics to be queried, generate the data index range of the database; The data index range is added to the fourth query script, wherein the fourth query script is the query script that needs to be sent to the database from the first query script and the second query script.

9. A terminal device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the computer program, it implements the database query method as described in any one of claims 1 to 7.

10. A computer-readable storage medium storing a computer program, characterized in that, When the computer program is executed by a processor, it implements the database query method as described in any one of claims 1 to 7.

11. A computer program product, when run on a terminal device, causes the terminal device to perform the database query method as described in any one of claims 1 to 7.

Images