A query method, device and medium based on index warehouse segmentation strategy

By dividing business types and configuring the metric warehouses in different databases and tables, and adopting the master-slave table structure, the problem of degradation in query performance of traditional metric warehouses is solved, efficient management and high-performance queries are realized, and suitable for large data volume and multi-dimensional indicators are suitable for large data volume and multi-dimensional indicators. scene.

CN118760703BActive Publication Date: 2024-11-15INSPUR GENERSOFT CO LTD
View PDF 0 Cites 0 Cited by

Patent Information

Application Number
CN202411237704.0
Authority / Receiving Office
CN · China
Patent Type
Patents(China)
Current Assignee / Owner
Filing Date
2024-09-05
Publication Date
2024-11-15
Estimated Expiration
2044-09-05

AI Technical Summary

Technical Problem

When the data volume and indicator volume increase, the query performance declines and it is difficult to carry more dimensional indicators, resulting in inefficient query efficiency and increased system resource consumption.

Method used

By dividing the indicator system based on business type information, the database and table configuration is carried out, and the master-slave table structure is adopted to move some indicator columns to the slave table. Combining multi-dimensional segmentation strategy and innovative query acceleration optimization methods, we can achieve efficient data management and high-performance query.

Benefits of technology

It reduces the amount of redundant data under a single system, makes the data more streamlined and efficient, facilitates analysis and decision-making support, ensures data integrity and consistency, and improves the query performance of the indicator warehouse.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN118760703B_ABST
    Figure CN118760703B_ABST
Patent Text Reader

Abstract

The present invention discloses a query method, device and medium based on an indicator warehouse segmentation strategy, which belongs to the field of data processing and analysis technology, and is used to solve the technical problem that the data storage and query performance of existing databases decreases greatly under the massive growth of indicator quantity and data quantity, and it is difficult to carry more dimensional indicators under the same system. The method includes: based on business type information, the indicator data is configured to obtain an exclusive indicator system; through the exclusive indicator system, the sub-library and sub-table of the database is configured with relevant demand judgment to obtain a sub-library and sub-table configuration plan; according to the sub-library and sub-table configuration plan, the data table is combined with the sub-table under the relevant dimensions and indicators to obtain a data configuration strategy with multiple related master-slave tables; the related master-slave tables in the data configuration strategy are added and deleted to determine a new data configuration strategy; the new data configuration strategy is queried for business demand information to obtain business query result information.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present application relates to the technical field of data processing and analysis, and in particular to a query method, device and medium based on an indicator warehouse segmentation strategy. Background Art

[0002] In the era of big data, enterprises and organizations are facing increasing demands and challenges for indicator warehouses. With the explosive growth of data volume, traditional indicator warehouse management methods have become difficult to cope with, especially when dealing with extremely large amounts of data, query performance often becomes a bottleneck.

[0003] Traditional indicator warehouses usually adopt a "big and complete" storage mode. Although this mode can store a large amount of data in a centralized manner, it leads to serious data redundancy. Data redundancy not only takes up a lot of storage space and increases storage costs, but also makes query operations complicated and inefficient. In addition, due to the complex relationships between different indicators, traditional indicator warehouse management methods often have difficulty in effectively handling these relationships, thus affecting the accuracy of the data and the effectiveness of analysis.

[0004] In order to solve these problems, there are many key technologies such as database sharding and hot and cold storage strategies. However, there are many key technologies such as database sharding and hot and cold data storage to solve the problem of retrieval of very large data volumes. These methods focus on efficiently extracting part of the data from the warehouse, that is, filtering the required information from the massive data for a single SQL query. However, when the same user initiates a large number of indicator queries in a short period of time, traditional query strategies are often unable to cope with the situation, resulting in low query efficiency and a sharp increase in system resource consumption. Summary of the invention

[0005] The embodiments of the present application provide a query method, device and medium based on an indicator warehouse segmentation strategy, which are used to solve the following technical problems: as the number of indicators and data in existing databases increases significantly, the data storage and query performance decreases significantly, making it difficult to carry more dimensional indicators under the same system, resulting in low query efficiency.

[0006] The present application embodiment adopts the following technical solutions:

[0007] On the one hand, an embodiment of the present application provides a query method based on an indicator warehouse segmentation strategy, including: based on business type information, configuring multiple related indicator data to obtain an exclusive indicator system; through the exclusive indicator system, configuring the sub-libraries and sub-tables of the database for relevant demand judgment to obtain a sub-library and sub-table configuration plan; according to the sub-library and sub-table configuration plan, combining the data tables under relevant dimensions and indicators to obtain a data configuration strategy with multiple related master-slave tables; adding and deleting the related master-slave tables in the data configuration strategy to determine a new data configuration strategy; querying the new data configuration strategy for business demand information to obtain business query result information.

[0008] The embodiment of the present application divides the indicator system and classifies the same type of indicators and related dimensions into the same system, thereby reducing the amount of redundant data under a single system and making the data more streamlined and efficient. This not only facilitates the analysis and mining of data, but also provides more powerful support for business decisions. At the same time, when the data is divided into tables, not only a single dimension is considered, but also a combination of multiple dimensions is combined to ensure that the data is more complete and consistent logically. By adopting a master-slave structure, some indicator columns are moved to the slave table, thus breaking through this limitation and allowing more indicators to be carried under the same system. The indicator warehouse is accurately divided into tables through a multi-dimensional segmentation strategy, and combined with innovative query acceleration optimization methods, efficient management and high-performance query of indicator warehouse data are achieved.

