WO2015184782A1

WO2015184782A1 - Data query method and device

Info

Publication number: WO2015184782A1
Application number: PCT/CN2014/095684
Authority: WO
Inventors: 陈波
Original assignee: 中兴通讯股份有限公司
Priority date: 2014-10-22
Filing date: 2014-12-30
Publication date: 2015-12-10
Also published as: CN105589864A

Abstract

A data query method and device. During a data query, first obtaining query conditions, the query conditions comprising a key query field, a common query field related to the key query field, and data filtering conditions for the key query field; determining the database tables corresponding respectively to the common query field and the key query field; correspondence exist between the data stored in the database tables corresponding to the common query field and key query field; finding from the database table corresponding to the key query field the key data that satisfy the data filtering conditions of the key query field, and then finding the related data corresponding to the key data from the database table corresponding to the common query field according to said correspondence. Key data corresponding to the key field is first found, and related data corresponding to other common fields are then directly found using the correspondence, thereby substantially reducing resources consumption and enhancing query efficiency.

Description

Data query method and device

Technical field

The present invention relates to the field of data query technology, and in particular, to a data query method and apparatus.

Background technique

At present, for the data corresponding to certain key fields, there are often statistical requirements such as maximal/minimum and logical filtering. The similar statistics also include the busy hour indicator (the hour/minute of the minimum/maximum index value in a day or a time range) Information), and these key fields are usually counted along with many other common fields to facilitate comparison analysis. A common method is to sequentially query all the corresponding values of this field, and obtain the final statistical result according to the value corresponding to the key field. Since querying all fields requires access to multiple data tables, the associated query method consumes a lot of database resources and leads to statistical inefficiencies.

Summary of the invention

The main technical problem to be solved by the present invention is to provide a data query method and device, and solve the problem that the related data query method has large resource consumption and low query efficiency.

In order to solve the above technical problems, the following technical solutions are adopted:

A data query method, including:

Obtaining a query condition, wherein the query condition includes a key query field, a common query field associated with the key query field, and a key query field data filtering condition;

Determining a database table corresponding to the common query field and the key query field respectively;

Setting an association relationship between the data stored in the general query field database table and the data stored in the database table corresponding to the key query field;

Determining, according to the keyword query field, key data that meets the filtering condition of the key query field data from a database table corresponding to the key query field;

Querying from the database table corresponding to the common query field according to the association relationship Data associated with key data.

Optionally, when the key query field includes multiple query fields, the step of determining the database table corresponding to the key query field includes:

The corresponding data in the respective database tables is processed according to a preset algorithm to obtain a database table corresponding to the keyword query field.

Optionally, before the step of querying the key data that meets the data filtering condition of the key query field from the database table corresponding to the key query field, the method further includes:

Determining whether the ratio of the number of query fields included in the keyword query field to the number of query fields included in the common query field is less than a preset ratio; if less than, executing the database table corresponding to the key query field The step of querying the key data that satisfies the data filtering condition of the key query field is queried.

Determining whether the amount of data stored in the database in which the database table corresponding to the key query field is located is greater than a preset threshold. If the value is greater than, the query is performed from the database table corresponding to the key query field to satisfy the key query field. The step of data filtering the key data.

Optionally, the association relationship includes at least one of a time association and a location association.

A data query device includes a condition acquisition module, a data table determination module, a key data query module, and an associated data query module, wherein

The condition obtaining module is configured to: obtain a query condition, where the query condition includes a key query field, a common query field associated with the key query field, and a key query field data screening condition;

The data table determining module is configured to: determine a database table corresponding to the common query field and the key query field, and set data corresponding to the database table corresponding to the common query field and the key query field The relationship between the data stored in the database table;

The key data query module is configured to: query, according to the keyword query field, a key that meets the filtering condition of the key query field from a database table corresponding to the key query field data;

The associated data query module is configured to: query data associated with the key data from a database table corresponding to the common query field according to the association relationship.

Optionally, the data table determining module includes a computing processing submodule, and is configured to:

When the key query field includes multiple query fields, the corresponding data in the respective database tables are processed according to a preset algorithm to obtain a new database table, and the new database table is The database table corresponding to the keyword query field.

Optionally, the device further includes a ratio judgment module, where

The ratio determining module is configured to: determine whether a ratio of the number of query fields included in the keyword query field to the number of query fields included in the common query field is less than the preset ratio; if not, notify the The key data query module queries, from the database table corresponding to the key query field, key data that satisfies the data filtering condition of the key query field.

