Data query method and device, equipment, medium and program product

Abstract

The invention provides a data query method, which can be applied to the field of big data, and comprises the following steps: under the condition that a first data table and a second data table establish association according to a target association relationship, generating a screening item according to a target association variable list between the first data table and the second data table, the data size of the first data table is smaller than that of the second data table, the target associated variable list at least comprises a first associated variable and a second associated variable, and the second associated variable represents a segmentation range of the first associated variable for segmenting a value range of the first associated variable according to a preset segmentation condition; the screening item at least comprises a second association variable meeting a preset condition; generating a third data table according to the screening item and the second data table, wherein the third data table records at least part of content in the second data table; associating the first data table with the third data table according to a target association relationship to obtain a target data table; and querying the target content in the first associated variable based on the target data table.

Description

Technical Field

[0001] The present disclosure relates to the field of big data, and more specifically, to a data query method, device, equipment, medium and program product. Background Art

[0002] With the rapid development and application of databases, various databases are being used, such as the Greenplum relational database. Among them, there are three commonly used association methods: hash join, nested loop join, and combined sort join. At present, database-based analysis tools generally provide association steps for users to choose to use. This method relies on the user's data analysis ability and requires a certain basis for the selection of the currently used association method, which may lead to the problem of inaccurate association method selection. On the other hand, setting a fixed association method for a fixed scenario requires data redistribution of the entire table of a large table, resulting in low efficiency. On the other hand, there is no targeted optimization of data characteristics, resulting in low efficiency of user flexible association analysis. Summary of the invention

[0003] In view of this, the present disclosure provides a data query method, device, equipment, medium and program product for efficient data table association.

[0004] One aspect of the present disclosure provides a data query method, including: when a first data table and a second data table are associated with each other by a target association relationship, generating a filter item according to a target association variable list between the first data table and the second data table, the data volume of the first data table is smaller than the data volume of the second data table, the target association variable list includes at least a first association variable and a second association variable, the second association variable represents a segmentation range of the first association variable according to a preset segmentation condition, and the filter item includes at least the second association variable that meets the preset condition; generating a third data table according to the filter item and the second data table, the third data table recording at least part of the content in the second data table; associating the first data table and the third data table by a target association relationship to obtain a target data table; and querying the target content in the first association variable based on the target data table.

[0005] According to an embodiment of the present disclosure, the first associated variable represents an associated variable having a target indexing method, and the target indexing method represents that the number of different values ​​in the index field is within a preset number range.

[0006] According to an embodiment of the present disclosure, before generating a filter item, the method includes: determining whether there is a first associated variable in the target associated variable lists of the first data table and the second data table, and the value range corresponding to the first associated variable is greater than a preset range threshold; if the first associated variable exists, determining whether there is a second associated variable corresponding to the first associated variable in the target associated variable list; generating a filter item includes: if the second associated variable exists, deduplicating the values ​​corresponding to the target associated variable based on the target associated variable list to generate a filter item.

[0007] According to an embodiment of the present disclosure, the values ​​corresponding to the target-associated variables are deduplicated based on the target-associated variable list to generate filter items, including: determining a unique value for each target-associated variable based on the target-associated variable list, the unique value representing a combination of different numerical values ​​corresponding to each target-associated variable in the first data table; generating filter items based on each unique value, wherein the combination of different numerical values ​​corresponding to each target-associated variable in the filter items is not repeated.

[0008] According to an embodiment of the present disclosure, before generating the filter item, it also includes: if there is no second associated variable corresponding to the first associated variable, generating the second associated variable based on a preset segmentation condition, and adding the second associated variable to the associated variable list.

[0009] According to an embodiment of the present disclosure, generating a second association variable includes: generating a second association variable having a target indexing mode.

[0010] According to an embodiment of the present disclosure, generating filter items includes: in response to the number of unique values ​​being less than or equal to a preset number threshold, and / or in response to the number of unique values ​​and the number of parameters corresponding to the target index method satisfying a preset quantitative relationship, generating filter items according to each unique value.

[0011] According to an embodiment of the present disclosure, generating a third data table according to filter items and a second data table includes: filtering the second data table according to the filter items to generate the third data table, wherein each row of data in the third data table includes a unique value.

[0012] According to an embodiment of the present disclosure, the target indexing method is bitmap indexing.

[0013] According to an embodiment of the present disclosure, the target association method is a hash association using a target association variable as an association field.

[0014] Another aspect of the present disclosure provides a data query device, including: a first generation module, used to generate a filter item according to a target association variable list between the first data table and the second data table when a first data table and a second data table are associated with each other in a target association relationship, the data volume of the first data table is smaller than the data volume of the second data table, the target association variable list includes at least a first association variable and a second association variable, the second association variable represents a segmentation range of the first association variable according to a preset segmentation condition for segmenting the value range of the first association variable, and the filter item includes at least the second association variable that meets the preset condition; a second generation module, used to generate a third data table according to the filter item and the second data table, the third data table recording at least part of the content in the second data table; and an association module, used to associate the first data table and the third data table in a target association relationship to obtain a target data table; and a query module, used to query the target content in the first association variable based on the target data table.

[0015] Another aspect of the present disclosure provides an electronic device, including: one or more processors; and a memory for storing one or more programs, wherein when the one or more programs are executed by the one or more processors, the one or more processors implement the above method.

[0016] Another aspect of the present disclosure provides a computer-readable storage medium storing computer-executable instructions, which are used to implement the above method when executed.

[0017] Another aspect of the present disclosure provides a computer program product, which includes computer executable instructions, and the instructions are used to implement the above method when executed.

