Data aggregation query method and system based on time dimension

Abstract

The invention relates to the field of data query, and discloses a data aggregation query method and system based on time dimensionalities, the method comprises the following steps: obtaining a data aggregation query request, the data aggregation query request at least comprising a time index table of the time dimensionalities, the time dimensionalities being represented by at least one of year, month, day, hour and minute; analyzing the data aggregation query request, and determining an aggregation granularity, a date index value and an aggregation step length in a time index table; performing aggregation calculation according to the aggregation granularity, the date index value and the aggregation step length, and determining an aggregation date index; and performing field matching in a database through the aggregation date index, and performing grouping aggregation on matching results to determine a data query result. Indexes are added for different aggregation dimensions by creating a date index table, and an index calculation algorithm is combined, so that multiple time dimension query requirements are met, the data query speed can be remarkably increased, consumption of calculation resources is reduced, and the query flexibility is improved.

Description

TECHNICAL FIELD

[0001] The present application relates to the field of data query, and in particular to a data aggregation query method and system based on time dimension. BACKGROUND

[0002] With the promotion of Industry 4.0 and digital transformation, the industrial data generated in the factory production scene is growing exponentially, for example, millions of industrial data are generated every day according to the production needs. In the era of big data, manufacturers or institutions need to store massive industrial data in the cloud server, and use the convenient and powerful data processing capability of the cloud server to query massive data.

[0003] However, there are mainly two kinds of data query methods: the first kind is to synchronize the data table to the index as it is, and it is necessary to combine multiple table data to query from the index and then to the database or another index to obtain, that is, at least two queries are needed, and the query efficiency is not high; the second kind is to synchronize the log data to the queue by parsing, and then to write the data to the index by arranging the data through other programs, at this time, not only the queue and the additional consumption program need to be introduced, but also the efficiency of synchronizing the data to the index is not high. Therefore, there is an urgent need for a high-efficiency data query scheme in the database. SUMMARY

[0004] The embodiments of the present application provide a data aggregation query method and system based on time dimension to solve the problems of low efficiency and poor accuracy in data query in the related art.

[0005] The embodiments of the present application provide a data aggregation query method based on time dimension, comprising: obtaining a data aggregation query request, the data aggregation query request at least containing a time index table of time dimension, the time dimension being characterized by at least one of year, month, day, hour and minute; analyzing the data aggregation query request to determine the aggregation granularity, the date index value and the aggregation step length in the time index table; performing aggregation calculation according to the aggregation granularity, the date index value and the aggregation step length to determine the aggregation date index; performing field matching in the database through the aggregation date index, grouping and aggregating the matching results to determine the data query result.

[0006] In an embodiment of the present application, before obtaining the data aggregation query request, it further comprises: creating a time index table, the time index table comprising column name, data type, field type and rule description; wherein the column name comprises at least one of date data column, year index column, month index column, day index column, hour index column and minute index column; the rule description is used to record the counting method of each type of index column.

[0007] In an embodiment of the present application, the data aggregation query request is parsed to determine the aggregation granularity, the date index value and the aggregation step in the time index table, including: parsing the data aggregation query request to determine the time index table; traversing the time index table to determine the aggregation granularity according to the index field read in each index column;

[0008] Based on the date data column, the time difference between the current query date and the start date is determined, and based on the ratio of the time difference to the type of the current aggregation granularity, the date index value of each aggregation granularity is determined; the aggregation step of each type of index field is determined based on each aggregation granularity.

[0009] In an embodiment of the present application, the aggregation calculation is performed according to the aggregation granularity, the date index value and the aggregation step to determine the aggregation date index, including: calculating the ratio between the date index value and the aggregation step according to each type of aggregation granularity to determine the aggregation date index of each type of aggregation granularity; wherein, if the aggregation granularity includes minute aggregation, the minute aggregation date index is determined according to the ratio relationship between the minute corresponding date index value and the minute aggregation step, and the minute aggregation step is the minute aggregation number; if the aggregation granularity includes hour aggregation, the hour aggregation date index is determined according to the ratio relationship between the hour corresponding date index value and the hour aggregation step, and the hour aggregation step is the hour aggregation number; if the aggregation granularity includes day aggregation, the day aggregation date index is determined according to the ratio relationship between the day corresponding date index value and the day aggregation step, and the day aggregation step is the day aggregation number; if the aggregation granularity includes week aggregation, the week aggregation date index is determined according to the ratio relationship between the week corresponding date index value and the week aggregation step, and the week aggregation step is the week aggregation number; if the aggregation granularity includes month aggregation, the month aggregation date index is determined according to the ratio relationship between the month corresponding date index value and the month aggregation step, and the month aggregation step is the month aggregation number; if the aggregation granularity includes year aggregation, the year aggregation date index is determined according to the ratio relationship between the year corresponding date index value and the year aggregation step, and the year aggregation step is the year aggregation number.

[0010] In an embodiment of the present application, the field matching is performed in the database through the aggregation date index, the matching results are grouped and aggregated to determine the data query results, including: performing field matching in the database through the aggregation date index to determine the aggregated data, wherein the database is columnar storage data; grouping the aggregated data according to the preset data grouping category to obtain a plurality of data groups corresponding to the aggregated data, and determining the plurality of data groups as the data query results, wherein the preset data grouping category is the same type as the aggregation granularity.

[0011] In an embodiment of the present application, the data aggregation query request is parsed to determine the time index table, including: filtering the data aggregation query request to determine a query statement carrying a label, the label including whether the query statement is relational data or non-relational data; identifying the statement type of the query statement based on the label; if the statement type is relational data, storing the parsed time index table in a MySQL database; if the statement type is an Oracle statement, storing the parsed time index table in an Oracle database; if the statement type is a non-relational data Redis statement, storing the parsed time index table in an ES database; and if the statement type is a postgres statement, storing the parsed time index table in a postgres database.

[0012] In an embodiment of the present application, it further includes: if the data aggregation query request includes an encrypted data query condition, performing encrypted query on the encrypted database based on the encrypted data query condition to determine the ciphertext data; performing field matching on the ciphertext data through the aggregated date index, grouping and aggregating the matching results to determine the data query result.

