Column-to-row aggregation calculation method and device for distributed database, equipment and medium

Abstract

The invention relates to the technical field of databases, and discloses a column-to-row aggregation calculation method for a distributed database, which comprises the following steps: acquiring a target data table and a UNPIVOT query statement, and analyzing the target data table and the UNPIVOT query statement to obtain a UNPIVOT semantic object; the UNPIVOT semantic object is analyzed, an NULL value filtering strategy is generated, and a logic execution plan is generated through the NULL value filtering strategy and the sub-query statement; and executing the sub-query statements according to the logic execution plan through a physical execution layer to generate narrow table data. According to the method, a target data table and a UNPIVOT query statement are analyzed, metadata are extracted and packaged into semantic objects, a logic execution plan is generated through an NULL value filtering strategy and sub-query, results are executed and merged by relying on operators and a calculation framework multiplexed by a physical layer, wide tables are efficiently converted into narrow tables, data redundancy is eliminated, and the aggregation calculation function of a distributed database is improved.

Description

Technical Field

[0001] This invention relates to the field of database technology, and in particular to a method, apparatus, device, and storage medium for column-to-row aggregation calculation in a distributed database. Background Technology

[0002] In existing technologies, business data analysis often relies on wide tables that store similar data in multiple columns (such as "January sales" and "February sales" being separate columns). This type of data structure has inherent problems: First, it has high data redundancy, with similar dimensional information scattered across multiple columns, failing to form a unified dimensional management system. Second, it has poor analytical adaptability, making it impossible to directly filter or aggregate by core dimensions such as "month" (e.g., sum by month). Additional processing is required to meet the needs of subsequent data cleaning, dimension alignment, and aggregation analysis, reducing the efficiency of data analysis.

[0003] While column-to-row (unpivot) transformations are widely used in mainstream single-machine databases and tools like Excel, the distributed database field lacks mature implementations for column-to-row aggregation calculations. It's impossible to directly reuse the unpivot functionality of single-machine databases, nor has a dedicated implementation adapted to distributed architectures (such as distributed storage and distributed computing frameworks) been developed. This makes it difficult for distributed databases to balance functional compatibility, execution efficiency, and architectural adaptability when handling wide table transformations, thus failing to meet the data analysis needs of distributed scenarios. Summary of the Invention

[0004] The main objective of this invention is to provide a column-to-row aggregation calculation method, apparatus, device, and storage medium for distributed databases, aiming to solve the problems of redundant similar data in wide tables and inconvenience in dimension-based filtering and aggregation in distributed databases.

[0005] To achieve the above objectives, the present invention provides a column-to-row aggregation calculation method for distributed databases, comprising: Obtain the target data table and the UNPIVOT query statement, analyze the target data table and the UNPIVOT query statement to obtain the UNPIVOT semantic object; The UNPIVOT semantic object is parsed to generate a NULL value filtering strategy, and a logical execution plan is generated using the NULL value filtering strategy and subquery statements. The physical execution layer executes subquery statements according to the logical execution plan to generate narrow table data.

[0006] Furthermore, to achieve the above objectives, the present invention provides a column-to-row aggregation computing device for a distributed database, comprising: The syntax parsing module is used to obtain the target data table and the UNPIVOT query statement, analyze the target data table and the UNPIVOT query statement, and obtain the UNPIVOT semantic object. The logic transformation module is used to parse the UNPIVOT semantic object, generate a NULL value filtering strategy, and generate a logic execution plan through the NULL value filtering strategy and subquery statements. The execution adaptation module is used to execute subquery statements according to the logical execution plan through the physical execution layer to generate narrow table data.

[0007] Furthermore, to achieve the above objectives, the present invention also provides a computer device, the computer device including a memory, a processor, and a column-to-row aggregation calculation program for a distributed database stored in the memory and executable on the processor, wherein when the column-to-row aggregation calculation program for the distributed database is executed by the processor, it implements the steps of the column-to-row aggregation calculation method for the distributed database as described above.

[0008] Furthermore, to achieve the above objectives, the present invention also provides a computer-readable storage medium storing a column-to-row aggregation calculation program for a distributed database, wherein when the column-to-row aggregation calculation program for the distributed database is executed by a processor, it implements the steps of the column-to-row aggregation calculation method for the distributed database as described above.

