Row / column conversion method and device, server and storage medium

Abstract

The invention provides a row / column conversion method and device, a server and a storage medium, the method adopts an iterative nested conversion strategy to automatically convert a plurality of parallel row / column conversion operations into a hierarchical nested structure, the execution sequence is clarified, and optimization by an optimizer is facilitated. A layered condition aggregation expression construction mechanism is adopted, multi-dimensional condition judgment is efficiently generated through a unified logic expression combination rule, and the readability and maintainability of a logic plan are improved. A combination mode of a virtual table and Cartesian product connection is adopted, and the function of column-to-row conversion is efficiently realized through one-time table scanning and one Cartesian product connection operation. In the query rewriting stage, the used columns are automatically identified and removed from the projection list, so that the data transmission quantity of unnecessary columns is reduced.

Description

Technical Field

[0001] This application relates to the field of database management system technology, and in particular to a row / column conversion method, apparatus, server, and storage medium. Background Technology

[0002] In scenarios such as data warehousing, business intelligence, reporting, ETL, and data science, wide table to long table (UNPIVOT) and long table to wide table (PIVOT) are frequently used data reorganization operations. PIVOT typically transforms several enumerated values ​​in the dimension value space into columns and aggregates them for a specified metric; UNPIVOT expands several columns into multiple rows according to the axis dimension and outputs the corresponding axis values ​​and metrics.

[0003] In existing technologies, an expression is typically constructed for each combination of Pivot value and each aggregate function to complete the PIVOT operation. The UNPIVOT operation is achieved by using Union All to combine multiple SELECT statements, with each SELECT statement corresponding to an Unpivot column.

[0004] The existing technologies described above have the following problems: When the number of Pivot columns and Pivot values ​​is large, using traditional nested loop construction will generate a large number of intermediate objects, affecting memory efficiency and subsequent optimization. Multiple scans of the source table result in significant performance degradation when the number of columns involved in Unpivot is large. Furthermore, after Pivot and Unpivot operations, the existing logical plan may contain redundant columns that have already been consumed, leading to unnecessary data transfer and computational overhead. Summary of the Invention

[0005] This application provides a row / column conversion method, apparatus, server, and storage medium to solve the problems in the prior art where traditional nested loop constructions generate a large number of intermediate objects, affecting memory efficiency and subsequent optimization; when Unpivot involves a large number of columns, performance degrades significantly; and after Pivot and Unpivot operations, the existing logical plan may contain redundant columns that have already been consumed, leading to unnecessary data transfer and computational overhead.

[0006] Firstly, this application provides a row / column conversion method, including: For the target table in the database to be formatted, obtain a Structured Query Language (SQL) statement. The SQL statement carries the conversion data of the target table, which includes Pivot clause information and Unpivot clause information. Based on the Pivot clause information and the Unpivot clause information, an intermediate subquery structure is constructed, a projection list is obtained, and an iterative nested transformation process is performed based on the projection list and the intermediate subquery structure to construct a logical plan tree; An aggregate expression is invoked to perform a row-to-column transformation on specified data in the target table, wherein the aggregate expression is constructed using the Pivot value in the Pivot clause information; The logical plan structure is invoked to perform a column-to-row operation on specified data in the target table. The logical plan structure is constructed using the Unpivot value in the Unpivot clause information. Perform deduplication on the target table after performing row-to-column and row-to-row operations.

[0007] In one possible implementation, the step of constructing an intermediate subquery structure based on the Pivot clause information and the Unpivot clause information, obtaining a projection list, and performing iterative nested transformation processing based on the projection list and the intermediate subquery structure to construct a logical plan tree includes: The Pivot clause information and the Unpivot clause information are converted into an ordered sequence; Based on the first N-1 transformation operations in the ordered sequence, construct an intermediate subquery structure; Obtain the projection list for the Nth transformation operation of the ordered sequence; The logical plan tree is constructed by performing iterative nested transformations based on the projection list and the intermediate subquery structure.

[0008] In one possible implementation, the invocation of the aggregate expression to perform a row-to-column transformation on specified data in the target table includes: When the transformation data in the SQL statement includes Pivot clause information, a binary equality expression is constructed based on the Pivot value in the Pivot clause information; The binary equality expressions are combined to construct a unified aggregate expression, and a target name is set for the aggregate expression based on the Pivot information.

[0009] In one possible implementation, when the transformation data in the SQL statement includes Pivot clause information, constructing a binary equality expression based on the Pivot value in the Pivot clause information includes: When the transformation data in the SQL statement includes Pivot clause information, obtain the Pivot value; Obtain the multidimensional value corresponding to the Pivot value based on the Pivot value; Based on the multidimensional value corresponding to the Pivot value, construct the binary equality expression corresponding to the multidimensional value.

[0010] In one possible implementation, the invocation logic plan structure performs a column-to-row operation on specified data in the target table, including: When the transformation data in the SQL statement includes Unpivot clause information, row data is constructed based on the Unpivot value, and a virtual table is constructed based on the Unpivot value and the row data; Obtain the original table corresponding to the Unpivot information, and perform a join operation on the original table and the virtual table to obtain a chain-like logical plan structure.

[0011] In one possible implementation, when the transformation data in the SQL statement includes Unpivot clause information, constructing row data based on the Unpivot value, and constructing a virtual table based on the Unpivot value and the row data, includes: When the transformation data in the SQL statement includes Unpivot clause information, obtain the Unpivot value; For each Unpivot value, construct the row data corresponding to that Unpivot value; A virtual table is constructed based on the Unpivot value and the row data, the data of the virtual table is encapsulated, and a unique table name is generated for the virtual table.

