CN106570022B

CN106570022B - Cross-data-source query method, device and system

Info

Publication number: CN106570022B
Application number: CN201510651622.5A
Authority: CN
Inventors: 郝佳
Original assignee: Cainiao Smart Logistics Holding Ltd
Current assignee: Cainiao Smart Logistics Holding Ltd
Priority date: 2015-10-10
Filing date: 2015-10-10
Publication date: 2020-06-23
Anticipated expiration: 2035-10-10
Also published as: CN106570022A

Abstract

The application provides a cross-data source query method, a device and a system, wherein the method comprises the following steps: acquiring a mapping relation between common fields of two data tables related to a target search statement and grouping fields in the target search statement by analyzing the target search statement; determining a second query result corresponding to the second data table in the intermediate query results which are obtained by querying the second data table by using the second query statement and the second condition restriction statement and grouped according to the virtual grouping field; determining a first query result corresponding to the first data table in query of the first data table by using the first query statement and the additional condition restriction statement and obtaining query results grouped by grouping fields; and integrating the first query result and the second query result according to the logical relation in the target search statement to obtain a final query result. According to the method and the device, the two data tables are directly and separately queried, so that the integration process of the two data tables is omitted, and the efficiency and the accuracy of data query are improved.

Description

Cross-data-source query method, device and system

Technical Field

The present application relates to the field of database technologies, and in particular, to a cross-data-source query method, apparatus, and system.

Background

At present, in the software development process, data query is a very important link; accurate data query at a time can provide powerful basic data for subsequent software development. Current data queries are primarily database-based queries. At present, the database-based data query process generally refers to querying the required data in the data table of the database through a query statement.

As the amount of data in the database increases, the required data may be distributed in two or more data tables; this results in the data query process needing to combine two or more tables in the database to perform the query to obtain the desired data. The process of querying in two or more data tables is referred to as cross-data source querying.

Since data queries for the same data table can be directly queried through the query statement, there is no way for cross-data source queries to be directly queried through the query statement. Thus, the process for cross-data source queries is generally: and integrating the data of the two or more data tables by utilizing the common fields of the two or more data tables so as to integrate the two or more data tables into the same data table, and then inquiring the data in the same data table.

Because the error rate in the data integration process is very high, and a large amount of time and resources are consumed for data integration, the query efficiency and accuracy in the cross-data source query process are low, and the waste of resources is serious.

Disclosure of Invention

The application provides a cross-data source query method, device and system, which can complete cross-data source query on the premise of not integrating data, thereby improving query efficiency and accuracy and reducing resource consumption.

In order to achieve the above object, the present application provides the following technical means:

a cross-data source query method, comprising:

acquiring a mapping relation between common fields of two data tables related to a target search statement and grouped fields in the target search statement by analyzing the target search statement; wherein, the data table containing the grouping field in the two data tables is a first data table, the data table not containing the grouping field is a second data table, and the mapping relation is determined in a query result obtained by querying the first data table according to a first condition restriction statement;

determining a second query result corresponding to a second data table in the intermediate query results which are obtained by querying the second data table by using a second query statement and a second condition restriction statement and grouped by a virtual grouping field; wherein the virtual grouping field is a virtual field which is created in the second data table by using the mapping relation and is equivalent to the grouping field in the first data table;

determining a first query result corresponding to a first data table in query of the first data table by using a first query statement and an additional condition restriction statement and obtaining query results grouped according to the grouping field; wherein the additional condition restriction statement is that the numerical value of the grouping field in the first data table is equal to the numerical value of the virtual grouping field in the second query result;

and integrating the first query result and the second query result according to the logical relation in the target search statement to obtain a final query result.

Preferably, the first conditional restriction statement is a conditional restriction statement related to the first data table in the target search statement.

Preferably, the second query statement and the second conditional restriction statement are query statements and conditional restriction statements related to the second data table in the target search statement.

Preferably, the first query statement comprises a query statement related to the first data table in the target search statement.

Preferably, the obtaining, by analyzing the target search statement, a mapping relationship between a common field of the two data tables related to the target search statement and a grouping field in the target search statement includes:

querying, in conjunction with the first conditional restriction statement, a first mapping dataset corresponding to both the group field and the common field in the first data table; wherein the first mapping data set comprises a packet data set corresponding to the packet field and a first data set corresponding to the common field, and the packet data in the packet data set is in one-to-one correspondence with the first data in the first data set; wherein the packet data set comprises a plurality of packet data, and the first data set comprises a plurality of first data;

in the first mapping data set, determining a one-to-one correspondence between packet data in the packet data set and first data in the first data set as the mapping relationship between the packet field and the common field.

Preferably, the determining, in the first mapping data set, a one-to-one correspondence between packet data in the packet data set and first data in the first data set as the mapping relationship between the packet field and the common field includes:

determining a part of data sets selected from the first mapping data set according to the sequence from top to bottom as a second mapping data set;

and in the second mapping data set, determining the one-to-one correspondence between the packet data in the packet data set and the first data in the first data set as the mapping relationship between the packet field and the common field.

Preferably, the determining a second query result corresponding to the second data table in querying the second data table by using the second query statement and the second conditional restriction statement and obtaining intermediate query results grouped by the virtual grouping field includes:

dynamically creating the virtual packet field in the second data table using the mapping relationship; wherein the virtual packet field corresponds to a virtual packet data set; wherein the determination process of each virtual packet data in the virtual packet data set comprises: determining a piece of second data in a second data set of the second data table corresponding to the common field, and determining packet data corresponding to the first data in the mapping relationship as virtual packet data corresponding to the second data under the condition that the second data is equal to one piece of first data in the mapping relationship;

and inquiring the second data table according to the second inquiry statement and the second condition limiting statement, and outputting an intermediate inquiry result grouped according to the virtual grouping field.

Preferably, the determining the second query result corresponding to the second data table includes:

determining the intermediate query result as the second query result if the target search statement does not contain a number limit statement;

under the condition that the target search statement comprises a quantity limiting statement, judging whether the data quantity of the intermediate query result is smaller than a preset quantity or not; wherein the preset number is extracted from the conditional restriction statement;

and if the data quantity of the intermediate query result is not less than the preset quantity, determining the preset quantity of data extracted from the intermediate query result as the second query result.

Preferably, the method further comprises the following steps:

if the data quantity of the intermediate query results is less than the preset quantity, repeatedly executing the following steps until the obtained data quantity of all the intermediate query results is not less than the preset quantity:

determining the partial data set reselected in the first mapping data set as the second mapping data set; determining, in the second mapping dataset, the mapping relationship between the packet field and the common field; and querying the second data table by using a second query statement and a second condition limiting statement, and obtaining intermediate query results grouped according to the virtual grouping field.

Preferably, the integrating the first query result and the second query result according to the logical relationship in the target search statement to obtain a final query result includes:

if the sum of the query results of the first query result and the second query result is not accordant with the target query result in the target search statement, screening at least two intermediate results which are not accordant with the target query result from the first query result and the second query result;

calculating the operation result of the at least two intermediate results according to the logic operators of the at least two intermediate results;

and determining other query results which accord with the target query result in the first query result and the second query result and the operation result as the final query result.

A cross-data source querying device comprising:

the system comprises an acquisition unit, a search unit and a processing unit, wherein the acquisition unit is used for acquiring the mapping relation between the common fields of two data tables related to a target search statement and the grouping fields in the target search statement by analyzing the target search statement; wherein, the data table containing the grouping field in the two data tables is a first data table, the data table not containing the grouping field is a second data table, and the mapping relation is determined in a query result obtained by querying the first data table according to a first condition restriction statement;

a first processing unit, configured to determine, in querying the second data table by using a second query statement and a second conditional restriction statement and obtaining intermediate query results grouped by a virtual grouping field, a second query result corresponding to the second data table; wherein the virtual grouping field is a virtual field which is created in the second data table by using the mapping relation and is equivalent to the grouping field in the first data table;

a second processing unit, configured to determine, in querying the first data table by using a first query statement and an additional condition restriction statement and obtaining query results grouped by the grouping field, a first query result corresponding to the first data table; wherein the additional condition restriction statement is that the numerical value of the grouping field in the first data table is equal to the numerical value of the virtual grouping field in the second query result;

and the integration unit is used for integrating the first query result and the second query result according to the logical relationship in the target search statement to obtain a final query result.

Preferably, the acquiring unit includes:

a first query unit, configured to query, in combination with the first conditional restriction statement, a first mapping data set corresponding to both the grouping field and the common field in the first data table; wherein the first mapping data set comprises a packet data set corresponding to the packet field and a first data set corresponding to the common field, and the packet data in the packet data set is in one-to-one correspondence with the first data in the first data set; wherein the packet data set comprises a plurality of packet data, and the first data set comprises a plurality of first data;

a first mapping relationship determining unit, configured to determine, in the first mapping data set, a one-to-one correspondence between packet data in the packet data set and first data in the first data set as the mapping relationship between the packet field and the common field.

Preferably, the first mapping relationship determining unit includes:

a mapping data set determining unit, configured to determine a part of data sets selected in the first mapping data set in an order from top to bottom as a second mapping data set;

a second mapping relationship determining unit, configured to determine, in the second mapping data set, a one-to-one correspondence between packet data in the packet data set and first data in the first data set as the mapping relationship between the packet field and the common field.

Preferably, the first processing unit includes:

the second query unit is used for querying the second data table by using a second query statement and a second condition restriction statement and obtaining intermediate query results grouped according to the virtual grouping field;

and the first determining unit is used for determining a second query result corresponding to the second data table in the intermediate query results.

Preferably, the second query unit includes:

a creating unit, configured to dynamically create the virtual grouping field in the second data table by using the mapping relationship; wherein the virtual packet field corresponds to a virtual packet data set; wherein the determination process of each virtual packet data in the virtual packet data set comprises: determining a piece of second data in a second data set of the second data table corresponding to the common field, and determining grouped data corresponding to the first data in the mapping relationship as virtual grouped data corresponding to the second data under the condition that the second data is equal to one piece of first data in the mapping relationship;

and the output unit is used for inquiring the second data table according to the second inquiry statement and the second condition limiting statement and outputting the intermediate inquiry result grouped according to the virtual grouping field.

Preferably, the first determining unit includes:

a second determining unit, configured to determine the intermediate query result as the second query result if the target search statement does not include a number limit statement;

the judging unit is used for judging whether the data quantity of the intermediate query result is less than a preset quantity or not under the condition that the target search statement comprises a quantity limiting statement; wherein the preset number is extracted from the conditional restriction statement;

a third determining unit, configured to determine, if the number of data of the intermediate query result is not less than the preset number, the preset number of data extracted from the intermediate query result as the second query result.

Preferably, the method further comprises the following steps:

the mapping data set determining unit is further configured to determine, if the number of data of the intermediate query result is smaller than the preset number, a part of data sets reselected from the first mapping data set as the second mapping data set.

Preferably, the integration unit comprises:

a screening unit, configured to screen, if a sum of query results of the first query result and the second query result does not match a target query result in the target search statement, at least two intermediate results that do not match the target query result from the first query result and the second query result;

the calculation unit is used for calculating the operation result of the at least two intermediate results according to the logical operators of the at least two intermediate results;

and a final result determining unit, configured to determine, as the final query result, another query result that matches the target query result in the first query result and the second query result, and the operation result.

A cross-data source query system, comprising:

the system comprises a processor and an input device, wherein the processor stores a database, and the input device is connected with the processor;

the input equipment is used for acquiring a target search statement and sending the target search statement to the processor;

the processor is used for carrying out data query according to the target search statement; the method specifically comprises the following steps: acquiring a mapping relation between common fields of two data tables related to a target search statement and grouped fields in the target search statement by analyzing the target search statement; wherein, the data table containing the grouping field in the two data tables is a first data table, the data table not containing the grouping field is a second data table, and the mapping relation is determined in a query result obtained by querying the first data table according to a first condition restriction statement; determining a second query result corresponding to a second data table in the intermediate query results which are obtained by querying the second data table by using a second query statement and a second condition restriction statement and grouped by a virtual grouping field; wherein the virtual grouping field is a virtual field which is created in the second data table by using the mapping relation and is equivalent to the grouping field in the first data table; determining a first query result corresponding to a first data table in query of the first data table by using a first query statement and an additional condition restriction statement and obtaining query results grouped according to the grouping field; wherein the additional condition restriction statement is that the numerical value of the grouping field in the first data table is equal to the numerical value of the virtual grouping field in the second query result; and integrating the first query result and the second query result according to the logical relation in the target search statement to obtain a final query result.

A cross-data source query system, comprising:

the system comprises a processor, an input device connected with the processor, and a memory connected with the processor and used for storing a database;

the processor is used for calling the database in the memory and inquiring data according to the target search statement; the method specifically comprises the following steps: the system comprises a database, a target search statement and a database, wherein the database is used for acquiring a mapping relation between common fields of two data tables related to the target search statement and grouping fields in the target search statement by analyzing the target search statement; wherein, the data table containing the grouping field in the two data tables is a first data table, the data table not containing the grouping field is a second data table, and the mapping relation is determined in a query result obtained by querying the first data table according to a first condition restriction statement; determining a second query result corresponding to a second data table in the intermediate query results which are obtained by querying the second data table by using a second query statement and a second condition restriction statement and grouped by a virtual grouping field; wherein the virtual grouping field is a virtual field which is created in the second data table by using the mapping relation and is equivalent to the grouping field in the first data table; determining a first query result corresponding to a first data table in query of the first data table by using a first query statement and an additional condition restriction statement and obtaining query results grouped according to the grouping field; wherein the additional condition restriction statement is that the numerical value of the grouping field in the first data table is equal to the numerical value of the virtual grouping field in the second query result; and integrating the first query result and the second query result according to the logical relation in the target search statement to obtain a final query result.

From the above, it can be seen that the present application has the following beneficial effects:

the method and the device perform a separate query process on the two data tables. In order to realize the purpose of separate query, in the second data table which does not contain the grouping field, a virtual grouping field which is equivalent to the grouping field is created according to the first conditional restriction statement, so that the query process in the second data table is smoothly realized. And querying the first data table by combining the second query result, wherein the second query result comprises a second condition limiting statement, so that the query process of the first data table can be smoothly completed.