[0009] Beneficial Effects: This invention relates to the field of database technology and can be applied to business system platforms in communications, healthcare, and fintech. It discloses a column-to-row aggregation calculation method for distributed databases, comprising: obtaining a target data table and an UNPIVOT query statement; analyzing the target data table and the UNPIVOT query statement to obtain a UNPIVOT semantic object; parsing the UNPIVOT semantic object to generate a NULL value filtering strategy; generating a logical execution plan through the NULL value filtering strategy and subqueries; and executing the subqueries according to the logical execution plan through the physical execution layer to generate narrow table data. This invention parses the target data table and the UNPIVOT query statement to extract metadata and encapsulate it into a semantic object. It generates a logical execution plan through a NULL value filtering strategy and subqueries, and executes and merges the results using operators and a computational framework reused at the physical layer. This efficiently achieves wide-table to narrow-table conversion, eliminates data redundancy, and improves the aggregation calculation function of distributed databases. Attached Figure Description

[0010] The present invention will be further described below with reference to the accompanying drawings and embodiments. In the accompanying drawings: Figure 1 This is a schematic diagram of an application environment for a column-to-row aggregation calculation method for a distributed database according to an embodiment of the present invention; Figure 2 This is a flowchart illustrating an embodiment of the column-to-row aggregation calculation method for distributed databases according to the present invention. Figure 3 A schematic diagram of the functional modules of a preferred embodiment of the column-to-row aggregation computing device for the distributed database of the present invention; Figure 4 This is a schematic diagram of the structure of a computer device according to an embodiment of the present invention; Figure 5 This is another structural schematic diagram of a computer device according to one embodiment of the present invention. Detailed Implementation

[0011] It should be understood that the specific embodiments described herein are for illustrative purposes only and are not intended to limit the scope of the invention.

[0012] The column-to-row aggregation calculation method for distributed databases provided in this invention can be applied to applications such as... Figure 1 In this application environment, the user terminal communicates with the server via a network. The server can obtain the target data table and UNPIVOT query statement from the user terminal, analyze the target data table and UNPIVOT query statement to obtain UNPIVOT semantic objects, parse the UNPIVOT semantic objects to generate a NULL value filtering strategy, and generate a logical execution plan through the NULL value filtering strategy and subqueries. The physical execution layer executes the subqueries according to the logical execution plan to generate narrow table data. This invention parses the target data table and UNPIVOT query statement to extract metadata and encapsulate it into semantic objects. A logical execution plan is generated through a NULL value filtering strategy and subqueries. The results are executed and merged based on operators and computational frameworks reused at the physical layer, efficiently converting wide tables to narrow tables, eliminating data redundancy, and improving the distributed database aggregation computation function. The user terminal can be, but is not limited to, various personal computers, laptops, smartphones, tablets, and portable wearable devices. The server can be implemented using a standalone server or a server cluster composed of multiple servers. The invention will be described in detail below through specific embodiments.

[0013] Please see Figure 2 , Figure 2 This is a flowchart illustrating an embodiment of the column-to-row aggregation calculation method for a distributed database provided by the present invention. It should be noted that although the logical order is shown in the flowchart, in some cases, the steps shown or described may be performed in a different order than that shown here.

[0014] like Figure 2 As shown, the column-to-row aggregation calculation method for distributed databases proposed in this invention includes the following steps: S100: Obtain the target data table and UNPIVOT query statement, analyze the target data table and UNPIVOT query statement to obtain the UNPIVOT semantic object; S200. Parse the UNPIVOT semantic object to generate a NULL value filtering strategy, and generate a logical execution plan using the NULL value filtering strategy and subquery statements. S300: The subquery statement is executed according to the logical execution plan through the physical execution layer to generate narrow table data.

[0015] In this embodiment, the first step is to define the target data table and the corresponding UNPIVOT query statement, which forms the basis for all subsequent processing. The target data table is a table that stores the original wide table data. The characteristic of a wide table is that it distributes the same type of data across multiple columns (for example, sales figures for different quarters are treated as separate columns). The UNPIVOT query statement is an SQL statement used to convert this type of wide table into a narrow table. Its core function is to extract key data and reshape the data format.

[0016] Next, the target data table and the UNPIVOT query statement are analyzed to obtain the UNPIVOT semantic object. The parser recursively traverses the Abstract Syntax Tree (AST, a tree structure formed after parsing the SQL statement to represent its syntactic structure), accurately extracting the core metadata of the UNPIVOT structure. This includes columns to be retained (columns that do not participate in the transformation and retain their original identifiers), columns to be transformed (original columns that need to be converted from columns to rows), dimension columns (columns that store the original column names and serve as new data dimensions), value columns (columns that store the original column values ​​and their corresponding dimension data), and an alias mapping set (the table aliases, column aliases, and other correspondences defined in the query statement). This extracted metadata is uniformly encapsulated into a UNPIVOT semantic object and stored in the context information of the logical query plan, providing data support for subsequent processing.