[0018] According to the embodiments of the present disclosure, according to the data query method, device, equipment, medium and program product provided by the present disclosure, filter items are generated through the target association variables in the first data table and the second data table, and the third data table is generated through the filter items and the second data table, and the first data table and the third data table with less data volume are associated to obtain the target data table. At least one of the following beneficial effects is achieved: it solves the problem that when users select the association method, they need certain professional accumulation, manual observation and calculation judgment, and cannot support the user's flexible association analysis scenario, and achieves the technical effect of accurate and flexible association of data. Since the filter items related to the unique value are generated according to the first association variable, the second data table is filtered by the filter items, and the data table obtained after the screening is associated with the first data table to narrow the query range and improve the query speed. For the bitmap index scenario and the use of the data distribution characteristics of specific fields, segmented fields are established, and there is no need to scan the second data table in full table, thereby improving efficiency. BRIEF DESCRIPTION OF THE DRAWINGS

[0019] The above and other objects, features and advantages of the present disclosure will become more apparent through the following description of the embodiments of the present disclosure with reference to the accompanying drawings, in which:

[0020] Figure 1 The application scenario diagram of the data query method and device according to the embodiment of the present disclosure is schematically shown;

[0021] Figure 2 A flowchart of a data query method according to an embodiment of the present disclosure is schematically shown;

[0022] Figure 3 A flowchart of generating filter items in a data query method according to an embodiment of the present disclosure is schematically shown;

[0023] Figure 4 Another flow chart for generating filter items in the data query method according to an embodiment of the present disclosure is schematically shown;

[0024] Figure 5 Another flow chart of the data query method according to an embodiment of the present disclosure is schematically shown;

[0025] Figure 6 Another flow chart of the data query method according to an embodiment of the present disclosure is schematically shown;

[0026] Figure 7 Another flow chart of the data query method according to an embodiment of the present disclosure is schematically shown;

[0027] Figure 8 Another flow chart of the data query method according to an embodiment of the present disclosure is schematically shown;

[0028] Fig. 9 A block diagram schematically shows a data query device according to an embodiment of the present disclosure; and

[0029] Fig.10 A block diagram of an electronic device according to an embodiment of the present disclosure is schematically shown. DETAILED DESCRIPTION

[0030] Hereinafter, embodiments of the present disclosure will be described with reference to the accompanying drawings. However, it should be understood that these descriptions are exemplary only and are not intended to limit the scope of the present disclosure. In the following detailed description, for ease of explanation, many specific details are set forth to provide a comprehensive understanding of the embodiments of the present disclosure. However, it is apparent that one or more embodiments may also be implemented without these specific details. In addition, in the following description, descriptions of known structures and technologies are omitted to avoid unnecessary confusion of the concepts of the present disclosure.

[0031] The terms used herein are only for describing specific embodiments and are not intended to limit the present disclosure. The terms "comprise", "include", etc. used herein indicate the existence of features, steps, operations and / or components, but do not exclude the existence or addition of one or more other features, steps, operations or components.

[0032] All terms (including technical and scientific terms) used herein have the meanings commonly understood by those skilled in the art unless otherwise defined. It should be noted that the terms used herein should be interpreted as having a meaning consistent with the context of this specification and should not be interpreted in an idealized or overly rigid manner.

[0033] When using expressions such as "at least one of A, B, and C, etc.", they should generally be interpreted according to the meaning of the expression commonly understood by those skilled in the art (for example, "a system having at least one of A, B, and C" should include but is not limited to a system having A alone, B alone, C alone, A and B, A and C, B and C, and / or A, B, C, etc.).

[0034] In the embodiments of the present disclosure, the collection, updating, analysis, processing, use, transmission, provision, disclosure, storage, etc. of the data involved (for example, including but not limited to user personal information) are in compliance with the provisions of relevant laws and regulations, are used for legitimate purposes, and do not violate public order and good morals. In particular, necessary measures are taken for user personal information to prevent illegal access to user personal information data and maintain the security of user personal information, network security, and national security.

[0035] In the embodiments of the present disclosure, the user's authorization or consent is obtained before obtaining or collecting the user's personal information.

[0036] The acquisition, transmission, storage, use, and processing of data in this disclosure are in compliance with relevant national laws and regulations.

[0037] It should be noted that in the embodiments of the present application, certain software, components, models and other existing solutions in the industry may be mentioned, and they should be regarded as exemplary. Their purpose is only to illustrate the feasibility of implementing the technical solution of the present disclosure, but it does not mean that the applicant has or will necessarily use the solution.

[0038] An embodiment of the present disclosure provides a data query method, including: when a first data table and a second data table are associated with each other by a target association relationship, generating a filter item according to a target association variable list between the first data table and the second data table, the data volume of the first data table is smaller than the data volume of the second data table, the target association variable list includes at least a first association variable and a second association variable, the second association variable represents a segmentation range of the first association variable according to a preset segmentation condition for segmenting the value range of the first association variable, and the filter item includes at least the second association variable that meets the preset condition; generating a third data table according to the filter item and the second data table, the third data table recording at least part of the content in the second data table; associating the first data table and the third data table by a target association relationship to obtain a target data table; and querying the target content in the first association variable based on the target data table.

[0039] Figure 1 The application scenario diagram of the data query method and device according to the embodiments of the present disclosure is schematically shown.

[0040] like Figure 1 As shown, the application scenario 100 according to this embodiment may include a database 101, a network 102 and a server 103. The database 101 includes a first data table and a second data table. The network 102 is a medium for providing a communication link between the database 101 and the server 103 and between the first data table and the second data table.

[0041] The database 101 stores various data for association. For example, the amount of data in the first data table may be smaller than the amount of data in the second data table.

