Query method and device, equipment and storage medium

By judging the query method based on the dimensions of the query conditions, and combining with the classification model to predict the query time, deciding whether to perform pre-calculation, the problem of pre-calculation occupies resources and space in the multi-dimensional big data application system is solved, and the effect of rapid query and resource conservation is achieved .

CN119088840APending Publication Date: 2024-12-06CHINA CONSTRUCTION BANK +1
View PDF 4 Cites 0 Cited by

Patent Information

Application Number
CN202411236552.2
Authority / Receiving Office
CN · China
Patent Type
Applications(China)
Current Assignee / Owner
Filing Date
2024-09-04
Publication Date
2024-12-06

AI Technical Summary

Technical Problem

In the existing multi-dimensional large data application system, although the pre-computation query method can shorten the query time, it occupies a large amount of computing resources and data storage space. It is necessary to find a way to improve query speed without occupying too much storage space.

Method used

By obtaining the query conditions entered by the user, determining the query dimension, and determining whether to use pre-calculation based on the dimension, if pre-calculation is not used, online calculation is performed, and the classification model is used to predict the query time to determine whether to perform pre-calculation, reducing the storage of pre-calculation results demand.

Benefits of technology

It realizes the improvement of query speed without increasing storage space usage, improves user experience, and optimizes the query process through online computing and aggregated dimension levels.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN119088840A_ABST
    Figure CN119088840A_ABST
Patent Text Reader

Abstract

The invention relates to the technical field of data processing, in particular to a query method and device, equipment and a storage medium. The method comprises the steps of obtaining a query condition input by a user, and determining a query dimension corresponding to the query condition; determining a query mode corresponding to the query condition according to the query dimension corresponding to the query condition; if the query mode is pre-calculation, determining a query result corresponding to the query condition from each pre-calculation result; if the query mode is non-pre-calculation, online calculation is carried out according to the query dimension corresponding to the query condition, and a query result corresponding to the query condition is obtained; and returning a query result corresponding to the query condition to the user. Compared with a method for obtaining the query result by adopting a pre-calculation mode completely, the query method provided by the invention does not adopt the pre-calculation mode to obtain the query result for all queries, so that the space for storing the pre-calculation result is saved, and the speed for obtaining the query result is also improved to a certain extent.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present application relates to the field of data processing technology, and in particular to a query method, device, equipment and storage medium. Background Art

[0002] Currently, for multidimensional and large data application systems, pre-calculated queries are usually used to obtain query results. Query pre-calculation is an optimization technology that aims to improve query performance and efficiency. The so-called query pre-calculation refers to pre-calculating and processing data before executing the query so that query results can be provided faster when the query is requested.

[0003] For example, taking a distributed monitoring cluster management system with a large amount of data as an example, the system includes regional monitoring data collection nodes, centralized monitoring query nodes, monitoring alarm points and control nodes. In the query node, the real-time data collection volume is collected by regularly querying the current collection node indicators, and combined with the pre-defined pre-calculated indicator items, the data is pre-calculated, the new indicator data is saved, and the mapping relationship between the new indicator data and the pre-calculated indicator items is saved in the database.

[0004] Although the above solutions can shorten the query time, they all obtain the query results through pre-calculated query methods, which will take up a lot of computing resources and data storage space. Therefore, it is necessary to implement a query method that can shorten the query time without taking up too much storage space. Summary of the invention

[0005] The present application provides a query method, apparatus, device and storage medium to improve query speed and reduce storage space occupation.

[0006] In a first aspect, the present application provides a query method, the method comprising:

[0007] Obtaining the query condition input by the user, and determining the query dimension corresponding to the query condition;

[0008] Determining a query method corresponding to the query condition according to a query dimension corresponding to the query condition;

[0009] If the query method is pre-calculation, the query result corresponding to the query condition is determined from each pre-calculation result; if the query method is non-pre-calculation, online calculation is performed according to the query dimension corresponding to the query condition to obtain the query result corresponding to the query condition;

[0010] The query results corresponding to the query conditions are returned to the user.

[0011] The query method provided by the present application, after obtaining the query conditions of the user, first determines the query dimension corresponding to the query conditions, and then determines the corresponding query method according to the query dimension, indicating that not all query results of the present application are obtained by pre-calculation. For non-pre-calculation methods, the query results are obtained by online calculation; that is, the present application judges whether to adopt the pre-calculation query method, thereby reducing the storage space used to store the pre-calculated results. Therefore, the present application not only improves the speed of obtaining query results, but also reduces the storage space required to store the pre-calculated results.

[0012] In a possible design, determining a query mode corresponding to the query condition according to a query dimension corresponding to the query condition includes:

[0013] Determine the dimension level combination to which the query dimension corresponding to the query condition belongs;

[0014] If the dimension level combination belongs to a pre-computed combination label, it is determined that the query mode corresponding to the query condition is pre-computed; any pre-computed combination label is obtained by predicting the query time of the query conditions corresponding to each dimension level combination under multiple query dimensions through a classification model, wherein the dimension level combination whose query time output by the classification model is greater than the expected response time is set as the pre-computed combination label.

