A data connection method, device, storage medium and electronic device

Abstract

This specification discloses a data connection method, apparatus, storage medium, and electronic device. In the data connection method provided in this specification, a semi-join query statement is received, the first data table and the second data table connected by the semi-join query statement are obtained, and the join condition in the semi-join query statement is determined; when there is a non-equality condition in the join condition, the second data table is grouped according to the join condition; according to the join condition, the non-equality candidate keys included in the non-equality condition for joining in the second data table are determined; for each group of data, according to the non-equality condition and the values of the non-equality candidate keys of each row of data in this group of data, a specified key value is determined, and the data of the row where the specified key value is located is determined as the specified row data; according to the specified row data in each group of data, a deduplicated second data table is constructed; the connection method between the first data table and the deduplicated second data table is modified to an inner join to obtain a query result.

Description

Technical Field

[0001] This specification relates to the field of database technology, and particularly to a data connection method, apparatus, storage medium, and electronic device. Background Art

[0002] In a relational database system, during the process of executing a business, it may be necessary to find out whether there is matching data in two different data tables. When comparing two data tables, a semi-join is usually used to connect the two data tables. However, under a semi-join form of query statement, the nested loop relationship between the two data tables is fixed, and the selection space of the execution plan is limited. Therefore, in many cases, the semi-join is modified to an inner join to expand the selection space of the execution plan, better adjust the execution plan, and improve the query efficiency.

[0003] Generally, for the same data table, a semi-join will only return the result once for each row, while an inner join may return the result multiple times for each row. When modifying a semi-join to an inner join, it is necessary to additionally consider how to ensure that the result is still only returned once for each row. In the prior art, when modifying the semi-join in a query statement to an inner join, one of the data tables is first de-duplicated according to the equality condition, and then an inner join is performed between the other data table and the de-duplicated data table. However, this method can only handle semi-joins where all the join conditions in the query statement are equality conditions, and the applicable scenarios are limited. When non-equality conditions appear in the join conditions, there may still be multiple returns for the same row in the query result.

[0004] Therefore, how to handle the modification of a semi-join to an inner join under non-equality conditions in a database system is an urgent problem to be solved. Summary of the Invention

[0005] This specification provides a data connection method, apparatus, storage medium, and electronic device to partially solve the above problems existing in the prior art.

[0006] This specification adopts the following technical solutions:

[0007] This specification provides a data connection method, including:

[0008] Receiving a semi-join query statement, obtaining a first data table and a second data table connected by the semi-join query statement, and determining the join condition in the semi-join query statement;

[0009] When there is a non-equality condition in the join condition, grouping the second data table according to the join condition;

[0010] According to the join condition, determining the non-equality candidate keys included in the non-equality conditions for joining in the second data table;

[0011] For each group of data, determine a specified key value according to the non-equivalence condition and the values of the non-equivalence candidate keys of each row of data in this group of data, and determine the data of the row where the specified key value is located as the specified row data;

[0012] Construct a deduplicated second data table according to the specified row data in each group of data;

[0013] Modify the connection method between the first data table and the deduplicated second data table to an inner join to obtain a query result.

[0014] Optionally, group the second data table according to the connection condition, specifically including:

[0015] When there is an equivalence condition in the connection condition, determine that the equivalence candidate key is included in the equivalence conditions participating in the connection in the second data table;

[0016] Group the rows of data in the second data table according to the equivalence candidate key, where the values of the equivalence candidate keys of the rows of data in each group are the same.

[0017] Optionally, group the second data table according to the connection condition, specifically including:

[0018] When there is no equivalence condition in the connection condition, divide the second data table as a whole into one group.

[0019] Optionally, determine a specified key value according to the non-equivalence condition and the values of the non-equivalence candidate keys of each row of data in this group of data, specifically including:

[0020] When the non-equivalence condition is a greater-than condition, select the minimum value of the values of the non-equivalence candidate keys in the rows of data in this group of data as the specified key value;

[0021] When the non-equivalence condition is a less-than condition, select the maximum value of the values of the non-equivalence candidate keys in the rows of data in this group of data as the specified key value.

[0022] Optionally, the method further includes:

[0023] When there is more than one non-equivalence condition in the connection condition, modify the connection method between the first data table and the second data table to an inner join to obtain a connection result, and deduplicate the connection result to obtain a query result.

[0024] Optionally, deduplicate the connection result, specifically including:

[0025] For any two rows of data in the connection result, when the two rows of data are the same, delete one of the two rows of data until there are no two identical rows of data in the connection result.

[0026] Optionally, the method further includes:

[0027] When there is no non-equivalent condition in the connection condition, determine the equivalent candidate keys included in the equivalent conditions for joining in the second data table according to the connection condition;

[0028] For any two rows of data in the second data table, when the values of the same equivalent candidate key in the two rows of data are the same, delete one of the two rows of data until there are no two rows of data with the same value of the same equivalent candidate key in the second data table;

[0029] Modify the connection method between the first data table and the deduplicated second data table to an inner join to obtain a query result.

[0030] This specification provides a data connection device, including:

[0031] A receiving module, configured to receive a semi-join query statement, obtain the first data table and the second data table joined by the semi-join query statement, and determine the connection condition in the semi-join query statement;

[0032] A grouping module, configured to group the second data table according to the connection condition when there is a non-equivalent condition in the connection condition;

[0033] A determining module, configured to determine the non-equivalent candidate keys included in the non-equivalent conditions for joining in the second data table according to the connection condition;

[0034] A screening module, configured to, for each group of data, determine a specified key value according to the non-equivalent condition and the values of the non-equivalent candidate keys of each row of data in the group, and determine the data in the row where the specified key value is located as the specified row data;

[0035] A construction module, configured to construct a deduplicated second data table according to the specified row data in each group of data;

