A pivot table data expansion method and device

Abstract

The present application discloses a pivot table data expansion method and device, including: connecting the row field value and column field value of each record in the data source, constructing a unique path string, and storing it in a hash table; when the aggregate information of the target path string needs to be queried, traversing the hash table to determine whether the target path string exists, so as to perform a query or update operation on the hash table; splitting the path string into row fields and column fields, and creating or searching for corresponding nodes in a rendering tree according to the values ​​of these fields, completing the construction of the rendering tree; traversing the entire rendering tree, determining the specific position of each node in a two-dimensional array according to its path string; filling the aggregate information of each node into the corresponding position of the two-dimensional array, and completing the two-dimensional expansion process of the pivot table. The method solves the problem that the traditional solution cannot complete the expansion task of the pivot table in time, thus affecting the user experience and business efficiency.

Description

Technical Field

[0001] The present application relates to the technical field of data processing, and in particular to a method and device for expanding pivot table data. Background Art

[0002] A Pivot Table is a data summary, analysis, and visualization tool that allows users to group, summarize, and display data based on specified row and column fields, thereby revealing patterns and trends in the data.

[0003] The traditional expansion scheme of a pivot table usually follows the process of building row and column information first and then filling in data. Specifically, the total size of the pivot table is first calculated based on the number of unique items in the row and column fields, and then the original data records are traversed. For each record, the corresponding row and column are found in the two-dimensional array based on the values ​​of its row and column fields, and the aggregate information of the value field is filled in the position. In this process, the algorithm complexity depends on the number of original data records, the number of value fields, and the total size of the pivot table.

[0004] When calculating the total size of a pivot table, the traditional solution needs to traverse all unique items in the row and column fields and perform multiplication operations. This causes the algorithm complexity to grow exponentially as the number of row and column fields increases. Due to the high algorithm complexity of the traditional solution, in an environment with limited memory and computing resources, the pivot table expansion task cannot be completed in time, thus affecting user experience and business efficiency. Summary of the invention

[0005] In an embodiment of the present application, a method for expanding pivot table data is provided to solve the problem that due to the high algorithm complexity of traditional solutions, the pivot table expansion task cannot be completed in a timely manner in an environment with limited memory and computing resources, thereby affecting user experience and business efficiency.

[0006] In a first aspect, an embodiment of the present application provides a pivot table data expansion method, the method comprising: connecting the row field value and the column field value of each record in the data source in a preset order, and using a delimiter to separate the value of each field to construct a unique path string; using the constructed path string as a key and its corresponding aggregation information as a value, storing it in a hash table to ensure that each path string in the hash table uniquely corresponds to a set of aggregation information; when it is necessary to query the aggregation information of the target path string, traversing the hash table to determine whether the target path string exists, so as to perform a query or update operation on the hash table and complete the construction of the hash table; traversing each path string in the hash table key-value pairs, split the path string into row fields and column fields, and create or find corresponding nodes in the rendering tree according to the values ​​of these fields to complete the construction of the rendering tree; initialize an empty two-dimensional array as the basic structure of the pivot table according to the combination of row fields and column fields; start from the root node of the rendering tree, traverse the entire rendering tree, and for each traversed node, determine the specific position of the node in the two-dimensional array according to its path string; fill the aggregation information of the node into the corresponding position of the two-dimensional array to perform the filling operation; when all nodes of the rendering tree are traversed and all positions of the two-dimensional array are filled, the two-dimensional expansion process of the pivot table is completed.

[0007] In a possible implementation, before connecting the row field value and the column field value of each record in the data source in a preset order, the data in the data source is preprocessed; the preprocessing is: replacing all non-numeric values ​​in the data source with numeric indexes.

[0008] In one possible implementation, when it is necessary to query the aggregation information of the target path string, the hash table is traversed to determine whether the target path string exists in order to perform a query or update operation on the hash table, including: if the target path string exists, obtaining the aggregation information corresponding to the target path string to perform a query operation on the hash table; if the target path string does not exist, inserting the target path string and its corresponding aggregation information into the hash table to perform an update operation on the hash table.

[0009] In a possible implementation, traversing each key-value pair in the hash table, splitting the path string into row fields and column fields, and creating or searching for corresponding nodes in the rendering tree according to the values ​​of these fields to complete the construction of the rendering tree includes: if the node corresponding to the value of the target field does not exist in the rendering tree, creating the node and adding it to the rendering tree to perform a corresponding node creation operation; if the node corresponding to the value of the target field exists in the rendering tree, updating or merging the aggregation information of the node to perform a corresponding node search operation; when all key-value pairs in the hash table are traversed, the construction of the rendering tree is completed; wherein the rendering tree is used to reflect the hierarchical relationship and aggregation information of each dimension in the pivot table.

