Data table sorting method and device, electronic equipment and storage medium

By obtaining historical query information, filtering and sorting business data tables, the problem of slow business data query speed is solved, and query efficiency and storage optimization are improved.

CN119088805AActive Publication Date: 2024-12-06BEIJING QIYI CENTURY SCI & TECH CO LTD
View PDF 4 Cites 0 Cited by

Patent Information

Application Number
CN202411193937.5
Authority / Receiving Office
CN · China
Patent Type
Applications(China)
Current Assignee / Owner
Filing Date
2024-08-28
Publication Date
2024-12-06
Estimated Expiration
2044-08-28

AI Technical Summary

Technical Problem

In the prior art, the data query speed of business data tables is slow, and the threshold for accelerated query is high, and the business personnel are insufficient, resulting in low query efficiency.

Method used

By obtaining historical query information, extracting historical query characteristics, filtering candidate business data tables, determining the distribution weight and query frequency of data columns, and sorting data files according to filter weights to improve query efficiency.

Benefits of technology

It improves the efficiency of business data query, reduces the number of data files and lines required for query, optimizes the storage space, and conforms to the tendency of business query.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN119088805A_ABST
    Figure CN119088805A_ABST
Patent Text Reader

Abstract

The embodiment of the invention provides a data table sorting method and device, electronic equipment and a storage medium, and the method comprises the steps: obtaining historical query information, and extracting historical query features which at least comprise a to-be-queried table identifier and a filtering condition; screening to obtain a candidate business data table according to the to-be-queried table identifier; for each candidate service data table, determining the distribution weight of each data content in the data column and the historical frequency of querying the data column; determining the filtering weight of each data column according to the historical frequency and the distribution weight; and sorting the data contents in all the data files included in the candidate service data table according to the size of the filtering weight to obtain a sorted data file, so that the efficiency of carrying out data query by utilizing the sorted data file is improved. And the same data contents in the sorted data files are distributed together, so that compression is facilitated, and the storage space is reduced.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present invention relates to the technical field of data query, and in particular to a data table sorting method, device, electronic equipment and storage medium. Background Art

[0002] In order to facilitate the management of the company's business data, the business data can be stored in a business data table. A data table often represents a file directory, which contains multiple data files. These data files store the data of this table.

[0003] Since multiple data files are stored in different files in disorder, when the electronic device queries the data content in the business data table according to the query instructions of the business personnel, it needs to traverse the data files in multiple files to obtain the query results. The query speed is not fast enough, and the threshold for using business accelerated query is relatively high for enterprise personnel. In addition, the business personnel have little motivation to implement accelerated query and are relatively passive. Summary of the invention

[0004] The purpose of the embodiments of the present invention is to provide a data table sorting method, device, electronic device and storage medium to improve the speed of business data query. The specific technical solution is as follows:

[0005] In a first aspect, an embodiment of the present invention provides a data table sorting method, wherein each business data table includes data files stored in multiple partitions, and the business data table stores data content by column, and the method includes:

[0006] Acquire historical query information, and extract historical query features in the historical query information, wherein the historical query features at least include a table identifier to be queried and a filtering condition;

[0007] According to the to-be-queried table identifier included in the historical query feature, screening out candidate business data tables from the business data tables;

[0008] For each candidate business data table, determine the distribution weight of each data content in the data column in the data column, and the historical frequency of querying the data column in the candidate business data table based on the filtering condition included in the historical query feature;

[0009] Determine the filtering weight of each data column in the candidate business data table according to the historical frequency and the distribution weight;

[0010] The data contents in all data files included in the candidate business data table are sorted according to the filter weights of the data columns to obtain sorted data files, so that when data query is needed, the sorted data files can be used to perform data query and obtain query results.

[0011] Optionally, the step of determining the distribution weight of each type of data content in the data column includes:

[0012] Determine the quantity of each type of data content contained in a target data column in a data file included in the candidate business data table, wherein the target data column is a data column corresponding to a filtering condition included in the historical query feature;

[0013] The ratio of the quantity of each type of data content to the total quantity of data content included in the target data column is calculated as the distribution weight of the data content in the target data column.

[0014] Optionally, the step of determining the filtering weight of each data column in the candidate business data table according to the historical frequency and the distribution weight includes:

[0015] Determining the uniformity of distribution of the data content in the data column according to the distribution weight of each type of data content in the data column;

[0016] For each data column, the filtering weight of the data column is determined according to the historical frequency corresponding to the data column and the distribution uniformity, wherein the size of the filtering weight is positively correlated with the historical frequency and the distribution uniformity.

[0017] Optionally, the step of determining the filtering weight of the data column according to the historical frequency corresponding to the data column and the distribution uniformity includes:

[0018] The filter weight of the data column is calculated according to the following formula:

[0019] S = R × (1-A);

[0020] Among them, S is the filtering weight of the data column, R is the historical frequency corresponding to the data column, A is the distribution weight, and 1-A is the distribution uniformity.

[0021] Optionally, the step of determining the distribution weight of each type of data content in the data column includes:

[0022] Determine, among the data columns included in the candidate business data table, a first preset number of data columns with the largest corresponding historical frequencies as candidate data columns;

[0023] For each candidate data column, determine the quantity of each type of data content contained in the candidate data column;

[0024] Calculating the ratio of the quantity of each type of data content to the total quantity of data content included in the candidate data column as the distribution weight of the data content in the candidate data column;

[0025] According to the distribution weight of each data content in the candidate data column, a second preset number of distribution weights with the highest distribution weights are determined as the distribution weight of each data content in the data column.

[0026] Optionally, the step of sorting the data contents in all the data files included in the candidate business data table according to the filtering weights of the data columns to obtain sorted data files includes:

[0027] The data contents in all the data files included in the candidate business data table are sorted according to a preset sorting method, wherein the preset sorting method is determined by the attributes of the data contents included in the candidate data column with the highest filtering weight included in the candidate business data table.

[0028] Optionally, the method further includes:

[0029] Acquire information to be queried, and extract query features from the information to be queried;

[0030] According to the query feature, determining the target business data table indicated by the information to be queried in the business data table, wherein the business data table is obtained by sorting according to the data table sorting method;

[0031] In the target business data table, the data content indicated by the information to be queried is queried based on the query feature to obtain a query result.

[0032] Optionally, the target business data table includes target data files stored in multiple partitions, and the query feature includes at least a query table identifier and a filter condition;

[0033] The step of determining, in the business data table, a target business data table indicated by the information to be queried according to the query characteristics comprises:

[0034] In the business data table, searching for a target data file included in the target business data table indicated by the query table identifier;

[0035] The step of querying the data content indicated by the information to be queried based on the query feature in the target business data table to obtain the query result includes:

[0036] In the target data file, according to the filtering condition and the order corresponding to the filtering condition represented by the preset sorting method, determine the target data file to be queried;

[0037] The data content corresponding to the filtering condition is queried from the target data file to be queried to obtain a query result.

[0038] In a second aspect, an embodiment of the present invention provides a data table sorting device, wherein each business data table includes data files stored in multiple partitions, and the business data table stores data content by column, and the device includes:

[0039] A historical query feature extraction module, used to obtain historical query information and extract historical query features in the historical query information, wherein the historical query features at least include a table identifier to be queried and a filtering condition;

[0040] A business data table screening module, used for screening candidate business data tables from the business data table according to the to-be-queried table identifier included in the historical query feature;

[0041] A data information determination module, for determining, for each candidate business data table, the distribution weight of each data content in the data column, and the historical frequency of querying the data column in the candidate business data table based on the filtering conditions included in the historical query feature;

[0042] A filtering weight determination module, used to determine the filtering weight of each data column in the candidate business data table according to the historical frequency and the distribution weight;

[0043] The data file sorting module is used to sort the data contents in all data files included in the candidate business data table according to the filtering weights of the data columns to obtain sorted data files, so that when data query is needed, the sorted data files can be used to perform data query and obtain query results.

[0044] In a third aspect, an embodiment of the present invention provides an electronic device, including a processor, a communication interface, a memory and a communication bus, wherein the processor, the communication interface and the memory communicate with each other via the communication bus;

[0045] Memory, used to store computer programs;

[0046] The processor is used to implement any method step described in the first aspect when executing the program stored in the memory.