[0012] In one possible implementation, the deduplication process performed on the target table after performing row-to-column and row-to-row operations includes: Obtain the target column set based on the logical plan tree, and perform a binding operation on the target column set using a projection expression; The projection list is traversed. When the target column name in the projection list is the same as the target column name in the target column set, deduplication is performed and the target column is deleted from the projection list.

[0013] Secondly, this application provides a row / column conversion device, comprising: The acquisition module is used to acquire a Structured Query Language (SQL) statement for a target table in the database that needs to be formatted. The SQL statement carries the conversion data of the target table, and the conversion data includes Pivot clause information and Unpivot clause information. The construction module is used to construct an intermediate subquery structure based on the Pivot clause information and the Unpivot clause information, obtain a projection list, and perform iterative nested transformation processing based on the projection list and the intermediate subquery structure to construct a logical plan tree; The first execution module is used to call an aggregate expression to perform a row-to-column transformation operation on specified data in the target table, wherein the aggregate expression is constructed using the Pivot value in the Pivot clause information; The second execution module is used to call a logical plan structure to perform a column-to-row operation on specified data in the target table. The logical plan structure is constructed using the Unpivot value in the Unpivot clause information. The deduplication module is used to perform deduplication on the target table after performing row-to-column and row-to-row operations.

[0014] Thirdly, this application provides a server, including a processor and a memory, wherein the processor is configured to execute a row / column conversion program stored in the memory to implement the row / column conversion method described in any one of the first aspects.

[0015] Fourthly, this application provides a storage medium storing one or more programs that can be executed by one or more processors to implement the row / column conversion method described in any of the first aspects.

[0016] Compared with the prior art, the technical solution provided in this application has the following advantages: The method provided in this application obtains a Structured Query Language (SQL) statement for a target table in a database to be formatted, the SQL statement carrying conversion data of the target table, the conversion data including Pivot clause information and Unpivot clause information; constructs an intermediate subquery structure based on the Pivot clause information and the Unpivot clause information, obtains a projection list, performs iterative nested conversion processing based on the projection list and the intermediate subquery structure to construct a logical plan tree; calls an aggregate expression to perform row-to-column conversion on specified data in the target table, wherein the aggregate expression is constructed using the Pivot value in the Pivot clause information; calls the logical plan structure to perform column-to-row conversion on specified data in the target table, the logical plan structure being constructed using the Unpivot value in the Unpivot clause information; and performs deduplication processing on the target table after performing row-to-column and row-to-column conversion operations. Transforming complex parallel syntax into easily understandable nested syntax clarifies the execution order, facilitating optimization by the optimizer; it reduces the creation of intermediate objects and memory usage, improving the readability and maintainability of the logical plan; it simplifies multiple table scans into a single table scan, making it easier for the optimizer to perform predicate pushdown and other optimizations; it reduces unnecessary column data transfer, and the automated optimization process requires no manual user intervention, improving the user experience. Attached Figure Description

[0017] The accompanying drawings, which are incorporated in and form part of this specification, illustrate embodiments consistent with this application and, together with the description, serve to explain the principles of this application.

[0018] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, for those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0019] One or more embodiments are illustrated by way of example with reference numerals in the accompanying drawings. These illustrations do not constitute a limitation on the embodiments. Elements with the same reference numerals in the drawings are denoted as similar elements. Unless otherwise stated, the figures in the drawings are not to be limited by scale.

[0020] Figure 1 A flowchart illustrating an embodiment of a row / column conversion method provided in this application; Figure 2 A flowchart illustrating an embodiment of another row / column conversion method provided in this application; Figure 3 A block diagram illustrating an embodiment of a row / column conversion device provided in this application; Figure 4 This is a schematic diagram of the structure of a server provided in an embodiment of this application. Detailed Implementation

[0021] To make the objectives, technical solutions, and advantages of the embodiments of this application clearer, the technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, not all embodiments. Based on the embodiments of this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0022] The following disclosure provides numerous different embodiments or examples for implementing various structures of this application. To simplify the disclosure, specific examples of components and arrangements are described below. These are merely examples and are not intended to limit the scope of this application. Furthermore, reference numerals and / or letters may be repeated in different examples. Such repetition is for simplification and clarity and does not in itself indicate a relationship between the various embodiments and / or arrangements discussed.

[0023] To address the technical issues in existing technologies, such as the generation of numerous intermediate objects in traditional nested loop constructions impacting memory efficiency and subsequent optimization; significant performance degradation with a large number of columns involved in Unpivot; and the potential for redundant columns to be consumed after Pivot and Unpivot operations, leading to unnecessary data transfer and computational overhead, this application provides a row / column transformation method. This method transforms complex parallel syntax into easily understandable nested syntax, clarifies the execution order, and facilitates optimization by the optimizer. It reduces the creation of intermediate objects and memory usage, improving the readability and maintainability of the logical plan. It simplifies multiple table scans into a single table scan, facilitating subsequent optimizations such as predicate pushdown by the optimizer. Furthermore, it reduces unnecessary column data transfer, and the automated optimization process eliminates the need for manual user intervention, improving the user experience.

[0024] Figure 1 This is a flowchart illustrating an embodiment of a row / column conversion method provided in this application. Figure 1 As shown, the method includes the following steps: S101. For the target table in the database to be formatted, obtain the Structured Query Language (SQL) statement. The SQL statement carries the conversion data of the target table, including Pivot clause information and Unpivot clause information.

[0025] This application applies to the implementation of row-to-column and column-to-row methods in databases. By constructing logical plans and rewriting queries, the database system can support row-to-column and column-to-row operations, as well as complex chained row-to-column transformations. It also allows users to use multiple Pivot / Unpivot operations consecutively in a single SQL statement, thereby improving logical plan quality and query execution performance.