[0010] In a possible implementation, the filling operation includes: if a node corresponds to values ​​of multiple fields, filling the value of each field separately; if a position in the two-dimensional array has no corresponding node, filling it with a null value.

[0011] In a possible implementation, the hash table is a dynamic hash table that can automatically adjust its capacity to store more path character strings and corresponding aggregation information.

[0012] In a second aspect, an embodiment of the present application provides a pivot table data expansion device, which includes: building a hash table module, which is used to connect the values ​​of the row field and the column field of each record in the data source in a preset order, and use a delimiter to separate the value of each field to build a unique path string; using the constructed path string as a key and its corresponding aggregation information as a value, storing it in a hash table to ensure that each path string in the hash table uniquely corresponds to a set of aggregation information; when it is necessary to query the aggregation information of the target path string, traversing the hash table to determine whether the target path string exists, so as to query or update the hash table and complete the construction of the hash table; building a rendering tree module, which is used to traverse For each key-value pair in the hash table, the path string is split into row fields and column fields, and corresponding nodes are created or found in the rendering tree according to the values ​​of these fields to complete the construction of the rendering tree; the expansion module is used to initialize an empty two-dimensional array as the basic structure of the pivot table according to the combination of row fields and column fields; starting from the root node of the rendering tree, the entire rendering tree is traversed, and for each traversed node, the specific position of the node in the two-dimensional array is determined according to its path string; the aggregation information of the node is filled into the corresponding position of the two-dimensional array to perform the filling operation; when all nodes of the rendering tree are traversed and all positions of the two-dimensional array are filled, the two-dimensional expansion process of the pivot table is completed.

[0013] In a third aspect, an embodiment of the present application provides a pivot table data expansion server, comprising a memory and a processor; the memory is used to store computer executable instructions; the processor is used to execute the computer executable instructions to implement the method described in the first aspect or any possible implementation method of the first aspect.

[0014] In a fourth aspect, an embodiment of the present application provides a computer-readable storage medium, wherein the computer-readable storage medium stores executable instructions, and when a computer executes the executable instructions, the method described in the first aspect or any possible implementation method of the first aspect can be implemented.

[0015] One or more technical solutions provided in the embodiments of the present application have at least the following technical effects:

[0016] The embodiment of the present application provides a pivot table data expansion method, which avoids the complex row and column combination calculation in the traditional method by connecting the value of the row field and the value of the column field and using a separator to generate a unique path string, thereby reducing the algorithm complexity. The target path string and its corresponding aggregate information are efficiently stored and queried using a hash table, thereby achieving fast data access and update, and significantly improving the construction speed of the pivot table. The combined use of the hash table and the rendering tree avoids the problem of pre-allocating a large amount of memory in the traditional two-dimensional array, and realizes dynamic allocation and efficient use of memory. Since the hash table and the rendering tree are both constructed based on the unique path string, data duplication storage is avoided, further reducing memory usage. The dynamic characteristics of the hash table and the rendering tree enable the pivot table to easily respond to data changes and realize real-time data update and query. By constructing a rendering tree, users can flexibly select a combination of row fields and column fields to perform multi-dimensional data analysis to meet diverse business needs. As the basic structure of the pivot table, the two-dimensional array is easy for users to understand and use, and improves the intuitiveness and ease of use of data analysis. The fast query feature of the hash table enables users to quickly obtain required data and improves work efficiency. This solves the problem that due to the high complexity of the traditional algorithm, the pivot table expansion task cannot be completed in time in an environment with limited memory and computing resources, thus affecting user experience and business efficiency. BRIEF DESCRIPTION OF THE DRAWINGS

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

[0018] Figure 1A flowchart of a pivot table data expansion method provided in an embodiment of the present application;

[0019] Figure 2 A specific flow chart of the embodiment of the present application for traversing a hash table to determine whether the target path string exists when the aggregate information of the target path string needs to be queried, so as to perform a query or update operation on the hash table;

[0020] Figure 3 A specific flowchart of traversing each key-value pair in a hash table provided in an embodiment of the present application, splitting a path string into row fields and column fields, and creating or searching for corresponding nodes in a rendering tree according to the values ​​of these fields to complete the construction of a rendering tree;

[0021] Figure 4 A schematic diagram of a salesperson product sales data table provided in an embodiment of the present application;