[0017] Then, the UNPIVOT semantic object is parsed to generate a NULL value filtering strategy, and a logical execution plan is generated by combining it with subquery statements. During the generation of the logical execution plan, the kernel's logical plan optimizer triggers the "UNPIVOT to UNION ALL" transformation logic. It reads the metadata from the UNPIVOT semantic object and splits it into multiple subquery statements according to the column-to-row rule: each subquery corresponds to a column to be transformed, using the column name as the value of the dimension column and the value of the column to be transformed as the value of the value column, while embedding the conditional statement corresponding to the NULL value filtering strategy (such as "WHERE column to be transformed IS NOT NULL"). Afterwards, the UNION ALL operator (an SQL operator used to merge multiple query result sets without deduplication) is used to connect all the subquery statements, replacing the original UNPIVOT operator, to form the final logical execution plan.

[0018] Finally, the physical execution layer executes the subquery statements according to the logical execution plan, generating narrow table data. The physical execution layer reuses existing UNION ALL operators, aggregation calculation frameworks, and conditional calculation frameworks in the distributed database, eliminating the need to develop entirely new execution modules and reducing scalability complexity. During execution, each subquery statement is executed separately, filtering out rows that match the NULL value filtering strategy. Then, the UNION ALL operator is used to combine the results of all subqueries, ultimately outputting a narrow table with a unified structure. The narrow table is presented in the form of "retained columns + dimension columns + value columns," with each row corresponding to a column from the original wide table, achieving the goal of "converting a wide table into a narrow table," facilitating subsequent data analysis and aggregation calculations.

[0019] In one embodiment, prior to S100, the following is included: S001, Predefine the UNPIVOT syntax rule template; S002. Add an UNPIVOT syntax parsing module to the SQL parser of the distributed database; S003. Configure the UNPIVOT syntax parsing module with the strategy for identifying reserved columns, the syntax positioning strategy for dimension columns and value columns, and the alias mapping identification strategy.

[0020] In this embodiment, before analyzing the target data table and UNPIVOT query statements to obtain the UNPIVOT semantic object, a series of preparatory work needs to be completed to ensure the smoothness and accuracy of the subsequent parsing and analysis process. First, a UNPIVOT syntax rule template needs to be predefined. This template must clearly define the standard structure and writing specifications of the UNPIVOT query statement, covering the order and syntax format requirements of key clauses such as identifier columns, dimension columns, value columns, columns to be transformed, and include / exclude nulls. This ensures that the template is compatible with the UNPIVOT function syntax of mainstream databases and adapts to the kernel framework of distributed databases, providing a unified judgment basis for subsequent syntax parsing.

[0021] Next, an UNPIVOT syntax parsing module is added to the SQL parser (the core component used to parse the syntax structure of SQL statements) of the distributed database. This step extends the functionality of the existing parser, aiming to enable the parser to recognize and process UNPIVOT clauses, avoiding the problem that query statements cannot be parsed due to the original parser's lack of support for this syntax. This module will serve as an independent and dedicated parsing unit, undertaking the task of recognizing UNPIVOT-related syntax.

[0022] Finally, a series of key identification strategies are configured in the newly added UNPIVOT syntax parsing module. Among them, the column retention strategy is used to accurately filter columns in the query statement that do not need to undergo column-to-row transformation and must retain their original identifier attributes, clarifying their positional characteristics and syntactic identifiers in the statement; the dimension column and value column syntax positioning strategy specifies how to distinguish and locate dimension columns (new columns storing the original column names) and value columns (new columns storing the original column values) in the statement, clarifying their corresponding syntactic keywords, naming rules, and relationships with the columns to be transformed; the alias mapping identification strategy is used to identify the definition rules and correspondences of table aliases and column aliases in the statement, ensuring accurate association between original column names and user-defined aliases, avoiding confusion during subsequent metadata extraction. Through the configuration of these strategies, the UNPIVOT syntax parsing module can efficiently and accurately extract key information according to preset rules, laying the foundation for the subsequent generation of UNPIVOT semantic objects.

[0023] In one embodiment, S100 includes: S101. The SQL parser recursively traverses the abstract syntax tree to obtain the target data table and the corresponding UNPIVOT query statement. S102. Parse the UNPIVOT query statement to determine the columns to be converted; S103. Filter and retain columns from the columns of the target data table; S104. Determine the set of dimension columns, value columns, and alias mappings based on the UNPIVOT query statement; S105. Extract data from the retained columns, columns to be transformed, dimension columns, value columns, and alias mapping set, and verify the extracted data based on the UNPIVOT syntax rule template to generate metadata; S106. Encapsulate the metadata to obtain an UNPIVOT semantic object, and store the UNPIVOT semantic object in the context information of the logical query plan.