[0042] The network 102 may include various connection types, such as wired, wireless communication links or optical fiber cables, etc. The wired mode may be, for example, a cable and any of the following interfaces: optical fiber channel, infrared interface, D-type data interface, serial interface, USB interface, USB Type-C interface or Dock interface, and the wireless mode may be, for example, a wireless communication mode, wherein the wireless communication may be, for example, any of multiple wireless technology standards such as Bluetooth, Wi-Fi, Infrared, ZigBee, etc.

[0043] The server 103 may be a server that provides various services, such as a background management server that performs data association between different data tables in a database (only as an example). The background management server may set an index variable and an association threshold for the second data table, and perform data association between the first data table and the second data table based on the index variable and the association threshold as judgment conditions.

[0044] It should be noted that the data query method provided in the embodiment of the present disclosure can generally be executed by the server 103. Accordingly, the data query device provided in the embodiment of the present disclosure can generally be set in the server 103. The data query method provided in the embodiment of the present disclosure can also be executed by a server or server cluster that is different from the server 103 and can communicate with the database 101 and / or the server 103. Accordingly, the data query device provided in the embodiment of the present disclosure can also be set in a server or server cluster that is different from the server 103 and can communicate with the database 101 and / or the server 103.

[0045] It should be understood that Figure 1 The number of databases, networks and servers in the embodiment is only for illustration. Any number of databases, networks and servers may be provided as required.

[0046] The data query method provided by the embodiments of the present disclosure can be applied to the field of data processing, and specifically to the field of financial technology. For example, in order to meet the data management needs of a bank, it is necessary to associate data tables of various data stored in a database to obtain a required data table. There are three commonly used association connection methods: hash join, nested loop join, and merge join.

[0047] In a hash join, the right table is first scanned and loaded into a hash table, using the join columns as hash keys. Next, the left table is scanned, and the join attribute values ​​of each row found in the scan are used as hash keys to find matching rows in the hash table.

[0048] In a nested loop join, the right table is scanned once for each row found in the left table. This strategy is the easiest to implement but can be very time-consuming.

[0049] In a composite sort join, each table is sorted by the join columns before the join begins. Then the two tables are scanned in parallel, and matching rows are combined into the join rows. Since each table is scanned only once in this join, the required ordering can be obtained by an explicit sort step, or by scanning the relations in the proper order using an index on the join keys.

[0050] However, in general, bank staff choose the corresponding data association method based on their own data analysis capabilities. In this scenario, due to the differences in data analysis capabilities of bank staff, the currently selected data association method may not be the best association method, making the analyzed data connection table inaccurate and unable to meet business needs well. In addition, the efficiency of data association is low, affecting the efficient development of the business. In addition, in some specific association scenarios, such as large table associations in non-btree index and distributed key scenarios, on the one hand, the efficiency is low due to the need to redistribute data in the entire large table, and on the other hand, there is no targeted optimization of data characteristics, resulting in low efficiency of user flexible association analysis.

[0051] Figure 2 The flowchart of the data query method according to the embodiment of the present disclosure is schematically shown.

[0052] like Figure 2 As shown, the method may include operations S210 to S230.

[0053] In operation S210, when the first data table and the second data table are associated with each other in a target association relationship, a filter item is generated according to the target association variable list between the first data table and the second data table. The data volume of the first data table is smaller than the data volume of the second data table, the target association variable list includes at least the first association variable and the second association variable, the second association variable represents the segmentation range of the first association variable according to the preset segmentation condition, and the filter item includes at least the second association variable that meets the preset condition.

[0054] For example, the first data table can be understood as the left table, and the second data table can be understood as the right table. The target association relationship can be a left association or an inner association. The association variable can be understood as an association key or an association field. The role of the association key or association field can be a field that establishes a connection between data tables when data is associated. The association variable refers to the original data field that exists in both the first data table and the second data table. The target association variable is an association variable that meets specific requirements among the association variables. The first association variable can be a variable with a certain range of values, such as amount, quantity, time, temperature, probability, score, distance, quality, frequency, probability, concentration, etc. The second association variable is a segmentation range that segments the value range of the first association variable according to the preset segmentation conditions, that is, the content represented by the second association variable is the first association variable with a certain range or interval.

[0055] The preset segmentation condition can be to divide the range between the maximum value and the minimum value of the first associated variable into multiple intervals at equal intervals. This segmentation method is suitable for uniformly distributed data, such as continuous variables such as temperature, length, time, etc. It is simple, intuitive, and easy to implement; the interval width is consistent and easy to interpret.

[0056] The preset segmentation condition can also be based on the data in the first associated variable, divided according to the historical frequency of occurrence, so that the number of data points contained in each interval is not much different. This segmentation method is suitable for data with concentrated and sparse distribution, which helps to make the amount of data contained in each interval not much different, thereby improving data retrieval efficiency.

[0057] The preset segmentation conditions can also be to automatically find the best division point based on the timing distribution of the data in the first associated variable, with the difference within each interval being the smallest and the difference between intervals being the largest. This segmentation method is suitable for data with obvious data aggregation or obvious categories, such as geographic data, market data, etc.

[0058] The preset segmentation conditions may also be, for example, hash segmentation, logarithmic partitioning, inverted index segmentation, etc. Technical personnel in this field may select the above or other segmentation conditions according to actual conditions to divide the value range of the first associated variable to obtain the second associated variable.

[0059] The following is a specific table example of how to obtain the second associated variable, namely the amount segmentation field, by taking the amount field as the first associated variable:

[0060] Table 1 Correspondence rules between amount field and amount segment field

[0061]