[0047] In a fourth aspect, an embodiment of the present invention provides a computer-readable storage medium, wherein the computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the method steps described in any one of the first aspects are implemented.

[0048] Beneficial effects of the embodiments of the present invention:

[0049] In the solution provided by the embodiment of the present invention, the electronic device obtains historical query information and extracts historical query features in the historical query information, wherein the historical query features at least include a table identifier to be queried and a filtering condition, and then screens the candidate business data table from the business data table according to the table identifier to be queried included in the historical query features. For each candidate business data table, the distribution weight of each data content in the data column and the historical frequency of querying the data column in the candidate business data table are determined. Then, the filtering weight of each data column in the candidate business data table is determined based on the historical frequency and distribution weight, and the data content in the data file included in the candidate business data table is sorted according to the data column with the largest query, to obtain a sorted data file, so that the efficiency of data query using the sorted data file can be improved.

[0050] Since the above filtering weight is determined based on the query frequency of the business personnel on the data column in the business data table and the distribution of each data content in the data column, the above sorted data file is more in line with the business personnel's tendency to query in the business data table. Performing data query in the sorted data file improves the query efficiency of the business personnel, and the same data content in the sorted business data table is distributed together, which is convenient for compressing the business data table and reducing the storage space of the business data table. Of course, any product or method implementing the present invention does not necessarily need to achieve all the advantages described above at the same time. BRIEF DESCRIPTION OF THE DRAWINGS

[0051] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art are briefly introduced below.

[0052] Figure 1 A flowchart of a data table sorting method provided by an embodiment of the present invention;

[0053] Figure 2 Based on Figure 1 A schematic diagram of extracting historical query features from historical query information in the illustrated embodiment;

[0054] Figure 3 for Figure 1 A schematic diagram of a sorted data file of the illustrated embodiment;

[0055] Figure 4 Another flow chart of a data table sorting method provided by an embodiment of the present invention;

[0056] Figure 5 for Figure 4A specific flow chart of step S402 and step S403 in the illustrated embodiment;

[0057] Figure 6 Based on Figure 1 A schematic flow chart of a data table sorting method according to the illustrated embodiment;

[0058] Figure 7 Based on Figure 6 A schematic diagram of a method of sorting data files in the illustrated embodiment;

[0059] Figure 8 A schematic diagram of the structure of a data table sorting method provided by an embodiment of the present invention;

[0060] Fig. 9 A schematic diagram of the structure of a data table sorting device provided by an embodiment of the present invention;

[0061] Fig.10 The present invention is a schematic diagram of the structure of an electronic device provided by an embodiment of the present invention. DETAILED DESCRIPTION

[0062] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments in the present invention, all other embodiments obtained by ordinary technicians in this field based on this application belong to the scope of protection of the present invention.

[0063] In order to reduce the number of data files and data rows that need to be scanned and improve the data query speed when querying the data content in the business data table, the embodiment of the present invention provides a data table sorting method, device, electronic device and storage medium. The following first introduces a data table sorting method provided by an embodiment of the present invention.

[0064] A data table sorting method provided in an embodiment of the present invention can be applied to any electronic device that needs to perform business data query, for example, it can be a server, a query platform, a database management device, etc., without specific limitation here, for the sake of clear description, it is referred to as an electronic device in the following.

[0065] like Figure 1 As shown, a data table sorting method, each business data table includes data files stored in multiple partitions, and the business data table stores data content by column;

[0066] S101, obtaining historical query information, and extracting historical query features from the historical query information,

[0067] Wherein, the historical query characteristics at least include the identification of the table to be queried and the filtering conditions;

[0068] S102, screening out candidate business data tables from the business data tables according to the to-be-queried table identifier included in the historical query feature;

[0069] S103, for each candidate business data table, determining the distribution weight of each data content in the data column in the data column, and the historical frequency of querying the data column in the candidate business data table based on the filtering condition included in the historical query feature;

[0070] S104, determining a filtering weight for each data column in the candidate business data table according to the historical frequency and the distribution weight;

[0071] S105, sorting the data contents in all the data files included in the candidate business data table according to the filter weights of the data columns to obtain sorted data files, so that when data query is needed, the sorted data files can be used to perform data query to obtain query results.

[0072] It can be seen that in the solution provided by the embodiment of the present invention, the electronic device obtains historical query information and extracts historical query features in the historical query information, wherein the historical query features at least include the table identifier to be queried and the filtering condition, and then the candidate business data table is screened from the business data table according to the table identifier to be queried included in the historical query features. For each candidate business data table, the distribution weight of each data content in the data column in the data column and the historical frequency of querying the data column in the candidate business data table are determined. Then, the filtering weight of each data column in the candidate business data table is determined based on the historical frequency and distribution weight, and the data content in the data file included in the candidate business data table is sorted according to the data column with the largest query, to obtain the sorted data file, so that the efficiency of data query using the sorted data file can be improved.

[0073] Since the above-mentioned filtering weight is determined based on two factors: the query frequency of the business personnel on the data column in the business data table and the distribution of each data content in the data column, the above-mentioned sorted data file is more in line with the business personnel's tendency to query in the business data table. Performing data queries in the sorted data file improves the query efficiency of the business personnel, and the same data contents in the sorted business data table are distributed together, which facilitates the compression of the business data table and reduces the storage space of the business data table.

[0074] In actual situations, a data table often represents a file directory, and the amount of data content contained in the business data table is very large, and it is difficult to store the business data table in one file. Therefore, the data in the business data table will be divided into multiple data files for storage, and these data files are all in one data directory to relieve the storage pressure of the electronic device. In addition, since column storage has a higher data compression rate and query efficiency than row storage, in order to facilitate data compression and data query, the above-mentioned business data table can store business data in a column storage manner, and the above-mentioned historical query features can at least include the table identifier to be queried and the filter condition.

[0075] Since business personnel frequently query business data, and each query of business data requires the use of query information, the electronic device can collect a lot of historical query information and use this historical query information as historical query information. In the above step S101, the electronic device can obtain the historical query information and extract the historical query features in the historical query information to obtain the historical query features.

[0076] The above historical query information may be a SQL (Strutured Query Language) statement, which is not specifically limited here.

[0077] In one embodiment, the query information submitted by the business personnel is forwarded through the Pilot module in the SQL gateway. The Pilot module in the SQL gateway can collect the query information submitted by the business personnel and store the collected query information. The electronic device can obtain the query information stored by the Pilot module according to a preset period as historical query information. Among them, the preset period can be 1 day, 3 days, 1 week, etc., which are all reasonable and are not specifically limited here.

[0078] After acquiring the historical query information, the electronic device may extract the historical query features in each piece of historical query information and store the extracted historical query features. The steps of acquiring the historical query information and extracting the historical query features in the historical query information may be performed by a parsing module in the electronic device.

[0079] In one embodiment, after obtaining the historical query information, for each piece of historical query information, the parser in the decision module can parse the historical query information and parse each piece of historical query information into an abstract syntax tree that can be recognized by the electronic device. Then the feature extractor in the decision module acts as an observer and the syntax tree as the observed. The feature extractor uses the observer pattern to traverse each node in the abstract syntax tree multiple times to extract the historical query features, wherein the nodes in the abstract syntax tree correspond to each feature in the historical query information.

[0080] For example, Figure 2 As shown, the abstract syntax tree obtained by parsing the historical query information "SELECT*FROM employee WHERE age='30'", the first-level node 201 in the abstract syntax tree indicates that the historical query information is a SELECT statement; the second-level node 202 indicates the table identifier to be queried, that is, the business data query is performed in the "table employee"; the second-level node 203 and the second-level node 204 together constitute the filtering condition, that is, the data content of "value 30" is queried in the "column age"; the second-level node 203 indicates the filter column name used in the query, that is, the data filtering range is in the "column age"; the second-level node 204 indicates the query condition, that is, the data content of query condition "=30", and all the above nodes are traversed multiple times to extract the second-level nodes 202, 203 and 204 as the query features of the historical query information "SELECT*FROM employeeWHERE age='30'".

[0081] Among them, in the abstract syntax tree obtained by parsing the historical query information, the query features in the secondary node "WHERE col(column)_a='xxx'" or "WHERE col_a in('xxx','yyy')" are the filtering conditions in the historical query information submitted by the business personnel.