[0024] In this embodiment, during the process of obtaining the UNPIVOT semantic object, the SQL parser first recursively traverses the Abstract Syntax Tree (AST, a tree structure formed after parsing SQL statements, which can clearly present the syntactic logic of the statement and the relationship between its parts). Through the traversal process, the target data table (the table that stores the original wide table data) and the corresponding UNPIVOT query statement (the SQL statement used to convert the wide table to a narrow table) are accurately located and obtained.

[0025] Next, the SQL parser performs a deep analysis of the obtained UNPIVOT query statement. Based on predefined UNPIVOT syntax rules, it identifies the columns that need to be converted from columns to rows—these columns are usually multiple independent columns in the target data table that store similar data. Simultaneously, it filters and retains columns from all columns in the target data table. These retained columns are those that do not need to participate in the column-to-row conversion and must retain their original identifying attributes. They are used to uniquely identify the origin of each record in the converted data.

[0026] Subsequently, based on the syntax structure of the UNPIVOT query statement, we determined the dimension columns (new columns that store the original column names after transformation, used to reflect the classification dimensions of the data), value columns (new columns that store the original column values ​​after transformation, corresponding to the specific data of the dimension columns), and alias mapping set (containing the correspondence between table aliases, column aliases and original names in the query statement, to ensure the accuracy of data association).

[0027] After identifying the key elements mentioned above, data is extracted from the retained columns, columns to be transformed, dimension columns, value columns, and alias mapping sets. The extraction process strictly follows the syntax rules and data format requirements. Then, the extracted data is validated using a predefined UNPIVOT syntax rule template to check whether the format of each element is compliant, whether the associations are reasonable, and whether there are any missing or incorrect elements, ensuring the accuracy and completeness of the data. Once the validation is successful, standardized metadata is generated.

[0028] Finally, the verified metadata is uniformly encapsulated to form a structured UNPIVOT semantic object, which centrally carries all the core information required for column-to-row transformation. Simultaneously, this UNPIVOT semantic object is stored in the context information of the logical query plan, providing directly accessible data support for subsequent logical transformations and physical execution.

[0029] In one embodiment, S200 includes: S201. Read the UNPIVOT semantic object stored in the context information; S202. Parse the reserved columns, columns to be transformed, dimension columns, value columns and alias mapping sets in the UNPIVOT semantic object to generate a NULL value filtering strategy. S203. Generate a corresponding subquery statement for each column to be transformed; S204. Generate a logical execution plan based on the NULL value filtering strategy and the subquery statement.

[0030] In this embodiment, firstly, the logical plan optimizer reads the UNPIVOT semantic object stored in the logical query plan context information. This object contains all the core metadata required for column-to-row transformation and is the foundation for subsequent parsing and plan generation. Next, it performs in-depth parsing of the retained columns, columns to be transformed, dimension columns, value columns, and alias mapping set in the UNPIVOT semantic object, focusing on extracting the instructions regarding null value handling in the UNPIVOT query statement. If the statement specifies "EXCLUDE NULLS" (exclude null values), the generated NULL value filtering strategy is to "filter out rows with NULL values ​​in the columns to be transformed"; if "INCLUDE NULLS" (include null values) is specified, the filtering strategy is to "retain rows with NULL values ​​in the columns to be transformed," thus ensuring that the null value handling logic is consistent with the semantics of the original query.

[0031] Subsequently, a corresponding subquery statement is generated for each column to be transformed. Each subquery statement will contain a reserved column (keeping the original identifier unchanged), a dimension column (using the column name of the current column to be transformed as the dimension value), and a value column (using the value of the current column to be transformed as the data of the corresponding dimension). At the same time, it will reference the correspondence in the alias mapping set to ensure the consistency between column names and table names.

[0032] Finally, the NULL value filtering strategy is embedded into each corresponding subquery statement. This involves adding a NULL value filtering condition to the WHERE clause (such as "WHERE column to be transformed IS NOT NULL" or omitting this condition to retain NULL values), and then using the UNION ALL operator (an SQL operator used to merge multiple query result sets without automatic deduplication) to connect all subqueries, replacing the original UNPIVOT operator and forming a complete logical execution plan. This plan clarifies the execution logic of each subquery, the NULL value filtering rules, and the result merging method, providing a clear execution basis for the physical execution layer and ensuring that the final output conforms to the column-to-row semantics.