[0036] A first modification module, configured to modify the connection method between the first data table and the deduplicated second data table to an inner join to obtain a query result.

[0037] This specification provides a computer-readable storage medium, where the storage medium stores a computer program, and when the computer program is executed by a processor, the above data connection method is implemented.

[0038] This specification provides an electronic device, including a memory, a processor, and a computer program stored on the memory and executable on the processor. When the processor executes the program, the above data connection method is implemented.

[0039] The above at least one technical solution adopted in this specification can achieve the following beneficial effects:

[0040] In the data connection method provided in this specification, a semi-join query statement is received, the first data table and the second data table connected by the semi-join query statement are obtained, and the join condition in the semi-join query statement is determined; when there is a non-equality condition in the join condition, the second data table is grouped according to the join condition; according to the join condition, the non-equality candidate keys included in the non-equality condition for joining in the second data table are determined; for each group of data, according to the non-equality condition and the values of the non-equality candidate keys of each row of data in this group, the specified key value is determined, and the data of the row where the specified key value is located is determined as the specified row data; according to the specified row data in each group of data, a deduplicated second data table is constructed; the join method between the first data table and the deduplicated second data table is modified to an inner join to obtain the query result.

[0041] It can be seen from the above method that when the data connection method provided in this specification is adopted, in the case of a non-equality condition in the join condition, the semi-join query statement in the database system can be modified to an inner join query statement, increasing the selection space of the query statement execution plan, enabling the query optimizer to select a better execution plan. BRIEF DESCRIPTION OF THE DRAWINGS

[0042] The drawings described herein are used to provide a further understanding of this specification, and constitute a part of this specification. The schematic embodiments of this specification and their descriptions are used to explain this specification and do not constitute an improper limitation to this specification. In the attached

[0043] In the figure:

[0044] Figure 1 is a schematic flowchart of a data connection method provided in this specification;

[0045] Figure 2 is a schematic diagram of a data connection device provided in this specification;

[0046] Figure 3 corresponds to Figure 1 is a schematic diagram of an electronic device provided in this specification. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0047] In the database language Structured Query Language (SQL), there are multiple ways to join two data tables. Among them, the two relatively common ways are inner join and semi-join, which are represented by join and semi join in SQL respectively. Among them, for each row of data in the data table, in the semi-join method, after finding the data that meets the join condition, the corresponding output will be made and the query will stop, and then the next set of data will be queried; while in the inner-join method, the entire data table will be traversed, and as long as a piece of data that meets the join condition is found, an output will be made once.

[0048] For example, a common form of semi-join in SQL can be as follows:

[0049] SELECT * FROM T1 SEMI JOIN T2 ON T1.C1 = T2.C1 AND T1.C2 = T2.C2;

[0050] Among them, T1 and T2 represent two data tables, C1 and C2 represent the keys existing in the data tables, and * represents all columns; SELECT is used to select data from the data table, FROM is used to represent the source of the selected data, SEMI JOIN is a semi-join, and the statement after ON is used to represent the join condition. The above statement means that in the case of a semi-join between data table T1 and data table T2, when there are two rows of data in data tables T1 and T2 where the values of key C1 and key C2 are equal, select that row of data in data table T1 for output.

[0051] Suppose data table T1 is as shown in Table 1 below, and data table T2 is as shown in Table 2 below:

[0052] C1 C2 C3 0 9 10 1 8 20 2 7 30 3 6 40

[0053] Table 1

[0054]

[0055]

[0056] In the case of using the above semi-join query statement, the returned output will be as shown in Table 3:

[0057] C1 C2 C3 0 9 10 1 8 20

[0058] Table 3

[0059] There are a total of 4 rows of data in data table T1 and 8 rows of data in data table T2. For the join condition T1.C1 = T2.C1 AND T1.C2 = T2.C2 in the above semi-join query statement, there are two rows of data (the 1st and 5th rows) in data table T2 that match the first row of data in data table T1, two rows of data (the 2nd and 6th rows) that match the second row of data in data table T1, and no data matches the third and fourth rows of data in data table T1. In the semi-join mode, no matter how many rows of data meet the conditions, they will only be output once, and data that does not meet the conditions will not be output. Therefore, the final result is as shown in Table 3, where the first and second rows of data table T1 are output.

[0060] On the other hand, a common form of inner join in SQL can be as follows:

[0061] SELECT T1.* FROM T1 JOIN T2 ON T1.C1 = T2.C1 AND T1.C2 = T2.C2;

[0062] In fact, the inner join query statement here is to change the semi-join in the above semi-join query statement to an inner join, with no other changes. Still taking Table 1 as data table T1 and Table 2 as data table T2 as an example, in the inner join mode, the output result will be as shown in Table 4:

[0063] C1 C2 C3 0 9 10 0 9 10 1 8 20 1 8 20

[0064] Table 4

[0065] Since the join condition in this inner join is the same as that in the above semi-join, the analysis of the above semi-join still applies here. That is, for the join condition T1.C1 = T2.C1 AND T1.C2 = T2.C2, there are two rows of data (the 1st and 5th rows) in data table T2 that match the first row of data in data table T1, two rows of data (the 2nd and 6th rows) that match the second row of data in data table T1, and no data matches the third and fourth rows of data in data table T1. In an inner join, as long as there is one row of data in data table T2 that meets the join condition, the data in data table T1 will be output correspondingly once. Therefore, the final output result is as shown in Table 4, where the first and second rows of data table T1 are each output twice.

[0066] It should be noted that in order to make this specification more concise and understandable, in all cases provided in this specification, all columns in the data table are selected with * after SELECT. Those skilled in the art should understand that in actual applications, only the required fields can be selected after SELECT to reduce unnecessary computing overhead.