[0013] In an embodiment of the present application, it further includes: disassembling the encrypted data query condition to obtain at least one matching condition; for any matching condition, finding the corresponding column in the columnar storage data in the database according to the matching condition, and performing ciphertext comparison of the matching condition based on the corresponding column to obtain the matching result; determining the ciphertext data according to the matching result corresponding to one or more matching conditions; and performing ciphertext aggregation on the ciphertext data through the aggregated date index to obtain the data query result.

[0014] In an embodiment of the present application, the data aggregation query request includes a business indicator combined with a time dimension, the business indicator including a business instruction for a target product or a target node, and the business instruction including at least one of a sum instruction, an average value instruction, a maximum value instruction, a minimum value instruction, a median value instruction, a variance instruction, and a standard deviation instruction.

[0015] The present application also provides a data aggregation query system based on a time dimension, including: a data acquisition module configured to acquire a data aggregation query request, the data aggregation query request including at least a time index table of a time dimension, the time dimension being represented by at least one of a year, a month, a day, an hour, and a minute; a request parsing module configured to parse the data aggregation query request to determine an aggregation granularity, a date index value, and an aggregation step length in the time index table; an aggregation calculation module configured to perform aggregation calculation according to the aggregation granularity, the date index value, and the aggregation step length to determine an aggregated date index; and an aggregation query module configured to perform field matching in a database through the aggregated date index, group and aggregate the matching results to determine a data query result.

[0016] In the scheme implemented by the time-dimension-based data aggregation query method and system provided above, the data aggregation query request is obtained, and the data aggregation query request at least contains a time index table of a time dimension; the data aggregation query request is parsed to determine an aggregation granularity, a date index value, and an aggregation step length in the time index table; an aggregation date index is determined through aggregation calculation according to the aggregation granularity, the date index value, and the aggregation step length; and the aggregation date index meeting the query requirement is generated through accurate aggregation calculation, thereby providing an effective index basis for accurate data matching and query in the database, enabling quick positioning to data of a relevant time period and improving data query and aggregation efficiency; the field matching is performed in the database through the aggregation date index, the matching result is grouped and aggregated to determine a data query result, thereby enabling extraction of an aggregation result meeting the query requirement from a large amount of data, improving the pertinence and effectiveness of data query, reducing unnecessary data transmission and processing, and improving overall performance and response speed. In summary, through creation of a date index table to add indexes for different aggregation dimensions and combination of index calculation algorithms, the time-dimension-based data aggregation query method provided by the present application meets various time-dimension query requirements and can significantly improve data query speed, reduce consumption of computing resources, improve query flexibility, and flexibly support various time-dimension query requirements such as multi-month, multi-week, and multi-day. BRIEF DESCRIPTION OF DRAWINGS

[0017] In order to more clearly illustrate the technical solutions of the embodiments of the present application, the drawings needed to be used in the description of the embodiments of the present application will be briefly introduced. Obviously, the drawings in the following description are only some embodiments of the present application, and other drawings can be obtained by those skilled in the art without creative labor.

[0018] Figure 1 A flowchart of a time-dimension-based data aggregation query method provided by an embodiment of the present application;

[0019] Figure 2 A schematic diagram of a time-dimension-based data aggregation query system provided by an embodiment of the present application;

[0020] Figure 3 A complete flowchart of a time-dimension-based data aggregation query method provided by an embodiment of the present application;

[0021] Figure 4 A structural schematic diagram of an electronic device in an embodiment of the present application;

[0022] Figure 5 Another structural schematic diagram of an electronic device in an embodiment of the present application. DETAILED DESCRIPTION

[0023] With reference to the drawings of the embodiments of the present application, the technical solutions in the embodiments of the present application will be described clearly and completely. Obviously, the described embodiments are only some of the embodiments of the present application, but not all of the embodiments of the present application. Based on the embodiments in the present application, all other embodiments obtained by those of ordinary skill in the art without creative effort are within the scope of the present application.

[0024] In order to enable those skilled in the art to better understand the improvements of the technical solutions provided by the present disclosure, the present disclosure briefly introduces the implementation scenarios and related information of the data aggregation query method based on time dimension in the related art.

[0025] Please refer to Figure 1 A flowchart of the data aggregation query method based on time dimension provided by the embodiments of the present application is shown below:

[0026] In step S101, a data aggregation query request is obtained, and the data aggregation query request at least includes a time index table of time dimension, which is represented by at least one of year, month, day, hour and minute;

[0027] In the embodiment, the query request is obtained from the message queue. For example, in a distributed system, the query request to be processed is sent to the message queue, and the related request is subscribed from the message queue. In this way, asynchronous processing can be realized, and the response performance and stability are improved.

[0028] In step S102, the data aggregation query request is parsed to determine the aggregation granularity, the date index value and the aggregation step length in the time index table.

[0029] In the embodiment, the request data is parsed field by field by using a parser such as a regular expression or a syntax analyzer. According to the pre-defined request format specification, the positions and formats of various parameters are identified, so that the aggregation granularity, the date index value and the aggregation step length and other information are accurately extracted. For example, for a request conforming to a specific JSON format, the corresponding field values are found by parsing the JSON structure to obtain these key parameters.

[0030] In step S103, the aggregation calculation is performed according to the aggregation granularity, the date index value and the aggregation step length to determine the aggregation date index.

[0031] In the embodiment, the mathematical calculation is performed according to the time conversion rule corresponding to the different aggregation granularity, in combination with the date index value and the aggregation step length. For example, if the day is used as the aggregation granularity, the next aggregation date index value is obtained by adding N days to the starting date index value if the starting date index value is a specific date and the aggregation step length is N days.

[0032] Step S104, field matching is performed in the database through the aggregation date index, and the matching results are grouped and aggregated to determine the data query result.

[0033] The database index mechanism, such as B-tree index, hash index, etc., is used to quickly locate the data records that meet the time range according to the aggregation date index, and the GROUP BY and aggregation functions, such as SUM, COUNT, AVG, etc., of the SQL statement are used to perform grouping and aggregation operations on the matched data.

[0034] For example, a data aggregation query request is received from the client, which contains at least a time index table of the time dimension, which can be represented by at least one of year, month, day, hour, and minute. For example, a query request is received, which specifies to query data within a certain time period, such as from January 1, 2025 to June 30, 2025, which is included in the time index table.