[0022] Figure 5 A schematic diagram of a monthly product sales summary table for a salesperson provided in an embodiment of the present application;

[0023] Figure 6 A schematic diagram of a hash table constructed in accordance with an embodiment of the present application;

[0024] Figure 7 A schematic diagram of a rendering tree constructed according to an embodiment of the present application;

[0025] Figure 8 A schematic diagram of a pivot table data expansion device provided in an embodiment of the present application;

[0026] Fig. 9 A schematic diagram of a pivot table data expansion server provided in an embodiment of the present application. DETAILED DESCRIPTION

[0027] The technical solutions in the embodiments of the present application will be described clearly and completely below in conjunction with the drawings in the embodiments of the present application. Obviously, the described embodiments are part of the embodiments of the present application, rather than all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.

[0028] The following describes some of the techniques involved in the embodiments of the present application to facilitate understanding, and they should be considered as merely exemplary. Therefore, it should be appreciated by those of ordinary skill in the art that various changes and modifications may be made to the embodiments described herein without departing from the scope and spirit of the present application. Similarly, for the sake of clarity and conciseness, some descriptions of well-known functions and structures are omitted in the following description.

[0029] The present application embodiment provides a pivot table data expansion method, such as Figure 1 As shown, the method includes steps S101 to S108. Among them, Figure 1 This is only an execution order shown in the embodiment of the present application, and does not represent the only execution order of a pivot table data expansion method. If the final result can be achieved, Figure 1 The steps shown may be performed in parallel or reversed.

[0030] S101: Connect the row field value and the column field value of each record in the data source in a preset order, and separate the value of each field with a separator to construct a unique path string.

[0031] Specifically, suppose there is a data source that contains multiple records. Each record contains multiple row fields and multiple column fields. , multiple column fields such as .in, Indicates row fields, Indicates Column fields, and For each record in the data source, you need to connect the row field value and the column field value. Use a delimiter, such as a hyphen. " to separate the values ​​of each field to build a unique path string. For a specific combination in a record, such as , concatenate its field values ​​in order to form a path string, such as .in, For the The row field corresponds to numerical values, For the The mth value corresponding to the column field, and All are integers.

[0032] A hash table is used to store these path strings and their aggregate information, which refers to the result obtained by performing some calculation or operation on a set of data. These calculations or operations can include sum, count, average, maximum value, and minimum value.

[0033] Before connecting the row field value and column field value of each record in the data source in a preset order, the data in the data source is preprocessed. The preprocessing is to replace all non-numeric values ​​in the data source with numeric indexes.

[0034] Specifically, collect data from the data source and ensure the integrity and consistency of the data. Clean the data, including removing duplicate records, handling missing values, correcting erroneous data, etc. Traverse each record in the data source and identify and record all non-numeric values, such as text strings. Assign a unique numeric index to each unique non-numeric value. Create a mapping table to map each non-numeric value to its corresponding numeric index. The assignment of indexes can be arbitrary, but for consistency, they will be assigned in order, such as alphabetical order, order of appearance, etc. Using the mapping table created earlier, replace all occurrences of non-numeric values ​​in the data source with their corresponding numeric indexes. Ensure that the replacement process is consistent, that is, the same non-numeric value is replaced with the same numeric index in the data source. Numeric indexes usually take up less storage space than text strings, which can reduce memory usage and increase calculation speed.

[0035] S102: The constructed path string is used as a key and its corresponding aggregation information is used as a value to store in a hash table, ensuring that each path string in the hash table uniquely corresponds to a set of aggregation information.

[0036] Specifically, a hash table is a data structure that uses key-value pairs to store data. In this application, the path string is used as the key, and the corresponding aggregation information is used as the value. Each path string is unique in the hash table and corresponds only to a specific set of aggregation information, which means that there will be no situation in the hash table where two path strings are the same but the aggregation information is different. When you need to query the aggregation information of a specific path string, you only need to quickly search through the hash table without traversing the entire data set. Using hash tables can manage memory resources more efficiently, especially when dealing with large-scale data sets. Hash tables not only support efficient search operations, but also fast insertion and deletion operations. When you need to update the aggregation information of a path string, you only need to find the corresponding key in the hash table and update its value.

[0037] Furthermore, the hash table is a dynamic hash table that can automatically adjust its capacity to store more path character strings and corresponding aggregation information.

[0038] S103: When the aggregation information of the target path string needs to be queried, the hash table is traversed to determine whether the target path string exists, so as to perform a query or update operation on the hash table and complete the construction of the hash table.