Optionally, the device further includes a data amount determining module, where

The data amount judging module is configured to: determine whether the amount of data stored in the database in which the database table corresponding to the key query field is located is greater than a preset threshold, and if not, notify the key data query module from the key query field The corresponding database table queries the key data that meets the filtering conditions of the key query field data.

A computer program comprising program instructions that, when executed by a computer, cause the computer to perform any of the above data query methods.

A carrier carrying the computer program.

The beneficial effects of the embodiments of the present invention are:

The data query method and device provided by the embodiment of the present invention first acquires a query condition when performing data query, and the query condition includes a key query field and an ordinary query associated with the key query field. Field, and key query field data filtering conditions; determine the database table corresponding to the common query field and the key query field; the relationship between the common query field and the data stored in each database table corresponding to the key query field; Key data corresponding to the data filtering condition of the key query field is queried in the database table corresponding to the key query field, and the associated data corresponding to the key data is queried from the database table corresponding to the common query field according to the above association relationship (that is, each common query) The data corresponding to the field). It can be seen that the embodiment of the present invention can firstly query the key data corresponding to the key segment, and then directly query the associated data corresponding to other common fields by using the association relationship, which can reduce the data access amount of the database by an order of magnitude, and can significantly reduce resource consumption, and Improve query efficiency.

BRIEF abstract

1 is a schematic flowchart of a data query method according to Embodiment 1 of the present invention;

2 is a schematic structural diagram of a data query apparatus according to Embodiment 2 of the present invention;

3 is a schematic structural diagram of another data query apparatus according to Embodiment 2 of the present invention;

FIG. 4 is a schematic structural diagram of another data query apparatus according to Embodiment 2 of the present invention.

Preferred embodiment of the invention

The present invention will be further described in detail below with reference to the accompanying drawings.

Embodiment 1:

Referring to FIG. 1 , the data query method provided in this embodiment includes:

Step 101: Acquire a query condition; the query condition may be currently input by the user, or may be preset in advance. The query conditions in this embodiment include a key query field, a common query field associated with the key query field, and a key query. Field data filtering conditions;

Optionally, the key query field data filtering condition may be, for example, a maximum of N data, a minimum of M data, or each data within a user-set range value, and the like.

Step 102: Determine a database table corresponding to the common query field and the key query field respectively; set an association relationship between the data stored in the database table corresponding to the common query field and the data stored in the database table corresponding to the key query field, That is, the two are associated database tables;

Optionally, the association relationship includes at least one of a latitude association field such as a time association and a location association;

Time association refers to the existence of a relationship in time, such as the same time or other relationships in time. The positional relationship refers to the existence of a relationship in the position, such as corresponding position or other relationship in position.

Step 103: Query, according to the keyword query field, the key data that meets the filtering condition of the key query field data from the database table corresponding to the key query field;

Step 104: Query data associated with the key data, that is, data corresponding to each common query field, from the database table corresponding to the common query field according to the foregoing association relationship and the common query field.

In this embodiment, the key data corresponding to the key field is first queried, and the associated data corresponding to other common fields is directly queried by using the association relationship, which can reduce the data access amount of the database by an order of magnitude, can significantly reduce resource consumption, and improve the query. effectiveness.

The data query method of FIG. 1 will be explained below through a specific application example.

The application example is described by taking the time association as an example. The key query field included in the query condition is the c2 field, and the c1, c3, and c6 of the T1 table are the common field key query fields. Find the minimum value of the c2 field;

The database table corresponding to the key query field c2 is the following T1 table, and the database table corresponding to the common query field 2014-12-2300:15 is the following T2 table;

The association relationship between the data stored in the database table corresponding to the common query field and the data stored in the database table corresponding to the key query field is: begintime is an associated field, that is, the two tables are associated by time, that is, The start time in the table is 2014-12-23.

In the query, first query the c2 field of T1, and find the minimum value of c2 to get the value of 99, and the time field corresponding to this value is "2014-12-2300:15", according to this time, the associated fields get c1, c3 and T2. Table c4, c5, c6.

The keyword query field input by the user in the embodiment mainly reflects the corresponding query index. The query indicator in this embodiment includes an indicator that can be embodied by using a query field, and includes an indicator that is reflected by multiple query fields; When the query fields are displayed, the key query field in this embodiment includes multiple query fields. At this time, the database table corresponding to the key query field is determined to include:

The corresponding data of the plurality of query fields in the respective database tables are processed according to a preset algorithm to obtain a new database table, and the new database table is a database table corresponding to the keyword query field.

For example, if the indicator needs to be combined with the query field A+the query field B, the keyword query field in this embodiment includes the query fields A and B; when the corresponding database table is determined, the corresponding corresponding in the query field A and the query field B are respectively extracted. The data in the data table is added to obtain a new database table, which is the database table of the keyword query field (ie, query field A + query field B). That is, in this embodiment, for the case where the index is obtained by multiple query field operations, the data table obtained by calculating the plurality of query fields may be first obtained and the key satisfying the screening condition (for example, the maximum 10 values) may be queried therefrom. Data, and then query the values of other common fields according to the key data association, can achieve a good efficiency optimization effect.

For example, if the indicator is reflected by a query field C, the keyword query field in this embodiment includes the query field C; when the corresponding database table is determined, the data table corresponding to the query field C is the keyword query field. Database Table.

In this embodiment, before querying the key data that meets the data filtering condition of the key query field from the database table corresponding to the key query field, the method further includes:

Determining whether the ratio of the number of query fields included in the keyword query field to the number of query fields included in the common query field is less than the preset ratio; if yes, querying the data of the key query field from the database table corresponding to the key query field Key data for screening criteria. That is, in this embodiment, only when the ratio of the number of data tables involved in the key query field to the number of data tables involved in the common query field is less than the preset ratio, the key data corresponding to the key query field is first queried, and then the association relationship is used. Data corresponding to other common query fields of the association; otherwise, the query may be directly queried in the corresponding data table for each query field. This is because when the ratio of the number of query fields included in the keyword query field to the number of query fields included in the normal query field is greater than or equal to the preset ratio, the effect is not obvious for the improvement of the query efficiency. However, the method of the embodiment of the invention can also be used.

The preset ratio in this embodiment may be less than or equal to one-half, for example, the specific value may be one-half, and the number of query fields included in the keyword query field and the number of query fields included in the common query field are assumed. The ratio is one-seventh, less than one-half of the preset ratio. The key data corresponding to the key query field is first queried, and then the association data is used to query the data corresponding to other common query fields.

In this embodiment, before the key data satisfying the data filtering condition of the key query field is queried from the database table corresponding to the key query field, the method further includes determining the key data corresponding to the key query field by combining the data size of the database, and then Use the association relationship to query the data corresponding to other common query fields of the association. Because the database with a small amount of data can be directly queried in the corresponding data table for each query field; the judging process includes:

Determining whether the amount of data stored in the database in which the database table is located is greater than a preset threshold. If yes, querying the database table corresponding to the key query field to query key data that meets the filtering conditions of the key query field; otherwise, directly targeting each query field. Query in the corresponding data table. The method of the embodiment of the present invention is used only when the amount of data stored in the database in which the database table is located is greater than a preset threshold, and the query efficiency is also used when the amount of data stored in the database where the database table is located is less than or equal to a preset threshold. The improvement is not very obvious. However, the method of the embodiment of the invention can also be used.

In this embodiment, for a common relational database, the data amount of the database may be counted according to the number of data tables in the database; for the column storage database, the data amount of the database may be counted according to the number of fields, or according to the number of fields and The amount of data is used to count the amount of data in the database. The preset threshold in this embodiment may be specifically selected according to a specific database and an application scenario or the like.

In order to better understand the present invention, a specific example will be described below.

Suppose a service statistic requires querying 8 associated query fields, which are from 8 associated database tables, one of which is a key query field, and the remaining 7 are common query fields, and the filtering condition is the largest according to the key query field. Ten values (ie Top max10) are presented. And assume that each data table needs to query the amount of data is 10 million, the query process is as follows:

First, according to the key query field, the key data is queried in its corresponding data table. At this time, 10 million pieces of data in the database table need to be accessed to obtain the maximum 10 pieces of data; and then according to the time and/or time of the 10 pieces of data. Associated dimension fields such as location to match the data of the remaining 7 query fields Corresponding data in the library table. At this time, for each common query field, according to the association relationship between the data, only the corresponding 10 pieces of data need to be accessed in the corresponding database table, so only a total of 10 million zeros are needed. 70 data can be used to obtain the final result; and the related query method requires at least 80 million pieces of data to be accessed. It can be seen that the solution provided in this embodiment greatly reduces the reading and writing consumption of the database and effectively improves the statistical efficiency.