Different from the prior art, the method and the device do not integrate the two data tables in the target search statement, but directly query the two data tables separately, so that the integration process of the two data tables is omitted, and the efficiency and the accuracy of data query are improved.

Drawings

In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings used in the description of the embodiments or the prior art will be briefly described below, it is obvious that the drawings in the following description are only some embodiments of the present application, and for those skilled in the art, other drawings can be obtained according to the drawings without creative efforts.

Fig. 1 is a schematic structural diagram of a cross-data-source query system according to a first embodiment of the present disclosure;

fig. 2 is a schematic structural diagram of a cross-data-source query system according to a second embodiment of the present disclosure;

FIG. 3 is a flowchart of a cross-data source query method disclosed in an embodiment of the present application;

FIG. 4 is a flowchart of another cross-data-source query method disclosed in an embodiment of the present application;

FIG. 5 is a flowchart of another cross-data-source query method disclosed in an embodiment of the present application;

FIG. 6 is a flowchart of yet another cross-data source query method disclosed in an embodiment of the present application;

FIG. 7 is a flowchart of yet another cross-data source query method disclosed in an embodiment of the present application;

FIG. 8 is a flowchart of yet another cross-data source query method disclosed in an embodiment of the present application;

FIG. 9 is a syntax tree diagram illustrating a cross-data-source query method according to an embodiment of the present application;

FIG. 10 is a schematic structural diagram of a cross-data-source query device according to an embodiment of the present disclosure;

fig. 11 is a schematic diagram of an obtaining unit in a cross-data-source querying device according to an embodiment of the present application;

FIG. 12 is a schematic diagram of a first processing unit in a cross-data-source querying device according to an embodiment of the disclosure;

FIG. 13 is a diagram illustrating a second query unit in a cross-data source query device according to an embodiment of the disclosure;

FIG. 14 is a schematic diagram of a first determining unit in a cross-data-source querying device according to an embodiment of the disclosure;

fig. 15 is a schematic diagram of an integration unit in a cross-data-source query device according to an embodiment of the disclosure.

Detailed Description

Before introducing the details of the present application, a target search statement is first introduced so that the reader may clearly understand the following details.

In general, the target search statement may include a query statement, a conditional restriction statement, a grouping statement, and a quantity restriction statement, and of course, may include other statements, which are not listed here. The query statement comprises query keywords and query fields corresponding to the query keywords, the condition limiting statement comprises condition limiting keywords and condition fields corresponding to the condition limiting keywords, the grouping statement comprises grouping keywords and grouping fields corresponding to the grouping keywords, the quantity limiting statement comprises quantity limiting keywords and constants of the quantity limiting keywords, and the constants are used for representing the quantity of data which are limited to be output.

For example, suppose there are two data tables, "roster" and "achievement table," the roster "including the" school number, "name," "class," "department of school," and the data set of each field; the score table comprises a study number, a score and a data set of each field; the common field of both tables is the "school number".

On the basis of the data table, listing a target search statement instance:

select school number, name, class

from roster and achievement list

The scholar roster of flowers, school number and flower roster, school department, first school department and score table, score > 60

group by roster class

limit 0,10

In the query statement, "select" is the query keyword "school number, name, class" is 3 query fields.

The "from flower roster and achievement table, where the school number is the achievement table, the school number is the and flower roster, the department is the first department and achievement table, the score > 60" is the conditional restriction sentence, wherein, "from" and "where" are 2 conditional restriction keywords, "the" flower roster and achievement table "and" flower roster, the school number is the achievement table, the school number is the first department and achievement table, the score > 60 "is 2 conditional restriction sentences.

The "group by roster and class" are grouped sentences, wherein the "group by" is a grouped keyword, and the "roster and class" is a grouped field.

"limit 0, 10" is a quantity limit statement, where "limit" is a quantity limit keyword and "0, 10" is a constant.

The above example of the target search statement is to be expressed in terms of: establishing association of two data tables in a roster and a score table through a school number field, searching the school number, the name and the class of the first school department with the score more than 60 classmates in all classmates, grouping according to the class, and outputting the school number, the name and the class of the first 10 names from top to bottom.

The above is a simple introduction to the target search statement so that the reader can more clearly understand the structure of the target search statement.

The technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the drawings in the embodiments of the present application, and it is obvious that the described embodiments are only a part of the embodiments of the present application, and not all of the embodiments. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present application.

Before describing particular embodiments of the present application, a cross-data source query system is first described to clearly illustrate the application scenarios of the present application.

As shown in fig. 1, a first embodiment of a cross-data-source query system provided by the present application includes:

a processor 11 storing a database, and an input device 12 connected to the processor 11.

The input device 12 is configured to obtain a target search statement, and send the target search statement to the processor 11; the processor 11 is used for data query according to the target search statement.

In the system shown in fig. 1, the input device 12 may acquire a target search sentence input by a user, or acquire a target search sentence transmitted from another device, and send the target search sentence to the processor 11. Since the processor 11 has a database, the database inside itself can be called for data query.

As shown in fig. 2, a second embodiment of a cross-data-source query system provided by the present application includes:

a processor 21, an input device 22 connected to said processor 21, and a memory 23 connected to said processor 21 for storing a database.

The input device 22 is configured to obtain a target search statement and send the target search statement to the processor; the processor 21 is configured to invoke the database in the memory 23 and perform data query according to the target search statement.

In the embodiment shown in fig. 2, the input device 22 may acquire the target search sentence input by the user, or acquire the target search sentence transmitted by another device, and send the target search sentence to the processor 21. Since there is no database on the processor 21, the processor 21 may remotely call a database on the memory 23 to implement the data query process.

On the basis of the cross-data-source query system shown in fig. 1 and 2, the data query process can be realized mainly depending on a processor. The following describes in detail the process of the processor performing data query according to the target search statement.

The purpose of the method is to perform data query in two or more data tables of the database, and each data table has an incidence relation, namely, cross-data source query is realized. The association relationship of each data table can be shown by a common field between the two data tables.

For example, the database has three data tables, data table 1, data table 2 and data table 3; also, data table 1 and data table 2 have one common field a, and data table 2 and data table 3 have one common field B. Then data table 1 may be associated with data table 2 through common field a, data table 2 may be associated with data table 3 through common field B, and data table 1 may be associated with data table 3 indirectly through data table 2, so data table 1, data table 2, and data table 3 may be associated with each other through common field a and common field B.

The method can carry out cross-data source query on two or more data tables, and the general process of carrying out data query comprises the steps of firstly carrying out data query on two data tables with common fields, carrying out data query on the obtained query result and another data table with common fields, and the like until all the data tables are completely queried.

For example, continuing the above example, the query process of the present application may first perform a data query on data table 1 and data table 2 having a common field a, thereby obtaining query result 1. It is to be understood that since there is a common field B in data table 2, there is also a common field B in query result 1. Then, data query is performed on the query result 1 and the data table 3 with the common field B, so as to obtain a final query result.

From the above, the data query is performed on two data tables with common fields in one query process; for example, data query is performed on data table 1 and data table 2, or data query is performed on query result 1 (the query result is also a data table) and data table 3. Since each query process is only different from the data table, and the specific steps executed are consistent, only one query process is described in detail later in the application, and the rest data query processes are similar to the data query processes provided by the application, and thus are not described again.