[0009] In a feasible implementation, based on the business type information, a variety of related indicator data are configured to obtain an exclusive indicator system, which specifically includes: extracting text information about the business description and business name of the business type information to determine the business identification information; according to the business identification information, correspondingly configuring the system name, system-related description and auxiliary information; generating a system number corresponding to the business type information; wherein the system number is a unique identifier of the exclusive indicator system; combining the business identification information with the system number to obtain an exclusive data table name based on the business type information; according to the exclusive data table name, the preset number of indicators and the number of dimensions are added accordingly, and the exclusive indicator system is constructed based on the database language; wherein different indicators under the same system should have dimensional correlation.

[0010] In a feasible implementation, the dedicated indicator system is used to configure the sub-libraries and sub-tables of the database for demand judgment, and a sub-library and sub-table configuration plan is obtained, which specifically includes: judging whether the business type information needs to be stored in a sub-library through the dedicated indicator system; if sub-library storage processing is required, configuring the data source connection mode of different databases; judging whether the business type information needs data sub-tables for horizontal data; if data sub-table processing is required, configuring the dimension combination of the data sub-table; wherein the dimension combination is uniquely represented between different data sub-tables and is used in all data The data sub-tables are also uniquely represented; whether the business type information needs a structural sub-table for the related vertical data is judged; if structural sub-table processing is required, the indicator data in the business type information is divided into a master-slave table structure to obtain a master table structure and a slave table structure; wherein the master table structure includes: a unique primary key column, a dimension column and an indicator column; the slave table structure includes: the unique primary key column and the indicator column; based on the configuration method of the structural sub-table, the sub-library storage and the data sub-table, and according to the association relationship between the data sub-table and the sub-library storage, the sub-library and sub-table configuration scheme is obtained.

[0011] In a feasible implementation manner, if data sub-table processing and sub-library storage processing are required, an association relationship between the data sub-table and the sub-library storage is constructed.

[0012] In a feasible implementation, according to the sub-library and sub-table configuration scheme, the data table is subjected to sub-table combination processing under relevant dimensions and indicators to obtain a data configuration strategy with multiple related master-slave tables, specifically including: based on the sub-library storage configuration mode in the sub-library and sub-table configuration scheme, judging whether the databases between different sub-library storages are connected to obtain the sub-library data table logic; based on the data sub-table configuration mode in the sub-library and sub-table configuration scheme, the data sub-tables are combined with the dimensions in a corresponding relationship, and based on the sub-library data table logic, a data sub-table intermediate table is constructed; wherein, the data sub-table intermediate table includes: a first data column, a first field type and a first description; based on the structural sub-table configuration method in the sub-library and sub-table configuration scheme, the structural sub-table is combined with the dimensions and indicators in a corresponding relationship to obtain a structural table intermediate table; wherein the structural table intermediate table includes: a second data column, a second field type and a second description; through a preset sub-table combiner, the data sub-table intermediate table and the structural table intermediate table are created as data tables under the sub-table rules in the relevant table structure to obtain several related master-slave tables; wherein the sub-table rules in the table structure include: an indicator table name, a data sub-table name and a structural sub-table name; based on several of the related master-slave tables, the data configuration strategy is constructed.

[0013] In a feasible implementation manner, the relevant master-slave tables in the data configuration strategy are added and deleted to determine a new data configuration strategy, which specifically includes: based on preset addition and deletion indicators, adding, deleting and modifying corresponding data table fields in the structural table intermediate table of the relevant master-slave tables in the data configuration strategy; making association judgments on preset addition and deletion dimensions; if the addition and deletion dimensions are not associated with the data grouping dimensions of the relevant master-slave tables, then performing corresponding addition and deletion processing on the data sub-table master table in the relevant master-slave tables; if the addition and deletion dimensions are associated with the data grouping dimensions of the relevant master-slave tables, then performing corresponding addition and deletion processing on the data dimensions in the relevant master-slave tables through data backup and data migration solutions, and maintaining the data sub-table configuration mode in the data configuration strategy; wherein, the data sub-table configuration mode includes: a sub-database storage configuration mode, a structure sub-table configuration mode and a data sub-table configuration mode.

[0014] In a feasible implementation, the new data configuration strategy is subjected to a reorganization query processing of business demand information to obtain business query result information, specifically including: inputting the SQL statement corresponding to the business demand information into the new data configuration strategy; parsing the SQL statement under relevant element tags through a preset SQL parser; and according to the structure table intermediate table and the data sub-table intermediate table of the relevant master-slave table in the new data configuration strategy, the SQL statement is subjected to a reorganization query judgment of the corresponding data table to obtain the data storage table location; wherein the elements include at least: system number, indicator column, dimension column, filter condition and grouping condition; based on the data storage table location, the business demand information is queried to obtain business query result information; wherein the business query result information is the content attribute data corresponding to each data table.

[0015] In a feasible implementation, the SQL statement is subjected to a reorganized query judgment of the corresponding data table to obtain the data storage table location, specifically including: converting the SQL statement into a number of sub-table SQL query statements applicable to the data table; reorganizing the several sub-table SQL query statements, and reconstructing the reorganized sub-table SQL query statements through SQL statement limiting conditions to obtain efficient sub-table SQL query statements; querying the data table through the efficient sub-table SQL query statements to obtain the data storage table location of each of the data tables.

[0016] In the second aspect, an embodiment of the present application also provides a query device based on an indicator warehouse segmentation strategy, the device comprising: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions that can be executed by the at least one processor, so that the at least one processor can execute a query method based on an indicator warehouse segmentation strategy described in any of the above embodiments.

[0017] In the third aspect, an embodiment of the present application also provides a non-volatile computer storage medium, characterized in that the storage medium is a non-volatile computer-readable storage medium, and the non-volatile computer-readable storage medium stores at least one program, each of which includes instructions, and when the instructions are executed by the terminal, the terminal executes a query method based on an indicator warehouse segmentation strategy described in any of the above-mentioned embodiments.