[0033] In one embodiment, S203 specifically includes: S2031. Obtain the column name of the column to be transformed as the value of the dimension column; S2032. Obtain the column value of the column to be converted as the value of the value column; S2033. Generate a corresponding subquery statement for each column to be transformed based on the values ​​of the retained column, dimension column, and value column.

[0034] In this embodiment, when generating a corresponding subquery statement for each column to be transformed, the column name of the current column to be transformed is first accurately obtained and used directly as the value of the dimension column. The core function of the dimension column is to record the classification dimension of the data. Through the column name of the column to be transformed, the original attribute corresponding to the subsequent value column data can be clearly identified (for example, if the column name to be transformed is "q1-sales", then the value of the dimension column is "q1-sales", clearly corresponding to the first quarter sales volume dimension). Next, the specific data content stored in the column to be transformed is extracted and used as the value of the value column. The value column and the dimension column correspond one-to-one, and are used to carry the actual business data under that dimension (for example, the specific sales volume value in the "q1-sales" column will be used as the value column data corresponding to that dimension). Finally, based on the retained column (the retained column maintains the original identification function to ensure the uniqueness of each data record), combined with the previously determined values ​​of the dimension column and the value column, a subquery statement is constructed separately for each column to be transformed according to the standard format of the SQL query statement. Each subquery statement will fully include the retained columns, the dimension columns with the column names to be transformed as values, and the value columns with the data of the column to be transformed as values. This ensures that a single subquery can independently output the "identifier-dimension-value" structured data corresponding to the column to be transformed, preparing for subsequent merging of result sets and completion of column-to-row transformation.

[0035] In one embodiment, S204 specifically includes: S2041. If the UNPIVOT query statement specifies a null value exclusion strategy, then add a filtering condition that the data value of the column to be converted is not NULL in the corresponding subquery statement, and then connect all subquery statements through the UNION ALL operator to generate a logical execution plan. S2042. If the UNPIVOT query statement specifies a null value inclusion strategy, then there is no need to add filtering conditions to the subquery statement. All subquery statements are connected by the UNION ALL operator to generate a logical execution plan.

[0036] In this embodiment, when generating the logical execution plan based on the NULL value filtering strategy and subquery statements, the core is to process each subquery statement specifically according to the NULL value handling rules explicitly specified in the UNPIVOT query statement, and then merge them into a complete plan in a unified manner. If the UNPIVOT query statement specifies an "exclude NULL values" (EXCLUDENULLS, i.e., filter out records with NULL values ​​in the column to be transformed) strategy, then in each corresponding subquery statement, a "the data value of the column to be transformed is not NULL" filter condition will be added through the WHERE clause to ensure that only rows with valid data in the column to be transformed will be retained. After adding the filter conditions for all subquery statements, the UNION ALL operator (a set merging operator in SQL used to directly merge multiple query result sets without removing duplicate records, ensuring data integrity) is used to connect all the processed subquery statements in sequence to form the final logical execution plan.

[0037] If the UNPIVOT query specifies an "INCLUDE NULLS" strategy (i.e., retaining records where the data value in the column to be transformed is NULL), then no additional filtering conditions need to be added to each subquery statement; the original query structure of "retained column + dimension column + value column" is directly retained. Then, the UNION ALL operator is used to connect all subqueries without filtering conditions, ensuring that records containing NULL values ​​are also included in the result set, ultimately generating a logical execution plan that meets the NULL value retention requirement. Both scenarios' logical execution plans clearly define the execution rules of the subqueries and the result merging method, providing a clear execution basis for the physical execution layer and ensuring that the output results are completely consistent with the original semantics of the UNPIVOT query.

[0038] In one embodiment, S300 includes: S301, the physical execution layer reuses the UNION ALL operator, aggregation calculation framework and conditional calculation framework of the distributed database; S302. The physical execution layer executes each subquery statement according to the logical execution plan, and combines the aggregation calculation framework to group and aggregate the generated subquery results to generate an aggregate result set for each subquery statement. S303. The UNION ALL operator is used to merge all aggregation result sets to obtain narrow table data.

[0039] In this embodiment, during the physical execution phase of generating narrow table data, the core is to rely on the existing mature execution framework of the distributed database to efficiently implement the logical execution plan. The physical execution layer directly reuses the existing UNION ALL operator (a core component used to merge multiple result sets without removing duplicate data), aggregation calculation framework (an execution module responsible for data grouping statistics and aggregation operations), and conditional calculation framework (an execution module that handles filtering conditions and data validation in queries) in the distributed database. There is no need to develop new execution components, which reduces the complexity of technical expansion and ensures the stability and compatibility of the execution process.