The application is applied to a processor, and the processor can receive the target search statement input by a user or sent by other processors; then, the database of the memory of the user is called, or the database of other memories is called remotely, so that the aim of inquiring the database according to the target search statement is fulfilled.

The present application is directed to cross-data source queries where the target search statement may involve two data tables. Different from the prior art, the method and the device do not integrate the two data tables, but directly query the two data tables separately, so that the integration process of the two data tables is omitted, and the efficiency and the accuracy of data query are improved.

The two data tables involved in the target search statement are respectively a first data table and a second data table, and the data table containing the grouping field is the first data table, and the data table not containing the grouping field is the second data table. In order to implement a query of the first data table and the second data table, the target search statement necessarily contains a first search statement for the first data table and a second search statement for the second data table.

For the purpose of implementing separate query on the two data tables, the first search statement and the second search statement need to be extracted from the target search statement respectively. The first search statement comprises a first query statement and a first conditional restriction statement; the second search statement includes a second query statement and a second conditional restriction statement. If the first data table is directly queried by the first search statement and the second data table is directly queried by the second search statement, the query of the two data tables cannot be completed smoothly. The reason is as follows: there is no second conditional restriction statement for the second data table in the first search statement, and there is no first conditional restriction statement for the first data table and no packet field for the first data table in the second data table.

For example, continuing with the first example of the specification, a data query can be made in "roster" according to a first search statement (query "first department", "school number", "name" and "class", and group by group field "class"), but there is no way to do the query in conjunction with the conditional restriction statements in "achievement list" (i.e., a query with "score > 60" cannot be completed). In the "achievement list", a query can be made in the second search sentence (query "school number" and complete a query with "score > 60"), but the first condition restriction sentence and the grouping sentence in the "roster" cannot be completed (data of the "first school" cannot be obtained by the query and are grouped by the grouping field "class").

For the purpose of implementing separate queries on two data tables, the skilled person envisages: since the limitation of the second data table query process is that there is no first condition restriction statement in the second search statement and no packet field in the second data table, in order to eliminate the limitation, a virtual packet field may be created in the second data table in combination with the first condition restriction statement, so that the query process of the first condition restriction statement and the packet according to the virtual packet field may be completed in the second data table, thereby smoothly completing the query of the second data table and obtaining the second query result.

The meaning of the virtual packet field is that the real second data table does not contain the packet field, and the application creates a field equivalent to the packet field, which acts in accordance with the packet field and is therefore referred to as the virtual packet field. Since the first data table and the second data table have common fields, and the mapping relationship between the grouping field and the common field is contained in the first data table, the virtual grouping field can be created in the second data table through the cascading relationship of the common fields in the first data table and the second data table.

The limitation of the query process of the first data table is that: the first search statement is free of the second conditional restriction statement. Because the second query result already meets the second conditional restriction statement, the query can be performed only on the data meeting the second query result in the first data table in combination with the first search statement; although the first search statement does not have the second condition limiting statement, the second query result already meets the second condition limiting statement, so that the second condition limiting statement is added in the first search statement in a phase-changing manner by combining the second query result, so that the query on the first data table is successfully completed, and the first query result is obtained.

Therefore, the main technical idea of the present application is: and determining a virtual grouping field in the second data table by combining the first conditional restriction statement of the first search statement, and performing data query on the second data table by using the second search statement and the virtual grouping field to obtain a second query result. And then, performing data query on the first data table by using the first query statement of the first search statement and combining the second query result, thereby obtaining a first query result. And performing data integration on the first query result and the second query result to obtain a final query result.

Specific implementations of the present application are described in detail below. As shown in fig. 3, the present application provides a cross-data source query method, which specifically includes the following steps S301 to S304:

step S301: acquiring a mapping relation between common fields of two data tables related to a target search statement and grouped fields in the target search statement by analyzing the target search statement; wherein, a data table containing the grouping field in the two data tables is a first data table, a data table not containing the grouping field is a second data table, the mapping relation is determined in a query result obtained by querying the first data table according to a first conditional restriction statement, and the first conditional restriction statement is a conditional restriction statement related to the first data table in the target search statement.

After obtaining the target search statement, the processor firstly analyzes the target search statement to determine two data tables related to the target search statement, and further determines common fields of the two data tables through fields contained in the two data tables. And analyzing the target search statement to obtain a grouping statement, wherein the grouping statement comprises a grouping keyword and a grouping field, a data table comprising the grouping field is determined as a first data table, and the other data table is called as a second data table.

After the parsing of the target search statement is completed, a process of creating a virtual grouping field in the second data table in conjunction with the first conditional restriction statement should be performed. The process of creating the virtual grouping field needs to be based on the mapping relation between the grouping field and the common field in the first data table; for this purpose, first, in conjunction with a first conditional restriction statement, a mapping between packet fields and common fields is determined in a first data table.

As shown in fig. 4, the process of determining the mapping relationship may specifically include the following steps:

step S401: querying, in conjunction with the first conditional restriction statement, a first mapping dataset corresponding to both the group field and the common field in the first data table; wherein the first mapping data set comprises a packet data set corresponding to the packet field and a first data set corresponding to the common field, and the packet data in the packet data set is in one-to-one correspondence with the first data in the first data set. Wherein the packet data set comprises a plurality of packet data and the first data set comprises a plurality of first data.

Because the second data table does not have the first conditional restriction statement, when the mapping relation for creating the virtual grouping field is constructed, the first data table is inquired by combining the first conditional restriction statement in the first search statement, so that the obtained first mapping data set meets the first conditional restriction statement, and a foundation is laid for the subsequent data inquiry in the second data table.

The specific process can be as follows: after the target search statement is analyzed, the conditional restriction statement related to the first data table is extracted from the target search statement and determined as a first conditional restriction statement. For example, the department of study, which is the first department of study, is a conditional restriction statement related to a first data table (roster).

And querying a first mapping data set corresponding to the grouping field and the common field in the first data table in combination with the first conditional restriction statement. The first mapping data set includes a packet data set corresponding to the packet field and a first data set corresponding to the common field, and the packet data in the packet data set has a one-to-one correspondence with the first data in the first data set.

For example, taking the first data table as a roster, see table 1 for a specific example of roster:

TABLE 1

The department of academic, the first department, queries the first data table according to the first conditional restriction statement, and thus a first mapping data set containing common fields and grouping fields is obtained. Referring to table 2, after the present step is performed, data of which the department field is not the first department may be deleted, and only data of which the department is the first department may be left.

TABLE 2

Step S402: in the first mapping data set, determining a one-to-one correspondence between packet data in the packet data set and first data in the first data set as the mapping relationship between the packet field and the common field.

In table 2, it can be seen that packet data sets (one shift, two shifts … …) corresponding to packet fields and first data sets (001, 002, 003 … …) corresponding to common fields are included in the first mapping data set, and the packet data of the packet data sets and the first data of the first data sets have a one-to-one correspondence relationship; and determining the one-to-one correspondence as a mapping relation.

For example, the school number 001 corresponds to one shift, the school number 003 corresponds to two shifts, and the school number 005 corresponds to three shifts; the correspondence relationship between the packet data and the first data may be a mapping relationship.

After the mapping relationship between the grouping field and the common field is determined in the first data table, a virtual grouping field can be created in the second data table according to the mapping relationship, and data query can be carried out on the second data table by utilizing the virtual grouping field of the second search statement set.