[0018] The embodiment of the present application provides a query method, device and medium based on the index warehouse segmentation strategy. Compared with the prior art, the embodiment of the present application has the following beneficial technical effects:

[0019] The embodiment of the present application divides the indicator system and classifies the same type of indicators and related dimensions into the same system, thereby reducing the amount of redundant data under a single system and making the data more streamlined and efficient. This not only facilitates the analysis and mining of data, but also provides more powerful support for business decisions. At the same time, when the data is divided into tables, not only a single dimension is considered, but also a combination of multiple dimensions is combined to ensure that the data is more complete and consistent logically. By adopting a master-slave structure, some indicator columns are moved to the slave table, thus breaking through this limitation and allowing more indicators to be carried under the same system. The indicator warehouse is accurately divided into tables through a multi-dimensional segmentation strategy, and combined with innovative query acceleration optimization methods, efficient management and high-performance query of indicator warehouse data are achieved. BRIEF DESCRIPTION OF THE DRAWINGS

[0020] In order to more clearly illustrate the technical solutions in the embodiments of the present application or the prior art, the drawings required for use in the embodiments or the prior art descriptions are briefly introduced below. Obviously, the drawings described below are only some embodiments recorded in the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without creative work. In the drawings:

[0021] Figure 1 A flow chart of a query method based on an indicator warehouse segmentation strategy provided in an embodiment of the present application;

[0022] Figure 2 A data table flow chart of the same indicator system provided in an embodiment of the present application;

[0023] Figure 3 A processing flow chart of a horizontal and vertical table configurator provided in an embodiment of the present application;

[0024] Figure 4 A schematic diagram of the structure of a query device based on an indicator warehouse segmentation strategy provided in an embodiment of the present application. DETAILED DESCRIPTION

[0025] In order to enable those skilled in the art to better understand the technical solutions in this application, the technical solutions in the embodiments of this application will be clearly and completely described below in conjunction with the drawings in the embodiments of this application. Obviously, the described embodiments are only part of the embodiments of this application, not all of them. Based on the embodiments of this specification, all other embodiments obtained by ordinary technicians in this field without creative work should fall within the scope of protection of this application.

[0026] It should be noted that the present application includes two parts: a multi-dimensional segmentation strategy and a high-performance query engine, wherein the multi-dimensional segmentation strategy is the foundation and the basis of the high-performance query engine.

[0027] The multi-dimensional segmentation strategy includes indicator system configuration strategy (mandatory), atomic indicator configuration strategy (mandatory if conditions are met), data configuration strategy, and data table publishing configuration strategy.

[0028] Indicator system configuration strategy (mandatory): This strategy constitutes the core cornerstone of the multi-dimensional segmentation strategy and is a mandatory strategy that must be implemented. The concept of indicator system mainly refers to the fine division of indicators in business direction to ensure that indicators in the same business direction belong to the same system. The correlation between indicators in different systems is weak at the business level. This division method helps to improve the clarity of data management and the pertinence of queries. For example, according to industry characteristics, we can divide indicators into financial indicators, mining indicators, etc. Such classification makes the indicators under the same system have stronger relevance in business, which makes it easier for us to extract common dimensions and effectively reduce data redundancy, thereby significantly improving query efficiency.

[0029] Atomic indicator configuration strategy (mandatory execution after conditions are met): In the same system, there are often multiple atomic indicators, such as operating income. As the business complexity increases, the number of atomic indicators in the same system may reach hundreds or thousands. In order to improve query efficiency, all atomic indicators are usually stored as columns of the data table. However, due to the characteristics of the database table structure, thousands of indicators cannot be placed in the same data table. Therefore, the data needs to be split multiple times according to the strength of business relevance to form multiple data tables. The indicator columns between different data tables are different. The execution of this strategy is closely related to database performance. After practical verification, it is recommended that the number of atomic indicators in the same data table should not exceed 400 to ensure the efficient operation of the database.

[0030] Data configuration strategy: Under the same system, with the continuous development of the business, the amount of data is growing rapidly. According to the data storage strategy, the increase in data is mainly manifested as an increase in data rows in the database. However, this strategy may lead to a decrease in query performance when facing massive data. Therefore, this strategy proposes to pre-plan the data storage strategy based on the combination of one or more dimensions, that is, by partitioning the data so that different dimension combinations are stored in different tables. In this way, dynamic iteration of hot and cold data can be achieved, reducing the amount of data in the table where the current active data is located, thereby significantly improving query performance. This strategy aims to optimize the data storage structure, improve data access efficiency, and meet the needs of rapid business development.

[0031] Among them, there may be one or more dimensions under the current system, and one or more are selected as conditions for data partitioning.

[0032] Data table publishing configuration strategy: Multiple data tables will be formed according to the configuration strategy. This strategy configures the storage location of the data tables. The multiple tables formed by the atomic indicator configuration strategy cannot be in multiple sources. This strategy is mainly for the tables formed by the indicator system configuration strategy and the data configuration strategy.

[0033] Under the multi-dimensional segmentation strategy configuration, the minimum granularity of the data table finally formed is 1, among which the indicator system configuration strategy is a mandatory strategy and the highest priority strategy, the data table publishing configuration strategy is the last configuration strategy, and the other two strategies have no order of precedence and no mandatory dependency in principle.

[0034] The high-performance query engine includes an input module, an SQL parser, an SQL regrouping module, an SQL reconstruction module, a result splitting and processing module, and an output module.

[0035] Input module: As the starting point of the entire high-performance query engine, it is responsible for receiving SQL groups for large-scale query of index warehouse data. These SQL groups may contain 1 to N query requests.