[0082] Although many different business data tables are stored in the database, not every data content in each business data table has been queried. Therefore, in order to improve the efficiency of sorting the data tables, in the above step S102, the electronic device can filter out candidate business data tables from the business data tables based on the identifier of the table to be queried included in the historical query characteristics.

[0083] The electronic device can pull historical query features within a certain time range according to a preset period for sorting the business data table, where the preset period can be 1 week, 15 days, 30 days, etc., and the time range can be set according to the time span of the stored historical query features, for example, the time range is 2024-7-1 to 2024-7-3, 2024-7-1 to 2024-7-5, 2024-7-1 to 2024-7-10, etc., and no specific limitation is made here.

[0084] After the historical query features are acquired, a business data table having a name consistent with the table name of the table to be queried is screened out from multiple business data tables as a candidate business data table according to the table name of the table to be queried included in the historical query features.

[0085] The step of filtering the candidate business data table from the business data table according to the to-be-queried table identifier included in the historical query characteristics may be performed by a decision maker in a decision module of the electronic device.

[0086] For example, three historical query features are obtained, namely ""SELECT""table name user""column level = 2"", ""SELECT""table name employee""column age = 30"", and ""SELECT""table name user""column prov = HeBei"". Then, according to the to-be-queried table identifier "table name user" in the first historical query feature and the to-be-queried table identifier "table name user" in the third historical query feature, the user information table with the table name user is determined as a candidate business data table in multiple business data tables; according to the to-be-queried table identifier "table name employee" in the second historical query feature, the employee information table with the table name employee is determined as a candidate business data table in multiple business data tables.

[0087] In order to determine the filtering weight of each data column in the candidate business data table, it is necessary to first determine the distribution weight of each data content in the data column, and the historical number and frequency of queries on the data column in the candidate business data table. Therefore, in the above step S103, the electronic device determines, for each candidate business data table, the distribution weight of each data content in the data column, and the historical frequency of queries on the data column in the candidate business data table based on the filtering conditions included in the historical query characteristics.

[0088] In one embodiment, for each of the above-mentioned candidate business data tables, the distribution of each type of data content in the business candidate data table in the candidate data column is determined as the distribution weight; and the number of each type of filtering conditions included in the acquired historical query features is used as the historical frequency of querying the corresponding data column in the candidate data table.

[0089] For example, for the user information table as a candidate business data table, which contains data columns such as id column, age column, gender column, prov column, and level column, the distribution weight of each id content in the id column, the distribution weight of each age value in the age column, the distribution weight of each of the two genders in the gender column, the distribution weight of each province in the prov column, and the distribution weight of each level value in the level column are determined. And determine the number of data columns used in each type of filtering condition contained in all the historical query features obtained, and determine the historical frequency of the corresponding data column in the candidate data table based on the number of the above data columns.

[0090] For example, all the filtering conditions obtained include 50 times of "column age", 45 times of "column level", 30 times of "column prov", and 0 times of "column gender". Then, the historical frequency of filtering the age column in the user information table is 0.4, the historical frequency of filtering the level column in the user information table is 0.36, the historical frequency of filtering the prov column in the user information table is 0.24, and the historical frequency of filtering the gender column in the user information table is 0.

[0091] In one implementation, the historical frequency of querying the data columns in the candidate business data table can be determined first. For data columns with a historical frequency of 0, it means that the business personnel have not queried the data column before, so the distribution weight of each data content in the data column can be determined to improve data processing efficiency.

[0092] For example, for the user information table as a candidate business data table, all the historical query features obtained include 50 times of "column age", 45 times of "column level", 30 times of "column prov", and 0 times of "column gender". The historical frequency of querying the age column in the user information table is 0.4, the historical frequency of querying the level column in the user information table is 0.36, the historical frequency of querying the prov column in the user information table is 0.24, and the historical frequency of querying the gender column in the user information table is 0.

[0093] In this way, when determining the distribution weight of each data content in a data column, it is not necessary to calculate the distribution weights of the two genders in the gender column. Instead, the distribution weight of each id content in the id column, the distribution weight of each age value in the age column, the distribution weight of each province in the prov column, and the distribution weight of each level value in the level column are calculated.

[0094] In order to clearly know which data column to use to sort the data content in the data file, after determining the distribution weight of each data content in the data column and the historical number and frequency of queries on the data column in the candidate business data table, it is necessary to further determine the filtering weight of each data column. In the above step S104, the electronic device can determine the filtering weight of each data column in the candidate business data table based on the historical frequency and the distribution weight.

[0095] Since the historical frequency represents the frequency with which business personnel query the data column in the business data table, when a data column in the business data table is queried frequently, it means that for the business data table, the data content that the business personnel are most concerned about is the data content contained in the data column with high query frequency. The next time the business personnel query the data content in the business data table, the probability that the data content to be queried is the data content in the data column with high query frequency is the highest.

[0096] The distribution weight represents the distribution of each type of data content in the data column. The closer the quantity of each type of data content in the data column is, the more uniform the distribution of the data content in the data column is. When querying each type of data content in the data column, more other types of data content need to be filtered out, thereby improving query efficiency.

[0097] Therefore, the filtering weight of each data column in the candidate business data table determined according to the historical frequency and the distribution weight represents the frequency of querying each data column and the distribution of the data content in each data column.

[0098] In order to make the sorted data files meet the business personnel's inclination to perform data queries, after obtaining the filtering weight of each data column in the candidate business data table, in the above step S105, the data contents in all data files included in the candidate business data table are sorted according to the size of the filtering weight of the data column to obtain the sorted data files, so that when data query is needed, the sorted data files can be used to perform data query and obtain query results.

[0099] After obtaining the filtering weight in the data column, the data contents in all the data files included in the candidate business data table are sorted according to the filtering weight of the data column. For example, all the above data files can be sorted according to the data column with the largest filtering weight to obtain the sorted data files.

[0100] For example, if the candidate business data table is a user information table, the data content arrangement in the user information data file before sorting is as shown in Table 1 below.

[0101] Table 1

[0102] id age gender level prov a11111 13 male 1 Beijing a22222 50 male 5 ShangHai a33333 22 female 10 HeBei a44444 26 female 3 AnHui a55555 13 male 2 Beijing …… …… …… …… …… annnnn xx x y z

[0103] After determining that the data column with the largest filtering weight in the user information table is the age column, the data content in the user information data file included in this user information table is sorted according to the age column, and the data content in the id column, gender column, prov column, and level column in the user information data file are sorted from small to large according to the age value in the age column, to obtain the sorted data content of the user information data file as shown in Table 2 below.

[0104] Table 2

[0105] id age gender level prov a11111 13 male 1 Beijing a55555 13 male 2 Beijing a33333 22 female 10 HeBei a44444 26 female 3 AnHui a22222 50 male 5 ShangHai …… …… …… …… …… annnnn xx x y z

[0106] As can be seen from Table 2, all the data contents in the sorted user information data file are arranged in ascending order according to the values ​​in the age column. Each user information data file corresponds to an age range, thus obtaining the following: Figure 3 The four user information data files shown determine the age range.

[0107] Among them, Figure 3 As shown, before sorting, the age sorting in the four user information data files is random, and the specific age range of each user information data file is unclear. The age range in the first user information data file is 32 to unknown (unknown number), the age range in the second user information data file is 23 to unknown, the age range in the third user information data file is 20 to unknown, and the age range in the fourth user information data file is 47 to unknown. After sorting the above four user information data files according to the age column, the age ranges of the four user information data files are 20 to 30, 30 to 40, 40 to 50, and 50 to unknown.

[0108] It can be seen that in the solution provided by the embodiment of the present invention, the electronic device obtains historical query information and extracts historical query features in the historical query information, wherein the historical query features at least include the table identifier to be queried and the filtering condition, and then the candidate business data table is screened from the business data table according to the table identifier to be queried included in the historical query features. For each candidate business data table, the distribution weight of each data content in the data column in the data column and the historical frequency of querying the data column in the candidate business data table are determined. Then, the filtering weight of each data column in the candidate business data table is determined based on the historical frequency and distribution weight, and the data content in the data file included in the candidate business data table is sorted according to the data column with the largest query, to obtain the sorted data file, so that the efficiency of data query using the sorted data file can be improved.