[0040] Subsequently, the physical execution layer executes each subquery statement one by one according to the order and rules specified in the logical execution plan. During execution, in conjunction with the aggregation calculation framework, based on the grouping logic implicit in the subquery statement (using the retained columns as the core grouping basis to ensure orderly association of dimension data under the same identifier), the original results generated by each subquery are grouped and aggregated, filtering out data that does not meet the conditions (if the logical execution plan includes null value filtering conditions, the condition calculation framework will work together to complete the null value filtering), and finally generating a structured aggregation result set corresponding to each subquery. Each result set is presented in the format of "retained columns + dimension columns + value columns" to ensure the standardization of individual subquery results.

[0041] Finally, the UNION ALL operator is used to completely merge the aggregated result sets of all subqueries. This operator retains all records in the result set as is, without deduplication, ensuring data integrity and original semantic consistency. The merged result set is no longer the original wide table structure, but rather a unified conversion of similar data originally scattered across multiple columns into a multi-row narrow table format of "one identifier + one dimension + one value," which is the final generated narrow table data. This format can directly adapt to subsequent data analysis, dimension filtering, and other business needs.

[0042] In one embodiment, a column-to-row aggregation calculation device for a distributed database is provided, which corresponds one-to-one with the column-to-row aggregation calculation method for a distributed database described in the above embodiments. (Refer to...) Figure 3 , Figure 3This is a schematic diagram of the functional modules of a preferred embodiment of the column-to-row aggregation computing device for the distributed database of the present invention. The modules include a syntax parsing module 10, a logic conversion module 20, and an execution adaptation module 30. Detailed descriptions of each functional module are as follows: The syntax parsing module 10 is used to obtain the target data table and the UNPIVOT query statement, analyze the target data table and the UNPIVOT query statement, and obtain the UNPIVOT semantic object. The logic transformation module 20 is used to parse the UNPIVOT semantic object, generate a NULL value filtering strategy, and generate a logic execution plan through the NULL value filtering strategy and subquery statements. The execution adaptation module 30 is used to execute subquery statements according to the logical execution plan through the physical execution layer to generate narrow table data.

[0043] In one embodiment, the parser extension module includes: Predefine UNPIVOT syntax rule templates; Add an UNPIVOT syntax parsing module to the SQL parser of the distributed database; Configure the UNPIVOT syntax parsing module with a strategy for identifying retained columns, syntax location strategies for dimension columns and value columns, and alias mapping identification strategies.

[0044] In one embodiment, the syntax parsing module 10 includes: The SQL parser recursively traverses the abstract syntax tree to obtain the target data table and the corresponding UNPIVOT query statement. The UNPIVOT query statement is parsed to determine the columns to be transformed; Filter and retain columns from the columns of the target data table; The UNPIVOT query statement determines the set of dimension columns, value columns, and alias mappings. Data is extracted from the retained columns, columns to be transformed, dimension columns, value columns, and alias mapping set, and the extracted data is validated based on the UNPIVOT syntax rule template to generate metadata; The metadata is encapsulated to obtain an UNPIVOT semantic object, and the UNPIVOT semantic object is stored in the context information of the logical query plan.

[0045] In one embodiment, the logic conversion module 20 includes: Read the UNPIVOT semantic object stored in the context information; Parse the reserved columns, columns to be transformed, dimension columns, value columns, and alias mapping sets in the UNPIVOT semantic object to generate a NULL value filtering strategy; Generate a corresponding subquery statement for each column to be transformed; A logical execution plan is generated based on the NULL value filtering strategy and subquery statements.

[0046] In one embodiment, generating a corresponding subquery statement for each column to be transformed specifically includes: Obtain the column name of the column to be transformed as the value of the dimension column; Get the column values ​​of the column to be transformed as the values ​​of the value column; Based on the values ​​of the retained columns, dimension columns, and value columns, generate a corresponding subquery statement for each column to be transformed.

[0047] In one embodiment, generating a logical execution plan based on the NULL value filtering strategy and the subquery statement specifically includes: If the UNPIVOT query statement specifies a null value exclusion strategy, then add a filter condition that the data value of the column to be transformed is not NULL in the corresponding subquery statement, and then connect all subquery statements using the UNION ALL operator to generate a logical execution plan; If the UNPIVOT query statement specifies a null value inclusion strategy, then there is no need to add filtering conditions to the subquery statement. All subquery statements are connected by the UNION ALL operator to generate a logical execution plan.