[0039] Figure 2 The embodiment of the present application provides a specific flow chart for performing a query or update operation of the hash table by traversing the hash table to determine whether the target path string exists when the aggregate information of the target path string needs to be queried, such as Figure 2 As shown, it includes step S201 to step S202.

[0040] S201: If the target path character string exists, obtain the aggregation information corresponding to the target path character string to perform a query operation on the hash table.

[0041] Specifically, when the aggregate information of a target path string needs to be queried, it first traverses the hash table. The hash value of the target path string is calculated using a hash function to determine its position in the hash table. If the target path string exists in that position, it checks whether there is a key that matches the target path string. If a matching key is found, the value corresponding to the key is read, which is the aggregate information of the target path string.

[0042] S202: If the target path character string does not exist, insert the target path character string and its corresponding aggregation information into the hash table to perform an update operation on the hash table.

[0043] Specifically, if a key matching the target path string is not found in the hash table, this means that the aggregation information of the path has not been recorded and the target path string does not exist. In this case, an update operation needs to be performed to add a new key-value pair to the hash table. A new slot is allocated to the target path string in the hash table, and the target path string is used as the key and its corresponding aggregation information is used as the value and stored in the slot to perform an update operation on the hash table.

[0044] Through the hash table, the system can quickly locate the aggregate information of the target path string without traversing the entire data set. This greatly improves query efficiency, especially when processing large-scale data sets. When a new path string appears, the corresponding key-value pair can be automatically added to the hash table.

[0045] S104: traverse each key-value pair in the hash table, split the path string into row fields and column fields, and create or search for corresponding nodes in the rendering tree according to the values ​​of these fields, so as to complete the construction of the rendering tree.

[0046] Figure 3 The specific flow chart of traversing each key-value pair in the hash table provided in the embodiment of the present application, splitting the path string into row fields and column fields, and creating or searching for corresponding nodes in the rendering tree according to the values ​​of these fields to complete the construction of the rendering tree is as follows: Figure 3 As shown, it includes steps S301 to S303.

[0047] S301: If the node corresponding to the value of the target field does not exist in the rendering tree, create the node and add it to the rendering tree to perform a corresponding node creation operation.

[0048] Specifically, in the process of traversing the hash table, for each key-value pair, its path string is split into row fields and column fields. Check whether there is a node corresponding to the target field value in the rendering tree. If not, it means that this is a new dimension value or combination. The node needs to be created and added to the rendering tree. This step can ensure that the rendering tree can fully reflect all dimension values ​​in the pivot table, providing a basis for the subsequent display of aggregate information.

[0049] S302: If the node corresponding to the value of the target field exists in the rendering tree, update or merge the aggregation information of the node to perform a corresponding node search operation.

[0050] Specifically, if the node corresponding to the value of the target field exists in the rendering tree, the aggregation information of the node needs to be updated or merged. This usually involves merging the aggregation information corresponding to the key-value pair in the hash table with the current aggregation information of the node in the rendering tree, such as summing or averaging, to reflect the latest aggregation status of the dimension in the pivot table. This step can maintain the accuracy and real-time nature of the aggregation information in the rendering tree, ensuring that the display results of the pivot table are consistent with the actual data.

[0051] S303: After all the key-value pairs in the hash table are traversed, the construction of the rendering tree is completed. The rendering tree is used to reflect the hierarchical relationship and aggregation information of each dimension in the pivot table.

[0052] S105: Initialize an empty two-dimensional array as the basic structure of the pivot table according to the combination of the row field and the column field.

[0053] S106: Starting from the root node of the rendering tree, the entire rendering tree is traversed, and for each traversed node, the specific position of the node in the two-dimensional array is determined according to its path string.

[0054] Specifically, this step ensures that each node can be accurately mapped to the corresponding position of the two-dimensional array, providing accurate positioning for subsequent data filling.

[0055] S107: Fill the aggregation information of the node into the corresponding position of the two-dimensional array to perform a filling operation.

[0056] The filling operation includes: if the node corresponds to the value of multiple fields, fill the value of each field separately. If there is no corresponding node at a certain position in the two-dimensional array, fill it with a null value.

[0057] Specifically, if a node corresponds to the values ​​of multiple fields, for example, a node may contain multiple values ​​of row fields and column fields at the same time, the value of each field needs to be filled separately. This means that the corresponding row and column need to be found in the two-dimensional array according to the path string of the node, and the aggregate information of the node is filled into that position. If there is no corresponding node at a certain position in the two-dimensional array, that is, the corresponding node is not found in the rendering tree at that position, it is filled with a null value. This helps to maintain the structural integrity of the two-dimensional array and clearly indicates which dimension combinations have no data.