[0036] SQL parser: This module deeply parses the SQL passed by the input module, extracts and converts the data table (system number), indicator column, dimension column, filter condition, grouping condition and other information involved in each SQL into recognizable structured data. After the parsing is completed, the elements in the SQL are marked according to the pre-set multi-dimensional segmentation strategy, and a unique identification mark is given to each SQL parsing object to ensure subsequent tracking and management.

[0037] SQL regrouping module: This module is responsible for regrouping the parsed SQL. The regrouping basis includes querying SQL of the same system, querying SQL of the same year under the same system, etc. These grouping principles are mainly based on data configuration strategies and filtering conditions obtained during SQL parsing. The purpose of this step is to group SQL with similar characteristics together as much as possible to facilitate SQL reconstruction and query optimization in subsequent steps.

[0038] SQL Reconstruction Module: This module performs reconstruction operations on the regrouped SQL. For example, the "equal" condition in the original multiple SQLs is changed to an "IN" condition to improve query efficiency. At the same time, based on the grouping results in the previous step, multiple SQLs are reconstructed into a few more efficient SQL statements. In this process, it is also necessary to replace the data table name actually stored in the database in the original SQL to ensure that the query can be executed correctly.

[0039] Result splitting and processing module: This module is responsible for executing the reconstructed SQL query and splitting and processing the query results. Based on the original SQL tags recorded during the SQL regrouping and SQL reconstruction process, this module can accurately correspond the query results to each original SQL request and process the specific results required for each SQL.

[0040] Output module: As the end point of the entire query process, this module is responsible for organizing the query result set of each SQL and returning it to the caller in a unified form. In this way, the caller can obtain complete and accurate query results.

[0041] The function of this method is to reconstruct the SQL query statements while ensuring that the input and output results remain unchanged. The original hundreds or thousands of SQL statements are reconstructed into 1 to N new SQL statements, which greatly reduces the amount of SQL statements. At the same time, due to the sharding feature, each SQL query becomes much faster, thereby improving the query performance as a whole.

[0042] The present application embodiment provides a query method based on the index warehouse segmentation strategy, such as Figure 1 As shown, the query method based on the indicator warehouse segmentation strategy specifically includes steps S101-S105:

[0043] S101. Based on the business type information, various related indicator data are configured to obtain an exclusive indicator system.

[0044] Specifically, it is necessary to first extract the text information of the business description and business name from the business type information to determine the business identification information. According to the business identification information, the system name, system-related description and auxiliary information are configured accordingly. A system number corresponding to the business type information is generated. Among them, the system number is the unique identifier of the exclusive indicator system.

[0045] Furthermore, the business identification information and the system number are combined to obtain a dedicated data table name based on the business type information.

[0046] Furthermore, it is necessary to add the preset number of indicators and the number of dimensions according to the exclusive data table name, and build an exclusive indicator system based on the database language. Among them, different indicators under the same system should have dimensional correlation. That is, the number of indicators and the number of dimensions under the same system cannot be less than 1, and different indicators under the same system should have dimensional correlation. For example: the balance sheet indicator system, the indicators will have operating income this month, etc., and the dimensions will include year, period type, period, organization, data logic type, and possibly data version, etc. If it is a report business, there may also be the concept of tasks (the same task is the same type of table).

[0047] In one embodiment, Figure 2 A data table flow chart of the same indicator system provided in the embodiment of the present application, such as Figure 2 As shown, the text information of the business description and business name is extracted from the business type information to determine the business identification information. Then, when creating the indicator system, the system number, system name, related descriptions and auxiliary settings need to be set. The system number is the unique identifier of this system, so the system number cannot be repeated. The combination of module identifier + system number is used as the display table name of the current system data table, which is also the only table name. Any SQL operating the current system can use this table name. For example: create system number: ZCFZB, system name: balance sheet, and store the indicator data information related to the balance sheet. At this time, the name of the system structure table formed by the database should be: module identifier (JL) + system number (ZCFZB) = data table name (JLZCFZB).

[0048] S102. Using a dedicated indicator system, configure the sub-libraries and sub-tables of the database based on the relevant demand judgment to obtain a sub-library and sub-table configuration plan.

[0049] Specifically, the dedicated indicator system is first used to determine whether the business type information needs to be stored in a separate database. If separate database storage is required, the data source connection method of different databases is configured.

[0050] Furthermore, the business type information is judged whether a data sub-table for the horizontal data is required. If data sub-table processing is required, a dimension combination of the data sub-table is configured. The dimension combination is uniquely represented between different data sub-tables and is also uniquely represented between all data sub-tables.

[0051] Furthermore, the business type information is judged whether the structural sub-table of the vertical data is required. If the structural sub-table is required, the indicator data in the business type information is divided into the structure of the master and slave tables to obtain the master table structure and the slave table structure. The master table structure includes: a unique primary key column, a dimension column, and an indicator column; the slave table structure includes: a unique primary key column and an indicator column.

[0052] Furthermore, based on the configuration mode of the structural sub-tables, sub-library storage and data sub-tables, and according to the association relationship between the data sub-tables and sub-library storage, a sub-library and sub-table configuration scheme is obtained.

[0053] As a feasible implementation method, if data table partitioning and database partitioning are required, an association relationship between the data table partitioning and the database partitioning is established.

[0054] In one embodiment, Figure 2 As shown, 1) Determine whether separate databases are needed for storage. If separate databases are needed, configure the data source connection methods for different databases.

[0055] 2) Determine whether data sub-tables are needed. If necessary, check one or more data sub-table dimensions. The dimensions or dimension combinations of the selected data sub-tables must have certain business significance and the granularity cannot be too small. The dimension combination is unique between different sub-tables, and the combination of all dimensions is unique between all sub-tables. For example: If the above balance sheet is sub-tabled, annual sub-tables should be used. Historical years are not frequently accessed, and there are 10,000 units*12 months*limited data logic types*limited data versions*limited period types under a year. The amount of data in the table is neither too small nor too large. The number of sub-tables is expanded according to the year. If unit sub-tables are used, there will be tens of thousands of sub-tables, which is not friendly to query performance.