[0062] Table 1 schematically shows the corresponding rules of the amount field and the amount segment field according to the embodiment of the present disclosure, such as amount < 0.01, box number = -1; 0.01 <= amount < 20000, box number = amount / 100 rounded down; amount >= 20000, box number = 200. Assume that in the first data table, the transaction amount is as follows: 0.005 yuan, 150 yuan, 6,500 yuan, 22,000 yuan. According to the preset segmentation conditions, the segmented transaction amounts of the above transaction amounts are: -1, 1, 65, 200 respectively.

[0063] According to the embodiment of the present disclosure, the application scenario of the technical solution of the embodiment provided by the present disclosure can be judged before operation S210. For example, if the associated large table has a btree index and is also an associated field, and the small table is small after deduplication according to the associated field, then using netstloop will improve the association efficiency of Greenplum. At this time, it can be judged whether the application scenario conforms to the method. If not, the technical solution of the embodiment provided by the present disclosure can be used to improve the efficiency.

[0064] In operation S220, a third data table is generated according to the filter item and the second data table, and the third data table records at least part of the content in the second data table. The data in the third data table is at least related to the associated variable corresponding to the filter item. For example, if the filter item is the amount, the third data table may include at least the amount and data associated with the amount. In this way, the second data table is filtered to obtain the third data table, and the amount of data included in the third data table is less than the amount of data in the second data table.

[0065] In operation S230, the first data table and the third data table are associated with each other by a target association relationship to obtain a target data table. The target association relationship includes an association field and an association method. For example, the association is performed with a target association variable as the association field and a hash association is used as the association method. The association method of the first data table and the third data table is the same as the association method of the first data table and the second data table. For example, when the first data table and the second data table are associated with the amount as the association field and the hash association as the association method, the first data table and the third data table are also associated with the amount as the association field and the hash association as the association method.

[0066] The third data table records at least part of the content in the second data table. Since the second data table is generally a large table with a large amount of data, the third data table is generated by filtering items, so that valid data is filtered out from the second data table to form the third data table. There is no need to scan the entire second data table with a large amount of data, thereby improving the association efficiency when associating data.

[0067] According to an embodiment of the present disclosure, a target association variable represents an association variable with a target indexing method, and the target indexing method represents that the number of different values ​​in the index field is within a preset number range. The target indexing method represents the different values ​​in the index field. A simple example is that if the index field is gender, then the different values ​​include male and female, which are two. When the index field is an amount, the number of different values ​​will be relatively large. In an embodiment of the present disclosure, the preset number range can be 100~1000000.

[0068] According to an embodiment of the present disclosure, the target indexing method is bitmap indexing.

[0069] B-tree index is a classic tree structure index that sorts by column value and maintains a balanced tree structure. Each node contains a range of values, and the target value can be quickly found by filtering layer by layer during query.

[0070] Understandably, Bitmap indexes are more suitable for read-only query scenarios, not for data update scenarios. Bitmap indexes may perform better when the number of DISTINCT values ​​(that is, the different values ​​in the index field mentioned above) of the index field is between 100 and 100,000, and is often queried with other index fields. DISTINCT value refers to the number of different values ​​that the field has in all rows of the database table. Fields with fewer than 100 DISTINCT values ​​may not be suitable for any type of index. For example, the gender field usually has only two DISTINCT values: male and female, which is not suitable for indexing. For a field with more than 100,000 DISTINCT values, the size and performance advantages of Bitmap indexes will be severely reduced. For multi-condition queries using AND and OR in the WHERE clause, bitmap operations can be performed directly on the bitmap index without converting to tuple ID first, which can greatly improve performance.

[0071] In operation S240, based on the target data table, the target content in the first association variable is queried. The content of the data table (hereinafter referred to as the original association data table) obtained by associating the first data table and the second data table through the target association relationship is the same as the data in the target data table obtained by associating the first data table and the third data table through the target association relationship, or the content in the original association data table includes the data in the target data table obtained by associating the first data table and the third data table through the target association relationship. That is, the data in the target data table is less than or equal to the original association data table. In the case where the content in the original association data table is greater than the target data table, the data in the original association data table that is more than the target data table should be the repeated content of part of the data in the target data table.

[0072] Therefore, the speed of searching and querying through the target data table is greater than or equal to the speed of searching and querying through the original associated data table. In addition, the target data table is obtained by associating the first data table and the third data table through the target associated relationship, and only the third data table needs to be traversed in its entirety. Compared with the need to perform a full table traversal on the second data table to generate the original associated data table, the time and processing content required are greatly reduced, thereby improving the search efficiency.

[0073] It can be understood that in the technical solution of the present invention, in a scenario such as associating a small table with a large table, when the large table contains a bitmap index, when the unique value of the associated field of another associated table is within a specified record range, the initial association statement is converted into a step-by-step process, so that the associated right table is a third data table obtained by simplifying the second data table after processing. By utilizing the bitmap index, when the number of records to be returned is very small, the query can quickly obtain the result without the need for a full table scan, thereby improving the screening efficiency of associated data.

[0074] Figure 3 The flowchart of generating filter items in the data query method according to the embodiment of the present disclosure is schematically shown.

[0075] like Figure 3 As shown, based on any of the foregoing embodiments, operations S310 to S330 may be included before operation S210.

[0076] In operation S310, it is determined whether there is a first associated variable in the target associated variable list of the first data table and the second data table, and the value range corresponding to the first associated variable is greater than the preset range threshold. It can be understood that the first associated variable can be a field with a larger data range, such as an amount field.

[0077] In operation S320, if the first associated variable exists, it is determined whether there is a second associated variable corresponding to the first associated variable in the target associated variable list. In banks, the range of amount data is generally wide, ranging from 0 to hundreds of millions. By using preset segmentation conditions, such as the data distribution characteristics of the amount transaction record, for the amount association scenario, the amount association is converted into segmented amount field screening. For large amount data query association scenarios, the segmented amount bitmap index is used. When the number of records to be returned is very small, the query can quickly obtain the result.