[0058] S108: After all nodes of the rendering tree are traversed and all positions of the two-dimensional array are filled, the two-dimensional expansion process of the pivot table is completed.

[0059] Specifically, after completing the two-dimensional expansion process of the pivot table, the two-dimensional array contains the aggregate information under all dimension combinations in the pivot table, which can be used for subsequent data analysis and presentation.

[0060] Figure 4 A schematic diagram of a salesperson's product sales data table provided in an embodiment of the present application. Figure 4 There are 6 salespeople in the data, and their names are Chris, Radow, Peyton, Johnson, Vic, and Ian. There are 5 product types, namely desk, pen, pencil, chair, and notebook. There are 17 rows of sales data, including sales date, sales quantity, unit price, and total sales. There are 4 sales months. Figure 5 A schematic diagram of a monthly product sales summary table for a salesperson provided in an embodiment of the present application.

[0061] According to the following Figure 4 and Figure 5 The data in are used to compare the traditional scheme with the method of this application.

[0062] The specific operation of the traditional solution is as follows.

[0063] Assume that there is a data source containing N rows of records, which form the basis for generating a pivot table. The data source contains all the information needed to generate a pivot table, such as salesperson name, product type, sales month, total sales, etc. Determine the row fields (i) and column fields (j) of the pivot table based on your needs. In this example, the row fields include salesperson (6) and product type (5), and the column field is sales month (4). Calculate the total size of the pivot table data area, which is the product of the number of unique items in the row fields multiplied by the number of unique items in the column fields, that is In this example, the total size of the data area is , that is, 120 cells. Traverse each record in the data source, and find the corresponding pivot table cell based on the salesperson's name, product type, and sales month in the record. For each cell, calculate the sales (or other value field) as needed. For example, suppose you want to fill cell C4, which represents the total sales of chris sold by salesperson Chris in January. In order to get this data, you need to traverse the entire data source (there are 17 records) and find all records that meet the conditions of "salesperson is Chris, month is January, and product is chair". In this example, it is found that only one record meets the conditions, and the sales of this record is 136. Therefore, fill this value into cell C4. Similarly, cell C3 represents the total sales of all products sold by salesperson Chris in January. In order to get this data, you also need to traverse the entire data source, but this time the condition is "salesperson is Chris, month is January". In Figure 4 Find the records that meet the criteria (rows 8, 15, and 16) and add up their sales to get a total of 191.2. Then, fill this value into cell C3. For the data area in the PivotTable, each cell filling requires a traversal and query of the data source. Considering that there are 6 salespeople, 5 product types, 4 months, and 1 sales value field, the total number of query summaries can be calculated. This number is equal to the number of salespeople multiplied by the number of product types multiplied by the number of months multiplied by the number of value fields multiplied by the number of record rows in the data source, that is, , that is, the number of query summaries is 2024. In some cases, some salespeople may not have sold certain products in a particular month. For example, Johnson has not sold a notebook in four months. In this case, you want to remove these invalid data from the pivot table. However, this will increase the complexity of the algorithm because additional traversal and judgment operations are required to determine which data is invalid.

[0064] In summary, the algorithm complexity of generating pivot tables according to the traditional scheme is relatively high. Its complexity can be expressed as . Where N is the number of rows in the data source, k is the number of value fields, is the number of unique entries in the row field, is the number of unique entries in the column field. In this example , which means at least 2040 queries are required. If the step of removing invalid data is included, the complexity of the traditional algorithm may be higher.

[0065] The specific operations of this application are as follows.