[0109] Since the above-mentioned filtering weight is determined based on two factors: the query frequency of the business personnel on the data column in the business data table and the distribution of each data content in the data column, the above-mentioned sorted data file is more in line with the business personnel's tendency to query in the business data table. Performing data queries in the sorted data file improves the query efficiency of the business personnel, and the same data contents in the sorted business data table are distributed together, which facilitates the compression of the business data table and reduces the storage space of the business data table.

[0110] After the candidate business data tables are screened, in order to sort the candidate business data tables according to the filtering weights of the data columns in the candidate data tables, it is necessary to first determine the distribution weight of each data content in the data column used to determine the filtering weight.

[0111] As an implementation of an embodiment of the present invention, the above step of determining the distribution weight of each data content in the data column in the data column may include at least the following two implementations:

[0112] In a first implementation manner, the quantity of each type of data content included in the target data column in the data file included in the candidate business data table is determined.

[0113] Since business personnel may not use every data column in the business data table for filtering, the historical frequency of filtering some data columns in the business data table may be 0. For data columns with a historical frequency of 0, it means that the probability of business personnel filtering and selecting these data columns is extremely low. In this case, the distribution weight of the data content in these data classes with a historical frequency of 0 can be determined to improve the efficiency of data processing.

[0114] The filtering condition included in the historical query feature represents what data content in the candidate business table is to be queried, so the electronic device can determine the data column queried by the business personnel in the data column of the candidate data table as the target data column according to the filtering condition included in the historical query feature.

[0115] After determining the target data column, the number of each data content contained in the target data column in the data file included in the candidate business data table is determined. For example, it is reasonable to determine the number of each age value in the age column determined as the target data column in the user information table, or the number of each province in the prov column determined as the target data column.

[0116] In one embodiment, when more than one data column is determined as the target data column, since the candidate business data table includes data files stored in multiple files, when determining the quantity of each data content contained in the target data column, the quantity of each data content contained in each target data column can be first determined in the same data file, and then the sum of the quantities of each data content contained in each target data column determined in each data file is used as the quantity of each data content contained in each target data column.

[0117] For example, the user information table contains four user information data files, namely user information data file 1, user information data file 2, user information data file 3, and user information data file 4. When the age column and the prov column in the user information table are both determined as target data columns, the quantity of each data content contained in the age column and the prov column can be determined in user information data file 1 first, and then the quantity of each data content contained in the age column and the prov column can be determined in user information data files 2, 3, and 4. The sum of the quantities of each data content contained in the age column and the prov column determined in user information data files 1, 2, 3, and 4 can be used as the quantity of each data content in the age column and the prov column.

[0118] In one embodiment, when more than one data column is determined as the target data column, since the candidate business data table includes data files stored in multiple files, when determining the quantity of each data content contained in the target data column, the quantity of each data content contained in one of the target data columns can be first determined in each data file, and the sum of the quantities of each data content contained in the target data column determined in each data file is used as the quantity of each data content contained in the target data column.

[0119] Furthermore, the quantity of each data content contained in another target data column can be determined in each data file, and the sum of the quantities of each data content contained in the target data column determined in each data file can be used as the quantity of each data content contained in the target data column, until the quantity of each data content contained in each target data column is determined.

[0120] For example, the user information table contains four user information data files, namely user information data file 1, user information data file 2, user information data file 3, and user information data file 4. When the age column and prov column in the user information table are both determined as target data columns, the quantity of each data content contained in the age column can be determined in user information data file 1 first.

[0121] Then continue to determine the quantity of each data content in the age column in user information data files 2, 3, and 4, and then use the sum of the quantity of each data content in the age column determined in user information data files 1, 2, 3, and 4 as the quantity of each data content contained in the age column. Determine the quantity of each data content contained in the prov column in user information data file 1, and then continue to determine the quantity of each data content contained in the prov column in user information data files 2, 3, and 4, and then use the sum of the quantity of each data content in the prov column determined in user information data files 1, 2, 3, and 4 as the quantity of each data content contained in the prov column.

[0122] After determining the quantity of each data content contained in the target data column in the business data table, the ratio of the quantity of each data content to the total quantity of data content contained in the target data column is calculated as the distribution weight of the data content in the target data column.

[0123] If the target data column contains more data contents of the same kind, the ratio of the data contents to the total number of data contents contained in the target data column is greater, and the distribution weight of the data contents in the data column is higher, indicating that the distribution of the data contents in the target data column is not uniform; if the numbers of the data contents in the data column are close, the distribution weights of the data contents in the target data column are also close, indicating that the distribution of the data contents in the target data column is relatively uniform.

[0124] In other words, the distribution weight represents the distribution of each type of data content in the data column. The closer the distribution weight of each type of data content in the target data column is, the more uniform the distribution of the data content in the target data column is, and when querying each type of data content in the data column, more other types of data content need to be filtered out, thereby improving query efficiency.

[0125] It can be seen that in this embodiment, the electronic device can determine the quantity of each type of data content contained in the target data column in the data file included in the candidate business data table, and then calculate the ratio of the quantity of each type of data content to the total quantity of data content contained in the target data column as the distribution weight of the data content in the target data column. Since the distribution weight characterizes the distribution of each type of data content in the target data column, it is convenient to determine the filtering weight of the target data column according to the distribution weight, so as to realize sorting of the business data table according to the size of the data column filtering weight.

[0126] In a second implementation manner, among the data columns included in the candidate business data table, a first preset number of data columns with the largest corresponding historical frequencies are determined as candidate data columns.

[0127] Since the candidate business data table contains many data columns, and in fact, business personnel often query only some of the data columns in the candidate data table, and the other data columns are queried relatively less frequently, in order to reduce the amount of data to be processed and improve the efficiency of determining data distribution weights, the data columns with the highest corresponding query history frequency can be determined from the data columns included in the candidate business data table as the candidate data columns. The specific value of the first preset number can be set according to actual needs, such as TOP3 columns, TOP5 columns, etc., which are all reasonable and are not specifically limited here.

[0128] After determining a first preset number of candidate data columns, for each candidate data column, determine the quantity of each type of data content contained in the candidate data column, and then calculate the ratio of the quantity of each type of data content to the total quantity of data content contained in the candidate data column in which it is located as the distribution weight of the data content in the candidate data column.

[0129] In one embodiment, when more than one data column is determined as a candidate data column, since the candidate business data table includes data files stored in multiple files, when determining the quantity of each data content contained in the candidate data column, the quantity of each data content contained in one of the candidate data columns can be first determined in each data file.

[0130] Then, the sum of the quantities of each data content contained in the candidate data column determined in each data file is used as the quantity of each data content contained in the candidate data column. Then, the quantity of each data content contained in another candidate data column is determined in each data file, and the sum of the quantities of each data content contained in the candidate data column determined in each data file is used as the quantity of each data content contained in the candidate data column, until the quantity of each data content contained in each candidate data column is determined.

[0131] In one embodiment, when more than one data column is determined as a candidate data column, since the candidate business data table includes data files stored in multiple files, when determining the quantity of each data content contained in the candidate data column, the quantity of each data content contained in each candidate data column can be first determined in the same data file, and then the sum of the quantities of each data content contained in each candidate data column determined in each data file is used as the quantity of each data content contained in each candidate data column.

[0132] After determining the distribution weight of each data content in the candidate data column, in order to reduce the amount of data that needs to be calculated when subsequently determining the filtering weight, the second preset number of distribution weights with the highest distribution weights can be determined according to the distribution weight of each data content in the candidate data column as the distribution weight of each data content in the data column. The specific value of the second preset number can be set according to actual needs, for example, it can be 3, 5, 10, etc., which are all reasonable and are not specifically limited here.

[0133] It can be seen that in this embodiment, the electronic device can first determine the first preset number of data columns with the highest corresponding historical frequency as candidate data columns, and for each candidate data column, obtain the ratio of the number of each data content to the total number of data content contained in the candidate data column in which it is located as the distribution weight of the data content in the candidate data column. According to the distribution weight of each data content in the candidate data column, the second preset number of distribution weights with the highest distribution weights are determined as the distribution weight of each data content in the data column. The amount of data used in determining the distribution weight of the data column and the subsequent determination of the data column filtering weight is reduced, and the calculation efficiency is improved.