[0035] The data aggregation query request obtained is parsed to extract the aggregation granularity, date index value, and aggregation step in the time index table, wherein the aggregation granularity determines the time interval of data aggregation, such as by day, by hour, etc.; the date index value determines the key time points such as the start and end dates of the query; and the aggregation step is the time interval of time advancement in aggregation calculation. For example, if the aggregation granularity is "day", the date index value is from January 1, 2025 to June 30, 2025, and the aggregation step is 1 day, then the subsequent aggregation calculation will be performed by day. According to the aggregation granularity, date index value, and aggregation step obtained by parsing, a series of aggregation date indexes are determined from the date index value as the starting point, according to the aggregation step, and according to the aggregation granularity.

[0036] For example, if the aggregation granularity is "hour", the date index value starts from January 1, 2025 0:00, and the aggregation step is 2 hours, then the calculated aggregation date indexes are January 1, 2025 0:00, January 1, 2025 2:00, January 1, 2025 4:00, etc. The generated aggregation date index is used to perform field matching in the database to find the relevant data records corresponding to the index. The matching results are grouped and aggregated according to the specified rules, for example, grouped according to a certain dimension field such as region, product category, etc., and the data in each group is aggregated, such as sum, count, average, etc., to finally determine the data query result. Assuming that there is a sales record table in the database, which contains fields such as sales time, region, sales amount, etc., the sales records within a certain time period are matched through the aggregation date index, and then grouped according to the region field to calculate the total sales amount of each region within the time period.

[0037] In this way, the data is processed according to the time granularity and range expected by the user, improving the accuracy and flexibility of the query; the accurate aggregation calculation generates an aggregation date index that meets the query requirements, providing an effective index basis for accurate data matching and query of the database, enabling quick positioning to the data of the related time period, and improving the data query and aggregation efficiency; through accurate field matching and effective grouping and aggregation in the database, the aggregation result that meets the query requirements can be extracted from a large amount of data, improving the pertinence and effectiveness of data query, reducing unnecessary data transmission and processing, and improving the overall performance and response speed.

[0038] In some embodiments, before obtaining the data aggregation query request, the method further includes:

[0039] A time index table is created, and the time index table includes column names, data types, field types, and rule descriptions; wherein the column names include at least one of a date data column, a year index column, a month index column, a day index column, an hour index column, and a minute index column; and the rule descriptions are used to record the counting method of each type of index column.

[0040] Specifically, before performing the data aggregation query related operation, the time index table is created, and each property of the time index table is defined according to the specified format. First, the column names are determined, at least one of the date data column, the year index column, the month index column, the day index column, the hour index column, and the minute index column is selected for creation; then, the data type corresponding to each column is set, such as setting the date data column as date type and the year index column as integer type; then, the field type is specified, such as distinguishing between normal fields and index fields; and finally, the counting method of each type of index column is recorded in detail in the rule description.

[0041] In this way, the time index table provides a structured and standardized time dimension basis for data aggregation query; by setting the column names, data types, and counting rules, query errors caused by chaotic time data formats are avoided, improving the accuracy of data processing; and by efficiently managing the index in the database, the speed of data query and aggregation operation is accelerated.

[0042] In some embodiments, the data aggregation query request is parsed to determine the aggregation granularity, the date index value, and the aggregation step in the time index table, including:

[0043] The data aggregation query request is parsed to determine the time index table;

[0044] The time index table is traversed, and the aggregation granularity is determined according to the index field read in each index column;

[0045] determining a time difference between the current query date and the start date based on the date data column, determining a date index value of each aggregation granularity based on a ratio of the time difference and a type of the current aggregation granularity;

[0046] determining an aggregation step length of each index field based on each aggregation granularity.

[0047] Specifically, after receiving a data aggregation query request, the request is analyzed in syntax and semantics, and parameters and conditions related to the time dimension are identified. From a plurality of time index tables created in advance, a time index table that meets the current query requirement is matched. For example, if the query request specifies data aggregation by month, a time index table containing a "month index column" and meeting the query time range is searched. The index column information of each index column in the determined time index table is read in order. According to the type of the index field, such as year, month, day, hour, minute, etc., and its usage in the query request, the granularity of this data aggregation is determined. For example, if the query request focuses on month data, and the "month index column" in the time index table is marked as the key field of the query condition, then the aggregation granularity is determined to be "month". The start date and the current query date are obtained from the date data column of the time index table. Then the time difference between the two dates is calculated, and the unit of the time difference is determined according to the type of the aggregation granularity. For example, if the aggregation granularity is "month", the time difference is calculated in months. Finally, the calculated time difference is divided by the unit value corresponding to the type of the current aggregation granularity to obtain a ratio, and the date index value of each aggregation granularity is determined according to the ratio. For example, the aggregation granularity is "day", the start date is January 1, 2023, the current query date is January 10, 2023, and the time difference is 9 days. Therefore, the date index value is 9. According to the determined aggregation granularity, the characteristics of each index column in the time index table and the query requirement, the aggregation step length of each index field is determined. The aggregation step length represents the interval unit of time advancement when data aggregation is performed. For example, if the aggregation granularity is "hour", and the query requirement is to count the whole point data of each day, then the aggregation step length can be set to 1 hour; if the query requirement is to count the data of every 4 hours of each day, then the aggregation step length is 4 hours. According to the aggregation granularity and business requirements, the aggregation step length of each index field is reasonably calculated and set.

[0048] By the above manner, by accurately analyzing the query request and matching to the appropriate time index table, the data query and aggregation errors caused by using the wrong time index table are avoided, the accuracy and reliability of the whole system are improved; by scientific analysis to determine the aggregation granularity, the data aggregation operation can be carried out according to the actual needs of the user, the problem of too detailed or too general data caused by improper aggregation granularity selection is avoided, the effectiveness and practicality of data analysis are improved; by accurately calculating the time difference and the ratio, the accurate date index value is determined, the specific time point in the query range can be accurately located, the accuracy of data query and aggregation is improved; by reasonably determining the aggregation step, the data aggregation operation can improve the processing efficiency while ensuring the accuracy of the result, unnecessary data processing overhead is avoided, the use of system resources is optimized, and the overall performance and response speed are improved.