[0056] 3) Determine whether data needs to be stored in separate databases if the data is divided into separate tables. If so, establish the association between data division and separate database storage.

[0057] 4) Determine whether to perform structural sub-tables. Generally, it is determined based on the number of selected indicators. Currently, the program automatically performs structural sub-tables when the number of indicators exceeds 400 by default. The structure of the main table of the structural sub-table is: unique primary key column + dimension column + indicator column, and the structure of the secondary table is: unique primary key column + indicator column. The indicator column is unique in all structural sub-tables, and the value of the unique primary key column is the most complete in the structural main table, including the primary key values ​​of all secondary tables. The master and secondary tables of the structural sub-table are associated using unique primary keys.

[0058] Then, using steps 1) to 4), we can get the database and table sharding configuration plan.

[0059] S103. According to the sub-library and sub-table configuration plan, the data table is combined and processed under the sub-tables of relevant dimensions and indicators to obtain a data configuration strategy with multiple related master-slave tables.

[0060] Specifically, Figure 3 A processing flow chart of a horizontal and vertical table configurator provided in an embodiment of the present application, such as Figure 3 As shown, it is necessary to first determine whether the databases between different sub-library storages are connected based on the sub-library storage configuration method in the sub-library and sub-table configuration plan to obtain the sub-library data table logic.

[0061] Furthermore, based on the data table configuration mode in the database and table configuration scheme, the data tables and dimensions are combined in a corresponding relationship, and based on the database and table logic, a data table intermediate table is constructed. The data table intermediate table includes: a first data column, a first field type, and a first description.

[0062] Further, based on the structure sub-table configuration mode in the sub-library and sub-table configuration scheme, the structure sub-table is combined with the dimension and the index in a corresponding relationship to obtain a structure table intermediate table, wherein the structure table intermediate table includes: a second data column, a second field type and a second description.

[0063] Furthermore, through the preset table sharding combiner, the data sharding intermediate table and the structure table intermediate table are created under the table sharding rules in the table structure to obtain several related master-slave tables. Among them, the table sharding rules in the table structure include: indicator table name, data sharding name and structure sharding name. Finally, a data configuration strategy can be constructed based on several related master-slave tables.

[0064] In one embodiment, Figure 3 As shown, first determine whether the databases in different sub-libraries are connected based on whether they are sub-libraries, and obtain the sub-library data table logic.

[0065] Then, based on whether a data sub-table is formed, an intermediate table of the relationship between the data sub-table and the dimension is formed, that is, the data sub-table intermediate table; at the same time, according to the sub-database and sub-table configuration logic, the corresponding storage library is recorded on each sub-table. For example, the data sub-table intermediate table is shown in Table 1:

[0066] Table 1 Data partition table intermediate table

[0067]

[0068] Table 2: Example table of specific liabilities and assets of data sub-tables and dimension relationships

[0069]

[0070] Among them, Table 2 is a specific liability and asset example table of the data sub-table and dimension relationship in the data sub-table intermediate table.

[0071] Next, depending on whether the structural sub-table rule is formed, an intermediate table between the indicator, dimension and structural sub-table is formed, that is, the structural table intermediate table. For example, the structural table intermediate table is shown in Table 3:

[0072] Table 3 Structure table intermediate table

[0073]

[0074] Table 4 Example of specific liabilities and assets between indicators, dimensions and structural sub-tables

[0075]

[0076] Among them, Table 4 is an example table of specific liabilities and assets between the relevant indicators, dimensions and structural sub-tables of the middle table of the structure table.

[0077] Finally, if Figure 3 As shown, through the preset table sharding combiner, the data sharding intermediate table and the structure table intermediate table are created under the table sharding rules in the table structure. That is, it is necessary to form a specific table structure according to the configuration information of the sharding intermediate table, and complete the creation of the data table structure. The table sharding rules in the table structure are: indicator table name_data sharding table name_structure sharding table name. The example above is JLZCFZB_QT001_0001. The related master-slave tables that should be formed in the end are as follows:

[0078] Table 5 Related master-slave tables

[0079]

[0080] S104: Add and delete related master-slave tables in the data configuration strategy to determine a new data configuration strategy.

[0081] Specifically, based on the preset addition and deletion index, the structure table and intermediate table of the relevant master-slave table in the data configuration strategy may be added, deleted, or modified in the corresponding data table fields.

[0082] Furthermore, the preset addition and deletion dimensions are associated with each other:

[0083] If the addition and deletion dimension is not associated with the data grouping dimension of the relevant master-slave table, the corresponding addition and deletion processing is performed on the data sub-table master table in the relevant master-slave table. If the addition and deletion dimension is associated with the data grouping dimension of the relevant master-slave table, the corresponding addition and deletion processing is performed on the data dimension in the relevant master-slave table through the data backup and data migration plan, and the data sub-table configuration method in the data configuration strategy is maintained. Among them, the data sub-table configuration method includes: sub-database storage configuration method, structure sub-table configuration method and data sub-table configuration method.

[0084] As a feasible implementation method, the data configuration strategy can be modified. After the modification, the relevant master-slave tables under the indicator system need to be republished according to the configuration. After adding or deleting indicators under the data configuration strategy, the records of the middle table of the structure table of the relevant master-slave table should be added or deleted first. This table records the relationship between the indicators and the structure slave table. At the same time, 1 to N data table fields are added, deleted, and modified according to the data sub-table. After adding or deleting dimensions under the data configuration strategy, if it is determined that this dimension is not related to the data grouping dimension, it can be modified under the main table of all data sub-tables. After adding or deleting dimensions under the indicator system, if it is determined that this dimension is related to the grouping dimension, a reminder needs to be given. It cannot be modified by default. If it must be modified, a data backup and data migration plan is required to ensure that the configuration relationship remains in the standard way after the data grouping dimension changes.