[0134] As an implementation method of an embodiment of the present invention, after obtaining the distribution weight of each data content in the data column, in order to determine which data column in the candidate data table is used to sort the data table, it is necessary to first determine the filtering weight of the data column in the candidate data table according to the historical frequency and the distribution weight. The above step of determining the filtering weight of each data column in the candidate business data table according to the historical frequency and the distribution weight may include:

[0135] Step A: determining the distribution uniformity of the data content in the data column according to the distribution weight of each data content in the data column.

[0136] The distribution weight of each data content in the data column represents the distribution of each data content in the data column. When the distribution weights of each data content are similar, it means that the data distribution of the data column is relatively uniform. When the difference in the distribution weights of each data content is large, it means that the data distribution of the data column is poor.

[0137] Whether the distribution of data content in a data column is uniform or not represents the difficulty of searching for data content in the data column. If the distribution of data content in a data column is uniform, when searching for data content that meets the conditions in the data column, more data content that does not meet the conditions needs to be filtered out, and the speed of searching for data content is faster; if the distribution of data content in a data column is uneven, when searching for data content that meets the conditions in the data column, less data content that does not meet the conditions needs to be filtered out, and the speed of searching for data content is slower.

[0138] Step B: for each data column, determine the filtering weight of the data column according to the historical frequency corresponding to the data column and the distribution uniformity.

[0139] The more historical frequencies of queries corresponding to a data column, the higher the frequency of queries for the data column, and the greater the possibility that business personnel will query the data column in the candidate business data table in the future; the uniformity of the distribution of data content in a data column represents the difficulty of querying data content in the data column. The more uniform the distribution of data content in a data column, the easier it is to query data content in the data column. Therefore, the filtering weight of a data column is positively correlated with the historical frequency corresponding to the data column and the uniformity of the distribution of data content in the data column.

[0140] In one embodiment, when determining the filtering weight of a data column, the calculation can be performed based on the historical frequency and distribution uniformity of the data column. For example, the candidate business data table is a user information table, where the historical frequency of the age column is 0.4 and the distribution uniformity of the age column is 0.4, then the filtering weight of the age column is 0.16; the historical frequency of the level column is 0.36 and the distribution uniformity of the level column is 0.6, then the filtering weight of the level column is 0.216.

[0141] In another embodiment, in different business scenarios, when determining the filtering weight of a data column, the importance of the historical frequency or historical frequency and the distribution weight is different. Therefore, after obtaining the historical frequency or historical frequency corresponding to the data column, the historical frequency or historical frequency and the distribution weight can be weighted, and then the filtering weight of the data column is determined based on the weighted historical frequency or historical frequency and the distribution weight.

[0142] The weights corresponding to the historical frequency and the distribution weight can be set according to the actual situation. For example, the weight set for the historical frequency is 0.7, and the weight set for the distribution weight is 0.3; the weight set for the historical frequency or the historical frequency is 0.4, and the weight set for the distribution weight is 0.6, which are all reasonable and are not specifically limited here.

[0143] It can be seen that in this embodiment, the electronic device can determine the distribution uniformity of the data content in the data column according to the distribution weight of each data content in the data column, and then determine the filtering weight of the data column according to the historical frequency and distribution uniformity corresponding to the data column. The filtering weight of the data column represents the frequency of querying the data column and the difficulty of querying the data content in the data column, which facilitates the subsequent determination of the way to sort the business data table based on the filtering weight of the data column.

[0144] As an implementation of an embodiment of the present invention, the step of determining the filtering weight of the data column according to the historical frequency and distribution uniformity corresponding to the data column may include:

[0145] The filter weight of the data column is calculated according to the following formula:

[0146] S = R × (1-A);

[0147] Among them, S is the filtering weight of the data column, R is the historical frequency corresponding to the data column, A is the distribution weight, and 1-A is the degree of distribution uniformity.

[0148] Since the filtering weight of a data column is positively correlated with the historical frequency corresponding to the data column and the distribution uniformity of the data content in the data column, the product of the historical frequency corresponding to the data column and the distribution uniformity of the data content in the data column can be used as the filtering weight of the data column. For example, in the user information table, the historical frequency corresponding to the age column is 0.4, the distribution weight of the age column is 0.6, and the distribution uniformity of the age column is 0.4, then the filtering weight of the age column is 0.16; the historical frequency of the level column is 0.36, the distribution weight of the level column is 0.4, and the distribution uniformity of the level column is 0.6, then the filtering weight of the level column is 0.216.

[0149] It can be seen that in this embodiment, the electronic device can use the above formula to calculate the filtering weight of the data column, and can accurately calculate the filtering weight.

[0150] As an implementation of an embodiment of the present invention, after obtaining the data column with the largest filtering weight, when sorting the data contents in the data file according to the filtering weights of the data columns, different types of data contents are sorted differently.

[0151] Based on this, the step of sorting the data contents in the data file included in the candidate business data table according to the filtering weights of the data columns to obtain the sorted data file may include:

[0152] The data contents in the data files included in the candidate business data table are sorted according to a preset sorting method.

[0153] The electronic device may preset different sorting methods according to different types of data content, and the preset sorting methods are determined according to the attributes of the data content included in the data column. For example, when the data content included in the candidate data column with the highest filtering weight included in the candidate business data table is a numerical value, the data content in the data file may be sorted in an ascending order or a descending order based on the magnitude of the numerical value.

[0154] For another example, when the data content included in the candidate data column with the highest filtering weight included in the candidate business data table is text, the data content in the data file can be sorted in alphabetical order based on the first letter of the text, which is reasonable and not specifically limited here.

[0155] It can be seen that in this embodiment, the electronic device can sort the data content in the data file included in the candidate business data table according to a preset sorting method, clarify the sorting method of data content with different attributes, and improve the sorting efficiency.

[0156] As an implementation of the present invention, after sorting the data contents in all data files included in the candidate business data table, when the business personnel again query data in the business data table, they can use the sorted data files to query data.

[0157] In this case, if Figure 4 As shown, the above method may also include:

[0158] S401, obtaining information to be queried, and extracting query features from the information to be queried.

[0159] The information to be queried is relevant information about the data content to be queried provided by the business personnel. For example, it can be a query statement submitted by the business personnel when querying the required data content, which may include the scope of the business data table to be queried by the business personnel, the data content to be queried, the operating environment of the database storing the business data table, etc.

[0160] For example, if the business personnel want to query the user information whose user level is 2 in the user information table, the information to be queried can be SELECT (select) * FROM (from) user (user) WHERE (where) level (level) = '2'; if the business personnel want to query the employee information whose age is 30 years old in the employee information table, the information to be queried can be SELECT * FROM employee (employee) WHERE age (age) = '30'; if the business personnel want to query the user information whose user address is Beijing, Shanghai, and Shenzhen in the user information table, the information to be queried can be SELECT * FROM user WHERE prov (province) in (BeiJing,ShangHai,ShenZhen), which are all reasonable.

[0161] Since the information to be queried used by business personnel is different, the query features extracted for different information to be queried are also different. As an implementation method, the query features can include a query table identifier, that is, representing which business data tables the data content is queried in. For example, when the information to be queried is "SELECT*FROMemployee1", the query features extracted from this information to be queried include "SELECT", "table name employee1", etc., where employee1 is the query table identifier.

[0162] As an implementation method, the query feature may include a query table identifier and a filter condition, that is, the query table identifier indicates which business data tables are queried for data content, and the filter condition indicates which data content is queried. For example, when the information to be queried is "SELECT id (identification), age FROM employee WHERE age = '30'", the query features obtained by extracting this information to be queried include "SELECT column id, age", "table name employee", "column age = 30", etc., wherein employee is the query table identifier, age = '30' is the filter condition, and age is the column used by the filter condition; when the information to be queried is "SELECT * FROM user WHERE level = '2'", the query features obtained by extracting this information to be queried include "SELECT", "table name user", "column level = 2", etc., wherein user is the query table identifier, level = '2' is the filter condition, and level is the column used by the filter condition.

[0163] S402, determining, in the business data table, a target business data table indicated by the information to be queried according to the query characteristics;