[0048] In one embodiment, the execution adaptation module 30 includes: The physical execution layer reuses the UNION ALL operator, aggregation computation framework, and conditional computation framework of distributed databases; The physical execution layer executes each subquery statement according to the logical execution plan, and combines the aggregate computing framework to group and aggregate the generated subquery results to generate an aggregate result set for each subquery statement; The UNION ALL operator is used to merge all aggregation result sets to obtain narrow table data.

[0049] In one embodiment, a computer device is provided, which may be a server, and its internal structure diagram may be as follows: Figure 4As shown, the computer device includes a processor, memory, network interface, and database connected via a system bus. The processor provides computational and control capabilities. The memory includes non-volatile and / or volatile storage media and internal memory. The non-volatile storage media stores the operating system, computer programs, and database. The internal memory provides an environment for the operation of the operating system and computer programs in the non-volatile storage media. The network interface is used for communication with external user terminals via a network connection. When the computer program is executed by the processor, it implements the functions or steps of a column-to-row aggregation calculation method for a distributed database on the server side.

[0050] In one embodiment, a computer device is provided, which may be a user terminal, and its internal structure diagram may be as follows: Figure 5 As shown, the computer device includes a processor, memory, network interface, display screen, and input devices connected via a system bus. The processor provides computing and control capabilities. The memory includes non-volatile storage media and internal memory. The non-volatile storage media stores the operating system and computer programs. The internal memory provides an environment for the operation of the operating system and computer programs in the non-volatile storage media. The network interface is used to communicate with an external server via a network connection. When the computer program is executed by the processor, it implements the user-side functions or steps of a column-to-row aggregation calculation method for a distributed database. In one embodiment, a computer device is provided, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to perform the following steps: Obtain the target data table and the UNPIVOT query statement, analyze the target data table and the UNPIVOT query statement to obtain the UNPIVOT semantic object; The UNPIVOT semantic object is parsed to generate a NULL value filtering strategy, and a logical execution plan is generated using the NULL value filtering strategy and subquery statements. The physical execution layer executes subquery statements according to the logical execution plan to generate narrow table data.

[0051] In one embodiment, a computer-readable storage medium is provided having a computer program stored thereon, the computer program performing the following steps when executed by a processor: Obtain the target data table and the UNPIVOT query statement, analyze the target data table and the UNPIVOT query statement to obtain the UNPIVOT semantic object; The UNPIVOT semantic object is parsed to generate a NULL value filtering strategy, and a logical execution plan is generated using the NULL value filtering strategy and subquery statements. The physical execution layer executes subquery statements according to the logical execution plan to generate narrow table data.

[0052] It should be noted that the functions or steps that can be implemented by the computer-readable storage medium or computer device described above can be referred to the relevant descriptions on the server side and user side in the foregoing method embodiments. To avoid repetition, they will not be described one by one here.

[0053] Those skilled in the art will understand that all or part of the processes in the methods of the above embodiments can be implemented by a computer program instructing related hardware. The computer program can be stored in a non-volatile computer-readable storage medium, and when executed, it can include the processes of the embodiments of the above methods. Any references to memory, storage, databases, or other media used in the embodiments provided in this 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. By way of illustration and not limitation, RAM is available in various forms, such as static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), dual data rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), synchronous link DRAM (SLDRAM), Rambus direct RAM (RDRAM), direct memory bus dynamic RAM (DRDRAM), and memory bus dynamic RAM (RDRAM), etc.

[0054] Those skilled in the art will clearly understand that, for the sake of convenience and brevity, the above-described division of functional units and modules is used as an example. In practical applications, the above functions can be assigned to different functional units and modules as needed, that is, the internal structure of the device can be divided into different functional units or modules to complete all or part of the functions described above.

[0055] It should be noted that if any software tools or components not belonging to this company appear in the embodiments of this application, they are merely illustrative examples and do not represent actual use. The embodiments described above are only used to illustrate the technical solutions of the present invention, and not to limit them; although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features; and these modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of the present invention, and should all be included within the protection scope of the present invention.

Claims

1. A column-to-row aggregation calculation method for a distributed database, characterized in that, Includes the following steps: Obtain the target data table and the UNPIVOT query statement, analyze the target data table and the UNPIVOT query statement to obtain the UNPIVOT semantic object; The UNPIVOT semantic object is parsed to generate a NULL value filtering strategy, and a logical execution plan is generated using the NULL value filtering strategy and subquery statements. The physical execution layer executes subquery statements according to the logical execution plan to generate narrow table data.