Then, returning to fig. 3, the flow proceeds to step S302: determining a second query result corresponding to a second data table in the intermediate query results which are obtained by querying the second data table by using a second query statement and a second condition restriction statement and grouped by a virtual grouping field; wherein the virtual grouping field is a virtual field which is created in the second data table by using the mapping relation and is equivalent to the grouping field in the first data table; the second query statement and the second conditional restriction statement are the query statement and the conditional restriction statement related to the second data table in the target search statement.

The second data table and the first data table have a common field, and the data set corresponding to the common field in the second data table is a second data set; the second data set includes a number of second data. And creating a virtual grouping field in the second data table through the second data set and the mapping relation.

The process of constructing the virtual packet field in the second data table, as shown in fig. 5, specifically includes the following steps:

step S501: in a second data set corresponding to the second data table and the common field, a second data is determined.

Step S502: judging whether the second data exists in the mapping relation; judging whether the second data is equal to one first data in the mapping relation or not; if yes, the process proceeds to step S503, otherwise, the process proceeds to step S504.

The first mapping data set corresponds to the common field as a first data set, and the second mapping data set corresponds to the common field as a second data set in the second data table. The first data table and the second data table have an association relationship only if the second data in the second data set is equal to the first data in the first data set. And if the second data is not equal to the first data, the first data table and the second data table have no association relationship. The virtual packet field can be created only according to the association relationship of the first data table and the second data table.

Step S503: and determining the grouped data corresponding to the first data in the mapping relation as the virtual grouped data corresponding to the second data.

When the second data is equal to the first data, it is described that the mapping relation corresponding to the second data is stored in the mapping relation, and therefore packet data corresponding to the first data (or the second data equal to the first data) is queried in the mapping relation, and the obtained packet data is virtual packet data corresponding to the second data.

For example, taking the second data table as the achievement table, see table 3 as an example of the achievement table:

TABLE 3

Assuming that the selected second data is 001, by comparing with the mapping relationship shown in table 2, the second data 001 is found to be equal to the first data 001 in the mapping relationship, and therefore, the "one shift" corresponding to the first data 001 in the mapping relationship may be determined as the virtual packet data corresponding to the second data 001. If the second data is 006, the first data consistent with the second data cannot be found in table 2, which means that the second data cannot obtain the corresponding virtual packet data in the mapping relationship.

The virtual grouping field and the virtual grouping data set corresponding to the virtual grouping field can be obtained through the step. For example, continuing the above example, to add a second data table of virtual packet fields, see table 4:

TABLE 4

Step S504: and judging whether the second data table has unmatched second data or not, if so, entering the step S501, and if not, ending the program.

The process shown in fig. 5 is referred to as a process of creating a virtual packet field, and it is understood that the above process may be performed before querying the second data table, or may be dynamically generated during the querying process of the second data table, and the creation time of the virtual packet field is not limited herein.

After the virtual packet field is created in the second data table, a data query may be made to the second data table. For this reason, the second query statement and the second conditional restriction statement corresponding to the second data table (the second query statement and the second conditional restriction statement are the second search statement) may be extracted from the target search statement, and the second data table may be queried. And then, grouping according to the virtual grouping field, outputting an intermediate query result, and determining a second query result corresponding to the second data table in the intermediate query result.

For example, after table 3 is subjected to the query of the second search statement, data having a score less than 60 and data having a grouping field (class) of null are deleted, and the obtained results are grouped by class, and the obtained second query result may be as shown in table 5.

TABLE 5

The above is the query process for the second data table, and the following describes the query process for the first data table by using the first query statement in combination with the second query result.

Then, returning to fig. 3, the flow proceeds to step S303: determining a first query result corresponding to a first data table in query of the first data table by using a first query statement and an additional condition restriction statement and obtaining query results grouped according to the grouping field; wherein the additional condition restriction statement is that the numerical value of the grouping field in the first data table is equal to the numerical value of the virtual grouping field in the second query result; the first query statement comprises a query statement in the target search statement that is related to the first data table.

The second query result is a query result that has satisfied the first conditional restriction statement, the second query statement, and the second conditional restriction statement. Therefore, the second query result is essentially already the final result set. Therefore, when the first data table is queried, the first data table can be queried only by using the first query statement and combining the second query result.

In practical applications, additional conditional restriction statements may be utilized for the purpose of incorporating the second query result. Because the second query result contains the common field and the first data table also contains the common field, when the value of the common field of one piece of data in the first data table is equal to the value of the common field of the second query result, the piece of data can be considered to satisfy all the search statements, and therefore the content of the query field can be output.

For example, taking the first data table as the roster, the first query statement is "select school number, name, class". Combining the first data table (table 1) and the second query result (table 5), and searching the data of the common field (academic number) contained in the second query result (table 5) in the first data table (table 1); i.e., study numbers 002, 003, 004, and 005 correspond to the data. Then, the school numbers, names, and classes corresponding to the school numbers 002, 003, 004, and 005 are output and grouped by the grouping field (class), thereby obtaining a first query result. See table 6 for an example of a first query result.

TABLE 6

Step S304: and integrating the first query result and the second query result according to the logical relation in the target search statement to obtain a final query result.

In some cases, the sum of the query results at the first query result and the second query result is not the final query result; and corresponding logic relationship integration needs to be carried out on the first query result and the second query result, so that the final query result can be obtained.

As shown in fig. 6, the specific process of this step is described as follows:

step S601: and judging whether the target query result of the target search statement is in accordance with the sum of the query results of the first query result and the second query result, if so, ending the program, otherwise, entering the step S602.

For example, assume that the target query result of the target search statement is field A, sum (field B)/sum (field C) and field D, whereas the first query result contains only field A and sum (field B) and the second query result contains sum (field C) and field D. The comparison shows that the field A and the field D are consistent with the target query result, and therefore, the field A and the field D are determined as the final query result. But sum (field B) and sum (field C) do not match sum (field B)/sum (field C) in the target query result; so, sum (field B) and sum (field C) are intermediate results, not final query results.

Step S602: and screening out at least two intermediate results which do not accord with the target query result from the first query result and the second query result.

In the first query result and the second query result, the result that does not match the target query result is not the final query result but an intermediate result. Therefore, it is necessary to screen out the intermediate results and perform logic operation according to the corresponding logic relationship of the intermediate results.

For example, continuing the previous example, the intermediate results are screened for sum (field B) and sum (field C) in the first query result and the second query result.

Step S603: and calculating the operation result of the at least two intermediate results according to the logic operators of the at least two intermediate results.

And determining logic operators of at least two intermediate results in the target query result, and performing logic operation on the at least two intermediate results by using the logic operators to obtain an operation result.

For example, comparing sum (field B) and sum (field C) with the target query result, finding that the logical operation required to be performed on sum (field B) and sum (field C) is "/"; namely, sum (field B) and sum (field C) are divided, so as to obtain the final query result.

Step S604: and determining other query results which accord with the target query result in the first query result and the second query result and the operation result as the final query result.

For example, field a and field D, and sum (field B)/sum (field C) after logical operations are determined in the first query result and the second query result, and then the field A, sum (field B)/sum (field C), and field D are determined as the final query result.