[0164] The business data table is obtained by sorting the business data table according to the number of times the data content included in the business data table is queried and the distribution of the data content in the business data table.

[0165] As an implementation method, the query table identifier contained in the extracted query feature indicates which business data table is to be queried for data content, so the business data table consistent with the query table identifier can be screened out from multiple business data tables as the target business data table according to the query table identifier in the query feature. For example, for the "table name employee1" contained in the query feature, the employee1 table can be determined as the target business data table indicated by the information to be queried among multiple business data tables such as the employee table, the employee1 table, and the user table.

[0166] Since the business data table is obtained by sorting the business data table according to the number of times the data content included in the business data table is queried and the distribution of the data content in the business data table, the data content in the determined target business data table is sorted according to the preset sorting method. For example, for the employee information table determined as the target business data table, since the age column in the employee information table is queried many times and the data content distribution of the age column is relatively uniform, the data content in the employee information table is sorted in descending order according to the size of the data content in the age column.

[0167] S403: In the target business data table, query the data content indicated by the information to be queried based on the query feature to obtain a query result.

[0168] As an implementation method, the filter condition contained in the extracted query feature represents what data content is to be queried in the target business data table, so according to the filter condition in the query feature, the column with the column name consistent with the filter condition can be screened out in the target business data table, and the data content contained in the column is used as the query result. For example, for the "SELECT column id, age" contained in the above query feature, the id column and the age column can be found in the id column, age column, gender column, prov column, duration column and other columns contained in the employee table, and the data content in the id column and the age column is used as the query result indicated by the query information "SELECT id, age FROM employee".

[0169] As an implementation method, the filtering conditions contained in the extracted query features represent the data content that meets the conditions to be queried in the target business data table. Therefore, based on the filtering conditions in the query features, the column name consistent with the filtering conditions can be determined in the target business data table, and the data content that meets the conditions can be found in the data content contained in the column where the column name is located.

[0170] For example, for the "column level" "='2'" included in the query feature, the level column can be found in the id column, age column, gender column, prov column, level column and other columns included in the user table, and according to the data content interval of the level column, all data contents with data content 2 are found in the interval containing "2" as the query result indicated by the query information "SELECT*FROM user WHERE level='2'". Among them, all data contents can include all data contents of the row where the business data with data content 2 in the level column is located, and can include name, age, province, etc., which are not specifically limited here.

[0171] It can be seen that in the solution provided by the embodiment of the present invention, business personnel reduce the number of data files and data rows that need to be scanned when performing data query, and can directly determine the business content interval within which the filter condition is located based on the business content in the sorted target business data table, thereby improving the query speed.

[0172] As an implementation manner of an embodiment of the present invention, the target business data table may include target data files stored in multiple partitions, and the query feature at least includes a query table identifier and a filter condition.

[0173] like Figure 5 As shown, the step of determining the target business data table indicated by the information to be queried in the business data table according to the query characteristics may include:

[0174] S4021, searching the business data table for a target data file included in the target business data table indicated by the query table identifier.

[0175] Since the target business data table includes target data files stored in multiple files, it is necessary to determine all corresponding target data files according to the query table identifier.

[0176] For example, the user information table includes user information data files stored in four files, namely user information data file 1, user information data file 2, user information data file 3, and user information data file 4. Therefore, when the query table identifier in the information to be queried indicates the user information table, the above-mentioned user information data files 1, 2, 3, and 4 are all determined as target data files.

[0177] In this case, the step of querying the data content indicated by the information to be queried based on the query feature in the target business data table to obtain the query result may include:

[0178] S4031, in the target data file, according to the filtering condition and the order corresponding to the filtering condition represented by the preset sorting method, determine the target data file to be queried.

[0179] Since the target business data table includes target data files stored in multiple files, after all target data files are determined according to the table identifier, it is necessary to further determine the target data files where the filter conditions are located according to the filter conditions, and use the target data files containing the filter conditions as the target data files to be queried.

[0180] For example, the user information table determined as the target business data table includes user information data files stored in 4 files, and the data content in the user information data files is sorted according to the age column, wherein the age range contained in user information data file 1 is 20-30, the age range contained in user information data file 2 is 30-40, the age range contained in user information data file 3 is 40-50, and the age range contained in user information data file 4 is 50-unknown.

[0181] When the business personnel needs to query the user information table for users who are 30 years old, user information data file 1 is determined as the target data file, and the query is performed in user information data file 1 and user information data file 2. When the business personnel needs to query the user information table for users who are 45 years old, user information data file 3 is determined as the target data file, and the query is performed in user information data file 3.

[0182] S4032: Query the data content corresponding to the filtering condition from the target data file to be queried to obtain a query result.

[0183] After determining the target data file to be queried, the corresponding data column is determined according to the filtering condition, and a query is performed in the data content of the data column to obtain a query result corresponding to the information to be queried.

[0184] It can be seen that in this embodiment, the electronic device searches for the target data file included in the target business data table indicated by the query table identifier in the business data table, and determines the target data file to be queried in the found target data file according to the filtering condition and the range of the filtering condition respectively contained in each target data file, thereby determining the data content corresponding to the filtering condition in the target data file to be queried, and obtaining the query result. Since each target data file corresponds to a range of data content, the target data file where the filtering condition is located can be directly determined according to the range of the above data content, thereby reducing the number of data files and data rows to be scanned, and improving the efficiency of data query.

[0185] In order to facilitate understanding of the method provided by the present application, the following Figure 6 The specific example shown is used to illustrate:

[0186] S601, obtaining the user's SQL record, that is, obtaining the above historical query information.

[0187] S602, the SQL parser included in the parsing module in the electronic device parses the SQL statement in the SQL record to obtain the AST (Abstract Syntax Tree) corresponding to the SQL statement, and the feature extractor uses the observer pattern to traverse each node in the abstract syntax tree to obtain SQL features and store them persistently; that is, extract the historical query features in the historical query information.

[0188] S603, the decision module in the electronic device obtains the attributes of the business data table, i.e., the identifier of the business data table, the meta information of the table, i.e., the distribution weight of the data content in the data column, and pulls the SQL features within a certain time range. The decision maker in the decision module counts and analyzes the attributes, meta information, and features of the above-obtained table, and uses Rule 1, Rule 2...Rule n provided by the rule model to determine the recommended value of the data column, i.e., the filtering weight, wherein Rule 1 can be for counting and analyzing the attributes, meta information, and features based on the filtering conditions.

[0189] After obtaining the recommended value of the data column, sort the data content in the business data table according to the data column with the highest recommended value. This sorting method of the business data table can be applied to the Sort column recommendation of the Iceberg table and ClickHouse primary key management.

[0190] like Figure 7As shown, the Iceberg table includes data files stored in 4 files. Before sorting, the order of the data contents in the prov columns of the 4 data files is random, and the prov range contained in each data file is unclear. The prov range in the first data file is AnHui~unknown (unknown number), the prov range in the second data file is HeBei~unknown, the prov range in the third data file is AnHui~unknown, and the prov range in the fourth data file is AnHui~unknown.

[0191] The data content in the Sort column of the Iceberg table is sorted according to the sorting method of the prov column with the highest recommended value, and 4 data files are obtained after sorting by the prov column. The prov range in the first user information data file is AnHui~HeBei, the prov range in the second user information data file is HeBei~LiaoNing, the prov range in the third user information data file is LiaoNing~SiChuan, and the prov range in the fourth user information data file is SiChuan~unknown.

[0192] In this way, when you need to query the data content of prov Beijing, you only need to scan the data file in the first file to get all the data content corresponding to the query information. Similarly, the data content of the first primary key of the ClickHouse table can also be sorted according to the sorting method of the prov column with the highest recommended value among the data columns contained. It can be seen that in this embodiment, based on the historical query information of the business, SQL features are extracted, and the tendency of business queries is analyzed, so as to optimize based on the historical query information and intelligently recommend a way to sort the data files.

[0193] In one embodiment, the electronic device may include an analysis module, a decision module and a platform interface, and the structural relationship between the three is as follows: Figure 8 shown.

[0194] The parsing module includes a parser and a feature extractor, wherein the parser is used to parse the SQL statement in the SQL record to obtain the abstract syntax corresponding to the SQL statement; the feature extractor is used to traverse each node in the abstract syntax tree to obtain SQL features.