Embodiment 2:

This embodiment provides a data query device. Referring to FIG. 2, the data query device 1 includes a condition acquisition module 11, a data table determination module 12, a key data query module 13, and an associated data query module 14;

The condition obtaining module 11 is configured to: obtain a query condition, which may be currently input by the user, or may be preset in advance. The query condition in this embodiment includes a key query field and a common query field associated with the key query field. And key query field data filtering conditions;

Optionally, the key query field data filtering condition may be, for example, selecting the largest N data, the smallest M data, or each data within the user set range value.

The data table determining module 12 is configured to: determine a database table corresponding to the common query field and the key query field respectively; and set an association relationship between the data stored in each database table corresponding to the common query field and the key query field;

Optionally, the association relationship includes at least one of a latitude association field such as a time association and a location association; that is, each database table corresponding to each query field is an associated database table;

The key data query module 13 is configured to: query key data that meets the filtering conditions of the key query field data from the database table corresponding to the key query field;

The associated data query module 14 is configured to: query the data associated with the key data (that is, the data corresponding to each common query field) from the database table corresponding to the common query field according to the foregoing association relationship and the common query field.

The data querying apparatus 1 in this embodiment firstly queries the key data corresponding to the key field, and then directly queries the associated data corresponding to other common fields by using the association relationship, which can reduce the data access amount of the database by an order of magnitude, and can significantly reduce the data access amount. Resource consumption and improve query efficiency.

The keyword query field input by the user in the embodiment mainly reflects the corresponding query index. The query indicator in this embodiment includes an indicator that can be embodied by using a query field, and includes an indicator that is reflected by multiple query fields; When the query fields are embodied, the key query field in this embodiment includes multiple query fields. Therefore, the data table determining module 12 in this embodiment may include a calculation processing sub-module, configured to: according to a preset algorithm, corresponding data of the plurality of query fields in the respective database tables when the key query field includes multiple query fields After processing, a new database table is obtained, and the new database table is a database table corresponding to the keyword query field.

As shown in FIG. 3, the data querying apparatus in this embodiment further includes a ratio determining module 15 configured to query, in the database table corresponding to the key query field, the key data query module to meet the key data of the key query field data screening condition. Before, it is determined whether the ratio of the number of query fields included in the keyword query field to the number of query fields included in the common query field is less than a preset ratio; if yes, the key data query module is notified to query the database table corresponding to the key query field. Key data that meets the key query field data filtering criteria; otherwise, it can be queried directly in each corresponding data table for each query field.

The preset ratio in this embodiment may be less than or equal to one-half, for example, the specific value may be one-half, and the number of query fields included in the keyword query field and the number of query fields included in the common query field are assumed. The ratio is one-seventh, less than one-half of the preset ratio. The key data corresponding to the key query field is first queried, and then the associated relationship is used to query the corresponding common query fields. data.

In this embodiment, before the key data satisfying the data filtering condition of the key query field is queried from the database table corresponding to the key query field, the method further includes determining the key data corresponding to the key query field by combining the data size of the database, and then Use the association relationship to query the data corresponding to other common query fields of the association. Because the database with a small amount of data can be directly queried in the corresponding data table for each query field. As shown in FIG. 4, the data query apparatus 1 in this embodiment further includes a data quantity determining module 16 configured to query, in the database table corresponding to the key query field, the key data query module to meet the filtering conditions of the key query field data. Before the critical data, determining whether the amount of data stored in the database in which the database table is located is greater than a preset threshold, and then notifying the key data query module to query the data table corresponding to the key query field from the database table corresponding to the key query field. Key data. Otherwise, the query is directly made in the corresponding data table for each query field.

Suppose a service statistic requires querying 10 associated query fields, which are from 8 associated database tables, one of which is a key query field, and the remaining 9 are common query fields, and the filtering condition is the smallest according to the key query field. 100 values (ie Top min10) are presented. And assume that each data table needs to query the amount of data is 1 million, the query process is as follows:

First, according to the key query field, the key data is queried in its corresponding data table. At this time, it is necessary to access 1 million pieces of data in the database table to obtain the minimum 100 pieces of data; and then according to the time and/or of the 100 pieces of data. Position and other associated dimension fields to match the corresponding data in the database table corresponding to the remaining 9 query fields. At this time, for each common query field, according to the association relationship between the data, only the corresponding 100 in the corresponding database table is accessed. The data is OK, so you only need to access 1 million and 900 data to get the final result. The related query method needs to access at least 10 million data, which shows that the solution provided in this embodiment is greatly reduced. The database read and write consumption, and effectively improve the statistical efficiency.