The specific implementation of the individual steps in this application is described below:

recall step S402 shown in fig. 4: in the first mapping data set, determining a one-to-one correspondence between packet data in the packet data set and first data in the first data set as the mapping relationship between the packet field and the common field.

In determining the mapping relationship, the entire content of the first mapping data set may be utilized, and a part of the content of the first mapping data set may also be utilized. Since the data volume of the first mapping data set is relatively large, and the final query result is generally arranged at the position of the front, the part of the content arranged at the front can be selected in the first mapping data set to determine the mapping relationship.

The following describes in detail the process of selecting a part of the content in the first mapping data set and determining the mapping relationship. As shown in fig. 7, the method specifically includes:

step S701: and determining a part of data sets selected from the first mapping data set according to the sequence from top to bottom as a second mapping data set. Since the data in the first mapping data set are all arranged in order, and the required data are all at the position before the arrangement examination in general, the partial data set before the arrangement examination can be selected. Specifically, a predetermined number may be set. The specified number is used to limit the number of selected partial data sets.

Assuming that the prescribed number is 1000, 1000 pieces of data of sequence number 0 to sequence number 1000 are selected as the second mapping data set in the first mapping data set.

Step S702: and in the second mapping data set, determining the one-to-one correspondence between the packet data in the packet data set and the first data in the first data set as the mapping relationship between the packet field and the common field.

Determining the mapping relationship between the packet field and the common field in the second mapping data set, and after that, performing step S302 in fig. 3: and determining a second query result corresponding to the second data table in querying the second data table by using a second query statement and a second condition restriction statement and obtaining intermediate query results grouped by virtual grouping fields.

Step S302 may be subdivided into two steps:

step S3021: and querying the second data table by using a second query statement and a second condition limiting statement, and obtaining intermediate query results grouped according to the virtual grouping field.

Step S3022: and determining a second query result corresponding to the second data table in the intermediate query result.

Determining the second query result corresponding to the second data table can be divided into two cases:

in the first case: determining the intermediate query result as the second query result if the target search statement does not contain a number limit statement.

Typically, the data size of the intermediate query results is large, and some target search statements contain a size limiting statement for limiting the size of the output results. In some target search sentences, the number limit sentences are not included, i.e., the number of output results is not limited. In this case, it may be straightforward for all intermediate query results to be determined as second query results.

In the second case: and under the condition that the target search statement comprises a quantity limiting statement, determining a preset quantity of data extracted from the intermediate query result as the second query result.

The quantity limiting statement comprises a preset quantity limited by the output result, and the data quantity of the intermediate query result is larger, so that the data of the preset quantity intercepted from top to bottom according to the existing arrangement sequence in the intermediate query result is determined as the second query result.

Assume that the intermediate query results obtained through step 701, step 702, and step 3021 for the first time are represented by intermediate query result 1. As shown in fig. 8, a specific implementation procedure for the second case is as follows:

step S801: judging whether the data quantity of the existing intermediate query results is smaller than a preset quantity, and if not, entering the step S802; if it is smaller, the process proceeds to step S803.

Since a preset amount of data needs to be extracted from the intermediate query result 1 and determined as the second query result, it is necessary to first determine whether there is a sufficient amount of data in the intermediate query result 1. If there is a sufficient amount of data in the intermediate query data 1, step S802 is directly entered to determine a second query result.

Step S802: and determining a preset amount of data extracted from the intermediate query result as the second query result in the existing intermediate query result.

Step S803: the second mapping data set is redetermined, steps S702, and S3021 are executed again, a new intermediate query result is determined, and the process advances to step S801.

If there is not enough data in the intermediate query data 1, a preset amount of data cannot be extracted. This indirectly means that the amount of data of the second mapping data set used by the intermediate query result 1 is too small, so that a sufficient number of second query results cannot be extracted now.

Therefore, the second mapping data set needs to be determined again, and assuming that the data with sequence number 0-sequence number 1000 is used in the intermediate query result 1, 1000 pieces of data with sequence number 1000-sequence number 2000 are selected again to be determined as the second mapping data set.

And repeatedly executing the step S702 and the step S3021 in combination with the second mapping data set, and determining an intermediate query result 2. Then, the process proceeds to step S801.

In step S801, it is determined whether the sum of the data amounts of the intermediate query result 1 and the intermediate query result 2 is less than the preset amount, and if not, it indicates that there is enough data amount, and a second query result may be determined in the intermediate query result 1 and the intermediate query result 2.

If the sum of the data quantity of the intermediate query result 1 and the intermediate query result 2 is still smaller than the preset quantity, it indicates that the second query result cannot be determined by using the intermediate query result 1 and the intermediate query result 2; at this time, step S803, step S702, and step S3021 may be repeatedly performed until the sum of the data amounts of all the intermediate query results is not less than the preset amount.

The above is all detailed implementation of the present application.

The detailed steps of the present application are illustrated below by way of another specific example:

suppose that: the first data table is t1 table a, b, c; the second data table is t2 table c, d, e, f; the t1 table is not a unified data source with the t2 table, even one is a mysql data source and one is an oracle data source.

The target search statement is:

select t1.a,sum(t1.b)/sum(t2.d),sum(t2.e),t1.c

from t1 left join t2 on t1.c＝t2.c

where t1.b＝8and t2.d＝9

group by t1.a

limit 20

the first step is as follows: obtaining a syntax tree of the target search statement by analyzing the target search statement; the father node of the grammar tree is each keyword in the target search statement, the analysis result of each level under each keyword is a child node of the father node, and the analysis result of the last level is a leaf node of the father node. The detailed syntax tree is shown in fig. 9.

Relevant key words are defined in the database, and nodes corresponding to the key words in the target search statement are father nodes, such as select, from, where, group by and limit. The content between two keywords is a child of the previous keyword. For example, the content between select and from is the select sub-node, e.g., t1.a, sum (t1.b)/sum (t2.d), sum (t2.e), t1. c.

The two data tables t1 and t2 can be determined by analyzing the target search statement, and the field a after the keyword group is a grouping field. The common field of both tables is field c.

The second step is that: the mapping data set is queried and a mapping relationship is determined.

The first conditional restriction statement (where t1.b ═ 8) associated with the t1 table is extracted in the syntax tree, and then the mapping dataset for the grouping field a and the common field c is queried in the t1 table. The specific query statement may be:

select a,c

from t1

where b＝8

group by a,c

order by a,c

limit 10000

since the first conditional restriction statement related to the t1 table is involved in the query process, mapping the data set may be equivalent to performing a data query on the t1 table.

The quantity limit statement in this step is changed from "limit 20" to "limit 10000" because the subsequent process needs to query the second query result of the t2 table in the mapping data set obtained from the t1 table. Since the data set conforming to the first conditional restriction statement in t1 may be very large, 10000 pieces of data between sequence number 0 and sequence number 10000 are selected from top to bottom after grouping and sorting by fields a, c to form a mapping data set. Thereby reducing the amount of data processed to increase query speed. Wherein 10000 is the number specifically set in this embodiment, and may be determined according to the actual situation in the practical application, which is not limited herein.

And according to the mapping data set, determining the one-to-one correspondence relationship between the data set of the field a and the data set of the field c in the mapping data set as the mapping relationship.

The third step: the virtual packet field is constructed in the second data table and a data query is made in the t2 table.