[0195] The decision module includes a rule model and a decision maker. The rule model is used to provide preset rules used in statistics and analysis of table attributes, meta information, and features; the decision maker is used to statistics and analysis of table attributes, meta information, and features.

[0196] The platform interface is used to display optimization suggestions for the business data table, where the optimization suggestion is the sorting method of the sorted business data table.

[0197] Corresponding to the above data table sorting method, an embodiment of the present invention further provides a business data query device. The following is an introduction to a business data query device provided by an embodiment of the present invention.

[0198] like Fig. 9 As shown, a data table sorting device, each business data table includes data files stored in multiple partitions, and the business data table stores data content by column, and the device includes:

[0199] A historical query feature extraction module 910 is used to obtain historical query information and extract historical query features from the historical query information;

[0200] The historical query feature at least includes an identifier of a table to be queried and a filtering condition.

[0201] The business data table screening module 920 is used to screen the business data tables to obtain candidate business data tables according to the to-be-queried table identifiers included in the historical query characteristics.

[0202] The data information determination module 930 is used to determine, for each candidate business data table, the distribution weight of each data content in the data column and the historical frequency of querying the data column in the candidate business data table based on the filtering conditions included in the historical query characteristics.

[0203] The filtering weight determination module 940 is used to determine the filtering weight of each data column in the candidate business data table according to the historical frequency and the distribution weight.

[0204] The data file sorting module 950 is used to sort the data contents in all the data files included in the candidate business data table according to the filtering weights of the data columns to obtain sorted data files, so that when data query is needed, the sorted data files can be used to perform data query and obtain query results.

[0205] It can be seen that in the solution provided by the embodiment of the present invention, the electronic device obtains historical query information and extracts historical query features in the historical query information, wherein the historical query features at least include the table identifier to be queried and the filtering condition, and then the candidate business data table is screened from the business data table according to the table identifier to be queried included in the historical query features. For each candidate business data table, the distribution weight of each data content in the data column in the data column and the historical frequency of querying the data column in the candidate business data table are determined. Then, the filtering weight of each data column in the candidate business data table is determined based on the historical frequency and distribution weight, and the data content in the data file included in the candidate business data table is sorted according to the data column with the largest query, to obtain the sorted data file, so that the efficiency of data query using the sorted data file can be improved.

[0206] Since the above filtering weight is determined based on the query frequency of the business personnel on the data column in the business data table and the distribution of each data content in the data column, the above sorted data file is more in line with the business personnel's tendency to query in the business data table. Performing data query in the sorted data file improves the query efficiency of the business personnel, and the same data content in the sorted business data table is distributed together, which is convenient for compressing the business data table and reducing the storage space of the business data table. Of course, any product or method implementing the present invention does not necessarily need to achieve all the advantages described above at the same time.

[0207] As an implementation of an embodiment of the present invention, the data information determination module 930 may include:

[0208] A content quantity determination submodule, used to determine the quantity of each data content contained in the target data column in the data file included in the candidate business data table;

[0209] The target data column is a data column corresponding to the filtering condition included in the historical query feature.

[0210] The distribution weight determination submodule is used to calculate the ratio of the quantity of each data content to the total quantity of data content included in the target data column as the distribution weight of the data content in the target data column.

[0211] As an implementation of an embodiment of the present invention, the filtering weight determination module 940 may include:

[0212] The distribution uniformity determination submodule is used to determine the distribution uniformity of the data content in the data column according to the distribution weight of each type of data content in the data column.

[0213] A filtering weight determination submodule, for determining the filtering weight of each data column according to the historical frequency corresponding to the data column and the distribution uniformity;

[0214] The size of the filtering weight is positively correlated with the historical frequency and the distribution uniformity.

[0215] As an implementation of an embodiment of the present invention, the above-mentioned filtering weight determination submodule may include:

[0216] The filter weight determination unit is used to calculate the filter weight of the data column according to the following formula:

[0217] S = R × (1-A);

[0218] Among them, S is the filtering weight of the data column, R is the historical frequency corresponding to the data column, A is the distribution weight, and 1-A is the distribution uniformity.

[0219] As an implementation of an embodiment of the present invention, the data information determination module 930 may include:

[0220] A candidate data column determination submodule is used to determine, among the data columns included in the candidate business data table, a first preset number of data columns with the largest corresponding historical frequencies as candidate data columns;

[0221] A quantity determination submodule, used to determine, for each candidate data column, the quantity of each type of data content contained in the candidate data column;

[0222] A distribution weight determination submodule, used to calculate the ratio of the quantity of each data content to the total quantity of data content included in the candidate data column as the distribution weight of the data content in the candidate data column;

[0223] The distribution weight determination submodule is used to determine a second preset number of distribution weights with the highest distribution weights according to the distribution weight of each data content in the candidate data column as the distribution weight of each data content in the data column.

[0224] As an implementation of an embodiment of the present invention, the data file sorting module 950 may include:

[0225] A data content sorting submodule, used to sort the data content in the data files included in the candidate business data table according to a preset sorting method;

[0226] The preset sorting method is determined by the attribute of the data content included in the candidate data column with the highest filtering weight included in the candidate business data table.

[0227] As an implementation manner of the embodiment of the present invention, the device further includes:

[0228] A query feature extraction module, used to obtain information to be queried and extract query features from the information to be queried;

[0229] a target business data table determination module, configured to determine, in a business data table, a target business data table indicated by the information to be queried, according to the query feature, wherein the business data table is obtained by sorting according to the data table sorting method;

[0230] The query result acquisition module is used to query the data content indicated by the information to be queried in the target business data table based on the query characteristics to obtain the query result.

[0231] As an implementation manner of the embodiment of the present invention, the target business data table includes target data files stored in multiple partitions, and the query feature at least includes a query table identifier and a filter condition.

[0232] The target business data table determination module may include:

[0233] The target data file search submodule is used to search the target data file included in the target business data table indicated by the query table identifier in the business data table.

[0234] The query result acquisition module may include:

[0235] The target data file to be queried determining submodule is used to determine the target data file to be queried in the target data file according to the filtering condition and the order corresponding to the filtering condition represented by the preset sorting method.

[0236] The query result acquisition submodule is used to query the data content corresponding to the filtering condition from the target data file to be queried to obtain the query result.

[0237] The embodiment of the present invention further provides an electronic device, such as Fig.10 As shown, it includes a processor 1001 , a communication interface 1002 , a memory 1003 and a communication bus 1004 , wherein the processor 1001 , the communication interface 1002 , and the memory 1003 communicate with each other via the communication bus 1004 .

[0238] Memory 1003, used for storing computer programs;

[0239] The processor 1001 is used to implement the steps of the data table sorting method described in any of the above embodiments when executing the program stored in the memory 1003.

[0240] It can be seen that in the solution provided by the embodiment of the present invention, the electronic device obtains historical query information and extracts historical query features in the historical query information, wherein the historical query features at least include the table identifier to be queried and the filtering condition, and then the candidate business data table is screened from the business data table according to the table identifier to be queried included in the historical query features. For each candidate business data table, the distribution weight of each data content in the data column in the data column and the historical frequency of querying the data column in the candidate business data table are determined. Then, the filtering weight of each data column in the candidate business data table is determined based on the historical frequency and distribution weight, and the data content in the data file included in the candidate business data table is sorted according to the data column with the largest query, to obtain the sorted data file, so that the efficiency of data query using the sorted data file can be improved.

[0241] Since the above-mentioned filtering weight is determined based on two factors: the query frequency of the business personnel on the data column in the business data table and the distribution of each data content in the data column, the above-mentioned sorted data file is more in line with the business personnel's tendency to query in the business data table. Performing data queries in the sorted data file improves the query efficiency of the business personnel, and the same data contents in the sorted business data table are distributed together, which facilitates the compression of the business data table and reduces the storage space of the business data table.

[0242] The communication bus mentioned in the above electronic device can be a Peripheral Component Interconnect (PCI) bus or an Extended Industry Standard Architecture (EISA) bus, etc. The communication bus can be divided into an address bus, a data bus, a control bus, etc. For ease of representation, only one thick line is used in the figure, but it does not mean that there is only one bus or one type of bus.

[0243] The communication interface is used for communication between the above electronic device and other devices.

[0244] The memory may include a random access memory (RAM) or a non-volatile memory, such as at least one disk memory. Optionally, the memory may also be at least one storage device located away from the aforementioned processor.

[0245] The above-mentioned processor can be a general-purpose processor, including a central processing unit (CPU), a network processor (NP), etc.; it can also be a digital signal processor (DSP), an application specific integrated circuit (ASIC), a field programmable gate array (FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components.

[0246] In another embodiment of the present invention, a computer-readable storage medium is provided, in which a computer program is stored. When the computer program is executed by a processor, the data table sorting method described in any of the above embodiments is implemented.

[0247] In another embodiment provided by the present invention, a computer program product including instructions is also provided, which, when executed on a computer, enables the computer to execute the data table sorting method described in any one of the above embodiments.

[0248] In the above embodiments, it can be implemented in whole or in part by software, hardware, firmware or any combination thereof. When implemented by software, it can be implemented in whole or in part in the form of a computer program product. The computer program product includes one or more computer instructions. When the computer program instructions are loaded and executed on a computer, the process or function described in the embodiment of the present invention is generated in whole or in part. The computer can be a general-purpose computer, a special-purpose computer, a computer network, or other programmable device. The computer instructions can be stored in a computer-readable storage medium, or transmitted from one computer-readable storage medium to another computer-readable storage medium. For example, the computer instructions can be transmitted from a website site, computer, server or data center to another website site, computer, server or data center by wired (e.g., coaxial cable, optical fiber, digital subscriber line (DSL)) or wireless (e.g., infrared, wireless, microwave, etc.). The computer-readable storage medium can be any available medium that a computer can access or a data storage device such as a server or data center that includes one or more available media integrated. The available medium can be a magnetic medium (e.g., a floppy disk, a hard disk, a tape), an optical medium (e.g., a DVD), or a semiconductor medium (e.g., a solid-state hard disk Solid State Disk (SSD)), etc.

[0249] It should be noted that, in this article, relational terms such as first and second, etc. are only used to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Moreover, the terms "include", "comprise" or any other variants thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or device. In the absence of further restrictions, the elements defined by the sentence "comprise a ..." do not exclude the existence of other identical elements in the process, method, article or device including the elements.

[0250] Each embodiment in this specification is described in a related manner, and the same or similar parts between the embodiments can be referred to each other, and each embodiment focuses on the differences from other embodiments. In particular, for the device embodiment, electronic device embodiment, computer-readable storage medium embodiment, and computer program product embodiment, since they are basically similar to the method embodiment, the description is relatively simple, and the relevant parts can be referred to the partial description of the method embodiment.

[0251] The above description is only a preferred embodiment of the present invention and is not intended to limit the protection scope of the present invention. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present invention are included in the protection scope of the present invention.

Claims

1. A data table sorting method, characterized in that: Each business data table includes data files stored in multiple partitions, and the business data table stores data content in columns. The method includes: Acquire historical query information, and extract historical query features in the historical query information, wherein the historical query features at least include a table identifier to be queried and a filtering condition; According to the to-be-queried table identifier included in the historical query feature, screening out candidate business data tables from the business data tables; For each candidate business data table, determine the distribution weight of each data content in the data column in the data column, and the historical frequency of querying the data column in the candidate business data table based on the filtering condition included in the historical query feature; Determine the filtering weight of each data column in the candidate business data table according to the historical frequency and the distribution weight; The data contents in all data files included in the candidate business data table are sorted according to the filter weights of the data columns to obtain sorted data files, so that when data query is needed, the sorted data files can be used to perform data query and obtain query results.

2. The method according to claim 1, characterized in that The step of determining the distribution weight of each type of data content in the data column includes: Determine the quantity of each type of data content contained in a target data column in a data file included in the candidate business data table, wherein the target data column is a data column corresponding to a filtering condition included in the historical query feature; The ratio of the quantity of each type of data content to the total quantity of data content included in the target data column is calculated as the distribution weight of the data content in the target data column.

3. The method according to claim 1, characterized in that The step of determining the filtering weight of each data column in the candidate business data table according to the historical frequency and the distribution weight includes: Determining the uniformity of distribution of the data content in the data column according to the distribution weight of each type of data content in the data column; For each data column, the filtering weight of the data column is determined according to the historical frequency corresponding to the data column and the distribution uniformity, wherein the size of the filtering weight is positively correlated with the historical frequency and the distribution uniformity.

4. The method according to claim 3, characterized in that The step of determining the filtering weight of the data column according to the historical frequency corresponding to the data column and the distribution uniformity includes: The filter weight of the data column is calculated according to the following formula: S = R × (1-A); Among them, S is the filtering weight of the data column, R is the historical frequency corresponding to the data column, A is the distribution weight, and 1-A is the distribution uniformity.

5. The method according to claim 1, characterized in that The step of determining the distribution weight of each type of data content in the data column includes: Determine, among the data columns included in the candidate business data table, a first preset number of data columns with the largest corresponding historical frequencies as candidate data columns; For each candidate data column, determine the quantity of each type of data content contained in the candidate data column; Calculating the ratio of the quantity of each type of data content to the total quantity of data content included in the candidate data column as the distribution weight of the data content in the candidate data column; According to the distribution weight of each data content in the candidate data column, a second preset number of distribution weights with the highest distribution weights are determined as the distribution weight of each data content in the data column.

6. The method according to any one of claims 1 to 5, characterized in that: The step of sorting the data contents in all the data files included in the candidate business data table according to the filtering weights of the data columns to obtain sorted data files comprises: The data contents in all the data files included in the candidate business data table are sorted according to a preset sorting method, wherein the preset sorting method is determined by the attributes of the data contents included in the candidate data column with the highest filtering weight included in the candidate business data table.

7. The method according to claim 6, characterized in that The method further comprises: Acquire information to be queried, and extract query features from the information to be queried; According to the query feature, determining the target business data table indicated by the information to be queried in the business data table, wherein the business data table is obtained by sorting according to the data table sorting method; In the target business data table, the data content indicated by the information to be queried is queried based on the query feature to obtain a query result.

8. The method according to claim 7, characterized in that The target business data table includes target data files stored in multiple partitions, and the query characteristics include at least a query table identifier and a filter condition; The step of determining, in the business data table, a target business data table indicated by the information to be queried according to the query characteristics comprises: In the business data table, searching for a target data file included in the target business data table indicated by the query table identifier; The step of querying the data content indicated by the information to be queried based on the query feature in the target business data table to obtain the query result includes: In the target data file, according to the filtering condition and the order corresponding to the filtering condition represented by the preset sorting method, determine the target data file to be queried; The data content corresponding to the filtering condition is queried from the target data file to be queried to obtain a query result.

9. A data table sorting device, characterized in that: Each business data table includes data files stored in multiple partitions, and the business data table stores data content in columns. The device includes: A historical query feature extraction module, used to obtain historical query information and extract historical query features in the historical query information, wherein the historical query features at least include a table identifier to be queried and a filtering condition; A business data table screening module, used for screening candidate business data tables from the business data table according to the to-be-queried table identifier included in the historical query feature; A data information determination module, for determining, for each candidate business data table, the distribution weight of each data content in the data column, and the historical frequency of querying the data column in the candidate business data table based on the filtering conditions included in the historical query feature; A filtering weight determination module, used to determine the filtering weight of each data column in the candidate business data table according to the historical frequency and the distribution weight; The data file sorting module is used to sort the data contents in all data files included in the candidate business data table according to the filtering weights of the data columns to obtain sorted data files, so that when data query is needed, the sorted data files can be used to perform data query and obtain query results.

10. An electronic device, characterized in that: It includes a processor, a communication interface, a memory and a communication bus, wherein the processor, the communication interface and the memory communicate with each other through the communication bus; Memory, used to store computer programs; A processor, for implementing the method steps described in any one of claims 1 to 8 when executing a program stored in a memory.

11. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the method steps described in any one of claims 1 to 8 are implemented.

Citation Information

Patent Citations

  • Data warehouse information processing method, device, system and medium

    CN109388637A

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

    CN117009392A

  • SQL statement generation method, device and equipment

    CN117056351A

  • SQL statement generation method and device

    CN118377783A