[0015] The so-called expected response time refers to the time within which a user can obtain query results when performing a query operation. When a classification model is used to predict a dimension-level combination, if the model output time is less than or equal to the expected response time, it indicates that the query results of the query conditions corresponding to the dimension-level combination can be obtained in a shorter time. In this way, there is no need to set a pre-calculated combination label for the dimension-level combination. Even if online calculation is performed, the query results can be returned to the user more quickly, giving the user a better experience.

[0016] In a possible design, the classification model is determined by the following method, including:

[0017] Collecting multiple historical query statements and the query time required for each of the multiple historical query statements;

[0018] Determine a feature set corresponding to each of the multiple historical query statements;

[0019] The classification model is obtained by training according to the feature set corresponding to each of the multiple historical query statements and the query time required for each of the multiple historical query statements.

[0020] The classification model is obtained by training with feature sets corresponding to historical query statements and the query time required for the historical query statements. Therefore, the classification model has good accuracy when predicting the query time corresponding to the feature set based on the feature set.

[0021] In a possible design, the feature set corresponding to each of the multiple historical query statements includes at least one of the first feature, the second feature, the third feature, the fourth feature, and the fifth feature;

[0022] The first feature is the number of fact tables associated with the first historical query statement; the second feature is the number of query dimensions corresponding to the first historical query statement; the third feature is associated with the full data volume of the fact table; the fourth feature is the total number of query dimensions; the fifth feature is the maximum number of dimensional levels under a single query dimension; the first historical query statement is any one of the multiple historical query statements.

[0023] The present application takes into account the following factors that mainly affect the query time: first, the amount of data involved in the query statement, which corresponds to the first feature mentioned above; second, the number of query dimensions involved in the query statement. Different dimension combinations mean different query plans. More dimensions may mean more filtering, connection and aggregation operations, which in turn increase the query time. This influencing factor corresponds to the second feature mentioned above; third, the total amount of data. The larger the total amount of data, the more original data needs to be scanned, which in turn increases the query time. This influencing factor corresponds to the third feature mentioned above; fourth, the total number of query dimensions. More query dimensions require more indexes to be maintained, and more time will be consumed when querying indexes, which is equivalent to increasing the query time. This influencing factor corresponds to the fourth feature mentioned above; fifth, the number of categories under each query dimension. The number of categories is also the number of dimension levels. The more dimension levels there are, the more data blocks need to be scanned, which in turn increases the query time. This influencing factor corresponds to the fifth feature mentioned above; the classification model of the present application predicts the time required for the query based on the first to fifth features, which can improve the accuracy of the prediction results, thereby improving the accuracy of the pre-calculated label settings.

[0024] In a possible design, determining the query mode corresponding to the query condition according to the query dimension corresponding to the query condition includes:

[0025] Determine whether the query dimension corresponding to the query condition is an aggregate dimension; the amount of data corresponding to any aggregate dimension is greater than a set amount; when the amount of data corresponding to any query dimension exceeds the set amount, the query time required for querying the query condition with the query dimension is greater than the expected response time;

[0026] If it is an aggregate dimension, the query method corresponding to the query condition is pre-calculation.

[0027] When the amount of data corresponding to the query dimension is greater than the set amount and the query time required for the query condition of the query dimension is greater than the expected response time, it means that it takes a long time to obtain the query result corresponding to the query dimension. The query dimension should be set as an aggregation dimension to shorten the query time and improve the user experience.

[0028] In a possible design, if the query mode is non-pre-calculation, online calculation is performed according to the query dimension corresponding to the query condition to obtain the query result corresponding to the query condition, including:

[0029] If the query mode is non-pre-calculation, determining that the query dimension corresponding to the query condition is a dimension level of an aggregate dimension on a lower dimension;

[0030] Obtaining a pre-calculated result corresponding to the query condition in the dimension level that is an aggregate dimension in the lower dimension;

[0031] An online calculation is performed based on the pre-calculated result corresponding to the query condition to obtain a query result corresponding to the query condition.

[0032] For non-precomputed query conditions, first determine that the query dimension corresponding to the query condition is the dimension level of the aggregation dimension in the lower dimension level, and perform online calculation based on the pre-calculated results in the lower dimension level to obtain the query result. Compared with directly performing online calculation based on the query condition, the query speed can be improved to a certain extent.

[0033] In one possible design, any aggregation dimension is determined as follows:

[0034] According to the order of the dimension levels from the top layer to the bottom layer under each query dimension, count the amount of data in each dimension level under each query dimension;

[0035] Determine the dimension level whose data volume is greater than the set amount as the aggregation dimension;

[0036] Aggregate the data under the aggregation dimension according to the preset aggregation index to obtain the pre-calculated results.