[0085] S105 , performing reorganization query processing on the business demand information of the new data configuration strategy to obtain business query result information.

[0086] Specifically, the SQL statement corresponding to the business requirement information is input into the new data configuration strategy.

[0087] Furthermore, it is also necessary to parse the SQL statement under the relevant element tags through the preset SQL parser. And according to the structure table intermediate table of the relevant master-slave table and the data sub-table intermediate table in the new data configuration strategy, the SQL statement is reorganized and queried to determine the corresponding data table to obtain the data storage table location. Among them, the elements at least include: system number, indicator column, dimension column, filter condition and grouping condition.

[0088] As a feasible implementation method, the SQL statement can also be used to reorganize the query judgment of the corresponding data table, and can also include: converting the SQL statement into several sub-table SQL query statements applicable to the data table; reorganizing the several sub-table SQL query statements, and reconstructing the reorganized sub-table SQL query statements through SQL statement limiting conditions to obtain efficient sub-table SQL query statements; querying the data table through the efficient sub-table SQL query statements to obtain the data storage table location of each data table.

[0089] Furthermore, based on the data storage table location, query processing of business demand information is performed to obtain business query result information, wherein the business query result information is the content attribute data corresponding to each data table.

[0090] In one embodiment, the indicator encapsulates a unified storage and query module, and only a simple SQL statement is needed to return the data after the database and table are divided. For example:

[0091] 1) Enter the SQL statement: SELECT YYSR1FROM JLZCFZB WHERE year=2023;

[0092] 2) SQL parser: The relevant table parsed is JLZCFZB, which has the following columns: YYSR1, year;

[0093] 3) Data sub-table judgment: Through the configuration table, it is known that the next year of this system is a dimension combination field, and all sub-tables under the 2023 dimension are obtained by entering the data sub-table intermediate table;

[0094] 4) Structural sub-table judgment: According to the structural sub-table intermediate table, the structural sub-table where the YYSR1 indicator is located is obtained. Through rule combination, the actual storage table is JLZCFZB_QT003_0001, and the actual indicator field is IND_YYSR1. After conversion, the SQL becomes SELECT IND_YYSR1 FROM JLZCFZB_QT003_0001 WHERE year = 2023;

[0095] 5) Query: Based on the data storage table location corresponding to the sub-table SQL query statement, query the sub-table related data;

[0096] 6) Combination: After obtaining the data, the conversion table data is recombined into the original data structure, that is, the corresponding content attribute data of each data table.

[0097] In one embodiment, the query operation for SQL statements: The core function is to receive and process a large number of SQL statements for querying indicator warehouse data. These SQL statements may be generated by indicator formulas, and have significant characteristics such as large number, serious homogeneity (except for individual conditions), single source (all from the same user), and high real-time requirements. In order to improve query efficiency and meet real-time requirements, this module adopts the efficient query method provided by this patent, which can process and submit these SQL statements at one time to ensure the accuracy and timeliness of data queries.

[0098] For example, the SQL statement you enter may be:

[0099] ①SELECT YYSR1FROM JLZCFZB WHERE year='2023'AND period='01' and unit='01';

[0100] ②SELECT YYSR1FROM JLZCFZB WHERE year='2023' AND period='01' and unit='02';

[0101] ③SELECT YYSR2FROM JLZCFZB WHERE year='2023' AND period='01' and unit='03';

[0102] ④SELECT YYSR1FROM JLZCFZB WHERE year='2023' AND period='02' and unit='04';

[0103] ⑤SELECT YYSR1FROM JLZCFZB WHERE year='2023' AND period='02' and unit='04';

[0104] The core function of the SQL parser module is to deeply parse SQL statements and break them into identifiable node elements. During the parsing process, the SQL parser module will also mark and update the parsed elements according to the pre-set configuration strategy.

[0105] Taking the first SQL input as an example, after parsing by this SQL parser module, it can accurately identify that the indicator table involved in the current query is JLZCFZB. At the same time, the parsing result also shows that the indicator column to be queried includes YYSR1, and the dimensions used include year, period and unit. For these dimensions, we further parse out their corresponding specific values: year is 2023, period is 01, and unit is 01.

[0106] After parsing is completed, the SQL parser module will also mark it according to the order of the input SQL, such as "Enter the first SQL". In addition, combined with the rule combination in the configuration strategy, we can determine that the actual storage table is JLZCFZB_QT003_0001, and find the corresponding actual indicator field is IND_YYSR1. In this way, the SQL parser module provides accurate and efficient basic information for subsequent data query and processing.

[0107] Based on the parsing results of each SQL, the main responsibility of the SQL regrouping module is to extract key information and reorganize the original SQL statements. Taking the above input SQL and parsing results as an example, through comparative analysis, we found that SQL 1, 2, and 3 query the same data table, and the annual and period conditions are also consistent. Therefore, according to the preset configuration strategy, these SQLs are reorganized to reduce redundant queries and optimize query efficiency.

[0108] The reorganized SQL statement is as follows:

[0109] SELECT IND_YYSR1,IND_YYSR2,Unit FROM JLZCFZB_QT003_0001 WHERE year='2023' period='01' and unit in ('01','02'','03')

[0110] During the reorganization process, special attention should be paid to the processing of deformed dimensions to ensure that these dimensions are also correctly organized into the SELECT query statement, such as the "unit" dimension in the above example. In this way, we not only optimize the query performance, but also ensure the accuracy and completeness of the query results.

[0111] Based on the above returned reorganized subtable SQL query statement, the original three SQL statements are reconstructed into a more efficient SQL query. This reconstructed SQL query statement returns a data set containing three columns and three rows. In order to ensure that each original SQL can correspond to the correct query result, the returned result set needs to be further processed. In order to accurately split the reconstructed result set into three independent values, corresponding to the original three SQL statements, thus ensuring the accuracy and integrity of the data.

[0112] Finally, the reconstructed data table query is processed to obtain the data storage table location of each data table, thereby obtaining the business query result information.

[0113] In addition, the embodiment of the present application also provides a query device based on the index warehouse segmentation strategy, such as Figure 4 As shown, the query device 400 based on the index warehouse segmentation strategy specifically includes:

[0114] At least one processor 401. And a memory 402 in communication with the at least one processor 401. The memory 402 stores instructions that can be executed by the at least one processor 401, so that the at least one processor 401 can execute:

[0115] Based on the business type information, various related indicator data are configured to obtain a dedicated indicator system;

[0116] Through the exclusive indicator system, the database sub-library and sub-table configuration is carried out according to the relevant demand judgment, and the sub-library and sub-table configuration plan is obtained;

[0117] According to the sub-library and sub-table configuration plan, the data table is combined and processed under the relevant dimensions and indicators to obtain a data configuration strategy with multiple related master and slave tables;

[0118] Add and delete related master-slave tables in the data configuration strategy to determine a new data configuration strategy;

[0119] The new data configuration strategy is subjected to a reorganization query process for the business demand information to obtain business query result information.

[0120] The embodiment of the present application divides the indicator system and classifies the same type of indicators and related dimensions into the same system, thereby reducing the amount of redundant data under a single system and making the data more streamlined and efficient. This not only facilitates the analysis and mining of data, but also provides more powerful support for business decisions. At the same time, when the data is divided into tables, not only a single dimension is considered, but also a combination of multiple dimensions is combined to ensure that the data is more complete and consistent logically. By adopting a master-slave structure, some indicator columns are moved to the slave table, thus breaking through this limitation and allowing more indicators to be carried under the same system. That is, the indicator warehouse is accurately divided into tables through a multi-dimensional segmentation strategy, and combined with innovative query acceleration optimization methods, efficient management and high-performance query of indicator warehouse data are achieved.

[0121] Each embodiment in this application is described in a progressive manner, and the same or similar parts between the embodiments can be referred to each other, and each embodiment focuses on the differences from other embodiments. In particular, for the device and medium embodiments, since they are basically similar to the method embodiments, the description is relatively simple, and the relevant parts can be referred to the partial description of the method embodiments.

[0122] The devices and media provided in the embodiments of the present application correspond one-to-one to the methods. Therefore, the devices and media also have similar beneficial technical effects as the corresponding methods. Since the beneficial technical effects of the methods have been described in detail above, the beneficial technical effects of the devices and media will not be repeated here.

[0123] Those skilled in the art will appreciate that the embodiments of the present application may be provided as methods, systems, or computer program products. Therefore, the present application may adopt the form of a complete hardware embodiment, a complete software embodiment, or an embodiment combining software and hardware. Moreover, the present application may adopt the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program codes.

[0124] The present application is described with reference to the flowcharts and / or block diagrams of the methods, devices (systems), and computer program products according to the embodiments of the present application. It should be understood that each process and / or box in the flowchart and / or block diagram, as well as the combination of the processes and / or boxes in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to generate a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the processes in the flowchart and / or block diagram. Figure 1 A process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0125] These computer program instructions may also be stored in a computer-readable memory capable of directing a computer or other programmable data processing device to operate in a specific manner, so that the instructions stored in the computer-readable memory produce an article of manufacture comprising an instruction device, which implements the process Figure 1 A process or multiple processes and / or boxes Figure 1 A function specified in one or more boxes.

[0126] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operating steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing instructions for implementing the process. Figure 1 A process or multiple processes and / or boxes Figure 1 The steps for the functions specified in one or more boxes.

[0127] In a typical configuration, a computing device includes one or more processors (CPU), input / output interfaces, network interfaces, and memory.

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

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

[0130] It should also be noted that the terms "include", "comprises" or any other variations thereof are intended to cover non-exclusive inclusion, so that a process, method, commodity or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, commodity or device. In the absence of more restrictions, the elements defined by the sentence "comprises a ..." do not exclude the existence of other identical elements in the process, method, commodity or device including the elements.

[0131] The above is only an embodiment of the present application and is not intended to limit the present application. For those skilled in the art, the present application may have various changes and variations. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present application should be included in the scope of the specification of the present application.

Claims

1. A query method based on an indicator warehouse segmentation strategy, characterized in that: The method comprises: Based on the business type information, various related indicator data are configured to obtain a dedicated indicator system; Through the exclusive indicator system, the sub-libraries and sub-tables of the database are configured according to the relevant demand judgment, and the sub-libraries and sub-tables configuration plan is obtained; According to the sub-library and sub-table configuration scheme, the data table is combined and processed under the sub-tables of the relevant dimensions and indicators to obtain a data configuration strategy with multiple related master and slave tables, specifically including: Based on the sub-library storage configuration mode in the sub-library and sub-table configuration scheme, determine whether the databases between different sub-library storages are connected to obtain the sub-library data table logic; Based on the data sub-table configuration mode in the sub-library and sub-table configuration scheme, the data sub-tables are combined with the dimensions in a corresponding relationship, and based on the sub-library data table logic, a data sub-table intermediate table is constructed; wherein the data sub-table intermediate table includes: a first data column, a first field type, and a first description; Based on the structural sub-table configuration mode in the sub-library and sub-table configuration scheme, the structural sub-table is combined with the dimension and the indicator in a corresponding relationship to obtain a structural table intermediate table; wherein the structural table intermediate table includes: a second data column, a second field type and a second description; Through a preset sub-table combiner, the data sub-table intermediate table and the structure table intermediate table are created according to the sub-table rules in the table structure to obtain a number of related master-slave tables; wherein the sub-table rules in the table structure include: an indicator table name, a data sub-table name, and a structure sub-table name; Based on the related master-slave tables, construct the data configuration strategy; Performing addition and deletion processing on the relevant master-slave tables in the data configuration strategy to determine a new data configuration strategy; The new data configuration strategy is subjected to a reorganization query process of the business demand information to obtain business query result information.