[0049] In some embodiments, the aggregation calculation is performed according to the aggregation granularity, the date index value and the aggregation step, and the aggregation date index is determined, including:

[0050] According to each type of aggregation granularity, the ratio between the date index value and the aggregation step is calculated to determine the aggregation date index of each type of aggregation granularity.

[0051] If the aggregation granularity includes minute aggregation, the minute aggregation date index is determined according to the ratio relationship between the minute corresponding date index value and the minute aggregation step, and the minute aggregation step is the minute aggregation number.

[0052] If the aggregation granularity includes hour aggregation, the hour aggregation date index is determined according to the ratio relationship between the hour corresponding date index value and the hour aggregation step, and the hour aggregation step is the hour aggregation number.

[0053] If the aggregation granularity includes day aggregation, the day aggregation date index is determined according to the ratio relationship between the day corresponding date index value and the day aggregation step, and the day aggregation step is the day aggregation number.

[0054] If the aggregation granularity includes week aggregation, the week aggregation date index is determined according to the ratio relationship between the week corresponding date index value and the week aggregation step, and the week aggregation step is the week aggregation number.

[0055] If the aggregation granularity includes month aggregation, the month aggregation date index is determined according to the ratio relationship between the month corresponding date index value and the month aggregation step, and the month aggregation step is the month aggregation number.

[0056] If the aggregation granularity includes year aggregation, the year aggregation date index is determined according to the ratio relationship between the year corresponding date index value and the year aggregation step, and the year aggregation step is the year aggregation number.

[0057] Exemplarily, the determined each type of aggregation granularity, such as year, month, day, hour, minute, etc., and the date index value and the aggregation step length under the corresponding granularity are acquired. For each type of aggregation granularity, the date index value is divided by the aggregation step length to obtain a corresponding ratio through mathematical division operation. For example, if the type of aggregation granularity is "day", the date index value is 30, and the aggregation step length is 5, the calculated ratio is 30 ÷ 5 = 6. The calculation operation is sequentially performed on all the determined aggregation granularities to form a ratio set under different granularities. According to the ratio of each type of aggregation granularity obtained in the previous step, in combination with the time index table and the logical rules of data aggregation, the aggregation date index corresponding to each type of aggregation granularity is determined. According to the ratio, the ratio is converted into a specific date index identifier in the time index table according to a certain mapping rule. For example, if the ratio is 6 and the type of aggregation granularity is "day", starting from the start date of the time index table, every 5 days (the aggregation step length is 5) are taken as the aggregation date index, and 6 aggregation date index values are sequentially generated; for different types of aggregation granularity, different mapping and generation methods are adopted according to the characteristics thereof, and finally a complete set of aggregation date indexes of each type of aggregation granularity is formed.

[0058] In the above manner, by accurately calculating the ratio between the date index value and the aggregation step length, a quantitative basis is provided for determining the aggregation date index. Through the aggregation distribution of data under different aggregation granularities, the blindness of data processing is reduced, the accuracy and standardization of data calculation are improved, the calculation result is converted into an index identifier that can be directly used for database query and data aggregation operation, and the efficiency of data query and aggregation is greatly improved. Through the aggregation date index, repeated query and invalid processing of data are avoided, system resource consumption is reduced, and the accuracy and integrity of the data aggregation result are ensured.

[0059] In some embodiments, the field matching is performed on the database through the aggregation date index, the matching result is grouped and aggregated to determine the data query result, including:

[0060] The field matching is performed on the database through the aggregation date index to determine the aggregated data, wherein the database is a columnar storage data.

[0061] The aggregated data is grouped according to a preset data grouping category to obtain a plurality of data groups corresponding to the aggregated data, and the plurality of data groups are determined as the data query result, wherein the preset data grouping category is of the same type as the type of the aggregation granularity.

[0062] Exemplarily, the determined aggregation date index is sent as a query condition to a database that stores data using columnar grouping. After the database receives the query request, it quickly locates the data rows that meet the index condition in the corresponding date column according to the aggregation date index. For example, in a database that stores sales data, the sales records of a specific time period are determined according to the aggregation date index, and the corresponding data is extracted from the date column, the sales column, the product category column, etc. to form an aggregation data set containing sales time, amount, product, etc. information. A preset data grouping category is obtained, which is the same type as the aggregation granularity, such as year, month, day, hour, etc. The aggregation data is traversed, and according to the time information in the aggregation data, i.e., the corresponding aggregation granularity, the data is distributed to the corresponding grouping category. For example, if the aggregation granularity is "month" and the preset data grouping category is also "month", then according to the date field in the aggregation data, each data record is grouped by month, and data belonging to the same month is grouped together. Finally, a number of data groups are formed, which are the final data query results.

[0063] In the above manner, the aggregation data is determined by field matching using the aggregation date index, which can fully utilize the storage advantages of columnar databases to quickly and accurately filter the data that meets the conditions from massive data. This reduces unnecessary data reading and processing and improves the efficiency of data acquisition. The aggregation data is grouped according to the preset data grouping category, which organizes the chaotic aggregation data into organized and regular data groups, making the data query results clearer and more intuitive, and facilitating data analysis and decision-making by users. The unified grouping standard (same as the aggregation granularity) ensures the consistency and comparability of the data, improving the quality and usability of the data query results.

[0064] In some embodiments, the data aggregation query request is parsed to determine a time index table, including:

[0065] The data aggregation query request is filtered to determine a query statement carrying a label, and the label includes whether the query statement is relational data or non-relational data.

[0066] The statement type of the query statement is identified based on the label. If the statement type is relational data, the parsed time index table is stored in a MySQL database. If the statement type is an Oracle statement, the parsed time index table is stored in an Oracle database. If the statement type is a non-relational data Redis statement, the parsed time index table is stored in an ES database. If the statement type is a postgres statement, the parsed time index table is stored in a postgres database.

[0067] Exemplarily, after receiving the data aggregation query request, a filtering procedure is started, and the query request is scanned line by line or section by section through preset syntax rules and tag identification algorithms, for example, whether specific identification keywords or sentence structure features are contained in the query request is checked, if it is detected that it conforms to a certain data type (relational or non-relational), the query request is added with a corresponding tag, marked as a relational data query statement or a non-relational data query statement, and the query statement with the tag is screened out.