[0037] In one possible design, the multiple query dimensions include at least one of an institution query dimension, a date query dimension, and a product category query dimension; wherein the dimension hierarchy under the institution query dimension includes at least one of a provincial branch, a city branch, a county branch, a first-level outlet, a second-level outlet, and a sub-branch outlet.

[0038] In a second aspect, the present application provides a query device, the device comprising: a transceiver unit and a processing unit;

[0039] The transceiver unit is used to obtain the query condition input by the user and determine the query dimension corresponding to the query condition;

[0040] The processing unit is configured to determine a query method corresponding to the query condition according to a query dimension corresponding to the query condition; if the query method is pre-calculation, determine a query result corresponding to the query condition from each pre-calculation result; if the query method is non-pre-calculation, perform online calculation according to the query dimension corresponding to the query condition to obtain a query result corresponding to the query condition;

[0041] The transceiver unit is further used to return the query result corresponding to the query condition to the user.

[0042] In a possible design, the processing unit is used to determine the query mode corresponding to the query condition according to the query dimension corresponding to the query condition:

[0043] Determine the dimension level combination to which the query dimension corresponding to the query condition belongs;

[0044] If the dimension level combination belongs to a pre-computed combination label, it is determined that the query mode corresponding to the query condition is pre-computed; any pre-computed combination label is obtained by predicting the query time of the query conditions corresponding to each dimension level combination under multiple query dimensions through a classification model, wherein the dimension level combination whose query time output by the classification model is greater than the expected response time is set as the pre-computed combination label.

[0045] In a possible design, the transceiver unit is also used to collect multiple historical query statements and the query time required for each of the multiple historical query statements; the processing unit is also used to determine the feature set corresponding to each of the multiple historical query statements; training is performed based on the feature set corresponding to each of the multiple historical query statements and the query time required for each of the multiple historical query statements to obtain the classification model.

[0046] In a possible design, the feature set corresponding to each of the multiple historical query statements includes at least one of the first feature, the second feature, the third feature, the fourth feature, and the fifth feature;

[0047] The first feature is the number of fact tables associated with the first historical query statement; the second feature is the number of query dimensions corresponding to the first historical query statement; the third feature is associated with the full data volume of the fact table; the fourth feature is the total number of query dimensions; the fifth feature is the maximum number of dimensional levels under a single query dimension; the first historical query statement is any one of the multiple historical query statements.

[0048] In a possible design, the processing unit is used to determine the query method corresponding to the query condition according to the query dimension corresponding to the query condition: determine whether the query dimension corresponding to the query condition is an aggregation dimension; the amount of data corresponding to any aggregation dimension is greater than a set number; when the amount of data corresponding to any query dimension exceeds the set number, the query time required for the query condition with the query dimension when querying is greater than the expected response time; if it is an aggregation dimension, the query method corresponding to the query condition is pre-calculation.

[0049] In a possible design, if the query method is non-pre-calculated, the processing unit performs online calculation according to the query dimension corresponding to the query condition, and obtains the query result corresponding to the query condition: if the query method is non-pre-calculated, determine that the query dimension corresponding to the query condition is a dimension level of an aggregation dimension on the lower dimension; obtain the pre-calculated result corresponding to the query condition in the dimension level that is an aggregation dimension on the lower dimension; perform online calculation according to the pre-calculated result corresponding to the query condition, and obtain the query result corresponding to the query condition.

[0050] In a possible design, any aggregation dimension is determined in the following manner: the processing unit counts the amount of data in each dimensional level under each query dimension in order from the top layer to the bottom layer of the dimensional level under each query dimension; determines that the dimensional level with a data volume greater than the set number is the aggregation dimension; aggregates the data under the aggregation dimension according to the preset aggregation index to obtain a pre-calculated result.

[0051] In one possible design, the multiple query dimensions include at least one of an institution query dimension, a date query dimension, and a product category query dimension; wherein the dimension hierarchy under the institution query dimension includes at least one of a provincial branch, a city branch, a county branch, a first-level outlet, a second-level outlet, and a sub-branch outlet.

[0052] In a third aspect, the present application further provides a query device, the device comprising: a processor, and a memory communicatively connected to the processor;

[0053] The memory stores computer-executable instructions;

[0054] The processor executes the computer-executable instructions stored in the memory to implement the method described in the first aspect above.

[0055] In a fourth aspect, the present application further provides a computer-readable storage medium, wherein the computer-readable storage medium includes a program, and when the program is executed on a device, the device executes the method as described in any one of the above-mentioned first aspects.

[0056] In a fifth aspect, the present application further provides a computer program product, wherein the computer program product comprises a computer program, and when the computer program is executed by a processor, the method described in the first aspect is implemented. BRIEF DESCRIPTION OF THE DRAWINGS

[0057] In order to more clearly illustrate the technical solutions in the embodiments of the present application, the drawings required for use in the description of the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.

[0058] Figure 1 A flowchart of a query method provided in an embodiment of the present application;