[0026] In this embodiment, a target table requiring format conversion is determined within the database. A corresponding Structured Query Language (SQL) statement is obtained based on the target table. The SQL statement is parsed to extract the conversion data of the target table contained within it. This conversion data may include Pivot clause information and Unpivot clause information. The obtained conversion data is stored; for example, the extracted conversion data results can be stored in the corresponding Pivot execution plan and Unpivot execution plan.

[0027] In one example, the target table requiring format conversion is identified within the database. The corresponding Structured Query Language (SQL) statement is then retrieved based on this target table. For instance, in the SQL statement "select col,.. from table pivot ()unpivot ()", Pivot and Unpivot are syntactically parallel, but their semantics require a clear execution order. Treating them as parallel directly would lead to semantic ambiguity. Therefore, the SQL statement needs to be parsed to extract the converted data of the target table contained within it, facilitating subsequent iterative nested conversion processing.

[0028] S102. Based on the Pivot clause information and Unpivot clause information, construct an intermediate subquery structure, obtain a projection list, and perform iterative nested transformation processing based on the projection list and the intermediate subquery structure to construct a logical plan tree.

[0029] In this embodiment, an ordered sequence is constructed based on the obtained Pivot and Unpivot clause information. This ordered sequence is traversed, and an intermediate subquery structure is constructed based on the transformation operations within it. This intermediate subquery structure serves as the basis for subsequent query operations, ensuring that each transformation operation correctly references the required column names during execution. This process guarantees that all involved column names are completely and accurately recorded and referenced during the rewriting phase, thereby ensuring subsequent query optimization and execution. For the last transformation operation in the ordered sequence, the projection list in the user-specified SELECT clause can be obtained; that is, only the columns explicitly requested by the user in the query can be selected, instead of selecting all columns from the target table.

[0030] Furthermore, based on the above intermediate subquery structure, the nesting relationship between each intermediate subquery is clarified, and iterative nesting transformation is performed according to the projection list and the above nesting relationship to construct a logical plan tree.

[0031] S103. Call the aggregate expression to perform row-to-column transformation on the specified data in the target table. The aggregate expression is constructed using the Pivot value in the Pivot clause information.

[0032] In this embodiment, during the Pivot operation, for each combination of Pivot value and each aggregate function, an expression of the form "agg(CASE WHEN col1=val1 AND col2=val2 AND ... THENsource_column ELSE NULL END)" needs to be constructed. When the number of Pivot columns and Pivot values ​​is large, using traditional nested loop construction will generate a large number of intermediate objects, affecting memory efficiency and subsequent optimization. Therefore, a hierarchical conditional aggregation expression construction mechanism is adopted, which efficiently generates multi-dimensional conditional judgments through unified logical expression combination rules.

[0033] Specifically, a row-to-column transformation is performed on specified data in the target table by invoking an aggregate expression. This aggregate expression is constructed using the Pivot value from the Pivot clause information. A basic binary equality expression is built based on the multi-dimensional values ​​corresponding to each Pivot value in the Pivot clause information. This binary equality expression is then combined using a binary logical AND expression to construct the aggregate expression. By invoking the aggregate expression to perform the row-to-column transformation on specified data in the target table, the creation of intermediate objects and memory usage are reduced.

[0034] S104. Call the logical plan structure to perform column-to-row operation on the specified data in the target table. The logical plan structure is constructed using the Unpivot value in the Unpivot clause information.

[0035] In this embodiment, the Unpivot operation requires converting data from multiple columns into multiple rows using a column-to-row conversion operation. Existing methods typically use Union All to combine multiple SELECT statements, with each SELECT statement corresponding to an Unpivot column, resulting in multiple scans of the source table. When the number of columns involved in Unpivot is large, performance degrades significantly. Therefore, this application employs a combination of virtual tables and Cartesian product joins to efficiently achieve the column-to-row conversion function through a single table scan and a Cartesian product join operation.

[0036] Specifically, the logical plan structure is invoked to perform a column-to-row transformation on specified data in the target table. This logical plan structure is constructed using the Unpivot values ​​in the Unpivot clause information. Each Unpivot value in the Unpivot clause information is used to construct corresponding row data. A virtual table is then built based on each Unpivot value and the Unpivot clause information, and this virtual table is used to transform the column data into row data. Finally, a Cartesian product join operation is used to connect the original table and the virtual table, and a projection layer is constructed, resulting in a chained logical plan structure. By invoking the logical plan structure to perform the column-to-row transformation on specified data in the target table, multiple table scans are simplified to a single table scan.

[0037] S105. Perform deduplication on the target table after performing row-to-column and row-to-row operations.

[0038] In this application, after Pivot and Unpivot operations, some columns in the original table (such as the Group By column of Pivot or the source column of Unpivot) are logically consumed and should not appear in the final projection result. However, the existing logical plan may still contain these redundant columns, leading to unnecessary data transfer and computational overhead. Therefore, in the query rewrite stage, this application performs deduplication processing on the target table after performing row-to-column and row-to-row operations, automatically identifying the columns that have been used and removing them from the projection list.

[0039] Specifically, based on the logic tree obtained above, a "use column set" is maintained. This use column set can contain all column names explicitly used in the Unpivot operation. The use column set is bound and traversed using projection expressions. If a column name exists in the use column set (case-insensitive), deduplication is performed, and it is removed from the projection list.

