CN107193874B - Data query method based on locator and logic query condition - Google Patents
Data query method based on locator and logic query condition Download PDFInfo
- Publication number
- CN107193874B CN107193874B CN201710260143.XA CN201710260143A CN107193874B CN 107193874 B CN107193874 B CN 107193874B CN 201710260143 A CN201710260143 A CN 201710260143A CN 107193874 B CN107193874 B CN 107193874B
- Authority
- CN
- China
- Prior art keywords
- query
- result
- intermediate result
- data
- locator
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
- 238000000034 method Methods 0.000 title claims abstract description 30
- 238000004806 packaging method and process Methods 0.000 claims description 2
- 238000013461 design Methods 0.000 abstract description 7
- 238000012545 processing Methods 0.000 description 4
- 238000004458 analytical method Methods 0.000 description 3
- 230000011664 signaling Effects 0.000 description 3
- 238000013500 data storage Methods 0.000 description 2
- 238000005065 mining Methods 0.000 description 2
- 238000012856 packing Methods 0.000 description 2
- 238000004364 calculation method Methods 0.000 description 1
- 238000007405 data analysis Methods 0.000 description 1
- 238000011161 development Methods 0.000 description 1
- 238000010586 diagram Methods 0.000 description 1
- 230000000694 effects Effects 0.000 description 1
- 230000003203 everyday effect Effects 0.000 description 1
- 238000004519 manufacturing process Methods 0.000 description 1
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2453—Query optimisation
- G06F16/24534—Query rewriting; Transformation
- G06F16/24537—Query rewriting; Transformation of operators
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Computational Linguistics (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention relates to a data query method based on a locator and a logic query condition, which aims at iterative query intermediate results in a data query process, carries out brand-new data structure design, utilizes the relation between address location and query statements, effectively reduces the storage space required by the query intermediate results, and ensures the integrity of the iterative query intermediate results and the working efficiency of data query.
Description
Technical Field
The invention relates to a data query method based on a locator and a logic query condition, and belongs to the technical field of data warehouse, big data analysis and online analysis processing.
Background
With the continuous development of informatization, more and more enterprises accumulate a large amount of structured data in the production informatization process, and with the example of operator data, with more and more provincial user scales approaching the billion scale, billions of signaling data and charging data are generated every day; the rapid increase of the data volume puts higher demands on the processing scale, the processing speed and the processing mechanism of the data warehouse.
In the face of analysis and mining of big data, the analysis and mining can be completed only by repeated iteration, and each step of calculation generates a corresponding result set; many times, the size of the result set even exceeds the size of the original data set, which puts high requirements on storage and operation rate, and therefore, a fast, economical and simple method is needed to record the intermediate results so as to realize a more efficient data query mode.
Disclosure of Invention
The invention aims to solve the technical problem of providing a data query method which adopts a brand-new design and has the advantages of high efficiency, economy and simplicity and is based on a locator and a logic query condition.
The invention adopts the following technical scheme for solving the technical problems: the invention designs a data query method based on a locator and a logic query condition, which comprises the steps of firstly, obtaining the query condition corresponding to each record in all original data, and respectively using the query condition as the locator of each record; then, carrying out data query according to a target query statement aiming at the original data, wherein the following steps are carried out aiming at intermediate results of all levels of query until a query target result is obtained;
001, respectively obtaining the locators corresponding to the records in the query intermediate result, and entering the step 002;
002, packaging the original data and the locators corresponding to the records in the intermediate query result to form a virtual view corresponding to the intermediate query result, and then entering the step 003;
step 003, judging whether the intermediate result of the query is a query target result, if so, obtaining each query target record from the original data in the query statement according to the locator in the virtual view corresponding to the intermediate result of the query, wherein the query target record is used as the query target result, and the data query is finished; otherwise, go to step 004;
step 004, aiming at the virtual view corresponding to the intermediate result of the query, obtaining each record in the intermediate result of the query from the original data in the query statement according to the locator in the virtual view, and entering step 005;
and 005, further inquiring each record in the intermediate inquiry result to obtain the intermediate inquiry result of the next stage, and returning to the step 001.
As a preferred technical scheme of the invention: in the step 001, while the locators corresponding to the records in the intermediate result of the query are obtained, each designated attribute is set as the designated query attribute of the intermediate result of the query, and then the step 002 is performed;
in the step 002, the original data, the locators corresponding to each record in the intermediate result of the query, and the specified query attributes of the intermediate result of the query are packed to form a virtual view corresponding to the intermediate result of the query, and then the step 003 is performed;
step 003, judging whether the intermediate result is the query target result, if so, obtaining each specified attribute of each query target record from the original data in the query statement according to the locator and the specified query attribute in the virtual view corresponding to the intermediate result as the query target result aiming at the virtual view corresponding to the intermediate result, and finishing the data query; otherwise, go to step 004;
step 004, aiming at the virtual view corresponding to the query intermediate result, obtaining each record in the query intermediate result from the original data in the query intermediate result through a query statement according to the locator and the specified query attribute, and entering step 005;
and 005, further inquiring each record in the intermediate inquiry result to obtain the intermediate inquiry result of the next stage, and returning to the step 001.
As a preferred technical scheme of the invention: the step 001 further comprises the following steps:
setting an intermediate result table aiming at the intermediate result of the query, setting the maximum record number of the intermediate result table as M, then judging whether the record number in the intermediate result of the query is greater than M, if so, randomly acquiring M records from the intermediate result and storing the M records in the intermediate result table; otherwise, storing each record in the intermediate result table.
As a preferred technical scheme of the invention: the target query statement and the query statement are SQL query statements.
Compared with the prior art, the data query method based on the locator and the logic query condition has the following technical effects by adopting the technical scheme:
(1) the data query method based on the locator and the logic query condition is designed, a brand new data structure design is carried out aiming at the iterative query intermediate result in the data query process, the storage space required by the query intermediate result is effectively reduced by utilizing the relation between the address location and the query statement, and the working efficiency of data query is ensured while the integrity of the iterative query intermediate result is ensured;
(2) in the data query method based on the locator and the logic query condition, aiming at the query intermediate result, each designated attribute is set as the designated query attribute of the query intermediate result, and the designated query attribute is introduced, so that the data storage space is effectively reduced, the records needing to be browsed can be rapidly positioned by a user, and in addition, the storage overhead and the I/O overhead during access can be greatly reduced by introducing the designated query attribute based on the locator design;
(3) in the data query method based on the locator and the logic query condition, the intermediate result table is further designed and set aiming at the intermediate result of query, so that analysts can be supported to browse the intermediate result as required, and the access speed of the intermediate result of query is effectively improved.
Drawings
FIG. 1 is a flow chart illustrating a method for querying data based on a locator and a logical query condition according to the present invention;
FIG. 2 is a schematic structural diagram of a virtual view in a data query method based on a locator and a logical query condition according to the present invention.
Detailed Description
The following description will explain embodiments of the present invention in further detail with reference to the accompanying drawings.
As shown in fig. 1, in the data query method based on the Locator (Locator) and the logic query condition according to the present invention, first, the query condition corresponding to each record in all the original data is obtained and respectively used as the Locator (Locator) of each record; then, carrying out data query according to a target query statement aiming at the original data, wherein the following steps are carried out aiming at intermediate results of all levels of query until a query target result is obtained;
001, respectively obtaining a Locator (Locator) corresponding to each record in the query intermediate result, and entering the step 002;
step 002, packing the original data and the locators (Locator) corresponding to each record in the intermediate result of the query to form a virtual view corresponding to the intermediate result of the query, and then entering step 003;
step 003, judging whether the intermediate result of the query is a query target result, if so, obtaining each query target record from the original data in the query statement according to the Locator (Locator) in the virtual view corresponding to the intermediate result of the query, and taking the query target record as a query target result to finish data query; otherwise, go to step 004;
step 004, aiming at the virtual view corresponding to the query intermediate result, obtaining each record in the query intermediate result from the original data in the query statement according to a Locator (Locator) in the virtual view, and entering step 005;
and 005, further inquiring each record in the intermediate inquiry result to obtain the intermediate inquiry result of the next stage, and returning to the step 001.
The data query method based on the Locator and the logic query condition is designed by the technical scheme, a brand new data structure design is carried out aiming at the iterative query intermediate result in the data query process, the storage space required by the query intermediate result is effectively reduced by utilizing the relation between the address location and the query statement, and the working efficiency of data query is ensured while the integrity of the iterative query intermediate result is ensured.
Based on the above technical scheme of designing a data query method based on a Locator (Locator) and a logic query condition, the present invention further designs the following preferred technical scheme: aiming at the query intermediate result, setting each designated attribute as the designated query attribute of the query intermediate result, and introducing the designated query attribute, so that the data storage space is effectively reduced, and the user can be helped to quickly locate the record to be browsed; and aiming at the intermediate result of the query, an intermediate result table is further designed and set, so that analysts can be supported to browse the intermediate result as required, and the access speed of the intermediate result of the query is effectively improved.
As shown in fig. 1, in the actual application process, the data query method based on the Locator (Locator) and the logic query condition according to the present invention first obtains the query condition corresponding to each record in all the original data, and respectively uses the query condition as the Locator (Locator) of each record; then, data query is carried out on the original data according to a target SQL query statement, wherein the following steps are carried out on intermediate results of all levels of query until a query target result is obtained;
step 001, respectively obtaining a Locator (Locator) corresponding to each record in the intermediate result of the query, setting each designated attribute as the designated query attribute of the intermediate result of the query according to the intermediate result of the query, simultaneously setting an intermediate result table according to the intermediate result of the query, setting the maximum record number of the intermediate result table as M, then judging whether the record number in the intermediate result of the query is greater than M, if so, randomly obtaining M records from the intermediate result and storing the M records in the intermediate result table; otherwise, storing each record in the intermediate result table, and then entering step 002;
step 002, packing the original data, the locators (Locator) corresponding to each record in the intermediate result of the query and the specified query attributes of the intermediate result of the query to form a virtual view corresponding to the intermediate result of the query, as shown in fig. 2, and then entering step 003;
step 003, judge whether the intermediate result of this inquiry is the result of the query goal, if yes, to the virtual view that the intermediate result of this inquiry corresponds, according to Locator (Locator) and appointing and inquiring the attribute among them, obtain every appointed attribute of every query goal record from the original data among them through SQL query statement, as inquiring the target result, the data query is over; otherwise, go to step 004;
step 004, aiming at the virtual view corresponding to the intermediate result of the query, obtaining each record in the intermediate result of the query from the original data in the SQL query statement according to the Locator (Locator) and the specified query attribute, and entering step 005;
and 005, further inquiring each record in the intermediate inquiry result to obtain the intermediate inquiry result of the next stage, and returning to the step 001.
The technical scheme of the data query method based on the Locator and the logic query condition is applied to data of an actual operator, wherein mobile signaling data in original data is shown in the following table 1, that is, a mobile signaling table TBL:
| Locator | time of day | Number (I) | Number of opposite terminal | Home of opposite terminal | Duration of call |
TABLE 1
Assuming more than 100 hundred million records in the original data, two iterative queries are required:
iteration 1: selecting the conversation time from 10 minutes to 15 minutes, and the occurrence time is 2015, 10 months, 1 day 12: 00 to 13: records between 00.
Iteration 2: on the basis of the result, searching a number with an opposite-end attribution place of Beijing and a home-end number attribution place of Nanjing; (the first seven digits of the number can determine the attribution of the number, and belong is set as a function for calculating the attribution of the number)
For iteration 1, the SQL statement that generates T1 is:
Insert into T1as Select locator from TBL where
"call duration" >600and "call duration" < ═ 900
and "time" between (time ("20151001120000"),
time(“20151001130000”))
when it is desired to view the results of iteration T1, the following SQL statement may be used:
select Proj from tbl where locator in(select locator from T1)
for iteration 2, the SQL statement that generated T2 is:
when it is desired to view the results of iteration T2, the following SQL statement may be used:
select Proj from tbl where locator in(select locator from T2)
aiming at each stage of query intermediate results in the two iteration processes, the data query method based on the Locator and the logic query condition is introduced to realize data query, effectively improve the storage space required by the query intermediate results, and ensure the working efficiency of the data query while ensuring the integrity of the iteration query intermediate results.
The embodiments of the present invention have been described in detail with reference to the drawings, but the present invention is not limited to the above embodiments, and various changes can be made within the knowledge of those skilled in the art without departing from the gist of the present invention.
Claims (3)
1. A data query method based on a locator and a logic query condition is characterized in that: firstly, acquiring query conditions corresponding to all records in original data, and respectively using the query conditions as locators of all records; then, carrying out data query according to a target query statement aiming at the original data, wherein the following steps are carried out aiming at intermediate results of all levels of query until a query target result is obtained;
001, respectively obtaining the locator corresponding to each record in the intermediate result of the query, meanwhile, setting each designated attribute as the designated query attribute of the intermediate result of the query, and then entering the step 002;
002, packaging the original data, the locators corresponding to the records in the intermediate query result and the specified query attributes of the intermediate query result to form a virtual view corresponding to the intermediate query result, and then entering the step 003;
step 003, judging whether the intermediate result is the query target result, if so, obtaining each specified attribute of each query target record from the original data in the query statement according to the locator and the specified query attribute in the virtual view corresponding to the intermediate result as the query target result aiming at the virtual view corresponding to the intermediate result, and finishing the data query; otherwise, go to step 004;
step 004, aiming at the virtual view corresponding to the query intermediate result, obtaining each record in the query intermediate result from the original data in the query intermediate result through a query statement according to the locator and the specified query attribute, and entering step 005;
and 005, further inquiring each record in the intermediate inquiry result to obtain the intermediate inquiry result of the next stage, and returning to the step 001.
2. The data query method according to claim 1, wherein the data query method comprises the following steps: the step 001 further comprises the following steps:
setting an intermediate result table aiming at the intermediate result of the query, setting the maximum record number of the intermediate result table as M, then judging whether the record number in the intermediate result of the query is greater than M, if so, randomly acquiring M records from the intermediate result and storing the M records in the intermediate result table; otherwise, storing each record in the intermediate result table.
3. The data query method according to claim 1, wherein the data query method comprises the following steps: the target query statement and the query statement are SQL query statements.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201710260143.XA CN107193874B (en) | 2017-04-20 | 2017-04-20 | Data query method based on locator and logic query condition |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201710260143.XA CN107193874B (en) | 2017-04-20 | 2017-04-20 | Data query method based on locator and logic query condition |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN107193874A CN107193874A (en) | 2017-09-22 |
| CN107193874B true CN107193874B (en) | 2020-06-16 |
Family
ID=59871117
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201710260143.XA Active CN107193874B (en) | 2017-04-20 | 2017-04-20 | Data query method based on locator and logic query condition |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN107193874B (en) |
Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US6356888B1 (en) * | 1999-06-18 | 2002-03-12 | International Business Machines Corporation | Utilize encoded vector indexes for distinct processing |
| CN101089851A (en) * | 2007-07-12 | 2007-12-19 | 复旦大学 | XML flow buffer store manage method based on partial binary prefix code |
| CN101216840A (en) * | 2008-01-21 | 2008-07-09 | 金蝶软件（中国）有限公司 | Data enquiry method and data enquiry system |
| CN103049473A (en) * | 2012-10-15 | 2013-04-17 | 新浪技术（中国）有限公司 | Data querying method and device |
| CN104756101A (en) * | 2012-10-31 | 2015-07-01 | 惠普发展公司，有限责任合伙企业 | Executing a query having multiple set operators |
-
2017
- 2017-04-20 CN CN201710260143.XA patent/CN107193874B/en active Active
Patent Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US6356888B1 (en) * | 1999-06-18 | 2002-03-12 | International Business Machines Corporation | Utilize encoded vector indexes for distinct processing |
| CN101089851A (en) * | 2007-07-12 | 2007-12-19 | 复旦大学 | XML flow buffer store manage method based on partial binary prefix code |
| CN101216840A (en) * | 2008-01-21 | 2008-07-09 | 金蝶软件（中国）有限公司 | Data enquiry method and data enquiry system |
| CN103049473A (en) * | 2012-10-15 | 2013-04-17 | 新浪技术（中国）有限公司 | Data querying method and device |
| CN104756101A (en) * | 2012-10-31 | 2015-07-01 | 惠普发展公司，有限责任合伙企业 | Executing a query having multiple set operators |
Also Published As
| Publication number | Publication date |
|---|---|
| CN107193874A (en) | 2017-09-22 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US11893022B2 (en) | Computer-implemented method for improving query execution in relational databases normalized at level 4 and above | |
| US10866971B2 (en) | Hash collision tables for relational operations | |
| EP2885730B1 (en) | Hardware implementation of the aggregation/group by operation: filter method | |
| CN106528787B (en) | query method and device based on multidimensional analysis of mass data | |
| CN101908075A (en) | SQL-based parallel computing system and method | |
| CN104021161A (en) | Cluster storage method and device | |
| CN109791543B (en) | Control method for executing multi-table connection operation and corresponding device | |
| CN103294702A (en) | Data processing method, device and system | |
| CN108255855B (en) | Data storage method and device | |
| CN110399395B (en) | Pre-calculation-based accelerated query method and storage medium | |
| CN104685499A (en) | Hardware implementation of the filter/project operations | |
| CN103744913A (en) | Database retrieval method based on search engine technology | |
| CN103455560A (en) | Data query method and system | |
| CN108549688B (en) | Data operation optimization method, device, equipment and storage medium | |
| CN107291770A (en) | The querying method and device of mass data in a kind of distributed system | |
| CN101916281B (en) | Concurrent computational system and non-repetition counting method | |
| CN104391923A (en) | Data set query method and apparatus | |
| WO2016038749A1 (en) | A method for efficient one-to-one join | |
| CN102915344A (en) | SQL (structured query language) statement processing method and device | |
| US20160275146A1 (en) | Use a parallel hardware search device to implement big databases efficiently | |
| CN109800228B (en) | Method for efficiently and quickly solving hash conflict | |
| CN110888861A (en) | Novel big data storage method | |
| CN107193874B (en) | Data query method based on locator and logic query condition | |
| CN107491544B (en) | Data processing platform for enhancing analysis capability of non-relational database | |
| CN107145522B (en) | Database data content comparison method |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant | ||
| TR01 | Transfer of patent right | ||
| TR01 | Transfer of patent right |
Effective date of registration: 20200730 Address after: 12 / F, building a, Xingzhi science and Technology Park, Xingzhi Road, Nanjing Economic and Technological Development Zone, Jiangsu Province 210000 Patentee after: Jiangsu weichi Digital Technology Co.,Ltd. Address before: Yudaojie Qinhuai District of Nanjing City, Jiangsu Province, No. 29 210000 Patentee before: Nanjing University of Aeronautics and Astronautics |
|
| PE01 | Entry into force of the registration of the contract for pledge of patent right |
Denomination of invention: A data query method based on locators and logical query conditions Effective date of registration: 20201010 Granted publication date: 20200616 Pledgee: Jiangsu Zijin rural commercial bank Limited by Share Ltd. branch of science and technology Pledgor: Jiangsu weichi Digital Technology Co.,Ltd. Registration number: Y2020980006707 |
|
| PE01 | Entry into force of the registration of the contract for pledge of patent right | ||
| PC01 | Cancellation of the registration of the contract for pledge of patent right | ||
| PC01 | Cancellation of the registration of the contract for pledge of patent right |
Date of cancellation: 20210922 Granted publication date: 20200616 Pledgee: Jiangsu Zijin rural commercial bank Limited by Share Ltd. branch of science and technology Pledgor: Jiangsu weichi Digital Technology Co.,Ltd. Registration number: Y2020980006707 |
|
| PE01 | Entry into force of the registration of the contract for pledge of patent right | ||
| PE01 | Entry into force of the registration of the contract for pledge of patent right |
Denomination of invention: A data query method based on locator and logical query conditions Effective date of registration: 20210924 Granted publication date: 20200616 Pledgee: Jiangsu Zijin Rural Commercial Bank Co.,Ltd. Gulou sub branch Pledgor: Jiangsu weichi Digital Technology Co.,Ltd. Registration number: Y2021320000243 |
|
| PC01 | Cancellation of the registration of the contract for pledge of patent right | ||
| PC01 | Cancellation of the registration of the contract for pledge of patent right |
Date of cancellation: 20220812 Granted publication date: 20200616 Pledgee: Jiangsu Zijin Rural Commercial Bank Co.,Ltd. Gulou sub branch Pledgor: Jiangsu weichi Digital Technology Co.,Ltd. Registration number: Y2021320000243 |
|
| PE01 | Entry into force of the registration of the contract for pledge of patent right | ||
| PE01 | Entry into force of the registration of the contract for pledge of patent right |
Denomination of invention: A Data Query Method Based on Locator and Logical Query Conditions Effective date of registration: 20220818 Granted publication date: 20200616 Pledgee: Jiangsu Zijin Rural Commercial Bank Co.,Ltd. Gulou sub branch Pledgor: Jiangsu weichi Digital Technology Co.,Ltd. Registration number: Y2022320000475 |