The embodiment of the invention also discloses a computer program, comprising program instructions, which when executed by a computer, enable the computer to execute any of the above data query methods.

The embodiment of the invention also discloses a carrier carrying the computer program.

The above is a further detailed description of the present invention in connection with the specific embodiments, and the specific embodiments of the present invention are not limited to the description. It will be apparent to those skilled in the art that the present invention may be made without departing from the spirit and scope of the invention.

Industrial applicability

The embodiment of the present invention can firstly query the key data corresponding to the key segment, and then directly query the associated data corresponding to other common fields by using the association relationship, which can reduce the data access amount of the database by an order of magnitude, can significantly reduce resource consumption, and improve Query efficiency. Therefore, the present invention has strong industrial applicability.

Claims

A data query method, including:

Obtaining a query condition, wherein the query condition includes a key query field, a common query field associated with the key query field, and a key query field data filtering condition;

Determining a database table corresponding to the common query field and the key query field respectively;

Setting an association relationship between the data stored in the general query field database table and the data stored in the database table corresponding to the key query field;

Determining, according to the keyword query field, key data that meets the filtering condition of the key query field data from a database table corresponding to the key query field;

And querying, according to the association relationship, data associated with the key data from a database table corresponding to the common query field.
The data query method according to claim 1, wherein when the key query field includes a plurality of query fields, the step of determining a database table corresponding to the key query field comprises:

The corresponding data in the respective database tables is processed according to a preset algorithm to obtain a database table corresponding to the keyword query field.
The data query method according to claim 2, wherein before the step of querying the key data corresponding to the data filtering condition of the key query field from the database table corresponding to the key query field, the method further comprises:

Determining whether the ratio of the number of query fields included in the keyword query field to the number of query fields included in the common query field is less than a preset ratio; if less than, executing the database table corresponding to the key query field The step of querying the key data that satisfies the data filtering condition of the key query field is queried.
The data query method according to any one of claims 1 to 3, wherein, before the step of querying the key data satisfying the data filtering condition of the key query field from the database table corresponding to the key query field, The method also includes:

Determining whether the amount of data stored in the database in which the database table corresponding to the key query field is located is greater than a preset threshold, and if yes, executing the database corresponding to the key query field The step of querying the key data that satisfies the data filtering condition of the key query field is queried in the table.
The data query method according to any one of claims 1 to 3, wherein the association relationship includes at least one of a time association and a location association.
A data query device includes a condition acquisition module, a data table determination module, a key data query module, and an associated data query module, wherein

The condition obtaining module is configured to: obtain a query condition, where the query condition includes a key query field, a common query field associated with the key query field, and a key query field data screening condition;

The data table determining module is configured to: determine a database table corresponding to the common query field and the key query field, and set data corresponding to the database table corresponding to the common query field and the key query field The relationship between the data stored in the database table;

The key data query module is configured to: query, according to the keyword query field, key data that meets the filtering condition of the key query field data from a database table corresponding to the key query field;

The associated data query module is configured to: query data associated with the key data from a database table corresponding to the common query field according to the association relationship.
The data query device according to claim 6, wherein said data table determining module comprises a computing processing sub-module configured to:

When the key query field includes multiple query fields, the corresponding data in the respective database tables are processed according to a preset algorithm to obtain a new database table, and the new database table is The database table corresponding to the keyword query field.
The data query device according to claim 7, further comprising a ratio judgment module, wherein

The ratio determining module is configured to: determine whether a ratio of the number of query fields included in the keyword query field to the number of query fields included in the common query field is less than the preset ratio; if not, notify the The key data query module queries, from the database table corresponding to the key query field, key data that satisfies the data filtering condition of the key query field.
A data query device according to any one of claims 6-8, further comprising data amount Judging module, wherein

The data amount judging module is configured to: determine whether the amount of data stored in the database in which the database table corresponding to the key query field is located is greater than a preset threshold, and if not, notify the key data query module from the key query field The corresponding database table queries the key data that meets the filtering conditions of the key query field data.
The data query device according to any one of claims 6-8, wherein the association relationship comprises at least one of a time association and a location association.
A computer program comprising program instructions which, when executed by a computer, cause the computer to perform the data query method of any of claims 1-5.
A carrier carrying the computer program of claim 13.