[0078] Operation S210 may include operation S330. In operation S330, when there is a second associated variable, the values ​​corresponding to the target associated variable are deduplicated based on the target associated variable list to generate a filter item. The values ​​corresponding to the target associated variable may be duplicated. For example, both the first data table and the second data table have variables X1 and X2. X1 and X2 are used as target associated variables. The data is:

[0079] Among them, there are duplicate data (X1A, X2A). If the above data is used as the association variable for association, there will be duplicate data in the associated data table, which reduces the retrieval efficiency. Therefore, it is necessary to remove duplicates from the target association variable. For example, the data obtained is:

[0080]

[0081] Figure 4 Another flow chart for generating filter items in the data query method according to an embodiment of the present disclosure is schematically shown.

[0082] like Figure 4 As shown, in Figure 3 Based on the illustrated embodiment, operation S330 may include operations S410 to S420.

[0083] In operation S410, based on the target associated variable list, a unique value of each target associated variable is determined, where the unique value represents a combination of different numerical values ​​corresponding to each target associated variable in the first data table. For example, a conditional restriction may be imposed on the unique value to obtain a unique value that meets the condition.

[0084] The following schematically shows an example of obtaining a list of unique values:

[0085] For example, the variable X1 corresponds to the left table associated variable X1', where:

[0086]

[0087] The X2 variable corresponds to the left table associated variable X2', where:

[0088]

[0089] Define thresholds Z1=10, Z2=30, and find all unique combinations of X1' and X2' (i.e., the unique values ​​that meet the conditions mentioned above) where X1' is greater than or equal to Z1 and X2' is greater than or equal to Z2. Then, the above two tables can be processed through specific database statements to obtain the results:

[0090]

[0091] Among them, the number of unique value combinations Y=3, and the unique value list is (10, 30), (20, 30), (30, 40).

[0092] In operation S420, filter items are generated according to the unique values, and the combinations of different numerical values ​​corresponding to the target-related variables in the filter items are not repeated. For example, based on the above content, (10, 30), (20, 30), (30, 40) are used as filter items.

[0093] exist Figure 4Based on the illustrated embodiment, operation S220 may include filtering the second data table according to the filter item to generate a third data table, and each row of data in the third data table includes a unique value corresponding to the filter item. For example, the second data table may be filtered, and the filter of the unique value list may be added for the target associated variable with the target index mode, such as the bitmap index associated variable, to generate the third data table. Based on the above specific example, if (10, 30), (20, 30), (30, 40) are used as the filter items, then in the second data table, all data containing X1' is 10 and X2' is 30, data containing X1' is 20 and X2' is 30, and data containing X1' is 30 and X2' is 40 are filtered.

[0094] The following is a specific example. For simplicity, assume that the right table has the following structure and data:

[0095]

[0096] In the above content, the obtained unique value list is (10, 30), (20, 30), (30, 40). Then, using the unique value list as a filter item, filter the second data table and obtain the third data table as follows:

[0097]

[0098] Then, the first data table and the third data table are associated in a target association manner, such as a left join or an inner join, to obtain a target data table. In the case where the second data table is very large, in the above example, the (X1', X2') combination is used as a bitmap index. If you need to query (X1', X2') IN ((10, 30), (20, 30), (30, 40)), the database can quickly access the bitmap of each combination, merge these bitmaps through bit operations (such as OR), and then quickly determine which rows meet these conditions. This operation is much faster than scanning and comparing (X1', X2') values ​​row by row, especially when the table is very large, it can significantly improve query performance by reducing the amount of data that must be checked, optimizing memory usage, and accelerating logical operations. .

[0099] Figure 5 Another flow chart of the data query method according to an embodiment of the present disclosure is schematically shown.

[0100] like Figure 5 As shown, in Figure 4 Based on the illustrated embodiment, the data query method may further include operation S510.

[0101] In operation S510, if there is no second associated variable corresponding to the first associated variable, a second associated variable is generated based on a preset segmentation condition, and the second associated variable is added to the associated variable list. Specifically, if the associated variable lists of the first data table and the second data table only have the first associated variable, such as the amount field, but do not have the segmentation field of the target field, then the first associated variable needs to be segmented according to the preset segmentation condition, the second associated variable corresponding to the first associated variable is established, and then the second associated variable is added to the target associated variable lists of the target first data table and the second data table.

[0102] According to an embodiment of the present disclosure, operation S510 may include generating a second associated variable having the target index mode. After the second associated variable content is obtained after segmenting the first associated variable, it is necessary to establish a corresponding target index for the second associated variable. For example, under the premise that the amount field has a bitmap index, after generating the amount segmentation field corresponding to the amount field, a bitmap index is also established for the amount segmentation field.

[0103] Figure 6 Another flow chart of the data query method according to an embodiment of the present disclosure is schematically shown.

[0104] like Figure 6 As shown, in Figure 4 or Figure 5 Based on the illustrated embodiment, operation S210 may further include operation S610.

[0105] In operation S610, in response to the number of unique values ​​being less than or equal to a preset number threshold, and / or in response to the number of unique values ​​and the number of parameters corresponding to the target indexing method satisfying a preset quantity relationship, a filter item is generated according to each of the unique values.

[0106] In the embodiments of the present disclosure, since the index conditions and data conditions of the second data table (right table) vary greatly, different parameter controls are implemented for different second data tables (large tables) by configuring different variable threshold numbers (main table record number limits).