[0067] As can be seen from the example, in terms of the results, there may be duplicate outputs in the inner join. However, in terms of the specific implementation, the choice space of the inner join execution plan is usually larger than that of the semi-join execution plan. This is because in the semi-join method, only the relatively earlier data table in the statement can be used as the driving table, while there is no such limitation in the inner join method.

[0068] Still taking the above semi-join and inner-join query statements as an example, in the semi-join query statement SELECT * FROM T1 SEMI JOIN T2 ON T1.C1 = T2.C1 AND T1.C2 = T2.C2;, the query optimizer can only generate a nested loop join with data table T1 as the driving table and data table T2 as the inner table; but in the inner-join query statement SELECT T1.* FROM T1 JOIN T2 ON T1.C1 = T2.C1 AND T1.C2 = T2.C2;, the query optimizer can either choose data table T1 as the driving table or choose data table T2 as the driving table to generate more execution plans. It is not difficult to imagine that in some data distribution cases, choosing data table T2 as the driving table can obtain a better execution plan, which cannot be achieved in the semi-join. Therefore, in many cases, changing the semi-join to an inner join can achieve better results.

[0069] Still taking the above inner-join query statement as an example, currently, the traditional way to change the semi-join to an inner join is as follows.

[0070] SELECT * FROM T1 SEMI JOIN T2 ON T1.C1 = T2.C1 AND T1.C2 = T2.C2;

[0071] Rewrite it as

[0072] SELECT T1.* FROM T1 JOIN (SELECT DISTINCT C1, C2 FROM T2) V ON T1.C1 =

[0073] V.C1 AND T1.C2 = V.C2;

[0074] Among them, V represents a new data table, and the parentheses before V indicate that the data table V is created according to the rules given inside the parentheses; DISTINCT is the duplicate removal operation. In the above statement, the content inside the parentheses means to remove duplicates from the data table T2 based on C1 and C2. Specifically, in the data table T2, when there are multiple rows of data and the values of C1 and C2 in these rows are the same, only the first row of data is retained, and the other rows of data that appear later are deleted. Still taking the data table T2 as Table 2 as an example, after performing the above duplicate removal operation on the data table T2, the resulting result can be as follows:

[0075]

[0076]

[0077] Table 5

[0078] It can be seen that in Table 2, the values of C1 and C2 in the data of the first row and the fifth row, the second row and the sixth row, the third row and the seventh row, and the fourth row and the eighth row are respectively the same. Therefore, after duplicate removal, only the first four rows of data that appear first are retained, and the resulting data table T2 after duplicate removal is the data table V. At this time, an inner join is performed on the data table T1 and the data table V. The first row in V satisfies the join condition with the first row in T1, and the second row in V satisfies the join condition with the second row in T1. The other data do not satisfy the join condition. The final resulting result will still be as shown in Table 3. In this way, the same result as the semi-join is obtained under the inner join.

[0079] However, the currently adopted method of directly removing duplicates from the data table T2, that is, from the inner table, can only function when the join condition does not contain non-equality conditions. When there are non-equality conditions in the join condition, the above rewriting method can no longer be implemented.

[0080] Therefore, this specification provides a new data connection method to solve the problem that the semi-join cannot be converted into an inner join when there are non-equality conditions.

[0081] To make the purpose, technical solution, and advantages of this specification clearer, the technical solution of this specification will be clearly and completely described below in conjunction with specific embodiments of this specification and the corresponding drawings. Obviously, the described embodiments are only a part of the embodiments of this specification, rather than all the embodiments. Based on the embodiments in this specification, all other embodiments obtained by those of ordinary skill in the art without creative efforts belong to the scope protected by this specification.

[0082] The following will detail the technical solutions provided by each embodiment of this specification in conjunction with the drawings.

[0083] Figure 1The flowchart of a data connection method provided in this specification includes the following steps:

[0084] S100: Receive a semi-join query statement, obtain the first data table and the second data table connected by the semi-join query statement, and determine the join condition in the semi-join query statement.

[0085] In this specification, the execution entity for implementing the data connection method may refer to a specified device such as a server set up in a service platform. For the convenience of description, this specification only takes the server as the execution entity as an example to describe a data connection method provided in this specification.

[0086] The final effect of the data connection method provided in this specification is to change the semi-join method in the database system to an inner-join method. Therefore, it is necessary to first receive the semi-join query statement in the database system. After receiving the semi-join query statement, the first data table and the second data table connected by the semi-join query statement can be determined. At the same time, the join condition between the first data table and the second data table can also be determined. Take the following semi-join query statement as an example:

[0087] SELECT*FROM T1 SEMI JOIN T2 ON T1.C1=T2.C1 AND T1.C2=T2.C2;

[0088] When receiving the above semi-join query statement, it can be determined that the first data table is data table T1, the second data table is data table T2, and the join condition is T1.C1=T2.C1 AND T1.C2=T2.C2.

[0089] It is worth mentioning that in each join statement, the number of join conditions is not fixed and can be set according to specific requirements. For example, in the above example given, there are two join conditions, namely T1.C1=T2.C1 and T1.C2=T2.C2.

[0090] At the same time, it can be seen that the above two join conditions are both equivalent conditions connected by the equal sign "=". In actual applications, in addition to equivalent conditions, there are also non-equivalent conditions connected by the greater-than sign ">" or the less-than sign "<".

[0091] For example, consider the following semi-join query statement:

[0092] SELECT*FROM T1 SEMI JOIN T2 ON T1.C1=T2.C1 AND T1.C2>T2.C2;

[0093] In this semi-join query statement, there are two join conditions, namely T1.C1 = T2.C1 and T1.C2 > T2.C2. Among them, the number of equal-value conditions is 1, which is T1.C1 = T2.C1; the number of non-equal-value conditions is also 1, which is T1.C2 > T2.C2.