[0040] The technical solution provided in this application involves obtaining a Structured Query Language (SQL) statement for a target table in a database to be formatted. The SQL statement carries conversion data for the target table, including Pivot clause information and Unpivot clause information. Based on the Pivot and Unpivot clause information, an intermediate subquery structure is constructed to obtain a projection list. Iterative nested conversion processing is then performed based on the projection list and the intermediate subquery structure to construct a logical plan tree. An aggregate expression is invoked to perform a row-to-column conversion on specified data in the target table, where the aggregate expression is constructed using the Pivot value in the Pivot clause information. The logical plan structure is then invoked to perform a column-to-row conversion on specified data in the target table, where the logical plan structure is constructed using the Unpivot value in the Unpivot clause information. Finally, deduplication is performed on the target table after the row-to-column and row-to-column conversion operations. Transforming complex parallel syntax into easily understandable nested syntax clarifies the execution order, facilitating optimization by the optimizer; it reduces the creation of intermediate objects and memory usage, improving the readability and maintainability of the logical plan; it simplifies multiple table scans into a single table scan, making it easier for the optimizer to perform predicate pushdown and other optimizations; it reduces unnecessary column data transfer, and the automated optimization process requires no manual user intervention, improving the user experience.

[0041] Figure 2 A flowchart illustrating an embodiment of another row / column conversion method provided in this application. Figure 2 The process shown includes the following steps: S201. For the target table in the database that needs to be formatted, obtain the Structured Query Language (SQL) statement.

[0042] This application applies to the implementation of row-to-column and column-to-row methods in databases. By constructing logical plans and rewriting queries, the database system can support row-to-column and column-to-row operations, as well as complex chained row-to-column transformations. It also allows users to use multiple Pivot / Unpivot operations consecutively in a single SQL statement, thereby improving logical plan quality and query execution performance.

[0043] In this embodiment, a target table requiring format conversion is determined within the database. A corresponding Structured Query Language (SQL) statement is obtained based on the target table. The SQL statement is parsed to extract the conversion data of the target table contained within it. This conversion data may include Pivot clause information and Unpivot clause information. The obtained conversion data is stored; for example, the extracted conversion data results can be stored in the corresponding Pivot execution plan and Unpivot execution plan.

[0044] In one example, for the target table in the database to be formatted, the Structured Query Language (SQL) statement is obtained. Taking a basic SQL statement, select a1,b1 from t1 pivot (...) unpivot(...), the SQL statement is parsed to extract the Pivot and Unpivot clause information (such as column names, aggregation information, and other necessary data) and the results are stored in the corresponding Pivot and Unpivot execution plans.

[0045] S202. Convert the Pivot clause information and Unpivot clause information into an ordered sequence.

[0046] In this embodiment of the application, after obtaining the Pivot clause information and Unpivot clause information, an ordered sequence can be constructed according to the order in which each Pivot and Unpivot conversion operation appears in the SQL statement. This ordered sequence stores the Pivot clause information and Unpivot clause information and reflects the order in which the conversion operations occur during the query execution process. It also provides accurate information for subsequent logical plan construction and query optimization.

[0047] In one example, based on the Pivot and Unpivot clause information contained in the SQL statement obtained above, an ordered sequence is constructed. This ordered sequence can be an array of the form [pivot, unpivot, pivot...].

[0048] S203. Construct an intermediate subquery structure based on the first N-1 transformation operations in the ordered sequence.

[0049] In this embodiment, the obtained ordered sequence is traversed, and each transformation operation is accessed sequentially starting from the beginning of the ordered sequence. During the access process, each transformation operation is analyzed, and the parallel logical plan in the SQL statement is modified into a subquery statement. Then, for the first N-1 transformation operations in the ordered sequence, the corresponding intermediate subquery structure is constructed. Each intermediate subquery includes: the base relation (LogicalProject + LogicalPivot), the current row and column transformation operation, and a SELECT * projection layer, which is used to pass all intermediate results.

[0050] In one example, taking a basic SQL statement `select a1,b1 from t1 pivot (...)unpivot(....)` as an example, we iterate through the ordered sequence obtained above and modify the parallel logic plan in the SQL statement into a subquery statement: `select a1,b1 from (select * from t1 pivot (...))unpivot(....)`. Then, for the first N-1 transformation operations in this ordered sequence, we construct the corresponding intermediate subquery structure.

[0051] S204. Obtain the projection list for the Nth transformation operation of the ordered sequence.

[0052] In this embodiment, after constructing the aforementioned intermediate subquery structure, the Nth transformation operation (the last transformation operation) in the ordered sequence is determined. For the Nth transformation operation, a projection list can be obtained from the user-specified SELECT clause. By using the user-specified projection list, only the columns explicitly requested by the user in the query are selected, rather than selecting all columns from the table, avoiding unnecessary intermediate columns in the final result. This avoids unnecessary data processing and transmission, improving the overall query performance and accuracy.

[0053] S205. Perform iterative nested transformation processing based on the projection list and intermediate subquery structure to construct a logical plan tree.

[0054] In this embodiment, the nesting relationship between each intermediate subquery is clearly defined based on the aforementioned intermediate subquery structure. According to this nesting relationship, the intermediate subqueries are iteratively nested and transformed in an order from the inside out. The innermost subquery is processed first, and its result serves as the input to the outer subquery to construct a logical plan tree. This logical plan tree reflects the execution logic of the entire SQL statement, including the dependencies and execution order between each subquery.

[0055] In one example, taking three parallel transformation operations as an example, the transformation process is iteratively nested based on the projection list and the intermediate subquery structure, resulting in: select a1,b1 from (select * from (select * from t1 pivot (...))unpivot (...)) unpivot(...) The result of the first pivot clause is used as the lookup table for the next unpivot clause.

[0056] S206. When the transformation data in the SQL statement includes Pivot clause information, obtain the Pivot value.

[0057] S207. Obtain the multidimensional value corresponding to the Pivot value based on the Pivot value.

[0058] The following is a unified discussion of S206-S207.

[0059] In this embodiment, when the transformation data in the SQL statement includes Pivot clause information, it is necessary to perform a row-to-column transformation operation on the specified data in the target table. Specifically, each Pivot value is obtained based on the Pivot clause information. For each Pivot value, its corresponding multidimensional value is extracted. This multidimensional value can be the value of other columns corresponding to each value in the Pivot column. Each Pivot value and its corresponding multidimensional value are recorded and stored for subsequent processing and analysis.