[0068] The filtered query statement with the tag is read, and the type of the query statement is directly identified according to the tag content. If the tag indicates that the query statement is relational data, it is judged whether it is a statement of a specific relational database such as MySQL, Oracle, postgres, etc.; if the tag indicates non-relational data, it is judged whether it is a Redis statement or other non-relational database statement such as an ES database statement. For example, when the tag shows "relational data-MySQL", the query statement is identified as a MySQL relational database query statement type. The filtered query statement with the tag is read, and the type of the query statement is directly identified according to the tag content. If the tag indicates that the query statement is relational data, it is judged whether it is a statement of a specific relational database; if the tag indicates non-relational data, it is judged whether it is a Redis statement or other non-relational database statement. For example, when the tag shows "relational data-MySQL", the query statement is identified as a MySQL relational database query statement type.

[0069] In the above manner, through filtering and tag determination of the data aggregation query request, different types of query requests can be quickly and accurately distinguished, avoiding the situation of storing inappropriate query data into the wrong type of database, improving the accuracy and pertinence of data storage, and reducing query errors and performance problems caused by improper data storage. Based on the accurate identification of the type of the query statement by the tag, through accurate type identification, it is ensured that the data can be stored in the most suitable database for its structure and query requirements, fully utilizing the performance advantages of different databases, improving the efficiency of data storage and query, and reducing the cost of data management and maintenance. According to the type of the statement, the time index table is stored in the corresponding database, realizing the precise adaptation of data and database. This storage method fully utilizes the advantages of different databases in processing specific types of data, such as the integrity and consistency of relational databases in processing structured data, and the efficiency of non-relational databases in processing high concurrency and unstructured data, thereby improving the performance and reliability of data storage.

[0070] In some embodiments, further comprising:

[0071] If the data aggregation query request includes an encrypted data query condition, an encrypted database is queried based on the encrypted data query condition to determine encrypted data;

[0072] Field matching is performed on the encrypted data based on the aggregated date index, and the matching results are grouped and aggregated to determine the data query result.

[0073] For example, when receiving a data aggregation query request containing an encrypted data query condition, the encrypted data query condition is parsed to extract key encrypted query elements; the encrypted query elements are matched with the data in the encrypted database. For example, if the encrypted query condition is a range query on an encrypted user transaction amount field, such as querying transaction records with an amount within a certain interval, in the encrypted state, the corresponding encrypted transaction amount field data in the database is scanned and filtered based on a specific encrypted query algorithm to determine the encrypted data record set that meets the condition. After obtaining the encrypted data, if there is a related setting in the entire data aggregation query process, field matching is performed on the encrypted data using the determined aggregated date index. For example, if the aggregated date index is to divide the data by month, and the encrypted data contains an encrypted transaction date field, the encrypted transaction date field is matched with the aggregated date index in the encrypted state to find the encrypted data records corresponding to the month. According to other grouping conditions set by the user, such as transaction type, transaction location, and other encrypted fields, the matched encrypted data records are grouped, and for each group of encrypted data, an aggregation function that supports encrypted data operations is used, such as sum, count, and other functions that can be implemented in the encrypted state to perform aggregation operations, obtaining the encrypted data query result in encrypted form.

[0074] In the above manner, secure querying in the encrypted data environment ensures that the data is always in an encrypted state during the entire query process, protecting the confidentiality of the data and avoiding security risks caused by data decryption. At the same time, encrypted data that meets the encrypted query condition can also be accurately screened out; field matching, grouping, and aggregation operations are completed at the encrypted data level, obtaining the data query result that meets the user's requirements, and the entire process is performed without decrypting the data, greatly ensuring data security; valuable aggregated analysis results are obtained without touching the original plaintext data, meeting the data security requirements and realizing effective utilization of data, improving the security and practicality of data processing.

[0075] In some embodiments, it also includes:

[0076] The encrypted data query condition is disassembled to obtain at least one matching condition;

[0077] For any matching condition, the corresponding column in the columnar stored data in the database is found according to the matching condition, and a ciphertext comparison corresponding to the matching condition is performed based on the corresponding column to obtain a matching result;

[0078] The data query result is obtained by performing a ciphertext aggregation on the ciphertext data based on the date index.

[0079] Exemplarily, after receiving a data aggregation query request containing an encrypted data query condition, the encrypted data query condition is analyzed by a preset disassembling algorithm. For example, if the encrypted data query condition is "query transaction records with transaction date in 2024 and transaction amount greater than 1000 yuan", the disassembling algorithm disassembles it into two independent matching conditions "transaction date in 2024" and "transaction amount greater than 1000 yuan". By identifying the logical operators (such as "and", "or", etc.), comparison operators (such as "greater than", "less than", etc.) and field identifiers in the query condition, the complex query condition is divided into multiple simple and independently executable matching conditions.

[0080] For each matching condition disassembled, the corresponding column data is located in the columnar stored database according to the field information involved in the matching condition. For example, for the matching condition "transaction date in 2024", the column storing the transaction date is found. Since the data is encrypted, an algorithm supporting encrypted data comparison is used to compare the column data with the matching condition in an encrypted state; for example, using homomorphic encryption technology, the matching condition is converted into an encrypted form of comparison instruction, which is executed on the encrypted transaction date column data to determine whether each data satisfies the condition, thereby obtaining the matching result under the matching condition, i.e. the data record identifier or index that meets the condition. After obtaining the matching results corresponding to each matching condition, the matching results are integrated according to the relationship of the logical operators in the query condition. If the query condition is connected by "and", the intersection of the matching results is taken, and the data records in the intersection are determined as ciphertext data; if it is connected by "or", the union is taken as the ciphertext data. For example, for the two matching conditions "transaction date in 2024" and "transaction amount greater than 1000 yuan" mentioned above, if the query condition is the "and" relationship between the two, the data records that satisfy both the transaction date in 2024 and the transaction amount greater than 1000 yuan are found, and these records are determined as ciphertext data.