[0094] For another example, consider the following semi-join query statement:

[0095] SELECT * FROM T1 SEMI JOIN T2 ON T1.C1 = T2.C1 AND T1.C2 > T2.C2 AND T1.C3 < T2.C3;

[0096] In this semi-join query statement, there are three join conditions, namely T1.C1 = T2.C1, T1.C2 > T2.C2, and T1.C3 < T2.C3. Among them, the number of equal-value conditions is 1, which is T1.C1 = T2.C1; the number of non-equal-value conditions is 2, which are T1.C2 > T2.C2 and T1.C3 < T2.C3 respectively.

[0097] S102: When there are non-equal-value conditions in the join conditions, group the second data table according to the join conditions.

[0098] In the data connection method provided in this specification, the way to modify the semi-join to an inner join can be determined according to the number of non-equal-value conditions included in the join conditions of the semi-join query statement.

[0099] When there is only one non-equal-value condition in the join conditions, the duplicate removal operation can be first performed on the second data table, and then the join method between the first data table and the second data table after duplicate removal is modified from a semi-join to an inner join to obtain the final join result. When performing duplicate removal on the second data table, the second data table can be grouped according to the join conditions first, and each group of data separated is de-duplicated according to the non-equal-value conditions in the join conditions.

[0100] Furthermore, there may be two situations in the join conditions: the existence of equal-value conditions and the non-existence of equal-value conditions. For these two different situations, different ways can be adopted to group the second data table.

[0101] Specifically, when there are equal-value conditions in the join conditions, determine the equal-value candidate keys participating in the join in the second data table; group each row of data in the second data table according to the equal-value candidate keys, where the values of the equal-value candidate keys of each row of data in each group are the same.

[0102] Among them, the equivalent candidate key is the key participating in the join included in the equivalent condition, and the non-equivalent candidate key is the key participating in the join included in the non-equivalent condition. When grouping, it can be ensured that in the same group of data, the values of the equivalent candidate keys of each row of data are the same, thereby ensuring that the data in the same group can meet the same equivalent conditions.

[0103] For example, still consider the following semi-join query statement:

[0104] SELECT * FROM T1 SEMI JOIN T2 ON T1.C1 = T2.C1 AND T1.C2 > T2.C2;

[0105] In this semi-join query statement, the equivalent condition is T1.C1 = T2.C1, and the non-equivalent condition is T1.C2 > T2.C2. Correspondingly, the equivalent candidate key is C1, and the non-equivalent candidate key is C2. Then at this time, the data in the data table T2 can be grouped according to the equivalent candidate key C1 in the data table T2. Still taking the data table T2 as Table 2 as an example, it can be obtained that the values of C1 in the first row of data and the fifth row of data in Table 2 are the same and can be divided into one group; the values of C1 in the second row of data and the sixth row of data are the same and can be divided into one group; the values of C1 in the third row of data and the seventh row of data are the same and can be divided into one group; the values of C1 in the fourth row of data and the eighth row of data are the same and can be divided into one group.

[0106] In another case, when there is no equivalent condition in the join condition, then it can be considered that all the data in the second data table have met the same equivalent conditions, that is to say, all the data in the second data table can be divided into one group. Specifically, when there is no equivalent condition in the join condition, the second data table can be divided into one group as a whole.

[0107] S104: Determine the non-equivalent candidate keys included in the non-equivalent conditions for joining in the second data table according to the join condition.

[0108] In the embodiment given in step S102, the equivalent candidate keys are described. Similarly, the non-equivalent candidate keys are the candidate keys included in the non-equivalent conditions in the join condition. Still taking the following semi-join query statement as an example:

[0109] SELECT * FROM T1 SEMI JOIN T2 ON T1.C1 = T2.C1 AND T1.C2 > T2.C2;

[0110] In this semi-join query statement, the non-equivalent condition is T1.C2 > T2.C2, and the key included in it is C2, that is to say, C2 is the non-equivalent candidate key.

[0111] S106: For each set of data, determine a specified key value according to the non-equivalent condition and the values of the non-equivalent candidate keys of each row of data in this set of data, and determine the data of the row where the specified key value is located as the specified row data

[0112] In step S102, the data in the second data table is grouped. In this step, each set of data in the second data table can be filtered

[0113] In a semi-join, for each row in the first data table, only one query result may be returned. However, due to the characteristics of an inner join, the same row of data in the first data table may be returned multiple times when returning the query result. Therefore, in order to keep the query result unchanged after modifying the semi-join to an inner join, some processing needs to be performed on the second data table so that for each row of data in the first data table, at most one matching row of data can be queried in the second data table

[0114] Based on this, in step S102, the data in the second data table is first grouped according to the equivalent condition in the join condition. Since the values of the equivalent candidate keys of each row of data are the same in the same set of data, and the values of the equivalent candidate keys of each row of data are different in different sets of data. Therefore, for any row of data in the first data table, it is only possible to match different rows of data in one of the groups in the second data table at the same time, and it is impossible to match the data in different groups at the same time

[0115] And in order to ensure that for each row of data in the first data table, only one query result will be returned, it is necessary to filter each set of data in the second data table and only retain one specified row of data that is closest to the join condition. When filtering the specified row of data in each set of data, it is necessary to determine the specified key value according to the specific content of the non-equivalent condition and the values of the non-equivalent candidate keys of each row of data in this group, and determine the data of the row where the specified key value is located as the specified row data

[0116] Furthermore, the non-equivalent condition can generally be divided into two cases: greater than condition and less than condition. Among them, the greater than condition is that the value of the non-equivalent candidate key in the first data table is greater than the value of the non-equivalent candidate key in the second data table. For example: T1.C2 > T2.C2; the greater than condition is that the value of the non-equivalent candidate key in the first data table is less than the value of the non-equivalent candidate key in the second data table. For example: T1.C3 < T2.C3. For the two different non-equivalent conditions, the specified key value can be determined in the opposite way