The virtual grouping fields are dynamically created in the t2 table according to the mapping data set according to the mapping relationship. If the value 1 in the field c of the t2 table is equal to the value 2 in the mapping data set field c, the value 3 in the grouping field a corresponding to the value 2 in the mapping data set is determined as the value of the virtual field corresponding to the value 1. In this way, a virtual packet field is constructed in the second data table. Then, a second search statement is obtained in the syntax tree, and data query is performed in a second data table in combination with the virtual grouping field, so that a query result 1 is obtained.

The detailed query statement may be:

select sum(t2.d),sum(t2.e)

case

for(Object aValue in Map.keySet())

{when t2.c in Map.get(aValue)then aValue}

as t2.a

from t2

where t2.d＝9

group by t2.a order by t2.a

limit 20

after the step is completed, the query result 1 can be obtained, and since the number of output results in the query is limited to 20, if the number of data of the query result 1 is not less than 20, the final data set can be locked in the step. However, if the number of data of query result 1 is less than 20, it is proven that the final data set has not been locked. At this time, the second step is required to be entered, and the number limit statement limt 10000,20000 in the second step is changed; obtaining a query result 2 in the third step of repeated execution; then, whether the sum of the data quantity of the query result 1 and the query result 2 is less than 20 is judged, if the sum of the data quantity of the query result 1 and the data quantity of the query result 2 is still less than 20, the second step and the third step are continuously and repeatedly executed until the sum of the data quantity of all the obtained query results is not less than 20.

The fourth step: and querying the first data table to obtain a first query result.

Since it is determined in the third step that the second query result is already a result satisfying the second search statement and the first conditional restriction statement, the final result set is obtained. And determining a first query statement in the syntax tree, and querying corresponding field contents in the first data table by combining a second query result.

select t1.a,sum(t1.b),t1.c

from t1

where t1.a in (a in the 3 result set)

group by t1.a

In this step, the corresponding other fields needing to be queried in t1 are queried reversely according to the second query result of t2, so as to obtain the first query result.

The fifth step: and integrating the first query result and the second query result.

The final query results are determined to be t1.a, sum (t1.b)/sum (t2.d), sum (t2.e), and t1.c in the keywords of select in the syntax tree. However, the first query result includes t1.a, sum (t1.b), and t1. c; the second query result includes sum (t2.d) and sum (t2. e). Thus, sum (t1.b) in the first query result and sum (t2.d) in the second query result are intermediate results.

Therefore, it is necessary to determine the logical relationship between sum (t1.b) and sum (t2.d) in the child node of the select parent node of the syntax tree, and then perform logical operation on sum (t1.b) and sum (t2.d) to obtain sum (t1.b)/sum (t2. d). The final query data sets t1.a, sum (t1.b)/sum (t2.d), sum (t2.e), and t1.c are then output.

Corresponding to the cross-data-source query method shown in fig. 3, the present application provides a cross-data-source query apparatus, as shown in fig. 10, including:

an obtaining unit 101, configured to obtain, by analyzing a target search statement, a mapping relationship between a common field of two data tables related to the target search statement and a packet field in the target search statement; wherein, a data table containing the grouping field in the two data tables is a first data table, a data table not containing the grouping field is a second data table, the mapping relation is determined in a query result obtained by querying the first data table according to a first conditional restriction statement, and the first conditional restriction statement is a conditional restriction statement related to the first data table in the target search statement;

the first processing unit 102 is configured to determine, in querying the second data table by using a second query statement and a second conditional restriction statement and obtaining intermediate query results grouped by a virtual grouping field, a second query result corresponding to the second data table; wherein the virtual grouping field is a virtual field which is created in the second data table by using the mapping relation and is equivalent to the grouping field in the first data table; the second query statement and the second conditional restriction statement are the query statement and the conditional restriction statement related to the second data table in the target search statement;

a second processing unit 103, configured to determine, in query the first data table by using a first query statement and an additional condition restriction statement and obtaining query results grouped by the grouping field, a first query result corresponding to the first data table; wherein the additional condition restriction statement is that the numerical value of the grouping field in the first data table is equal to the numerical value of the virtual grouping field in the second query result; the first query statement comprises a query statement in the target search statement that is related to the first data table;

an integrating unit 104, configured to integrate the first query result and the second query result according to the logical relationship in the target search statement, so as to obtain a final query result.

As shown in fig. 11, the acquiring unit 101 includes:

a first query unit 111, configured to query, in combination with the first conditional restriction statement, a first mapping data set corresponding to both the grouping field and the common field in the first data table; wherein the first mapping data set comprises a packet data set corresponding to the packet field and a first data set corresponding to the common field, and the packet data in the packet data set is in one-to-one correspondence with the first data in the first data set; wherein the packet data set comprises a plurality of packet data, and the first data set comprises a plurality of first data;

a first mapping relation determining unit 112, configured to determine, in the first mapping data set, a one-to-one correspondence between packet data in the packet data set and first data in the first data set as the mapping relation between the packet field and the common field.

Wherein the first mapping relationship determining unit 112 includes:

a mapping data set determining unit 113, configured to determine a partial data set selected in the first mapping data set according to a sequence from top to bottom as a second mapping data set;

a second mapping relation determining unit 114, configured to determine, in the second mapping data set, a one-to-one correspondence between packet data in the packet data set and first data in the first data set as the mapping relation between the packet field and the common field.

The mapping data set determining unit 113 is further configured to determine, if the number of data of the intermediate query result is smaller than the preset number, a part of data sets reselected from the first mapping data set as the second mapping data set.

As shown in fig. 12, the first processing unit 102 includes:

a second query unit 121, configured to query the second data table by using a second query statement and a second conditional restriction statement, and obtain intermediate query results grouped by a virtual grouping field;

a first determining unit 122, configured to determine, in the intermediate query results, a second query result corresponding to the second data table.

As shown in fig. 13, the second querying unit 121 includes:

a creating unit 131, configured to dynamically create the virtual grouping field in the second data table by using the mapping relationship; wherein the virtual packet field corresponds to a virtual packet data set; wherein the determination process of each virtual packet data in the virtual packet data set comprises: determining a piece of second data in a second data set of the second data table corresponding to the common field, and determining grouped data corresponding to the first data in the mapping relationship as virtual grouped data corresponding to the second data under the condition that the second data is equal to one piece of first data in the mapping relationship;

an output unit 132, configured to query the second data table according to the second query statement and the second conditional restriction statement, and output an intermediate query result grouped according to the virtual grouping field.

As shown in fig. 14, the first determining unit 122 includes:

a second determining unit 141, configured to determine the intermediate query result as the second query result if the target search statement does not include a number limit statement;

a determining unit 142, configured to determine whether the data quantity of the intermediate query result is smaller than a preset quantity under the condition that the target search statement includes a quantity limiting statement; wherein the preset number is extracted from the conditional restriction statement;

a third determining unit 143, configured to determine, if the number of data of the intermediate query result is not less than the preset number, the preset number of data extracted from the intermediate query result as the second query result.

As shown in fig. 15, the integration unit 104 includes:

a screening unit 151, configured to screen, if a sum of query results of the first query result and the second query result does not match a target query result in the target search statement, at least two intermediate results that do not match the target query result from among the first query result and the second query result;