[0060] S208. Based on the multidimensional values ​​corresponding to the Pivot values, construct binary equality expressions corresponding to the multidimensional values.

[0061] S209. Combine binary equality expressions to construct a unified aggregate expression, and set a target name for the aggregate expression based on the Pivot information.

[0062] The following is a unified discussion of S208-S209.

[0063] In this embodiment, a binary equality expression corresponding to the multidimensional value corresponding to the obtained Pivot value is constructed. Specifically, after obtaining the multidimensional value corresponding to the Pivot value, a basic binary equality expression is constructed based on each dimension (i.e., each column) of the Pivot value: EqualTo(columnI, valueI), where columnI represents the column corresponding to that dimension, and valueI represents the value corresponding to that dimension.

[0064] Furthermore, the binary equality expressions above are combined using binary logical AND expressions to construct a unified aggregate expression. Specifically, this combined condition can be used as the predicate of the WHEN clause to create a WhenClause object. Finally, a unified CaseWhen expression is constructed for all WhenClause objects. After construction, a target name is set for the aggregate expression based on the Pivot information. This name can be a combination of the Pivot value alias and the aggregate function alias.

[0065] In one example, consider a pivot clause: select col from t1 pivot (sum(c1) as sumc1 for cnt in (1 as n1, 2 asn2)) The main function of the pivot clause is to rotate the row values ​​of table t1 by cnt into columns, calculate the sum of c1 when cnt=1 and cnt=2 respectively, and output two columns (corresponding to the aggregation results of n1 and n2) n1_sumc1 and n2_sumc1, along with the original grouping column. To achieve this function, an equivalent execution plan is used to replace the currently generated pivot execution plan, that is, to replace it with an equivalent statement: select col, sum(case when cnt = 1 then c1 end) as n1_sumc1,sum(casewhen cnt = 2 then c1 end) as n2_sumc1 from t1 group by col.

[0066] S210. When the transformation data in the SQL statement includes Unpivot clause information, obtain the Unpivot value.

[0067] S211. Construct the row data corresponding to each Unpivot value.

[0068] The following is a unified discussion of S210-S211.

[0069] In this embodiment, when the transformation data in the SQL statement includes Unpivot clause information, it is necessary to perform a column-to-row transformation operation on the specified data in the target table. Specifically, each Unpivot value is obtained based on the Unpivot clause information; this Unpivot value can be an UnPivotValue object. For each Unpivot value, the corresponding row data is constructed. This row data may include the Unpivot value and the source information corresponding to that Unpivot value.

[0070] S212. Construct a virtual table based on the Unpivot value and row data, encapsulate the data of the virtual table, and generate a unique table name for the virtual table.

[0071] In this embodiment, a virtual table is constructed based on the obtained Unpivot values ​​and row data. Specifically, an M×N virtual table can be constructed for an Unpivot operation containing M UnpivotValues ​​and N Unpivot target columns. This virtual table is used to convert column data into row data. For the generated virtual table, the InlineTable logical plan node can be used to encapsulate the virtual table data and generate a unique table name to ensure its global uniqueness and avoid conflicts with user table names. This table name can be an alias for a UUID.

[0072] In one example, let's take an unpivot SQL statement as an example: select * from mf_shops unpivot (sales for shop in (shop1, shop2)) The main function of this SQL statement is to expand the columns shop1 and shop2 in the table mf_shops into multiple rows, generate a column sales to store the value of the corresponding store and a column shop to indicate the original column name, while retaining the other original columns.

[0073] The original table can be: | item_id | year | shop1 | shop2 | +---------+------+-------+-------+ |1 | 2021 |100 |200 |1 | 2020 |100 |200 | |2 | 2021 |400 |300 | |2 | 2020 |300 |400 | Based on the Unpivot value and row data obtained above, a virtual table can be constructed, which can be: | item_id | year | shop| sales | +---------+------+-------+-------+ |1 | 2021 | shop1 |100 | |1 | 2020 | shop1 |100 | |2 | 2021 | shop1 |400 | |2 | 2020 | shop1 |300 | |1 | 2021 | shop2 |200 | |1 | 2020 | shop2 |200 | |2 | 2021 | shop2 |300 | |2 | 2020 | shop2 | 400 | The encapsulation process mainly involves constructing ('shop1') and ('shop2') into a virtual table plan node using parameters, which can then be connected to the original table and the virtual table via the CROSS JOIN operation.

[0074] S213. Obtain the original table corresponding to the Unpivot information, perform a join operation on the original table and the virtual table to obtain a chain-like logical plan structure.

[0075] In this embodiment, after constructing the virtual table, the original table corresponding to the Unpivot information is obtained, and a join operation is performed on the original table and the virtual table. The CROSS JOIN operation can be used to join the original table and the virtual table, and a projection layer is constructed. The purpose of using the CROSS JOIN operation to join the original table and the virtual table is to expand the data rows, splitting each row of data into multiple rows in the original table. That is, CROSS JOIN copies each row of the original table K times (K is the number of rows in the virtual table), generating all possible axis value candidate rows for each source row, forming a candidate set of "each source row × each axis value".

[0076] Furthermore, after performing a join operation on the original table and the virtual table, a CaseWhen expression is constructed, and logical filter logical plan nodes are built through the isnot null predicate condition, ultimately forming a chain-like logical plan structure.

[0077] S214. Obtain the target column set based on the logical plan tree, and perform a binding operation on the target column set using the projection expression.