[0117] Specifically, when the non-equivalent condition is a greater-than condition, among the data rows of this group of data, select the value with the smallest value of the non-equivalent candidate key and determine it as the specified key value; when the non-equivalent condition is a less-than condition, among the data rows of this group of data, select the value with the largest value of the non-equivalent candidate key and determine it as the specified key value.

[0118] For example, assume that the non-equivalent condition is T1.C2 > T2.C2. For any row of data in data table T1, in data table T2, among the group of data that satisfies the equivalent condition with this row of data, if there is a row of data whose C2 value is less than the C2 value of this row of data, then this row of data in T1 will be returned; and if there is no row of data in that group of data whose C2 value is less than the C2 value of this row of data, then this row of data in T1 will not be returned. Therefore, it can be imagined that if the row of data with the smallest C2 value in the group of data in data table T2 that satisfies the equivalent condition with this row of data does not satisfy T1.C2 > T2.C2, then all the data in this group of data will not satisfy the non-equivalent condition. Therefore, the value with the smallest value of the non-equivalent candidate key can be selected from each group of data in data table T2, determined as the specified key value, and the row of data where the specified key value is located is determined as the specified row of data.

[0119] Similarly, when the non-equivalent condition is a less-than condition, the value with the largest value of the non-equivalent candidate key can be selected from each group of data in data table T2, determined as the specified key value, and the row of data where the specified key value is located is determined as the specified row of data.

[0120] S108: Construct a deduplicated second data table based on the specified row data in each group of data.

[0121] In this step, the specified row data in each group of data in the second data table can be retained, and other row data can be deleted to obtain a deduplicated second data table. It should be noted that the "deduplication" mentioned in this step does not mean deleting one of the two exactly identical rows of data, but refers to deduplicating the same group of data in the second data table that satisfies the same equivalent condition, and only retaining the specified row of data that is closest to the non-equivalent condition.

[0122] S110: Modify the connection method between the first data table and the deduplicated second data table to an inner join to obtain the query result.

[0123] Finally, in this step, the connection method between the first data table and the deduplicated second data table can be modified to an inner join to obtain the query result. The query result here should be the same as the query result of the original semi-join query statement received.

[0124] For the above steps, the implementation methods in SQL can be as follows.

[0125] Suppose the received semi-join query statement is:

[0126] SELECT*FROM T1 SEMI JOIN T2 ON T1.C1=T2.C1 AND T1.C2>T2.C2;

[0127] After going through the steps in the data connection method provided in this specification, the above semi-join query statement can be rewritten as the following inner-join statement:

[0128] SELECT T1.*FROM T1 JOIN(SELECT T2.C1, MIN(T2.C2) AS MIN_C2 FROM T2

[0129] GROUP BY T2.C1)V WHERE T1.C1=V.C1 AND T1.C2>V.MIN_C2;

[0130] Among them, T1 represents the first data table, T2 represents the second data table; MIN(T2.C2) represents taking the minimum value of the C2 value in the data table T2; GROUP BY represents grouping; the connection conditions are connected after WHERE. The meaning of the above inner-join statement is to create a data table V according to the content in the parentheses, that is, first group the data in the data table T2 based on the equivalent candidate key C1, and select the minimum value of the non-equivalent candidate key C2 value in each group of data, that is, the specified key value, and construct the specified row data where the specified key value is located in each group into a new data table V, that is, the deduplicated data table T2. Subsequently, according to the original connection conditions, an inner join is performed on the data table T1 and the data table V.

[0131] When adopting the data connection method provided in this specification, it is possible to modify the semi-join query statement in the database system to an inner-join query statement in the case where there is a non-equivalent condition in the connection conditions, increasing the selection space of the query statement execution plan, so that the query optimizer can select a better execution plan.

[0132] Additionally, when there is more than one non-equivalent condition in the connection conditions, the connection method between the first data table and the second data table can be modified to an inner join to obtain a connection result, and the connection result is deduplicated to obtain a query result.

[0133] Among them, when deduplicating the connection result, specifically, for any two rows of data in the connection result, when the two rows of data are the same, one of the two rows of data is deleted until there are no two identical rows of data in the connection result.

[0134] The specific implementation method of the above method in SQL can be shown as follows.

[0135] Suppose the received semi-join query statement is:

[0136] SELECT*FROM T1 SEMI JOIN T2 ON T1.C1=T2.C1 AND T1.C2>T2.C2 AND T1.C3

[0137] <T2.C3;

[0138] Using the above method, the semi-join query statement can be rewritten as the following inner-join query statement:

[0139] SELECT T1.*FROM T1 JOIN T2 ON T1.C1=T2.C1 AND T1.C2>T2.C2 AND T1.C3<

[0140] T2.C3 GROUP BY T1.PK;

[0141] Among them, PK represents the primary key. Usually, in a data table, the primary keys of each row of data are different. Therefore, in the returned query results, the data in the query results can be directly grouped based on the primary key. The values of the primary keys of the data in the same group are the same, that is to say, all the data in the same group are the same data and are returned multiple times. Therefore, directly removing duplicates from each group of data can obtain the expected query results.

[0142] In addition, the above semi-join query statement can also be rewritten as the following inner-join query statement to obtain the same effect:

[0143] SELECT*FROM(SELECT DISTINCT T1.PK, T1.C1, T1.C2 FROM T1 JOIN T2 ON

[0144] T1.C1=T2.C1 AND T1.C2>T2.C2 AND T1.C3<T2.C3)V;