[0081] After the secret data is determined, the secret data is grouped and aggregated by using a pre-set aggregation date index. For example, if the aggregation date index is set to be aggregated by month, the data is grouped by month according to the encrypted transaction date field in the secret data. For the data in each group, an aggregation function supporting encrypted data operation, such as summation, counting, etc. is used to perform aggregation calculation in an encrypted state. For example, the encrypted data of transaction amount of each month is aggregated by summation to obtain the encrypted result of total transaction amount of each month, which constitutes the final data query result.

[0082] In the foregoing manner, the secret data meeting the overall query condition is determined according to the relationship of the plurality of matching conditions, the accuracy of the query result is ensured, and the diversified query requirements of the user are met. In the environment of encrypted data, the accurate screening of the data is realized through secure logical operation, the parts meeting the assembly requirements are selected from numerous parts according to the specific combination rule. The aggregation operation of the secret data is completed in an encrypted state to obtain the data query result meeting the requirements of the user. On the premise of ensuring the data security, the efficient analysis and utilization of the encrypted data are realized, the security risk caused by data decryption is avoided, and the efficiency of data processing is improved.

[0083] In some embodiments, the data aggregation query request includes a business index combined with a time dimension, the business index includes a business instruction for a target product or a target node, and the business instruction includes at least one of a sum instruction, an average value instruction, a maximum value instruction, a minimum value instruction, a median instruction, a variance instruction, and a standard deviation instruction.

[0084] Exemplarily, when initiating a data aggregation query request, the user explicitly specifies the business indicator related to the time dimension in the request parameters. For example, the user wants to query the average sales of a target product per month in the past year, and sets the time dimension as "the past year, in months" and the business indicator as "average value instruction (sales) for the target product" in the query request. After receiving the data aggregation query request, the time dimension information and business indicator information contained therein are identified and passed to subsequent processing modules for processing. When constructing the data aggregation query request, the user selects the corresponding business instruction according to the actual business requirements for a specific target product or target node. The target product can be a specific commodity, service, etc., and the target node can be a certain link in the business process or a certain data node in the system, etc. For example, the user selects the target product as mobile phone A and the business instruction as the sum instruction (sales volume), indicating that the user wants to query the total sales of mobile phone A in a specific time range. When parsing the data aggregation query request, the target product or target node information and the corresponding business instruction are extracted. At least one instruction is selected from a plurality of business instructions and added to the request, and after receiving the request, the business instruction is parsed and identified, and according to different business instructions, the corresponding data processing logic is executed. For example, if it is a sum instruction, the sum of the data related to the target product or target node in the specified time dimension is calculated; if it is an average value instruction, the average value is obtained by dividing the sum by the number of data.

[0085] In the above manner, by explicitly specifying the aggregation granularity, date index value and aggregation step, it can be clearly known how to divide and aggregate the data of the time dimension, improving the accuracy and efficiency of the aggregation calculation and avoiding incorrect calculation caused by unclear parameters; through accurate aggregation calculation, a date index conforming to the user's query requirements is generated. Querying the database through the date index enables the database to filter and aggregate data according to the user's expected time dimension. In the library, through accurate classification index, the required books can be quickly found, and through reasonable date index determination, the pertinence and efficiency of database query are improved, thereby improving the performance of the entire data aggregation query. The aggregation data result conforming to the user's time dimension and other grouping requirements provides clear and useful data insights for the user, meeting the user's data aggregation query requirements in different business scenarios. For example, enterprise managers can quickly understand the sales situation of different product categories in a specific time period through such query results, thereby making reasonable decisions; through efficient database field matching and grouping aggregation operations, the accuracy and efficiency of data query are improved.

[0086] In some embodiments, a time-dimension-based data aggregation query system is provided for performing the time-dimension-based data aggregation query method provided in any of the above embodiments. Please refer to Figure 2 , Figure 2 A structural diagram of the time-dimension-based data aggregation query system provided in the embodiments of the present application is shown in FIG. 2, which includes a data acquisition module 201, a request analysis module 202, an aggregation calculation module 203, and an aggregation query module 204. Specifically, Figure 2

[0087] The data acquisition module 201 is configured to acquire a data aggregation query request, which at least includes a time index table of a time dimension, and the time dimension is represented by at least one of year, month, day, hour, and minute.

[0088] The request analysis module 202 is configured to analyze the data aggregation query request, and determine an aggregation granularity, a date index value, and an aggregation step length in the time index table.

[0089] The aggregation calculation module 203 is configured to perform aggregation calculation according to the aggregation granularity, the date index value, and the aggregation step length, and determine an aggregation date index.

[0090] The aggregation query module 204 is configured to perform field matching in a database through the aggregation date index, group and aggregate the matching results, and determine a data query result.

[0091] The specific limitations of the time-dimension-based data aggregation query system can refer to the limitations of the time-dimension-based data aggregation query method described above, which will not be repeated here. The modules in the above time-dimension-based data aggregation query system can be realized by software, hardware, or a combination thereof, in whole or in part. The above modules can be embedded in or independent of the processor in the electronic device in hardware form, or stored in the memory in the electronic device in software form, so as to be called and executed by the processor to perform the operations corresponding to each module.

[0092] In the embodiments, the time-dimension-based data aggregation query system is essentially provided with a plurality of modules for performing the time-dimension-based data aggregation query method in any of the above embodiments. The specific functions and technical effects can refer to the above embodiments, which will not be repeated here.

[0093] ​The specific definition of the vehicle can refer to the definition of the data aggregation query method based on the time dimension in the above, which will not be repeated here. Each module in the vehicle described above can be implemented by software, hardware and their combination in whole or in part. The above-mentioned modules can be embedded in or independent of the processor in the electronic device in hardware form, or can be stored in the memory in the electronic device in the form of software, so that the processor calls and executes the operation corresponding to each module.

[0094] In some embodiments, a date index table aux_ddate is created, and the table fields are as follows:

[0095]

[0096] Date data is inserted into the aux_ddate table from the beginning date of the data (2010-01-01 00:00:00) to the date 50 years later (2051-01-25 23:59:59). The ddate column represents the date data column, the minute_idx column is the minute index column, which starts from the beginning date of the data (the value of 2010-01-01 00:01 is 0) and then increases; The hour_idx column is the hour index column, which starts from the beginning date of the data (the value of 2010-01-01 01 is 0) and then increases; The day_idx column is the date index column, which starts from the beginning date of the data (the value of 2010-01-01 is 0) and then increases; The month_idx column is the month index column, which starts from the beginning month of the data (the value of 2010-01 is 0) and then increases; The effect after the data is inserted is shown in the following table:

[0097] ddate minute_idx hour_idx day_idx month_idx 2010-01-01 00:01 0 0 0 0 2010-01-01 00:02 1 0 0 0 … … … … … 2010-01-02 00:01 1440 24 1 0 … … … … …

[0098] When the aggregation query is performed, the aux_ddate table is queried by associating the data table, and the aggregated date index is calculated by the formula: date_index=floor((aux_ddate.index field-#{startIdx}) / #{aggregateStep}), where the index field represents the specific database field, please refer to Figure 3 The complete flowchart of the data aggregation query method based on the time dimension provided by the embodiments of the present application is as follows:

[0099] In the data aggregation query, by selecting the aggregation granularity, when aggregating by minute, the index field is the minute_idx field in the aux_ddate table; when aggregating by hour, the index field is the hour_idx field in the aux_ddate table; when aggregating by day or week, the index field is the idx field in the aux_ddate table; when aggregating by month, the index field is the month_idx field in the aux_ddate table; startIdx represents the date index value (when aggregating by minute, the minute difference between the query start date and the earliest date of the aux_ddate data; when aggregating by hour, the hour difference between the query start date and the earliest date of the aux_ddate data; when aggregating by day, the day difference between the query start date and the earliest date of the aux_ddate data; when aggregating by week, the day difference between the Monday of the week of the query start date and the earliest date of the aux_ddate data; when aggregating by month, the month difference between the query start date and the earliest date of the aux_ddate data), and aggregateStep represents the aggregation step, for example, 2 when aggregating by 2 minutes; 2 when aggregating by 2 hours; 2 when aggregating by 2 days; 4 when aggregating by 4 months; and 14 when aggregating by 2 weeks; the aggregation date index can be calculated by formula, and the date indexes in the same aggregation time dimension range are the same, so that the data can be aggregated and queried by hour, minute, day, week, month, etc.

[0100] In the aggregation date index, the ratio between the date index value and the aggregation step can be determined according to each type of aggregation granularity, and the aggregation date index of each type of aggregation granularity is not described here.

[0101] In one embodiment, an electronic device is provided, which can be a server, and an internal structure diagram thereof can be as shown in Figure 4 The electronic device includes a processor, a memory, a network interface and a database connected by a system bus. The processor of the electronic device is used to provide computing and control capabilities. The memory of the electronic device includes a non-volatile and / or volatile storage medium, an internal memory. The non-volatile storage medium stores an operating system, a computer program and a database. The internal memory provides an environment for the operation of the operating system and the computer program in the non-volatile storage medium. The network interface of the electronic device is used to communicate with the external client through the network connection. The computer program is executed by the processor to implement the functions or steps of the server side of the above-mentioned method.

[0102] In one embodiment, an electronic device is provided, which can be a client, and an internal structure diagram thereof can be as shown in Figure 5As shown. The electronic device includes a processor, a memory, a network interface, a display screen and an input device connected through a system bus. Among them, the processor of the electronic device is used to provide computing and control capabilities. The memory of the electronic device includes a non-volatile storage medium, an internal memory. The non-volatile storage medium stores an operating system and a computer program. The internal memory provides an environment for the operating system and the computer program in the non-volatile storage medium to run. The network interface of the electronic device is used to communicate with the external server through the network connection. The computer program is executed by the processor to realize the functions or steps of the client side of the above method.

[0103] In one embodiment, an electronic device is provided, comprising a memory, a processor and a computer program stored on the memory and executable on the processor, the processor executing the computer program to implement the following steps:

[0104] Obtaining a data aggregation query request, the data aggregation query request at least containing a time index table of a time dimension, the time dimension being characterized by at least one of year, month, day, hour and minute; parsing the data aggregation query request to determine the aggregation granularity, the date index value and the aggregation step length in the time index table; performing aggregation calculation according to the aggregation granularity, the date index value and the aggregation step length to determine the aggregated date index; performing field matching in the database through the aggregated date index, and grouping and aggregating the matching results to determine the data query result.

[0105] In one embodiment, a computer readable storage medium is provided, which stores a computer program, the computer program being executed by a processor to implement the following steps:

[0106] Obtaining a data aggregation query request, the data aggregation query request at least containing a time index table of a time dimension, the time dimension being characterized by at least one of year, month, day, hour and minute; parsing the data aggregation query request to determine the aggregation granularity, the date index value and the aggregation step length in the time index table; performing aggregation calculation according to the aggregation granularity, the date index value and the aggregation step length to determine the aggregated date index; performing field matching in the database through the aggregated date index, and grouping and aggregating the matching results to determine the data query result.

[0107] It should be noted that the functions or steps that the above computer readable storage medium or electronic device can implement can be referred to the related description of the server side and the client side in the foregoing method embodiments. To avoid repetition, they will not be described one by one here.

[0108] Those skilled in the art can understand that all or part of the processes in the above-mentioned embodiment methods can be completed by instructing the relevant hardware through a computer program. The above-mentioned computer program can be stored in a non-volatile computer readable storage medium. When the computer program is executed, the processes of the above-mentioned embodiments of each method can be included. Any reference to memory, storage, database or other medium used in each embodiment provided by the present application can include non-volatile and / or volatile memory. Non-volatile memory can include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM) or flash memory. Volatile memory can include random access memory (RAM) or external cache memory. As an illustration but not limitation, RAM is available in various forms, such as static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), double data rate SDRAM (DDR SDRAM), enhanced SDRAM (ESDRAM), synchronous link DRAM (SLDRAM), memory bus direct RAM (RDRAM), direct memory bus dynamic RAM (DRDRAM), and the like.

[0109] Those skilled in the art can clearly understand that, for the convenience and brevity of description, only the above-mentioned division of each functional unit and module is exemplified. In actual application, the above-mentioned functions can be completed by different functional units and modules according to needs, that is, the internal structure of the above-mentioned device and system is divided into different functional units or modules to complete all or part of the above-mentioned functions.