[0078] In this embodiment, the target column set is obtained based on the LogicalUnPivot and LogicalPivot logical plan nodes in the aforementioned logical plan tree. This target column set can be a "UsedColumn Set," containing all column names explicitly used in the Unpivot operation. During the rewrite phase, the target column set is bound using the projection expressions of the Pivot and UnPivot nodes to ensure the integrity and accuracy of the column names. The projection expression can be the result column to be displayed or a result expression. Through the binding operation, the relevant attributes of these columns and whether these columns are required can be determined.

[0079] S215. Traverse the projection list. When the target column name in the projection list is the same as the target column name in the target column set, perform deduplication and delete the target column from the projection list.

[0080] In this embodiment, after the binding operation, the column names of the projection list are traversed. When a target column name in the projection list is duplicated with a target column name in the target column set, deduplication is performed, and the target column is deleted from the projection list. The deduplicated projection list is then applied to the LogicalPivot and LogicalUnPivot nodes (rewriting the Project layer of the logical plan) to reduce unnecessary column data transmission, thereby obtaining an optimized logical plan.

[0081] Figure 2 The illustrated process provides an alternative method for row / column transformation, employing an iterative nested transformation strategy to automatically convert multiple parallel row / column transformation operations into a hierarchical nested structure. This clarifies the execution order and facilitates optimizer processing. A hierarchical conditional aggregation expression construction mechanism is used, generating multi-dimensional conditional judgments efficiently through unified logical expression combination rules, improving the readability and maintainability of the logical plan. A combination of virtual tables and Cartesian product joins efficiently achieves column-to-row transformation with a single table scan and a Cartesian product join operation. During the query rewrite phase, columns that have been used are automatically identified and removed from the projection list, reducing unnecessary column data transfer.

[0082] Figure 3 This is a block diagram illustrating an embodiment of a row / column conversion device provided in this application. Figure 3 As shown, the device includes: The acquisition module 301 is used to acquire a Structured Query Language (SQL) statement for a target table in the database to be formatted. The SQL statement carries the conversion data of the target table, and the conversion data includes Pivot clause information and Unpivot clause information. The construction module 302 is used to construct an intermediate subquery structure based on the Pivot clause information and the Unpivot clause information, obtain a projection list, and perform iterative nested transformation processing based on the projection list and the intermediate subquery structure to construct a logical plan tree; The first execution module 303 is used to call an aggregate expression to perform a row-to-column transformation operation on specified data in the target table, wherein the aggregate expression is constructed using the Pivot value in the Pivot clause information; The second execution module 304 is used to call a logical plan structure to perform a column-to-row operation on specified data in the target table. The logical plan structure is constructed using the Unpivot value in the Unpivot clause information. The deduplication module 305 is used to perform deduplication on the target table after performing row-to-column and row-to-row operations.

[0083] In one possible implementation, the construction module 302 is specifically used to convert the Pivot clause information and the Unpivot clause information into an ordered sequence; construct an intermediate subquery structure based on the first N-1 transformation operations in the ordered sequence; obtain a projection list for the Nth transformation operation of the ordered sequence; and perform iterative nested transformation processing based on the projection list and the intermediate subquery structure to construct a logical plan tree.

[0084] In one possible implementation, the first execution module 303 is specifically configured to, when the transformation data in the SQL statement includes Pivot clause information, construct a binary equality expression based on the Pivot value in the Pivot clause information; combine the binary equality expressions to construct a unified aggregate expression; and set a target name for the aggregate expression based on the Pivot information.

[0085] In one possible implementation, the first execution module 303 is specifically configured to: obtain a Pivot value when the transformation data in the SQL statement includes Pivot clause information; obtain a multidimensional value corresponding to the Pivot value based on the Pivot value; and construct a binary equality expression corresponding to the multidimensional value based on the multidimensional value corresponding to the Pivot value.

[0086] In one possible implementation, the second execution module 304 is specifically configured to, when the transformation data in the SQL statement includes Unpivot clause information, construct row data based on the Unpivot value, construct a virtual table based on the Unpivot value and the row data; obtain the original table corresponding to the Unpivot information, and perform a join operation on the original table and the virtual table to obtain a chain-like logical plan structure.

[0087] In one possible implementation, the second execution module 304 is specifically configured to: obtain an Unpivot value when the transformation data in the SQL statement includes Unpivot clause information; construct row data corresponding to each Unpivot value; construct a virtual table based on the Unpivot value and the row data; encapsulate the data of the virtual table; and generate a unique table name for the virtual table.

[0088] In one possible implementation, the deduplication module 305 is specifically used to obtain the target column set according to the logical plan tree, perform a binding operation on the target column set using a projection expression, traverse the projection list, and when the target column name in the projection list is the same as the target column name in the target column set, perform deduplication processing and delete the target column from the projection list.

[0089] like Figure 4 As shown in the figure, this application provides a device including a processor 411, a communication interface 412, a memory 413, and a communication bus 414, wherein the processor 411, the communication interface 412, and the memory 413 communicate with each other through the communication bus 414. Memory 413 is used to store computer programs; In one embodiment of this application, when the processor 411 executes a program stored in the memory 413, it implements the row / column conversion method provided in any of the foregoing method embodiments, including: For the target table in the database to be formatted, a Structured Query Language (SQL) statement is obtained. This SQL statement carries the conversion data for the target table, including Pivot clause information and Unpivot clause information. Based on the Pivot and Unpivot clause information, an intermediate subquery structure is constructed, and a projection list is obtained. Iterative nested conversion processing is performed based on the projection list and the intermediate subquery structure to construct a logical plan tree. An aggregate expression is invoked to perform a row-to-column conversion on specified data in the target table, where the aggregate expression is constructed using the Pivot value from the Pivot clause information. The logical plan tree is then invoked to perform a column-to-row conversion on specified data in the target table, where the logical plan tree is constructed using the Unpivot value from the Unpivot clause information. Finally, deduplication is performed on the target table after the row-to-column and row-to-column conversion operations.