[0145] The effect achieved by this inner-join statement is the same as that achieved by the first rewriting method, and the logic is also to group and remove duplicates based on the primary key of the first data table. This specification will not elaborate here.

[0146] Additionally, when there is no non-equality condition in the connection condition, the equality candidate keys included in the equality connection condition for connection in the second data table can be determined according to the connection condition; for any two rows of data in the second data table, when the values of the same equality candidate key in these two rows of data are the same, one of the two rows of data is deleted until there are no two rows of data with the same value of the same equality candidate key in the second data table; the connection method between the first data table and the deduplicated second data table is modified to an inner join to obtain the query result.

[0147] When there is no non-equality condition in the connection condition, an existing method can be used to rewrite the semi-join query statement, which will not be elaborated in this specification.

[0148] The above is one or more embodiments of the data connection method in this specification. Based on the same idea, this specification also provides a corresponding data connection device, as Figure 2 shown.

[0149] Figure 2 is a schematic diagram of a data connection device provided in this specification, including:

[0150] A receiving module 200, configured to receive a semi-join query statement, obtain the first data table and the second data table connected by the semi-join query statement, and determine the connection condition in the semi-join query statement;

[0151] A grouping module 202, configured to group the second data table according to the connection condition when there is a non-equality condition in the connection condition;

[0152] A determining module 204, configured to determine the non-equality candidate keys included in the non-equality condition for connection in the second data table according to the connection condition;

[0153] A screening module 206, configured to, for each group of data, determine a specified key value according to the non-equality condition and the values of the non-equality candidate keys of each row of data in this group of data, and determine the data in the row where the specified key value is located as the specified row data;

[0154] A construction module 208, configured to construct a deduplicated second data table according to the specified row data in each group of data;

[0155] A first modification module 210, configured to modify the connection method between the first data table and the deduplicated second data table to an inner join to obtain the query result.

[0156] Optionally, the grouping module 202 is specifically configured to, when there is an equality condition in the connection condition, determine that the equality candidate keys included in the equality conditions for joining in the second data table; group the rows of data in the second data table according to the equality candidate keys, where the values of the equality candidate keys for the rows of data in each group are the same.

[0157] Optionally, when there is no equality condition in the connection condition, the grouping module 202 is specifically configured to divide the second data table as a whole into one group.

[0158] Optionally, when the non-equality condition is a greater-than condition, the screening module 206 is specifically configured to select the minimum value of the non-equality candidate key values from the rows of data in this group of data and determine it as the specified key value; when the non-equality condition is a less-than condition, the screening module 206 is specifically configured to select the maximum value of the non-equality candidate key values from the rows of data in this group of data and determine it as the specified key value.

[0159] Optionally, the apparatus further includes a second modification module 212, which is specifically configured to, when there is more than one non-equality condition in the connection condition, modify the connection method between the first data table and the second data table to an inner join, obtain a connection result, and deduplicate the connection result to obtain a query result.

[0160] Optionally, for any two rows of data in the connection result, the second modification module 212 is specifically configured to, when the two rows of data are the same, delete one of the two rows of data until there are no two identical rows of data in the connection result.

[0161] Optionally, the apparatus further includes a third modification module 214, which is specifically configured to, when there is no non-equality condition in the connection condition, determine the equality candidate keys included in the equality conditions for joining in the second data table according to the connection condition; for any two rows of data in the second data table, when the values of the same equality candidate key in the two rows of data are the same, delete one of the two rows of data until there are no two rows of data with the same value of the same equality candidate key in the second data table; modify the connection method between the first data table and the deduplicated second data table to an inner join to obtain a query result.

[0162] This specification also provides a computer-readable storage medium, which stores a computer program, and the computer program can be used to execute the above Figure 1 provided data connection method.

[0163] This specification also provides Figure 3 the schematic structural diagram of an electronic device corresponding to Figure 1 as shown. As Figure 3As described above, at the hardware level, the electronic device includes a processor, an internal bus, a network interface, a memory, and a non-volatile memory. Of course, it may also include other hardware required for other services. The processor reads the corresponding computer program from the non-volatile memory into the memory and then runs it to implement the above Figure 1 data connection method. Of course, in addition to the software implementation method, this specification does not exclude other implementation methods, such as logical devices or a combination of software and hardware, etc. That is to say, the execution subject of the following processing flow is not limited to each logical unit, and can also be hardware or logical devices.

[0164] In the 1990s, it was obvious to distinguish whether an improvement to a technology was a hardware improvement (e.g., improvement to circuit structures such as diodes, transistors, switches, etc.) or a software improvement (improvement to method processes). However, with the development of technology, many method process improvements today can be regarded as direct improvements to hardware circuit structures. Almost all designers obtain the corresponding hardware circuit structure by programming the improved method process into the hardware circuit. Therefore, it cannot be said that an improvement to a method process cannot be implemented with a hardware entity module. For example, a Programmable Logic Device (PLD) (e.g., a Field Programmable Gate Array (FPGA)) is such an integrated circuit whose logical function is determined by the user programming the device. The designer programs by himself to "integrate" a digital system on a piece of PLD, without having to ask the chip manufacturer to design and produce a dedicated integrated circuit chip. Moreover, nowadays, instead of manually fabricating integrated circuit chips, this programming is mostly implemented using "logic compiler" software, which is similar to the software compiler used in program development and writing. The original code before compilation also has to be written in a specific programming language, which is called a Hardware Description Language (HDL). And there is not only one kind of HDL, but many kinds, such as ABEL (Advanced Boolean Expression Language), AHDL (Altera Hardware Description Language), Confluence, CUPL (Cornell University Programming Language), HDCal, JHDL (Java Hardware Description Language), Lava, Lola, MyHDL, PALASM, RHDL (Ruby Hardware Description Language), etc. The most commonly used ones currently are VHDL (Very-High-Speed Integrated Circuit Hardware Description Language) and Verilog. Those skilled in the art should also be aware that by simply performing a little logical programming on the method process with the above-mentioned several hardware description languages and programming it into the integrated circuit, it is easy to obtain the hardware circuit that implements the logical method process.