[0107] When the number of unique values ​​is greater than a certain number, after deduplication, the data in the table is still the same number, so the association efficiency in the data table is still not as good as the hash association. At this time, you can select the initial target association relationship and associate the first data table with the second data table to avoid reducing the index efficiency. The threshold number here can be the number threshold number set by the technician, or it can be obtained according to the number of parameters corresponding to the target index method. For example, the threshold is twice the sum of the parameters corresponding to the bitmap index variable in the association variable list.

[0108] The setting of variable threshold number can be shown in the following table:

[0109]

[0110] It can be clearly seen from the above table that different variable threshold numbers are set according to the index conditions and data conditions of different right tables, which can better adapt to the data association of different data tables.

[0111] Figure 7 Another flow chart of the data query method according to an embodiment of the present disclosure is schematically shown.

[0112] like Figure 7 As shown, in Figure 6 Based on the illustrated embodiment, the data query method may further include S710.

[0113] In operation S710, in response to the number of unique values ​​being greater than a preset threshold number, and / or in response to the number of unique values ​​and the number of parameters corresponding to the target indexing method not satisfying a preset quantitative relationship, the first data table and the second data table are associated with a target association relationship to obtain a target data table.

[0114] Specifically, when there are too many target associated variables, for example, when the number of unique values ​​corresponding to the associated variables with bitmap indexes is greater than the threshold, the method of mapping and converting the first associated variable and the second associated variable and screening in steps will be inefficient. Therefore, in this case, the default association mode is used to associate the data tables.

[0115] Figure 8 Another flow chart of the data query method according to an embodiment of the present disclosure is schematically shown.

[0116] like Figure 8 As shown, based on any of the foregoing embodiments, the data query method may further include S810.

[0117] In operation 810, in response to the absence of a target associated variable in the associated variables of the first data table and the second data table, the first data table and the second data table are associated with each other in a target associated relationship to obtain a target data table. For example, if there is no associated variable using a bitmap index in the first data table and the second data table, then it means that the first data table and the second data table cannot be associated with each other in the aforementioned manner. Figure 2-Figure 6The embodiment shown in the figure performs mapping conversion of key fields to improve indexing efficiency. For example, when there is no associated variable indexed by bitmap, the association of each associated variable needs to be searched in the entire table in the data table to ensure that no data is missed. This method requires repeated traversal of the second data table. If there is an associated variable indexed by bitmap as the target associated variable, due to the fast indexing characteristics of bitmap, repeated traversal is not required, and the disclosed method can be used. Figure 2-Figure 6 The illustrated embodiment performs a further acceleration.

[0118] Fig. 9 A block diagram of a data query device according to an embodiment of the present disclosure is schematically shown.

[0119] like Fig. 9 As shown, the data query device 900 includes a first generating module 910 , a second generating module 920 , an associating module 930 and a query module 940 .

[0120] The first generating module 910 is used to generate filtering items according to a target associated variable list between the first data table and the second data table when the first data table and the second data table are associated with each other by a target associated relationship, the data volume of the first data table is smaller than the data volume of the second data table, the target associated variable list includes at least a first associated variable and a second associated variable, the second associated variable represents a segmented range of the first associated variable according to a preset segmentation condition for segmenting the value range of the first associated variable, and the filtering items include at least a second associated variable that meets the preset condition; in some embodiments, the first generating module 910 can also be used to execute operation S210 in the aforementioned method, which will not be elaborated here.

[0121] The second generation module 920 is used to generate a third data table based on the filter items and the second data table, and the third data table records at least part of the content in the second data table; in some embodiments, the second generation module 920 can also be used to execute operation S220 in the aforementioned method, which is not elaborated here.

[0122] The association module 930 is used to associate the first data table and the third data table with a target association relationship to obtain a target data table; in some embodiments, the association module 930 can also be used to perform operation S230 in the aforementioned method, which will not be described in detail here.

[0123] The query module 940 is used to query the target content in the first associated variable based on the target data table. In some embodiments, the query module 940 can also be used to perform operation S240 in the above method, which will not be described in detail here.

[0124] According to the embodiments of the present invention, any one or more of the modules, submodules, units, and subunits, or at least part of the functions of any one of them can be implemented in one module. According to the embodiments of the present invention, any one or more of the modules, submodules, units, and subunits can be split into multiple modules for implementation. According to the embodiments of the present invention, any one or more of the modules, submodules, units, and subunits can be at least partially implemented as hardware circuits, such as field programmable gate arrays (FPGAs), programmable logic arrays (PLAs), systems on chips, systems on substrates, systems on packages, application specific integrated circuits (ASICs), or can be implemented by hardware or firmware in any other reasonable way of integrating or packaging circuits, or can be implemented in any one of the three implementation methods of software, hardware, and firmware, or in any appropriate combination of any of them. Alternatively, according to the embodiments of the present invention, one or more of the modules, submodules, units, and subunits can be at least partially implemented as computer program modules, and when the computer program modules are run, the corresponding functions can be performed.

[0125] For example, any of the first generation module 910, the second generation module 920, the association module 930, and the query module 940 can be combined in one module / unit / sub-unit for implementation, or any of the modules / units / sub-units can be split into multiple modules / units / sub-units. Alternatively, at least part of the functions of one or more of these modules / units / sub-units can be combined with at least part of the functions of other modules / units / sub-units and implemented in one module / unit / sub-unit. According to an embodiment of the present disclosure, at least one of the first generation module 910, the second generation module 920, the association module 930, and the query module 940 can be at least partially implemented as a hardware circuit, such as a field programmable gate array (FPGA), a programmable logic array (PLA), a system on a chip, a system on a substrate, a system on a package, an application specific integrated circuit (ASIC), or can be implemented by hardware or firmware such as any other reasonable way of integrating or packaging the circuit, or by any one of the three implementation methods of software, hardware, and firmware, or by a suitable combination of any of them. Alternatively, at least one of the first generating module 910 , the second generating module 920 , the associating module 930 , and the querying module 940 may be at least partially implemented as a computer program module, and when the computer program module is executed, a corresponding function may be performed.

