CN115033575A - Data query method, device, equipment and storage medium - Google Patents
Data query method, device, equipment and storage medium Download PDFInfo
- Publication number
- CN115033575A CN115033575A CN202210778189.1A CN202210778189A CN115033575A CN 115033575 A CN115033575 A CN 115033575A CN 202210778189 A CN202210778189 A CN 202210778189A CN 115033575 A CN115033575 A CN 115033575A
- Authority
- CN
- China
- Prior art keywords
- data
- query
- target
- business
- field
- 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.)
- Pending
Links
- 238000000034 method Methods 0.000 title claims abstract description 54
- 238000003860 storage Methods 0.000 title claims abstract description 19
- 238000012545 processing Methods 0.000 claims description 16
- 238000004590 computer program Methods 0.000 claims description 11
- 238000013507 mapping Methods 0.000 claims description 10
- 238000000605 extraction Methods 0.000 claims description 6
- 238000006243 chemical reaction Methods 0.000 claims description 5
- 230000003993 interaction Effects 0.000 claims description 4
- 238000004140 cleaning Methods 0.000 claims description 3
- 238000011161 development Methods 0.000 description 16
- 230000008569 process Effects 0.000 description 10
- 238000010586 diagram Methods 0.000 description 8
- 238000004891 communication Methods 0.000 description 7
- 238000012360 testing method Methods 0.000 description 7
- 238000001914 filtration Methods 0.000 description 6
- 238000005457 optimization Methods 0.000 description 5
- 230000004044 response Effects 0.000 description 5
- 230000001360 synchronised effect Effects 0.000 description 5
- 230000009471 action Effects 0.000 description 4
- 238000004364 calculation method Methods 0.000 description 4
- 238000012216 screening Methods 0.000 description 4
- 238000013461 design Methods 0.000 description 3
- 230000006872 improvement Effects 0.000 description 3
- 238000012423 maintenance Methods 0.000 description 3
- 238000010276 construction Methods 0.000 description 2
- 230000002354 daily effect Effects 0.000 description 2
- 238000003032 molecular docking Methods 0.000 description 2
- 238000012544 monitoring process Methods 0.000 description 2
- 238000005070 sampling Methods 0.000 description 2
- 230000002159 abnormal effect Effects 0.000 description 1
- 230000009286 beneficial effect Effects 0.000 description 1
- 230000005540 biological transmission Effects 0.000 description 1
- 238000013079 data visualisation Methods 0.000 description 1
- 230000007547 defect Effects 0.000 description 1
- 230000000694 effects Effects 0.000 description 1
- 238000005516 engineering process Methods 0.000 description 1
- 230000003203 everyday effect Effects 0.000 description 1
- 230000010354 integration Effects 0.000 description 1
- 210000001503 joint Anatomy 0.000 description 1
- 238000007726 management method Methods 0.000 description 1
- 230000003287 optical effect Effects 0.000 description 1
- 238000007781 pre-processing Methods 0.000 description 1
- 230000000750 progressive effect Effects 0.000 description 1
- 238000004904 shortening Methods 0.000 description 1
- 230000001052 transient effect Effects 0.000 description 1
- 230000000007 visual effect Effects 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/22—Indexing; Data structures therefor; Storage structures
- G06F16/2282—Tablespace storage structures; Management thereof
-
- 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/2455—Query execution
-
- 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/28—Databases characterised by their database models, e.g. relational or object models
- G06F16/283—Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Databases & Information Systems (AREA)
- Data Mining & Analysis (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Software Systems (AREA)
- Computational Linguistics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The application relates to the technical field of computers, and discloses a data query method, a device, equipment and a storage medium, which comprises the following steps: acquiring a plurality of service data tables with incidence relations from a database of a service system, and storing the plurality of service data tables into a data warehouse; determining target fields from fields corresponding to a plurality of business data tables stored in the data warehouse according to query requirements, and constructing a target width table containing the target fields and corresponding field data in a database of a statistical system; wherein, the target field at least comprises a field corresponding to the query data; and acquiring a query instruction and querying the target wide table according to the query instruction to obtain the query data corresponding to the query instruction. The method and the device can avoid the problem that query logic simultaneously relates to a plurality of data tables so that query complexity is high to cause query timeout, simplify the query logic and further improve data query efficiency.
Description
Technical Field
The present invention relates to the field of computer technologies, and in particular, to a data query method, apparatus, device, and storage medium.
Background
At present, Data query of a revenue billboard is based on a Data warehouse directly, a foreground and a background are connected through a Data API, Data service is provided by using the API, the specific flow of the method is as shown in fig. 1, query statements corresponding to query terms are written in the Data API, and then desired Data are acquired from the Data warehouse by using an interface calling mode, so as to be provided to the foreground. When a user searches on a page, the front end provides the query parameters when calling the API, and then the query parameters are supplemented into a Data API query statement through the processing of the rear end, so that a desired query result is obtained.
The above-mentioned flow has the following problems: 1) the query logic is complex, and the problem of interface response timeout occurs. Often, a plurality of tables are needed to be associated for inquiring one item of data in the revenue watching board, the inquiry condition is too many, the inquiry speed is greatly reduced, and the interface response time often exceeds 10 seconds, so that the user experience is very poor;
2) the docking and maintenance costs are high. Because the query logic is complex, the query statement in the Data API is generally longer, the action is the query statement of dozens of lines, and the code is relatively complex to write. Each iterative optimization of the revenue board requirements requires significant time-consuming undertaking of the project development to become familiar with how to work. As a company's internal operation, when they find that the earnings counted on the earning signboard are inconsistent with the accumulated earnings estimated by themselves, they expect to inquire which one is wrong in particular. Generally, the operation has little knowledge about Data API tools, and if the query statement is too complex, it also needs to consume certain learning cost of operation and communication cost with the test and development, which is time-consuming and labor-consuming. In addition, the inquired sentences are stored in a Data API development tool and need to be maintained manually at regular intervals, and once the inquired sentences are used by others, the inquiry result can be influenced; 3) the system stability is not enough, and the problem is difficult to be checked. Based on the business advancing current situation of each business department, the earning amount of each business and the stability of the system are insufficient in the peak period of the transaction, and the condition that earning billboard data are abnormal can occur. Problems are not easy to be checked, and a large amount of time is consumed for checking data.
Therefore, the above technical problems need to be solved by those skilled in the art.
Disclosure of Invention
In view of this, an object of the present invention is to provide a data query method, apparatus, device and storage medium, which can avoid the problem that query logic relates to multiple data tables simultaneously, so that query complexity is high and query timeout occurs, simplify the query logic and further improve data query efficiency. The specific scheme is as follows:
a first aspect of the present application provides a data query method, including:
acquiring a plurality of service data tables with incidence relations from a database of a service system, and storing the plurality of service data tables into a data warehouse;
determining target fields from fields corresponding to a plurality of business data tables stored in the data warehouse according to query requirements, and constructing a target width table containing the target fields and corresponding field data in a database of a statistical system; the target field at least comprises a field corresponding to the query data;
and acquiring a query instruction and querying the target wide table according to the query instruction to obtain the query data corresponding to the query instruction.
Optionally, after obtaining a plurality of service data tables with association relationships from a database of the service system, the method further includes:
and sequentially carrying out extraction processing, cleaning processing and conversion processing on the data in the plurality of business data tables so as to store the finally processed plurality of business data tables into the data warehouse.
Optionally, the obtaining a plurality of service data tables with an association relationship from a database of a service system, and storing the plurality of service data tables in a data warehouse includes:
and synchronizing the plurality of business data tables with the incidence relation in the database of the business system to the data warehouse according to a first preset synchronization period by using a data synchronization tool.
Optionally, after the storing the plurality of business data tables in the data warehouse, the method further includes:
integrating and calculating data in the data tables according to configuration logic stored in a data table format to obtain integrated field data;
correspondingly, the constructing a target width table containing the target fields and the corresponding field data in the database of the statistical system includes:
and constructing the target wide table containing the target fields and the corresponding integrated field data in a database of the statistical system.
Optionally, the constructing the target width table containing the target fields and the corresponding integrated field data in the database of the statistical system includes:
constructing the target width table containing the target field in a database of the statistical system;
and updating the integrated field data corresponding to the target field in the data warehouse in the target wide table in a mode of setting and running a timing task.
Optionally, after the query instruction is obtained, the method further includes:
acquiring a trigger account of the query instruction; the triggering account is a first department account currently logged in the statistical system;
determining a second department account corresponding to the trigger account in the service system, and inquiring the field data related to the second department account in the target broad list according to the inquiry instruction to obtain the inquiry data corresponding to the inquiry instruction;
and outputting the query data corresponding to the query instruction to a manual interaction interface of a revenue billboard for display.
Optionally, the data query method further includes:
and constructing a mapping relation between the first department account number in the statistical system and the second department account number in the business system so as to determine the second department account number corresponding to the trigger account number in the business system according to the mapping relation.
A second aspect of the present application provides a data query apparatus including:
the acquisition and storage module is used for acquiring a plurality of business data tables with incidence relations from a database of a business system and storing the business data tables into a data warehouse;
the determining and constructing module is used for determining a target field from fields corresponding to a plurality of business data tables stored in the data warehouse according to query requirements, and constructing a target width table containing the target field and corresponding field data in a database of a statistical system; wherein, the target field at least comprises a field corresponding to the query data;
and the acquisition query module is used for acquiring a query instruction and querying the target wide table according to the query instruction to obtain the query data corresponding to the query instruction.
A third aspect of the application provides an electronic device comprising a processor and a memory; wherein the memory is used for storing a computer program which is loaded and executed by the processor to implement the aforementioned data query method.
A fourth aspect of the present application provides a computer-readable storage medium, in which computer-executable instructions are stored, and when the computer-executable instructions are loaded and executed by a processor, the foregoing data query method is implemented.
In the application, a plurality of business data tables with incidence relations are obtained from a database of a business system, and the business data tables are stored in a data warehouse; then, determining target fields from fields corresponding to a plurality of business data tables stored in the data warehouse according to query requirements, and constructing a target width table containing the target fields and corresponding field data in a database of a statistical system; wherein, the target field at least comprises a field corresponding to the query data; and finally, acquiring a query instruction and querying the target wide table according to the query instruction to obtain the query data corresponding to the query instruction. Therefore, the plurality of business data tables in the business system database are uniformly stored in the data warehouse, and due to the fact that the plurality of business data tables have the association relation, when the query is carried out, the query logic may simultaneously relate to the plurality of data tables, and the query logic is complex. Therefore, fields of a plurality of service data tables in a data warehouse are taken as extraction bases, the fields are extracted as required, a new wide table is constructed in a statistical system by utilizing the extracted fields and field data, most of query logic only relates to the newly constructed wide table when query is carried out, the problem that query logic simultaneously relates to a plurality of data tables so that query complexity is high and query overtime is generated can be avoided, the query logic is simplified, and data query efficiency is improved.
Drawings
In order to more clearly illustrate the embodiments of the present invention 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 embodiments of the present invention, and for those skilled in the art, other drawings can be obtained according to the provided drawings without creative efforts.
FIG. 1 is a schematic diagram of a method for querying data through an API provided in the present application;
FIG. 2 is a flowchart of a data query method provided in the present application;
FIG. 3 is a diagram illustrating a specific data query method provided herein;
FIG. 4 is a schematic diagram of a statistical sampling order paid amount query method provided by the present application;
FIG. 5 is a schematic structural diagram of a data query device provided in the present application;
fig. 6 is a block diagram of a data query electronic device according to the present application.
Detailed Description
The technical solutions in the embodiments of the present invention will be clearly and completely described below with reference to the drawings in the embodiments of the present invention, and it is obvious that the described embodiments are only a part of the embodiments of the present invention, 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 invention.
In the prior art, a foreground and a background are connected through a Data API, and a scheme for providing Data service by using the API mode has the problems of complicated query logic and interface response timeout; the butt joint and maintenance cost is high; the system stability is not enough, and the problems are not easy to be checked. In view of the technical defects, the application provides a data query scheme, which can avoid the problem that query logic simultaneously relates to a plurality of data tables so that query complexity is high to cause query timeout, simplify the query logic and further improve data query efficiency.
Fig. 2 is a flowchart of a data query method according to an embodiment of the present application. Referring to fig. 2, the data query method includes:
s11: and acquiring a plurality of service data tables with incidence relations from a database of a service system, and storing the plurality of service data tables into a data warehouse.
In this embodiment, the data of each service constitutes a service system data source, that is, a database of the service system, and the user transaction is in each service system, and transaction data generated in the transaction process is stored in the database of each service system, which constitutes a data source of the service system. Firstly, a plurality of business data tables with incidence relation are obtained from a database of a business system, and the business data tables are stored in a data warehouse. It is understood that a Data Warehouse (DW or DWH) can be abbreviated as a strategic set that provides all types of Data support for all levels of decision making processes of an enterprise. It is a single data store created for analytical reporting and decision support purposes. And providing guidance for business process improvement, monitoring time, cost, quality and control for enterprises needing business intelligence.
Further, the data synchronization tool may be used to synchronize the plurality of business data tables having the association relationship in the database of the business system into the data warehouse according to a first preset synchronization period. The data synchronization tool can trigger the data synchronization to the data warehouse through a timing task, and the query is in a form of 'T + 1' day, namely, today, only data up to yesterday can be found. Before synchronization, that is, after acquiring a plurality of service data tables having an association relationship from a database of a service system, data in the plurality of service data tables needs to be sequentially extracted, cleaned, and converted, so that the finally processed plurality of service data tables are stored in the data warehouse. For example, the plurality of service data tables (service order data, configuration data of the charging system, etc.) are: the tables 1, 2, 3 and 4 are extracted, cleaned and converted by a data synchronization tool, and then loaded to a data warehouse for storage. As shown in particular in figure 3.
S12: determining target fields from fields corresponding to a plurality of business data tables stored in the data warehouse according to query requirements, and constructing a target width table containing the target fields and corresponding field data in a database of a statistical system; wherein, the target field at least comprises a field corresponding to the query data.
In this embodiment, after a plurality of service data tables are stored in a data warehouse, a target field is determined from fields corresponding to the plurality of service data tables stored in the data warehouse according to a query requirement. The target field at least comprises a field corresponding to the query data. And then constructing a target width table containing the target fields and the corresponding field data in a database of a statistical system. A wide table is a database table with many fields in a literal sense. Generally refers to a data table which is related to indexes, attributes and maintenance of business topics and is associated together. The method reduces table association by placing related fields in a plurality of tables in the same table, and can improve the efficiency of data calculation. In short, the space is changed in time, so that iteration is facilitated, the table association quantity is reduced, and a plurality of tables are not required to be changed when a small amount of data is modified.
In this embodiment, a configuration logic is stored in the data warehouse, and further, the data in the data tables is integrated and calculated according to the configuration logic stored in a data table format, so as to obtain integrated field data. The newly-built target wide table is mainly used for storing integrated statistical fields, the fields used for statistics are all placed in the same table, and finally data statistics query is directly carried out through the wide table, so that the table association quantity is reduced, and the data query statistical efficiency is improved. And constructing the target wide table containing the target fields and the corresponding integrated field data in a database of the statistical system. Specifically, the target wide table including the target field is first constructed in the database of the statistical system, and then the integrated field data corresponding to the target field in the data warehouse is updated in the target wide table by setting and running a timing task.
For example, after data warehouse tasks such as daily are synchronized, fields 1, 2, 3, 4, 5 required for statistics are extracted from multiple service tables and authority tables such as table1, table2, table3, table4 and the like in a form of timing tasks, and a temporary wide table5 is formed by using fields required for query such as field 1, field 2, field 3, field 4, field 5 and the like. Data statistics requires which fields to use, which fields are synchronized from each table to the temporary wide table5 at 00:00 points per day in the form of a timing task, so that all fields used by our data query are already in the wide table. As for the index of the wide table, it is only necessary to create it according to the actual service needs. In addition, data updates may be made daily, with the timing tasks synchronizing the changing data updates in the data warehouse to the wide table5 every 2:00 a.m. Because table5 already contains all the fields needed by the statistical data, the data system query can directly filter all the conditions through the temporary wide table and query all the needed fields, thus greatly reducing the table association number, greatly reducing the database pressure, accelerating the query speed and improving the query performance.
S13: and acquiring a query instruction and querying the target wide table according to the query instruction to obtain the query data corresponding to the query instruction.
In this embodiment, when performing screening query on a page, a query instruction is obtained and the target wide table is queried according to the query instruction, so as to obtain the query data corresponding to the query instruction. The data query process can be commonly used for all statistical schemes, can improve the data query efficiency, has universality and is beneficial to secondary development. In most cases, statistical data can not be obtained in only one table, and for the query with complex logic, fields in multiple tables can be extracted and integrated into a new wide table for query according to needs, so that data association query is reduced, SQL query statements are simplified, and query efficiency is improved. Generally, data statistics has some generality, such as statistics by time, statistics by region and department, and secondary development statistics can also be performed based on a wide statistical table extracted by the set of processes. The method has the advantages that a small amount of data in the wide meter is modified, a plurality of meters do not need to be changed, and time and labor can be saved during development.
In the embodiment, the query result is displayed on the revenue billboard after the query result is obtained, the revenue billboard counts all revenue data of the company in a more visual and vivid manner through a data visualization tool, and the method is also a management means capable of improving efficiency. For the sake of data security, the data of the revenue billboard is not disclosed for all people, but the data range can be seen differently according to different authorities. Therefore, when the screening query is performed on the page, the account number authority and other screening conditions need to be filtered to obtain the final desired statistical result. Specifically, a trigger account of the query instruction is obtained first; and the triggering account is a first department account which is logged in the statistical system at present. And then determining a second department account corresponding to the trigger account in the business system, and inquiring the field data related to the second department account in the target broad list according to the inquiry instruction to obtain the inquiry data corresponding to the inquiry instruction. Therefore, a mapping relationship between the first department account in the statistical system and the second department account in the business system needs to be constructed, so as to determine the second department account corresponding to the trigger account in the business system according to the mapping relationship. And finally, outputting the query data corresponding to the query instruction to a manual interaction interface of a revenue board for display.
The first department account number can be a nailing department account number (id), the second department account number can be a business department account number (id), each employee account number has a home department, and mapping filtering authority is communicated with a revenue department id in a bill through the nailing department id. For example, the order has a corresponding bill, the bill has revenue departments, the revenue department id in the bill is mapped through the nail department id of the login account, and the corresponding order is filtered through the bill under the revenue department, so that the purpose of obtaining the required order statistical data through the department authority filtering of the account is achieved. Therefore, the earning data of each employee and the earning data of each department can be counted conveniently. Taking an interface for counting paid amount of a commission order in the prior art as an example, the interface before optimization frequently has the condition that the response time exceeds 10 seconds, the page statistical data is not coming late, and a user often hears to spit the slot. When the concurrency is 5, the utilization rate of the CPU of the database reaches more than 90 percent. Thus, with a slight application of pressure, the system collapses. After optimization, the interface is subjected to pressure test, the average response time of the interface can be generally controlled to be about 1 second, the database pressure is reduced, the utilization rate of a database CPU (Central processing Unit) can be controlled to be below 60%, the optimal concurrency number of the interface is improved to 30, and the improvement of the performance of the interface is remarkable.
Therefore, in the embodiment of the application, a plurality of service data tables with association relations are obtained from a database of a service system, and the plurality of service data tables are stored in a data warehouse; then, determining target fields from fields corresponding to a plurality of business data tables stored in the data warehouse according to query requirements, and constructing a target width table containing the target fields and corresponding field data in a database of a statistical system; wherein, the target field at least comprises a field corresponding to the query data; and finally, acquiring a query instruction and querying the target wide table according to the query instruction to obtain the query data corresponding to the query instruction. According to the embodiment of the application, the plurality of business data tables in the business system database are uniformly stored in the data warehouse, and due to the fact that the plurality of business data tables have the incidence relation, when the query is carried out, the query logic may simultaneously relate to the plurality of data tables, and the query logic is complex. Therefore, fields of a plurality of service data tables in a data warehouse are taken as extraction bases, the fields are extracted as required, a new wide table is constructed in a statistical system by utilizing the extracted fields and field data, most of query logic only relates to the newly constructed wide table when query is carried out, the problem that query logic simultaneously relates to a plurality of data tables so that query complexity is high and query overtime is generated can be avoided, the query logic is simplified, and data query efficiency is improved.
The above process is specifically described below by taking a statistical sampling order paid amount query as an example, as shown in fig. 4.
First, the business system generates transaction data based on the transaction, the data related to the transaction are stored in different databases and form a data source of the business system, and the data originally stored in the databases are not processed by logical operation. Due to special and system performance factors of a business scene, logic of the calculation is abstracted to a big data platform for processing. This requires that the table data be synchronized to the data warehouse by the timing task in advance for further calculation. And then synchronizing a plurality of tables of table1, table2, table3, table4, table5 and the like in the original database to one part of the database of the data warehouse. From these tables, the fields required for statistics are then extracted, such as bill type, bill status, amount charged, total amount, charging policy, payee (company), revenue department, region to which the bill belongs, etc. And establishing a new temporary wide table data new table by using the extracted fields, and updating and synchronizing the changed data in the data warehouse into the new table every day through a timing task. Therefore, data which needs to be associated with multi-table query originally can be queried only through one single table, the database pressure is greatly reduced, and the data query speed is accelerated.
Often, in consideration of company data security, the data of the revenue billboard is not disclosed for all persons, but the data range can be seen differently according to different authorities. The data of the revenue watching board has the filtering of the account number authority and the filtering of the service data authority. The filtering of the account number authority is realized by the mapping between the nail department and the bill revenue department. The corresponding revenue department id can be found through each nailing department id. The order is in the bill, and the bill has the revenue department, so that viewable order data can be obtained through the department authority filtering of the account number. And the page is inquired under the screening condition, the rear end returns the paid amount of the final obtained order in the interface through logic operation and processing, and the paid amount is displayed through the front end.
The above process, the first aspect, simplifies the query and reduces the cost of docking learning. In the original scheme, query sentences are stored in a DATA API development tool, the query sentences on the DATA API development tool are different from sentences directly queried from a database, the query SQL related database table is too many, the sentences are too complex, and a certain learning cost is consumed for testing and developing a new catcher. The new scheme realizes the conversion from multi-table correlation query to single-table query, the SQL sentences are simpler, the query efficiency is improved, the novice catcher service is easier to get on hand, and the learning time cost is reduced. Practice proves that the original scheme is adopted in the past, development and test at least need 0.5 day to be familiar with the original design, and after the new scheme is adopted, development and test can be completed only within 0.5 day after being added, so that the time cost is saved.
And in the second aspect, the query efficiency is improved, and the user experience and the system performance are improved. The original scheme directly queries from a DATA warehouse by calling a DATA API, is limited by the DATA warehouse, and often has the problems of interface overtime, slow SQL and the like. The new scheme is combined with a data warehouse to perform optimization processing, data of dozens of tables originally are uniformly integrated in one temporary wide table by preprocessing the data, the conversion from multi-table association query to single-table query is realized, query SQL sentences are simplified, and the query efficiency is improved. The slow SQL and interface overtime phenomena are solved, the user experience is improved, and the system performance improvement effect is obvious.
And in the third aspect, the fault tolerance rate of the system is improved, and the service expansion support is smoother. Due to the continuous expansion of the service, if some contents are added or deleted aiming at the revenue board, whether the field needs to be added in the temporary wide table or not is judged according to the counted field, and then the query SQL is updated. The SQL of the new scheme is simpler and easier to modify. And if the data in the data warehouse is lost for some reasons, the data to be used for statistics is synchronized into the wide table through the timing task before the data is lost in the temporary wide table, so that the whole billboard cannot be used. Meanwhile, system monitoring alarm can be added, and the calculation is pulled again aiming at the data which is lost and not synchronized.
And in the fourth aspect, the universality is improved, the secondary development cost is reduced, and the project lead time is shortened. In most cases, the statistics are not available in only one table. For the query with similar complicated logic, the fields in the multiple tables can be extracted and integrated into a new wide table for query according to the requirement, so that the query efficiency is improved, and the method is suitable for the data statistics query of each service. In addition, secondary statistics can be performed on the basis of the statistics broad table extracted by the set of flow, for example, statistics according to time, statistics according to regions and departments and the like, so that different types of statistics can be conveniently and upwards compatible. The development design of the original scheme for each data statistics billboard needs 1 day, the development is carried out for 5 days, and then the data test needs at least 3 days; the new scheme can be changed on the basis of the original wide table, and only a small number of data fields need to be modified without changing multiple tables according to the iteration requirement. The development design is hardly changed, the development only needs 1 day, and the test only needs 2 days to complete. The original delivery needs 9 days, and only 3 days after optimization, thereby greatly reducing the time cost of secondary development and shortening the delivery cycle.
Referring to fig. 5, an embodiment of the present application further discloses a data query apparatus, which includes:
the acquisition and storage module 11 is configured to acquire a plurality of service data tables having an association relationship from a database of a service system, and store the plurality of service data tables in a data warehouse;
a determining and constructing module 12, configured to determine a target field from fields corresponding to the multiple service data tables stored in the data warehouse according to a query requirement, and construct a target width table including the target field and data of the corresponding field in a database of a statistical system; wherein, the target field at least comprises a field corresponding to the query data;
and the obtaining and inquiring module 13 is configured to obtain an inquiry instruction and inquire the target wide table according to the inquiry instruction to obtain the inquiry data corresponding to the inquiry instruction.
Therefore, in the embodiment of the application, a plurality of service data tables with association relations are obtained from a database of a service system, and the plurality of service data tables are stored in a data warehouse; then, determining target fields from fields corresponding to a plurality of business data tables stored in the data warehouse according to query requirements, and constructing a target width table containing the target fields and corresponding field data in a database of a statistical system; the target field at least comprises a field corresponding to the query data; and finally, acquiring a query instruction and querying the target wide table according to the query instruction to obtain the query data corresponding to the query instruction. According to the embodiment of the application, the plurality of business data tables in the business system database are uniformly stored in the data warehouse, and due to the fact that the plurality of business data tables have the incidence relation, when the query is carried out, the query logic may simultaneously relate to the plurality of data tables, and the query logic is complex. Therefore, fields of a plurality of service data tables in a data warehouse are taken as extraction bases, the fields are extracted as required, a new wide table is constructed in a statistical system by utilizing the extracted fields and field data, most of query logic only relates to the newly constructed wide table when query is carried out, the problem that query logic simultaneously relates to a plurality of data tables so that query complexity is high and query overtime is generated can be avoided, the query logic is simplified, and data query efficiency is improved.
In some specific embodiments, the obtaining storage module 11 is specifically configured to synchronize, by using a data synchronization tool, the service data tables having an association relationship in the database of the service system into the data warehouse according to a first preset synchronization period.
In some embodiments, the data query device further includes:
the processing module is used for sequentially carrying out extraction processing, cleaning processing and conversion processing on the data in the plurality of business data tables so as to store the finally processed plurality of business data tables into the data warehouse;
the integration module is used for integrating and calculating the data in the data tables according to configuration logic stored in a data table format to obtain integrated field data;
in some specific embodiments, the determining and constructing module 12 specifically includes:
the determining unit is used for determining a target field from fields corresponding to a plurality of business data tables stored in the data warehouse according to query requirements;
and the construction unit is used for constructing the target wide table containing the target fields and the corresponding integrated field data in a database of the statistical system.
In some specific embodiments, the building unit is specifically configured to build the target width table including the target field in a database of the statistical system, and update the integrated field data corresponding to the target field in the data warehouse in the target width table by setting and running a timing task.
In some embodiments, the data query apparatus further includes:
the account number acquisition module is used for acquiring a trigger account number of the query instruction; the triggering account is a first department account which is logged in the statistical system at present;
the account number determining module is used for determining a second department account number corresponding to the trigger account number in the business system, and inquiring the field data related to the second department account number in the target broad table according to the inquiry command to obtain the inquiry data corresponding to the inquiry command;
the output module is used for outputting the query data corresponding to the query instruction to a manual interaction interface of a revenue watching board for displaying;
and the relationship construction module is used for constructing a mapping relationship between the first department account number in the statistical system and the second department account number in the business system so as to determine the second department account number corresponding to the trigger account number in the business system according to the mapping relationship.
Further, the embodiment of the application also provides electronic equipment. FIG. 6 is a block diagram illustrating an electronic device 20 according to an exemplary embodiment, and the contents of the diagram should not be construed as limiting the scope of use of the present application in any way.
Fig. 6 is a schematic structural diagram of an electronic device 20 according to an embodiment of the present disclosure. The electronic device 20 may specifically include: at least one processor 21, at least one memory 22, a power supply 23, a communication interface 24, an input output interface 25, and a communication bus 26. Wherein, the memory 22 is used for storing a computer program, and the computer program is loaded and executed by the processor 21 to implement the relevant steps in the data query method disclosed in any of the foregoing embodiments.
In this embodiment, the power supply 23 is configured to provide a working voltage for each hardware device on the electronic device 20; the communication interface 24 can create a data transmission channel between the electronic device 20 and an external device, and a communication protocol followed by the communication interface is any communication protocol applicable to the technical solution of the present application, and is not specifically limited herein; the input/output interface 25 is configured to acquire external input data or output data to the outside, and a specific interface type thereof may be selected according to specific application requirements, which is not specifically limited herein.
In addition, the storage 22 is used as a carrier for resource storage, and may be a read-only memory, a random access memory, a magnetic disk or an optical disk, etc., and the resources stored thereon may include an operating system 221, a computer program 222, data 223, etc., and the storage may be a transient storage or a permanent storage.
The operating system 221 is used for managing and controlling each hardware device and the computer program 222 on the electronic device 20, so as to realize the operation and processing of the mass data 223 in the memory 22 by the processor 21, and may be Windows Server, Netware, Unix, Linux, and the like. The computer program 222 may further include a computer program that can be used to perform other specific tasks in addition to the computer program that can be used to perform the data query method disclosed in any of the foregoing embodiments and executed by the electronic device 20. Data 223 may include spreadsheet data collected by electronic device 20.
Further, an embodiment of the present application further discloses a storage medium, in which a computer program is stored, and when the computer program is loaded and executed by a processor, the steps of the data query method disclosed in any of the foregoing embodiments are implemented.
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 device disclosed by the embodiment corresponds to the method disclosed by the embodiment, so that the description is simple, and the relevant points can be referred to the method part for description.
Finally, it should also be noted that, herein, relational terms such as first and second, and the like may be used solely to distinguish one entity or action from another entity or action without necessarily requiring or implying any actual such relationship or order between such entities or actions. Also, the terms "comprises," "comprising," or any other variation thereof, are intended to cover a non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements does not include only those elements but may include other elements not expressly listed or inherent to such process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising an …" does not exclude the presence of other identical elements in a process, method, article, or apparatus that comprises the element.
The data query method, apparatus, device and storage medium provided by the present invention are described in detail above, and specific examples are applied herein to explain the principles and embodiments of the present invention, and the descriptions of the above embodiments are only used to help understanding the method and its core ideas of the present invention; meanwhile, for a person skilled in the art, according to the idea of the present invention, the specific embodiments and the application range may be changed, and in summary, the content of the present specification should not be construed as a limitation to the present invention.
Claims (10)
1. A method for querying data, comprising:
acquiring a plurality of service data tables with incidence relations from a database of a service system, and storing the plurality of service data tables into a data warehouse;
determining target fields from fields corresponding to a plurality of business data tables stored in the data warehouse according to query requirements, and constructing a target width table containing the target fields and corresponding field data in a database of a statistical system; wherein, the target field at least comprises a field corresponding to the query data;
and acquiring a query instruction and querying the target wide table according to the query instruction to obtain the query data corresponding to the query instruction.
2. The data query method of claim 1, after obtaining a plurality of business data tables with association relationships from a database of a business system, further comprising:
and sequentially carrying out extraction processing, cleaning processing and conversion processing on the data in the plurality of business data tables so as to store the finally processed plurality of business data tables into the data warehouse.
3. The data query method of claim 1, wherein the obtaining a plurality of business data tables with association relationships from a database of a business system and storing the plurality of business data tables in a data warehouse comprises:
and synchronizing a plurality of business data tables with incidence relations in a database of the business system to the data warehouse according to a first preset synchronization period by using a data synchronization tool.
4. The data query method of claim 3, after storing the plurality of business data tables in a data warehouse, further comprising:
integrating and calculating data in the data tables according to configuration logic stored in a data table format to obtain integrated field data;
correspondingly, the constructing a target width table containing the target fields and the corresponding field data in the database of the statistical system includes:
and constructing the target wide table containing the target fields and the corresponding integrated field data in a database of the statistical system.
5. The method according to claim 4, wherein said constructing the target broad table containing the target fields and the corresponding integrated field data in the database of the statistical system comprises:
constructing the target width table containing the target field in a database of the statistical system;
and updating the integrated field data corresponding to the target field in the data warehouse in the target wide table in a mode of setting and running a timing task.
6. The data query method according to any one of claims 1 to 5, wherein after the obtaining the query instruction, the method further comprises:
acquiring a trigger account of the query instruction; the triggering account is a first department account which is logged in the statistical system at present;
determining a second department account corresponding to the trigger account in the service system, and inquiring the field data related to the second department account in the target broad list according to the inquiry instruction to obtain the inquiry data corresponding to the inquiry instruction;
and outputting the query data corresponding to the query instruction to a manual interaction interface of a revenue billboard for display.
7. The data query method of claim 6, further comprising:
and constructing a mapping relation between the first department account number in the statistical system and the second department account number in the business system so as to determine the second department account number corresponding to the trigger account number in the business system according to the mapping relation.
8. A data query apparatus, comprising:
the acquisition and storage module is used for acquiring a plurality of business data tables with incidence relations from a database of a business system and storing the business data tables into a data warehouse;
the determining and constructing module is used for determining a target field from fields corresponding to a plurality of business data tables stored in the data warehouse according to query requirements, and constructing a target width table containing the target field and corresponding field data in a database of a statistical system; wherein, the target field at least comprises a field corresponding to the query data;
and the acquisition query module is used for acquiring a query instruction and querying the target wide table according to the query instruction to obtain the query data corresponding to the query instruction.
9. An electronic device, comprising a processor and a memory; wherein the memory is for storing a computer program that is loaded and executed by the processor to implement the data query method of any one of claims 1 to 7.
10. A computer-readable storage medium storing computer-executable instructions which, when loaded and executed by a processor, implement the data query method of any one of claims 1 to 7.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202210778189.1A CN115033575A (en) | 2022-06-29 | 2022-06-29 | Data query method, device, equipment and storage medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202210778189.1A CN115033575A (en) | 2022-06-29 | 2022-06-29 | Data query method, device, equipment and storage medium |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN115033575A true CN115033575A (en) | 2022-09-09 |
Family
ID=83129865
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202210778189.1A Pending CN115033575A (en) | 2022-06-29 | 2022-06-29 | Data query method, device, equipment and storage medium |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN115033575A (en) |
Cited By (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN115407992A (en) * | 2022-10-31 | 2022-11-29 | 平安银行股份有限公司 | Configuration method and device of data query menu, electronic equipment and storage medium |
| CN115525722A (en) * | 2022-11-04 | 2022-12-27 | 杭州小电科技股份有限公司 | Wide table data synchronization method and device, electronic device and storage medium |
| CN115544096A (en) * | 2022-11-22 | 2022-12-30 | 深圳市东信时代信息技术有限公司 | Data query method and device, computer equipment and storage medium |
| CN116578754A (en) * | 2023-07-13 | 2023-08-11 | 杭州之数科技有限公司 | Enterprise data accurate query method and system |
| CN116610714A (en) * | 2023-07-14 | 2023-08-18 | 北京数巅科技有限公司 | Data query method, device, computer equipment and storage medium |
Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110597851A (en) * | 2019-09-20 | 2019-12-20 | 四川长虹电器股份有限公司 | Big data based data processing and report display method |
| CN111241185A (en) * | 2020-04-26 | 2020-06-05 | 浙江网商银行股份有限公司 | Data processing method and device |
| CN112989171A (en) * | 2021-03-26 | 2021-06-18 | 广东电网有限责任公司 | Data query method, device, equipment and medium |
| WO2021139426A1 (en) * | 2020-01-06 | 2021-07-15 | 平安科技（深圳）有限公司 | Method, device and apparatus for querying data in database, and storage medium |
-
2022
- 2022-06-29 CN CN202210778189.1A patent/CN115033575A/en active Pending
Patent Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110597851A (en) * | 2019-09-20 | 2019-12-20 | 四川长虹电器股份有限公司 | Big data based data processing and report display method |
| WO2021139426A1 (en) * | 2020-01-06 | 2021-07-15 | 平安科技（深圳）有限公司 | Method, device and apparatus for querying data in database, and storage medium |
| CN111241185A (en) * | 2020-04-26 | 2020-06-05 | 浙江网商银行股份有限公司 | Data processing method and device |
| CN112989171A (en) * | 2021-03-26 | 2021-06-18 | 广东电网有限责任公司 | Data query method, device, equipment and medium |
Cited By (9)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN115407992A (en) * | 2022-10-31 | 2022-11-29 | 平安银行股份有限公司 | Configuration method and device of data query menu, electronic equipment and storage medium |
| CN115407992B (en) * | 2022-10-31 | 2023-04-28 | 平安银行股份有限公司 | Configuration method and device of data query menu, electronic equipment and storage medium |
| CN115525722A (en) * | 2022-11-04 | 2022-12-27 | 杭州小电科技股份有限公司 | Wide table data synchronization method and device, electronic device and storage medium |
| CN115544096A (en) * | 2022-11-22 | 2022-12-30 | 深圳市东信时代信息技术有限公司 | Data query method and device, computer equipment and storage medium |
| CN115544096B (en) * | 2022-11-22 | 2023-03-28 | 深圳市东信时代信息技术有限公司 | Data query method and device, computer equipment and storage medium |
| CN116578754A (en) * | 2023-07-13 | 2023-08-11 | 杭州之数科技有限公司 | Enterprise data accurate query method and system |
| CN116578754B (en) * | 2023-07-13 | 2023-11-03 | 杭州之数科技有限公司 | Enterprise data accurate query method and system |
| CN116610714A (en) * | 2023-07-14 | 2023-08-18 | 北京数巅科技有限公司 | Data query method, device, computer equipment and storage medium |
| CN116610714B (en) * | 2023-07-14 | 2023-10-31 | 北京数巅科技有限公司 | Data query method, device, computer equipment and storage medium |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN115033575A (en) | Data query method, device, equipment and storage medium | |
| US10891297B2 (en) | Method and system for implementing collection-wise processing in a log analytics system | |
| US7664741B2 (en) | Historical data warehousing system | |
| EP2702510B1 (en) | Joining tables in a mapreduce procedure | |
| US9218408B2 (en) | Method for automatically creating a data mart by aggregated data extracted from a business intelligence server | |
| US10545957B1 (en) | Method and system for implementing a batch stored procedure testing tool | |
| US11238082B2 (en) | Text analysis of unstructured data | |
| CN111858596A (en) | Data acquisition method and device, computer equipment and storage medium | |
| US20180165339A1 (en) | Delta Replication | |
| CN112231385B (en) | Data collection method, device, equipment and storage medium | |
| US11144373B2 (en) | Data pipeline using a pluggable topology connecting components without altering code of the components | |
| US11106673B2 (en) | Query plan sharing | |
| US8495009B2 (en) | Incorporating temporal aspects of data in a relational storage system | |
| CN114238720B (en) | View analysis display method and system based on data linkage | |
| CN114996104A (en) | Data processing method and device | |
| CN114463100A (en) | Order data processing method, device, equipment and storage medium | |
| CN110413733B (en) | Code statistical method and device | |
| CN110704832B (en) | Judgment method for identifying uniqueness of entity account business place based on big data processing | |
| CN106055284B (en) | Enterprise-level printing management method and system | |
| US8160997B1 (en) | System, method and computer program product for managing aging data in a database schema | |
| CN110888928B (en) | Visual control method based on ETL tool service assembly | |
| CN114741412B (en) | User behavior self-help analysis system | |
| US11809496B2 (en) | Data access using sorted count mapping | |
| CN116346514B (en) | Metering and charging method for cloud service products | |
| CN112967026A (en) | Salary issuing method, device, server and storage medium |
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 |