[0059] Figure 2 A schematic diagram of the structure of the query device provided in the embodiment of the present application Figure 1 ;

[0060] Figure 3 A schematic diagram of the structure of the query device provided in the embodiment of the present application Figure 2 . DETAILED DESCRIPTION

[0061] In order to make the purpose, technical scheme and advantages of the present application clearer, the technical scheme in the embodiment of the present application will be clearly and completely described below in conjunction with the drawings in the embodiment of the present application. Obviously, the described embodiment is only a part of the embodiment of the present application, rather than all the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in the field without making creative work are within the scope of protection of the present application. In the absence of conflict, the embodiments in the present application and the features in the embodiments can be arbitrarily combined with each other. In addition, although the logical order is shown in the flow chart, in some cases, the steps shown or described can be performed in an order different from that here.

[0062] The terms "first" and "second" in the specification and claims of the present application and the above-mentioned drawings are used to distinguish different objects, rather than to describe a specific order. In addition, the term "comprising" and any of their variations are intended to cover non-exclusive protection. For example, a process, method, system, product or device comprising a series of steps or units is not limited to the listed steps or units, but optionally also includes steps or units that are not listed, or optionally also includes other steps or units inherent to these processes, methods, products or devices. "Multiple" in the present application can mean at least two, for example, two, three or more, and the embodiments of the present application are not limited.

[0063] The following is a description of exemplary embodiments of the present application in conjunction with the accompanying drawings, including various details of the embodiments of the present application to facilitate understanding, which should be considered as merely exemplary. Therefore, those of ordinary skill in the art should recognize that various changes and modifications can be made to the embodiments described herein without departing from the scope of the present application. Similarly, for the sake of clarity and conciseness, the description of well-known functions and structures is omitted in the following description. It should be noted that in the embodiments of the present application, certain software, components, models and other existing solutions in the industry may be mentioned, which should be considered as exemplary, and their purpose is only to illustrate the feasibility of the implementation of the technical solution of the present application, but it does not mean that the applicant has or will necessarily use the solution.

[0064] In the technical solution of this application, the acquisition, transmission, storage, and use of data are in compliance with the requirements of relevant national laws and regulations.

[0065] In order to solve the problem that all pre-calculated query methods mentioned in the background technology section will take up a lot of storage space, this application proposes Figure 1 The query method shown in the figure can be executed by a server, a chip in the server, or a functional module in the server, which is not limited in this application. The following description is based on the server as the execution subject, and the method includes:

[0066] Step 100: Obtain the query conditions input by the user and determine the query dimensions corresponding to the query conditions.

[0067] For example, for application systems or application software used in daily life, users can perform query operations through the system interface. Generally, query conditions are displayed in the form of drop-down boxes or input boxes. Common query conditions include date, department, etc. For example, for a banking and financial system, relevant staff can query the amount of sales of a certain financial product in a certain month, or query the number of people who purchased a certain financial product on a certain day.

[0068] Exemplarily, each query condition has a corresponding query dimension. For example, taking the example of querying the amount of a certain financial product sold in a certain month, since the query condition is queried from the two dimensions of date and product category, the query dimensions corresponding to the query condition are date and product type.

[0069] Step 101: Determine a query method corresponding to a query condition according to a query dimension corresponding to the query condition.

[0070] Exemplarily, after determining the query dimension corresponding to the query condition input by the user in step 100, the present application provides the following two methods for determining the query method corresponding to the query condition according to the query dimension corresponding to the query condition:

[0071] Method 1: First, determine the dimension level combination to which the query dimension corresponding to the query condition belongs. If the dimension level combination belongs to a pre-calculated combination label, determine that the query method corresponding to the query condition is pre-calculated.

[0072] One thing that needs to be explained is that any pre-computed combination label is obtained by predicting the query time of the query conditions corresponding to each dimension level combination under multiple query dimensions through the classification model, wherein the dimension level combination whose query time output by the classification model is greater than the expected response time is set as the pre-computed combination label; in other words, when the classification model is used to predict the query time required for the query conditions corresponding to a certain dimension level combination, if the predicted result is greater than the expected response time, then the dimension level combination is set as the pre-computed combination label, and the query result corresponding to the dimension level combination is stored as the pre-computed result. The so-called expected response time can be understood as if the user obtains the query result within the expected response time when performing a query operation, then the user will have a better experience.

[0073] Exemplarily, the training process of the classification model is explained below: first, multiple historical query statements and the query time required for each of the multiple historical query statements are collected; then the feature set corresponding to each of the multiple historical query statements is determined; finally, training is performed based on the feature set corresponding to each of the multiple historical query statements and the query time required for each of the multiple historical query statements, thereby obtaining a classification model.