[0066] In the initial stage of data processing, it is necessary to traverse the entire data source. This data source contains multiple records, and each record contains four key information, namely the name of the salesperson, product type and sales month, and total sales. Figure 6 Schematic diagram of the constructed hash table provided in the embodiment of the present application. Figure 6 As shown, in order to efficiently manage and query this information, the present application constructs a hash table and names this hash table Pathpool. When constructing Pathpool, the values ​​of the row fields (salesperson's name, product type, and sales month) and the values ​​of the column fields (total sales) in each record are connected in a preset order and separated by a specific delimiter to generate a unique path string. This path string can accurately identify each record in the data source. These path strings are used as keys and their corresponding aggregate information (such as total sales) as values, which are stored in a dynamic hash table. This hash table can automatically adjust its capacity to accommodate the growing amount of data. After traversing all the records, a total of 17 summary queries were performed (because there are 17 records in the data source), and each query involved extracting four information fields, so the total number of executions was 68 times, i.e. . Finally, the hash table contains 16 unique data points, namely different salesperson names, product types, and sales month combinations. In actual operations, there may be data duplication, such as two or more rows of data that are exactly the same, then they will generate the same path string and be considered as the same data point in the hash table. For example, Figure 4 There are 17 rows of data, but there are 16 unique data points. The reason is that the data in rows 16 and 17 are completely repeated, so there are actually only 16 unique data points. It should be noted that there is an upper limit to the number of data points, which will not exceed the product of the number of salespeople (6 people), the number of product types (5 types), and the number of sales months (4 months), that is, 120.

[0067] After the hash table is generated, it is necessary to traverse each data point in it and build a tree structure for rendering, namely the render tree, according to the hierarchical relationship between the salesperson's name, product type and sales month. In the process of building the render tree, the path string is first split into row fields and column fields, and then the corresponding nodes are created or found in the tree according to the values ​​of these fields. If the node corresponding to a field value does not exist in the tree, a new node is created and added to the tree. If the node already exists, the aggregation information of the node is updated or merged. After such processing, the render tree can accurately reflect the hierarchical relationship and aggregation information of each dimension in the pivot table. Figure 7A schematic diagram of a rendering tree constructed according to an embodiment of the present application. Figure 7 As shown in the figure, there are 51 nodes with values ​​in the rendering tree. The number of these nodes is less than the theoretical maximum of 120, because not all combinations of salesperson name, product type, and sales month have corresponding sales records in the data source.

[0068] Finally, the render tree needs to be expanded two-dimensionally and filled onto a worksheet to form a standard pivot table. During the expansion process, the entire tree is traversed starting from the root node of the render tree. For each traversed node, the specific position of the node in the two-dimensional array is determined based on its path string, and the aggregate information of the node is filled into the corresponding position. If a node corresponds to the value of multiple fields, the value of each field is filled separately. If there is no corresponding node at a certain position in the two-dimensional array, it is filled with a null value. Since there are 51 nodes on the render tree, 51 operations are required to expand the tree into a two-dimensional array. When all nodes of the render tree are traversed and all positions in the two-dimensional array are filled, the two-dimensional expansion process of the pivot table is completed.

[0069] In summary, the total number of operations required to generate a pivot table according to the method of this application is 120 times, namely . Among them, 68 is the number of times the hash table is constructed, the first 51 is the number of nodes in the rendering tree, assuming that the construction of each node is regarded as one operation, and the second 51 is the number of operations in the process of expanding the rendering tree into a two-dimensional array. The total number of operations calculated is 120. The complexity of this application can be expressed as .in, Due to the complexity of this application, is the number of rows in the data source, is the number of row fields, is the number of column fields, The number of operations for the other parts, namely, constructing the rendering tree and expanding it into a two-dimensional array, is much lower than the 2040 aggregation queries required by the traditional solution, so the method of the present application has a significant advantage in performance.

[0070] This application avoids the complex row and column combination calculations in traditional methods and reduces the algorithm complexity by connecting the values ​​of the row field and the column field and using a separator to generate a unique path string. The hash table is used to efficiently store and query the target path string and its corresponding aggregate information, so as to achieve fast data access and update, and significantly improve the speed of building a pivot table. The combined use of the hash table and the rendering tree avoids the problem of pre-allocating a large amount of memory in the traditional two-dimensional array, and realizes the dynamic allocation and efficient use of memory. Since the hash table and the rendering tree are both built based on the unique path string, the repeated storage of data is avoided, and the memory usage is further reduced. The dynamic characteristics of the hash table and the rendering tree enable the pivot table to easily respond to data changes and realize real-time data update and query. By building a rendering tree, users can flexibly select the combination of row fields and column fields to perform multi-dimensional data analysis to meet diverse business needs. As the basic structure of the pivot table, the two-dimensional array is easy for users to understand and use, and improves the intuitiveness and ease of use of data analysis. The fast query feature of the hash table enables users to quickly obtain the required data and improve work efficiency. This solves the problem that due to the high complexity of the traditional algorithm, the pivot table expansion task cannot be completed in time in an environment with limited memory and computing resources, thus affecting user experience and business efficiency.

[0071] The present application embodiment also provides a pivot table data expansion device 800, such as Figure 8 As shown, the device includes: a hash table building module 801, a rendering tree building module 802 and an expansion module 803.

[0072] The hash table construction module 801 is used to connect the row field value and the column field value of each record in the data source in a preset order, and use a delimiter to separate the value of each field to construct a unique path string. The constructed path string is used as a key and its corresponding aggregation information is used as a value to store it in the hash table, ensuring that each path string in the hash table uniquely corresponds to a set of aggregation information. When it is necessary to query the aggregation information of the target path string, the hash table is traversed to determine whether the target path string exists, so as to perform a query or update operation on the hash table and complete the construction of the hash table.

[0073] The rendering tree construction module 802 is used to traverse each key-value pair in the hash table, split the path string into row fields and column fields, and create or search for corresponding nodes in the rendering tree according to the values ​​of these fields to complete the construction of the rendering tree.

[0074] The expansion module 803 is used to initialize an empty two-dimensional array as the basic structure of the pivot table according to the combination of the row field and the column field. Starting from the root node of the rendering tree, the entire rendering tree is traversed, and for each traversed node, the specific position of the node in the two-dimensional array is determined according to its path string. The aggregation information of the node is filled into the corresponding position of the two-dimensional array to perform the filling operation. When all nodes of the rendering tree are traversed and all positions of the two-dimensional array are filled, the two-dimensional expansion process of the pivot table is completed.

[0075] Some modules in the apparatus described in the present application can be described in the general context of computer executable instructions executed by a computer, such as program modules. Generally, program modules include routines, programs, objects, components, data structures, classes, etc. that perform specific tasks or implement specific abstract data types. The present application can also be practiced in distributed computing environments, in which tasks are performed by remote processing devices connected through a communication network. In a distributed computing environment, program modules can be located in local and remote computer storage media including storage devices.

[0076] The devices or modules described in the above application embodiments can be implemented by computer chips or entities, or by products with certain functions. For the convenience of description, the above devices are described in various modules according to their functions. When implementing the embodiments of the present application, the functions of each module can be implemented in the same or multiple software and / or hardware. Of course, the module that implements a certain function can also be implemented by combining multiple sub-modules or sub-units.

[0077] The methods, devices or modules described in this application can be implemented in the form of computer-readable program codes. The controller can be implemented in any appropriate manner. For example, the controller can take the form of a microprocessor or processor and a computer-readable medium storing computer-readable program codes (such as software or firmware) that can be executed by the (micro)processor, logic gates, switches, application-specific integrated circuits (English: Application Specific Integrated Circuit, referred to as: ASIC), programmable logic controllers and embedded microcontrollers. Examples of controllers include but are not limited to the following microcontrollers: ARC 625D, Atmel AT91SAM, Microchip PIC18F26K20 and Silicone Labs C8051F320. The memory controller can also be implemented as part of the control logic of the memory. Those skilled in the art also know that in addition to implementing the controller in the form of pure computer-readable program codes, the controller can be implemented in the form of logic gates, switches, application-specific integrated circuits, programmable logic controllers and embedded microcontrollers by logically programming the method steps. Therefore, this controller can be considered as a hardware component, and the devices included in it for implementing various functions can also be regarded as structures within the hardware component. Or even, the means for realizing various functions may be regarded as both a software module for realizing the method and a structure within a hardware component.

[0078] like Fig. 9 As shown, an embodiment of the present application also provides a pivot table data expansion server, including a memory 901 and a processor 902; the memory 901 is used to store computer executable instructions; the processor 902 is used to execute computer executable instructions to implement a pivot table data expansion method described above in an embodiment of the present application.

[0079] The embodiment of the present application further provides a computer-readable storage medium, wherein the computer-readable storage medium stores executable instructions, and when a computer executes the executable instructions, the pivot table data expansion method described above in the embodiment of the present application can be implemented.

[0080] It can be seen from the description of the above implementation methods that those skilled in the art can clearly understand that the present application can be implemented by means of software plus necessary hardware. Based on this understanding, the technical solution of the present application can be essentially or partly contributed to the prior art in the form of a software product, or it can be reflected in the implementation process of data migration. The computer software product can be stored in a storage medium, such as ROM / RAM, a disk, an optical disk, etc., including a number of instructions for a computer device (which can be a personal computer, a mobile terminal, a server, or a network device, etc.) to execute the method described in the embodiment of the present application.

[0081] The various embodiments in this specification are described in a progressive manner, and the same or similar parts between the various embodiments can be referred to each other, and each embodiment focuses on the differences from other embodiments. All or part of this application can be used in many general or special computer system environments or configurations.

[0082] The above embodiments are only used to illustrate the technical solutions of the present application, rather than to limit the present application. Although the present application has been described in detail with reference to the aforementioned embodiments, a person of ordinary skill in the art should understand that the technical solutions described in the aforementioned embodiments may still be modified, or some or all of the technical features thereof may be replaced by equivalents. However, these modifications or replacements do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the present application.

Claims

1. A pivot table data expansion method, characterized in that: include: Connect the row field value and column field value of each record in the data source in a preset order, and separate the value of each field with a delimiter to build a unique path string; The constructed path string is used as the key and the corresponding aggregation information is used as the value to store in the hash table, ensuring that each path string in the hash table uniquely corresponds to a set of aggregation information; When the aggregate information of the target path string needs to be queried, the hash table is traversed to determine whether the target path string exists, so as to perform a query or update operation on the hash table and complete the construction of the hash table; Traverse each key-value pair in the hash table, split the path string into row fields and column fields, and create or find corresponding nodes in the rendering tree according to the values ​​of these fields to complete the construction of the rendering tree; Initialize an empty two-dimensional array as the basic structure of the pivot table based on the combination of row fields and column fields; Starting from the root node of the rendering tree, traverse the entire rendering tree. For each traversed node, determine the specific position of the node in the two-dimensional array based on its path string; Fill the aggregation information of the node into the corresponding position of the two-dimensional array to perform a filling operation; When all nodes of the rendering tree are traversed and all positions of the two-dimensional array are filled, the two-dimensional expansion process of the pivot table is completed.

2. The pivot table data expansion method according to claim 1, characterized in that: Before connecting the row field value and the column field value of each record in the data source in a preset order, it also includes preprocessing the data in the data source; The preprocessing is: replacing all non-numeric values ​​in the data source with numeric indexes.

3. The pivot table data expansion method according to claim 1, characterized in that: When the aggregate information of the target path string needs to be queried, the hash table is traversed to determine whether the target path string exists, so as to perform a query or update operation on the hash table, including: If the target path string exists, obtain the aggregation information corresponding to the target path string to perform a hash table query operation; If the target path string does not exist, the target path string and its corresponding aggregation information are inserted into the hash table to perform an update operation on the hash table.

4. The pivot table data expansion method according to claim 1, characterized in that: The method traverses each key-value pair in the hash table, splits the path string into row fields and column fields, and creates or searches for corresponding nodes in the rendering tree according to the values ​​of these fields to complete the construction of the rendering tree, including: If the node corresponding to the value of the target field does not exist in the rendering tree, create the node and add it to the rendering tree to perform the corresponding node creation operation; If the node corresponding to the value of the target field exists in the rendering tree, update or merge the aggregation information of the node to perform the corresponding node search operation; When all key-value pairs in the hash table are traversed, the construction of the rendering tree is completed; wherein the rendering tree is used to reflect the hierarchical relationship and aggregation information of each dimension in the pivot table.

5. The pivot table data expansion method according to claim 1, characterized in that: The filling operation comprises: If the node corresponds to the value of multiple fields, fill in the value of each field separately; If there is no corresponding node at a certain position in the two-dimensional array, fill it with a null value.

6. The pivot table data expansion method according to claim 1, characterized in that: The hash table is a dynamic hash table that can automatically adjust its capacity to store more path character strings and corresponding aggregation information.

7. A pivot table data expansion device, characterized in that: include: A hash table module is constructed to connect the row field value and the column field value of each record in the data source in a preset order, and use a separator to separate the value of each field to construct a unique path string; the constructed path string is used as a key and the corresponding aggregation information is used as a value to store it in the hash table, ensuring that each path string in the hash table uniquely corresponds to a set of aggregation information; When the aggregate information of the target path string needs to be queried, the hash table is traversed to determine whether the target path string exists, so as to perform a query or update operation on the hash table and complete the construction of the hash table; Build a rendering tree module, which is used to traverse each key-value pair in the hash table, split the path string into row fields and column fields, and create or find corresponding nodes in the rendering tree according to the values ​​of these fields to complete the construction of the rendering tree; The expansion module is used to initialize an empty two-dimensional array as the basic structure of the pivot table according to the combination of row fields and column fields; start from the root node of the rendering tree, traverse the entire rendering tree, and for each traversed node, determine the specific position of the node in the two-dimensional array according to its path string; fill the aggregation information of the node into the corresponding position of the two-dimensional array to perform the filling operation; when all nodes of the rendering tree are traversed and all positions of the two-dimensional array are filled, the two-dimensional expansion process of the pivot table is completed.

8. A pivot table data expansion server, characterized in that: including memory and processor; The memory is used to store computer executable instructions; The processor is used to execute the computer executable instructions to implement the method according to any one of claims 1 to 6.

9. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores executable instructions, and when a computer executes the executable instructions, the method according to any one of claims 1 to 6 can be implemented.

Images