2. A query method based on an indicator warehouse segmentation strategy according to claim 1, characterized in that: Based on the business type information, various related indicator data are configured to obtain a dedicated indicator system, including: Extracting text information about the service description and the service name from the service type information to determine the service identification information; According to the business identification information, the system name, system-related description and auxiliary information are configured accordingly; Generate a system number corresponding to the business type information; wherein the system number is a unique identifier of the exclusive indicator system; Combining the business identification information with the system number to obtain a dedicated data table name based on the business type information; According to the exclusive data table name, the preset number of indicators and the number of dimensions are added accordingly, and based on the database language, the exclusive indicator system is constructed; wherein different indicators under the same system should have dimensional correlation.

3. A query method based on an indicator warehouse segmentation strategy according to claim 1, characterized in that: Through the exclusive indicator system, the sub-libraries and sub-tables of the database are configured according to the relevant demand judgment, and the sub-libraries and sub-tables configuration plan is obtained, which specifically includes: Through the dedicated indicator system, determine whether the business type information needs to be stored in a separate database; If separate database storage is required, configure the data source connection methods for different databases; Determine whether the business type information requires a separate data table for related horizontal data; If data sub-table processing is required, a dimension combination of the data sub-table is configured; wherein the dimension combination is uniquely represented between different data sub-tables and is also uniquely represented between all data sub-tables; Determine whether the business type information requires a structural sub-table of related vertical data; If structural table division is required, the indicator data in the business type information is divided into a master-slave table structure to obtain a master table structure and a slave table structure; wherein the master table structure includes: a unique primary key column, a dimension column, and an indicator column; and the slave table structure includes: the unique primary key column and the indicator column; Based on the configuration mode of the structural sub-table, the sub-library storage and the data sub-table, and according to the association relationship between the data sub-table and the sub-library storage, the sub-library and sub-table configuration scheme is obtained.

4. A query method based on an indicator warehouse segmentation strategy according to claim 3, characterized in that: If data sub-table processing and sub-library storage processing are required, an association relationship between the data sub-table and the sub-library storage is constructed.

5. The query method based on the index warehouse segmentation strategy according to claim 1 is characterized in that: Performing addition and deletion processing on the relevant master-slave tables in the data configuration strategy to determine a new data configuration strategy specifically includes: Based on the preset addition and deletion index, add, delete and modify the corresponding data table fields of the structure table and intermediate table of the relevant master-slave table in the data configuration strategy; Make association judgments on the preset addition and deletion dimensions; If the addition and deletion dimension is not associated with the data grouping dimension of the relevant master-slave table, corresponding addition and deletion processing is performed on the data sub-table and master table in the relevant master-slave table; If the addition and deletion dimension is associated with the data grouping dimension of the relevant master-slave table, the data dimension in the relevant master-slave table is added and deleted accordingly through the data backup and data migration plan, and the data sub-table configuration method in the data configuration strategy is maintained; wherein the data sub-table configuration method includes: sub-database storage configuration method, structure sub-table configuration method and data sub-table configuration method.

6. The query method based on the index warehouse segmentation strategy according to claim 1 is characterized in that: The new data configuration strategy is subjected to a reorganization query process for the business demand information to obtain business query result information, specifically including: Inputting the SQL statement corresponding to the business requirement information into the new data configuration strategy; The SQL statement is parsed under the relevant element tags by a preset SQL parser; and according to the structure table intermediate table and the data sub-table intermediate table of the relevant master-slave table in the new data configuration strategy, the SQL statement is reorganized and queried to determine the corresponding data table to obtain the data storage table location; wherein the elements at least include: system number, indicator column, dimension column, filter condition and grouping condition; Based on the data storage table location, query processing is performed on the business demand information to obtain business query result information; wherein the business query result information is content attribute data corresponding to each data table.

7. A query method based on an indicator warehouse segmentation strategy according to claim 6, characterized in that: The SQL statement is subjected to a reorganization query and judgment of the corresponding data table to obtain the data storage table location, specifically including: Convert the SQL statement into several sub-table SQL query statements applicable to the data table; Reorganize the plurality of sub-table SQL query statements, and reconstruct the reorganized sub-table SQL query statements through SQL statement limiting conditions to obtain efficient sub-table SQL query statements; The data table is queried and processed by using the efficient sub-table SQL query statement to obtain the data storage table location of each data table.

8. A query device based on an indicator warehouse segmentation strategy, characterized in that: The device comprises: at least one processor; and, a memory communicatively connected to the at least one processor; wherein, The memory stores instructions that can be executed by the at least one processor, so that the at least one processor can execute a query method based on an indicator warehouse segmentation strategy according to any one of claims 1-7.

9. A non-volatile computer storage medium, characterized in that: The storage medium is a non-volatile computer-readable storage medium, and the non-volatile computer-readable storage medium stores at least one program, each of which includes instructions, and when the instructions are executed by the terminal, the terminal executes a query method based on an indicator warehouse segmentation strategy according to any one of claims 1-7.