[0074] Further exemplarily, the present application takes into account that the query time required for the query is related to five factors: the amount of data involved in the query statement, the number of query dimensions involved in the query statement, the total amount of data, the total number of query dimensions, and the number of categories under each query dimension. Among them, the number of categories under the query dimension can also be referred to as the number of dimensional levels under the query dimension. Therefore, when determining the feature set corresponding to each historical query statement, the features corresponding to each historical query statement and the above five influencing factors are determined, or the features corresponding to each historical query statement and at least one of the above five influencing factors are determined, so as to ensure the accuracy of the classification model in predicting the query time, that is, the feature set corresponding to each historical query statement should include at least one of the first feature, the second feature, the third feature, the fourth feature and the fifth feature, corresponding to the above five influencing factors, the first feature is the number of fact tables associated with the first historical query statement (the first historical query statement is any one of the multiple historical query statements), the second feature is the number of query dimensions corresponding to the first historical query statement; the third feature is associated with the total amount of data of the fact table; the fourth feature is the total number of query dimensions; the fifth feature is the maximum number of dimensional levels under a single query dimension.

[0075] For example, assuming that there are 100 fact tables in the database of system A, and there are three query dimensions, namely date, department, and product category. There are three dimension levels under the query dimension of date, namely year, month, and day; there are four dimension levels under the query dimension of department, namely first-level department, second-level department, third-level department, and fourth-level department; there are three dimension levels under the query dimension of product category, namely product category one, product category two, and product category three; assuming that there are two fact tables involved in the first historical query statement, and the first historical query statement only queries based on the date dimension, then for the first historical query statement, the first feature is 2 (there are two fact tables involved), the second feature is 1 (query is performed only based on the date dimension), the third feature is 100 (there are 100 fact tables in system A), the fourth feature is 3 (there are three query dimensions in system A, namely date, department, and product category), and the fifth feature is 3, 4, and 3 (respectively the number of dimension levels under each of the three query dimensions).

[0076] Method 2: Determine whether the query dimension corresponding to the query condition is an aggregate dimension. If it is an aggregate dimension, the query method corresponding to the query condition is pre-calculation. The so-called aggregate dimension needs to meet certain conditions: the amount of data corresponding to any aggregate dimension is greater than the set number. The set number is determined based on the expected response time. The set number is less than or equal to the maximum amount of data corresponding to the query dimension that can be accepted to obtain the query result within the expected response time.

[0077] For example, assuming the expected response time is 0.1s, when the amount of data corresponding to the query dimension is 500, the query time required for the query condition with this query dimension is 0.05s, which is less than the expected response time; when the amount of data corresponding to the query dimension is 1000, the query time required for the query condition with this query dimension is 0.08s, which is still less than the expected response time; when the amount of data corresponding to the query dimension is 1500, the query time required for the query condition with this query dimension is 1.2s, and the query time required is greater than the expected response time. In this case, the set quantity should be less than or equal to 1000.

[0078] In other words, when the amount of data corresponding to any query dimension exceeds the set number, the query time required for query conditions with this query dimension will be greater than the expected response time. For example, assuming the set number is 1000, when the amount of data corresponding to a query dimension exceeds 1000, the query time required for query conditions with this query dimension will be greater than the expected response time. In this case, the query dimension should be set as an aggregation dimension, and the data of the query dimension should be aggregated in advance to obtain pre-calculated results to avoid long query time affecting the user experience.

[0079] Exemplarily, the multiple query dimensions in this application include at least one of the institution query dimension, the date query dimension, and the product classification query dimension. These three query dimensions include at least one dimension level. For the institution query dimension, the dimension levels are, from top to bottom, provincial branches, municipal branches, county branches, first-level outlets, second-level outlets, and branch outlets; for the date query dimension, the dimension levels are, from top to bottom, year, month, day, or year, month, day, hour, minute, and second; for the product classification query dimension, the dimension levels are, from top to bottom, product categories and product subcategories. It should be noted that the dimension levels under the above query dimensions are only examples, and the dimension levels can be customized according to actual business needs, and this application does not limit this.

[0080] Exemplarily, for each query dimension, the steps to determine whether its dimension level is an aggregation dimension are as follows: first, count the amount of data in each dimension level in order from the top level to the bottom level under each query dimension; then determine the dimension level with a data volume greater than a set number as an aggregation dimension; finally, aggregate the data under the aggregation dimension according to the preset aggregation indicator, obtain the pre-calculated result, and store the pre-calculated result.

[0081] Exemplarily, the data of the current dimension level is obtained by aggregating the data of the next dimension level. Therefore, the amount of data from the top level to the bottom level of the dimension level increases. For example, assuming that system A stores data from 2000 to 2023, there are 24 data items at the year dimension level. The first data item is obtained by aggregating the data of 12 months in 2000, the second data item is obtained by aggregating the data of 12 months in 2001... The 24th data item is obtained by aggregating the data of 12 months in 2023. Furthermore, since the amount of data from the top level to the bottom level of the dimension level increases, the possibility of data aggregation is greater for the lower level of the dimension level. Finally, this application determines to count the amount of data in each dimension level in the order from the top level to the bottom level of the dimension level.

[0082] Exemplarily, the preset aggregation indicator may be at least one of sales amount, sales quantity, and number of purchasers. For example, if the secondary outlets dimension level under the institution query dimension is determined to be the aggregation dimension, and assuming that the preset aggregation indicator is sales amount, then after aggregation, the sales amount of each type of product sold by each secondary outlet every day can be obtained; similarly, assuming that the aggregation indicators are sales quantity and number of purchasers, then after aggregation, the quantity of each type of product sold by each secondary outlet every day and the number of people who purchase each type of product can be obtained.

[0083] In this step, the data in each dimension level is not aggregated, nor are all dimension level combinations set as pre-calculated combination labels. Instead, the classification model and the amount of data in each dimension level are used to determine whether to pre-calculate. Pre-calculation is performed only for dimension level combinations whose data volume is greater than the set amount and whose output time after inputting the classification model is greater than the expected response time. This improves the rationality of pre-calculation and reduces the storage space required to store the pre-calculation results.

[0084] Step 102: If the query method is pre-calculation, the query result corresponding to the query condition is determined from each pre-calculation result; if the query method is non-pre-calculation, online calculation is performed according to the query dimension corresponding to the query condition to obtain the query result corresponding to the query condition.

[0085] Exemplarily, through step 101, the pre-calculated results corresponding to the dimension level combination set as the pre-calculated label and the pre-calculated results after aggregating the data in the aggregated dimension have been obtained, and the pre-calculated results have been stored. Therefore, when the query method is pre-calculation, the corresponding query results can be directly determined from the stored pre-calculated results according to the query conditions, which shortens the time to obtain the query results and improves the query speed.

[0086] Exemplarily, if the query mode is non-pre-calculation, online calculation should be performed according to the query dimension corresponding to the query condition, so as to obtain the query result corresponding to the query condition. However, the online calculation here is not a complete online calculation, but an online calculation based on the pre-calculation result. Specifically, when the query mode is non-pre-calculation, first determine that the query dimension corresponding to the query condition is the dimension level of the aggregation dimension on the lower dimension; then obtain the pre-calculation result corresponding to the query condition in the dimension level of the aggregation dimension on the lower dimension; then perform online calculation according to the pre-calculation result corresponding to the query condition to obtain the query result corresponding to the query condition.

[0087] For example, assuming that the secondary outlets under the institution query dimension are the aggregation dimension, the pre-calculated results obtained after aggregating the data in the dimension level are the number of each type of product sold by each secondary outlet every day and the number of people who purchase each type of product. When the user wants to query the number of product B sold by the primary outlet A, the query dimension corresponding to the query condition includes the primary outlets, and the primary outlets are non-aggregate dimensions. At this time, the dimension level that is the aggregation dimension should be searched on the lower dimension of the primary outlets. It is found that the secondary outlets are the aggregation dimension, and then online calculation is performed based on the pre-calculated results of the dimension level. The pre-calculated results of the dimension level include the number of each type of product sold by each secondary outlet every day and the number of people who purchase each type of product. Obviously, the number of product B sold by each secondary outlet every day can be obtained, and then the number of product B sold by each secondary outlet under the primary outlet A every day is accumulated, that is, the number of product B sold by the primary outlet A is obtained.

[0088] In this step, for pre-calculated query methods, the corresponding query results are directly obtained from the pre-calculated results according to the query conditions, which significantly improves the query speed. For non-pre-calculated query methods, the query results are obtained by searching the dimension level that is the aggregation dimension in the lower dimension and performing online calculation based on the pre-calculated results of the lower dimension. Compared with the complete online calculation, the query speed can also be improved to a certain extent.

[0089] Step 103: Return the query results corresponding to the query conditions to the user.

[0090] Exemplarily, after obtaining the query results corresponding to the query conditions through step 102, the query results are returned to the user. The user can export the query results or perform relevant analysis based on the query results for subsequent work guidance. This application does not limit the use of the query results.

[0091] Figure 2 and Figure 3The following is a schematic diagram of the structure of possible query devices provided by the embodiments of the present application. These query devices can be used to implement the functions of the server in the above method embodiments, and thus can also achieve the beneficial effects possessed by the above method embodiments.

[0092] like Figure 2 As shown, the query device 200 includes a transceiver unit 210 and a processing unit 220. The query device 200 is used to implement the above Figure 1 The functions of the server in the method embodiment are shown.

[0093] When the query device 200 is used to implement Figure 1 The functions of the server in the method embodiment shown are:

[0094] The transceiver unit 210 is used to obtain the query condition input by the user and determine the query dimension corresponding to the query condition;

[0095] The processing unit 220 is configured to determine a query method corresponding to the query condition according to a query dimension corresponding to the query condition; if the query method is pre-calculation, determine a query result corresponding to the query condition from each pre-calculation result; if the query method is non-pre-calculation, perform online calculation according to the query dimension corresponding to the query condition to obtain a query result corresponding to the query condition;

[0096] The transceiver unit 210 is further configured to return the query result corresponding to the query condition to the user.

[0097] In a possible design, the processing unit 220 is used to determine the query mode corresponding to the query condition according to the query dimension corresponding to the query condition:

[0098] Determine the dimension level combination to which the query dimension corresponding to the query condition belongs;

[0099] If the dimension level combination belongs to a pre-computed combination label, it is determined that the query mode corresponding to the query condition is pre-computed; any pre-computed combination label is obtained by predicting the query time of the query conditions corresponding to each dimension level combination under multiple query dimensions through a classification model, wherein the dimension level combination whose query time output by the classification model is greater than the expected response time is set as the pre-computed combination label.

[0100] In a possible design, the transceiver unit 210 is also used to collect multiple historical query statements and the query time required for each of the multiple historical query statements; the processing unit 220 is also used to determine the feature set corresponding to each of the multiple historical query statements; training is performed based on the feature set corresponding to each of the multiple historical query statements and the query time required for each of the multiple historical query statements to obtain the classification model.

[0101] In a possible design, the feature set corresponding to each of the multiple historical query statements includes at least one of the first feature, the second feature, the third feature, the fourth feature, and the fifth feature;

[0102] The first feature is the number of fact tables associated with the first historical query statement; the second feature is the number of query dimensions corresponding to the first historical query statement; the third feature is associated with the full data volume of the fact table; the fourth feature is the total number of query dimensions; the fifth feature is the maximum number of dimensional levels under a single query dimension; the first historical query statement is any one of the multiple historical query statements.

[0103] In a possible design, the processing unit 220 is used to determine the query method corresponding to the query condition according to the query dimension corresponding to the query condition: determine whether the query dimension corresponding to the query condition is an aggregation dimension; the amount of data corresponding to any aggregation dimension is greater than a set number; when the amount of data corresponding to any query dimension exceeds the set number, the query time required for the query condition with the query dimension during query is greater than the expected response time; if it is an aggregation dimension, the query method corresponding to the query condition is pre-calculation.

[0104] In one possible design, if the query method is non-pre-calculated, the processing unit 220 performs online calculation according to the query dimension corresponding to the query condition, and obtains the query result corresponding to the query condition: if the query method is non-pre-calculated, determine that the query dimension corresponding to the query condition is a dimension level of an aggregation dimension on the lower dimension; obtain the pre-calculated result corresponding to the query condition in the dimension level that is an aggregation dimension on the lower dimension; perform online calculation according to the pre-calculated result corresponding to the query condition, and obtain the query result corresponding to the query condition.

[0105] In a possible design, any aggregation dimension is determined in the following manner: the processing unit 220 counts the amount of data in each dimensional level under each query dimension in order from the top layer to the bottom layer of the dimensional level under each query dimension; determines that the dimensional level with a data volume greater than the set number is an aggregation dimension; aggregates the data under the aggregation dimension according to a preset aggregation indicator to obtain a pre-calculated result.

[0106] In one possible design, the multiple query dimensions include at least one of an institution query dimension, a date query dimension, and a product category query dimension; wherein the dimension hierarchy under the institution query dimension includes at least one of a provincial branch, a city branch, a county branch, a first-level outlet, a second-level outlet, and a sub-branch outlet.

[0107] For more detailed description of the transceiver unit 210 and the processing unit 220, please refer to Figure 1 The relevant description in the method embodiment shown is directly obtained and will not be repeated here.

[0108] like Figure 3 As shown, the query device 300 includes a processor 310 and an interface circuit 320. The processor 310 and the interface circuit 320 are coupled to each other. It is understood that the interface circuit 320 can be a transceiver or an input-output interface. Optionally, the query device 300 can also include a memory 330 for storing instructions executed by the processor 310 or storing input data required by the processor 310 to execute instructions or storing data generated after the processor 310 executes instructions.

[0109] When the query device 300 is used to implement Figure 1 When the method is shown, the processor 310 is used to implement the functions of the processing unit 220, and the interface circuit 320 is used to implement the functions of the transceiver unit 210.

[0110] The division of units in the embodiments of the present application is schematic and is only a logical function division. There may be other division methods in actual implementation. In addition, each functional unit in each embodiment of the present application may be integrated into a processor, or may exist physically separately, or two or more units may be integrated into one unit. The above-mentioned integrated unit may be implemented in the form of hardware or in the form of software functional units.

[0111] Although the preferred embodiments of the present application have been described, those skilled in the art may make other changes and modifications to these embodiments once they have learned the basic creative concept. Therefore, the appended claims are intended to be interpreted as including the preferred embodiments and all changes and modifications falling within the scope of the present application.

[0112] Obviously, those skilled in the art can make various changes and modifications to the present application without departing from the scope of the present application. Thus, if these modifications and variations of the present application fall within the scope of the claims of the present application and their equivalents, the present application is also intended to include these modifications and variations.

Claims