[0126] It should be noted that the data processing system part in the embodiments of the present disclosure corresponds to the data processing method part in the embodiments of the present disclosure. The description of the data processing system part specifically refers to the data processing method part, which will not be repeated here.

[0127] Fig.10A block diagram of an electronic device suitable for implementing the method described above according to an embodiment of the present disclosure is schematically shown. Fig.10 The electronic device shown is only an example and should not bring any limitation to the functions and scope of use of the embodiments of the present disclosure.

[0128] like Fig.10 As shown, the electronic device 1000 according to an embodiment of the present disclosure includes a processor 1001, which can perform various appropriate actions and processes according to a program stored in a read-only memory (ROM) 1002 or a program loaded from a storage part 1008 to a random access memory (RAM) 1003. The processor 1001 may include, for example, a general-purpose microprocessor (such as a CPU), an instruction set processor and / or a related chipset and / or a special-purpose microprocessor (for example, an application-specific integrated circuit (ASIC)), etc. The processor 1001 may also include an onboard memory for caching purposes. The processor 1001 may include a single processing unit or multiple processing units for performing different actions of the method flow according to an embodiment of the present disclosure.

[0129] In RAM 1003, various programs and data required for the operation of electronic device 1000 are stored. Processor 1001, ROM 1002 and RAM 1003 are connected to each other via bus 1004. Processor 1001 performs various operations of the method flow according to the embodiment of the present disclosure by executing the program in ROM 1002 and / or RAM 1003. It should be noted that the program can also be stored in one or more memories other than ROM 1002 and RAM 1003. Processor 1001 can also perform various operations of the method flow according to the embodiment of the present disclosure by executing the program stored in one or more memories.

[0130] According to an embodiment of the present disclosure, the electronic device 1000 may further include an input / output (I / O) interface 1005, which is also connected to the bus 1004. The electronic device 1000 may further include one or more of the following components connected to the input / output (I / O) interface 1005: an input portion 1006 including a keyboard, a mouse, etc.; an output portion 1007 including a cathode ray tube (CRT), a liquid crystal display (LCD), etc., and a speaker, etc.; a storage portion 1008 including a hard disk, etc.; and a communication portion 1009 including a network interface card such as a LAN card, a modem, etc. The communication portion 1009 performs communication processing via a network such as the Internet. A drive 1010 is also connected to the input / output (I / O) interface 1005 as needed. A removable medium 1011, such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, etc., is installed on the drive 1010 as needed, so that a computer program read therefrom is installed into the storage portion 1008 as needed.

[0131] According to an embodiment of the present disclosure, the method flow according to an embodiment of the present disclosure can be implemented as a computer software program. For example, an embodiment of the present disclosure includes a computer program product, which includes a computer program carried on a computer-readable storage medium, and the computer program contains a program code for executing the method shown in the flowchart. In such an embodiment, the computer program can be downloaded and installed from the network through the communication part 1009, and / or installed from the removable medium 1011. When the computer program is executed by the processor 1001, the above-mentioned functions defined in the system of the embodiment of the present disclosure are executed. According to an embodiment of the present disclosure, the system, equipment, device, module, unit, etc. described above can be implemented by a computer program module.

[0132] The present disclosure also provides a computer-readable storage medium, which may be included in the device / apparatus / system described in the above embodiments; or may exist independently without being assembled into the device / apparatus / system. The above computer-readable storage medium carries one or more programs, and when the above one or more programs are executed, the method according to the embodiment of the present disclosure is implemented.

[0133] According to an embodiment of the present disclosure, the computer-readable storage medium may be a non-volatile computer-readable storage medium. For example, it may include, but is not limited to: a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination thereof. In the present disclosure, a computer-readable storage medium may be any tangible medium containing or storing a program that may be used by or in combination with an instruction execution system, apparatus, or device.

[0134] For example, according to an embodiment of the present disclosure, the computer-readable storage medium may include the ROM 1002 and / or the RAM 1003 described above and / or one or more memories other than the ROM 1002 and the RAM 1003 .

[0135] The embodiments of the present disclosure also include a computer program product, which includes a computer program, which contains program code for executing the method provided by the embodiments of the present disclosure. When the computer program product runs on an electronic device, the program code is used to enable the electronic device to implement the data query method provided by the embodiments of the present disclosure.

[0136] When the computer program is executed by the processor 1001, the above functions defined in the system / device of the embodiment of the present disclosure are executed. According to the embodiment of the present disclosure, the system, device, module, unit, etc. described above can be implemented by a computer program module.

[0137] In one embodiment, the computer program may rely on tangible storage media such as optical storage devices, magnetic storage devices, etc. In another embodiment, the computer program may also be transmitted and distributed in the form of signals on a network medium, and downloaded and installed through the communication part 1009, and / or installed from the removable medium 1011. The program code contained in the computer program may be transmitted using any appropriate network medium, including but not limited to: wireless, wired, etc., or any suitable combination of the above.

[0138] According to an embodiment of the present disclosure, the program code for executing the computer program provided by the embodiment of the present disclosure can be written in any combination of one or more programming languages. Specifically, these computing programs can be implemented using high-level process and / or object-oriented programming languages, and / or assembly / machine languages. Programming languages ​​include, but are not limited to, Java, C++, python, "C" language or similar programming languages. The program code can be executed entirely on the user computing device, partially on the user device, partially on the remote computing device, or entirely on the remote computing device or server. In the case of a remote computing device, the remote computing device can be connected to the user computing device through any type of network, including a local area network (LAN) or a wide area network (WAN), or can be connected to an external computing device (for example, using an Internet service provider to connect through the Internet).