2. The column-to-row aggregation calculation method for distributed databases as described in claim 1, characterized in that, Before obtaining the target data table and the UNPIVOT query statement, and analyzing the target data table and the UNPIVOT query statement to obtain the UNPIVOT semantic object, the process includes: Predefine UNPIVOT syntax rule templates; Add an UNPIVOT syntax parsing module to the SQL parser of the distributed database; Configure the UNPIVOT syntax parsing module with a strategy for identifying retained columns, syntax location strategies for dimension columns and value columns, and alias mapping identification strategies.

3. The column-to-row aggregation calculation method for distributed databases as described in claim 1, characterized in that, The process of obtaining the target data table and the UNPIVOT query statement, analyzing the target data table and the UNPIVOT query statement to obtain the UNPIVOT semantic object includes: The SQL parser recursively traverses the abstract syntax tree to obtain the target data table and the corresponding UNPIVOT query statement. The UNPIVOT query statement is parsed to determine the columns to be transformed; Filter and retain columns from the columns of the target data table; The UNPIVOT query statement determines the set of dimension columns, value columns, and alias mappings. Data is extracted from the retained columns, columns to be transformed, dimension columns, value columns, and alias mapping set, and the extracted data is validated based on the UNPIVOT syntax rule template to generate metadata; The metadata is encapsulated to obtain an UNPIVOT semantic object, and the UNPIVOT semantic object is stored in the context information of the logical query plan.

4. The column-to-row aggregation calculation method for distributed databases as described in claim 1, characterized in that, The process of parsing the UNPIVOT semantic object to generate a NULL value filtering strategy, and generating a logical execution plan using the NULL value filtering strategy and subquery statements, includes: Read the UNPIVOT semantic object stored in the context information; Parse the reserved columns, columns to be transformed, dimension columns, value columns, and alias mapping sets in the UNPIVOT semantic object to generate a NULL value filtering strategy; Generate a corresponding subquery statement for each column to be transformed; A logical execution plan is generated based on the NULL value filtering strategy and subquery statements.

5. The column-to-row aggregation calculation method for distributed databases as described in claim 4, characterized in that, The step of generating a corresponding subquery statement for each column to be transformed specifically includes: Obtain the column name of the column to be transformed as the value of the dimension column; Get the column values ​​of the column to be transformed as the values ​​of the value column; Based on the values ​​of the retained columns, dimension columns, and value columns, generate a corresponding subquery statement for each column to be transformed.

6. The column-to-row aggregation calculation method for distributed databases as described in claim 4, characterized in that, The step of generating a logical execution plan based on the NULL value filtering strategy and subquery statements specifically includes: If the UNPIVOT query statement specifies a null value exclusion strategy, then add a filter condition that the data value of the column to be transformed is not NULL in the corresponding subquery statement, and then connect all subquery statements using the UNION ALL operator to generate a logical execution plan; If the UNPIVOT query statement specifies a null value inclusion strategy, then there is no need to add filtering conditions to the subquery statement. All subquery statements are connected by the UNION ALL operator to generate a logical execution plan.

7. The column-to-row aggregation calculation method for distributed databases as described in claim 1, characterized in that, The process of executing subquery statements according to the logical execution plan through the physical execution layer to generate narrow table data includes: The physical execution layer reuses the UNION ALL operator, aggregation computation framework, and conditional computation framework of distributed databases; The physical execution layer executes each subquery statement according to the logical execution plan, and combines the aggregate computing framework to group and aggregate the generated subquery results to generate an aggregate result set for each subquery statement; The UNION ALL operator is used to merge all aggregation result sets to obtain narrow table data.

8. A column-to-row aggregation computing device for a distributed database, characterized in that, The column-to-row aggregation computing device for the distributed database includes: The syntax parsing module is used to obtain the target data table and the UNPIVOT query statement, analyze the target data table and the UNPIVOT query statement, and obtain the UNPIVOT semantic object. The logic transformation module is used to parse the UNPIVOT semantic object, generate a NULL value filtering strategy, and generate a logic execution plan through the NULL value filtering strategy and subquery statements. The execution adaptation module is used to execute subquery statements according to the logical execution plan through the physical execution layer to generate narrow table data.

9. A computer device, characterized in that, The computer device includes a memory, a processor, and a column-to-row aggregation calculation program for a distributed database stored in the memory and executable on the processor. When the distributed database column-to-row aggregation calculation program is executed by the processor, it implements the steps of the column-to-row aggregation calculation method for a distributed database as described in any one of claims 1-7.

10. A computer-readable storage medium, characterized in that, The storage medium stores a column-to-row aggregation calculation program for a distributed database. When the distributed database column-to-row aggregation calculation program is executed by the processor, it implements the steps of the distributed database column-to-row aggregation calculation method as described in any one of claims 1-7.

Images