1. A query method, characterized in that: The method includes: Obtaining the query condition input by the user, and determining the query dimension corresponding to the query condition; Determining a query method corresponding to the query condition according to a query dimension corresponding to the query condition; If the query method is pre-calculation, the query result corresponding to the query condition is determined from each pre-calculation result; if the query method is non-pre-calculation, online calculation is performed according to the query dimension corresponding to the query condition to obtain the query result corresponding to the query condition; The query results corresponding to the query conditions are returned to the user.

2. The method according to claim 1, characterized in that Determining a query method corresponding to the query condition according to a query dimension corresponding to the query condition includes: Determine the dimension level combination to which the query dimension corresponding to the query condition belongs; If the dimension level combination belongs to a pre-computed combination label, it is determined that the query mode corresponding to the query condition is pre-computed; any pre-computed combination label is obtained by predicting the query time of the query conditions corresponding to each dimension level combination under multiple query dimensions through a classification model, wherein the dimension level combination whose query time output by the classification model is greater than the expected response time is set as the pre-computed combination label.

3. The method according to claim 2, characterized in that The classification model is determined by: Collecting multiple historical query statements and the query time required for each of the multiple historical query statements; Determine a feature set corresponding to each of the multiple historical query statements; The classification model is obtained by training according to the feature set corresponding to each of the multiple historical query statements and the query time required for each of the multiple historical query statements.

4. The method according to claim 3, characterized in that The feature set corresponding to each of the plurality of historical query statements includes at least one of the first feature, the second feature, the third feature, the fourth feature, and the fifth feature; The first feature is the number of fact tables associated with the first historical query statement; the second feature is the number of query dimensions corresponding to the first historical query statement; the third feature is associated with the full data volume of the fact table; the fourth feature is the total number of query dimensions; the fifth feature is the maximum number of dimensional levels under a single query dimension; the first historical query statement is any one of the multiple historical query statements.

5. The method according to claim 2, characterized in that Determining a query method corresponding to the query condition according to a query dimension corresponding to the query condition includes: Determine whether the query dimension corresponding to the query condition is an aggregate dimension; the amount of data corresponding to any aggregate dimension is greater than a set amount; when the amount of data corresponding to any query dimension exceeds the set amount, the query time required for querying the query condition with the query dimension is greater than the expected response time; If it is an aggregate dimension, the query method corresponding to the query condition is pre-calculation.

6. The method according to claim 5, characterized in that If the query mode is non-pre-calculation, online calculation is performed according to the query dimension corresponding to the query condition to obtain the query result corresponding to the query condition, including: If the query mode is non-pre-calculation, determining that the query dimension corresponding to the query condition is a dimension level of an aggregate dimension on a lower dimension; Obtaining a pre-calculated result corresponding to the query condition in the dimension level that is an aggregate dimension in the lower dimension; An online calculation is performed based on the pre-calculated result corresponding to the query condition to obtain a query result corresponding to the query condition.

7. The method according to claim 5, characterized in that Any aggregation dimension is determined as follows: According to the order of the dimension levels from the top layer to the bottom layer under each query dimension, count the amount of data in each dimension level under each query dimension; Determine the dimension level whose data volume is greater than the set amount as the aggregation dimension; Aggregate the data under the aggregation dimension according to the preset aggregation index to obtain the pre-calculated results.

8. The method according to any one of claims 1 to 7, characterized in that The multiple query dimensions include at least one of an institution query dimension, a date query dimension, and a product category query dimension; wherein the dimension levels under the institution query dimension include at least one of a provincial branch, a city branch, a county branch, a first-level outlet, a second-level outlet, and a sub-branch outlet.

9. A query device, characterized in that: The device comprises: a transceiver unit and a processing unit; The transceiver unit is used to obtain the query condition input by the user and determine the query dimension corresponding to the query condition; The processing unit is configured to determine a query method corresponding to the query condition according to a query dimension corresponding to the query condition; if the query method is pre-calculation, determine a query result corresponding to the query condition from each pre-calculation result; if the query method is non-pre-calculation, perform online calculation according to the query dimension corresponding to the query condition to obtain a query result corresponding to the query condition; The transceiver unit is further used to return the query result corresponding to the query condition to the user.

10. A query device, characterized in that: include: A processor, and a memory communicatively connected to the processor; The memory stores computer-executable instructions; The processor executes the computer-executable instructions stored in the memory to implement the method according to any one of claims 1 to 8.

11. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores computer-executable instructions, which are used to implement the method according to any one of claims 1 to 8 when executed by a processor.

12. A computer program product, characterized in that The invention comprises a computer program, which implements the method according to any one of claims 1 to 8 when being executed by a processor.

Citation Information

Patent Citations

  • Novel OLAP precomputation model and method for generating precomputation result

    CN108376143A

  • Pre-calculation result generation method and device, electronic equipment and storage medium

    CN112965991A

  • Data query method and device, computer equipment and storage medium

    CN117033411A

  • Multidimensional data analysis method, apparatus, and system

    WO2022007592A1