[0165] The controller can be implemented in any suitable manner. For example, the controller can take the form of, for example, a microprocessor or a processor and a computer-readable medium storing computer-readable program code (such as software or firmware) executable by the (micro)processor, logic gates, switches, an application specific integrated circuit (ASIC), a programmable logic controller, and an embedded microcontroller. Examples of the controller include, but are not limited to, the following microcontrollers: ARC625D, Atmel AT91SAM, Microchip PIC18F26K20, and Silicone Labs C8051F320. The memory controller can also be implemented as part of the control logic of the memory. Those skilled in the art also know that in addition to implementing the controller in the form of pure computer-readable program code, it is entirely possible to logically program the method steps to enable the controller to be implemented in the form of logic gates, switches, application specific integrated circuits, programmable logic controllers, embedded microcontrollers, etc. to achieve the same function. Therefore, such a controller can be considered a hardware component, and the devices included therein for implementing various functions can also be regarded as the structures within the hardware component. Or even, the devices for implementing various functions can be regarded as either software modules for implementing the method or structures within the hardware component.

[0166] The systems, devices, modules, or units illustrated in the above embodiments can be specifically implemented by computer chips or entities, or by products with certain functions. A typical implementation device is a computer. Specifically, the computer can be, for example, a personal computer, a laptop computer, a cellular phone, a camera phone, a smart phone, a personal digital assistant, a media player, a navigation device, an email device, a game console, a tablet computer, a wearable device, or any combination of these devices.

[0167] For the convenience of description, when describing the above devices, they are described separately as various units according to their functions. Of course, when implementing this specification, the functions of each unit can be implemented in the same or multiple software and / or hardware.

[0168] Those skilled in the art should understand that the embodiments of this specification can be provided as a method, a system, or a computer program product. Therefore, this specification can take the form of a complete hardware embodiment, a complete software embodiment, or an embodiment combining software and hardware aspects. Moreover, this specification can take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk memory, CD-ROM, optical memory, etc.) containing computer-usable program code.

[0169] This specification is described with reference to the flowcharts and / or block diagrams of methods, apparatuses (systems), and computer program products according to embodiments of the specification. It should be understood that each flow and / or block in the flowchart and / or block diagram, and combinations of flows and / or blocks in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to the processors of general-purpose computers, special-purpose computers, embedded processors, or other programmable data processing devices to generate a machine, such that the instructions executed by the processors of the computer or other programmable data processing devices produce means for implementing the functions specified in one or more of the flows Figure 1 one or more of the flows and / or blocks Figure 1 one or more of the blocks and for implementing the functions specified in the one or more of the flows and / or blocks

[0170] These computer program instructions can also be stored in a computer-readable memory that can direct a computer or other programmable data processing device to work in a specific manner, such that the instructions stored in the computer-readable memory produce a manufacture including instruction means for implementing the functions specified in one or more of the flows Figure 1 one or more of the flows and / or blocks Figure 1 one or more of the blocks and for implementing the functions specified in the one or more of the flows and / or blocks

[0171] These computer program instructions can also be loaded onto a computer or other programmable data processing device, such that a series of operation steps are executed on the computer or other programmable device to generate a computer-implemented process, and thus the instructions executed on the computer or other programmable device provide steps for implementing the functions specified in one or more of the flows Figure 1 one or more of the flows and / or blocks Figure 1 one or more of the blocks and for implementing the functions specified in the one or more of the flows and / or blocks

[0172] In a typical configuration, a computing device includes one or more processors (CPUs), an input / output interface, a network interface, and a memory.

[0173] The memory may include non-permanent memory in the form of computer-readable media, random access memory (RAM), and / or non-volatile memory such as read-only memory (ROM) or flash memory (flash RAM). The memory is an example of computer-readable media.

[0174] A computer-readable medium includes both permanent and non-permanent, removable and non-removable media and can implement information storage by any method or technology. The information can be computer-readable instructions, data structures, program modules, or other data. Examples of computer storage media include, but are not limited to, phase change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technologies, compact disc read-only memory (CD-ROM), digital versatile disc (DVD) or other optical storage, magnetic cassettes, magnetic tape, magnetic disk storage or other magnetic storage devices, or any other non-transitory medium that can be used to store information that can be accessed by a computing device. As defined herein, a computer-readable medium does not include transitory computer-readable media such as modulated data signals and carrier waves.

[0175] It should also be noted that the term "comprising", "including" or any other variant thereof is intended to cover non-exclusive inclusion, such that a process, method, article or apparatus comprising a series of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such process, method, article or apparatus. Without further limitation, an element defined by the statement "comprising an..." does not exclude the presence of additional identical elements in the process, method, article or apparatus comprising the element.

[0176] Those skilled in the art should understand that the embodiments of this specification can be provided as a method, system or computer program product. Therefore, this specification can take the form of a complete hardware embodiment, a complete software embodiment, or an embodiment combining software and hardware aspects. Moreover, this specification can take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0177] This specification can be described in the general context of computer-executable instructions executed by a computer, such as program modules. Generally, program modules include routines, programs, objects, components, data structures, etc. that perform specific tasks or implement specific abstract data types. This specification can also be practiced in a distributed computing environment where tasks are performed by remote processing devices connected through a communication network. In a distributed computing environment, program modules can be located in local and remote computer storage media including storage devices.