a calculating unit 152, configured to calculate an operation result of the at least two intermediate results according to a logical operator of the at least two intermediate results;

a final result determining unit 153, configured to determine, as the final query result, another query result, which matches the target query result, of the first query result and the second query result, and the operation result.

Referring to fig. 1, the present application provides a cross-data source query system in a first embodiment, including:

The processor 11 is specifically configured to: acquiring a mapping relation between common fields of two data tables related to a target search statement and grouped fields in the target search statement by analyzing the target search statement; wherein, a data table containing the grouping field in the two data tables is a first data table, a data table not containing the grouping field is a second data table, the mapping relation is determined in a query result obtained by querying the first data table according to a first conditional restriction statement, and the first conditional restriction statement is a conditional restriction statement related to the first data table in the target search statement; determining a second query result corresponding to a second data table in the intermediate query results which are obtained by querying the second data table by using a second query statement and a second condition restriction statement and grouped by a virtual grouping field; wherein the virtual grouping field is a virtual field which is created in the second data table by using the mapping relation and is equivalent to the grouping field in the first data table; the second query statement and the second conditional restriction statement are the query statement and the conditional restriction statement related to the second data table in the target search statement; determining a first query result corresponding to a first data table in query of the first data table by using a first query statement and an additional condition restriction statement and obtaining query results grouped according to the grouping field; wherein the additional condition restriction statement is that the numerical value of the grouping field in the first data table is equal to the numerical value of the virtual grouping field in the second query result; the first query statement comprises a query statement in the target search statement that is related to the first data table; and integrating the first query result and the second query result according to the logical relation in the target search statement to obtain a final query result.

Referring to fig. 2, the present application further provides a second embodiment of a cross-data-source query system, including:

a processor 21, an input device 22 connected to said processor 21, and a memory 23 connected to said processor for storing a database;

The processor is specifically configured to obtain, by analyzing a target search statement, a mapping relationship between a common field of two data tables related to the target search statement and a grouping field in the target search statement; wherein, a data table containing the grouping field in the two data tables is a first data table, a data table not containing the grouping field is a second data table, the mapping relation is determined in a query result obtained by querying the first data table according to a first conditional restriction statement, and the first conditional restriction statement is a conditional restriction statement related to the first data table in the target search statement; determining a second query result corresponding to a second data table in the intermediate query results which are obtained by querying the second data table by using a second query statement and a second condition restriction statement and grouped by a virtual grouping field; wherein the virtual grouping field is a virtual field which is created in the second data table by using the mapping relation and is equivalent to the grouping field in the first data table; the second query statement and the second conditional restriction statement are the query statement and the conditional restriction statement related to the second data table in the target search statement; determining a first query result corresponding to a first data table in query of the first data table by using a first query statement and an additional condition restriction statement and obtaining query results grouped according to the grouping field; wherein the additional condition restriction statement is that the numerical value of the grouping field in the first data table is equal to the numerical value of the virtual grouping field in the second query result; the first query statement comprises a query statement in the target search statement that is related to the first data table; and integrating the first query result and the second query result according to the logical relation in the target search statement to obtain a final query result.

The functions described in the method of the present embodiment, if implemented in the form of software functional units and sold or used as independent products, may be stored in a storage medium readable by a computing device. Based on such understanding, part of the contribution to the prior art of the embodiments of the present application or part of the technical solution may be embodied in the form of a software product stored in a storage medium and including several instructions for causing a computing device (which may be a personal computer, a server, a mobile computing device or a network device) to execute all or part of the steps of the method described in the embodiments of the present application. And the aforementioned storage medium includes: a U-disk, a removable hard disk, a Read-Only Memory (ROM), a Random Access Memory (RAM), a magnetic disk or an optical disk, and other various media capable of storing program codes.

The embodiments are described in a progressive manner, each embodiment focuses on differences from other embodiments, and the same or similar parts among the embodiments are referred to each other.

The previous description of the disclosed embodiments is provided to enable any person skilled in the art to make or use the present application. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the generic principles defined herein may be applied to other embodiments without departing from the spirit or scope of the application. Thus, the present application is not intended to be limited to the embodiments shown herein but is to be accorded the widest scope consistent with the principles and novel features disclosed herein.

Claims

1.A cross-data-source query method, comprising:

acquiring a mapping relation between common fields of two data tables related to a target search statement and grouped fields in the target search statement by analyzing the target search statement; wherein, the data table containing the grouping field in the two data tables is a first data table, the data table not containing the grouping field is a second data table, and the mapping relation is determined in a query result obtained by querying the first data table according to a first condition restriction statement; the target search statement comprises: a first query statement, a first conditional restriction statement, a second query statement, and a second conditional restriction statement;

2. The method of claim 1, wherein the first conditional restriction statement is a conditional restriction statement in the target search statement that is related to the first data table.

3. The method of claim 1, wherein the second query statement and the second conditional restriction statement are a query statement and a conditional restriction statement in the target search statement that are related to the second data table.

4. The method of claim 1, wherein the first query statement comprises a query statement in the target search statement that is related to the first data table.

5. The method of claim 1, wherein the obtaining the mapping relationship between the common fields of the two data tables involved in the target search statement and the grouping fields in the target search statement by parsing the target search statement comprises:

6. The method of claim 5, wherein the determining, in the first mapping dataset, a one-to-one correspondence between packet data in the packet data set and first data in the first dataset as the mapping between the packet field and the common field comprises:

7. The method of claim 6, wherein determining a second query result corresponding to the second data table in querying the second data table using a second query statement and a second conditional restriction statement and obtaining intermediate query results grouped by virtual grouping field comprises:

8. The method of claim 1, wherein said determining the second query result corresponding to the second data table comprises:

9. The method of claim 7, wherein said determining the second query result corresponding to the second data table comprises:

10. The method of claim 9, further comprising:

11. The method of claim 1, wherein said integrating the first query result and the second query result according to the logical relationship in the target search statement to obtain a final query result comprises:

12. A cross-data source querying device, comprising:

the system comprises an acquisition unit, a search unit and a processing unit, wherein the acquisition unit is used for acquiring the mapping relation between the common fields of two data tables related to a target search statement and the grouping fields in the target search statement by analyzing the target search statement; wherein, the data table containing the grouping field in the two data tables is a first data table, the data table not containing the grouping field is a second data table, and the mapping relation is determined in a query result obtained by querying the first data table according to a first condition restriction statement; the target search statement comprises: a first query statement, a first conditional restriction statement, a second query statement, and a second conditional restriction statement;

13. The apparatus of claim 12, wherein the obtaining unit comprises:

14. The apparatus of claim 13, wherein the first determine mapping relationship unit comprises:

15. The apparatus of claim 14, wherein the first processing unit comprises:

16. The apparatus of claim 15, wherein the second query unit comprises:

17. The apparatus of claim 15, wherein the first determining unit comprises:

18. The apparatus of claim 17, further comprising:

19. The apparatus of claim 12, wherein the integration unit comprises:

20. A cross-data source query system, comprising:

the input equipment is used for acquiring a target search statement and sending the target search statement to the processor; the target search statement comprises: a first query statement, a first conditional restriction statement, a second query statement, and a second conditional restriction statement;

21. A cross-data source query system, comprising: