A data table sorting method and device, electronic equipment and storage medium

Abstract

Embodiments of the present application provide a data table sorting method and device, electronic equipment and storage medium, the method comprises: obtaining historical query information, and extracting historical query features, wherein the historical query features at least include a table to be queried identifier and a filtering condition; according to the table to be queried identifier, a candidate business data table is screened; for each candidate business data table, the distribution weight of each data content in the data column is determined, and the historical frequency of querying the data column is determined; according to the historical frequency and the distribution weight, the filtering weight of each data column is determined; the data content in all data files included in the candidate business data table is sorted according to the size of the filtering weight, and the sorted data file is obtained, so that the efficiency of data query using the sorted data file is improved. And the same data content in the sorted data file is distributed together, which is convenient for compression and reduces the storage space.

Description

Technical Field

[0001] This invention relates to the field of data query technology, and in particular to a data table sorting method, apparatus, electronic device, and storage medium. Background Technology

[0002] To facilitate the management of a company's business data, business data can be stored in business data tables. A data table typically represents a directory containing multiple data files, which store the data for that table.

[0003] Because multiple data files are stored out of order in different files, electronic devices need to traverse multiple data files to obtain query results when querying data content in the business data table according to the query instructions of business personnel. The query speed is not fast enough, and the threshold for using business acceleration query is relatively high for enterprise personnel. Furthermore, business personnel are not very motivated to implement accelerated query and are rather passive. Summary of the Invention

[0004] The purpose of this invention is to provide a data table sorting method, apparatus, 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, embodiments of the present invention provide 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, the method comprising:

[0006] Obtain historical query information and extract historical query features from the historical query information, wherein the historical query features include at least the identifier of the table to be queried and filtering conditions;

[0007] Based on the identifier of the table to be queried included in the historical query features, candidate business data tables are obtained by filtering from the business data tables;

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

[0009] Based on the historical frequency and the distribution weight, determine the filtering weight of each data column in the candidate business data table;

[0010] The data content of all data files included in the candidate business data table is sorted according to the filtering weight of the data columns to obtain sorted data files, so that when data queries are needed, the sorted data files can be used to obtain query results.

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

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

[0013] 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, and use this ratio as the distribution weight of that type of 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 based on the historical frequency and the distribution weight includes:

[0015] Based on the distribution weight of each type of data content in the data column, the uniformity of the data content distribution in the data column is determined;

[0016] For each data column, a filtering weight is determined based on the historical frequency and the uniformity of the distribution corresponding to that data column, wherein the magnitude of the filtering weight is positively correlated with the historical frequency and the uniformity of the distribution.

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

[0018] Calculate the filter weight for this data column using the following formula:

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

[0020] Wherein, 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 data content in the data column includes:

[0022] Among the data columns included in the candidate business data table, the data column with the highest historical frequency is determined as the first preset number of candidate data columns;

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

[0024] 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, and use this ratio as the distribution weight of that type of data content in the candidate data column;

[0025] Based on the distribution weight of each data content in the candidate data column, determine the second preset number of distribution weights with the highest distribution weight, and use them as the distribution weight of each data content in that data column.

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

[0027] The data content of all data files included in the candidate business data table is sorted according to a preset sorting method, wherein the preset sorting method is determined by the attributes of the data content 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] Obtain the information to be queried and extract the query features from the information to be queried;

[0030] Based on the query characteristics, the target business data table indicated by the query information is determined 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 query information is queried based on the query features to obtain the query results.

[0032] Optionally, the target business data table includes target data files stored in multiple partitions, and the query features include at least a query table identifier and filtering conditions;

[0033] The step of determining the target business data table indicated by the query information in the business data table based on the query characteristics includes:

[0034] In the business data table, locate the 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 features in the target business data table to obtain the query result includes:

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

[0037] From the target data file to be queried, query the data content corresponding to the filtering conditions to obtain the query results.

[0038] Secondly, embodiments of the present invention provide a data table sorting apparatus, wherein each business data table includes data files stored in multiple partitions, and the business data table stores data content by column; the apparatus includes:

[0039] The historical query feature extraction module is used to obtain historical query information and extract historical query features from the historical query information, wherein the historical query features include at least the identifier of the table to be queried and filtering conditions;

[0040] The business data table filtering module is used to filter candidate business data tables from the business data table based on the table identifier to be queried included in the historical query features.

[0041] The data information determination module is used to determine the distribution weight of each type of data content in the data column for each candidate business data table, as well as the historical frequency of querying the data column of the candidate business data table based on the filtering conditions included in the historical query features.

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

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

[0044] Thirdly, embodiments of the present invention provide 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 through the communication bus;

[0045] Memory, used to store computer programs;

[0046] When a processor executes a program stored in memory, it implements any of the steps described in the first aspect above.

[0047] Fourthly, embodiments of the present invention provide a computer-readable storage medium storing a computer program, which, when executed by a processor, implements the steps of any of the methods described in the first aspect above.

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

[0049] In the solution provided by this invention, an electronic device acquires historical query information and extracts historical query features from the historical query information. These historical query features include at least the identifier of the table to be queried and filtering conditions. Then, based on the identifier of the table to be queried included in the historical query features, candidate business data tables are selected from the business data tables. 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 columns in that candidate business data table are determined. Furthermore, the filtering weight of each data column in the candidate business data table is determined based on the historical frequency and distribution weight. The data content in the data files included in the candidate business data table is then sorted according to the data column with the highest query frequency, resulting in a sorted data file. This improves the efficiency of data querying using the sorted data file.

[0050] Since the aforementioned filtering weights are determined based on two factors—the frequency of queries performed by business personnel on the corresponding data column in the business data table and the distribution of each type of data content within that column—the sorted data file better reflects the querying tendencies of business personnel within the business data table. Querying data within the sorted data file improves query efficiency. Furthermore, identical data content is grouped together in the sorted business data table, facilitating compression and reducing storage space. Of course, implementing any product or method of this invention does not necessarily require achieving all of the advantages described above simultaneously. Attached Figure Description

[0051] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the accompanying drawings used in the description of the embodiments or the prior art will be briefly introduced below.

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

[0053] Figure 2 For based on Figure 1 The illustrated embodiment is a schematic diagram of extracting historical query features from historical query information.

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

[0055] Figure 4 Another flowchart of a data table sorting method provided in an embodiment of the present invention;

[0056] Figure 5 for Figure 4A specific flowchart of steps S402 and S403 in the illustrated embodiment;

[0057] Figure 6 For based on Figure 1 A flowchart illustrating a data table sorting method in the embodiment shown;

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

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

[0060] Figure 9 This is a schematic diagram of the structure of a data table sorting device provided in an embodiment of the present invention;

[0061] Figure 10 This is a schematic diagram of the structure of an electronic device provided in an embodiment of the present invention. Detailed Implementation

[0062] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art based on this application are within the scope of protection of the present invention.

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

[0064] The data table sorting method provided in this embodiment of the invention can be applied to any electronic device that needs to perform business data queries, such as a server, query platform, database management device, etc., without specific limitations. For clarity, it will be referred to as an electronic device below.

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

[0066] S101, Obtain historical query information and extract historical query features from the historical query information.

[0067] The historical query features include at least the identifier of the table to be queried and the filtering conditions;

[0068] S102, Based on the identifier of the table to be queried included in the historical query features, candidate business data tables are obtained by filtering from the business data tables;

[0069] S103, for each candidate business data table, determine the distribution weight of each type of 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 features;

[0070] S104, Based on the historical frequency and the distribution weight, determine the filtering weight of each data column in the candidate business data table;

[0071] S105, sort the data content of all data files included in the candidate business data table according to the filtering weight of the data column to obtain a sorted data file, so that when data query is required, the sorted data file can be used to perform data query to obtain query results.

[0072] As can be seen, in the solution provided by this embodiment of the invention, the electronic device acquires historical query information and extracts historical query features from the historical query information. These historical query features include at least the identifier of the table to be queried and filtering conditions. Then, based on the identifier of the table to be queried included in the historical query features, candidate business data tables are selected from the business data tables. 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 columns in the candidate business data table are determined. Furthermore, the filtering weight of each data column in the candidate business data table is determined based on the historical frequency and distribution weight. The data content in the data files included in the candidate business data table is then sorted according to the data column with the highest query frequency, resulting in a sorted data file. This improves the efficiency of data querying using the sorted data file.

[0073] Since the aforementioned filtering weights are determined based on two factors—the frequency of queries performed by business personnel on the corresponding data column in the business data table and the distribution of each type of data content within that column—the sorted data file better reflects the querying tendencies of business personnel in that business data table. Querying data in the sorted data file improves the query efficiency for business personnel. Furthermore, identical data content in the sorted business data table is grouped together, facilitating compression of the business data table and reducing its storage space.

[0074] In practice, a single data table often represents a file directory. However, the amount of data contained in a business data table is enormous, making it difficult to store the entire business data table in a single file. Therefore, the data in a business data table is stored in multiple data files, all within a single data directory, to alleviate the storage pressure on electronic devices. Furthermore, since columnar storage offers higher data compression and query efficiency than row-based storage, the aforementioned business data table can be stored using columnar storage to facilitate data compression and retrieval. The aforementioned historical query features can at least include the identifier of the table to be queried and filtering conditions.

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

[0076] The historical query information mentioned above can be SQL (Structured Query Language) statements, and no specific restrictions are made here.

[0077] In one implementation, query information submitted by business personnel is forwarded through the Pilot module in the SQL gateway. The Pilot module in the SQL gateway can collect and store the query information submitted by business personnel. Electronic devices can retrieve the query information stored in the Pilot module at preset intervals as historical query information. The preset interval can be 1 day, 3 days, 1 week, etc., all of which are reasonable and not specifically limited here.

[0078] After obtaining historical query information, the electronic device can extract historical query features from each historical query and store the extracted features. The steps of obtaining historical query information and extracting historical query features can be performed by the parsing module within the electronic device.

[0079] In one implementation, after obtaining historical query information, for each historical query, the parser in the decision module can parse the 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 acts as the observed query. The feature extractor uses the observer pattern to traverse each node in the abstract syntax tree multiple times to extract the historical query features. The nodes in the abstract syntax tree correspond to each feature in the historical query information.

[0080] For example, such as Figure 2 As shown, the abstract syntax tree obtained by parsing the historical query information "SELECT * FROM employee WHERE age = '30'" shows that the first-level node 201 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 "employee" table; the second-level nodes 203 and 204 together constitute the filtering condition, that is, the data content with "value 30" in the "age" column is queried; the second-level node 203 indicates the name of the filtering column used in the query, that is, the data filtering scope is in the "age" column; the second-level node 204 indicates the query condition, that is, the data content with condition "= 30" is queried. By traversing all the above nodes multiple times, the second-level nodes 202, 203, and 204 are extracted as the query features of the historical query information "SELECT * FROM employee WHERE age = '30'".

[0081] In the abstract syntax tree obtained by parsing historical query information, the query features in the second-level nodes, such as "WHERE col_a = 'xxx'" or "WHERE col_a in('xxx','yyy')," constitute the filtering conditions in the historical query information submitted by business personnel.

[0082] Although the database stores many different business data tables, not all the data in each business data table has been queried. Therefore, in order to improve the efficiency of sorting the data tables, in step S102 above, the electronic device can filter from the business data tables to obtain candidate business data tables based on the identifier of the table to be queried, which is included in the historical query features.

[0083] Electronic devices can retrieve historical query features within a certain time range according to a preset period for sorting business data tables. 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 from July 1, 2024 to July 3, 2024, from July 1, 2024 to July 5, 2024, from July 1, 2024 to July 10, 2024, etc., without specific limitations.

[0084] After obtaining the historical query characteristics, based on the table name of the table to be queried included in the historical query characteristics, the business data table whose table name is consistent with the table name of the table to be queried is selected from multiple business data tables as candidate business data tables.

[0085] The step of filtering candidate business data tables from business data tables based on the identifiers of the tables to be queried, including the historical query features, can be performed by a decision-maker in the decision module of an electronic device.

[0086] For example, if 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 based on the table identifier "table_name_user" in the first and third historical query features, the user information table with the table name "user" is identified as a candidate business data table among multiple business data tables; based on the table identifier "table_name_employee" in the second historical query feature, the employee information table with the table name "employee" is identified as a candidate business data table among multiple business data tables.

[0087] 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 type of data content in the data column, as well as the historical frequency of querying the data column in the candidate business data table. Therefore, in step S103 above, the electronic device determines the distribution weight of each type of data content in the data column for each candidate business data table, as well as the historical frequency of querying the data column in the candidate business data table based on the filtering conditions included in the historical query features.

[0088] In one implementation, for each of the above candidate business data tables, the distribution of each type of data content in the candidate data table is determined as the distribution weight; and the number of each type of filtering condition included in the obtained historical query features is taken as the historical frequency of querying the corresponding data column in the candidate data table.

[0089] For example, for a user information table used as a candidate business data table, which contains data columns such as id, age, gender, prov, and level, the distribution weight of each id value in the id column, the distribution weight of each age value in the age column, the distribution weight of each gender category 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. Furthermore, the number of data columns used in each type of filtering condition within all historical query features is determined, and the historical frequency of the corresponding data columns in the candidate data table is determined based on the number of these data columns.

[0090] For example, if all the obtained filtering conditions include "age" 50 times, "level" 45 times, "prov" 30 times, and "gender" 0 times, 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 data columns in the candidate business data table can be determined first. For data columns with a historical frequency of 0, it means that business personnel have not queried the data column before. Therefore, the distribution weight of each data content in the data column can be determined, thereby improving data processing efficiency.

[0092] For example, for the user information table, which is a candidate business data table, if the historical query features obtained include 50 queries for "age", 45 queries for "level", 30 queries for "prov", and 0 queries for "gender", then 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, instead of calculating the distribution weight of the two genders in the gender column, we can calculate 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.

[0094] In order to determine which data column 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 frequency of querying 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 distribution weight.

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

[0096] Distribution weights represent the distribution of each type of data content in a data column. The more similar the quantity of each type of data content in a data column, the more evenly the data content is distributed in that data column. When querying each type of data content in that 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 based on historical frequency and distribution weight, represents the frequency of querying each data column and the distribution of data content in each data column.

[0098] In order to ensure that the sorted data file meets the data query preferences of business personnel, after obtaining the filtering weight of each data column in the candidate business data table, in step S105 above, the data content of all data files included in the candidate business data table is sorted according to the filtering weight of the data column to obtain the sorted data file, so that the sorted data file can be used to perform data query when data query is needed to obtain query results.

[0099] After obtaining the filter weights in the data columns, the data content of all data files included in the candidate business data table is sorted according to the size of the filter weights of the data columns. For example, all the above data files can be sorted according to the data column with the largest filter weight, so as 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 shown in Table 1 below.

[0101] Table 1

[0102] 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 age column has the highest filtering weight in the user information table, the data content of the user information data file included in this user information table is sorted according to the age column. The data content of the id, gender, prov, and level columns in the user information data file is then sorted from smallest to largest according to the age value in the age column, resulting in the sorted data content of the user information data file shown in Table 2 below.

[0104] Table 2

[0105] 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 in the sorted user information data files is arranged according to the ascending order of the values ​​in the age column. Each user information data file corresponds to an age range, thus yielding the following results. Figure 3 The image shows four user information data files that define the age range.

[0107] Among them, such as Figure 3 As shown, before sorting, the age order in the four user information data files was randomized, and the specific age range for each user information data file was unknown. The age range in the first user information data file was 32 to unknown, the age range in the second user information data file was 23 to unknown, the age range in the third user information data file was 20 to unknown, and the age range in the fourth user information data file was 47 to unknown. After sorting the above four user information data files by 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, respectively.

[0108] As can be seen, in the solution provided by this embodiment of the invention, the electronic device acquires historical query information and extracts historical query features from the historical query information. These historical query features include at least the identifier of the table to be queried and filtering conditions. Then, based on the identifier of the table to be queried included in the historical query features, candidate business data tables are selected from the business data tables. 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 columns in the candidate business data table are determined. Furthermore, the filtering weight of each data column in the candidate business data table is determined based on the historical frequency and distribution weight. The data content in the data files included in the candidate business data table is then sorted according to the data column with the highest query frequency, resulting in a sorted data file. This improves the efficiency of data querying using the sorted data file.

[0109] Since the aforementioned filtering weights are determined based on two factors—the frequency of queries performed by business personnel on the corresponding data column in the business data table and the distribution of each type of data content within that column—the sorted data file better reflects the querying tendencies of business personnel in that business data table. Querying data in the sorted data file improves the query efficiency for business personnel. Furthermore, identical data content in the sorted business data table is grouped together, facilitating compression of the business data table and reducing its storage space.

[0110] After obtaining the candidate business data table, in order to sort the candidate business data table according to the filtering weight of the data column in the candidate data table, 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 one embodiment of the present invention, the step of determining the distribution weight of each data content in the data column can include at least the following two implementation methods:

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

[0113] Since business users may not filter every data column in the business data table, some data columns in the business data table may have a historical filtering frequency of 0. For data columns with a historical frequency of 0, it means that the probability of business users filtering these data columns is extremely low. Therefore, 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 conditions included in the historical query features indicate what data content in the candidate business table is to be queried. Therefore, electronic devices can determine the data columns that business personnel have queried as target data columns from the data columns of the candidate data table based on the filtering conditions included in the historical query features.

[0115] After identifying the target data column, determine the quantity of each data item contained in the target data column within the data files included in the candidate business data table. For example, it is reasonable to determine the quantity of each age value in the age column of the user information table, which is identified as the target data column, or the quantity of each province in the prov column, which is identified as the target data column.

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

[0117] For example, a user information table contains four user information data files: User Information Data File 1, User Information Data File 2, User Information Data File 3, and User Information Data File 4. When the age and prov columns in the user information table are both identified as target data columns, the quantity of each type of data content contained in the age and prov columns can be determined first in User Information Data File 1. Then, the quantity of each type of data content contained in the age and prov columns can be determined in User Information Data Files 2, 3, and 4. Finally, the sum of the quantities of each type of data content contained in the age and prov columns determined in User Information Data Files 1, 2, 3, and 4 can be used as the quantity of each type of data content in the age and prov columns.

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

[0119] Then, the quantity of each type of data content contained in another target data column can be determined in each data file. The sum of the quantities of each type of data content contained in the target data column determined in each data file is taken as the quantity of each type of data content contained in the target data column, until the quantity of each type of data content contained in each target data column is determined.

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

[0121] Then, continue determining the quantity of each data item in the `age` column from user information data files 2, 3, and 4. The sum of the quantities of each data item in the `age` column determined from user information data files 1, 2, 3, and 4 is then used as the quantity of each data item contained in the `age` column. Similarly, determine the quantity of each data item in the `prov` column from user information data file 1, and then continue determining the quantity of each data item in the `prov` column from user information data files 2, 3, and 4. The sum of the quantities of each data item in the `prov` column determined from user information data files 1, 2, 3, and 4 is then used as the quantity of each data item contained in the `prov` column.

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

[0123] If the target data column contains a larger quantity of the same type of data content, the greater the proportion of that type of data content to the total number of data contents in the target data column, the higher the distribution weight of that type of data content in the data column, indicating that the distribution of various data contents in the target data column is not uniform. If the quantity of various data contents in the data column is relatively close, the distribution weight of various data contents in the target data column is also relatively close, indicating that the distribution of various 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 more similar the distribution weights of each type of data content in the target data column, the more uniform the distribution of data content in the target data column. When querying each type of data content in the data column, there are relatively more other types of data content that need to be filtered out, thereby improving query efficiency.

[0125] As can be seen, in this embodiment, the electronic device can determine the quantity of each type of data content contained in the target data column of 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 that type of 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 sort the business data table according to the size of the data column filtering weight.

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

[0127] Since the candidate business data table contains many columns, but in reality, business personnel only frequently query a portion of these columns, with other columns being queried relatively infrequently, to reduce the amount of data to be processed and improve the efficiency of determining data distribution weights, a first preset number of columns with the highest historical query frequency can be identified from the candidate business data table as candidate columns. The specific value of this first preset number can be set according to actual needs, such as a top 3 column, a top 5 column, etc., all of which are reasonable and not specifically limited here.

[0128] After determining the 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, which is used as the distribution weight of that type of data content in the candidate data column.

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

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

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

[0132] After determining the distribution weight of each data item in the candidate data column, to reduce the amount of data that needs to be calculated when determining the filtering weights, a second preset number of distribution weights with the highest distribution weights can be determined based on the distribution weights of each data item in the candidate data column. These second preset number of weights can be set according to actual needs; for example, 3, 5, or 10 are all reasonable and are not specifically limited here.

[0133] As can be seen, in this embodiment, the electronic device can first determine a first preset number of data columns with the highest historical frequency as candidate data columns. For each candidate data column, the ratio of the quantity of each type of data content to the total quantity of data content contained in the candidate data column is obtained, which serves as the distribution weight of that type of data content in the candidate data column. Based on the distribution weight of each type of 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 type of data content in that data column. This reduces the amount of data used in determining the distribution weights of data columns and the subsequent determination of the filtering weights of data columns, thus improving computational efficiency.

[0134] As one 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 to use for sorting the data table, it is necessary to first determine the filtering weight of the data column in the candidate data table based on historical frequency and distribution weight. The above-mentioned step of determining the filtering weight of each data column in the candidate business data table based on historical frequency and distribution weight may include:

[0135] Step A: Determine the uniformity of the data content distribution in the data column based on the distribution weight of each type of data content in the data column.

[0136] The distribution weight of each data item in a data column represents the distribution of that data item within that column. When the distribution weights of each data item are relatively similar, it indicates that the data distribution in that column is relatively uniform. Conversely, when the differences in the distribution weights of each data item are large, it indicates that the data distribution in that column is relatively uniform.

[0137] The uniformity of the data distribution within a column indicates the ease of searching for relevant data. A uniform distribution means more data needs to be filtered out when searching for matching criteria, resulting in faster search speeds. Conversely, an uneven distribution means fewer data needs to be filtered out when searching for matching criteria, leading to slower search speeds.

[0138] Step B: For each data column, determine the filtering weight of the data column based on the historical frequency corresponding to the data column and the degree of distribution uniformity.

[0139] The higher the historical query frequency of a data column, the more frequently that data column is queried, and the greater the likelihood that business users will query that data column in the candidate business data table later. The evenness of the data distribution within a data column characterizes the ease of querying that data; the more evenly the data is distributed, the easier it is to query that data. Therefore, the filtering weight of a data column is positively correlated with its historical frequency and the evenness of the data distribution within that column.

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

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

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

[0143] As can be seen, in this embodiment, the electronic device can determine the uniformity of data content distribution in a data column based on the distribution weight of each type of data content. Then, based on the historical frequency and uniformity of distribution of the corresponding data column, it can determine the filtering weight of the data column. The filtering weight of a data column represents the frequency of querying that data column and the difficulty of querying the data content in that data column, which facilitates the subsequent determination of the sorting method for the business data table based on the filtering weight of the data column.

[0144] As one embodiment of the present invention, the step of determining the filtering weight of the data column based on the historical frequency and distribution uniformity of the data column may include:

[0145] Calculate the filter weight for this data column using the following formula:

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

[0147] Where S is the filtering weight of the data column, R is the historical frequency of the data column, A is the distribution weight, and 1-A is the uniformity of distribution.

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

[0149] As can be seen, 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 one embodiment of the present invention, after obtaining the data column with the largest filtering weight, when sorting the data content in the data file according to the filtering weight of the data column, the sorting method is different for different types of data content.

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

[0152] Sort the data content in the data files included in the candidate business data table according to the preset sorting method.

[0153] Electronic devices can preset different sorting methods for different types of data content. The preset sorting method is determined based on the attributes of the data content included in the data column. For example, when the candidate data column with the highest filtering weight in the candidate business data table contains numerical data, the data content in the data file can be sorted in ascending or descending order based on the size of the numerical values.

[0154] For example, when the candidate data column with the highest filtering weight in the candidate business data table contains text, it is reasonable to sort the data content in the data file according to the first letter of the text, and no specific restrictions are imposed here.

[0155] As can be seen, in this embodiment, the electronic device can sort the data content of the data file included in the candidate service data table according to a preset sorting method, which clarifies the sorting method of data content with different attributes and improves sorting efficiency.

[0156] As one embodiment of the present invention, after sorting the data content of all data files included in the candidate business data table, when business personnel perform data queries in the business data table again, they can use the sorted data files to perform data queries.

[0157] In this case, such as Figure 4 As shown, the above method may further include:

[0158] S401, Obtain the information to be queried and extract the query features from the information to be queried.

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

[0160] For example, if a business user wants to query user information with a user level of 2 in the user information table, the query could be SELECT * FROM user WHERE level = '2'; if the business user wants to query employee information with an age of 30 in the employee information table, the query could be SELECT * FROM employee WHERE age = '30'; if the business user wants to query user information with an address in Beijing, Shanghai, or Shenzhen, the query could be SELECT * FROM user WHERE prov in (BeiJing,ShangHai,ShenZhen), which is all reasonable.

[0161] Since the information to be queried used by business personnel is different, the query features extracted for different information will also be different. As one implementation method, query features can include query table identifiers, indicating which business data tables the data content is being queried from. For example, when the information to be queried is "SELECT * FROM employee1", the query features extracted from this information will include "SELECT", "table name employee1", etc., where employee1 is the query table identifier.

[0162] As one implementation method, query features can include query table identifiers and filtering conditions. The query table identifier indicates which business data tables are being queried, and the filtering conditions indicate which data content is being queried. For example, when the query information is "SELECT id(identification), age FROM employee WHERE age='30'", the query features extracted from this query information include "SELECT column id, age", "table name employee", and "column age=30", where employee is the query table identifier, age='30' is the filtering condition, and age is the column used in the filtering condition. Similarly, when the query information is "SELECT * FROM user WHERE level='2'", the query features extracted from this query information include "SELECT", "table name user", and "column level=2", where user is the query table identifier, level='2' is the filtering condition, and level is the column used in the filtering condition.

[0163] S402, Based on the query characteristics, determine the target business data table indicated by the query information in the business data table;

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

[0165] As one implementation method, the query table identifier contained in the extracted query features indicates which business data table the data content is to be queried. Therefore, based on the query table identifier in the query features, the business data table that matches the query table identifier can be selected as the target business data table from multiple business data tables. For example, for the query feature "table name employee1", the employee1 table can be determined as the target business data table indicated by the query information from 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 data based on the frequency of queries and the distribution of the data, the data in the identified target business data table is sorted according to a preset sorting method. For example, in the employee information table, which is identified as the target business data table, because the age column is queried frequently and the data in the age column is relatively evenly distributed, the data in the employee information table is sorted in ascending order based on the size of the data 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 features to obtain the query result.

[0168] As one implementation method, the filtering conditions included in the extracted query features characterize what data content to query in the target business data table. Therefore, based on the filtering conditions in the query features, columns with names consistent with the filtering conditions can be selected in the target business data table, and the data content contained in those columns can be used as the query results. For example, for the query feature "SELECT column id, age", the id and age columns can be found in the employee table, which contains columns such as id, age, gender, prov, and duration. The data content in the id and age columns can then be used as the query results indicated by the query information "SELECT id, age FROM employee".

[0169] As one 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 that is 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 a query feature containing "column level" "=" '2', the `user` table can be searched for multiple columns such as `id`, `age`, `gender`, `prov`, and `level`. The `level` column can then be located, and within the range containing "2", all data entries with the value '2' can be found. This result is the query result indicated by "SELECT * FROM user WHERE level = '2'". The "all data entries" can include all data in the rows containing the business data where the `level` column has a value of '2', and can include names, ages, provinces, etc., without specific limitations.

[0171] As can be seen, in the solution provided by the embodiments of the present invention, business personnel reduce the number of data files and data rows that need to be scanned when performing data queries. They can directly determine the range of business content for which the filtering conditions lie based on the business content in the sorted target business data table, thereby improving query speed.

[0172] As one embodiment of the present invention, the target business data table may include target data files stored in multiple partitions, and the query features include at least a query table identifier and filtering conditions.

[0173] like Figure 5 As shown, the steps described above for determining the target business data table indicated by the query information in the business data table based on the query characteristics may include:

[0174] S4021, In the business data table, locate the 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 the corresponding target data files based on 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 identified as target data files.

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

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

[0179] Since the target business data table includes target data files stored in multiple files, after identifying all target data files based on the table identifier, it is necessary to further determine the target data file containing the filter conditions based on the filter conditions, and use the target data file containing the filter conditions as the target data file to be queried.

[0180] For example, the user information table identified as the target business data table includes user information data files stored in four files. The data content in the user information data files is sorted by the age column. Specifically, user information data file 1 contains an age range of 20-30, user information data file 2 contains an age range of 30-40, user information data file 3 contains an age range of 40-50, and user information data file 4 contains an age range of 50-unknown.

[0181] When business personnel need to query the user information table to find users who are 30 years old, they should select user information data file 1 as the target data file and perform the query in both user information data file 1 and user information data file 2. When business personnel need to query the user information table to find users who are 45 years old, they should select user information data file 3 as the target data file and perform the query in user information data file 3.

[0182] S4032, query the data content corresponding to the filtering conditions from the target data file to be queried, and obtain the query results.

[0183] After identifying the target data file to be queried, the corresponding data columns are determined based on the filtering conditions, and the data content of the data columns is queried to obtain the query results corresponding to the information to be queried.

[0184] As can be seen, in this embodiment, the electronic device searches the business data table for the target data file included in the target business data table indicated by the query table identifier. Within the found target data file, based on the filtering conditions and the range of filtering conditions contained in each target data file, it determines the target data file to be queried. Thus, within the target data file to be queried, it determines the data content corresponding to the filtering conditions, obtaining the query result. Since each target data file corresponds to a range of data content, the target data file containing the filtering conditions can be directly determined based on the aforementioned range of data content, reducing the number of data files and rows that need to be scanned and improving the efficiency of data querying.

[0185] To facilitate understanding of the methods provided in this application, the following is in conjunction with, for example... Figure 6 The specific examples shown will be used to illustrate this:

[0186] S601 retrieves the user's SQL records, that is, retrieves the historical query information mentioned above.

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

[0188] S603, the decision module in the electronic device obtains the attributes of the business data table, namely the identifier of the business data table, the table's metadata, namely the distribution weight of the data content in the data column, and pulls the SQL features within a certain time range. The decision unit in the decision module performs statistics and analysis on the obtained table attributes, metadata, and features, and uses the rules 1, 2, ..., n provided by the rule model to determine the recommended value of the data column, namely the filtering weight. Among them, rule 1 can be based on filtering conditions to perform statistics and analysis on attributes, metadata, and features.

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

[0190] like Figure 7As shown, the Iceberg table includes data files stored in four files. Before sorting, the data content in the prov column of the four data files is randomly ordered, and the range of prov contained in each data file is unknown. The range of prov in the first data file is AnHui to unknown, the range of prov in the second data file is HeBei to unknown, the range of prov in the third data file is AnHui to unknown, and the range of prov in the fourth data file is AnHui to unknown.

[0191] The data in the Sort column of the Iceberg table is sorted according to the sorting method of the highest recommended value in the prov column, resulting in four data files sorted 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] Thus, when querying data where `prov` is set to "BeiJing", only the data file in the first file needs to be scanned to obtain all the data corresponding to the query information. Similarly, the data content of the first primary key in the ClickHouse table can also be sorted according to the sorting method of the `prov` column with the highest recommended value among the included data columns. It is evident that in this embodiment, based on historical query information, SQL features are extracted, and the tendency of business queries is analyzed, thereby optimizing based on historical query information and intelligently recommending a sorting method for the data files.

[0193] In one embodiment, the electronic device may include a parsing module, a decision-making module, and a platform interface, the structural relationship of which is as follows: Figure 8 As shown 。

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

[0195] The decision-making module includes a rule model and a decision-maker. The rule model provides preset rules for statistical analysis of the table's attributes, metadata, and features. The decision-maker is used to perform statistical analysis on the table's attributes, metadata, 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, this embodiment of the invention also provides a business data query device. The following describes a business data query device provided by this embodiment of the invention.

[0198] like Figure 9 As shown, a data table sorting device is provided, wherein each business data table includes data files stored in multiple partitions, and the business data tables store data content by column. The device includes:

[0199] The historical query feature extraction module 910 is used to acquire historical query information and extract historical query features from the historical query information.

[0200] The historical query features include at least the identifier of the table to be queried and the filtering conditions.

[0201] The business data table filtering module 920 is used to filter candidate business data tables from the business data table based on the table identifier to be queried included in the historical query features.

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

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

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

[0205] As can be seen, in the solution provided by this embodiment of the invention, the electronic device acquires historical query information and extracts historical query features from the historical query information. These historical query features include at least the identifier of the table to be queried and filtering conditions. Then, based on the identifier of the table to be queried included in the historical query features, candidate business data tables are selected from the business data tables. 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 columns in the candidate business data table are determined. Furthermore, the filtering weight of each data column in the candidate business data table is determined based on the historical frequency and distribution weight. The data content in the data files included in the candidate business data table is then sorted according to the data column with the highest query frequency, resulting in a sorted data file. This improves the efficiency of data querying using the sorted data file.

[0206] Since the aforementioned filtering weights are determined based on two factors—the frequency of queries performed by business personnel on the corresponding data column in the business data table and the distribution of each type of data content within that column—the sorted data file better reflects the querying tendencies of business personnel within the business data table. Querying data within the sorted data file improves query efficiency. Furthermore, identical data content is grouped together in the sorted business data table, facilitating compression and reducing storage space. Of course, implementing any product or method of this invention does not necessarily require achieving all of the advantages described above simultaneously.

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

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

[0209] The target data column is the data column corresponding to the filtering conditions included in the historical query features.

[0210] The distribution weight determination submodule is used to 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, which is used as the distribution weight of that type of data content in the target data column.

[0211] As one embodiment of the present invention, the above-mentioned filter 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 based on the distribution weight of each type of data content in the data column.

[0213] The filter weight determination submodule is used to determine the filter weight of each data column based on the historical frequency of the data column and the degree of distribution uniformity.

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

[0215] As one embodiment of the present invention, the above-mentioned filter 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] Wherein, 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 one embodiment of the present invention, the data information determination module 930 may include:

[0220] The candidate data column determination submodule is used to determine the first preset number of data columns with the highest historical frequency from the data columns included in the candidate business data table, and use them as candidate data columns;

[0221] The quantity determination submodule is used to determine the quantity of each type of data content contained in each candidate data column.

[0222] The distribution weight determination submodule is used to 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, which is used as the distribution weight of that type of data content in the candidate data column.

[0223] The distribution weight determination submodule is used to determine the second preset number of distribution weights with the highest distribution weights based on the distribution weight of each data content in the candidate data column, and use them as the distribution weight of each data content in that data column.

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

[0225] The data content sorting submodule is used to sort the data content of 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 attributes of the data content included in the candidate data column with the highest filtering weight in the candidate business data table.

[0227] As one embodiment of the present invention, the device further includes:

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

[0229] The target business data table determination module is used to determine the target business data table indicated by the query information in the business data table according to the query characteristics, 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 query information in the target business data table based on the query features, and obtain the query result.

[0231] As one embodiment of the present invention, the target business data table includes target data files stored in multiple partitions, and the query features include at least a query table identifier and filtering conditions.

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

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

[0234] The above query result retrieval module may include:

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

[0236] The query result retrieval submodule is used to query the data content corresponding to the filtering conditions from the target data file to be queried, and obtain the query results.

[0237] This invention also provides an electronic device, such as... Figure 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 through the communication bus 1004.

[0238] Memory 1003 is used to store computer programs;

[0239] When the processor 1001 executes the program stored in the memory 1003, it implements the data table sorting method steps described in any of the above embodiments.

[0240] As can be seen, in the solution provided by this embodiment of the invention, the electronic device acquires historical query information and extracts historical query features from the historical query information. These historical query features include at least the identifier of the table to be queried and filtering conditions. Then, based on the identifier of the table to be queried included in the historical query features, candidate business data tables are selected from the business data tables. 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 columns in the candidate business data table are determined. Furthermore, the filtering weight of each data column in the candidate business data table is determined based on the historical frequency and distribution weight. The data content in the data files included in the candidate business data table is then sorted according to the data column with the highest query frequency, resulting in a sorted data file. This improves the efficiency of data querying using the sorted data file.

[0241] Since the aforementioned filtering weights are determined based on two factors—the frequency of queries performed by business personnel on the corresponding data column in the business data table and the distribution of each type of data content within that column—the sorted data file better reflects the querying tendencies of business personnel in that business data table. Querying data in the sorted data file improves the query efficiency for business personnel. Furthermore, identical data content in the sorted business data table is grouped together, facilitating compression of the business data table and reducing its storage space.

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

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

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

[0245] The processors mentioned above can be general-purpose processors, including central processing units (CPUs), network processors (NPs), etc.; they can also be digital signal processors (DSPs), application-specific integrated circuits (ASICs), field-programmable gate arrays (FPGAs), or other programmable logic devices, discrete gate or transistor logic devices, or discrete hardware components.

[0246] In another embodiment of the present invention, a computer-readable storage medium is also provided, wherein a computer program is stored therein, and when the computer program is executed by a processor, it implements any of the data table sorting methods described in the above embodiments.

[0247] In another embodiment of the present invention, a computer program product containing instructions is also provided, which, when run on a computer, causes the computer to execute any of the data table sorting methods described in the above embodiments.

[0248] In the above embodiments, implementation can be achieved entirely or partially through software, hardware, firmware, or any combination thereof. When implemented using software, it can be implemented entirely or partially 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, all or part of the processes or functions described in the embodiments of the present invention are generated. 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. For example, the computer instructions can be transmitted from one website, computer, server, or data center to another website, computer, server, or data center via wired (e.g., coaxial cable, fiber optic, digital subscriber line (DSL)) or wireless (e.g., infrared, wireless, microwave, etc.) means. 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 integrates one or more available media. The available medium can be a magnetic medium (e.g., floppy disk, hard disk, magnetic tape), an optical medium (e.g., DVD), or a semiconductor medium (e.g., solid state disk (SSD)).

[0249] It should be noted that, in this document, relational terms such as "first" and "second" are used only to distinguish one entity or operation from another, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Furthermore, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitations, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

[0250] The various embodiments in this specification are described in a related manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on describing the differences from other embodiments. In particular, the device embodiments, electronic device embodiments, computer-readable storage medium embodiments, and computer program product embodiments are basically similar to the method embodiments, and therefore the descriptions are relatively simple; relevant parts can be referred to the descriptions of the method embodiments.

[0251] The above description is merely a preferred embodiment of the present invention and is not intended to limit the scope of protection of the present invention. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of the present invention are included within the scope of protection of the present invention.

Claims

1. A method for sorting a data table, characterized in that, Each business data table includes data files stored in multiple partitions. The business data table stores data content by column. The method includes: Obtain historical query information and extract historical query features from the historical query information, wherein the historical query features include at least the identifier of the table to be queried and filtering conditions; Based on the identifier of the table to be queried included in the historical query features, candidate business data tables are obtained by filtering from the business data tables; For each candidate business data table, determine the distribution weight of each type of data content in the data column, and the historical frequency of querying the data columns in the candidate business data table based on the filtering conditions included in the historical query features; Based on the historical frequency and the distribution weight, determine the filtering weight of each data column in the candidate business data table; The data content of all data files included in the candidate business data table is sorted according to the filtering weight of the data columns to obtain sorted data files, so that when data queries are needed, the sorted data files can be used to perform data queries and obtain query results.

2. The method according to claim 1, characterized in that, The step of determining the distribution weight of each data content in the data column includes: Determine the quantity of each type of data content contained in the target data column of the data file included in the candidate business data table, wherein the target data column is the data column corresponding to the filtering conditions included in the historical query features; 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, and use this ratio as the distribution weight of that type of 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 based on the historical frequency and the distribution weight includes: Based on the distribution weight of each type of data content in the data column, the uniformity of the data content distribution in the data column is determined; For each data column, a filtering weight is determined based on the historical frequency and the uniformity of the distribution corresponding to that data column, wherein the magnitude of the filtering weight is positively correlated with the historical frequency and the uniformity of the distribution.

4. The method according to claim 3, characterized in that, The step of determining the filtering weight of the data column based on the historical frequency and the uniformity of the distribution of the data column includes: Calculate the filter weight for this data column using the following formula: S = R × (1 - A); Wherein, 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 data content in the data column includes: Among the data columns included in the candidate business data table, the data column with the highest historical frequency is determined as the first preset number of candidate data columns; For each candidate data column, determine the number of each type of data content contained in that candidate data column; 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, and use this ratio as the distribution weight of that type of data content in the candidate data column; Based on the distribution weight of each data content in the candidate data column, determine the second preset number of distribution weights with the highest distribution weight, and use them as the distribution weight of each data content in that data column.

6. The method according to any one of claims 1-5, characterized in that, The step of sorting the data content of all data files included in the candidate business data table according to the filtering weight of the data columns to obtain the sorted data files includes: The data content of all data files included in the candidate business data table is sorted according to a preset sorting method, wherein the preset sorting method is determined by the attributes of the data content 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 includes: Obtain the information to be queried and extract the query features from the information to be queried; Based on the query characteristics, the target business data table indicated by the query information is determined 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 query information is queried based on the query features to obtain the query results.

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 features include at least the query table identifier and filtering conditions. The step of determining the target business data table indicated by the query information in the business data table based on the query characteristics includes: In the business data table, locate the 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 features in the target business data table to obtain the query result includes: In the target data file, the target data file to be queried is determined according to the filtering conditions and the order corresponding to the filtering conditions represented by the preset sorting method; From the target data file to be queried, query the data content corresponding to the filtering conditions to obtain the query results.

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 column-wise. The device includes: The historical query feature extraction module is used to obtain historical query information and extract historical query features from the historical query information, wherein the historical query features include at least the identifier of the table to be queried and the filtering conditions; The business data table filtering module is used to filter candidate business data tables from the business data table based on the table identifier to be queried included in the historical query features; The data information determination module is used to determine the distribution weight of each type of data content in the data column for each candidate business data table, as well as the historical frequency of querying the data column of the candidate business data table based on the filtering conditions included in the historical query features. The filtering weight determination module is used to determine the filtering weight of each data column in the candidate business data table based on the historical frequency and the distribution weight. The data file sorting module is used to sort the data content of all data files included in the candidate business data table according to the filtering weight of the data columns to obtain sorted data files, so that when data query is required, 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, when executing a program stored in memory, implements the steps of the method described in any one of claims 1-8.

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

Images