[0139] The flowcharts and block diagrams in the accompanying drawings illustrate the possible implementation architecture, functions and operations of the systems, methods and computer program products according to various embodiments of the present disclosure. In this regard, each box in the flowchart or block diagram may represent a module, a program segment, or a part of a code, and the above-mentioned module, program segment, or a part of the code contains one or more executable instructions for implementing the specified logical function. It should also be noted that in some alternative implementations, the functions marked in the box may also occur in an order different from that marked in the accompanying drawings. For example, two boxes represented in succession can actually be executed substantially in parallel, and they can sometimes be executed in the opposite order, depending on the functions involved. It should also be noted that each box in the block diagram or flowchart, and the combination of boxes in the block diagram or flowchart, can be implemented with a dedicated hardware-based system that performs a specified function or operation, or can be implemented with a combination of dedicated hardware and computer instructions. It can be understood by those skilled in the art that the features recorded in the various embodiments of the present disclosure can be combined and / or combined in a variety of ways, even if such a combination or combination is not explicitly recorded in the present disclosure. In particular, without departing from the spirit and teaching of the present disclosure, the features described in the various embodiments of the present disclosure may be combined and / or combined in a variety of ways. All of these combinations and / or combinations fall within the scope of the present disclosure.

[0140] The embodiments of the present disclosure are described above. However, these embodiments are only for illustrative purposes and are not intended to limit the scope of the present disclosure. Although the embodiments are described above, this does not mean that the measures in the various embodiments cannot be used in combination to advantage. Without departing from the scope of the present disclosure, those skilled in the art may make a variety of substitutions and modifications, which should all fall within the scope of the present disclosure.

Claims

1. A data query method, comprising: In the case where a first data table and a second data table are associated with each other in a target association relationship, a screening item is generated according to a target association variable list between the first data table and the second data table, the data volume of the first data table is smaller than the data volume of the second data table, the target association variable list includes at least a first association variable and a second association variable, the second association variable represents a segmentation range of the first association variable according to a preset segmentation condition, and the screening item includes at least the second association variable that meets the preset condition; Generate a third data table according to the screening items and the second data table, wherein the third data table records at least part of the content in the second data table; Associating the first data table with the third data table by the target association relationship to obtain a target data table; Based on the target data table, the target content in the first associated variable is queried.

2. The method according to claim 1, wherein: The first associated variable represents an associated variable with a target indexing method, and the target indexing method represents that the number of different values ​​in the index field is within a preset number range.

3. The method according to claim 2, wherein: Before generating the filter items, include: Determine whether the first associated variable exists in the target associated variable list, and the value range corresponding to the first associated variable is greater than a preset range threshold; In the case where the first associated variable exists, determining whether there is a second associated variable corresponding to the first associated variable in the target associated variable list; The generated filter items include: In the case where the second associated variable exists, the values ​​corresponding to the target associated variable are deduplicated based on the target associated variable list to generate the filter item.

4. The method according to claim 3, wherein: The step of removing duplicate values ​​corresponding to the target associated variables based on the target associated variable list to generate filter items includes: Based on the target-associated variable list, determining a unique value corresponding to each of the target-associated variables, wherein the unique value represents a combination of different numerical values ​​corresponding to each of the target-associated variables in the first data table; The screening items are generated according to the unique values, wherein the combinations of different numerical values ​​corresponding to the target-related variables in the screening items are not repeated.

5. The method according to claim 3, wherein: Before generating the filter items, the method further includes: In the case that there is no second associated variable corresponding to the first associated variable, the second associated variable is generated based on a preset segmentation condition, and the second associated variable is added to the target associated variable list.

6. The method according to claim 5, wherein: The generating of the second associated variable comprises: The second associated variable having the target index mode is generated.

7. The method according to claim 4, wherein: The generated filter items include: In response to the number of the unique values ​​being less than or equal to a preset number threshold, and / or in response to the number of the unique values ​​and the number of parameters corresponding to the target indexing method satisfying a preset quantity relationship, a filter item is generated according to each of the unique values.

8. The method according to claim 4, wherein: The step of generating a third data table according to the screening items and the second data table includes: The second data table is filtered according to the filtering item to generate a third data table, wherein each row of data in the third data table includes the unique value.

9. The method according to claim 2, wherein: The target indexing method is bitmap indexing.

10. The method according to claim 1, wherein: The target association method is a hash association using the target association variable as an association field.

11. A data query device, comprising: A first generating module is configured to generate a screening item according to a target association variable list between the first data table and the second data table when a first data table and a second data table are associated with each other in a target association relationship, wherein the data volume of the first data table is smaller than the data volume of the second data table, the target association variable list includes at least a first association variable and a second association variable, the second association variable represents a segmentation range of the first association variable according to a preset segmentation condition, and the screening item includes at least the second association variable that meets the preset condition; A second generating module, configured to generate a third data table according to the screening items and the second data table, wherein the third data table records at least part of the content in the second data table; as well as an associating module, configured to associate the first data table with the third data table by the target associative relationship to obtain a target data table; as well as A query module is used to query the target content in the first associated variable based on the target data table.

12. An electronic device comprising: one or more processors; a memory for storing one or more programs, When the one or more programs are executed by the one or more processors, the one or more processors implement the method according to any one of claims 1 to 10.

13. A computer-readable storage medium having executable instructions stored thereon, which, when executed by a processor, enables the processor to implement the method according to any one of claims 1 to 10.

14. A computer program product comprising a computer program, which, when executed by a processor, implements the method according to any one of claims 1 to 10.

Images