[0178] Each embodiment in this specification is described in a progressive manner. For the same or similar parts among the embodiments, reference can be made to each other. Each embodiment focuses on the differences from other embodiments. In particular, for the system embodiment, since it is basically similar to the method embodiment, the description is relatively simple, and for the relevant parts, reference can be made to the partial description of the method embodiment.

[0179] The above description is only for the embodiments of this specification and is not intended to limit this specification. For those skilled in the art, various modifications and changes can be made to this specification. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of this specification shall be included within the scope of the claims of this specification.

Claims

1. A data connection method, comprising: Receiving a semi-join query statement, obtaining a first data table and a second data table connected by the semi-join query statement, and determining a connection condition in the semi-join query statement; When there is a non-equality condition in the connection condition, grouping the second data table according to the connection condition; According to the connection condition, determining a non-equality candidate key included in the non-equality condition for joining in the second data table; For each group of data, determining a specified key value according to the non-equality condition and the values of the non-equality candidate keys of each row of data in the group, and determining the data in the row where the specified key value is located as the specified row data; Constructing a deduplicated second data table according to the specified row data in each group of data; Modifying the connection method between the first data table and the deduplicated second data table to an inner join to obtain a query result.

2. The method according to claim 1, wherein grouping the second data table according to the connection condition specifically comprises: When there is an equality condition in the connection condition, determining an equality candidate key included in the equality condition for joining in the second data table; Grouping each row of data in the second data table according to the equality candidate key, wherein the values of the equality candidate keys of each row of data in each group are the same.

3. The method according to claim 1, wherein grouping the second data table according to the connection condition specifically comprises: When there is no equality condition in the connection condition, dividing the second data table into one group as a whole.

4. The method according to claim 1, wherein determining a specified key value according to the non-equality condition and the values of the non-equality candidate keys of each row of data in the group specifically comprises: When the non-equality condition is a greater-than condition, selecting the minimum value of the values of the non-equality candidate keys in each row of data in the group as the specified key value; When the non-equality condition is a less-than condition, selecting the maximum value of the values of the non-equality candidate keys in each row of data in the group as the specified key value.

5. The method according to claim 1, the method further comprising: When there is more than one non-equality condition in the connection condition, modifying the connection method between the first data table and the second data table to an inner join to obtain a connection result, and deduplicating the connection result to obtain a query result.

6. The method according to claim 5, wherein deduplicating the connection result specifically comprises: For any two rows of data in the connection result, when the two rows of data are the same, deleting one of the two rows of data until there are no two identical rows of data in the connection result.

7. The method according to claim 1, the method further comprising: When there is no non-equality condition in the connection condition, determining an equality candidate key included in the equality condition for joining in the second data table according to the connection condition; For any two rows of data in the second data table, when the values of the same equality candidate key in the two rows of data are the same, deleting one of the two rows of data until there are no two rows of data with the same value of the same equality candidate key in the second data table; Modify the connection method between the first data table and the deduplicated second data table to an inner join to obtain a query result.

8. A data connection device, comprising: a receiving module, configured to receive a semi-join query statement, obtain a first data table and a second data table connected by the semi-join query statement, and determine a connection condition in the semi-join query statement; a grouping module, configured to group the second data table according to the connection condition when there is a non-equivalent condition in the connection condition; a determining module, configured to determine a non-equivalent candidate key included in the non-equivalent condition for connection in the second data table according to the connection condition; a filtering module, configured to, for each group of data, determine a specified key value according to the non-equivalent condition and the values of the non-equivalent candidate keys of each row of data in the group, and determine the data of the row where the specified key value is located as specified row data; a constructing module, configured to construct a deduplicated second data table according to the specified row data in each group of data; a first modification module, configured to modify the connection method between the first data table and the deduplicated second data table to an inner join to obtain a query result.

9. The apparatus according to claim 8, wherein the grouping module is specifically configured to, when there is an equality condition in the connection condition, determine that the equality candidate key is included in the equality conditions for connection in the second data table; and group the rows of data in the second data table according to the equality candidate key, where The values of the equivalent candidate keys of each row of data in each group are the same.

10. The device according to claim 8, wherein the grouping module is specifically configured to, when there is no equivalent condition in the connection condition, divide the second data table into a single group.

11. The device according to claim 8, wherein the filtering module is specifically configured to, when the non-equivalent condition is a greater-than condition, select the minimum value of the values of the non-equivalent candidate keys in each row of data in the group as the specified key value; when the non-equivalent condition is a less-than condition, select the maximum value of the values of the non-equivalent candidate keys in each row of data in the group as the specified key value.

12. The device according to claim 8, wherein the device further comprises a second modification module, specifically configured to, when there is more than one non-equivalent condition in the connection condition, modify the connection method between the first data table and the second data table to an inner join to obtain a connection result, and deduplicate the connection result to obtain a query result.

13. The device according to claim 12, wherein the second modification module is specifically configured to, for any two rows of data in the connection result, when the two rows of data are the same, delete one of the two rows of data until there are no two identical rows of data in the connection result.

14. The device according to claim 8, wherein the device further comprises a third modification module, specifically configured to, when there is no non-equivalent condition in the connection condition, determine an equivalent candidate key included in the equivalent condition for connection in the second data table according to the connection condition; for any two rows of data in the second data table, when the values of the same equivalent candidate key in the two rows of data are the same, delete one of the two rows of data until there are no two rows of data in the second data table with the same value of the same equivalent candidate key; modify the connection method between the first data table and the deduplicated second data table to an inner join to obtain a query result.

15. A computer-readable storage medium storing a computer program which, when executed by a processor, implements the method according to any one of claims 1 to 7 above.

16. An electronic device comprising a memory, a processor, and a computer program stored on the memory and executable on the processor, wherein the method according to any one of claims 1 to 7 above is implemented when the processor executes the program.

Images