[0110] The above-mentioned embodiments are only used to illustrate the technical solutions of the present application, but not to limit them. Although the present application has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that they can modify the technical solutions recorded in the foregoing embodiments, or make equivalent replacement for part of the technical features. The modification or replacement does not make the essence of the corresponding technical solution deviate from the spirit and scope of the technical solutions of the embodiments of the present application, and should be included in the protection scope of the present application.

Claims

1. A data aggregation query method based on time dimension, characterized in that: The method comprises: Obtaining a data aggregation query request, the data aggregation query request at least including a time index table of a time dimension, where the time dimension is represented by at least one of year, month, day, hour, and minute; Parsing the data aggregation query request to determine the aggregation granularity, date index value, and aggregation step in the time index table; Perform aggregation calculation according to the aggregation granularity, the date index value, and the aggregation step to determine an aggregation date index; Field matching is performed in the database using the aggregate date index, and the matching results are grouped and aggregated to determine the data query result.

2. The data aggregation query method based on time dimension according to claim 1, characterized in that: Before getting the data aggregation query request, it also includes: Create a time index table, which includes column names, data types, field types and rule descriptions; wherein the column names include at least one of a date data column, a year index column, a month index column, a day index column, an hour index column and a minute index column; and the rule descriptions are used to record the counting method for each type of index column.

3. The data aggregation query method based on time dimension according to claim 2, characterized in that: Parsing the data aggregation query request and determining the aggregation granularity, date index value, and aggregation step in the time index table include: Parsing the data aggregation query request and determining the time index table; Traversing the time index table, and determining the aggregation granularity according to the index fields read in each index column; Determine the time difference between the current query date and the start date based on the date data column, and determine the date index value of each aggregation granularity based on the ratio of the time difference to the type of the current aggregation granularity; An aggregation step length of each type of the index fields is determined based on each aggregation granularity.

4. The data aggregation query method based on time dimension according to claim 1, characterized in that: Performing aggregation calculation according to the aggregation granularity, the date index value, and the aggregation step to determine the aggregation date index includes: According to each type of the aggregation granularity, calculating the ratio between the date index value and the aggregation step, and determining the aggregation date index of each type of the aggregation granularity; If the aggregation granularity includes minute-by-minute aggregation, the minute-by-minute aggregation date index is determined based on the ratio between the date index value corresponding to the minute and the minute-by-minute aggregation step, and the minute-by-minute aggregation step is the number of minute-by-minute aggregations. If the aggregation granularity includes hourly aggregation, the hourly aggregation date index is determined according to the ratio between the date index value corresponding to the hour and the hourly aggregation step, and the hourly aggregation step is the number of hourly aggregations; If the aggregation granularity includes daily aggregation, the daily aggregation date index is determined according to the ratio between the date index value corresponding to the day and the daily aggregation step, and the daily aggregation step is the number of daily aggregations; If the aggregation granularity includes weekly aggregation, the weekly aggregation date index is determined according to the ratio between the date index value corresponding to the week and the weekly aggregation step, and the weekly aggregation step is the weekly aggregation number; If the aggregation granularity includes monthly aggregation, the monthly aggregation date index is determined according to the ratio between the date index value corresponding to the month and the monthly aggregation step, and the monthly aggregation step is the number of monthly aggregations; If the aggregation granularity includes yearly aggregation, the yearly aggregation date index is determined based on the ratio between the date index value corresponding to the year and the yearly aggregation step, and the yearly aggregation step is the number of yearly aggregations.

5. The data aggregation query method based on time dimension according to claim 1, characterized in that: Perform field matching in the database using the aggregate date index, and group and aggregate the matching results to determine data query results, including: Performing field matching on the database using the aggregate date index to determine aggregated data, wherein the database stores data in column format; The aggregated data is grouped according to a preset data grouping category to obtain a plurality of data groups corresponding to the aggregated data, and the plurality of data groups are determined as data query results, wherein the preset data grouping category is the same as the type of the aggregation granularity.

6. The data aggregation query method based on time dimension according to claim 2, characterized in that: Parsing the data aggregation query request and determining the time index table includes: Filtering the data aggregation query request to determine query statements carrying a label, wherein the label includes whether the query statement is relational data or non-relational data; Identify the statement type of the query statement based on the label; if the statement type is relational data, store the parsed time index table in the MySQL database; if the statement type is an Oracle statement, store the parsed time index table in the Oracle database; if the statement type is a non-relational data Redis statement, store the parsed time index table in the ES database; if the statement type is a postgres statement, store the parsed time index table in the postgres database.

7. The data aggregation query method based on time dimension according to claim 1, characterized in that: Also includes: If the data aggregation query request includes an encrypted data query condition, performing an encrypted query on the encrypted database based on the encrypted data query condition to determine the encrypted data; Field matching is performed on the secret data using the aggregate date index, and the matching results are grouped and aggregated to determine the data query result.

8. The data aggregation query method based on time dimension according to claim 7, characterized in that: Also includes: Decomposing the encrypted data query condition to obtain at least one matching condition; For any of the matching conditions, searching for a corresponding column in the column-based storage data in the database according to the matching condition, and performing a ciphertext comparison corresponding to the matching condition based on the corresponding column to obtain a matching result; The matching result corresponding to the one or more matching conditions is determined to be confidential data; the confidential data is aggregated in a confidential manner through the aggregation date index to obtain a data query result.

9. The data aggregation query method based on time dimension according to any one of claims 1 to 5, characterized in that: The data aggregation query request includes business indicators combined with the time dimension, and the business indicators include business instructions for the target product or target node. The business instructions include at least one of a sum instruction, an average instruction, a maximum instruction, a minimum instruction, a median instruction, a variance instruction, and a standard deviation instruction.

10. A data aggregation query system based on time dimension, characterized in that: include: A data acquisition module is configured to acquire a data aggregation query request, wherein the data aggregation query request includes at least a time index table of a time dimension, where the time dimension is represented by at least one of year, month, day, hour, and minute; A request parsing module, configured to parse the data aggregation query request and determine the aggregation granularity, date index value, and aggregation step in the time index table; an aggregation calculation module, configured to perform aggregation calculation according to the aggregation granularity, the date index value, and the aggregation step to determine an aggregation date index; The aggregate query module is used to perform field matching in the database through the aggregate date index, group and aggregate the matching results to determine the data query result.

Images