[0090] In one possible implementation, the Pivot clause information and the Unpivot clause information are converted into an ordered sequence; an intermediate subquery structure is constructed based on the first N-1 transformation operations in the ordered sequence; a projection list is obtained for the Nth transformation operation of the ordered sequence; and iterative nested transformation processing is performed based on the projection list and the intermediate subquery structure to construct a logical plan tree.

[0091] In one possible implementation, when the transformation data in the SQL statement includes Pivot clause information, a binary equality expression is constructed based on the Pivot value in the Pivot clause information; the binary equality expressions are combined to construct a unified aggregate expression, and a target name is set for the aggregate expression based on the Pivot information.

[0092] In one possible implementation, when the transformation data in the SQL statement includes Pivot clause information, the Pivot value is obtained; the multidimensional value corresponding to the Pivot value is obtained based on the Pivot value; and a binary equality expression corresponding to the multidimensional value is constructed based on the multidimensional value corresponding to the Pivot value.

[0093] In one possible implementation, when the transformation data in the SQL statement includes Unpivot clause information, row data is constructed based on the Unpivot value, and a virtual table is constructed based on the Unpivot value and the row data; the original table corresponding to the Unpivot information is obtained, and a join operation is performed on the original table and the virtual table to obtain a chain-like logical plan structure.

[0094] In one possible implementation, when the transformation data in the SQL statement includes Unpivot clause information, the Unpivot value is obtained; for each Unpivot value, row data corresponding to the Unpivot value is constructed; a virtual table is constructed based on the Unpivot value and the row data, the data of the virtual table is encapsulated, and a unique table name for the virtual table is generated.

[0095] In one possible implementation, a target column set is obtained according to the logical plan tree, and a binding operation is performed on the target column set using a projection expression; the projection list is traversed, and when the target column name in the projection list is the same as the target column name in the target column set, deduplication is performed, and the target column is deleted from the projection list.

[0096] This application also provides a storage medium (computer-readable storage medium). This storage medium stores one or more programs. The storage medium may include volatile memory, such as random access memory; it may also include non-volatile memory, such as read-only memory, flash memory, hard disk, or solid-state drive; it may also include combinations of the above types of memory. When one or more programs in the storage medium can be executed by one or more processors to implement the row / column conversion method executed on the row / column conversion device side described above, the processor is used to execute the row / column conversion program stored in the memory to implement the following steps of executing the row / column conversion method on the row / column conversion device side: For the target table in the database to be formatted, a Structured Query Language (SQL) statement is obtained. This SQL statement carries the conversion data for the target table, including Pivot clause information and Unpivot clause information. Based on the Pivot and Unpivot clause information, an intermediate subquery structure is constructed, and a projection list is obtained. Iterative nested conversion processing is performed based on the projection list and the intermediate subquery structure to construct a logical plan tree. An aggregate expression is invoked to perform a row-to-column conversion on specified data in the target table, where the aggregate expression is constructed using the Pivot value from the Pivot clause information. The logical plan tree is then invoked to perform a column-to-row conversion on specified data in the target table, where the logical plan tree is constructed using the Unpivot value from the Unpivot clause information. Finally, deduplication is performed on the target table after the row-to-column and row-to-column conversion operations.

[0097] In one possible implementation, the Pivot clause information and the Unpivot clause information are converted into an ordered sequence; an intermediate subquery structure is constructed based on the first N-1 transformation operations in the ordered sequence; a projection list is obtained for the Nth transformation operation of the ordered sequence; and iterative nested transformation processing is performed based on the projection list and the intermediate subquery structure to construct a logical plan tree.

[0098] In one possible implementation, when the transformation data in the SQL statement includes Pivot clause information, a binary equality expression is constructed based on the Pivot value in the Pivot clause information; the binary equality expressions are combined to construct a unified aggregate expression, and a target name is set for the aggregate expression based on the Pivot information.

[0099] In one possible implementation, when the transformation data in the SQL statement includes Pivot clause information, the Pivot value is obtained; the multidimensional value corresponding to the Pivot value is obtained based on the Pivot value; and a binary equality expression corresponding to the multidimensional value is constructed based on the multidimensional value corresponding to the Pivot value.

[0100] In one possible implementation, when the transformation data in the SQL statement includes Unpivot clause information, row data is constructed based on the Unpivot value, and a virtual table is constructed based on the Unpivot value and the row data; the original table corresponding to the Unpivot information is obtained, and a join operation is performed on the original table and the virtual table to obtain a chain-like logical plan structure.

[0101] In one possible implementation, when the transformation data in the SQL statement includes Unpivot clause information, the Unpivot value is obtained; for each Unpivot value, row data corresponding to the Unpivot value is constructed; a virtual table is constructed based on the Unpivot value and the row data, the data of the virtual table is encapsulated, and a unique table name for the virtual table is generated.

[0102] In one possible implementation, a target column set is obtained from the logical plan tree, and a binding operation is performed on the target column set using a projection expression; the projection list is traversed, and when the target column name in the projection list is the same as the target column name in the target column set, deduplication is performed, and the target column is deleted from the projection list.

[0103] The device embodiments described above are merely illustrative. The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the modules can be selected to achieve the purpose of this embodiment according to actual needs.

[0104] Through the above description of the embodiments, those skilled in the art can clearly understand that each embodiment can be implemented using software plus a general-purpose hardware platform, or of course, using hardware. Based on this understanding, the above technical solutions, in essence or the parts that contribute to the related technology, can be embodied in the form of a software product. This computer software product can be stored in a computer-readable storage medium, such as ROM / RAM, magnetic disk, optical disk, etc., and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute the methods described in the various embodiments or some parts of the embodiments.

[0105] It should be understood that the terminology used herein is for the purpose of describing particular exemplary embodiments only and is not intended to be limiting. Unless the context clearly indicates otherwise, the singular forms “a,” “an,” and “described” as used herein may also include the plural forms. The terms “comprising,” “including,” “containing,” and “having” are inclusive and therefore indicate the presence of the stated features, steps, operations, elements, and / or components, but do not exclude the presence or addition of one or more other features, steps, operations, elements, components, and / or combinations thereof. The method steps, processes, and operations described herein are not construed as requiring them to be performed in a particular order described or illustrated unless the order of performance is explicitly indicated. It should also be understood that additional or alternative steps may be used.

[0106] The above description is merely a specific embodiment of this application, enabling those skilled in the art to understand or implement this application. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of this application. Therefore, this application is not to be limited to the embodiments shown herein, but is to be accorded the widest scope consistent with the principles and novel features claimed herein.

Claims

1. A row / column conversion method, characterized in that, The method includes: For the target table in the database to be formatted, obtain a Structured Query Language (SQL) statement. The SQL statement carries the conversion data of the target table, which includes Pivot clause information and Unpivot clause information. Based on the Pivot clause information and the Unpivot clause information, an intermediate subquery structure is constructed, a projection list is obtained, and an iterative nested transformation process is performed based on the projection list and the intermediate subquery structure to construct a logical plan tree; An aggregate expression is invoked to perform a row-to-column transformation on specified data in the target table, wherein the aggregate expression is constructed using the Pivot value in the Pivot clause information; The logical plan structure is invoked to perform a column-to-row operation on specified data in the target table. The logical plan structure is constructed using the Unpivot value in the Unpivot clause information. Perform deduplication on the target table after performing row-to-column and row-to-row operations.

2. The method according to claim 1, characterized in that, The step of constructing an intermediate subquery structure based on the Pivot clause information and the Unpivot clause information, obtaining a projection list, and performing iterative nested transformation processing based on the projection list and the intermediate subquery structure to construct a logical plan tree includes: The Pivot clause information and the Unpivot clause information are converted into an ordered sequence; Based on the first N-1 transformation operations in the ordered sequence, construct an intermediate subquery structure; Obtain the projection list for the Nth transformation operation of the ordered sequence; The logical plan tree is constructed by performing iterative nested transformations based on the projection list and the intermediate subquery structure.

3. The method according to claim 1, characterized in that, The operation of calling the aggregate expression to perform row-to-column transformation on specified data in the target table includes: When the transformation data in the SQL statement includes Pivot clause information, a binary equality expression is constructed based on the Pivot value in the Pivot clause information; The binary equality expressions are combined to construct a unified aggregate expression, and a target name is set for the aggregate expression based on the Pivot information.

4. The method according to claim 3, characterized in that, When the transformation data in the SQL statement includes Pivot clause information, a binary equality expression is constructed based on the Pivot value in the Pivot clause information, including: When the transformation data in the SQL statement includes Pivot clause information, obtain the Pivot value; Obtain the multidimensional value corresponding to the Pivot value based on the Pivot value; Based on the multidimensional value corresponding to the Pivot value, construct the binary equality expression corresponding to the multidimensional value.

5. The method according to claim 1, characterized in that, The invocation logic plan structure performs a column-to-row operation on specified data in the target table, including: When the transformation data in the SQL statement includes Unpivot clause information, row data is constructed based on the Unpivot value, and a virtual table is constructed based on the Unpivot value and the row data; Obtain the original table corresponding to the Unpivot information, and perform a join operation on the original table and the virtual table to obtain a chain-like logical plan structure.

6. The method according to claim 5, characterized in that, When the transformation data in the SQL statement includes Unpivot clause information, row data is constructed based on the Unpivot value, and a virtual table is constructed based on the Unpivot value and the row data, including: When the transformation data in the SQL statement includes Unpivot clause information, obtain the Unpivot value; For each Unpivot value, construct the row data corresponding to that Unpivot value; A virtual table is constructed based on the Unpivot value and the row data, the data of the virtual table is encapsulated, and a unique table name is generated for the virtual table.

7. The method according to claim 1, characterized in that, The process of removing duplicates from the target table after performing row-to-column and row-to-row operations includes: Obtain the target column set based on the logical plan tree, and perform a binding operation on the target column set using a projection expression; The projection list is traversed. When the target column name in the projection list is the same as the target column name in the target column set, deduplication is performed and the target column is deleted from the projection list.

8. A row / column conversion device, characterized in that, The device includes: The acquisition module is used to acquire a Structured Query Language (SQL) statement for a target table in the database that needs to be formatted. The SQL statement carries the conversion data of the target table, and the conversion data includes Pivot clause information and Unpivot clause information. The construction module is used to construct an intermediate subquery structure based on the Pivot clause information and the Unpivot clause information, obtain a projection list, and perform iterative nested transformation processing based on the projection list and the intermediate subquery structure to construct a logical plan tree; The first execution module is used to call an aggregate expression to perform a row-to-column transformation operation on specified data in the target table, wherein the aggregate expression is constructed using the Pivot value in the Pivot clause information; The second execution module is used to call a logical plan structure to perform a column-to-row operation on specified data in the target table. The logical plan structure is constructed using the Unpivot value in the Unpivot clause information. The deduplication module is used to perform deduplication on the target table after performing row-to-column and row-to-row operations.

9. A server, characterized in that, include: A processor and a memory, the processor being configured to execute a row / column conversion program stored in the memory to implement the row / column conversion method according to any one of claims 1-7.

10. A storage medium, characterized in that, The storage medium stores one or more programs, which can be executed by one or more processors to implement the row / column conversion method according to any one of claims 1-7.

Images